22. 使用MySQL之使用檢視

hisun9發表於2024-11-09
MySql

1. 檢視

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

舉個例子來理解檢視：

  • 第15章中用下面的SELECT語句從3個表中檢索資料：

    select cust_name, cust_contact
from customers
where cust_id in (select cust_id 
                    from orders
                    where order_num in (select order_num 
                                        from orderitems
                                        where prod_id = 'TNT2'));

    此查詢用來檢索訂購了某個特定產品的客戶。任何需要這個資料的人都必須理解相關表的結構，並且知道如何建立查詢和對錶進行聯結。為了檢索其他產品（或多個產品）的相同資料，必須修改最後的WHERE子句。

  • 現在，假如可以把整個查詢包裝成一個名為productcustomers的虛擬表，則可以如下輕鬆地檢索出相同的資料：

    select cust_name, cust_contact
from productcustomers
where prod_id = 'TNT2';

  • 這就是檢視的作用。productcustomers是一個檢視，作為檢視，它不包含表中應該有的任何列或資料，它包含的是一個SQL查詢（與上面用以正確聯結表的相同的查詢）。

1.1 為什麼使用檢視

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

  • 重用SQL語句。

  • 簡化複雜的SQL操作。在編寫查詢後，可以方便地重用它而不必知道它的基本查詢細節。

  • 使用表的組成部分而不是整個表。

  • 保護資料。可以給使用者授予表的特定部分的訪問許可權而不是整個表的訪問許可權。

  • 更改資料格式和表示。檢視可返回與底層表的表示和格式不同的資料。

在檢視建立之後，可以用與表基本相同的方式利用它們。可以對檢視執行SELECT操作，過濾和排序資料，將檢視聯結到其他檢視或表，甚至能新增和更新資料（新增和更新資料存在某些限制。關於這個內容稍後還要做進一步的介紹）。

重要的是知道檢視僅僅是用來檢視儲存在別處的資料的一種設施。檢視本身不包含資料，因此它們返回的資料是從其他表中檢索出來的。在新增或更改這些表中的資料時，檢視將返回改變過的資料。

補充：

  • 效能問題：

    因為檢視不包含資料，所以每次使用檢視時，都必須處理查詢執行時所需的任一個檢索。如果用多個聯結和過濾建立了複雜的檢視或者巢狀了檢視，可能會發現效能下降得很厲害。因此，在部署使用了大量檢視的應用前，應該進行測試。

1.2 檢視的規則和限制

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

  • 與表一樣，檢視必須唯一命名（不能給檢視取與別的檢視或表相同的名字）。

  • 對於可以建立的檢視數目沒有限制。

  • 為了建立檢視，必須具有足夠的訪問許可權。這些限制通常由資料庫管理人員授予。

  • 檢視可以巢狀，即可以利用從其他檢視中檢索資料的查詢來構造一個檢視。

  • ORDER BY可以用在檢視中，但如果從該檢視檢索資料SELECT中也含有ORDER BY，那麼該檢視中的ORDER BY將被覆蓋。

  • 檢視不能索引，也不能有關聯的觸發器或預設值。

  • 檢視可以和表一起使用。例如，編寫一條聯結表和檢視的SELECT
    語句。

2. 使用檢視

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

  • 檢視用CREATE VIEW語句來建立。

  • 使用SHOW CREATE VIEW viewname;來檢視建立檢視的語句。

  • 用DROP刪除檢視，其語法為DROP VIEW viewname;。

  • 更新檢視時，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的檢視不存在，則第2條更新語句會建立一個檢視；如果要更新的檢視存在，則第2條更新語句會替換原有檢視。

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;

    img

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

  • 為檢索訂購了產品TNT2的客戶，可如下：

    select cust_name, cust_contact
from productcustomers
where prod_id = 'TNT2';

    輸出如下：

    img

    這條語句透過WHERE子句從檢視中檢索特定資料。在MySQL處理此查詢時，它將指定的WHERE子句新增到檢視查詢中的已有WHERE子句中，以便正確過濾資料。

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

補充：

  • 建立可重用的檢視：

    建立不受特定資料限制的檢視是一種好辦法。例如，上面建立的檢視返回生產所有產品的客戶而不僅僅是生產TNT2的客戶。擴充套件檢視的範圍不僅使得它能被重用，而且甚至更有用。這樣做不需要建立和維護多個類似檢視

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

如上所述，檢視的另一常見用途是重新格式化檢索出的資料。

舉例說明：

  • 下面的SELECT語句（來自第10章）在單個組合計算列中返回供應商名和位置：

    select concat(rtrim(vend_name), ' (', rtrim(vend_country), ')')
        as vend_title
from vendors
order by vend_name;

    輸出如下：

    img

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

    create view vendorlocations as 
select concat(rtrim(vend_name), ' (', rtrim(vend_country), ')')
        as vend_title
from vendors
order by vend_name;

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

    select * from vendorlocations;

    輸出如下：

    img

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;

    輸出如下：

    img

補充：

  • 0WHERE子句與WHERE子句：

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

2.4 使用檢視與計算欄位

檢視對於簡化計算欄位的使用特別有用。

舉例說明

  • 下面是第10章中介紹的一條SELECT語句。它檢索某個特定訂單中的物品，計算每種物品的總價格：

    select prod_id, quantity, item_price, quantity * item_price as expanded_price
from orderitems
where order_num = 20005;

    輸出如下：

    img

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

    create view orderitemsexpanded as
select order_num,
        prod_id, 
        quantity, 
        item_price, 
        quantity * item_price as expanded_price
from orderitems;

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

    select * 
from orderitemsexpanded
where order_num = 20005;

    輸出如下：

    img

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

2.5 更新檢視

迄今為止的所有檢視都是和SELECT語句使用的。

然而，檢視的資料能否更新？

答案視情況而定。

  • 通常，檢視是可更新的（即，可以對它們使用INSERT、UPDATE和DELETE）。

    更新一個檢視將更新其基表（可以回憶一下，檢視本身沒有資料）。如果對檢視增加或刪除行，實際上是對其基表增加或刪除行。

  • 但是，並非所有檢視都是可更新的。

    基本上可以說，如果MySQL不能正確地確定被更新的基資料，則不允許更新（包括插入和刪除）。這實際上意味著，如果檢視定義中有以下操作，則不能進行檢視的更新：

    • 分組（使用GROUP BY和HAVING）；

    • 聯結；

    • 子查詢；

    • 並；

    • 聚集函式（Min()、Count()、Sum()等）；

    • DISTINCT；

    • 匯出（計算）列。

    換句話說，本章許多例子中的檢視都是不可更新的。這聽上去好像是一個嚴重的限制，但實際上不是，因為檢視主要用於資料檢索。

補充：

  • 可能的變動：

    上面列出的限制自MySQL 5以來是正確的。不過，未來的MySQL很可能會取消某些限制。

  • 將檢視用於檢索：

    一般，應該將檢視用於檢索（SELECT語句）而不用於更新（INSERT、UPDATE和DELETE）。

相關文章