0

SELECT VERSION(); 
-- 10.3.29-MariaDB

CREATE TABLE users (user_id BIGINT PRIMARY KEY AUTO_INCREMENT, nickname VARCHAR(30) NOT NULL); CREATE TABLE category (category_id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL); CREATE TABLE book (book_id BIGINT PRIMARY KEY AUTO_INCREMENT, category_id BIGINT NOT NULL, title VARCHAR(100) NOT NULL, description TEXT, is_available BOOLEAN NOT NULL DEFAULT TRUE, FOREIGN KEY (category_id) REFERENCES category(category_id)); CREATE TABLE rental (rental_id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, book_id BIGINT NOT NULL, rented_at DATETIME NOT NULL, due_at DATETIME NOT NULL, returned_at DATETIME NULL, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (book_id) REFERENCES book(book_id));
CREATE TABLE tag (tag_id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL); CREATE TABLE book_tag (book_id BIGINT, tag_id BIGINT, PRIMARY KEY (book_id, tag_id), FOREIGN KEY (book_id) REFERENCES book(book_id), FOREIGN KEY (tag_id) REFERENCES tag(tag_id)); CREATE TABLE book_like (user_id BIGINT, book_id BIGINT, PRIMARY KEY (user_id, book_id), FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (book_id) REFERENCES book(book_id)); CREATE TABLE notification (notification_id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, type VARCHAR(30) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id));

INSERT INTO users (nickname) VALUES ('민서'), ('수현'); INSERT INTO category (name) VALUES ('문학'), ('과학'); INSERT INTO book (category_id, title, description, is_available) VALUES (1, '달빛 도서관', '소설', TRUE), (1, '겨울의 편지', '에세이', FALSE), (2, '우주를 읽는 법', '과학 교양', TRUE);
INSERT INTO rental (user_id, book_id, rented_at, due_at, returned_at) VALUES (1, 2, '2026-08-10 10:00:00', '2026-08-17 10:00:00', NULL), (2, 1, '2026-08-01 10:00:00', '2026-08-08 10:00:00', '2026-08-07 15:00:00'); INSERT INTO tag (name) VALUES ('소설'), ('추천'), ('과학'); INSERT INTO book_tag (book_id, tag_id) VALUES (1, 1), (1, 2), (3, 3); INSERT INTO book_like (user_id, book_id) VALUES (1, 1), (1, 3);

1

요구사항: “대여 가능한 책의 제목과 설명을 최신순으로 보여 주세요.”

SELECT * FROM book;
SELECT 
    book_id, title, description 
FROM 
    book 
WHERE 
    is_available = TRUE 
ORDER BY 
    book_id DESC;

요구사항: “문학 카테고리에서 대여 가능한 책을 10권 보여 주세요.”

SELECT * FROM book;
SELECT * FROM category;
-- SELECT b.book_id, b.title, c.name AS category_name FROM book  AS b JOIN category AS c ON b.category_id = c.category_id WHERE c.name = '문학' AND b.is_available = TRUE ORDER BY b.book_id DESC LIMIT 10;
SELECT
    b.book_id,
    b.title,
    c.name
FROM
    book AS b
JOIN
    category as c
ON
    b.category_id = c.category_id
    AND c.name = "문학"
    AND b.is_available = TRUE
ORDER BY
    b.book_id DESC
LIMIT 
    10;

2

“문학 카테고리에서 대여 가능한 도서를 최신순으로 10권 보여 준다.”

SELECT * FROM book;
SELECT * FROM category;
-- SELECT b.book_id, b.title, c.name AS category_name FROM book  AS b JOIN category AS c ON b.category_id = c.category_id WHERE c.name = '문학' AND b.is_available = TRUE ORDER BY b.book_id DESC LIMIT 10;
SELECT
    b.book_id,
    b.title,
    c.name
FROM
    book AS b
JOIN
    category as c
ON
    b.category_id = c.category_id
    AND c.name = "문학"
    AND b.is_available = TRUE
ORDER BY
    b.book_id DESC
LIMIT 
    10;

“로그인한 사용자가 아직 반납하지 않은 책을 반납 예정일 순으로 보여 준다.”

SELECT * FROM book;
SELECT * FROM rental;
-- book의 book_id와 rental의 book_id 엮기
-- rental의 due_at 오름차순

SELECT
    b.title, r.rented_at, r.due_at
FROM
    book as b
JOIN
    rental as r
ON
    b.book_id = r.book_id
    AND returned_at IS NOT NULL
    AND user_id = 2 /*현재 로그인한 유저의 아이디*/
ORDER BY
    r.due_at ASC;

“책 상세 화면에서 태그 목록과 현재 사용자의 좋아요 여부를 함께 확인한다.”

SELECT * FROM book;
SELECT * FROM book_tag;
SELECT * FROM tag;
SELECT * FROM book_like;

WITH params AS (
    SELECT
        1 AS TARGET_BOOK_ID,
        1 AS TARGET_USER
),
tag_texts AS (
    SELECT
        book_tag.book_id as book_id,
        GROUP_CONCAT(tag.name) AS tags
    FROM
        book_tag
    JOIN
        tag
    ON
        book_tag.tag_id = tag.tag_id
    JOIN
        params
    ON
        params.TARGET_BOOK_ID = book_tag.book_id
    GROUP BY
        book_id
)
SELECT
    book.title,
    tag_texts.tags,
    EXISTS (
        SELECT 1
        FROM book_like
        JOIN params
        ON book_like.book_id = book.book_id
        AND params.TARGET_USER = book_like.user_id
        AND params.TARGET_BOOK_ID = book.book_id
    ) as book_like
