[[407941]]

本文轉(zhuǎn)載自微信公眾號「數(shù)據(jù)與智能」，作者Alan Beaulieu。轉(zhuǎn)載本文請聯(lián)系數(shù)據(jù)與智能公眾號。

設(shè)計良好的應(yīng)用程序通常會在保持實現(xiàn)細節(jié)私有性的同時公開一個公共接口，從而在不影響終端用戶的情況下支持將來的設(shè)計變動。在設(shè)計數(shù)據(jù)庫時，通過保持表的私有性并允許用戶僅通過一組視圖訪問數(shù)據(jù)，你可以獲得類似的結(jié)果。本章致力于定義什么是視圖、如何創(chuàng)建它們、何時使用它們以及如何使用它們。

1. 什么是視圖

視圖其實就是一種數(shù)據(jù)查詢機制。與表不同，視圖不涉及數(shù)據(jù)存儲，所以不必擔(dān)心視圖會占用磁盤空間?？梢酝ㄟ^命名select語句來創(chuàng)建視圖，將其保存以供其他人使用。其他用戶可以使用該視圖訪問數(shù)據(jù)，就像他們直接查詢表一樣(實際上，他們甚至可能不知道自己正在使用視圖)。

舉一個簡單的例子，假設(shè)你希望部分隱藏customer表中的電子郵件地址。例如，市場營銷部門可能需要訪問電子郵件地址才能發(fā)布促銷廣告，但公司的隱私政策有規(guī)定必須保證這些數(shù)據(jù)的安全。因此不允許直接訪問customer表，而是定義一個名為customer_vw的視圖，并授權(quán)給所有非營銷人員使用以訪問客戶數(shù)據(jù)。視圖定義如下：

CREATE VIEW customer_vw 
 (customer_id, 
 first_name, 
 last_name, 
 email  
 ) 
AS 
SELECT  
 customer_id, 
 first_name, 
 last_name, 
 concat(substr(email,1,2), '*****', substr(email, -4)) email 
FROM customer; 

語句的第一部分列出了視圖的列名，這些列名可能與基礎(chǔ)表的列名不同。語句的第二部分是select語句，它必須為視圖中的每一列提供一個表達式。email列的生成方法是：獲取電子郵件地址的前兩個字符，與“*****”連接，然后與電子郵件地址的最后四個字符連接。

執(zhí)行create view語句時，數(shù)據(jù)庫服務(wù)器只簡單地存儲視圖定義以供將來使用。若不執(zhí)行查詢，也就不會檢索或存儲任何數(shù)據(jù)。創(chuàng)建視圖后，用戶可以像查詢表一樣使用它進行查詢，如下所示：

mysql> SELECT first_name, last_name, email 
 -> FROM customer_vw; 
+-------------+--------------+-------------+ 
| first_name | last_name | email | 
+-------------+--------------+-------------+ 
| MARY | SMITH | MA*****.org | 
| PATRICIA | JOHNSON | PA*****.org | 
| LINDA | WILLIAMS | LI*****.org | 
| BARBARA | JONES | BA*****.org | 
| ELIZABETH | BROWN | EL*****.org | 
... 
| ENRIQUE | FORSYTHE | EN*****.org | 
| FREDDIE | DUGGAN | FR*****.org | 
| WADE | DELVALLE | WA*****.org | 
| AUSTIN | CINTRON | AU*****.org | 
+-------------+--------------+-------------+ 
599 rows in set (0.00 sec) 

盡管customer_vw視圖定義包含customer表的四列，但前面的查詢只檢索其中三列。正如你將在本章后面看到的，如果視圖中的某些列被附加到函數(shù)或子查詢，那么這會是一個重要的區(qū)別。

從用戶的角度來看，視圖看起來就像一個表。要想知道視圖中有哪些列是可用的，可以使用MySQL(或Oracle)的describe命令查看：

mysql> describe customer_vw; 
+-------------+----------------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+-------------+----------------------+------+-----+---------+-------+ 
| customer_id | smallint(5) unsigned | NO | | 0 | | 
| first_name | varchar(45) | NO | | NULL | | 
| last_name | varchar(45) | NO | | NULL | | 
| email | varchar(11) | YES | | NULL | | 
+-------------+----------------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec) 

