[MySQL] 다대다 관계의 DB

여러 테이블이 복잡한 관계로 얽혀있는것을 다대다 테이블이라고 한다.

 

다대다 테이블을 만들어 보자

 

reviewers 테이블:

 

# 리뷰한 사람들의 데이터가 들어있다.

 

 

series 테이블:

 

# 티비 쇼 시리즈의 데이터가 들어있다.

 

 

reviews 테이블:

 

# 리뷰한 정보들이 들어있다.

# 다른 테이블의 id 컬럼을 foreign key설정으로 묶어줬다. 

 

 

title과 rating의 평균값이 나오도록 해보자

 

in:

select s.title, avg(r.rating) as avg_rating
from reviews r
join series s
on r.series_id = s.id
group by s.title
order by avg_rating;

out:

# 리뷰 테이블과 시리즈테이블이 연결되었다.

 

 

리뷰어의 성과 이름 그리고 rating을 나타내자

 

in:

select rer.first_name, rer.last_name, r.rating
from reviews r
join reviewers rer
on r.reviewer_id = rer.id;

out:

# 리뷰 테이블과 리뷰어 테이블이 연결되었다.

 

 

리뷰를 하지않은 티비쇼의 타이틀을 나타내자

 

in:

select title as unreviewed_series
from series s
left join reviews r
on s.id = r.series_id
where r.rating is null;

out:

# 시리즈 테이블에 리뷰 테이블이 left join으로 연결되었다.

 

 

리뷰어의 이름과 리뷰한 개수, 레이팅의 최소, 최대, 평균값을 나타내고 리뷰한것이 없으면 INACTIVE, 있으면 ACTIVE로 표기한다.

 

in:

select rer.first_name, rer.last_name, count(r.rating) as COUNT, 
min(ifnull(r.rating,0)) as MIN, max(ifnull(r.rating,0)) as MAX,
avg(ifnull(r.rating,0)) as AVG,
if(count(r.rating) = 0, 'INACTIVE','ACTIVE') as STATUS

from reviewers rer
left join reviews r
on rer.id = r.reviewer_id
group by rer.id;

out:

# count(*)을 하면 전체 컬럼이 되기때문에 인엑티브된 사람의 카운트 수가 1이 된다

# 따라서 레이팅을 카운트한다.

 

 

3개의 테이블을 join할수 있다.

 

제목과 레이팅 그리고 리뷰어의 이름을 나타내자

 

in:

select s.title, r.rating, concat(rer.first_name," ",rer.last_name) as reviewer
from series s
join reviews r
on s.id = r.series_id
join reviewers rer
on rer.id = r.reviewer_id
order by title;

out:

# 테이블을 하나 더 연결 하고싶으면 다시 join을 하고 on으로 연결될 컬럼을 설정하면된다.