[MySQL] 영화리뷰 실습

use movie;

-- 2 

-- 회원가입 SQL
insert into user (email, password, nickname, gender)
values ('qqq@gmail.com','1331', '김나나', 0 );

-- 로그인 할때 필요한 SQL
select * 
from user
where email = 'abc@naver.com' and password = '1234';

select * from user;
select * from review;
select * from movie;
select * from history;
select * from favorite;

-- 리뷰 등록하는 SQL
select * from review;
insert into review (movie_id, user_id, content, rating)
values (4, 1, '굳굳굳', 5);

-- 영화 등록하는 SQL
select * from movie;
insert into movie
(movie_name, summary, image_url, date)
values 
('스파이더맨', '영화 <스파이더맨: 노 웨이 홈>은 정체가 탄로난 스파이더맨', 'https://movie.naver.com/movie/bi/mi/photoViewPopup.naver?movieCode=208077', '2021-12-15'), 
('라라와 크리스마스 요정', '전 크리스마스가 참 좋아요.
 이번 크리스마스는 아빠와 함께 캐틀힐 농장에서 보내게 돼서 더 신나요.', 'https://movie.naver.com/movie/bi/mi/photoViewPopup.naver?movieCode=212489', '2021-12-15'),
 ('아멜리에' , '금요일 저녁, 혼자서 영화 보는 것을 즐기는 아멜리', 'https://movie.naver.com/movie/bi/mi/photoViewPopup.naver?movieCode=32588', '2001-10-19'),
 ('피부를 판 남자', '자유, 돈, 명예를 원한 계약서에 서명한다.', 'https://movie.naver.com/movie/bi/mi/photoViewPopup.naver?movieCode=196557', '2021-12-16'),
 ('매트릭스: 리저렉션', '매트릭스', 'https://movie.naver.com/movie/bi/mi/photoViewPopup.naver?movieCode=191600', '2021-12-22');

-- 즐겨찾기 등록하는 SQL
select * from favorite;
insert into favorite
(movie_id, user_id)
values
(1, 2);

-- 히스토리 등록하는 SQL
select * from history;
insert into history
(user_id, word)
values
(1, '스파이더맨');

-- 3 

 

-- 메인화면
-- 영화명, 리뷰개수, 별점평균
-- 25개씩
-- 내가 푼 정답
select m.movie_name, count(review), avg(rating)
from review r
join movie m 
    on r.movie_id = m.id
group by m.movie_name
order by count(review) desc
order by avg(rating) desc
limit 25;

-- 정답
select m.id, m.movie_name, count(r.user_id) as review_cnt, ifnull(avg(r.rating), 0) as avg_rating, f.user_id
from movie m
left join review r
    on m.id = r.movie_id
left join favorite f
    on m.id = f.movie_id and f.user_id = 1
group by m.id
order by review_cnt desc -- 리뷰개수로 정렬 oder by avg_rating desc
limit 0, 25;

-- 4 

 

-- 영화 상세 정보 화면

-- 관객수 업데이트 sql
select * from movie;

update movie
set count = 153920
where id= 3;

-- 영화 상세정보 가져오는 sql
select m.id, m.movie_name, m.summary, m.image_url, m.date, m.count, ifnull(avg(r.rating), 0) as avg_rating, count(r.user_id) as review_cnt
from movie m
left join review r -- left는 왼쪽 m에 r를 오른쪽에 붙여라. 데이터가 없어도 null로 처리되어 전체가 나옴. 
    on m.id = r.movie_id
where m.id = 4;


-- 5

-- 영화 리뷰 가져오는 SQL
-- content rating 25개
-- 내가 푼 정답
select r.content, r.rating
from review r
join movie m 
    on r.movie_id = m.id
limit 25;

-- 정답
select r.id, u.nickname, r.content, r.rating, r.created_at, r.updated_at
from review r
join user u 
    on r.user_id = u.id
where movie_id = 4
order by r.created_at desc
limit 25;

-- 6

-- 검색결과
-- 영화명, count(review), avg(rating)
-- 내가 푼 정답
select movie_name, count(review), avg(rating)
from history h
join movie m
    on h.id = m.id
join review r
    on h.id = r.movie.id;

-- 정답    
select m.id, m.movie_name, count(r.user_id) as review_cnt, ifnull(avg(r.rating) , 0) as avg_rating
from movie m
left join review r
    on m.id = r.movie_id
where m.movie_name like '%영화%' or m.summary like '%영화%'
group by m.id
limit 0, 25;
-- 중요한 포인트, like를 통해 검색하는 부분이 있으면, 해당 컬럼을 fulltext 인덱스로 만들어줘야 한다. 
    
-- 7 == > 3번 메인화면문제로
-- 리뷰 작성화면, 별점을 매기고, 내용작성 후 저장버튼
-- 정답
select movie_name, count(review), avg(rating)
from history h
join movie m
    on h.id = m.id
join review r
    on h.id = r.movie.id;

-- 8

-- 내 정보 화면 email, nickname, gender, movie_name, review
-- 내가 푼 정답
select u.email, u.nickname, u.gender, m.movie_name, r.content 
from review r
join user u
    on r.user_id = u.id
join movie m
    on r.movie_id = m.id;
 
-- 정답 - 따로따로 가져와야 한다.
-- 내 정보
select u.id, u.email, u.nickname, u.gender
from user u
where id=1; 

-- 내 리뷰리스트
select m.movie_name, r.rating, r.content 
from review r
join movie m
    on r.movie_id = m.id
where r.user_id = 1
order by r.created_at desc;

-- 9

-- 즐겨찾기 기능 movie_name, count(review), avg(별점)  
-- 내가 푼 정답
select m.movie_name, r.count(review), r.avg(별점)  
from favorite f
join movie m
    on f.movie_id = m.id and f.user_id = 1 
join user u
    on f.user_id = u.id
join review r
    on f.movie_id = r.movie_id;
 
-- 정답 : 내가 한 즐겨찾기
select f.id, m.movie_name, avg(r.rating) as avg_rating, f.user_id
from favorite f
join movie m
    on f.movie_id = m.id and f.user_id = 1 
left join review r
    on f.movie_id = r.movie_id
group by m.id
limit 0, 25;