在通過視圖進行查詢時，可以自由使用select語句中的任何子句，包括group by、having和order by。舉個例子：

mysql> SELECT first_name, count(*), min(last_name), max(last_name) 
 -> FROM customer_vw 
 -> WHERE first_name LIKE 'J%' 
 -> GROUP BY first_name 
 -> HAVING count(*) > 1 
 -> ORDER BY 1; 
+------------+----------+----------------+----------------+ 
| first_name | count(*) | min(last_name) | max(last_name) | 
+------------+----------+----------------+----------------+ 
| JAMIE | 2 | RICE | WAUGH | 
| JESSIE | 2 | BANKS | MILAM | 
+------------+----------+----------------+----------------+ 
2 rows in set (0.00 sec) 

此外，你還可以在查詢中連接視圖到其他表(或者甚至視圖)，如下所示：

mysql> SELECT cv.first_name, cv.last_name, p.amount 
 -> FROM customer_vw cv 
 -> INNER JOIN payment p 
 -> ON cv.customer_id = p.customer_id 
 -> WHERE p.amount >= 11; 
+------------+-----------+--------+ 
| first_name | last_name | amount | 
+------------+-----------+--------+ 
| KAREN | JACKSON | 11.99 | 
| VICTORIA | GIBSON | 11.99 | 
| VANESSA | SIMS | 11.99 | 
| ALMA | AUSTIN | 11.99 | 
| ROSEMARY | SCHMIDT | 11.99 | 
| TANYA | GILBERT | 11.99 | 
| RICHARD | MCCRARY | 11.99 | 
| NICHOLAS | BARFIELD | 11.99 | 
| KENT | ARSENAULT | 11.99 | 
| TERRANCE | ROUSH | 11.99 | 
+------------+-----------+--------+ 
10 rows in set (0.01 sec) 

此查詢將customer_vw視圖與payment表連接，以查找租賃電影花費了11美元或更多金額的客戶。

2. 為何要用視圖

在上一節(jié)中，我演示了一個簡單的視圖，它的目的是掩蓋customer.email列。雖然視圖通常被用于此種目的，但還有更多理由使用視圖，如下小節(jié)所述。

2.1 數(shù)據(jù)安全

如果你創(chuàng)建一個表并允許用戶查詢，那么他們將能夠訪問表中的每一列和每一行數(shù)據(jù)。但正如我前面提到的，你的表中有些列可能包含敏感信息，比如身份證號或信用卡號碼，把包括這些敏感數(shù)據(jù)在內(nèi)的表數(shù)據(jù)公開給用戶訪問絕對不是一個好主意，而且還可能違反公司的隱私政策，甚至觸犯州或聯(lián)邦法律。

對于這些情況，最好的方法是保持表的私有性(即不向任何用戶授予select權(quán)限)，然后創(chuàng)建一個或多個視圖省略或者模糊(比如對customer_vw.email列采取'*****'替代部分內(nèi)容)這些敏感信息。你還可以通過向視圖定義中添加where子句來限制一組用戶只能訪問哪些行。例如，下面的視圖定義將非活躍客戶排除在外：

CREATE VIEW active_customer_vw 
 (customer_id, 
 first_name, 
 last_name, 
 email 
 ) 
AS 
SELECT 
 customer_id, 
 first_name, 
 last_name, 
 concat(substr(email,1,2), '*****', substr(email, -4)) email 
FROM customer 
WHERE active = 1; 

如果將此視圖提供給市場營銷部門，他們將能夠避免向非活躍客戶發(fā)送信息，因為視圖的where子句中的條件將始終包含在查詢中。

注意

Oracle Database用戶還可以用另一種方法保證表中行和列的安全：虛擬私有數(shù)據(jù)庫(Virtual Private Database，VPD)。VPD允許你為表附加策略，然后服務(wù)器將根據(jù)需要修改用戶的查詢以執(zhí)行此策略。例如，如果你制定了策略指定銷售和市場營銷部門的成員只能看到活躍客戶，則條件active=1將添加到他們對customer表的所有查詢中。

