mysql檢視--mysql 5.1 參考手冊

shuangoracle發表於2011-07-20
MySql
第22章：檢視
目錄
22.1. ALTER VIEW語法
22.2. CREATE VIEW語法
22.3. DROP VIEW語法
22.4. SHOW CREATE VIEW語法
在5.1版MySQL伺服器中提供了檢視功能（包括可更新檢視）。
本章討論了下述主題：
· 使用CREATE VIEW或ALTER VIEW建立或更改檢視。
· 使用DROP VIEW銷燬檢視。
· 使用SHOW CREATE VIEW顯示檢視後設資料。
關於使用檢視方面的限制，請參見附錄I：特性限制。
如果你已從不支援檢視的較舊版本升級到MySQL 5.1，要想使用檢視，應升級授權表，使之包含與檢視有關的許可權。請參見2.10.2節，“升級授權表”。
22.1. ALTER VIEW語法
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
該語句用於更改已有檢視的定義。其語法與CREATE VIEW類似。請參見22.2節，“CREATE VIEW語法”。該語句需要具有針對檢視的CREATE VIEW和DROP許可權，也需要針對SELECT語句中引用的每一列的某些許可權。
22.2. CREATE VIEW語法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
該語句能建立新的檢視，如果給定了OR REPLACE子句，該語句還能替換已有的檢視。select_statement是一種SELECT語句，它給出了檢視的定義。該語句可從基表或其他檢視進行選擇。
該語句要求具有針對檢視的CREATE VIEW許可權，以及針對由SELECT語句選擇的每一列上的某些許可權。對於在SELECT語句中其他地方使用的列，必須具有SELECT許可權。如果還有OR REPLACE子句，必須在檢視上具有DROP許可權。
檢視屬於資料庫。在預設情況下，將在當前資料庫建立新檢視。要想在給定資料庫中明確建立檢視，建立時，應將名稱指定為db_name.view_name。
mysql> CREATE VIEW test.v AS SELECT * FROM t;
表和檢視共享資料庫中相同的名稱空間，因此，資料庫不能包含具有相同名稱的表和檢視。
檢視必須具有唯一的列名，不得有重複，就像基表那樣。預設情況下，由SELECT語句檢索的列名將用作檢視列名。要想為檢視列定義明確的名稱，可使用可選的column_list子句，列出由逗號隔開的ID。column_list中的名稱數目必須等於SELECT語句檢索的列數。
SELECT語句檢索的列可以是對錶列的簡單引用。也可以是使用函式、常量值、操作符等的表示式。
對於SELECT語句中不合格的表或檢視，將根據預設的資料庫進行解釋。通過用恰當的資料庫名稱限定表或檢視名，檢視能夠引用表或其他資料庫中的檢視。
能夠使用多種SELECT語句建立檢視。檢視能夠引用基表或其他檢視。它能使用聯合、UNION和子查詢。SELECT甚至不需引用任何表。在下面的示例中，定義了從另一表選擇兩列的檢視，並給出了根據這些列計算的表示式：
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;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+
檢視定義服從下述限制：
· SELECT語句不能包含FROM子句中的子查詢。
· SELECT語句不能引用系統或使用者變數。
· SELECT語句不能引用預處理語句引數。
· 在儲存子程式內，定義不能引用子程式引數或區域性變數。
· 在定義中引用的表或檢視必須存在。但是，建立了檢視後，能夠捨棄定義引用的表或檢視。要想檢查檢視定義是否存在這類問題，可使用CHECK TABLE語句。
· 在定義中不能引用TEMPORARY表，不能建立TEMPORARY檢視。
· 在檢視定義中命名的表必須已存在。
· 不能將觸發程式與檢視關聯在一起。
在檢視定義中允許使用ORDER BY，但是，如果從特定檢視進行了選擇，而該檢視使用了具有自己ORDER BY的語句，它將被忽略。
對於定義中的其他選項或子句，它們將被增加到引用檢視的語句的選項或子句中，但效果未定義。例如，如果在檢視定義中包含LIMIT子句，而且從特定檢視進行了選擇，而該檢視使用了具有自己LIMIT子句的語句，那麼對使用哪個LIMIT未作定義。相同的原理也適用於其他選項，如跟在SELECT關鍵字後的ALL、DISTINCT或SQL_SMALL_RESULT，並適用於其他子句，如INTO、FOR UPDATE、LOCK IN SHARE MODE、以及PROCEDURE。
如果建立了檢視，並通過更改系統變數更改了查詢處理環境，會影響從檢視獲得的結果：
mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
Query OK, 0 rows affected (0.00 sec)
mysql> SET NAMES 'latin1';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| latin1 | latin1_swedish_ci |
+-------------------+---------------------+
1 row in set (0.00 sec)
mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| utf8 | utf8_general_ci |
+-------------------+---------------------+
1 row in set (0.00 sec)
可選的ALGORITHM子句是對標準SQL的MySQL擴充套件。ALGORITHM可取三個值：MERGE、TEMPTABLE或UNDEFINED。如果沒有ALGORITHM子句，預設演算法是UNDEFINED（未定義的）。演算法會影響MySQL處理檢視的方式。
對於MERGE，會將引用檢視的語句的文字與檢視定義合併起來，使得檢視定義的某一部分取代語句的對應部分。
對於TEMPTABLE，檢視的結果將被置於臨時表中，然後使用它執行語句。
對於UNDEFINED，MySQL將選擇所要使用的演算法。如果可能，它傾向於MERGE而不是TEMPTABLE，這是因為MERGE通常更有效，而且如果使用了臨時表，檢視是不可更新的。
明確選擇TEMPTABLE的1個原因在於，建立臨時表之後、並在完成語句處理之前，能夠釋放基表上的鎖定。與MERGE演算法相比，鎖定釋放的速度更快，這樣，使用檢視的其他客戶端不會被遮蔽過長時間。
檢視演算法可以是UNDEFINED，有三種方式：
· 在CREATE VIEW語句中沒有ALGORITHM子句。
· CREATE VIEW語句有1個顯式ALGORITHM = UNDEFINED子句。
· 為僅能用臨時表處理的檢視指定ALGORITHM = MERGE。在這種情況下，MySQL將生成告警，並將演算法設定為UNDEFINED。
正如前面所介紹的那樣，通過將檢視定義中的對應部分合併到引用檢視的語句中，對MERGE進行處理。在下面的示例中，簡要介紹了MERGE的工作方式。在該示例中，假定有1個具有下述定義的檢視v_merge：
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;
示例1：假定發出了下述語句：
SELECT * FROM v_merge;
MySQL以下述方式處理語句：
· v_merge成為t
· *成為vc1、vc2，與c1、c2對應
· 增加檢視WHERE子句
所產生的將執行的語句為：
SELECT c1, c2 FROM t WHERE c3 > 100;
示例2：假定發出了下述語句：
SELECT * FROM v_merge WHERE vc1 < 100;
該語句的處理方式與前面介紹的類似，但vc1 < 100變為c1 < 100，並使用AND連線詞將檢視的WHERE子句新增到語句的WHERE子句中（增加了圓括號以確保以正確的優先順序執行子句部分）。所得的將要執行的語句變為：
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
事實上，將要執行的語句是具有下述形式的WHERE子句：
WHERE (select WHERE) AND (view WHERE)
MERGE演算法要求檢視中的行和基表中的行具有一對一的關係。如果不具有該關係。必須使用臨時表取而代之。如果檢視包含下述結構中的任何一種，將失去一對一的關係：
· 聚合函式（SUM(), MIN(), MAX(), COUNT()等）。
· DISTINCT
· GROUP BY
· HAVING
· UNION或UNION ALL
· 僅引用文字值（在該情況下，沒有基本表）。
某些檢視是可更新的。也就是說，可以在諸如UPDATE、DELETE或INSERT等語句中使用它們，以更新基表的內容。對於可更新的檢視，在檢視中的行和基表中的行之間必須具有一對一的關係。還有一些特定的其他結構，這類結構會使得檢視不可更新。更具體地講，如果檢視包含下述結構中的任何一種，那麼它就是不可更新的：
· 聚合函式（SUM(), MIN(), MAX(), COUNT()等）。
· DISTINCT
· GROUP BY
· HAVING
· UNION或UNION ALL
· 位於選擇列表中的子查詢
· Join
· FROM子句中的不可更新檢視
· WHERE子句中的子查詢，引用FROM子句中的表。
· 僅引用文字值（在該情況下，沒有要更新的基本表）。
· ALGORITHM = TEMPTABLE（使用臨時表總會使檢視成為不可更新的）。
關於可插入性（可用INSERT語句更新），如果它也滿足關於檢視列的下述額外要求，可更新的檢視也是可插入的：
· 不得有重複的檢視列名稱。
· 檢視必須包含沒有預設值的基表中的所有列。
· 檢視列必須是簡單的列引用而不是匯出列。匯出列不是簡單的列引用，而是從表示式匯出的。下面給出了一些匯出列示例：
· 3.14159
· col1 + 3
· UPPER(col2)
· col3 / col4
· (subquery)
混合了簡單列引用和匯出列的檢視是不可插入的，但是，如果僅更新非匯出列，檢視是可更新的。考慮下述檢視：
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
該檢視是不可插入的，這是因為col2是從表示式匯出的。但是，如果更新時不更新col2，它是可更新的。這類更新是允許的：
UPDATE v SET col1 = 0;
下述更新是不允許的，原因在於，它試圖更新匯出列：
UPDATE v SET col2 = 0;
在某些情況下，能夠更新多表檢視，假定它能使用MERGE演算法進行處理。為此，檢視必須使用內部聯合（而不是外部聯合或UNION）。此外，僅能更新檢視定義中的單個表，因此，SET子句必須僅命名檢視中某一表的列。即使從理論上講也是可更新的，不允許使用UNION ALL的檢視，這是因為，在實施中將使用臨時表來處理它們。
對於多表可更新檢視，如果是將其插入單個表中，INSERT能夠工作。不支援DELETE。
對於可更新檢視，可給定WITH CHECK OPTION子句來防止插入或更新行，除非作用在行上的select_statement中的WHERE子句為“真”。
在關於可更新檢視的WITH CHECK OPTION子句中，當檢視是根據另一個檢視定義的時，LOCAL和CASCADED關鍵字決定了檢查測試的範圍。LOCAL關鍵字對CHECK OPTION進行了限制，使其僅作用在定義的檢視上，CASCADED會對將進行評估的基表進行檢查。如果未給定任一關鍵字，預設值為CASCADED。請考慮下述表和檢視集合的定義：
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和v3是根據另一檢視v1定義的。v2具有LOCAL檢查選項，因此，僅會針對v2檢查對插入項進行測試。v3具有CASCADED檢查選項，因此，不僅會針對它自己的檢查對插入項進行測試，也會針對基本檢視的檢查對插入項進行測試。在下面的語句中，介紹了這些差異：
ql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
檢視的可更新性可能會受到系統變數updatable_views_with_limit的值的影響。請參見5.3.3節，“伺服器系統變數”。
INFORMATION_SCHEMA包含1個VIEWS表，從該表可獲取關於檢視物件的資訊。請參見23.1.15節，“INFORMATION_SCHEMA VIEWS表”。
22.3. DROP VIEW語法
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
DROP VIEW能夠刪除1個或多個檢視。必須在每個檢視上擁有DROP許可權。
可以使用關鍵字IF EXISTS來防止因不存在的檢視而出錯。給定了該子句時，將為每個不存在的檢視生成NOTE。請參見13.5.4.22節，“SHOW WARNINGS語法”。
如果給定了RESTRICT和CASCADE，將解析並忽略它們。
22.4. SHOW CREATE VIEW語法
SHOW CREATE VIEW view_name
該語句給出了1個建立給定檢視的CREATE VIEW語句。
mysql> SHOW CREATE VIEW v;
+------+----------------------------------------------------+
| View | Create View |
+------+----------------------------------------------------+
| v | CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` |
+------+----------------------------------------------------+
[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24496749/viewspace-1052771/，如需轉載，請註明出處，否則將追究法律責任。

相關文章