[MySQL] 多대多관계, null인 데이터를 가져오는 법

 

MANY : MANY

다 대 다 관계

해당 데이터에 foreign key를 연결해준다. 

 

테이블 reviewer, series, reviews 상기 이미지처럼 생성 및 foreign key 적용

하기 데이터 입력

use yh_db; INSERT INTO series (title, released_year, genre) VALUES    ('Archer', 2009, 'Animation'), ('Arrested Development', 2003, 'Comedy'),    ("Bob's Burgers", 2011, 'Animation'), ('Bojack Horseman', 2014, 'Animation'),    ("Breaking Bad", 2008, 'Drama'), ('Curb Your Enthusiasm', 2000, 'Comedy'),    ("Fargo", 2014, 'Drama'), ('Freaks and Geeks', 1999, 'Comedy'),    ('General Hospital', 1963, 'Drama'), ('Halt and Catch Fire', 2014, 'Drama'),    ('Malcolm In The Middle', 2000, 'Comedy'), ('Pushing Daisies', 2007, 'Comedy'),    ('Seinfeld', 1989, 'Comedy'), ('Stranger Things', 2016, 'Drama');   INSERT INTO reviewers (first_name, last_name) VALUES    ('Thomas', 'Stoneman'), ('Wyatt', 'Skaggs'),    ('Kimbra', 'Masters'), ('Domingo', 'Cortes'),    ('Colt', 'Steele'), ('Pinkie', 'Petit'), ('Marlon', 'Crafford');    INSERT INTO reviews(series_id, reviewer_id, rating) VALUES    (1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),    (2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),    (3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),    (4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),    (5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),    (6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),    (7,2,9.1),(7,5,9.7), (8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),    (9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),    (10,5,9.9), (13,3,8.0),(13,4,7.2), (14,2,8.5),(14,3,8.9),(14,4,8.9); select * from reviewers; select * from series; select * from reviews;

 

- TV쇼에 대한 리뷰 앱을 개발한다고 치자.

 

-- 실습 

첫번째 문제: print this

select s.title, r.rating from reviews r join series s on r.series_id = s.id join reviewers rv on r.reviewer_id = rv.id order by title; -- > 쌤 풀이 select s.title, r.rating from series s  join reviews r on s.id = r.series_id;

 

-- 실습 두번째 문제: print this

select s.title, avg(r.rating) as avg_rating from reviews r join series s on r.series_id = s.id join reviewers rv on r.reviewer_id = rv.id group by title order by avg_rating; -- > 쌤 풀이 select s.title, avg(r.rating) as avg_rating from series s  join reviews r on s.id = r.series_id group by s.title order by avg_rating;

 

-- 실습 세번째 문제: print this

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

 

- null인 데이터를 가져오는 법

-- 실습 네번째 문제: print this

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

 

-- 실습 다섯번째 문제: print this

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

 

-- 실습 여섯번째 문제: print this

select rv.first_name, rv.last_name, count(r.rating) as COUNT, ifnull(min(r.rating),0) as MIN,          ifnull(max(r.rating),0) AS MAX,          ifnull(avg(r.rating),0) AS AVG,         if(count(r.rating)= 0,'INACTIVE','ACTIVE') as STATUS from reviewers rv left join reviews r on rv.id= r.reviewer_id group by rv.id;

-- count(*)를 해봤더니, 리뷰 안쓴 애가 1이 나왔다. 행 자체가 1개 있다는 뜻. 

-- count(*)로 하면 안된다. 

-- 리뷰가 없기 때문에, null이 존재하는 rating으로 계산해야한다. 

 

-- 실습 마지막 문제: print this

select s.title, r.rating, concat(rv.first_name, ' ', rv.last_name) as reviewers from reviews r join reviewers rv on r.reviewer_id = rv.id join series s on r.series_id = s.id order by s.title; -- > 쌤 풀이: 같음 select s.title, r.rating, concat(rv.first_name, ' ', rv.last_name) as reviewers from series s  join reviews r on s.id = r.series_id join reviewers rv on r.reviewer_id = rv.id order by s.title;