2.2 數(shù)據(jù)聚合

報表程序通常需要聚合數(shù)據(jù)，而視圖就是一種實現(xiàn)該功能的很好的方法，可以使數(shù)據(jù)看起來像是已經(jīng)被預(yù)聚合并存儲在數(shù)據(jù)庫中。例如，假設(shè)一個應(yīng)用程序每月生成一個報表，用于顯示每類電影的總銷售額，這樣經(jīng)理就可以決定將哪些新電影添加到庫存中。你可以為他們提供以下視圖，而不是允許其直接針對表編寫查詢：

CREATE VIEW sales_by_film_category 
AS 
SELECT 
 c.name AS category, 
 SUM(p.amount) AS total_sales 
FROM payment AS p 
 INNER JOIN rental AS r ON p.rental_id = r.rental_id 
 INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id 
 INNER JOIN film AS f ON i.film_id = f.film_id 
 INNER JOIN film_category AS fc ON f.film_id = fc.film_id 
 INNER JOIN category AS c ON fc.category_id = c.category_id 
GROUP BY c.name 
ORDER BY total_sales DESC; 

這種方法給數(shù)據(jù)庫設(shè)計者提供很大的靈活性。如果將來某個時候你為了提高查詢性能，要將數(shù)據(jù)預(yù)聚合到表中而不是使用視圖求和，那么你可以創(chuàng)建一個film_category_sales表，用聚合數(shù)據(jù)加載該表，然后修改sales_by_film_category視圖定義以從此表中檢索數(shù)據(jù)。之后，所有使用sales_by_film_category視圖的查詢都將從新的film_category_sales表中檢索數(shù)據(jù)，這意味著用戶無需修改查詢就可以提高性能。

2.3 隱藏復(fù)雜性

部署視圖最常見的原因之一是為了保護終端用戶不受復(fù)雜性的影響。例如，假設(shè)每個月都會創(chuàng)建一個報表以顯示有關(guān)所有電影的信息，以及電影類別、電影中出現(xiàn)的演員數(shù)量、庫存中的總拷貝數(shù)以及每部電影的租賃數(shù)量。你可以提供如下視圖，而不是讓他們使用六個不同的表檢索數(shù)據(jù)：

CREATE VIEW film_stats 
AS 
SELECT f.film_id, f.title, f.description, f.rating, 
 (SELECT c.name 
 FROM category c 
 INNER JOIN film_category fc 
 ON c.category_id = fc.category_id 
 WHERE fc.film_id = f.film_id) category_name, 
 (SELECT count(*) 
 FROM film_actor fa 
 WHERE fa.film_id = f.film_id 
 ) num_actors, 
 (SELECT count(*) 
 FROM inventory i 
 WHERE i.film_id = f.film_id 
 ) inventory_cnt, 
 (SELECT count(*) 
 FROM inventory i 
 INNER JOIN rental r 
 ON i.inventory_id = r.inventory_id 
 WHERE i.film_id = f.film_id 
 ) num_rentals 
FROM film f; 

這個視圖定義很有趣，因為即使可以通過視圖檢索來自六個不同表的數(shù)據(jù)，查詢的from子句也只有一個表(film)。來自其他五個表的數(shù)據(jù)是使用標(biāo)量子查詢生成的。如果有人使用此視圖但未引用category_name、num_actors、inventory_cnt或num_rentals列，則不會執(zhí)行任何子查詢。這種方法允許在不連接其他五個表的情況下使用視圖從film表中提取描述性信息。

2.4 連接分區(qū)數(shù)據(jù)

一些數(shù)據(jù)庫設(shè)計將大型表分解為多個小塊以提高性能。例如，如果payment表變大了，設(shè)計者可能會決定將其分為兩個表：payment_current(保存最近六個月的數(shù)據(jù))和payment_historical(保存六個月前的所有數(shù)據(jù))。如果客戶希望查看某個特定客戶的所有交易，則需要同時查詢這兩個表。但是，通過創(chuàng)建一個視圖以查詢兩個表并組合查詢結(jié)果，可以使其看起來像是所有交易數(shù)據(jù)都存儲在一個表中。視圖定義如下：

