MySQL資料庫應用總結（九）—MySQL資料庫視圖的操作

SQL語法預覽：

創建視圖：【create [or replace] [algorithm={undefined | merge | temptable}] view 視圖名稱(屬性列) as select 欄位名稱 from 表名[with[cascaded | local] check option];】

查看視圖：【describe 視圖名;】【show table status like 視圖名G;】【show create view 視圖名;】【select * from information_schema.views;】

修改視圖：【create [or replace] [algorithm={undefined | merge | temptable}] view 視圖名稱(屬性列) as select 欄位名稱 from 表名[with[cascaded | local] check option];】【alter

更新視圖：【update 視圖名 set 視圖欄位名=值;】【insert into 表名 values(值，值…);】【delete from 視圖名 where 視圖欄位=值;】

刪除視圖：【drop view if exists 視圖名 [視圖名2…] [restrict | cascade];】

詳解：

一、 視圖概述

視圖是從一個或者多個表導出的，視圖的行為與表非常相似，但視圖是一個虛擬表。在視圖中可以使用select語句查詢數據，以及使用insert、update和delete語句修改記錄。從MySQL5.0開始可以使用視圖，視圖可以使用戶操作方便，而且可以保障資料庫系統的安全。

1. 視圖的含義

視圖是一個虛擬表是從資料庫的一個或者多個表中導出來的表。視圖還可以在已有的視圖的基礎上定義。

視圖一經定義便存儲在資料庫中，與其相對應的數據並沒有像表那樣在資料庫中再存儲一份，通過視圖看到的數據只是存放在基本表中的數據。對視圖的操作與對錶的操作一樣，可以對其進行查詢、修改和刪除。當對通過視圖看到的數據進行修改時，相應的基本表的數據也要發生變化；同時，若基本表發生變化，則這種變化可以自動地反映到視圖中。

2. 視圖的作用

與直接從數據表中讀取數據相比，視圖有以下優點：

1.簡單

視圖中看到的就是需要的。視圖不僅可以簡化用戶對大戶家的理解，也可以簡化他們的操作。經常使用的查詢可以被定義為視圖，從而可以免去為以後的操作每次指定全部條件的麻煩。

2.安全性高

通過視圖用戶只能查詢和修改他們所看到的數據，資料庫中的其他數據則即看不見也取不到。資料庫授權命令可以將每個用戶對資料庫的檢索限制到特定的資料庫對象上，但不能授權到資料庫的特定行和特定列上。通過視圖，用戶可以被在數據的不同子集上：

（1）使用許可權可被限制在基本表的行的子集上。

（2）使用許可權可被限制在基本表的列的子集上。

（3）使用許可權可被限制在基本表的行和列的子集上。

（4）使用許可權可被限制在多個基本表的連接所限制的行上。

（5）使用許可權可被限制在基本表的數據的統計匯總上。

（6）使用許可權可被限制在另一個視圖的一個子集上，或是一些視圖和基本表合併後的子集上。

3.邏輯數據獨立

視圖可以幫助用戶屏蔽真是表的結構變化帶來的影響。

二、 創建視圖

視圖中包含select查詢的結果，因此視圖的創建基於select語句和已存在的數據表，視圖可以建立在一張表上，也可以建立在多張表上。

1. 創建視圖的語法形式

創建視圖使用create view語句，基本語法格式如下：

