MySQL 영화 리뷰 서비스 개발 (2) SQL문 작성

-- 회원가입
insert into user (email,password,nickname,gender)
values('abc@naver.com','1234abcd','홍길동', 1) ;

# 인설트를 이용해 작성해준다

 

 

 

 

 

-- 리뷰 작성
insert into review (movie_id,user_id,content,rating)
values (100,4,'재밌어요', 4);

# 인설트를 이용해 작성해준다

 

 

 

 

-- 특정 영화의 리뷰 가져오기 (r.id 를 보내주는 이유는 수정을 위해서)
select r.id, u.nickname, r.content, r.rating, r.created_at
from review r
join user u
on u.id = r.user_id
where movie_id = 10
order by r.created_at desc
limit 0, 25;

# 해당 영화를 누르면 리뷰를 가져오기 위해 SQL 을 작성한다

화면에서 필요한 유저이름, 내용, 별점을 나타내기위해 셀렉트에 쓰고

이것을 가져오기 위해 리뷰와 유저를 조인한다

그리고 해당 영화를 지정해야되기때문에 where를 사용해서 무비 아이디를 지정해주고

최신순부터 정렬하기위해 오더바이로 내림차순해준다

이 후 리미트로 25개까지 가져오게 한다.

 

 

 

 

-- 내정보 화면에서, 내 리뷰 리스트 가져오기
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 ;

# 리뷰 리스트에서 필요한 영화이름 별점을 가져오기 위해 셀렉트에 써준다

각각의 컬럼이 들어있는 리뷰와 무비 테이블을 조인해준다

나라는것을 지정하기위해 where 를 써서 user_id 를 지정해준다

오더바이로 최신순으로 정렬해준다

 

 

 

 

 

-- 영화 상세 정보 화면
select ifnull(count(user_id), 0) as review_count ,
       ifnull(avg(rating), 0) as rating_avg ,
	   m.title ,m.genre, m.image_url ,
       date(m.year),m.attendance
from movie m
left join review r
on r.movie_id = m.id
where movie_id = 10 ;

# 셀렉트에 영화제목, 사진, 줄거리, 개봉일, 관객수, 별점평균

메긴사람의 수를 표시하기 위해

각각 해당하는 컬럼을 넣어준다

해당 컬럼을 가지고 있는 무비와 리뷰를 조인해준다

특정 영화를 지정하기위해 where 로 movie_id를 지정해준다

 

 

 

 

 

-- 검색어를 저장하는 SQL
insert into history(user_id,word)
values (7,'mon') ;

# 검색어를 저장하기 위해 SQL 를 만들어준다

인설트를 이용해 히스토리 테이블에 저장해준다.

 

 

 

-- 검색어가 포함된 영화의 목록을 가져오는 SQL
select m.title,count(r.user_id) as review_cnt ,
       ifnull(avg(r.rating), 0 ) as rating_avg
from movie m
left join review r
on m.id = r.movie_id
where title like '%mon%' 
group by m.id ;

# 이제 검색어가 포함된 영화의 목록을 가져오기 위해 SQL 을 만든다

화면에 표시 될 컬럼 영화 제목, 리뷰갯수, 별점평균에 해당하는 컬럼을 셀렉트에 넣는다

이 컬럼을 가진 무비와 리뷰를 조인한다

where 절로 특정 단어를 포함하는 조건을 지정한다

이것을 모두 영화 아이디로 묶는다

 

 

 

 

-- 즐겨찾기 설정하는 SQL
insert into favorite (movie_id , user_id)
values (7, 4);

-- 즐겨찾기 해제하는 SQL
delete from favorite
where id = 1;

-- 즐겨찾기 설정된 영화목록 가져오는 SQL
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 
where f.user_id = 4
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 rating_avg ,
	   if( f.id is null , 0 , 1 ) as is_my_favorite
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 = 4
group by m.id
order by review_cnt desc 
limit 0, 25;

# 마지막으로 메인화면을 만든다

화면에 표시될 영화명 리뷰갯수 별점평균 즐겨찾기를 가져오기 위해

해당 컬럼이 있는 테이블을 조인해준다

그 후 내 즐겨찾기를 가져오기 위해 아이디를 내 아이디로 설정해준다

그리고 영화 아이디로 그룹바이를 한 뒤 

리뷰 갯수 순으로 정렬해준다 

별점 평균 갯수로 정렬하려면 오더바이 조건만 바꾸면 된다.

 

모두다 만든 뒤에 foreign Keys 설정을 해준다.