2024.06.11 MySQL활용 블로그 서비스의 DB 구축(2)

class_26.sql

class_26.sql

0.00MB

User 테이블 명세서

 

 

Board 테이블 명세서

 

 

Reply 테이블 명세서

 

create database m_boad;
use m_boad;

create table user(
	id int primary key auto_increment,
    username varchar(100) not null unique, -- ex) 닉네임인 경우 (unique를 통해 중복검사)
    password varchar(255) not null,
    email varchar(100) not null,
    userRole varchar(20),
    createDate timestamp
);

-- board table, reply table 설계
-- 하나의 게시글에는 여러개의 댓글이 달릴 수 있다. 1:N

create table board(
	id int primary key auto_increment,
    userId int, -- 만약에 회원탈퇴를 하면 not null 이면 이전에 작성된 게시물도 사라져서 (회사마다 정책이 다르다..)
    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, -- 사용자의 id가 없다면 null로 처리
    foreign key(boardId) references board(id)
);

 

 

정규화 검토

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

 

샘플 데이터 입력

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());




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




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

 

-- 특정 사용자의 게시글 조회 (사용자 ID가 1인 홍길동의 모든 게시글을 보고 싶다면) 


-- 1번 게시글 대한 모든 댓글 조회


-- 게시글에 댓글 달기  
-- 예를 들어, 사용자 ID 2가 게시글 ID 1에 
-- "새로운 댓글입니다"라는 내용의 댓글을 추가하려면 다음 쿼리를 사용합니다.
-- Insert into ...


-- 특정 사용자의 게시글 해당 게시글의 댓글 수 조회
-- 제목, 내용, 작성자 이름, 댓글 수
-- 글1 , ..., 홍길동,  19
-- 글2 ,  .., 이순신,  10


-- 조회수가 가장 높은 게시글 상위 2개만 조회

 

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;