SQL 視圖簡介

• 一、視圖和表
• 二、創建視圖的方法
• 三、視圖的限制 ①——定義視圖時不能使用 ORDER BY 子句
• 四、視圖的限制 ② ——對視圖進行更新
• 五、洗掉視圖
• 請參閱

學習重點

• 從 SQL 的角度來看，視圖和表是相同的，兩者的區別在于表中保存的是實際的資料，而視圖中保存的是 SELECT 陳述句（視圖本身并不存盤資料），

• 使用視圖，可以輕松完成跨多表查詢資料等復雜操作，

• 可以將常用的 SELECT 陳述句做成視圖來使用，

• 創建視圖需要使用 CREATE VIEW 陳述句，

• 視圖包含“不能使用 ORDER BY ”和“可對其進行有限制的更新”兩項限制，

• 洗掉視圖需要使用 DROP VIEW 陳述句，

一、視圖和表

我們首先要學習的是一個新的工具——視圖，

KEYWORD

• 視圖

視圖究竟是什么呢？如果用一句話概述的話，就是“從 SQL 的角度來看視圖就是一張表”，實際上，在 SQL 陳述句中并不需要區分哪些是表，哪些是視圖，只需要知道在更新時它們之間存在一些不同就可以了，這一點之后會為大家進行介紹，至少在撰寫 SELECT 陳述句時并不需要特別在意表和視圖有什么不同，

那么視圖和表到底有什么不同呢？區別只有一個，那就是“是否保存了實際的資料”，

通常，我們在創建表時，會通過 INSERT 陳述句將資料保存到資料庫之中，而資料庫中的資料實際上會被保存到計算機的存盤設備（通常是硬碟）中，因此，我們通過 SELECT 陳述句查詢資料時，實際上就是從存盤設備（硬碟）中讀取資料，進行各種計算之后，再將結果回傳給用戶這樣一個程序，

但是使用視圖時并不會將資料保存到存盤設備之中，而且也不會將資料保存到其他任何地方，實際上視圖保存的是 SELECT 陳述句（圖 1），我們從視圖中讀取資料時，視圖會在內部執行該 SELECT 陳述句并創建出一張臨時表，

• 視圖的優點

  視圖的優點大體有兩點，第一點是由于視圖無需保存資料，因此可以節省存盤設備的容量，例如，我們在 資料的插入 中創建了用來匯總商品種類（product_type）的表，由于該表中的資料最終都會保存到存盤設備之中，因此會占用存盤設備的資料空間，但是，如果把同樣的資料作為視圖保存起來的話，就只需要代碼清單 1 那樣的 SELECT 陳述句就可以了，這樣就節省了存盤設備的資料空間，

  代碼清單 1　通過視圖等 SELECT 陳述句保存資料

  SELECT product_type, SUM(sale_price), SUM(purchase_price)
  FROM Product
  GROUP BY product_type;
  

  由于本示例中表的資料量充其量只有幾行，所以使用視圖并不會大幅縮小資料的大小，但是在實際的業務中資料量往往非常大，這時使用視圖所節省的容量就會非常可觀了，

  法則 1

  表中存盤的是實際資料，而視圖中保存的是從表中取出資料所使用的 SELECT 陳述句，

  第二個優點就是可以將頻繁使用的 SELECT 陳述句保存成視圖，這樣就不用每次都重新書寫了，創建好視圖之后，只需在 SELECT 陳述句中進行呼叫，就可以方便地得到想要的結果了，特別是在進行匯總以及復雜的查詢條件導致 SELECT 陳述句非常龐大時，使用視圖可以大大提高效率，

  而且，視圖中的資料會隨著原表的變化自動更新，視圖歸根到底就是 SELECT 陳述句，所謂“參照視圖”也就是“執行 SELECT 陳述句”的意思，因此可以保證資料的最新狀態，這也是將資料保存在表中所不具備的優勢 ^[1]，

  法則 2

  應該將經常使用的 SELECT 陳述句做成視圖，

二、創建視圖的方法

創建視圖需要使用 CREATE VIEW 陳述句，其語法如下所示，

KEYWORD

• CREATE VIEW 陳述句

語法 1　創建視圖的 CREATE VIEW 陳述句

CREATE VIEW 視圖名稱(<視圖列名1>, <視圖列名2>, ……)
AS
<select陳述句>

SELECT 陳述句需要書寫在 AS 關鍵字之后，SELECT 陳述句中列的排列順序和視圖中列的排列順序相同，SELECT 陳述句中的第 1 列就是視圖中的第 1 列，SELECT 陳述句中的第 2 列就是視圖中的第 2 列，以此類推，視圖的列名在視圖名稱之后的串列中定義，

備忘

接下來，我們將會以此前使用的 Product（商品）表為基礎來創建視圖，如果大家已經根據之前章節的內容更新了 Product 表中的資料，請在創建視圖之前將資料恢復到初始狀態，操作步驟如下>所示，

