MySQL基礎備忘（2）之視圖

　　使用過微軟Access小型數據庫管理系統的朋友可能永遠不會忘記查詢的概念，它也是二級Access考試的重中之重。掌握了查詢部分，二級過關就有了絕對把握。查詢之所以重要，其中一個主要原因在于其靈活性。創建數據庫表初期，我們可以建立一些基礎表格，但是由于現實需求的復雜性，很難一下設計出符合所有需求的所有表格，例如有時需要基于一些字段很多的大表創建一些臨時性的若干“子表”。這種所謂的‘子表’，就可以使用查詢來設計實現。從本質上講，這種查詢只是存儲了創建這些查詢的ＳＱＬ語句，并沒有生成獨立的查詢對應的‘子表’。當然，這也在一定程度上節約了存儲。

在例如MySQL,MS SQL及Oracle等大型關系數據庫中，你很難找到查詢的概念。相反，你看到的是視圖；它正是類似于Access中查詢的東西。因此，這種視圖在數據庫設計中的位置也是不言而喻的。

一． 視圖概念

　　MySQL視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數據。但是，視圖并不在數據庫中以存儲的數據值集形式存在。行和列數據來自由定義視圖的查詢所引用的表，并且在引用視圖時動態生成。

　　對其中所引用的基礎表來說，MySQL視圖的作用類似于篩選。定義視圖的篩選可以來自當前或其它數據庫的一個或多個表，或者其它視圖。通過視圖進行查詢沒有任何限制，通過它們進行數據修改時的限制也很少。

　　實際上，視圖是存儲在數據庫中的查詢的sql 語句，它主要出于兩種原因：安全原因，視圖可以隱藏一些數據，如：社會保險基金表，可以用視圖只顯示姓名，地址，而不顯示社會保險號和工資數等，另一原因是可使復雜的查詢易于理解和使用。

　　實用開發中，視圖一般是從一個或多個表或視圖中導出的表，其結構和數據是建立在對表的查詢基礎上的。和表一樣，視圖也是包括幾個被定義的數據列和多個數據行，但就本質而言這些數據列和數據行來源于其所引用的表。

　　所以，MySQL視圖不是真實存在的基礎表而是一張虛表，視圖所對應的數據并不實際地以視圖結構存儲在數據庫中，而是存儲在視圖所引用的表中。

　　視圖一經定義便存儲在數據庫中，與其相對應的數據并沒有像表那樣又在數據庫中再存儲一份，通過視圖看到的數據只是存放在基本表中的數據。對視圖的操作與對表的操作一樣，可以對其進行查詢、修改(有一定的限制)、刪除。

　　當對通過視圖看到的數據進行修改時，相應的基本表的數據也要發生變化，同時，若基本表的數據發生變化，則這種變化也可以自動地反映到視圖中。

　　視圖有很多優點，主要表現在：

• 視點集中和簡化操作：視圖機制使用戶可以將注意力集中在所關心地數據上。如果這些數據不是直接來自基本表，則可以通過定義視圖，使數據庫看起來結構簡單、清晰，并且可以簡化用戶的的數據查詢操作。例如，那些定義了若干張表連接的視圖，就將表與表之間的連接操作對用戶隱藏起來了。換句話說，用戶所作的只是對一個虛表的簡單查詢，而這個虛表是怎樣得來的，用戶無需了解。

• 定制數據：視圖機制能使不同的用戶以不同的方式看待同一數據，當許多不同種類的用戶共享同一個數據庫時，這種靈活性是非常必要的。

• 視圖對重構數據庫提供了一定程度的邏輯獨立性：

　　數據的物理獨立性是指用戶的應用程序不依賴于數據庫的物理結構。數據的邏輯獨立性是指當數據庫重構造時，如增加新的關系或對原有的關系增加新的字段，用戶的應用程序不會受影響。層次數據庫和網狀數據庫一般能較好地支持數據的物理獨立性，而對于邏輯獨立性則不能完全的支持。
　　在關許數據庫中，數據庫的重構造往往是不可避免的。重構數據庫最常見的是將一個基本表“垂直”地分成多個基本表。例如：將學生關系Student（Sno，Sname，Ssex，Sage，Sdept），
分為SX（Sno，Sname，Sage）和SY（Sno，Ssex，Sdept）兩個關系。這時原表Student為SX表和SY表自然連接的結果。如果建立一個視圖Student：

