블로그 서비스의 DB 구축

 

create database m_board;

use m_board;

create table user(
	id int primary key auto_increment,
    username varchar(100) not null unique,
	password varchar(255) not null,
    email varchar(100) not null,
    userRole varchar(20),
    createDate timestamp
);

-- board table(게시글), reply table(댓글) 설계

create table board(
	id int primary key auto_increment,
    userId int,
    title varchar(100) not null,
	content text,
    foreign key(userId) references user(id)
);

create table reply(
	id int primary key auto_increment,
    userId int,
    boardId int,
    content varchar(300) not null,
    createDate timestamp,
    foreign key(userId) references user(id) on delete set null, -- 사용자가 탈퇴 시, 사용자 아이디를 자동적으로 null로 설정하도록 추가적인 제약을 검
    foreign key(boardId) references board(id)
);

 

정규화 검토

• 제 1 정규형 (1NF): 모든 필드는 원자값을 가지고 있으며, 각 컬럼은 유일한 데이터 유형을 가진다. 테이블의 모든 키는 유일하게 식별된다.
• 제 2 정규형 (2NF): 기본 키의 일부에만 종속되는 비키 종속성이 없다. 각 테이블에서 기본 키가 완전히 기능적 종속성을 이루고 있다.
• 제 3 정규형 (3NF): 모든 필드가 기본 키에만 종속되고, 기본 키가 아닌 다른 필드에 종속되는 이행적 종속성이 없다. 예를 들어, User 테이블에서 사용자의 주소나 역할은 사용자 ID에만 종속된다.

 

샘플 데이터 입력

desc user;

INSERT INTO user (username, password, email, address, userRole, createDate)
VALUES
('홍길동', '1234', 'hong@example.com', '서울시 강남구', 'admin', NOW()),
('이순신', '1234', 'lee@example.com', '부산시 해운대구', 'user', NOW()),
('김유신', '1234', 'kim@example.com', '대구시 수성구', 'user', NOW());

-- 스키마 구조를 변경하는 쿼리 --- DDL, DML, DCL 
-- user 테이블에 address 컬럼을 추가해주세요 
alter table user add address varchar(100) not null;

desc board;
INSERT INTO board (userId, title, content, readCount)
VALUES
(1, '첫 번째 글입니다', '안녕하세요, 홍길동입니다. 이것은 테스트 게시글입니다.', 150),
(2, '이순신의 포스팅', '부산에서 이순신입니다. 바다가 아름다운 날입니다.', 45),
(3, '대구의 뜨거운 여름', '여름이 기승을 부리는 대구에서 김유신입니다.', 30);
alter table board add readCount int;


INSERT INTO reply (userId, boardId, content, createDate)
VALUES
(2, 1, '홍길동님의 글 잘 읽었습니다!', NOW()),
(3, 1, '저도 의견이 같네요.', NOW()),
(1, 2, '부산도 좋지만 서울도 좋아요!', NOW()),
(1, 3, '대구가 그렇게 덥군요, 조심하세요!', NOW());

 

SELECT * FROM user;
SELECT * FROM board;
SELECT * FROM reply;

-- 특정 사용자의 게시글 조회 (사용자 ID가 1인 홍길동의 모든 게시글을 보고 싶다면) 
SELECT b.title, b.content, b.readCount
FROM board b
WHERE b.userId = 1;

-- 1번 게시글 대한 모든 댓글 조회
SELECT u.username, r.content, r.createDate
FROM reply r
LEFT JOIN user u ON r.userId = u.id
WHERE r.boardId = 1;


-- 게시글에 댓글 달기  
-- 예를 들어, 사용자 ID 2가 게시글 ID 1에 
-- "새로운 댓글입니다"라는 내용의 댓글을 추가하려면 다음 쿼리를 사용합니다.
-- Insert into ...
INSERT INTO reply (userId, boardId, content, createDate)
VALUES (2, 1, '새로운 댓글입니다', NOW());


-- 특정 사용자의 게시글 해당 게시글의 댓글 수 조회
-- 제목, 내용, 작성자 이름, 댓글 수
-- 글1 , ..., 홍길동,  19
-- 글2 ,  .., 이순신,  10
SELECT b.title, b.content, u.username, COUNT(r.id) as CommentCount
FROM board b
JOIN user u ON b.userId = u.id
LEFT JOIN reply r ON b.id = r.boardId
GROUP BY b.id;


-- 조회수가 가장 높은 게시글 상위 2개만 조회
SELECT title, content, readCount
FROM board
ORDER BY readCount DESC
limit 2;