[MySQL] 화면 기획서보고 필요한 SQL문 만들기 _영화 리뷰 서비스

화면 기획서보고 필요한 SQL문 만들기

 

: 영화 리뷰 서비스 관련된 앱의 화면 기획서를 보고 SQL문 작성하기

 

[ SQL문 만들기 ]

화면 기획서를 보고 테이블 생성
> 테스트 데이터 인서트
> 화면에 필요한 SQL문 작성 (클라이언트에게 전달할 데이터 가공)
    1) 작성 순서 : 단일 테이블 처리 가능한 것부터 작성하고
                          메인 화면처럼 여러 테이블 조인하는 것은 맨 나중에 작성한다.
    2) 개발자들이 데이터 찾는 것에 문제가 없도록 해당데이터 ID는 항상 같이 넣어준다.
    3) 데이터가 많을 경우 페이징 처리한다.
         limit 키워드를 사용해서 페이징 처리를 한 SQL문을 만든다.
    4) 서버 개발자는 데이터를 가공하지 않고 추출해서만 만든다.

 

내가 개발해야 하는 화면들

회원가입 화면
-- 이메일 비밀번호 유저이름 성별

메인화면
-- 영화이름, 리뷰 갯수, 별점 평균, 즐겨찾기 기능

상세 정보
-- 제목 내용 등 영화 상세 정보, 별점 평균, 별점 개수

리뷰
-- 유저 닉네임, 리뷰, 별점

무비 검색 화면 (검색에 입력한 글자저장)
-- 검색 결과의 영화명, 리뷰 개수, 별점 평균

리뷰 작성 화면
-- 리뷰 테이블 CRUD에 C에 해당하는 화면 단독 리뷰 테이블의 들어간다
-- 개발 순서에 최우선 순서!!

내정보
-- 유저정보, 내 리뷰 리스트의 영화이름, 별점

즐겨찾기
-- 메인화면에 종속되어 있다.
-- 우선순위가 높다!
-- 영화명, 리뷰 개수, 별점 평균

 

테이블 생성

1. 회원가입 화면에서 받아온 정보를 저장할 user 테이블 생성

   : id, 이메일(UQ 값으로 지정), 비밀번호, 닉네임, 가입날짜( Timestamp로 디폴트값 now() 설정 ) 컬럼 생성

user

2. 영화 정보를 저장할 movie 테이블 생성

   : id, 제목, 내용요약, 장르, 관객수, 개봉일( 시간정보는 없어서 DATE로 지정 )

movie

3. 영화의 리뷰 정보를 저장할 review 테이블 생성

   : id, 등록된 영화 id( Foreign Key ), 등록된 유저 id ( Foreign Key ), 별점점수, 리뷰내용,

     생성날짜( Timestamp로 디폴트값 now() 설정  ),

     업데이트생성날짜( Timestamp로 디폴트값 now() on update now() 설정 )

 

 

4. 유저가 즐겨찾기 한 항목을 저장할 favorite 테이블 생성

   : id, 등록된 영화 id( Foreign Key ), 등록된 유저 id ( Foreign Key ), 생성날짜( Timestamp로 디폴트값 now() 설정  )

favorite

   : 좋아요한 항목을 나타내는 등록된 영화 id와 등록된 유저 id는 두 개를 동시에 유니크 값으로 묶어준다.(인덱스탭에서 직접 설정)

favorite indexes

 

 

테이블이 완성되었으면 우선순위에 따라 SQL문은 만들자.

테스트/활용

-- 0. 회원가입하는 SQl
select *
from user;

insert into user
(email, password, nickname, gender)
values
('abc@naver.com','1234','홍길동','1');
    
-- 1. 하트 눌러서, 즐겨찾기에 저장하는 SQL
select *
from favorite;

insert into favorite
(movieId, userId)
values
(850, 159);

delete from favorite
where id = 5;

-- 2. 다시 하트 누르면, 즐겨찾기에서 삭제하는 SQL
select *
from favorite;

delete from favorite
where movieId = 10 and userId = 301 ;

-- 3. 내 즐겨찾기 리스트 가져오는 SQL
select m.title , concat( count(r.id), '개') review_cnt, ifnull( avg( r.rating ), 0) avg_rating
from favorite f
left join movie m
	on f.movieId = m.id
left join review r
	on m.id = r.movieId
where f.userId = 301
group by m.id
order by f.createdAt desc
limit 0, 25;

-- 4. 리뷰 저장 / 수정 / 삭제 하는 SQL
select *
from review ; 
-- 리뷰 작성하기 _저장
insert into review
(movieId, userId, rating, content)
values
(512, 301, 1, '그냥 지루하다');
-- 수정
update review
set content = '별로다', rating = 1
where movieId = 100 and userId = 301;
-- 삭제
delete from review
where movieId = 100 and userId = 301;

-- 4. 내가 작성한 리뷰 리스트 가져오는 SQL
select r.id, m.title, r.rating
from review r
join movie m
	on r.movieId = m.id
where userId = 301
order by r.createdAt desc
limit 0,25;

-- 5. 영화제목 검색하여, 영화 목록 가져오는 SQL
select m.id , m.title, 
	count(r.id) review_cnt, 
    ifnull( avg( r.rating ), 0) rating_avg
from movie m
left join review r
	on m.id = r.movieId
where title like '%story%' or summary like '%story%'
group by m.id
limit 0, 25;

-- 6. 해당 영화에 대한 리뷰 리스트 가져오는 SQL
-- limit 페이징처리 하고 한다.
select m.id, m.title, u.nickname, r.content, r.rating  
from review r
join user u
	on r.userId = u.id
join movie m
	on r.movieId = m.id
where r.movieId = 2
order by r.createdAt desc
limit 0, 25;

-- 7. 영화 상세 정보 가져오는 SQL
select m.*, 
        avg( r.rating ) avg_rating, count( r.id ) review_cnt
from movie m
join review r
	on m.id = r.movieId
where m.id = 20;

-- 8. 메인페이지의 영화 리스트 가져오는 SQL
-- 내가 즐겨찾기 한 정보까지 있어야 함
-- 리뷰갯수정렬
select m.id , m.title, count( r.id ) review_cnt, 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.movieId and f.userId = 301
group by m.id
order by review_cnt desc
limit 0, 25;