[MySQL] 관계 DB 실습: 영화 리뷰 서비스 개발

영화 리뷰 서비스 개발

 

실습 0. 다시한번 어떤 테이블과 어떤컬럼을 어떻게 설정해야할지 구상해서 직접 만들어보자

 

실습 1. 하트를 눌러서 즐겨찾기에 저장하는 SQL

 

insert into favorites
(movie_id,user_id)
values
(10,2);

실습 2. 다시 하트를 누르면 즐겨찾기에서 삭제하는 SQL

in:

delete from favorites
where id = 4;

select * from favorites;

out:

 

 

실습 3. 내 즐겨찾기 리스트 가져오는 SQL

 

in:

select m.title as 영화명, count(r.movie_id) as 영화갯수, ifnull(avg(r.ratings),0) as 별점평균
from favorites f
join movies m
on m.id = f.movie_id
left join reviews r
on r.movie_id = m.id
where f.user_id = 1
group by m.title;

out:

 

 

실습 4. 내가 작성한 리뷰 리스트 가져오는 SQL

 

in:

select m.title as 영화이름, r.ratings as 별점
from reviews r
join movies m
on m.id = r.movie_id
where user_id = 45
order by 별점 desc, 영화이름 asc;

out:

 

 

실습 5. 영화제목 검색하여 영화목록을 가져오는 SQL  (검색어: hero)

 

in:

select m.title as 영화명, count(r.movie_id) as 리뷰갯수, ifnull(avg(r.ratings),0) as 별점평균
from movies m
left join reviews r
on m.id = r.movie_id
where m.title like '%hero%'
group by m.title
order by 리뷰갯수 desc, 별점평균 desc;

out:

# like로 검색할 컬럼에 fulltext를 적용시켜서 더 빠르게 검색할수 있도록한다

 

 

 

실습 6. 해당 영화에 대한 리뷰 가져오기

 

in:

select m.title as 영화명, u.username as 유저, r.content as 내용, r.ratings as 별점
from movies m
join reviews r
on m.id = r.movie_id
join users u
on r.user_id = u.id
where r.movie_id = 2
group by u.username;

out:

 

 

실습 7. 영화 상세 정보 가져오기

 

in:

select m.title, m.genre, m.summary, m.year,count(r.movie_id) as reviews, m.attendance, avg(r.ratings) as avg
from movies m
join reviews r
on m.id = r.movie_id
where m.id = 2;

out:

 

 

실습 8. 메인화면 나타내기

 

in:

select m.id,m.title, count(r.movie_id) as count, ifnull(avg(r.ratings),0) as avg,
if(f.user_id is null, 0,1) as favorite
from movies m
left join reviews r
on m.id = r.movie_id
left join favorites f
on m.id = f.movie_id and f.user_id = 2
group by m.id
order by count desc, avg desc
limit 25;

out:

# on에 키를 연결할 뿐만 아니라 원하는 데이터, 특정 유저 id만 가져올수 있게 한다.

# 추가적으로 1일때 검정하트, 0일때 빈 하트가 나오도록 설정해봤다.