Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-17   MySQL   瀏覽次數:

MySQL - 第二十六章 | 視圖(VIEW)

視圖

視圖是虛擬的表。與包含資料的表不一樣,視圖只包含使用時動態檢索資料的查詢。
理解視圖的最好方法是看一個例子:

1
2
3
4
5
6
7
8
9
10
11
SELECT
cust_name,
cust_contact
FROM
customers,
orders,
orderitems
WHERE
customers.cust_id = orders.cust_id
AND orders.order_num = orderitems.order_num
AND prod_id = 'TNT2'

此查詢用來檢索訂購了某個特定產品的客戶。任何需要這個資料的人都必須理解相關表的結構,並且知道如何建立查詢和對表進行聯結。為了檢索其他產品(或多個產品)的相同資料,必須修改最後的WHERE子句。

現在,假如可以把整個查詢包裝成一個名為productcustomers的虛擬表,則可以如下輕鬆地檢索出相同的資料:

1
2
3
4
5
6
7
SELECT
cust_name,
cust_contact
FROM
productcustomers
WHERE
prod_id = 'TNT2'

這就是視圖的作用。productcustomers是一個視圖,作為視圖,它不包含表中應該有的任何列或資料,它包含的是一個SQL查詢(與上面用以正確聯結表的相同的查詢)。

為什麼使用視圖

我們已經看到了視圖應用的一個例子。下面是視圖的一些常見應用。

  • 重用SQL語句。
  • 簡化複雜的SQL操作。在編寫查詢後,可以方便地重用它而不必知道它的基本查詢細節。
  • 使用表的組成部分而不是整個表。
  • 保護資料。可以給用戶授予表的特定部分的訪問權限而不是整個表的訪問權限。
  • 更改資料格式和表示。視圖可返回與底層表的表示和格式不同的資料。

在視圖建立之後,可以用與表基本相同的方式利用它們。可以對視圖執行SELECT操作,過濾和排序資料,將視圖聯結到其他視圖或表,甚至能添加和更新資料(添加和更新資料存在某些限制)。

重要的是知道視圖僅僅是用來查看存儲在別處的資料的一種設施。視圖本身不包含資料,因此它們返回的資料是從其他表中檢索出來的。在添加或更改這些表中的資料時,視圖將返回改變過的資料。

注意:性能問題 因為視圖不包含資料,所以每次使用視圖時,都必須處理查詢執行時所需的任一個檢索。如果你用多個聯結和過濾建立了複雜的視圖或者嵌套了視圖,可能會發現性能下降得很厲害。因此,在部署使用了大量視圖的應用前,應該進行測試。

視圖的規則和限制

下面是關於視圖建立和使用的一些最常見的規則和限制。

  • 與表一樣,視圖必須唯一命名(不能給視圖取與別的視圖或表相同的名字)。
  • 對於可以建立的視圖數目沒有限制。
  • 為了建立視圖,必須具有足夠的訪問權限。這些限制通常由資料庫管理人員授予。
  • 視圖可以嵌套,即可以利用從其他視圖中檢索資料的查詢來構造一個視圖。
  • ORDER BY可以用在視圖中,但如果從該視圖檢索資料SELECT中也含有ORDER BY,那麼該視圖中的ORDER BY將被覆蓋。
  • 視圖不能索引,也不能有關聯的觸發器或默認值。
  • 視圖可以和表一起使用。例如,編寫一條聯結表和視圖的SELECT語句。

使用視圖

在理解什麼是視圖(以及管理它們的規則及約束)後,建立視圖。

  • 視圖用CREATE VIEW語句來建立。
  • 使用SHOW CREATE VIEW viewname;來查看建立視圖的語句。
  • 用DROP刪除視圖,其語法為DROP VIEW viewname;。
  • 更新視圖時,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的視圖不存在,則第2條更新語句會建立一個視圖;如果要更新的視圖存在,則第2條更新語句會替換原有視圖。

利用視圖簡化複雜的聯結

視圖的最常見的應用之一是隱藏複雜的SQL,這通常都會涉及聯結。

1
2
3
4
5
6
7
8
9
10
11
12
CREATE VIEW productcustomers AS
SELECT
cust_name,
cust_contact,
prod_id
FROM
customers,
orders,
orderitems
WHERE
customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

分析:這條語句建立一個名為productcustomers的視圖,它聯結三個表,以返回已訂購了任意產品的所有客戶的列表。如果執行SELECT * FROM productcustomers,將列出訂購了任意產品的客戶。

為檢索訂購了產品TNT2的客戶:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT
cust_name,
cust_contact
FROM
productcustomers
WHERE
prod_id = 'TNT2'
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
2 rows in set (0.03 sec)

分析:這條語句通過WHERE子句從視圖中檢索特定資料。在MySQL處理此查詢時,它將指定的WHERE子句添加到視圖查詢中的已有WHERE子句中,以便正確過濾資料。

可以看出,視圖極大地簡化了複雜SQL語句的使用。利用視圖,可一次性編寫基礎的SQL,然後根據需要多次使用。

提示:建立可重用的視圖 建立不受特定資料限制的視圖是一種好辦法。例如,上面建立的視圖返回生產所有產品的客戶而不僅僅是生產TNT2的客戶。擴展視圖的範圍不僅使得它能被重用,而且甚至更有用。這樣做不需要建立和維護多個類似視圖。

