17. MySQL 화면 기획서 보고 테이블 만들어보기

-- 회원가입에 필요한 SQL

INSERT INTO user(email,password,nickname,gender)
values('abc@naver.com','1234','홍길동',1);

-- 리뷰 작성 화면

INSERT INTO review(movieId,userId,rating,content)
values(1,301,5,'너무 재미있어요.');

 

-- 즐겨찾기 하는 SQL

 

-- 누가 , 어떤 영화를 ,언제 즐겨찾기 했냐 저장

-- 유저가 어떤 영화의 즐겨찾기(하트모양)을 누르면

-- 누가, 어떤 영화를, 언제 즐겨찾기 했는지 저장해야 한다.

-- 따라서 테이블을 하나 만듭니다.

-- favorit

-- - id

-- - user_id

-- - movie_id

-- - created_at : 디폴트 now()

 

-- 나(301)가 영화 아이디 1번을 즐겨찾기 하는 경우

INSERT INTO favorite(user_id,movie_id)
values (301,1);
INSERT INTO favorite(user_id,movie_id)
values (301,2);
INSERT INTO favorite(user_id,movie_id)
values (301,10);
INSERT INTO favorite(user_id,movie_id)
values (301,34);
INSERT INTO favorite(user_id,movie_id)
values (301,64);
INSERT INTO favorite(user_id,movie_id)
values (1,44);
INSERT INTO favorite(user_id,movie_id)
values (1,22);

 

-- 즐겨찾기 해제 하는 SQL문

DELETE FROM favorite
WHERE user_id = 301 AND movie_id = 1;

 

-- 내 즐겨찾기 목록 가져오기

SELECT m.id as movie_id,m.title,
count(r.id) as cnt_review,
IFNULL(AVG(r.rating),0) as avg_rating
FROM favorite f
join movie m
on f.movie_id = m.id
LEFT join review r
on m.id = r.movieId
WHERE user_id = 301
GROUP BY m.id;

 

-- 영화 상세페이지 화면에 사용할 SQL

-- 영화 아이디 2번에 대한 상세 정보를 가져온다.

SELECT m.*,
COUNT(r.id) as cnt_review,
IFNULL( AVG(r.rating),0) as avg_rating 
FROM movie m
left join review r
on m.id = r.movieId
WHERE m.id = 2;

-- 특정 영화에 대한 리뷰 리스트 보여준 화면에 SQL

-- 가정: 영화 아이디 2번에 대한 리뷰 가져오기

SELECT r.id as review_id,u.nickname,r.content,r.rating
FROM review r
join user u
on r.userId = u.id
WHERE r.movieId = 2
order by r.created_at DESC ;

-- 메인화면 SQL

-- 1. 영화명,리뷰갯수,별점평균 정보를 보여준다.

-- 2. 한번 요총시마다 25개씩 불러오도록 만든다.

-- 3. 정렬 조건이 있다.

-- 리뷰갯수 내림차순 정렬

-- 별점 평균 내림차순 정렬

-- 4.하트는 즐겨찾기다.

-- 내가 하트를 누르면, 내 즐겨찾기에 저장된다.

SELECT m.id as movie_id,m.title,COUNT(r.id) cnt_review,
IFNULL( AVG(r.rating),0) avg_rating ,
IF (f.id is null,0,1) is_favorite
FROM movie m
left join review r
on m.id = r.movieId
left JOIN favorite f
on m.id = f.movie_id and f.user_id = 301
group BY m.id
order BY cnt_review DESC 
LIMIT 0,20;

 

-- 검색화면 SQL

SELECT m.id as movie_id,m.title,
COUNT(r.id) as cnt_review,
IFNULL( AVG(r.rating),0) as avg_rating 
FROM movie m
left join review r
on m.id = r.movieId
WHERE m.title LIKE '%big%'
group by m.id;

 

-- 히스토리를 저장하기 위해서

-- history

-- 1.id

-- 2.user_id

-- 3. keword

-- 4. created_at

-- 가정 : 내(유저 아이디 301)가 검색한 검색어를 저장

INSERT INTO history(user_id,keyword)
values (301,'er');

INSERT INTO history(user_id,keyword)
values (301,'big');

 

-- 검색 기록을 보여주기 위한 SQL

SELECT *
FROM history
WHERE user_id = 301
order by created_at DESC

 

-- 히스토리 삭제 SQL

DELETE FROM history
WHERE id = 2 AND  user_id = 301;

 

-- 내정보 화면

-- 오른쪽 화면처럼 정보를 가져오는 API 개발

-- 내 리뷰 리스트는 전부 다 가져오도록 한다.

 

-- 가정 : 내 아이디는 301 이다.

SELECT id as user_id,email,nickname,gender
FROM user
WHERE id = 301;

 

-- 가정 : 내아이디는 301이고, 내 리뷰 리스트 가져온다.

SELECT r.id as review_id,m.title,r.rating
FROM review r
join movie m
on r.movieId = m.id
WHERE userId = 301;