View
view는 query의 결과 집합을 하나의 가상의 테이블로 보여주는 방식이다. 즉, 실제의 테이블을 만드는 것이 아니라 가상의 테이블을 만드는 것이다. 핵심은 query에 우리의 이름을 부여하고 저장하는 query라는 것이다. 단, 결과로 나오는 테이블이 가상의 테이블이라는 것이다.
먼저 table을 만들어보자.
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)
);
CREATE TABLE reviewers (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL
);series와 reviewers가 다대다 관계이고, 이를 reviews라는 table을 만들어서 일대다 관계로 풀어낸 것이다.
이제 data를 넣어보도록 하자.
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와 reviewers를 서로 join해보도록 하자. 이들은 다대다 관계이기 때문에 reviews를 통해서 관계를 맺어야 한다.
SELECT title, released_year, genre, rating, first_name, last_name FROM reviews
JOIN series ON series.id = reviews.series_id
JOIN reviewers ON reviewers.id = reviews.reviewer_id;
+----------------------+---------------+-----------+--------+------------+-----------+
| title | released_year | genre | rating | first_name | last_name |
+----------------------+---------------+-----------+--------+------------+-----------+
| Archer | 2009 | Animation | 8.0 | Thomas | Stoneman |
| Arrested Development | 2003 | Comedy | 8.1 | Thomas | Stoneman |
| Bob's Burgers | 2011 | Animation | 7.0 | Thomas | Stoneman |
| Bojack Horseman | 2014 | Animation | 7.5 | Thomas | Stoneman |
...
| General Hospital | 1963 | Drama | 4.3 | Pinkie | Petit |
+----------------------+---------------+-----------+--------+------------+-----------+이제 위 데이터를 바탕으로 집계나 수학적인 통계 데이터를 내보려고 한다. 그런데, 매번 GROUP BY 할 때마다 위의 query를 반복해야한다는 것은 큰 부담이 된다. 그래서 아예 테이블로 만드는 것이다. 이것이 view이다.
VIEW의 사용 방법은 다음과 같다.
CREATE VIEW [view_table_name] AS
[QUERY]QUERY의 결과를 가상의 테이블인 view_table_name에 저장하는 것이다. 가령 다음과 같이 사용할 수 있다.
CREATE VIEW full_reviews AS
SELECT title, released_year, genre, rating, first_name, last_name FROM reviews
JOIN series ON series.id = reviews.series_id
JOIN reviewers ON reviewers.id = reviews.reviewer_id;명령어가 성공했다면 table이 만들어졌는 지 보도록 하자.
show tables;
+---------------+
| Tables_in_gyu |
+---------------+
| full_reviews |
| reviewers |
| reviews |
| series |
+---------------+full_reviews table이 만들어진 것을 볼 수 있다.
어떤 데이터들이 있는 지 보도록 하자.
SELECT * FROM full_reviews;
+----------------------+---------------+-----------+--------+------------+-----------+
| title | released_year | genre | rating | first_name | last_name |
+----------------------+---------------+-----------+--------+------------+-----------+
| Archer | 2009 | Animation | 8.0 | Thomas | Stoneman |
| Arrested Development | 2003 | Comedy | 8.1 | Thomas | Stoneman |
| Bob's Burgers | 2011 | Animation | 7.0 | Thomas | Stoneman |
| Bojack Horseman | 2014 | Animation | 7.5 | Thomas | Stoneman |
...
| General Hospital | 1963 | Drama | 4.3 | Pinkie | Petit |
+----------------------+---------------+-----------+--------+------------+-----------+우리가 원했던 데이터들이 그대로 저장된 것을 볼 수 있다.
이제 view를 사용함으로서 query를 더 짧게 사용하고 효율적으로 사용할 수 있다. view는 또한 가상의 테이블이기 때문에, 여기에 사용된 작업 결과가 실제 원본에 반영되지 않는다. 또한, 실제 쿼리를 반복하는 것보다 훨씬 더 시스템 자원적으로 효율적이다.
업데이트 할 수 있는 view
view는 실제 테이블이 아니다. 그렇기 때문에 view 테이블을 수정하는 것도 한계가 있는데, 가령 우리의 join 테이블인 full_reviews도 수정이 불가능하다.
join을 실행한 view이기 때문에 update할 수 없다고 나온다.
DELETE FROM full_reviews WHERE released_year = 2010;
ERROR 1395 (HY000): Can not delete from join view 'gyu.full_reviews'view의 수정, 삭제 권한에 대한 규칙은 꽤나 까다로운데 다음과 같다.
1. 집계 함수인 SUM, MIN, MAX, COUNT 등이나 window 함수는 사용이 불가능하다.
2. DISTINCT 사용 시 불가
3. GROUP BY 사용 시 불가
4. HAVING 절 사용 시 불가
5. UNION, UNION ALL 사용 시 불가
6. query에 하위 query가 있는 경우
7. join이 있는 경우, 단 update할 수 있는 join도 있다.
그렇다고 모든 view table이 업데이트가 불가능한 것은 아니다.
아래의 간단한 view를 만들어보도록 하자.
CREATE VIEW ordered_series AS
SELECT * FROM series ORDER BY released_year;이제 ordered_series라는 테이블에 데이터를 추가, 삭제해보도록 하자.
INSERT INTO ordered_series(title, released_year, genre) VALUES('The Great', 2020, 'Comedy');재밌게도 성공하는데, table에 실제 데이터가 추가되었는 지 확인해보도록 하자.
SELECT * FROM ordered_series;
+----+-----------------------+---------------+-----------+
| id | title | released_year | genre |
+----+-----------------------+---------------+-----------+
| 9 | General Hospital | 1963 | Drama |
...
| 15 | The Great | 2020 | Comedy |
+----+-----------------------+---------------+-----------+The Great라는 series가 추가된 것을 볼 수 있다.
마지막으로 삭제도 가능한 지 확인해보도록 하자.
DELETE FROM ordered_series WHERE title = 'The Great';문제없이 query가 실행된 것을 볼 수 있을 것이다.
view 변경, 삭제
view를 만들고 난 뒤, 시간이 지나 실제 원본 table의 데이터 변경 계속될 수 있다. 그래서 이전의 view table이 아니라 변동 사항을 반영한 view table을 새로 만들어야하는데, 덮어쓰기는 불가능하다.
아래는 위에서 만들었던 ordered_series view table을 내림차순으로 정렬한 버전으로 덮어쓰려는 코드이다.
CREATE VIEW ordered_series AS
SELECT * FROM series ORDER BY released_year DESC;그러나 덮어쓰기가 실패한다. 덮어쓰기를 하고 싶다면 CREATE 옆에 OR REPLACE를 추가해야한다.
아래를 실행하면 문제없이 view가 만영될 것이다.
CREATE OR REPLACE VIEW ordered_series AS
SELECT * FROM series ORDER BY released_year DESC;실제로 반영되었는 지 결과를 확인해보도록 하자.
SELECT * FROM ordered_series;
+----+-----------------------+---------------+-----------+
| id | title | released_year | genre |
+----+-----------------------+---------------+-----------+
| 14 | Stranger Things | 2016 | Drama |
| 4 | Bojack Horseman | 2014 | Animation |
| 7 | Fargo | 2014 | Drama |
| 10 | Halt and Catch Fire | 2014 | Drama |
| 3 | Bob's Burgers | 2011 | Animation |
| 1 | Archer | 2009 | Animation |
| 5 | Breaking Bad | 2008 | Drama |
| 12 | Pushing Daisies | 2007 | Comedy |
| 2 | Arrested Development | 2003 | Comedy |
| 6 | Curb Your Enthusiasm | 2000 | Comedy |
| 11 | Malcolm In The Middle | 2000 | Comedy |
| 8 | Freaks and Geeks | 1999 | Comedy |
| 13 | Seinfeld | 1989 | Comedy |
| 9 | General Hospital | 1963 | Drama |
+----+-----------------------+---------------+-----------+내림차순으로 정렬된 것을 볼 수 있다.
다른 방법으로는 REPLACE 대신에 ALTER VIEW를 사용하는 방법도 있다.
ALTER VIEW ordered_series AS
SELECT * FROM series ORDER BY released_year;마지막으로 table을 삭제하는 방법은 아래와 같다.
DROP VIEW ordered_series;Having
Having 절은 Group BY로 얻은 그룹을 필터링하는 데 사용된다. 예제를 하나 보도록 하자.
아래는 rating을 title을 토대로 그룹화하여 평균을 낸 값이다.
SELECT title, AVG(rating)
FROM full_reviews
GROUP BY title;
| title | AVG(rating) |
+----------------------+-------------+
| Archer | 8.12000 |
| Arrested Development | 8.08000 |
| Bob's Burgers | 7.52000 |
| Bojack Horseman | 7.94000 |
| Breaking Bad | 9.36000 |
| Curb Your Enthusiasm | 8.12000 |
| Fargo | 9.40000 |
| Freaks and Geeks | 8.60000 |
| General Hospital | 5.38000 |
| Halt and Catch Fire | 9.90000 |
| Seinfeld | 7.60000 |
| Stranger Things | 8.76667 |
+----------------------+-------------+그런데 생각해보면 특정 title은 데이터가 너무 부족해서 평균이라는 값이 어색할 수 있는 경우가 있다. 가령, review가 두 개 이상인 title만 보도록 하자. 이를 위해서는 GROUP BY로 만든 결과를 가지고 WHERE를 적용하는 방법 밖에 없다. 즉, GROUP BY가 적용된 query문에 곧 바로 쓸 수 없다는 것이다.
HAVING을 사용한다면 이 문제를 해결할 수 있다. GROUP BY의 결과값에 실제로 포함하고 싶은 그룹의 범위를 좁혀주는 것이다. HAVING은 마치 WHERE를 쓰듯이 GROUP BY 옆에 써주면 된다.
SELECT title, AVG(rating), COUNT(rating)
FROM full_reviews
GROUP BY title
HAVING COUNT(title) > 1;
+----------------------+-------------+---------------+
| title | AVG(rating) | COUNT(rating) |
+----------------------+-------------+---------------+
| Archer | 8.12000 | 5 |
| Arrested Development | 8.08000 | 5 |
| Bob's Burgers | 7.52000 | 5 |
| Bojack Horseman | 7.94000 | 5 |
| Breaking Bad | 9.36000 | 5 |
| Curb Your Enthusiasm | 8.12000 | 5 |
| Fargo | 9.40000 | 2 |
| Freaks and Geeks | 8.60000 | 4 |
| General Hospital | 5.38000 | 5 |
| Seinfeld | 7.60000 | 2 |
| Stranger Things | 8.76667 | 3 |
+----------------------+-------------+---------------+위와 같이 HAVING을 쓰게 되면 title의 그룹이 2개 이상인 경우만 rating의 평균 값을 낸다.