用視圖重新格式化檢索出的資料

視圖的另一常見用途是重新格式化檢索出的資料。下面的SELECT語句在單個組合計算列中返回供應商名和位置:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+
6 rows in set (0.03 sec)

現在,假如經常需要這個格式的結果。不必在每次需要時執行聯結,建立一個視圖,每次需要時使用它即可。為把此語句轉換為視圖:

1
2
3
4
5
6
7
8
9
mysql> CREATE VIEW vendorlocations AS
SELECT
Concat(
RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM
vendors
ORDER BY
vend_name;
Query OK, 0 rows affected (0.01 sec)

分析:這條語句使用與以前的SELECT語句相同的查詢建立視圖。為了檢索出以建立所有郵件標籤的資料。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM vendorlocations;
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+
6 rows in set (0.01 sec)

用視圖過濾不想要的資料

視圖對於應用普通的WHERE子句也很有用。例如,可以定義customeremaillist視圖,它過濾沒有電子郵件地址的客戶。

1
2
3
4
5
6
7
8
9
10
mysql> CREATE VIEW customeremailllist AS
SELECT
cust_id,
cust_name,
cust_email
FROM
customers
WHERE
cust_email IS NOT NULL;
Query OK, 0 rows affected (0.03 sec)

分析:顯然,在發送電子郵件到郵件列表時,需要排除沒有電子郵件地址的用戶。這裡的WHERE子句過濾了cust_email列中具有NULL值的那些行,使他們不被檢索出來。

現在,可以像使用其他表一樣使用視圖customeremaillist。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT * FROM customeremailllist;
+---------+----------------+---------------------+
| cust_id | cust_name | cust_email |
+---------+----------------+---------------------+
| 10001 | Coyote Inc. | ylee@coyote.com |
| 10003 | Wascals | rabbit@wascally.com |
| 10004 | Yosemite Place | sam@yosemite.com |
| 10013 | A | ylee@coyote.com |
| 10015 | C | rabbit@wascally.com |
| 10016 | D | sam@yosemite.com |
| 10020 | A | ylee@coyote.com |
| 10022 | C | rabbit@wascally.com |
| 10023 | D | sam@yosemite.com |
+---------+----------------+---------------------+
9 rows in set (0.00 sec)

說明:WHERE子句與WHERE子句 如果從視圖檢索資料時使用了一條WHERE子句,則兩組子句(一組在視圖中,另一組是傳遞給視圖的)將自動組合。

使用視圖與計算欄位

視圖對於簡化計算欄位的使用特別有用。它檢索某個特定訂單中的物品,計算每種物品的總價格:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01 | 10 | 5.99 | 59.90 |
| ANV02 | 3 | 9.99 | 29.97 |
| TNT2 | 5 | 10.00 | 50.00 |
| FB | 1 | 10.00 | 10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.01 sec)

為將其轉換為一個視圖:

1
2
3
4
5
6
7
8
9
10
mysql> CREATE VIEW orderitemsexpanded AS
SELECT
order_num,
prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM
orderitems;
Query OK, 0 rows affected (0.02 sec)

為檢索訂單20005的詳細內容:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM orderitemsexpanded WHERE order_num = 20005;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
| 20005 | ANV01 | 10 | 5.99 | 59.90 |
| 20005 | ANV02 | 3 | 9.99 | 29.97 |
| 20005 | TNT2 | 5 | 10.00 | 50.00 |
| 20005 | FB | 1 | 10.00 | 10.00 |
+-----------+---------+----------+------------+----------------+
4 rows in set (0.01 sec)

視圖非常容易建立,而且很好使用。正確使用,視圖可極大地簡化複雜的資料處理。

更新視圖

迄今為止的所有視圖都是和SELECT語句使用的。然而,視圖的資料能否更新?答案視情況而定。
通常,視圖是可更新的(即,可以對它們使用INSERT、UPDATE和DELETE)。更新一個視圖將更新其基表(視圖本身沒有資料)。如果你對視圖增加或刪除行,實際上是對其基表增加或刪除行。
但是,並非所有視圖都是可更新的。基本上可以說,如果MySQL不能正確地確定被更新的基資料,則不允許更新(包括插入和刪除)。這實際上意味著,如果視圖定義中有以下操作,則不能進行視圖的更新:

  • 分組(使用GROUP BY和HAVING);
  • 聯結;
  • 子查詢;
  • 並;
  • 聚集函數(Min()、Count()、Sum()等);
  • DISTINCT;
  • 導出(計算)列。

許多例子中的視圖都是不可更新的。這聽上去好像是一個嚴重的限制,但實際上不是,因為視圖主要用於資料檢索。

提示:將視圖用於檢索 一般,應該將視圖用於檢索(SELECT語句)而不用於更新(INSERT、UPDATE和DELETE)。

結語

我!完全!沒有用過VIEW!而且是在近期才看到公司的一些專案裡面有用到VIEW,可以把他想像成,寫一個共用的語法,讓大家可以去使用,避免大家各寫各的。

如果真的要用要考慮的就是,如上面提到性能問題,還有用於檢索(SELECT語句)而不用於更新(INSERT、UPDATE和DELETE),這幾點要特別小心和注意。

註:以上參考了
MySQL必知必会 MySQL Crash Course