[DBeaver-MySQL] : (스키마 설계) 영화 리뷰 서비스 개발 실습

 

 

 

 

아래 화면을 보고 SQL를 짜보자.

- SQL를 짜기 전에 데이터베이스와 테이블을 만들어주자.

  Database : movie_db 

  Tables는 위 화면을 보고 어떤 테이블이 필요한지 하나씩 봐보자.

 

먼저 리뷰를 작성하려면 사용자 정보가 필요하니까

user 테이블을 만들어준다.

 

-- 서비스 회원가입 

tables > user > columms   id : int <primary key, auto increment> 아이디가 중복되지 않게하고 자동으로 추가한다.   email : varchar(100) <unique key>  이메일이 중복되지 않게 한다.   password : varchar(100)   nickname : varchar(100)   gender  : int 성별은 0과 1로 나타낼거기 때문에 int(정수) 로 한다.

 

 

 

-- 회원가입하기

INSERT into user(email, password, nickname, gender)
values ('abc@naver.com', '1234', '홍길동', 1);

 

 

 

이제 사용자가 회원가입을 했으니 리뷰를 남겨야하는데, 리뷰를 남기기 위해선 영화 항목이 필요하다.

movie 테이블을 만들어준다.

tables > movie > columms   id : int <primary key, auto increment> 아이디가 중복되지 않게하고 자동으로 추가한다.   title : varchar(200)   summary : varchar(500)   genre : varchar(200)   attendance : int   year : date

 

-- movie 데이터 추가

movie_data.sql

0.23MB

 

 

 

이제 리뷰 작성 테이블도 만들어준다.

tables > review > columms   id : int <primary key, auto increment> 아이디가 중복되지 않게하고 자동으로 추가한다.   movieId  : int <Foreign Keys> movie 테이블에 id   userId : int <Foreign Keys> user 테이블에 id   rating : varchar(200)   content : varchar(500)   created_at : timestamp

 

-- 리뷰 작성 화면

INSERT into review(movieId, userId, rating, content)
values (1, 301, 5, '너무 재밌습니다!');

 

 

 

 

 

 

 

 

 

 

 

-- 리뷰 수정 / 삭제 예시

# 수정
update review
set rating = 3, content = '생각해보니 조금 아쉽네요'
where userId = 1 and id = 1;

delete 
from review
where id = 1;

 

 

 

-- 유저가 어떤 영화의 즐겨찾기(♥)를 누르면

-- 누가 어떤영화를 언제 즐겨찾기 했는지 저장해야 한다.

-- 따라서 테이블을 하나 만들어야된다.

favorites 테이블 생성

tables > favorites  > columms   id : int <primary key, auto increment> 아이디가 중복되지 않게하고 자동으로 추가한다.   user_id : int <Foreign Keys> user 테이블에 id   movie_id : int <Foreign Keys> movie 테이블에 id   created_at : timestamp

 

-- 즐겨찾기 하는 SQL

-- 나(301)가 영화아이디 1번을 즐겨찾기 하는 경우

insert into favorites(user_id, movie_id)
values (301, 1);

 

 

-- 즐겨찾기 해제하는 SQL

-- 나(301)가 영화아이디 1번을 즐겨찾기 해제하는 경우

DELETE FROM favorites
WHERE user_id = 301 and movie_id = 1;

 

 

 

 

-- 내 즐겨찾기 목록 가져오기

SELECT m.id as movie_id, m.title, COUNT(r.id) as cnt_review, 
IFNULL(AVG(r.rating), 0) as avg_rating
from favorites f
join movie m
on f.movie_id = m.id
left join review r
on m.id = r.movieId
WHERE user_id = 301
group by m.id;

 

 

 

 

 

 

 

-- 영화 상세페이지 화면

-- 영화 아이디 2번에 대한 상세 정보를 가져온다.

-- 리뷰는 있을 수도 있고 없을 수도 있어서 left join을 써준다.

SELECT m.*, COUNT(r.id) as cnt_review, IFNULL(AVG(r.rating), 0) as avg_rating
from movie m
left join review r
on m.id = r.movieId
WHERE m.id = 2;

 

 

 

 

 

 

 

 

-- 특정 영화에 대한 리뷰 리스트 보여준 화면에 대한 SQL

-- 가정 : 영화 아이디 2번에 대한 리뷰 가져오기.

SELECT r.id as review_id, u.nickname, r.content, r.rating
from review r
join user u
on r.userId = u.id

where movieId = 2
order by r.created_at desc;

 

 

 

 

 

 

 

 

 

-- 메인 화면

-- 즐겨찾기 했냐 ture = 1, 안했냐 flase = 0

-- 라는 컬럼을 또 만들어줘야됨.

SELECT m.id, m.title, COUNT(r.id) as cnt_review, IFNULL(AVG(r.rating), 0) as avg_rating,
if(f.id is null, 0, 1) as is_favorites
from movie m
left join review r
on m.id = r.movieId
left join favorites f
on f.movie_id = m.id and f.user_id = 301
group by m.id;

 

 

 

 

 

 

 

-- 영화 검색 화면

-- 가정 : 유저가 검색어로 big이라고 입력하면, 제목에 big가 포함된 영화를 가져온다.

SELECT m.id as movie_id, m.title, COUNT(r.id) as cnt_review,
IFNULL(AVG(r.rating), 0) as avg_rating
from movie m
left join review r
on m.id = r.movieId
where title like '%big%'
group by m.id;

 

 

 

 

 

 

 

 

-- 검색 기록을 저장할 테이블 생성

tables > history > columms   id : int <primary key, auto increment> 아이디가 중복되지 않게하고 자동으로 추가한다.   user_id : int <Foreign Keys> user 테이블에 id   keyword : varchar(100)    created_at : timestamp

-- 가정 : 내가(301), 검색한 검색어를 저장

INSERT into history(user_id, keyword)
values (301, 'bi');

 

 

-- 검색 기록을 보여주기 위한 SQL

SELECT *
from history
where user_id = 301
order by created_at desc;

 

-- 히스토리 삭제 SQL

DELETE from history
where id = 2 and user_id = 301;

 

-- 내 정보 화면

SELECT id, email, nickname, gender
from user
where id = 301;

 

-- 가정 : 내 아이디는 100이고, 내 리뷰 리스트 가져온다.

SELECT r.id as review_id, m.title, r.rating
from review r
join movie m
on r.movieId = m.id
where userId = 100;