① 洗掉 Product 表中的資料，將表清空

DELETE FROM Product;

② 執行代碼清單 6（表的洗掉和更新）中的 SQL 陳述句，將資料插入到空表 Product 中

下面就讓我們試著來創建視圖吧，和此前一樣，這次我們還是將 Product 表（代碼清單 2）作為基本表，

代碼清單 2　ProductSum 視圖

這樣我們就在資料庫中創建出了一幅名為 ProductSum（商品合計）的視圖，請大家一定不要省略第 2 行的關鍵字 AS，這里的 AS 與定義別名時使用的 AS 并不相同，如果省略就會發生錯誤，雖然很容易混淆，但是語法就是這么規定的，所以還是請大家牢記，

接下來，我們來學習視圖的使用方法，視圖和表一樣，可以書寫在 SELECT 陳述句的 FROM 子句之中（代碼清單 3），

代碼清單 3　使用視圖

執行結果

 product_type | cnt_product
--------------+------------
 衣服         |           2
 辦公用品     |           2
 廚房用具     |           4

通過上述視圖 ProductSum 定義的主體（SELECT 陳述句）我們可以看出，該視圖將根據商品種類（product_type）匯總的商品數量（cnt_product）作為結果保存了起來，這樣如果大家在作業中需要頻繁進行匯總時，就不用每次都使用 GROUP BY 和 COUNT 函式寫 SELECT 陳述句來從 Product 表中取得資料了，創建出視圖之后，就可以通過非常簡單的 SELECT 陳述句，隨時得到想要的匯總結果，并且如前所述，Product 表中的資料更新之后，視圖也會自動更新，非常靈活方便，

之所以能夠實作上述功能，是因為視圖就是保存好的 SELECT 陳述句，定義視圖時可以使用任何 SELECT 陳述句，既可以使用 WHERE、GROUP BY、HAVING，也可以通過 SELECT * 來指定全部列，

• 使用視圖的查詢

  在 FROM 子句中使用視圖的查詢，通常有如下兩個步驟：

  ① 首先執行定義視圖的 SELECT 陳述句

  ② 根據得到的結果，再執行在 FROM 子句中使用視圖的 SELECT 陳述句

  也就是說，使用視圖的查詢通常需要執行 2 條以上的 SELECT 陳述句 ^[2]，

  這里沒有使用“2 條”而使用了“2 條以上”，是因為還可能出現以視圖為基礎創建視圖的多重視圖（圖 2），例如，我們可以像代碼清單 4 那樣以 ProductSum 為基礎創建出視圖 ProductSumJim，

  KEYWORD

  • 多重視圖

  

  代碼清單 4　視圖 ProductSumJim

  

  -- 確認創建好的視圖
  SELECT product_type, cnt_product
  FROM ProductSumJim;
  

  執行結果

  product_type | cnt_product
  --------------+------------
  辦公用品     |           2
  

  雖然語法上沒有錯誤，但是我們還是應該盡量避免在視圖的基礎上創建視圖，這是因為對多數 DBMS 來說，多重視圖會降低 SQL 的性能，因此，希望大家（特別是剛剛接觸視圖的讀者）能夠使用單一視圖，

  法則 3

  應該避免在視圖的基礎上創建視圖，

  除此之外，在使用時還要注意視圖有兩個限制，接下來會給大家詳細介紹，

三、視圖的限制 ①——定義視圖時不能使用 ORDER BY 子句

雖然之前我們說過在定義視圖時可以使用任何 SELECT 陳述句，但其實有一種情況例外，那就是不能使用 ORDER BY 子句，因此下述視圖定義陳述句是錯誤的，

為什么不能使用 ORDER BY 子句呢？這是因為視圖和表一樣，資料行都是沒有順序的，實際上，有些 DBMS 在定義視圖的陳述句中是可以使用 ORDER BY 子句的 ^[3]，但是這并不是通用的語法，因此，在定義視圖時請不要使用 ORDER BY 子句，

法則 4

定義視圖時不要使用 ORDER BY 子句，

四、視圖的限制 ② ——對視圖進行更新

之前我們說過，在 SELECT 陳述句中視圖可以和表一樣使用，那么，對于 INSERT、DELETE、UPDATE 這類更新陳述句（更新資料的 SQL）來說，會怎么樣呢？

實際上，雖然這其中有很嚴格的限制，但是某些時候也可以對視圖進行更新，標準 SQL 中有這樣的規定：如果定義視圖的 SELECT 陳述句能夠滿足某些條件，那么這個視圖就可以被更新，下面就給大家列舉一些比較具有代表性的條件，

① SELECT 子句中未使用 DISTINCT

② FROM 子句中只有一張表

③ 未使用 GROUP BY 子句

④ 未使用 HAVING 子句

