SQL使用視圖

本文將介紹什么是視圖，它們怎樣工作，何時使用它們。

1. 視圖

視圖是虛擬的表。與包含數據的表不一樣，視圖只包含使用時動態檢索數據的查詢。

說明：SQLite 的視圖

SQLite 僅支持只讀視圖，所以視圖可以創建，可以讀，但其內容不能更改。

理解視圖的最好方法是看例子。下面的SELECT 語句從三個表中檢索數據：

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';

此查詢用來檢索訂購了某種產品的顧客。任何需要這個數據的人都必須理解相關表的結構，知道如何創建查詢和對表進行聯結。檢索其他產品（或多個產品）的相同數據，必須修改最后的WHERE子句。

現在，假如可以把整個查詢包裝成一個名為ProductCustomers的虛擬表，則可以如下輕松地檢索出相同的數據：

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

這就是視圖的作用。ProductCustomers是一個視圖，作為視圖，它不包含任何列或數據，包含的是一個查詢（與上面用以正確聯結表的查詢相同）。

提示：DBMS 的一致支持

我們欣慰地了解到，所有DBMS 非常一致地支持視圖創建語法。

1.1 為什么使用視圖

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

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

創建視圖之后，可以用與表基本相同的方式使用它們。可以對視圖執行SELECT 操作，過濾和排序數據，將視圖聯結到其他視圖或表，甚至添加和更新數據（添加和更新數據存在某些限制，關于這個內容稍后做介紹）。重要的是，要知道視圖僅僅是用來查看存儲在別處數據的一種設施。視圖本身不包含數據，因此返回的數據是從其他表中檢索出來的。在添加或更改這些表中的數據時，視圖將返回改變過的數據。

注意：性能問題

因為視圖不包含數據，所以每次使用視圖時，都必須處理查詢執行時需要的所有檢索。如果你用多個聯結和過濾創建了復雜的視圖或者嵌套了視圖，性能可能會下降得很厲害。因此，在部署使用了大量視圖的應用前，應該進行測試。

1.2 視圖的規則和限制

創建視圖前，應該知道它的一些限制。不過，這些限制隨不同的DBMS而不同，因此在創建視圖時應該查看具體的DBMS 文檔。下面是關于視圖創建和使用的一些最常見的規則和限制。

與表一樣，視圖必須唯一命名（不能給視圖取與別的視圖或表相同的名字）。
對于可以創建的視圖數目沒有限制。
創建視圖，必須具有足夠的訪問權限。這些權限通常由數據庫管理人員授予。
視圖可以嵌套，即可以利用從其他視圖中檢索數據的查詢來構造視圖。所允許的嵌套層數在不同的DBMS中有所不同（嵌套視圖可能會嚴重降低查詢的性能，因此在產品環境中使用之前，應該對其進行全面測試）。
許多DBMS 禁止在視圖查詢中使用ORDER BY 子句。
有些DBMS 要求對返回的所有列進行命名，如果列是計算字段，則需要使用別名（關于列別名的更多信息，請參閱第7 課）。
視圖不能索引，也不能有關聯的觸發器或默認值。
有些DBMS 把視圖作為只讀的查詢，這表示可以從視圖檢索數據，但不能將數據寫回底層表。詳情請參閱具體的DBMS 文檔。
有些DBMS 允許創建這樣的視圖，它不能進行導致行不再屬于視圖的插入或更新。例如有一個視圖，只檢索帶有電子郵件地址的顧客。如果更新某個顧客，刪除他的電子郵件地址，將使該顧客不再屬于視圖。這是默認行為，而且是允許的，但有的DBMS 可能會防止這種情況發生。

提示：參閱具體的DBMS 文檔

上面的規則不少，而具體的DBMS 文檔很可能還包含別的規則。因此，在創建視圖前，有必要花點時間了解必須遵守的規定。

2.創建視圖

理解了什么是視圖以及管理它們的規則和約束后，我們來創建視圖。

視圖用CREATE VIEW語句來創建。與CREATE TABLE一樣，CREATE VIEW只能用于創建不存在的視圖。

說明：視圖重命名

刪除視圖，可以使用DROP語句，其語法為DROP VIEW viewname;。覆蓋（或更新）視圖，必須先刪除它，然后再重新創建。

