天天看點
傳回

MySQL-視圖操作

  1. 視圖操作

國小的時候，每年都會舉辦一次抽考活動，意思是從每一個班級裡面篩選出幾個優秀的同學去參加考試。這時候很多班級篩選出來的這些同學就可以臨時組成一個班級，如果我們把每一個班級都當做是一張真實的表，這個臨時的班級在資料庫裡就可以當做一個視圖，也就是說，這個臨時的班級其實不是真實存在的，當考試過後，這些學生還是各回各家各找各媽。。。。

視圖是從一個或多個表中導出來的表，是一種虛拟存在的表。視圖就像一個視窗，通過這個視窗可以看到系統專門提供的資料，這樣使用者可以不看整個資料庫表中的資料，而隻關心對自己有用的資料。視圖可以使使用者的操作更友善，而且可以保障資料庫系統的安全性。

  1. 為什麼要使用視圖

通過前面章節的知識可以發現，資料庫中關于資料的查詢有時非常複雜，例如表連接配接、子查詢等，這種查詢會讓程式員感到非常痛苦，因為它的邏輯太複雜、編寫語句比較多，當這種查詢需要重複使用時，很難每次都編寫正确，進而降低了資料庫的實用性。

在具體操作表之前，有時候要求隻能操作部分字段，而不是全部字段。例如，在學校裡，學生的智商測試結果一般都是保密的，如果因為一時疏忽向查詢中多寫了關于“智商”的字段，則會讓學生的智商顯示給所有能夠檢視該查詢結果的人，這時就需要限制使用者操作的字段。

為了提高複雜的SQL語句的複用性和表的操作的安全性，MySQL資料庫管理系統提供了視圖特性。所謂視圖，本質上是一種虛拟表，其内容與真實的表相似，包含一系列帶有名稱的列和行資料。但是，視圖并不在資料庫中以存儲資料值的形式存在，行和列資料來自定義視圖的查詢所引用的基本表，并且在具體引用視圖時動态生成。

視圖使程式員隻關心感興趣的某些特定資料和他們所負責的特定任務。這樣程式員隻能看到視圖中所定義的資料，而不是視圖所引用表中的資料，進而提高資料庫中資料的安全性。

  1. 建立視圖

雖然視圖可以被看成是一種虛拟表，但是其實體上是不存在的，即MySQL并沒有專門的位置為視圖存儲資料。根據視圖的概念可以發現其資料來源于查詢語句，是以建立視圖的基本文法為：

    CREATE[OR REPLACE] VIEW viewname[columnlist]   

    AS SELECT statement                               

   其中，CREATE表示建立新的視圖；REPLACE表示替換已經建立的視圖；viewname為視圖的名稱；columnlist為屬性列；SELECT statement表示SELECT語句；

注意! 建立視圖需要登陸使用者有相應的權限，檢視權限方法：

mysql>  use school;   #選擇資料庫school                                                                                                                    

mysql>  select user, Select_priv, Create_view_priv FROM mysql.user;#查詢資料庫使用者建立和選擇視圖權限

在單表上建立視圖

mysql>  use school;   #選擇資料庫school

mysql>  alter table student add privacy varchar(64);# 增加私隐列                                                                                      

mysql>  ;#查詢資料庫使用者建立和選擇視圖權限    

mysql>  CREATE VIEW view_student AS select id, class_id, name from student ;#為學生表建立視圖

mysql>  desc view_student;#檢視視圖

mysql>  select * from view_student;   #根據視圖進行查詢 

在多表上建立視圖

    CREATE[OR REPLACE] VIEW viewname[columnlist]   

    AS SELECT statement                               

   其中，CREATE表示建立新的視圖；REPLACE表示替換已經建立的視圖；viewname為視圖的名稱；columnlist為屬性列；SELECT statement表示SELECT語句；與單表上建立視圖不同的是，SELECT子句是涉及到多表的聯合查詢語句。

mysql>  use school;   #選擇資料庫school

mysql>  alter table student add privacy varchar(64);# 增加私隐列                                                                                      

mysql>  ;#查詢資料庫使用者建立和選擇視圖權限    

mysql>  CREATE VIEW view_student_class AS select student.id, student.name, class.name,  class.teacher from class inner join student  on class.id = student.class_id;#為學生表建立視圖

mysql>  desc view_student_class;#檢視視圖

mysql>  select * from view_student_class;   #根據視圖進行查詢 

檢視視圖

