MySQL VIEW

MySQL資料庫中，View元件可以用來保存一段你指定的查詢敘述。

MySQL View的實作請參考 https://www.mysql.tw/2023/05/mysql-view.html

(1)CREATE VIEW語法 
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

https://dev.mysql.com/doc/refman/8.0/en/create-view.html

範例
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;

範例 WITH CHECK OPTION;
mysql> CREATE TABLE t1 (a INT);
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
-> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
-> WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
-> WITH CASCADED CHECK OPTION;

因為v2使用WITH LOCAL CHECK OPTION; 僅會針對v2檢查對插入項進行測試。
mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)

因為v3使用WITH CASCADED CHECK OPTION;不僅會針對它自己的檢查對插入項進行測試，也會針對v1檢查對插入項進行測試。
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

更多參考資料
https://www.mysqltutorial.org/mysql-view-with-check-option/
https://www.mysqltutorial.org/mysql-view-local-cascaded-in-with-check-option

(2)SHOW CREATE VIEW語法 
SHOW CREATE VIEW view_name

該語句給出了1個建立給定視圖的CREATE VIEW語句。
mysql> SHOW CREATE VIEW v;

(3)ALTER VIEW語法 
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

https://dev.mysql.com/doc/refman/8.0/en/alter-view.html

範例
mysql>Create VIEW myView (id,first_name,city) AS SELECT id, first_name, city FROM employee;
mysql>ALTER VIEW myView (id,first_name,city) AS SELECT id, upper(first_name), city FROM employee;

(4)讓show tables只顯示Tables
show full tables where Table_Type = 'BASE TABLE'
OR
show full tables where Table_Type != 'VIEW'

更多參考
http://www.codedata.com.tw/database/mysql-tutorial-11-views/
http://twpug.net/docs/mysql-5.1/views.html
http://www.cnblogs.com/zejin2008/p/4767531.html