[Spring boot + Reactjs(ts) + MySQL] 서타몽 프로젝트 (DML)

https://youtu.be/SrxPcd56pYA?si=XW2XNQJQuL1pX0fP

 

현재 공부한 DML 을 정리한것 입니다.

혹시 저와 같이 공부하시는 분이 보게 될 지 모르겠지만 에러가 나면 참고하시면 됩니다

 

-- 회원가입: 새 유저를 user 테이블에 추가

INSERT INTO
user VALUES 
('email@email.com','P!ssw0rd','nuckname','01012345678','부산광역시','롯데백화점',null);

-- 로그인: 이메일을 기준으로 user 테이블에서 유저 정보를 조회

SELECT * FROM user WHERE email = 'email@email.com';

-- 게시물 작성: board 테이블에 새 게시물을 추가

INSERT INTO board (title, content, write_datetime, favorite_count, comment_count, view_count , writer_email) 
VALUES ('제목','내용','2023-08-20 20:54',0,0,0,'email@email.com');

-- 이미지 추가: image 테이블에 이미지 URL을 추가 (board_number 1에 해당하는 이미지)

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

-- 댓글 작성: comment 테이블에 새 댓글을 추가 (board_number 1에 해당하는 댓글)

INSERT INTO comment (content , write_datetime, user_email , board_number)
VALUES ( '반갑' ,'2023-08-20 20:58', 'email@email.com',1);

-- 댓글 수 업데이트: board 테이블에서 board_number 1의 댓글 수를 1 증가

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

-- 좋아요 추가: favorite 테이블에 좋아요를 추가 (email과 board_number 1로 연결)

INSERT INTO favorite VALUES ('email@email.com',1);

-- 좋아요 갯수 업데이트: board 테이블에서 board_number 1의 좋아요 수를 1 증가

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

-- 좋아요 삭제: favorite 테이블에서 특정 유저의 특정 게시물에 대한 좋아요 삭제

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

-- 게시물 수정: board 테이블에서 board_number 1의 제목과 내용을 수정

UPDATE board SET title = '수정 제목입니다.' , content = '수정 내용입니다.' WHERE board_number = 1

-- 이미지 삭제: image 테이블에서 board_number 1에 해당하는 이미지 삭제

DELETE FROM image WHERE board_number =1;

-- 이미지 추가: image 테이블에 새로운 이미지 URL 추가 (board_number 1에 해당)

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

-- 게시물 삭제: 관련된 댓글, 좋아요, 이미지를 먼저 삭제한 후 게시물을 삭제

DELETE FROM comment WHERE board_number = 1;
DELETE FROM favorite WHERE board_number = 1;
DELETE FROM image WHERE board_number = 1;
DELETE FROM board WHERE board_number = 1;

-- 상세 게시물 불러오기: 특정 게시물의 상세 정보를 가져오기 (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;

-- 특정 게시물의 이미지 불러오기: image 테이블에서 board_number 1에 해당하는 이미지 URL 가져오기

SELECT image FROM image WHERE board_number = 1;

-- 특정 게시물의 좋아요한 유저 리스트 불러오기: favorite와 user 테이블을 조인하여 좋아요한 유저 정보 가져오기

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

-- 특정 게시물의 댓글 리스트 불러오기: comment와 user 테이블을 조인하여 댓글 정보 가져오기

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 = 1
ORDER BY write_datetime DESC;

-- 최신 게시물 리스트 불러오기: 최신 순서대로 3개의 게시물 리스트를 가져오기

SELECT * FROM board_list_view ORDER BY write_datetime DESC LIMIT 0, 3;

-- 검색어 리스트 불러오기: 제목이나 내용에 '제목'이 포함된 게시물 리스트를 가져오기

SELECT * FROM board_list_view 
WHERE title LIKE '%제목%' OR content LIKE '%제목%' 
ORDER BY write_datetime DESC;

-- 주간 상위 3 게시물 리스트 불러오기: 특정 기간 동안 좋아요, 댓글, 조회수가 높은 상위 3개의 게시물 리스트를 가져오기

SELECT * 
FROM board_list_view 
WHERE write_datetime BETWEEN '2023-08-14  00:00' AND '2023-08-20 21:48' 
ORDER BY favorite_count DESC, comment_count DESC , view_count DESC , write_datetime DESC 
LIMIT 3;

-- 특정 유저의 게시물 리스트 불러오기: 특정 유저가 작성한 게시물 리스트를 가져오기

SELECT * 
FROM board_list_view 
WHERE writer_email = 'email@email.com' 
ORDER BY write_datetime DESC;

-- 인기 검색어 리스트 (15개): 인기 검색어 상위 15개를 가져오기

SELECT search_word , count(search_word) AS count 
FROM search_log 
WHERE relation IS FALSE 
GROUP BY search_word 
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 = 'url2' 
WHERE email = 'email@email.com';