SPRING+REACT+MYSQL 프로젝트 (3, 4)

서린이1 2024. 9. 7. 00:19

1. DDL 작성하기 (게시글 조회 VIEW)

CREATE OR REPLACE VIEW board_list_view AS
SELECT
    B.board_number AS board_number
  , B.title AS title
  , B.content AS content
  , I.image AS image
  , B.favorite_count AS favorite_count
  , B.comment_count AS comment_count
  , B.view_count AS view_count
  , B.write_datetime AS write_datetime
  , B.writer_email AS writer_email
  , U.nickname AS writer_nickname
  , U.profile_image AS writer_profile_image
FROM board AS B
INNER JOIN user AS U
ON B.writer_email = U.email
LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
ON B.board_number = I.board_number;

2. API 기반 DML 작성하기

-- Active: 1725368211487@@localhost@3306@board
-- 회원가입
INSERT INTO user VALUES ('email@email.com', 'P!ssw0rd', 'nickname', '01012345678', '서울특별시 종로구', '그랑서울', null);

-- 로그인
SELECT * FROM user WHERE email = 'email@email.com';

-- 게시물 작성
INSERT INTO board (title, content, write_datetime, favorite_count, comment_count, view_count, writer_email)
VALUES ('제목', '내용', '2024-09-06 23:25:54', 0, 0, 0, 'email@email.com');

INSERT INTO image
VALUES (2, 'url');

-- 댓글 작성
INSERT INTO comment (content, write_datetime, user_email, board_number)
VALUES ('반갑습니다', '2024-09-06 23:28:50', 'email@email.com', 3);

UPDATE board
SET comment_count = comment_count + 1
WHERE board_number = 2;

-- 좋아요 등록, 취소
INSERT INTO favorite
VALUES ('email@email.com', 2);

UPDATE board
SET favorite_count = favorite_count + 1
WHERE board_number = 2;

DELETE FROM favorite
WHERE user_email = 'email@email.com'
AND board_number = 2;

UPDATE board
SET favorite_count = favorite_count - 1
WHERE board_number = 2;

-- 게시물 수정
UPDATE board
SET title = '수정 제목'
  , content = '수정 내용'
WHERE board_number = 2;

DELETE FROM image
WHERE board_number = 2;

INSERT INTO image
VALUES (2, 'url');

INSERT INTO image
VALUES (3, 'url3');

-- 게시물 삭제
DELETE FROM comment WHERE board_number = 2;
DELETE FROM favorite WHERE board_number = 2;
DELETE FROM image WHERE board_number = 2;
DELETE FROM board WHERE board_number = 2;

-- 상세 게시물 불러오기
SELECT
    B.board_number AS board_number
  , B.title AS title
  , B.content AS content
  , B.write_datetime AS write_datetime
  , B.writer_email AS writer_email
  , U.nickname AS nickname
  , U.profile_image AS profile_image
FROM board AS B
INNER JOIN user AS U
ON B.writer_email = U.email
WHERE board_number = 2;

SELECT image
FROM image
WHERE board_number = 2;

SELECT
    U.email AS email
  , U.nickname AS nickname
  , U.profile_image AS profile_image
FROM favorite AS F
INNER JOIN user AS U
ON F.user_email = U.email
WHERE F.board_number = 2;

SELECT
    U.nickname AS nickname
  , U.profile_image AS profile_image
  , C.write_datetime AS write_datetime
  , C.content AS content
FROM comment AS C
INNER JOIN user AS U
ON C.user_email = U.email
WHERE C.board_number = 2
ORDER BY write_datetime DESC;

-- 최신 게시물 리스트 불러오기

SELECT *
FROM board AS B
INNER JOIN user AS U
ON B.writer_email = U.email
LEFT JOIN image AS I
ON B.board_number = I.board_number;

