[MySQL] 클론코딩 tumblr의 게시글 검색 제작기

DB ERD

Node.js API

Node.js API 문서 예시

• 6개의 테이블을 조회합니다.
• Users, Posts, Images, Tags, Favorites, Follows
  • userId : Post를 작성한 user의 userId를 반환합니다.
  • nickname : Post를 작성한 user의 nickname을 반환합니다.
  • profileImg : Post를 작성한 user의 profileImg를 반환합니다.
  • postId : Post의 postId를 반환합니다.
  • reBlog : Post가 참조하는 부모 테이블을 반환합니다.
  • title : Post의 title을 반환합니다.
  • img : 동일한 postId의 img를 Images 테이블에서 가져옵니다.
  • content : Post의 content를 반환합니다.
  • tag : 동일한 postId의 tag를 Tags 테이블에서 가져옵니다.
  • reactionCount : reBlog가 설정되어있거나 Favorites 테이블에서 favorite 설정되어있다면 1씩 추가합니다.
  • favorite : 게시글을 보고 있는 userId가 Post를 favorite 했는지 확인합니다.
  • follow : 게시글을 보고 있는 userId가 Post를 작성한 userId와 follow 상태인지 'Y', 'N'으로 반환합니다.
  • createdAt : Post가 작성된 시간을 출력합니다.

# DB 생성 
CREATE TABLE Users(
    userId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email varchar(255) NOT NULL UNIQUE,
    nickname varchar(255) NOT NULL UNIQUE,
    password varchar(255) NOT NULL,
    profileImg varchar(1000),
    createdAt Datetime NOT NULL,
    updatedAt Datetime NOT NULL
);

CREATE TABLE Posts(
    postId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId int(11) NOT NULL,
    reBlog int(11),
    title varchar(255),
    content varchar(3000),
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (reBlog) REFERENCES Posts(postId) ON DELETE SET NULL,
);

CREATE TABLE Images(
    imageId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    postId int(11) NOT NULL,
    img varchar(1000) NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);

CREATE TABLE Tags(
    tagId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    postId int(11) NOT NULL,
    tag varchar(100) NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);

CREATE TABLE Favorites(
    favoriteId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId int(11) NOT NULL,
    postId int(11) NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);

