MySQL VIEW

mix 2


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

(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'



(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



留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

MySQL使用者權限設定