建立完視圖後，像表一樣，我們經常需要檢視視圖資訊。在MySQL中，有許多可以實作檢視視圖的語句，如DESCRIBE、SHOW TABLES、SHOW CREATE VIEW。如果要使用這些語句，首先要確定擁有SHOW VIEW的權限。本節将詳細講解檢視視圖的方法。

  1. 使用DESCRIBE | DESC語句檢視視圖基本資訊

前面我們已經詳細講解過使用DESCRIBE語句來檢視表的基本定義。因為視圖也是一張表，隻是這張表比較特殊，是一張虛拟的表，是以同樣可以使用DESCRIBE語句來檢視視圖的基本定義。DESCRIBE語句檢視視圖的文法如下：

    DESCRIBE | DESC viewname;

在上述語句中，參數viewname表示所要檢視設計資訊的視圖名稱。

  1. 使用SHOW TABLES語句檢視視圖基本資訊

從MySQL 5.1版本開始，執行SHOW TABLES語句時不僅會顯示表的名字，同時也會顯示視圖的名字。

下面示範通過SHOW TABLES語句檢視資料庫school中的視圖和表的功能，具體SQL語句如下，執行結果如下圖所示。

    SHOW TABLES;

MySQL-視圖操作
  1. 使用 show create view/table 語句檢視視圖建立資訊

    SHOW CREATE TABLE或VIEW   viewname;  

更新視圖資料

更新視圖是指通過視圖來插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的資料。因為視圖實質是一個虛拟表，其中沒有資料，通過視圖更新時都是轉換到基本表更新。更新視圖時，隻能更新權限範圍内的資料，超出範圍就不能更新了。

mysql>  use school;   #選擇資料庫school

mysql>  alter table student add privacy varchar(64);# 增加私隐列                                                                                      

mysql>  ;#查詢資料庫使用者建立和選擇視圖權限    

mysql>  CREATE VIEW view_student AS select id, class_id, name from student ;#為學生表建立視圖

mysql>  desc view_student;#檢視視圖

mysql>  select * from view_student;   #根據視圖進行查詢  

mysql>  update view_student set name='小花花' where name='小花'; #通過視圖更新小花為小花花

不能更新的情況：

  1. 視圖中包含SUM()、COUNT()、MAX()和MIN()等函數
  2. 視圖中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等關鍵字
  3. 視圖對應的表存在沒有預設值的列，而且該列沒有包含在視圖裡
  4. 包含子查詢的視圖
  5. 其他特殊情況

修改視圖

修改視圖是指修改資料庫中存在的視圖，當基本表的某些字段發生變化的時候，可以通過修改視圖來保持與基本表的一緻性。ALTER語句來修改視圖。

使用ALTER語句修改視圖

    ALTER VIEW viewname[columnlist]   

AS SELECT statement                               

這個文法中的所有關鍵字和參數除了alter 外，其他都和建立視圖是一樣的，是以不再贅述。

【示例9.3】對于示例9.2中建立的視圖view_student_class，使用一段時間後需要将表示編号的字段id加進去。步驟如下：

mysql>  use school;   #選擇資料庫school

mysql>  alter table student add privacy varchar(64);# 增加私隐列                                                                                      

mysql>  ;#查詢資料庫使用者建立和選擇視圖權限    

mysql>  ALTER VIEW view_student_class AS select student.id, student.name, class.name, class.id as class_id, class.teacher from class inner join student  on class.id = student.class_id;#為學生班級表視圖增加 class_id 字段

mysql>  desc view_student_class;#檢視視圖

mysql>  select * from view_student_class;   #根據視圖進行查詢  

删除視圖

删除視圖是指删除資料庫中已存在的視圖。删除視圖時，隻能删除視圖的定義，不會删除資料。

在MySQL中，可使用DROP VIEW語句來删除視圖，但是使用者必須擁有DROP權限。删除視圖的文法如下：

    DROP VIEW viewname [,viewnamen]；

在上述語句中，參數viewname表示所要删除視圖的名稱，可同時指定删除多個視圖。

mysql>  use school;   #選擇資料庫school      

mysql>  ;#查詢資料庫使用者建立和選擇視圖權限    

mysql>  CREATE VIEW view_student_class AS select student.id, student.name, class.name, class.id as class_id, class.teacher from class inner join student  on class.id = student.class_id;#為學生表建立視圖

mysql>  drop view view_student_class;#删除視圖

更多的關于資料庫學習我會在下面的文章中陸續的分享，也可以關注‘奇牛學院’

MySQL-視圖操作

來一起讨論

資料庫
上一篇: Zabbix報警功能
下一篇: Spring基礎 之 事務傳播行為

繼續閱讀