-- 최신 게시물 리스트 불러오기 (image row 중복 제거)
-- SELECT
--     B.board_number AS board_number
--   , B.title AS title
--   , B.content AS content
--   , I.image AS image
--   , B.favorite_count AS favorite_count
--   , B.comment_count AS comment_count
--   , B.view_count AS view_count
--   , B.write_datetime AS write_datetime
--   , U.nickname AS writer_nickname
--   , U.profile_image AS writer_profile_image
-- FROM board AS B
-- INNER JOIN user AS U
-- ON B.writer_email = U.email
-- LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
-- ON B.board_number = I.board_number
SELECT * FROM board_list_view
ORDER BY write_datetime DESC
-- LIMIT 0, 5; -- (페이지 - 1) * 5
LIMIT 5, 5;

-- 검색어 리스트
-- SELECT
--     B.board_number AS board_number
--   , B.title AS title
--   , B.content AS content
--   , I.image AS image
--   , B.favorite_count AS favorite_count
--   , B.comment_count AS comment_count
--   , B.view_count AS view_count
--   , B.write_datetime AS write_datetime
--   , U.nickname AS writer_nickname
--   , U.profile_image AS writer_profile_image
-- FROM board AS B
-- INNER JOIN user AS U
-- ON B.writer_email = U.email
-- LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
-- ON B.board_number = I.board_number
SELECT * FROM board_list_view
WHERE title LIKE '%수정%' OR content LIKE '%수정%'
ORDER BY write_datetime DESC;

-- 검색어 주간 상위 TOP 3 불러오기
-- SELECT
--     B.board_number AS board_number
--   , B.title AS title
--   , B.content AS content
--   , I.image AS image
--   , B.favorite_count AS favorite_count
--   , B.comment_count AS comment_count
--   , B.view_count AS view_count
--   , B.write_datetime AS write_datetime
--   , U.nickname AS writer_nickname
--   , U.profile_image AS writer_profile_image
-- FROM board AS B
-- INNER JOIN user AS U
-- ON B.writer_email = U.email
-- LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
-- ON B.board_number = I.board_number
SELECT * FROM board_list_view
WHERE write_datetime BETWEEN '2024-09-02 00:00:00' AND '2024-09-08 23:59:59'
ORDER BY favorite_count DESC, comment_count DESC, view_count DESC, write_datetime DESC
LIMIT 3; -- 3개만

-- 특정 유저 게시물 리스트 불러오기
-- SELECT
--     B.board_number AS board_number
--   , B.title AS title
--   , B.content AS content
--   , I.image AS image
--   , B.favorite_count AS favorite_count
--   , B.comment_count AS comment_count
--   , B.view_count AS view_count
--   , B.write_datetime AS write_datetime
--   , U.nickname AS writer_nickname
--   , U.profile_image AS writer_profile_image
-- FROM board AS B
-- INNER JOIN user AS U
-- ON B.writer_email = U.email
-- LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
-- ON B.board_number = I.board_number
SELECT * FROM board_list_view
WHERE writer_email = 'email@email.com'
ORDER BY write_datetime DESC, board_number DESC;

-- 인기 검색어 리스트
SELECT search_word, count(search_word) AS count -- search_word를 기준으로 count를 친다.
FROM search_log
WHERE relation IS FALSE -- 관계형이 아닌 것
GROUP BY search_word;   -- search_word로 group by 해준다.
ORDER BY count DESC
LIMIT 15;

-- 관련 검색어 리스트
SELECT relation_word, count(relation_word) AS count
FROM search_log
WHERE search_word = '검색어'
GROUP BY relation_word
ORDER BY count DESC
LIMIT 15;

-- 유저 정보 불러오기 / 로그인 유저 정보 불러오기
SELECT *
FROM user
WHERE email = 'email@email.com';

-- 닉네임 수정하기
UPDATE user
SET nickname = '수정 닉네임'
WHERE email = 'email@email.com';

-- 프로필 이미지 수정
UPDATE user
SET profile_image = 'new_profile_url'
WHERE email = 'email@email.com';

출처 : https://www.youtube.com/@jiraynorprogramming1589