CREATE VIEW payment_all 
 (payment_id, 
 customer_id, 
 staff_id, 
 rental_id, 
 amount, 
 payment_date, 
 last_update 
 ) 
AS 
SELECT payment_id, customer_id, staff_id, rental_id, 
 amount, payment_date, last_update 
FROM payment_historic 
UNION ALL 
SELECT payment_id, customer_id, staff_id, rental_id, 
 amount, payment_date, last_update 
FROM payment_current; 

在這種情況下使用視圖是一個好主意，因為它允許設(shè)計人員更改基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)而無需強制所有數(shù)據(jù)庫用戶修改其查詢。

3. 可更新的視圖

如果為用戶提供了一組用于數(shù)據(jù)檢索的視圖，但如果用戶還要修改同一數(shù)據(jù)，又該怎么辦呢?例如，強制用戶使用視圖檢索數(shù)據(jù)，然后允許用戶使用update或insert語句直接修改基礎(chǔ)表——這似乎有點奇怪。為此，MySQL、Oracle Database和SQL Server都允許在遵守某些規(guī)則的前提下通過視圖修改數(shù)據(jù)。對于MySQL，如果滿足以下條件，則視圖是可更新的：

• 不使用聚合函數(shù)(max()、min()、avg()等);
• 視圖不使用group by或having子句;
• select或from子句中不存在子查詢，且where子句中的任何子查詢都不引用from子句中的表;
• 視圖不使用union、union all或distinct;
• from子句至少包含一個表或可更新視圖;
• 如果有多個表或視圖，那么from子句只使用內(nèi)部連接。

為了演示可更新視圖的實用性，我們從簡單的視圖定義開始，然后逐步深入更復(fù)雜的視圖。

3.1 更新簡單視圖

本章開頭的視圖非常簡單，讓我們看看下面這個例子：

CREATE VIEW customer_vw  
 (customer_id,  
 first_name,  
 last_name,  
 email  
 )  
AS  
SELECT  
 customer_id,  
 first_name,  
 last_name,  
 concat(substr(email,1,2), '*****', substr(email, -4)) email  
FROM customer; 

視圖customer_vw查詢單個表，其四列中只有一列是通過表達式派生出來的。此視圖定義沒有違反前面列出的任何限制，因此你可以使用它修改customer表中的數(shù)據(jù)。下面讓我們使用該視圖更新Mary Smith的姓氏(更新為Smith Allen)：

mysql> UPDATE customer_vw 
 -> SET last_name = 'SMITH-ALLEN' 
 -> WHERE customer_id = 1; 
