MySQL學習筆記20

標籤：rem   rtrim(   資料   特定   允許   mys   為什麼   nbsp   min   

二十、使用視圖

1、 視圖

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

例如：SELECT cust_name, cust_contact

      FROM customers, orders, orderitems

      WHERE customers.cust_id = orders.cust_id

         AND orderitems.order_num = order.order_num

         AND prod_id = ‘TNT2’;

   此查詢用來檢索訂購了某個特定產品的使用者。可以看到檢索語句比較複雜，另外如果要檢索其他產品的相同資料，必須修改最後的WHERE子句。

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

     SELECT cust_name, cust_contact

     FROM productcustomers

     WHERE prod_id = ‘TNT2’;

 

 

為什麼使用視圖：

•       重用SQL語句
•       簡化複雜的SQL操作
• 　  使用表的組成部分而不是整個表
•       保護資料
•       更改資料格式和表示

視圖僅僅是用來查看儲存在別處的資料的一種設施，視圖本身不包含資料。

 

視圖的規則和限制：

•       與表一樣，視圖必須唯一命名。
•       對於可以建立的視圖數目沒有限制
•       為了建立視圖，必須具有足夠的存取權限
•       視圖可以嵌套。即可以利用從其他視圖中檢索資料的查詢來構造一個視圖
•       ORDER BY可以用在視圖中，但如果從該視圖檢索資料的SELECT語句中也含有ORDER BY，那麼該視圖中的ORDER BY將被覆蓋
•       視圖不能被索引，也不能有關聯的觸發器或預設值
•       視圖可以和表一起使用

2、 使用視圖

視圖的建立：

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

 

（1）、利用視圖簡化複雜的連接

   例如：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，將列出訂購了任意產品的客戶。

 

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

SELECT cust_name, cust_contact

FROM productcustomets

WHERE prod_id = ‘TNT2’;

 

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

 

（2）、用視圖重新格式化檢索出的資料

　　　下面的SELECT語句在單個組合計算資料行中返回供應商名和位置：

　　　SELECT Contact(RTrim(vend_name), ’ (‘, RTrim(vend_country), ‘)’)

           AS vend_title

      FROM vendors

      ORDER BY vend_name;

 

把此語句轉換為視圖，可按如下進 行：
     CREAT VIEW vendorlocations  AS

    SELECT Contact(RTrim(vend_name), ’ (‘, RTrim(vend_country), ‘)’)

          AS vend_title

     FROM vendors

     ORDER BY vend_name;

 

（3）、用視圖過濾不想要的資料

   視圖對於應用普通的WHERE子句也很有用。例如，可以定義customeremaillist視圖，過濾沒有電子郵件的客戶：

  CREATEVIEW customeremaillist AS

  SELECTcust_id, cust_name, cust_email

 FROM customers

 WHERE cust_eamil IS NOT NULL;

（4）、使用視圖與計算欄位

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

例如：檢索某個特定訂單中的物品，計算每種物品的總價格：

SELECT prod_id,

      quantity,

      item_price,

      quantity*item_price AS expanded_price

FROM orderitems

WHERE order_num = 20005;

 

將其轉換為一個視圖：

CREATE VIEW orderitemsexpanded AS

SELECT order_num,

      prod_id,

      quantity,

      item_price,

      quantity*item_price AS expanded_price

FROM orderitems;

（5）、更新視圖

視圖的資料能否更新要視情況而定。

通常視圖是可以更新的（即，可以對他們使用INSERT、UPDATE和DELETE）。更新一個視圖將更新其基表。如果對視圖增加或刪除行，實際上是對基表增加或刪除行。

但是，並非所有視圖都是可更新的。如果MySQL不能正確地確定被更新的基資料，則不允許更新。如果視圖定義以下操作，則不能進行視圖的更新：

•  分組（使用GROUP BY和HAVING）；
•  連接；
•  子查詢；
•  並；
•  聚集合函式（Min()、Count()、Sum()等）；
•  DISTINCT；
•  匯出（計算）列。

MySQL學習筆記20