MySQL 영화 리뷰 서비스 개발

 

 

 

use movie_test_db;

-- 1. 테이블 만들기 

-- 2. 더미 데이터 insert 했다고 가정하고 진행한다.

-- 3. 화면 기획서를 보고, 필요한 slq문을 작성.
--    중요! 순서 : 단일 테이블 처리 가능한 것부터 작성하고
--    메인 화면처럼, 여러 테이블 조인하는 것은 맨 나중에 작성한다.

-- 회원가입
insert into user ( email, password, nickname, gender)
values ( 'aksl@naver', '1234', '김닉네임', 1);

-- 영화명을 탭하면 리뷰 작성 화면
-- 100번 영화의 10번유저 리뷰 예시
insert into review ( movie_id, user_id,  content, rating)
values ( 100, 10 , "ㅋㅋ", 4);

-- 특정 영화의 리뷰 가져오는 sql
-- 영화 상세정보에서 이미 받은 타이틀 정보는 재활용 하는경우로 가정.
-- 리뷰 수정을 위해 보이진않게 하지만 r.id 정보를 보내준다.
select r.id , u.nickname, r.content, r.rating, r.created_at
from review r
join user u 
on r.user_id = u.id
where r.movie_id = 10
order by r.created_at desc
limit 0, 25 ;

-- 내정보 화면에서, 내 리뷰 리스트 가져오는 sql
select r.id, m.title, r.rating, r.created_at
from review r 
join movie m
on r.movie_id = m.id
where user_id = 1
order by r.created_at desc;

--  영화 상세 정보화면 ( 리뷰가 없을수도 있으니 left 조인을 해서 무비정보는 살려야한다.) date(m.year) 해서 시분초를 빼주면 더좋다.?
select m.*, ifnull(avg(r.rating) , 0 ) as avg_rating, ifnull( count(r.id), 0 ) as rivew_count
from movie m
left join review r
on m.id = r.movie_id
where m.id = 10 ;

-- 검색에 입력한 글자 저장 (히스토리)
insert into history ( user_id, word)
values (7 ,"mon");

-- 검색어가 포함된 영화의 목록을 가져오는 SQL
select m.title, 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
group by m.id having title like "%h%";

-- 즐겨찾기 설정하는 SQL
insert into favorite (  movie_id, user_id)
values ( 5, 10);
-- 즐겨찾기 해제하는 SQL 
delete from favorite
where id = 5;

-- 즐겨찾기 설정된 영화목록을 가져오는 SQL f.id는 클라에서 관리하기위해 , m.id는 영화를 누르면 상세정보로 이동하기 위해 포함시킨다.
-- 클라에 보내줄때 이름을 알아보기 쉽게 변경해주어야한다.
select f.id as favorite_id, 
		m.title, 
		count(r.id) as review_cnt,
        ifnull( avg(r.rating), 0 )as rating_avg, 
		m.id as movie_id
from favorite f
join movie m
on f.movie_id = m.id
left join review r
on f.movie_id = r.movie_id
join user u
on f.user_id = u.id
where f.user_id = 10
group by f.movie_id
order by f.created_at desc;

-- 메인 화면구성 ( 리뷰갯수정렬 )
select m.id,
		m.title, 
		count(r.id) as review_cnt,
		avg(r.rating) as avg_rating, 
        if( f.id is null, 0 ,  1) as is_my_favorite
from movie m
left join review r 
on r.movie_id = m.id
left join favorite f
on m.id = f.movie_id and f.user_id = 10
group by m.id 
order by count(r.id) desc
limit 0, 25;

-- 메인 화면구성 ( 별점 평균 정렬)
select m.id,
		m.title, 
		count(r.id) as review_cnt,
		avg(r.rating) as avg_rating, 
        if( f.id is null, 0 ,  1) as is_my_favorite
from movie m
left join review r 
on r.movie_id = m.id
left join favorite f
on m.id = f.movie_id and f.user_id = 10
group by m.id 
order by avg_rating desc
limit 0, 25;