MySQL必知應會-第22章-使用視圖

標籤：from   為什麼   min   insert   使用者   資料   預設   函數   mail   

第22章-使用視圖

本章將介紹視圖究竟是什麼，它們怎樣工作，何時使用它們。我們還將看到如何利用視圖簡化前面章節中執行的某些SQL操作。

22.1 視圖

需要MySQL 5 MySQL 5添加了對視圖的支援。因此，本章內容適用於MySQL 5及以後的版本。視圖是虛擬表。與包含資料的表不一樣，視圖只包含使用時動態檢索資料的查詢。理解視圖的最好方法是看一個例子。第15章中用下面的SELECT語句從3個表中檢索資料：

此查詢用來檢索訂購了某個特定產品的客戶。任何需要這個資料的人都必須理解相關表的結構，並且知道如何建立查詢和對錶進行連接。為了檢索其他產品（或多個產品）的相同資料，必須修改最後的WHERE子句。現在，假如可以把整個查詢封裝成一個名為productcustomers的虛擬表，則可以如下輕鬆地檢索出相同的資料：
select cust_name,cust_contact from productcustomers where prod_id=’TNT2’;
這就是視圖的作用。 productcustomers是一個視圖，作為視圖，它不包含表中應該有的任何列或資料，它包含的是一個SQL查詢（與上面用以正確連接表的相同的查詢）。

22.1.1 為什麼使用視圖

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

• 重用SQL語句。
• 簡化複雜的SQL操作。在編寫查詢後，可以方便地重用它而不必知道它的基本查詢細節。
• 使用表的組成部分而不是整個表。
• 保護資料。可以給使用者授予表的特定部分的存取權限而不是整個表的存取權限。
• 更改資料格式和表示。視圖可返回與底層表的表示和格式不同的資料。在視圖建立之後，可以用與表基本相同的方式利用它們。可以對視圖執行SELECT操作，過濾和排序資料，將視圖連接到其他視圖或表，甚至能添加和更新資料（添加和更新資料存在某些限制。關於這個內容稍後還要做進一步的介紹）。重要的是知道視圖僅僅是用來查看儲存在別處的資料的一種設施。視圖本身不包含資料，因此它們返回的資料是從其他表中檢索出來的。在添加或更改這些表中的資料時，視圖將返回改變過的資料。效能問題 因為視圖不包含資料，所以每次使用視圖時，都必須處理查詢執行時所需的任一個檢索。如果你用多個連接和過濾建立了複雜的視圖或者嵌套了視圖，可能會發現效能下降得很厲害。因此，在部署使用了大量視圖的應用前，應該進行測試。

22.1.2 視圖的規則和限制

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

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

22.2 使用視圖

在理解什麼是視圖（以及管理它們的規則及約束）後，我們來看一下視圖的建立。

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

22.2.1 利用視圖簡化複雜的連接

視圖的最常見的應用之一是隱藏複雜的SQL，這通常都會涉及連接。請看下面的例子：

這條語句建立一個名為productcustomers的視圖， 它連接三個表，以返回已訂購了任意產品的所有客戶的列表。如果執行SELECT * FROM productcustomers，將列出訂購了任意產品的客戶。為檢索訂購了產品TNT2的客戶，可如下進行：

這條語句通過WHERE子句從視圖中檢索特定資料。在MySQL處理此查詢時，它將指定的WHERE子句添加到視圖查詢中的已有WHERE子句中，以便正確過濾資料。可以看出，視圖極大地簡化了複雜SQL語句的使用。利用視圖，可一次性編寫基礎的SQL，然後根據需要多次使用。建立可重用的視圖 建立不受特定資料限制的視圖是一種好辦法。例如，上面建立的視圖返回生產所有產品的客戶而不僅僅是生產TNT2的客戶。擴充視圖的範圍不僅使得它能被重用，而且甚至更有用。這樣做不需要建立和維護多個類似視圖。

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

如上所述，視圖的另一常見用途是重新格式化檢索出的資料。下面的SELECT語句（來自第10章）在單個組合計算資料行中返回供應商名和位置：

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

這條語句使用與以前的SELECT語句相同的查詢建立視圖。為了檢索出以建立所有郵件標籤的資料，可如下進行：

22.2.3 用視圖過濾不想要的資料

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

顯然，在寄送電子郵件到郵件清單時，需要排除沒有電子郵件地址的使用者。這裡的WHERE子句過濾了cust_email列中具有NULL值的那些行，使他們不被檢索出來。現在，可以像使用其他表一樣使用視圖customeremaillist。

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

22.2.4 使用視圖與計算欄位

視圖對於簡化計算欄位的使用特別有用。下面是第10章中介紹的一條SELECT語句。它檢索某個特定訂單中的物品，計算每種物品的總價格：

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

分析輸出輸出21321422.2 使用視圖 161為檢索訂單20005的詳細內容（上面的輸出），如下進行：

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

22.2.5 更新視圖

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

• 分組（使用GROUP BY和HAVING）；
• 連接；
• 子查詢；
• 並；
• 聚集合函式（Min()、 Count()、 Sum()等）；
• DISTINCT；
• 匯出（計算）列。換句話說，本章許多例子中的視圖都是不可更新的。這聽上去好像是一個嚴重的限制，但實際上不是，因為視圖主要用於資料檢索。可能的變動 上面列出的限制自MySQL 5以來是正確的。不過，未來的MySQL很可能會取消某些限制。將視圖用於檢索 一般，應該將視圖用於檢索（ SELECT語句）而不用於更新（ INSERT、 UPDATE和DELETE）。

22.3 小結

視圖為虛擬表。它們包含的不是資料而是根據需要檢索資料的查詢。視圖提供了一種MySQL的SELECT語句層次的封裝，可用來簡化資料216 處理以及重新格式化基礎資料或保護基礎資料。

MySQL必知應會-第22章-使用視圖