CREATE VIEW Student（Sno，Sname，Ssex，Sage，Sdept）AS SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept FROM SX，SY WHERE SX.Sno=SY.Sno；

 這樣盡管數據庫的邏輯結構改變了（變為SX和SY兩個表了），但應用程序不必修改，因為新建立的視圖定義為用戶原來的關系，使用戶的外模式保持不變，用戶的應用程序通過視圖仍然能夠查找數據。

　　當然，視圖只能在一定程度上提供數據的邏輯獨立，比如由于視圖的更新是有條件的，因此應用程序中修改數據的語句可能仍會因為基本表構造的改變而改變。

• 安全性：有了視圖機制，就可以在設計數據庫應用系統時，對不同的用戶定義不同的視圖，使機密數據不出現在不應該看到這些數據的用戶視圖上。這樣視圖機制就自動提供了對機密數據的安全保護功能。例如，Student表涉及全校15個院系學生數據，可以在其上定義15個視圖，每個視圖只包含一個院系的學生數據，并只允許每個院系的主任查詢和修改本原系學生視圖。

二． 創建MySQL視圖——create view

1. 語法

   create [or replace] [algorithm = {undefined | merge | temptable}] view [db_name.]view_name [(column_list)] as select_statement [with [cascaded | local] check option]

　　通過該語句可以創建視圖，若給定了[or replace]，則表示當已具有同名的視圖時，將覆蓋原視圖。select_statement是一個查詢語句，這個查詢語句可從表或其它的視圖中查詢。視圖屬于數據庫，因此需要指定數據庫的名稱，若未指定時，表示在當前的數據庫創建新視圖。

　　表和數據庫共享數據庫中相同的名稱空間，因此，數據庫不能包含相同名稱的表和視圖，并且，視圖的列名也不能重復。

1. 使用舉例

　　本例創建一個產品表（product）和一個購買記錄表（purchase），再通過MySQL視圖purchase_detail查詢出購買的詳細信息。

create table product  
(  
product_id int not null,  
name varchar(50) not null,  
price double not null  );  
insert into product values(1, 'apple ', 5.5);  
create table purchase  
(  
id int not null,  
product_id int not null,  
qty int not null default 0,  
gen_time datetime not null  );  
insert into purchase values(1, 1, 10, now());  
create view purchase_detail as select product.name as name, product .price as price, purchase.qty as qty, 
product .price * purchase.qty as total_value from product, 
purchase where product.product_id = purchase.product_id;

創建成功后，輸入：select * from purchase_detail;

運行效果如下：

+-------+-------+-----+-------------+
| name | price | qty | total_value |
+-------+-------+-----+-------------+
| apple | 5.5 | 10 | 55 |
+-------+-------+-----+-------------+
1 row in set (0.01 sec)

注意事項歸納：

創建視圖存在如下注意事項：

（1） 運行創建視圖的語句需要用戶具有創建視圖（crate view）的權限，若加了[or replace]時，還需要用戶具有刪除視圖（drop view）的權限；

（2） select語句不能包含from子句中的子查詢；

（3） select語句不能引用系統或用戶變量；

（4） select語句不能引用預處理語句參數；

（5） 在存儲子程序內，定義不能引用子程序參數或局部變量；

（6）在定義中引用的表或視圖必須存在。但是，創建了MySQL視圖后，能夠舍棄定義引用的表或視圖。要想檢查視圖定義是否存在這類問題，可使用check table語句；

（7） 在定義中不能引用temporary表，不能創建temporary視圖；

（8） 在視圖定義中命名的表必須已存在；

（9） 不能將觸發程序與視圖關聯在一起；

（10） 在視圖定義中允許使用order by，但是，如果從特定視圖進行了選擇，而該視圖使用了具有自己order by的語句，它將被忽略。

三． 修改MySQL視圖——alter view

1. 語法

   alter [algorithm = {undefined | merge | temptable}] view view_name [(column_list)] as select_statement [with [cascaded | local] check option]該語句用于更改已有視圖的定義。其語法與create view類似。

2. 使用舉例

　　將上一小節中中創建的視purchase_detail進行修改，去掉qty列，語句如下：

alter  view purchase_detail as select product.name as name, product .price as price, product .
price * purchase.qty as total_value from product, 
purchase where product.product_id = purchase.product_id;

四，重要參考文章

• http://blog.csdn.net/lihaitao_1/article/details/51504626

• http://www.2cto.com/database/201508/427083.html
  

• http://www.jb51.net/article/36363.htm