語法結構：create [or replace] [algorithm={undefined | merge | temptable}] view 視圖名稱(屬性列) as select 欄位名稱 from 表名[with[cascaded | local]

其中，create表示創建新的視圖；replace表示替換已經創建的視圖；algorithm表示視圖的演算法；view_name表示視圖的名稱，column_list表示屬性列；select_statement表示select語句：with[ cascaded | local ]check option參數表示視圖在更新時保證在視圖的操作許可權範圍之內。

algorithm參數的取值有三個，分別是undefined、merge和temptable。undefined表示MySQL將自動選擇演算法；merge表示將使用的視圖語句與視圖定義合併，使得視圖定義的某一部分取代語句對應的部分；temptable表示將視圖的結果存入臨時表，然後用臨時表來執行語句。

cascaded與local為可選參數，cascaded為默認值。表示更新視圖時要滿足所有先關視圖和表的條件；local表示更新視圖時滿足該視圖本身定義的條件即可。

該語句要求具有針對視圖的create view許可權，以及針對由select語句選擇的每一列是那個的某些許可權。如果還有or replace子句，必須在視圖上具有drop許可權。

視圖屬於資料庫。在默認情況下，將在當前資料庫中創建新的視圖。要想在給定的資料庫中明確創建視圖，創建時應將視圖名稱設置為db_name.view_name。

2. 在單表上創建視圖

在MySQL中，可以在單個數據表上創建視圖。

案例：在資料庫db_view中創建表t，在表t上創建一個名為view_t的視圖。SQL語句如下：

命令語句：

create database db_view;

use db_view;

create table t(quantity int,price double);

insert into t values(3,50.0);

create view view_t as select quantity,price,quantity*price from t;

select * from view_t;

語句執行情況如下：

在默認情況下，創建的視圖和基本表的欄位是一樣的，也可以通過指定視圖欄位的名稱來創建視圖。只需要在創建視圖的時候指定視圖欄位就可以【create view view_t(quantity, price, total) as select quantity, price,quantity*price from t;】。這樣欄位不同，視圖數據卻相同，在使用視圖的時候，可以在不了解基本表且不接觸表中數據的情況下，查看所需要的數據，保證了資料庫的安全。

3. 在多表上創建視圖

在MySQL中，也可以在兩個或者兩個以上的表上創建視圖，可以使用create view語句實現。

案例：在表student和表stu_info上創建視圖stu_glass。

首先向兩個表中插入數據，輸入的語句如下：

命令語句：

create table student (id int,name varchar(11));

create table stu_info(id int,glass varchar(11),location varchar(11));

insert into student values(1,lifangyuan),(2,liumengyue),(3,lishemin);

insert into stu_info values(1,wuban,yangling),(2,liuban,luonan),(3,qiban,luonan);

create view stu_glass(id,name,glass) as select student.id,student.name, stu_info.glass from student,stu_info where student.id=stu_info.id;

select * from stu_glass;

執行結果如下：

這個例子就解決了開始提出的問題，通過這個視圖可以很好地保護基本表中的數據。這個視圖中的信息很簡單，包含了id、name和glass三個欄位，id欄位對應student表中的id欄位，name欄位對應student表中的name欄位，glass欄位對應stu_info表中的glass欄位。

三、 查看視圖

查看視圖是查看資料庫中已有的視圖的定義。查看視圖必須要有show view的許可權，MySQL資料庫下的user表中保存著這個信息。查看視圖的方法有describe、show table status和show create view，下面將介紹查看視圖的各種方法。

1. 用describe語句查看視圖的基本信息

用describe語句查看視圖的具體語法如下：

語法結構：describe 視圖名;

案例：通過describe語句查看視圖view_t的定義，SQL語句如下：

命令語句：describe view_t;

執行結果：

執行結果顯示出了視圖的欄位定義、欄位的數據類型、是否為空、是否為主/外鍵、默認值和額外信息。

一般情況下，describe都可簡寫成desc，輸入desc的執行結果和輸入describe的執行結果是一樣的。

2. 用show table status語句查看視圖的基本信息

用show table status語句查看視圖的具體語法如下：

語法結構：show table status like 視圖名G;

案例：用show table status語句查看視圖view_t的信息，SQL語句如下：

命令語句：show table status like view_t;

執行結果：

執行結果顯示，comment的值為view說明該表為視圖，其他的信息也為null說明這是一個虛擬表。用同樣的語句查看資料庫表的信息，執行結果如下：

從查詢結果來看，這裡的信息包含存儲引擎。創建時間等，comment的值為空，這就是視圖和表的區別。

3. 用show create view語句查看視圖的詳細信息

用show create view語句查看視圖的詳細定義，具體語法如下：

語法結構：show create view 視圖名;

案例：用show create view 語句查看視圖view_t的信息，SQL語句如下：

命令語句：show create view view_tG;

執行結果：

執行結果顯示了視圖的名稱、創建視圖的語句等信息。

4. 在views表中查看視圖的詳細信息

在MySQL中，information_schema資料庫下的view表中存儲了所有視圖的定義。通過對view表的查詢，可以查看資料庫中所有視圖的詳細信息，查詢語句如下：

語法結構：select * from information_schema.views;

案例：在views表中查看視圖詳細定義，SQL語句如下：

命令語句：select * from information_schema.viewsG;

執行結果：

查詢的結果中顯示了當前以及定義的所有視圖的詳細信息，在這裡也可以看到前面定義的名為stu_glass和view_t的兩個視圖發的詳細信息。

四、 修改視圖

修改視圖是指修改資料庫中存在的視圖，當基本表的某些欄位發生變化的時候，可以通過修改視圖來保持與基本表的一致性。在MySQL中，通過create or replace view語句和alter語句來修改視圖。

1. 用create or replace view 語句修改視圖

在MySQL中如果要修改視圖，可以使用create or replace view語句，語法格式如下：

語法結構：create [or replace] [algorithm={undefined | merge | temptable}] view 視圖名稱(屬性列) as select 欄位名稱 from 表名[with[cascaded | local]

可以看到，修改視圖的語句和創建視圖的語句完全一樣。當視圖存在時，修改語句對視圖進行修改；當視圖不存在時，創建視圖。下面通過一個實例來說明。

案例：修改視圖view_t，SQL語句如下：

命令語句：create or replace view view_t as select * from t;

先用describe查看更改前的視圖，以便於之後的視圖進行對比，執行結果：

從執行的結果來看，相比原來的視圖view_t，新的視圖view_t少了一個欄位。

2. 用alter語句修改視圖

用alter語句修改視圖的語法格式如下：

語法結構：alter [algorithm={undefined | merge | temptable}] view 視圖名稱(屬性列) as select 欄位名稱 from 表名[with[cascaded | local] check option];

案例：使用alter語句修改視圖，SQL語句如下：

命令語句：alter view view_t as select quantity from t;

執行結果如下：

通過alter語句同樣可以達到修改視圖view_t的目的，從上面的執行結果來看，視圖view_t只剩下1個quantity欄位，修改成功。

五、 更新視圖

更新視圖是指通過視圖來插入、個人能關心、刪除表中的數據。視圖是一個虛擬表，其中沒有數據，通過視圖更新的時候都是轉到基本表上進行更新的，如果對視圖增加或者刪除記錄，實際上是對其基本表增加或者刪除記錄。下面將介紹更新視圖的三種方法：insert、update和delete。

1.使用update語句更新視圖view_t

語法結構：update 視圖名 set 視圖欄位名=值;

案例：使用update語句更新視圖view_t，SQL語句如下：

命令語句：update view_t set quantity=5;

執行視圖更新之前，查看基本表和視圖的信息，執行結果如下：

對視圖view_t更新後，基本表t的內容也更新了，並且基於表t的其他視圖也會更新。

2.使用insert語句更新視圖

語法結構：insert into 表名 values(值，值…);

案例：使用insert語句在基本表t中插入一條記錄，SQL語句如下：

命令語句：insert into t values(3,5);

執行結果如下：

向表t中插入一條記錄，通過select查看錶t和視圖view_t，可以看到其中的內容也跟著更新了。

3.使用delete語句更新視圖

語法結構：delete from 視圖名 where 視圖欄位=值;

案例：使用delete語句刪除視圖view_t中的一條記錄，SQL語句如下：

命令語句：delete from view_t where quantity=5;

執行結果如下：

從執行的結果來看，在視圖view_t中刪除了quantity=5的記錄。因為視圖中的刪除操作最終是通過刪除基本表中的相關記錄實現的，所以查看刪除操作之後的表t和視圖view_t，可以看到通過視圖刪除了所依賴的基本表中的數據。

當視圖中含有如下內容時，視圖的更新操作將不能被執行：

（1）在定義視圖的select語句後的欄位列表中使用了數學表達式。

（2）在定義視圖的select語句後的欄位列表中使用了聚合函數。

（3）在定義視圖的select語句中使用了distinct、union、top、group by或having。

六、 刪除視圖

當視圖不再需要時，可以將其刪除，刪除一個或多個需要刪除的視圖使用drop view語句，語法格式如下：

語法結構：drop view if exists 視圖名 [視圖名2…] [restrict | cascade];

其中，view_name是要刪除的視圖的名稱，可以添加多個需要刪除的視圖名稱，名稱之間用逗號隔開。刪除視圖必須擁有drop許可權。

案例：刪除stu_glass視圖，SQL語句如下：

命令語句：drop view if exists stu_glass;

如果名稱為stu_glass的視圖存在，該視圖將被刪除。使用show create view語句查看操作結果：

結果顯示stu_glass視圖不存在，刪除成功。

SQL語法總結：

創建視圖：【create [or replace] [algorithm={undefined | merge | temptable}] view 視圖名稱(屬性列) as select 欄位名稱 from 表名[with[cascaded | local] check option];】

查看視圖：【describe 視圖名;】【show table status like視圖名G;】【show create view 視圖名;】【select * from information_schema.views;】

修改視圖：【create [or replace] [algorithm={undefined | merge | temptable}] view 視圖名稱(屬性列) as select 欄位名稱 from 表名[with[cascaded | local]check option];】【alter[algorithm={undefined | merge | temptable}] view 視圖名稱(屬性列) as select 欄位名稱 from 表名[with[cascaded | local] check option];】

更新視圖：【update 視圖名 set 視圖欄位名=值;】【insert into 表名 values(值，值…);】【delete from 視圖名 where 視圖欄位=值;】

刪除視圖：【drop view if exists 視圖名 [視圖名2…][restrict | cascade];】

-------------------《end》------------------