歡迎！您似乎正在使用廣告攔截器。請考慮通過禁用您的廣告攔截器來支持我。

J.J.'s Blogs

Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-17   MySQL   瀏覽次數：次   DMCA.com Protection Status {{moment(1594949400000).fromNow()}}

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

本文由J.J. Huang 創作，採用CC BY 3.0 TW協議 進行許可。可自由轉載、引用，但需署名作者且註明文章出處。