在前幾章的例子中，FROM 子句里通常只有一張表，因此，大家可能會覺得 ② 中的條件有些奇怪，但其實 FROM 子句中也可以并列使用多張表，大家在學習完 SQL 聯結 的操作之后就明白了，

其他的條件大多數都與聚合有關，簡單來說，像這次的例子中使用的 ProductSum 那樣，使用視圖來保存原表的匯總結果時，是無法判斷如何將視圖的更改反映到原表中的，

例如，對 ProductSum 視圖執行如下 INSERT 陳述句，

INSERT INTO ProductSum VALUES ('電器制品', 5);

但是，上述 INSERT 陳述句會發生錯誤，這是因為視圖 ProductSum 是通過 GROUP BY 子句對原表進行匯總而得到的，為什么通過匯總得到的視圖不能進行更新呢？

視圖歸根結底還是從表派生出來的，因此，如果原表可以更新，那么視圖中的資料也可以更新，反之亦然，如果視圖發生了改變，而原表沒有進行相應更新的話，就無法保證資料的一致性了，

使用前述 INSERT 陳述句，向視圖 ProductSum 中添加資料 (' 電器制品 ',5) 時，原表 Product 應該如何更新才好呢？按理說應該向表中添加商品種類為“電器制品”的 5 行資料，但是這些商品對應的商品編號、商品名稱和銷售單價等我們都不清楚（圖 3），資料庫在這里就遇到了麻煩，

法則 5

視圖和表需要同時進行更新，因此通過匯總得到的視圖無法進行更新，

• 能夠更新視圖的情況

  像代碼清單 5 這樣，不是通過匯總得到的視圖就可以進行更新，

  代碼清單 5　可以更新的視圖

  

  對于上述只包含辦公用品類商品的視圖 ProductJim 來說，就可以執行類似代碼清單 6 這樣的 INSERT 陳述句，

  代碼清單 6　向視圖中添加資料行

  

注意事項

由于 PostgreSQL 中的視圖會被初始設定為只讀，所以執行代碼清單 6 中的 INSERT 陳述句時，會發生下面這樣的錯誤，

執行結果（使用 PostgreSQL）

ERROR:  不能向視圖中插入資料
HINT:  需要一個無條件的ON INSERT DO INSTEAD規則

這種情況下，在 INSERT 陳述句執行之前，需要使用代碼清單 A 中的指令來允許更新操作，在 DB2 和 MySQL 等其他 DBMS 中，并不需要執行這樣的指令，

代碼清單 A　允許 PostgreSQL 對視圖進行更新

PostgreSQL

CREATE OR REPLACE RULE insert_rule
AS ON INSERT
TO  ProductJim DO INSTEAD
INSERT INTO Product VALUES (
           new.product_id,
           new.product_name,
           new.product_type,
           new.sale_price,
           new.purchase_price,
           new.regist_date);

下面讓我們使用 SELECT 陳述句來確認資料行是否添加成功吧，

• 視圖

  -- 確認資料是否已經添加到視圖中
  SELECT * FROM ProductJim;
  

  執行結果

  

• 原表

  -- 確認資料是否已經添加到原表中
  SELECT * FROM Product;
  

  執行結果

  

  UPDATE 陳述句和 DELETE 陳述句當然也可以像操作表時那樣正常執行，但是對于原表來說卻需要設定各種各樣的約束（主鍵和 NOT NULL 等），需要特別注意，

五、洗掉視圖

洗掉視圖需要使用 DROP VIEW 陳述句，其語法如下所示，

KEYWORD

• DROP VIEW 陳述句

語法 2　洗掉視圖的 DROP VIEW 陳述句

DROP VIEW 視圖名稱(<視圖列名1>, <視圖列名2>, ……)

例如，想要洗掉視圖 ProductSum 時，就可以使用代碼清單 7 中的 SQL 陳述句，

代碼清單 7　洗掉視圖

DROP VIEW ProductSum;

特定的 SQL

在 PostgreSQL 中，如果洗掉以視圖為基礎創建出來的多重視圖，由于存在關聯的視圖，因此會發生如下錯誤，

執行結果（使用 PostgreSQL）

ERROR:   由于存在關聯視圖，因此無法洗掉視圖productsum
DETAIL:  視圖productsumjim與視圖productsum相關聯
HINT:    洗掉關聯物件請使用DROP…CASCADE

這時可以像下面這樣，使用 CASCADE 選項來洗掉關聯視圖，

PostgreSQL

DROP VIEW ProductSum CASCADE;

備忘

下面我們再次將 Product 表恢復到初始狀態（8 行），請執行如下 DELETE 陳述句，洗掉之前添加的 1 行資料，

代碼清單 B

-- 洗掉商品編號為0009（印章）的資料
DELETE FROM Product WHERE product_id = '0009';

請參閱

• 視圖
• 子查詢
• 關聯子查詢

（完）
</select陳述句>

標籤：SQL Server

上一篇：SQL 事務簡介

下一篇：SQL 子查詢簡介