Learning Objectives
- 說明檢視表是「存起來的 SELECT」，本身不存資料，以及它在簡化查詢、隱藏欄位與權限控管上的用途
- 用 CREATE VIEW 建立檢視表，指定欄位名稱，並說明指定之後原本的欄位名稱為什麼查不到
- 透過可更新的檢視表新增、修改、刪除資料，說明實際被改的是底層的表格
- 辨認不可更新的檢視表（GROUP BY、DISTINCT、聚合函式），用 WITH CHECK OPTION 防止寫入檢視表範圍以外的資料
- 用 CREATE OR REPLACE VIEW、ALTER VIEW、DROP VIEW 管理檢視表，用 SHOW FULL TABLES 分辨表格與檢視表
檢視表是存起來的 SELECT
檢視表（view，也譯作「視圖」）是一個有名字的 SELECT 敘述。建立之後，可以像表格一樣查詢它， 每次查詢時 MySQL 才去執行背後的 SELECT，從底層的表格取資料。檢視表本身不存資料，底層表格的資料改變，查詢檢視表的結果也跟著改變。
CREATE VIEW 檢視表名稱 [(欄位名稱, ...)]
AS
SELECT 敘述;
檢視表常見的用途：
| 用途 | 說明 |
|---|---|
| 簡化查詢 | 把常用、複雜的 SELECT（多表 JOIN、計算欄位）存成一個名稱，使用的人只要 SELECT * FROM 檢視表 |
| 隱藏欄位 | 只列出需要的欄位，例如員工資料不列出薪資與身分證字號 |
| 權限控管 | 只授權使用者查詢檢視表，不授權底層表格，對方就只看得到檢視表列出的欄位與資料列 |
| 穩定的介面 | 底層表格改了結構，只要調整檢視表的定義，使用檢視表的程式不必修改 |
原始課程說「把常用的 SELECT 寫成 View，可以減少解析時間、提升效率」，在 MySQL 並不成立： MySQL 不會把檢視表的執行計畫存起來，每次查詢都會把檢視表的定義併入查詢重新最佳化（或先算成暫存表格），速度和直接寫出完整的 SELECT 差不多。 MySQL 也沒有其他資料庫的「實體化檢視表」（materialized view）。檢視表的價值在於簡化與控管，不是加速。
本課使用原始課程的 president 表格（美國前 8 任總統），執行前已經建立在 sampdb 資料庫裡：
| 欄位 | 型別 | 內容 |
|---|---|---|
last_name、first_name |
VARCHAR(15) NOT NULL |
姓、名 |
suffix |
VARCHAR(5) NULL |
字尾（如 Jr.），這 8 位都是 NULL |
city、state |
VARCHAR(20)、VARCHAR(2)，都是 NOT NULL |
出生的城市與州 |
birth、death |
DATE |
出生與過世日期 |
建立與查詢檢視表
vpres 只列出 president 的 4 個欄位。查詢檢視表時，右邊的 president 表格會標出讀到的列：資料其實來自底層表格。vpres2 在名稱後面用括號指定了欄位名稱 ln、fn，接著用原本的欄位名稱查詢它，會發生什麼事？
檢視表的欄位名稱
檢視表的欄位名稱有三種來源，優先順序由高到低：
- 檢視表名稱後面的欄位清單：
CREATE VIEW vpres2 (ln, fn) AS …，數量必須和 SELECT 的欄位數相同，否則ERROR 1353 - SELECT 裡的欄位別名：
SELECT last_name AS ln, first_name AS fn …，效果和上一種相同 - 都沒有指定時，沿用 SELECT 的欄位名稱
指定新名稱後，使用檢視表的人只看得到新名稱，原本的欄位名稱在檢視表裡不存在，所以 SELECT last_name FROM vpres2 得到
ERROR 1054 (42S22): Unknown column 'last_name' in 'field list'。原始課程把這點視為安全上的好處：使用者看不出底層的欄位名稱。
SELECT 裡有計算或函式時，最好取個別名，例如 SELECT state, COUNT(*) AS n …，否則檢視表的欄位名稱會是 COUNT(*) 這種不好用的文字。
透過檢視表修改資料
這是原始課程的例子：表格 t 只有一個整數欄位 i，檢視表 v 列出 t 的全部資料。對 v 執行 INSERT、DELETE、UPDATE，觀察右邊表格 t 的變化：綠色是新增、紅色刪除線是刪除、黃色是修改。
想一想：建立與使用檢視表
執行下面的敘述會得到什麼？
CREATE VIEW vpres2 (ln, fn) AS
SELECT last_name, first_name FROM president;
SELECT last_name FROM vpres2;
表格 t 原本有 1、2、3 三列，v 是 SELECT i FROM t 的檢視表。依序執行下面三個敘述後，SELECT SUM(i) FROM t; 的結果是什麼？
INSERT INTO v (i) VALUES (4);
DELETE FROM v WHERE i < 3;
UPDATE v SET i = i + 1;
關於檢視表，哪一個說法正確？
哪些檢視表可以更新
檢視表要能 INSERT、UPDATE、DELETE，MySQL 必須能把檢視表的每一列對應回底層表格的某一列。 SELECT 裡有下面這些東西時，結果的一列可能來自很多列，或根本不對應任何一列，檢視表就不可更新：
- 聚合函式：
COUNT()、SUM()、AVG()、MIN()、MAX()等 GROUP BY、HAVING、DISTINCTUNION、UNION ALL- SELECT 清單裡的子查詢，或只有常數、沒有引用任何表格
對不可更新的檢視表 UPDATE 或 DELETE，會得到 ERROR 1288 (HY000): The target table … of the UPDATE is not updatable。
INSERT 在真的 MySQL 上是另一個錯誤代碼：ERROR 1471 (HY000): The target table vstate of the INSERT is not insertable-into（播放器顯示成 1288）。
還有兩個細節：
- INSERT 要能補齊底層表格：檢視表沒有列出的欄位，必須允許 NULL 或有預設值。
vpres沒有列出birth（NOT NULL、沒有預設值）， 所以對vpresINSERT 會得到ERROR 1423 (HY000): Field of view 'sampdb.vpres' underlying table doesn't have a default value - 連接多張表格的檢視表：一次 UPDATE 只改其中一張表格的欄位時，MySQL 允許；同時改到兩張表格則不行（
ERROR 1393）；也不能透過它 DELETE（ERROR 1395）
WITH CHECK OPTION
檢視表有 WHERE 條件時，預設仍可以透過它寫入「不符合條件」的資料，寫完那一列就從檢視表消失了。
例如 vva 只列出維吉尼亞州（VA）的總統，透過它把 Monroe 的州改成 NY，更新成功，但 Monroe 從此不在 vva 裡。
這通常不是想要的結果。在定義最後加上 WITH CHECK OPTION，MySQL 就會檢查每一筆透過檢視表寫入的資料都必須符合 WHERE 條件，
否則拒絕：ERROR 1369 (HY000): CHECK OPTION failed 'sampdb.vva'。
檢視表建立在另一個檢視表上時，WITH CASCADED CHECK OPTION（預設）連同底層檢視表的條件一起檢查，WITH LOCAL CHECK OPTION 只檢查這一層的條件。
不可更新的檢視表與 WITH CHECK OPTION
vstate 用 GROUP BY 統計各州的人數，一列對應很多位總統，無法更新。vva 先建立沒有 CHECK OPTION 的版本，把 Monroe 改到 NY 之後再查詢 vva；接著用 CREATE OR REPLACE VIEW 換成有 WITH CHECK OPTION 的版本，再試一次。
修改、刪除與查看檢視表
| 指令 | 作用 |
|---|---|
CREATE OR REPLACE VIEW 名稱 AS … |
不存在就建立，已存在就換成新的定義 |
ALTER VIEW 名稱 AS … |
修改已存在的檢視表（不存在時是錯誤） |
DROP VIEW 名稱1, 名稱2; |
刪除檢視表，可以一次刪多個；DROP VIEW IF EXISTS 不存在時只給警告 |
SHOW FULL TABLES; |
列出表格與檢視表，Table_type 欄位是 BASE TABLE（表格）或 VIEW（檢視表） |
DESCRIBE 名稱; |
檢視表也可以 DESCRIBE，看欄位與型別 |
SHOW CREATE VIEW 名稱; |
顯示 MySQL 儲存的定義 |
檢視表和表格共用同一個名稱空間：同一個資料庫裡不能有同名的表格和檢視表（ERROR 1050 … already exists）。
DROP TABLE 不能刪除檢視表，DROP VIEW 也不能刪除表格。
SHOW CREATE VIEW 顯示的是 MySQL 整理過的定義，例如：
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vpres` AS select …
ALGORITHM：MySQL 怎麼執行檢視表，MERGE是把定義併入查詢，TEMPTABLE是先算成暫存表格，UNDEFINED是讓 MySQL 自己選DEFINER：建立者SQL SECURITY DEFINER：查詢檢視表時，以建立者的權限讀取底層表格。所以 root 建立vpres之後，GRANT SELECT ON sampdb.vpres TO 'fred'@'localhost';，fred 就能查詢vpres；但他查詢president會得到ERROR 1142 (42000): SELECT command denied，看不到被隱藏的欄位。這就是用檢視表做權限控管的方式（帳號與 GRANT 見第 11 課）
播放器顯示的定義文字是近似 MySQL 的整理格式，細節可能和真的伺服器不同。
原始課程列出的限制，在 MySQL 8 的現況
| 限制 | MySQL 8 |
|---|---|
| FROM 裡不能有子查詢 | 已取消（MySQL 5.7.7 起可以） |
SELECT 不能使用變數（如 @n） |
仍然有效：ERROR 1351 (HY000): View's SELECT contains a variable or parameter |
| 不能和觸發程序（trigger）相關 | 仍然有效：觸發程序只能建立在表格上，不能建立在檢視表上 |
| 建立時，參照的表格必須存在 | 仍然有效；建立之後才刪除底層表格，查詢檢視表會得到 ERROR 1356，說明檢視表參照了不存在的表格 |
管理檢視表
建立兩個檢視表後，SHOW FULL TABLES 比一般的 SHOW TABLES 多一個 Table_type 欄位，分得出哪些是檢視表。接著用 ALTER VIEW 修改定義、DESCRIBE 檢查欄位，最後刪除。
想一想：可更新與 CHECK OPTION
vstate 定義為 SELECT state, COUNT(*) AS n FROM president GROUP BY state。執行下面的敘述會得到什麼？
UPDATE vstate SET n = 10 WHERE state = 'VA';
vva 定義為只列出 state = 'VA' 的總統，並加上 WITH CHECK OPTION。執行下面的敘述會得到什麼？
UPDATE vva SET state = 'NY' WHERE last_name = 'Washington';
建立 vpres 檢視表之後，下面的查詢會傳回什麼？
SELECT TABLE_TYPE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sampdb' AND TABLE_NAME = 'vpres';
動手寫：把連接查詢存成檢視表
在 employee 資料庫建立檢視表 emp_dept，列出員工編號 employeeID、員工姓名（欄位名稱 employee）與部門名稱（欄位名稱 department），員工與部門用 departmentID 連接。再透過這個檢視表，查詢「Research and Development」部門的員工姓名與部門名稱，依員工姓名排序。
重點整理
| 主題 | 重點 |
|---|---|
| 檢視表 | 有名字的 SELECT，本身不存資料；用途是簡化查詢、隱藏欄位、權限控管，不是加速 |
| 欄位名稱 | CREATE VIEW 名稱 (欄位, …) 或別名；改名後原本的名稱查不到（ERROR 1054） |
| 可更新 | 每一列對應底層表格的一列時，INSERT、UPDATE、DELETE 會改到底層表格 |
| 不可更新 | 聚合函式、GROUP BY、HAVING、DISTINCT、UNION 等（ERROR 1288） |
| WITH CHECK OPTION | 透過檢視表寫入的資料必須符合 WHERE 條件（ERROR 1369） |
| 管理 | CREATE OR REPLACE VIEW、ALTER VIEW、DROP VIEW、SHOW FULL TABLES、SHOW CREATE VIEW |
| 權限 | 預設 SQL SECURITY DEFINER：只授權檢視表，使用者就只看得到檢視表的內容 |
到這一課，SQL 軌從安裝、設計、查詢一路到管理、最佳化與檢視表。可以拿自己的資料設計一個資料庫，把學過的指令都用一次。