Query OK, 1 row affected (0.11 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

如你所見，該語句修改了一行，但我們還是檢查一下基礎(chǔ)customer表以驗證我們的更新：

mysql> SELECT first_name, last_name, email 
 -> FROM customer 
 -> WHERE customer_id = 1; 
+------------+-------------+-------------------------------+ 
| first_name | last_name | email | 
+------------+-------------+-------------------------------+ 
| MARY | SMITH-ALLEN | MARY.SMITH@sakilacustomer.org | 
+------------+-------------+-------------------------------+ 
1 row in set (0.00 sec) 

雖然可以以這種方式修改視圖中的大多數(shù)列，但卻無法修改email列，因為它是從表達式派生的：

mysql> UPDATE customer_vw 
 -> SET email = 'MARY.SMITH-ALLEN@sakilacustomer.org' 
 -> WHERE customer_id = 1; 
ERROR 1348 (HY000): Column 'email' is not updatable 

這種情況可能并不是一件壞事，因為創(chuàng)建視圖的主要原因是為了隱藏電子郵件地址。

如果要使用customer_vw視圖插入數(shù)據(jù)，那就不太合適了，因為包含派生列的視圖不能用于插入數(shù)據(jù)，即使派生列未包含在語句中。例如，下一條語句嘗試使用customer_vw視圖填充customer_id、first_name和last_name列：

mysql> INSERT INTO customer_vw  
 -> (customer_id,  
 -> first_name,  
 -> last_name)  
 -> VALUES (99999,'ROBERT','SIMPSON');  
ERROR 1471 (HY000): The target table customer_vw of the INSERT is not insertable-into 

現(xiàn)在你已經(jīng)了解了簡單視圖的局限性，下一節(jié)將演示如何使用視圖連接多個表。

3.2 更新復(fù)雜視圖

雖然單表視圖確實很常見，但你遇到的許多視圖都會在基礎(chǔ)查詢的from子句中包含多個表。例如，下一個視圖連接customer、address、city和country表，以便可以方便地查詢客戶的所有數(shù)據(jù)：

CREATE VIEW customer_details  
AS  
SELECT c.customer_id,  
 c.store_id,  
 c.first_name,  
 c.last_name,  
 c.address_id,  
 c.active,  
 c.create_date,  
 a.address,  
 ct.city,  
 cn.country,  
 a.postal_code  
FROM customer c  
 INNER JOIN address a  
 ON c.address_id = a.address_id  
 INNER JOIN city ct  
 ON a.city_id = ct.city_id  
 INNER JOIN country cn  
 ON ct.country_id = cn.country_id; 

你可以使用此視圖更新customer或address表中的數(shù)據(jù)，如下語句所示：

mysql> UPDATE customer_details  
 -> SET last_name = 'SMITH-ALLEN', active = 0  
 -> WHERE customer_id = 1;  
Query OK, 1 row affected (0.10 sec)  
Rows matched: 1 Changed: 1 Warnings: 0  
mysql> UPDATE customer_details  
 -> SET address = '999 Mockingbird Lane'  
 -> WHERE customer_id = 1;  
Query OK, 1 row affected (0.06 sec)  
Rows matched: 1 Changed: 1 Warnings: 0 

第一個語句修改customer.last_name以及customer.active列，而第二條語句修改address.address列。你可能想知道，如果嘗試在一條語句中更新兩個表中的列，會發(fā)生什么情況，下面讓我們看看：

mysql> UPDATE customer_details 
 -> SET last_name = 'SMITH-ALLEN', 
 -> active = 0, 
 -> address = '999 Mockingbird Lane' 
 -> WHERE customer_id = 1; 
ERROR 1393 (HY000): Can not modify more than one base table  
 through a join view 'sakila.customer_details' 

如你所見，使用單個語句中修改兩個基礎(chǔ)表是不可行的，只能分別修改。接下來讓我們嘗試在兩個表中插入一些新客戶(customer_id=9998和9999)的數(shù)據(jù)：

mysql> INSERT INTO customer_details 
 -> (customer_id, store_id, first_name, last_name, 
 -> address_id, active, create_date) 
 -> VALUES (9998, 1, 'BRIAN', 'SALAZAR', 5, 1, now()); 
Query OK, 1 row affected (0.23 sec) 

此語句只填充customer表中的列并且工作正常。下面看一下如果擴展列列表以同時包含address表中的列會發(fā)生什么：

mysql> INSERT INTO customer_details  
 -> (customer_id, store_id, first_name, last_name,  
 -> address_id, active, create_date, address)  
 -> VALUES (9999, 2, 'THOMAS', 'BISHOP', 7, 1, now(),  
 -> '999 Mockingbird Lane');  
ERROR 1393 (HY000): Can not modify more than one base table   
 through a join view 'sakila.customer_details' 

此版本的語句包含跨兩個不同表的列，結(jié)果拋出異常。為了通過復(fù)雜視圖插入數(shù)據(jù)，你需要知道每個列的來源。由于創(chuàng)建視圖的目的是向終端用戶隱藏復(fù)雜性，因此如果用戶要熟悉視圖定義，那么這似乎就達不到隱藏復(fù)雜性的目的了。

注意

Oracle Database和SQL Server也允許通過視圖插入和更新數(shù)據(jù)，但與MySQL一樣有許多限制。如果你打算愿意編寫一些PL/SQL或Transact-SQL語句，那么可以使用名為替代觸發(fā)器(instead-of triggers)的功能，它允許你截取視圖的insert、update和delete語句，并編寫自定義代碼將這些更改包括在內(nèi)。如果沒有這種類型的功能，通常會有太多的限制，使得通過視圖進行更新成為非平凡應(yīng)用程序中的可行策略。