2.1 利用視圖簡化復雜的聯結

一個最常見的視圖應用是隱藏復雜的SQL，這通常涉及聯結。請看下面的例子：

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，將列出訂購了任意產品的顧客。檢索訂購了產品RGAN01的顧客，可如下進行：

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

輸出結果：

cust_name          cust_contact
------------------ ----------------
Fun4All            Denise L. Stephens
The Toy Store      Kim Howard

分析：?這條語句通過WHERE 子句從視圖中檢索特定數據。當DBMS 處理此查詢時，它將指定的WHERE 子句添加到視圖查詢中已有的WHERE 子句中，以便正確過濾數據。

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

提示：創建可重用的視圖

創建不綁定特定數據的視圖是一種好辦法。例如，上面創建的視圖返回訂購所有產品而不僅僅是RGAN01的顧客（這個視圖先創建）。擴展視圖的范圍不僅使得它能被重用，而且可能更有用。這樣做不需要創建和維護多個類似視圖。

2.2 用視圖重新格式化檢索出的數據

如前所述，視圖的另一常見用途是重新格式化檢索出的數據。下面的SELECT 語句在單個組合計算列中返回供應商名和位置：

ELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

下面是相同的語句，但使用了||語法：

SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

輸出結果：

vend_title
----------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

現在，假設經常需要這個格式的結果。我們不必在每次需要時執行這種拼接，而是創建一個視圖，使用它即可。把此語句轉換為視圖，可按如下進行：

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors;

下面是使用||語法的相同語句：

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
AS vend_title
FROM Vendors;

分析：?這條語句使用與以前SELECT 語句相同的查詢創建視圖。要檢索數據，創建所有的郵件標簽，可如下進行：

SELECT * FROM VendorLocations;

輸出結果：

vend_title
----------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

說明：SELECT 約束全部適用

在這一課的前面提到，各種DBMS 中用來創建視圖的語法相當一致。那么，為什么會有多種創建視圖的語句版本呢？因為視圖只包含一個SELECT 語句，而這個語句的語法必須遵循具體DBMS 的所有規則和約束，所以會有多個創建視圖的語句版本。

2.3 用視圖過濾不想要的數據

視圖對于應用普通的WHERE 子句也很有用。例如，可以定義CustomerEMailList視圖，過濾沒有電子郵件地址的顧客。為此，可使用下面的語句：

CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

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

現在，可以像使用其他表一樣使用視圖CustomerEMailList。

SELECT *
FROM CustomerEMailList;

輸出結果：

cust_id    cust_name          cust_email
---------- ------------------ --------------------------------
1000000001 Village Toys       sales@villagetoys.com
1000000003 Fun4All            jjones@fun4all.com
1000000004 Fun4All            dstephens@fun4all.com

說明：WHERE 子句與WHERE 子句

從視圖檢索數據時如果使用了一條WHERE 子句，則兩組子句（一組在視圖中，另一組是傳遞給視圖的）將自動組合。

2.4 使用視圖與計算字段

在簡化計算字段的使用上，視圖也特別有用。下面是一條SELECT 語句，它檢索某個訂單中的物品，計算每種物品的總價格：

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

輸出結果：

prod_id   quantity   item_price   expanded_price
--------- ---------- ------------ ---------------
RGAN01    5          4.9900       24.9500
BR03      5          11.9900      59.9500
BNBG01    10         3.4900       34.9000
BNBG02    10         3.4900       34.9000
BNBG03    10         3.4900       34.9000

要將其轉換為一個視圖，如下進行：

CREATE VIEW OrderItemsExpanded AS
SELECT order_num,prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM OrderItems;

檢索訂單20008 的詳細內容（上面的輸出），如下進行：

SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;

輸出結果：

order_num   prod_id   quantity   item_price   expanded_price
----------- --------- ---------- ------------ ---------------
20008       RGAN01    5          4.99         24.95
20008       BR03      5          11.99        59.95
20008       BNBG01    10         3.49         34.90
20008       BNBG02    10         3.49         34.90
20008       BNBG03    10         3.49         34.90

可以看到，視圖非常容易創建，而且很好使用。正確使用，視圖可極大地簡化復雜數據的處理。