FROM
    book
JOIN
    tag_texts
ON
    book.book_id = tag_texts.book_id;

with 문으로 가독성을 살렸다.
특히 params로 파라미터를 넣어서 타겟 아이디와 유저 아이디를 받을 수 있게 했다.
그러나 새롭게 생성하는 테이블이 있기에 약간 시간이 오래걸릴 것 같다.

1. params : 변수 관리
2. tag_texts : 특정 책 기준으로 tag 숫자 목록을 comma 단위로 바꿔줌. title과 tag(tag1,tag2 ..)
3. SELECT 문 : title, tag, 좋아요 여부를 반환함.
4. EXISTS 문 : 좋아요 목록을 보면서 일치하는 값이 존재하는지 리턴

mission

SELECT * FROM book;
SELECT * FROM category;
SELECT * FROM rental;
SELECT
    book.title,
    book.description,
    category.name
FROM
    book
JOIN
    category
ON
    category.category_id = book.category_id
    AND category.name = "문학"
    AND book.is_available = 1 /*어차피 여기서 NULL 걸러짐*/
JOIN
    rental
ON
    rental.book_id = book.book_id
ORDER BY
    returned_at DESC
LIMIT 10;

SELECT * FROM users;
SELECT * FROM book;
SELECT * FROM rental;

SELECT
    book.title,
    rental.rented_at,
    rental.due_at
FROM
    rental
JOIN
    users
ON
    rental.user_id = users.user_id
    AND returned_at IS NULL
    AND users.nickname = "민서" /*id로 처리하는 게 좋을 듯*/
JOIN
    book
ON
    rental.book_id = book.book_id
ORDER BY
    rental.due_at;

3은 위와 동일.

SELECT user.nickname, mission_list.mission_name FROM user JOIN mission_user ON user.user_id = mission_user.user_id AND mission_user.complete = 1 JOIN mission_list ON mission_list.mission_id = mission_user.mission_id ORDER BY mission_user.completed_date LIMIT 10;

유저들이 어떤 미션들을 끝냈는지 날짜 순으로 보여줌. 10개씩만

• 1. 요구사항 → SQL로 번역하기

     찾아보기: 화면 요구사항을 SELECT 컬럼, FROM 테이블, JOIN 관계, WHERE 조건, ORDER BY/LIMIT으로 나누는 방법을 정리해 보세요.

     SELECT : 최종적으로 뭘 보여줄지 or 중간에 넘겨야 할 데이터

     FROM : 그 테이블

     JOIN : 타 테이블과 연결

     WHERE : 필터링 (ON은 JOIN부에서 필터링)

     ORDER BY와 LIMIT으로 정렬 및 최대 개수 제한

• 2. DDL과 DML

     찾아보기: CREATE TABLE이 테이블 구조를, INSERT가 행 데이터를 담당하는 이유와 ALTER TABLE과의 차이를 살펴보세요.

     CREATE TABLE : 테이블의 큰 틀을 정의함.

     INSERT : 행 단위로 데이터 넣기

     ALTER TABLE과 차이 : CREATE는 생성, ALTER은 이미 존재한 거 수정할 때 씀

• 3. PK·FK와 JOIN 조건

     찾아보기: PK·FK가 무엇을 보장하는지, ON 절에서 관계가 잘못 연결되면 왜 중복 행이 생기는지 확인해 보세요.

     PK: 고유성. 보통 AUTO INCREMENT

     FK: 타 테이블의 PK 받아오고 없는 데이터 연결 방지

     ON에서 관계 설정 잘못하면 n x m 개 생성됨.

     번외. 동등(=) 조건 쓰면 Hash Join을 쓸 수 있는데 O(n + m)으로 아주 빨라질 수 있음

• 4. WHERE와 NULL

     찾아보기: WHERE 조건에서 NULL을 = 과 비교할 수 없는 이유와 IS NULL을 사용하는 이유를 알아보세요.

     NULL은 값이 아닌 상태여서 IS NULL, IS NOT NULL 씀. 비교하면 Unknown이 됨

• 5. ORDER BY와 일관된 정렬

     찾아보기: ORDER BY가 없을 때 목록 순서가 보장되지 않는 이유와 동일 값일 때의 보조 정렬 기준을 찾아보세요.

     DB는 순서 없는 행의 집합이어서 그럼. 그리고 DB 옵티마이저가 굉장히 똑똑해서 가장 빠른 방법으로 데이터를 읽는데 이 과정에서 뒤죽박죽 가능성 있음.

     보조 정렬은 ,(콤마) 뒤에 작성하면 되고, PK로 처리하면 고유해서 좋다.

• 6. LIMIT / OFFSET과 페이지네이션

     찾아보기: LIMIT/OFFSET이 페이지 번호 방식과 어떻게 연결되는지, 데이터가 많아질 때 어떤 한계가 있는지 살펴보세요.

     LIMIT은 개수, OFFSET은 몇 칸 띄울지

     문제는 OFFSET은 앞에 것 읽고 버리는 거여서 부하 가능

     그래서 고유한 column이 잘 정렬 되어있다. 그러면 WHERE 문으로 마지막 부분부터 처리함

     ex) offset 0, limit 10 으로 해서 id 56 까지 읽었음

     그러면 57(56 초과)부터 처리하면 되니까

     WHERE id > 56 을 추가하면 됨.

     단 정렬이 잘 되어있어야하고 고유해야 함