DB에 데이터 입력하기

프로젝트에서 만든 기능을 테스트 해보기 위해 샘플 데이터를 만들어야 했고 이것을 MariaDB GUI를 켜서
직접 데이터를 넣어보았다.

-- 멤버(Member) 생성
INSERT INTO member (email, nickname, boj_id, thumb_photo, social_info)
VALUES ('john@example.com', 'John', 'john123', 'thumbnail.jpg', 'github.com/john');

-- 알고리즘(Algorithm) 생성
INSERT INTO algorithm (algorithm_name) VALUES ('dp'), ('그리디'), ('완전탐색');

-- 테스트(Test) 생성
INSERT INTO test (test_date, test_time, problem_count, start_difficulty, end_difficulty, test_typename, test_rating, member_id)
VALUES
('2023-07-18 10:00:00', 3600, 20, 3, 6, 'Practice Test 1', 1500, 1),
('2023-07-19 14:30:00', 4200, 15, 4, 7, 'Practice Test 2', 1600, 1);

-- 문제(Problem) 생성
INSERT INTO problem (boj_problem_id, problem_difficulty) VALUES
(12345, 5), (23456, 6), (34567, 7), (45678, 5), (56789, 4),
(67890, 3), (78901, 6), (89012, 7), (90123, 4), (123456, 5),
(234567, 6), (345678, 7), (456789, 5), (567890, 4), (678901, 3),
(789012, 6), (890123, 7), (901234, 4), (1234567, 5), (2345678, 6);

-- 알고리즘-문제 매핑(AlgorithmProblemList) 생성
INSERT INTO algorithm_problem_list (problem_id, algorithm_id)
VALUES
(1, 1), (2, 1), (3, 1), (4, 2), (5, 2),
(6, 3), (7, 3), (8, 3), (9, 3), (10, 1),
(11, 2), (12, 1), (13, 3), (14, 2), (15, 1),
(16, 1), (17, 2), (18, 2), (19, 3), (20, 3),
(1, 2), (2, 3), (3, 1), (4, 2), (5, 3),
(6, 2), (7, 1), (8, 3), (9, 1), (10, 2),
(11, 1), (12, 2), (13, 3), (14, 3), (15, 2),
(16, 2), (17, 3), (18, 1), (19, 3), (20, 1);

-- AttemptProblem 생성 (test 1)
INSERT INTO attempt_problem (is_solved, test_date, member_id, test_id, problem_id)
SELECT
    IF((ROW_NUMBER() OVER () <= 3), TRUE, FALSE) AS is_solved,
    '2023-07-18' AS test_date,
    1 AS member_id,
    1 AS test_id,
    problem_id
FROM problem
ORDER BY RAND()
LIMIT 5;

-- AttemptProblem 생성 (test 2)
INSERT INTO attempt_problem (is_solved, test_date, member_id, test_id, problem_id)
SELECT
    IF((ROW_NUMBER() OVER () <= 3), TRUE, FALSE) AS is_solved,
    '2023-07-19' AS test_date,
    1 AS member_id,
    2 AS test_id,
    problem_id
FROM problem
ORDER BY RAND()
LIMIT 5;

일반적으로 insert into 엔티티명 (필드1, 필드2, ... ) 구조를 따르는 것을 알 수 있었다.
그리고 Values에 차례대로 값을 넣으면 된다.
여기서 attempt_problem이 인상 깊었는데 ROW_NUMBER OVER()를 사용하면 각 행에 번호가 주어진다.
그리고 이 값이 <= 3이라면 True, 아니라면 False를 넣도록 했다.
그리고 이 select 문에서 나온 결과를 insert into 하면서도 order by rand를 통해 무작위로 값을 가져온다.