CREATE TABLE Follows(
    followId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    followUserId int(11) NOT NULL,
    followerUserId int(11) NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (followUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (followerUserId) REFERENCES Users(userId) ON DELETE CASCADE
);

CREATE TABLE Alarms(
    alarmId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    giverUserId int(11) NOT NULL,
    receiverUserId int(11) NOT NULL,
    type TINYINT UNSIGNED NOT NULL,
    createdAt DateTime NOT NULL,
    updatedAt DateTime NOT NULL,
    FOREIGN KEY (giverUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (receiverUserId) REFERENCES Users(userId) ON DELETE CASCADE
);

# DB User 생성
insert into Users (email, nickname, password, createdAt, updatedAt) values ("shyu23@gmail.com", "123","1313", NOW(), NOW());
insert into Users (email, nickname, password, createdAt, updatedAt) values ("karlidis1@naver.com", "124","1414",NOW(), NOW());
insert into Users (email, nickname, password, createdAt, updatedAt) values ("archea@gmail.com", "1234","14141414",NOW(), NOW());
insert into Users (email, nickname, password, createdAt, updatedAt) values ("league1113@gmail.com", "4321","41414141",NOW(), NOW());
insert into Users (email, nickname, password, createdAt, updatedAt) values ("qwer@naver.com", "qwer","!@#$qwer",NOW(), NOW());

# DB Post 생성 
insert into Posts (userId, title, content, createdAt, updatedAt) values (1, "K K K K Title", "my Love K K K K",NOW(),NOW());
insert into Posts (userId, title, content, createdAt, updatedAt) values (1, "Opera Holic", "show me the money",NOW(),NOW());
insert into Posts (userId, title, content, createdAt, updatedAt) values (1, "Fance", "travel France",NOW(),NOW());
insert into Posts (userId, title, content, createdAt, updatedAt) values (2, "black number", "kobare",NOW(),NOW());
insert into Posts (userId, title, content, createdAt, updatedAt) values (2, "Lemon", "kenshi",NOW(),NOW());
insert into Posts (userId, reBlog, title, content, createdAt, updatedAt) values (3, 4, "reBlog Like !", "kuku kaka k k k ",NOW(),NOW());
insert into Posts (userId, reBlog, title, content, createdAt, updatedAt) values (3, 4, "Nitro Fun", "LOVE",NOW(),NOW());
insert into Posts (userId, reBlog, title, content, createdAt, updatedAt) values (2, 1, "Final BOSS", "EVOL",NOW(),NOW());
insert into Posts (userId, reBlog, title, content, createdAt, updatedAt) values (5, 2, "Final BOSS", "EVOL",NOW(),NOW());


# DB Favorite 생성
insert into Favorites (userId, postId, createdAt, updatedAt) values (1,1,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (1,2,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (1,3,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (2,2,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (2,4,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (2,1,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (3,1,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (1,6,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (4,2,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values (3,4,NOW(),NOW());
insert into Favorites (userId, postId, createdAt, updatedAt) values 
(4,3,NOW(),NOW()),
(4,5,NOW(),NOW());


# DB Image 생성
insert into Images (postId, img, createdAt, updatedAt) values 
(1, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787561.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(1, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787562.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(2, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787563.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(3, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787564.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(3, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787565.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(5, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787566.png", NOW(),NOW());
insert into Images (postId, img, createdAt, updatedAt) values 
(3, "https://ac.namu.la/20210608/c163d40a5a686667362966e8fdcb4f46a1aa9d36023a88cf119b782944787566.png", NOW(),NOW());

# DB Tag 생성
insert into Tags (postId, tag, createdAt, updatedAt) values (1, "Scream", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (1, "namu", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (1, "nyang", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (2, "aeong", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (2, "namu", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (2, "France", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (2, "Lemon", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (3, "aeong", NOW(),NOW());
insert into Tags (postId, tag, createdAt, updatedAt) values (4, "aeong", NOW(),NOW());

# DB Follow 생성
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (1, 2, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (1, 3, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (1, 4, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (2, 3, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (2, 4, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (3, 1, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values (3, 2, NOW(),NOW());
insert into Follows (followUserId, followerUserId, createdAt, updatedAt) values 
(4, 1, NOW(),NOW()),
(4, 2, NOW(),NOW()),
(4, 3, NOW(),NOW());

 

1. Posts 테이블을 가져와 모든 데이터를 출력한다.

 

SELECT * FROM Posts;

 

2. Users 테이블을 가져와 모든 데이터를 출력한다.

 

SELECT * FROM Users;

 

3. Users와 Posts를 INNER JOIN 하여 userId가 같은 경우만 출력하도록 설정한다.

 

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.title, p.content, p.createdAt
FROM Posts AS p
INNER JOIN Users AS u
on p.userId = u.userId;

 

4. Favorites 테이블에서 userId를 지정해 '좋아요'를 누른 postId만 출력하도록 한다.

 

SELECT postId FROM Favorites WHERE userId=1;

 

5. Favorites 테이블을 CASE WHEN으로 'Y', 'N'을 출력한다.

 

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.title, p.content, 
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
p.createdAt
FROM Posts AS p
JOIN Users AS u
ON p.userId = u.userId;

 

6. Favorites 테이블에서 postId를 지정해 해당하는 '좋아요'의 개수를 출력한다.

 

SELECT COUNT(*) AS favoriteCount
FROM Favorites
WHERE postId=2;

 

7. Posts 테이블에서 reBlog를 지정해 해당하는 게시글의 리블로그 횟수를 출력한다.

SELECT COUNT(*) AS reBlogCount
FROM Posts
WHERE reBlog=2;

 

8. favoriteCount, reBlogCount 값을 더해 reactionCount로 명명하고, Posts.postId 별로 뿌려준다.

 

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.title, p.content,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
  (SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN 'Y' ELSE 'N' END AS favorite,
p.createdAt
FROM Posts AS p
JOIN Users AS u
ON p.userId = u.userId;

 

9. Follows 테이블에서 게시글을 조회하는 userId, Post를 작성한 userId를 입력해 작성한 유저를 팔로우했는지 'Y', 'N'을 출력한다.

 

SELECT COALESCE(MIN('Y'), 'N') AS follow
FROM Follows
WHERE EXISTS (SELECT 1
              FROM  Follows
              WHERE followUserId = 1 AND followerUserId = 2);

 

10. 이전코드와 병합한다.

 

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.title, p.content,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
  (SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N')
  FROM Follows
  WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 AND followerUserId=p.userId)) AS follow,
p.createdAt
FROM Posts AS p
JOIN Users AS u
ON p.userId = u.userId;

 

11. 동일한 img가 존재할 경우 CONCAT_GROUP 함수를 사용해 하나의 레코드로 변환한다.

 

SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
FROM Images
WHERE postId = 1
GROUP BY postId;

 

12. 이전코드와 병합한다.

 

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title,
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
  FROM Images
  WHERE postId = p.postId
  GROUP BY postId) AS img,
p.content,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
  (SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N')
  FROM Follows
  WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 and followerUserId=p.userId)) AS follow,
p.createdAt
FROM Posts AS p
JOIN Users AS u
ON p.userId = u.userId;

 

13. 동일한 tag가 존재할 경우 CONCAT_GROUP 함수를 사용해 하나의 레코드로 변환한다.

 

SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
FROM Tags
WHERE postId = 1
GROUP BY postId;

 

14. 이전코드와 병합한다.

 

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title,
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
  FROM Images
  WHERE postId = p.postId
  GROUP BY postId) AS img,
p.content,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
  FROM Tags
  WHERE postId = p.postId
  GROUP BY postId) AS tag,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
(SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N')
  FROM Follows
  WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 and followerUserId=p.userId)) AS follow,
p.createdAt
FROM Posts AS p
JOIN Users AS u
ON p.userId = u.userId;

 

15. 검색 조건을 추가한다.

 

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title,
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
  FROM Images
  WHERE postId = p.postId
  GROUP BY postId) AS img,
p.content,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
  FROM Tags
  WHERE postId = p.postId
  GROUP BY postId) AS tag,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
(SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N')
  FROM Follows
  WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 and followerUserId=p.userId)) AS follow,
p.createdAt
FROM Posts AS p
JOIN Users AS u
ON p.userId = u.userId
WHERE p.title LIKE '%K K K%' OR p.content LIKE '%K K K%'; 

 

16. postId를 기준으로 내림차순 정렬해 최종 코드 병합을 종료한다.

 

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title,
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
  FROM Images
  WHERE postId = p.postId
  GROUP BY postId) AS img,
p.content,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
  FROM Tags
  WHERE postId = p.postId
  GROUP BY postId) AS tag,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
(SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N')
  FROM Follows
  WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 and followerUserId=p.userId)) AS follow,
p.createdAt
FROM Posts AS p
JOIN Users AS u
ON p.userId = u.userId
WHERE p.title LIKE '%K K K%' OR p.content LIKE '%K K K%'
ORDER BY p.postId DESC;