실습 — 게시판 스키마 설계

CREATE TABLE Board.T_TAGCSV (post_num int PRIMARY KEY, tags nvarchar(200) NULL);
INSERT INTO Board.T_TAGCSV VALUES
 (1, N'SQL,조인,입문'), (2, N'SQL,트랜잭션'), (3, N'입문'),
 (4, N'조인,성능'), (5, N'SQL서버,백업'), (6, N'성능');
GO
-- (가) SQL 태그가 붙은 글을 찾습니다.
SELECT post_num, tags FROM Board.T_TAGCSV WHERE tags LIKE N'%SQL%';

-- (나) 태그별 글 수를 셉니다.
SELECT tags, COUNT(*) AS 글수 FROM Board.T_TAGCSV GROUP BY tags;

-- (다) 태그 이름을 SQL 에서 T-SQL 로 고칩니다.
SELECT post_num, REPLACE(tags, N'SQL', N'T-SQL') AS 고친결과 FROM Board.T_TAGCSV;

CREATE TABLE Board.TAGS (
    num  int          IDENTITY(1,1) NOT NULL,
    name nvarchar(30)               NOT NULL,
    CONSTRAINT PK_TAGS PRIMARY KEY CLUSTERED (num),
    CONSTRAINT UX_TAGS_name UNIQUE (name)      -- 같은 이름이 둘 있으면 안 됩니다
);
GO
CREATE TABLE Board.POST_TAGS (
    post_num int NOT NULL,
    tag_num  int NOT NULL,
    -- 두 열이 함께 기본 키입니다. 대리 키를 따로 두지 않습니다.
    CONSTRAINT PK_POST_TAGS PRIMARY KEY CLUSTERED (post_num, tag_num),
    CONSTRAINT FK_POST_TAGS_POSTS FOREIGN KEY (post_num)
        REFERENCES Board.POSTS (num) ON DELETE CASCADE,
    CONSTRAINT FK_POST_TAGS_TAGS FOREIGN KEY (tag_num)
        REFERENCES Board.TAGS (num)          -- 태그는 연쇄 삭제하지 않습니다
);
GO
-- 태그로 글을 찾는 반대 방향입니다. 두 열뿐이라 그대로 커버링이 됩니다.
CREATE NONCLUSTERED INDEX IX_POST_TAGS_tag ON Board.POST_TAGS (tag_num, post_num);

-- (가) SQL 태그가 붙은 글
SELECT COUNT(*) AS 글수 FROM Board.POST_TAGS pt
    JOIN Board.TAGS t ON t.num = pt.tag_num
WHERE t.name = N'SQL';

-- (나) 태그별 글 수
SELECT t.name AS 태그, COUNT(*) AS 글수 FROM Board.POST_TAGS pt
    JOIN Board.TAGS t ON t.num = pt.tag_num
GROUP BY t.name ORDER BY 글수 DESC, 태그;

-- (다) 이름 고치기
UPDATE Board.TAGS SET name = N'T-SQL' WHERE name = N'SQL';

CREATE TABLE Board.POST_LIKES (
    post_num int          NOT NULL,
    user_num int          NOT NULL,
    reg_date datetime2(0) NOT NULL
        CONSTRAINT DF_POST_LIKES_reg_date DEFAULT SYSDATETIME(),
    CONSTRAINT PK_POST_LIKES PRIMARY KEY CLUSTERED (post_num, user_num),
    CONSTRAINT FK_POST_LIKES_POSTS FOREIGN KEY (post_num)
        REFERENCES Board.POSTS (num) ON DELETE CASCADE,
    CONSTRAINT FK_POST_LIKES_USERS FOREIGN KEY (user_num)
        REFERENCES Member.USERS (num)
);
GO
-- 내가 누른 글 목록을 내는 방향입니다.
CREATE NONCLUSTERED INDEX IX_POST_LIKES_user ON Board.POST_LIKES (user_num, post_num);
GO
-- 같은 사람이 같은 글에 또 누르면
INSERT INTO Board.POST_LIKES (post_num, user_num) VALUES (1, 2);

-- (가) 계산 열로 두려 하면
ALTER TABLE Board.POSTS ADD like_count AS
    (SELECT COUNT(*) FROM Board.POST_LIKES WHERE post_num = num);
GO

-- (나) 인덱싱된 뷰로 두면
CREATE VIEW Board.V_POST_LIKE_COUNT WITH SCHEMABINDING AS
SELECT post_num, COUNT_BIG(*) AS like_count
FROM Board.POST_LIKES GROUP BY post_num;
GO
CREATE UNIQUE CLUSTERED INDEX CX_V_POST_LIKE_COUNT ON Board.V_POST_LIKE_COUNT (post_num);

DELETE FROM Board.POSTS WHERE num = 6;

ALTER TABLE Board.POSTS ADD del_date datetime2(0) NULL;

CREATE NONCLUSTERED INDEX IX_POSTS_live
    ON Board.POSTS (board_num, group_num DESC, sort_no)
    INCLUDE (title, user_num, reg_date, hit_count, depth)
    WHERE del_date IS NULL;
GO
SELECT TOP (20) num, title FROM Board.POSTS
WHERE board_num = 2 AND del_date IS NULL
ORDER BY group_num DESC, sort_no;

SELECT TOP (20) num, title FROM Board.POSTS WITH (INDEX(IX_POSTS_live))
WHERE board_num = 2 AND del_date IS NULL
ORDER BY group_num DESC, sort_no;

DROP INDEX IX_POSTS_live ON Board.POSTS;
GO
CREATE NONCLUSTERED INDEX IX_POSTS_live
    ON Board.POSTS (board_num, group_num DESC, sort_no)
    INCLUDE (title, user_num, reg_date, hit_count, depth, del_date)   -- 필터 열을 더합니다
    WHERE del_date IS NULL;

UPDATE Board.POSTS SET del_date = '2026-03-01' WHERE num = 6;

SELECT num, parent_num, depth, sort_no, del_date, LEFT(title, 20) AS 제목
FROM Board.POSTS WHERE group_num = 6 ORDER BY sort_no;

CREATE VIEW Board.V_LIVE_POSTS AS
SELECT num, board_num, category_num, user_num, parent_num, group_num, depth, sort_no,
       title, content, hit_count, reg_date, mod_date
FROM Board.POSTS WHERE del_date IS NULL;

-- 태그별로 살아 있는 글이 몇 건인지
SELECT TOP (5) t.name AS 태그, COUNT(*) AS 글수
FROM Board.POST_TAGS pt
    JOIN Board.TAGS t ON t.num = pt.tag_num
    JOIN Board.V_LIVE_POSTS p ON p.num = pt.post_num
GROUP BY t.name ORDER BY 글수 DESC, 태그;

-- 좋아요가 많은 글
SELECT TOP (5) p.num, LEFT(p.title, 26) AS 제목, COUNT(l.user_num) AS 좋아요
FROM Board.V_LIVE_POSTS p JOIN Board.POST_LIKES l ON l.post_num = p.num
GROUP BY p.num, p.title ORDER BY 좋아요 DESC, p.num;

-- 글을 지운 것으로 표시해도 태그와 좋아요는 남는지
UPDATE Board.POSTS SET del_date = SYSDATETIME() WHERE num = 100;
SELECT COUNT(*) AS 태그 FROM Board.POST_TAGS WHERE post_num = 100;
SELECT COUNT(*) AS 좋아요 FROM Board.POST_LIKES WHERE post_num = 100;