首頁 / CompScience / Database / MySQL / MySQL CREATE VIEW

MySQL CREATE VIEW

2013-06-04_165400

這是MySQL 5實力養成暨評量裡的8-06.『下列對於檢視表(View)的敘述,何者正確?』

答案:(A) SELECT語句中的FROM子句,不能包含子查詢 (B) SELECT語句不能引用系統變數

請直接參考MySQL 5.7 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.16 CREATE VIEW Syntax

MySQL 5.1参考手册 :: 22. 视图::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

>  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資料表」

Print Friendly, PDF & Email
馬上成為工作達人的Fans

About ㄚ琪

工作達人Fun Taiwan的創辦者及總編,可以在這裡更認識他。

發表迴響

你的電子郵件位址並不會被公開。 Required fields are marked *

*

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料

Scroll To Top