MySQL - 일대다, Joins, 다대다 예제

일 대 다 (One To Many)

FOREING KEY ~ REFERENCES ~

외부 테이블의 column 참조하기

CREATE TABLE <table1-name> (
  	column1 type,
  	column2 type
);

CREATE TABLE <table2-name> (
  	column3 type,
  	column4 type,
  	FOREIGN KEY (column4) REFERENCES <table1-name>(column1)
);

ex) CREATE TABLE customers (
      id INT PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      email VARCHAR(50)
	);

    CREATE TABLE orders (
        id INT PRIMARY KEY AUTO_INCREMENT,
        order_date DATE,
        amount DECIMAL(8,2),
        customer_id INT,
        FOREIGN KEY (customer_id) REFERENCES customers(id)
	);

ON DELETE CASCADE

참조 값이 사라지면 해당 column 모두 삭제

CREATE TABLE <table2-name> (
  	column3 type,
  	column4 type,
  	FOREIGN KEY (column4) REFERENCES <table1-name>(column1) ON DELETE CASCADE
);

---

Joins

INNER JOIN

(INNER) JOIN ~ ON

A ∩ B

SELECT column FROM TABLE1 (INNER) JOIN TABLE2 ON 조건;
ex) SELECT * FROM customers (INNER) JOIN orders ON customers.id = orders.customer_id;

ex) SELECT first_name, last_name, SUM(amount) FROM customers JOIN orders ON orders.customer_id = customers.id
	GROUP BY customers.first_name, last_name;

LEFT JOIN

LEFT JOIN ~ ON

A ∪ (A ∩ B)

SELECT column FROM TABLE1 LEFT JOIN TABLE2 ON 조건;
ex) SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

RIGHT JOIN

RIGHT JOIN ~ ON

(A ∩ B) ∪ B

SELECT column FROM TABLE1 RIGHT JOIN TABLE2 ON 조건;
ex) SELECT * FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;

IFNULL

value1의 값이 NULL이면 value2로 치환

IFNULL(value1, value2);
ex) SELECT IFNULL(SUM(amount), 0);

---

다 대 다 (Many To Many)

데이터베이스 생성

CREATE TABLE reviewers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);
 
CREATE TABLE series (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    released_year YEAR,
    genre VARCHAR(100)
);
 
CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rating DECIMAL(2 , 1 ),
    series_id INT,
    reviewer_id INT,
    FOREIGN KEY (series_id)
        REFERENCES series (id),
    FOREIGN KEY (reviewer_id)
        REFERENCES reviewers (id)
);

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);

series

reviews

reviewers

---

사진과 같은 결과를 보여주는 쿼리 작성해보기

1번 예제

SELECT title, rating FROM series JOIN reviews ON series.id = reviews.series_id;

---

2번 예제

SELECT title, AVG(rating) AS avg_rating FROM series
JOIN reviews ON series.id = reviews.series_id GROUP BY title ORDER BY avg_rating;

---

3번 예제

SELECT first_name, last_name, rating FROM reviewers
JOIN reviews ON reviews.reviewer_id = reviewers.id;

---

4번 예제

SELECT title AS unreviewed_series FROM series
LEFT JOIN reviews ON series.id = reviews.series_id WHERE rating IS NULL;

and

SELECT title AS unreviewed_series FROM reviews
RIGHT JOIN series ON series.id = reviews.series_id WHERE rating IS NULL;

---

5번 예제

SELECT genre, AVG(rating) AS average_rating FROM series
JOIN reviews ON series.id = reviews.series_id GROUP BY genre;

---

6번 예제

SELECT 
    first_name,
    last_name,
    COUNT(rating) AS count,
    IFNULL(MIN(rating), 0) AS min,
    IFNULL(MAX(rating), 0) AS max,
    ROUND(IFNULL(AVG(rating), 0), 2) AS average,
    CASE
        WHEN COUNT(rating) >= 10 THEN 'POWERUSER'
        WHEN COUNT(rating) > 0 THEN 'ACTIVE'
        ELSE 'INACTIVE'
    END AS status
FROM
    reviewers
        LEFT JOIN
    reviews ON reviewers.id = reviews.reviewer_id
GROUP BY first_name , last_name;

and

SELECT 
    first_name,
    last_name,
    COUNT(rating) AS count,
    IFNULL(MIN(rating), 0) AS min,
    IFNULL(MAX(rating), 0) AS max,
    ROUND(IFNULL(AVG(rating), 0), 2) AS average,
    IF(COUNT(rating) > 0,
        'ACTIVE',
        'INACTIVE') AS status
FROM
    reviewers
        LEFT JOIN
    reviews ON reviewers.id = reviews.reviewer_id
GROUP BY first_name , last_name;

---

7번 예제

내 답안
SELECT title, rating, CONCAT(reviewer.first_name, ' ', reviewer.last_name) AS reviewer FROM series
JOIN reviews ON series.id = reviews.series_id
JOIN reviewers ON reviewers.id = reviews.reviewer_id;

정답
SELECT title, rating, CONCAT(first_name, ' ', last_name) AS reviewer FROM reviews
JOIN series ON reviews.series_id = series.id
JOIN reviewers ON reviews.reviewer_id = reviewers.id;

> reviewer.first_name이 아닌 first_name으로 하면 됐다.