MySQL - VIEW, GROUP BY와 함께 사용하는

VIEW

VIEW

가상 테이블 생성

CREATE VIEW <view-name> AS ~ ;
ex) 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;

OR REPLACE

가상 테이블 수정

CREATE OR REPLACE VIEW <view-name> AS ~ ;
ex) CREATE OR REPLACE VIEW ordered_series AS SELECT * FROM series ORDER BY released_year DESC;

or

ALTER VIEW <view-name> AS ~ ;
ex) ALTER VIEW ordered_series AS SELECT * FROM series ORDER BY released_year;

DROP VIEW

가상 테이블 삭제

DROP VIEW <view-name>;
ex) DROP VIEW ordered_series;

[참고] VIEW 정보

---

GROUP BY와 함께 사용하는

HAVING

GROUP BY로 생성한 그룹 필터링

HAVING 조건;
ex) SELECT title, AVG(rating), COUNT(rating) AS review_count FROM full_reviews
	GROUP BY title HAVING COUNT(rating) > 1;

WITH ROLLUP

SELECT column1, column2 FROM <table/view-name> GROUP BY column1 WITH ROLLUP;