程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySql視圖原理講解與使用大全

MySql視圖原理講解與使用大全

編輯：MySQL綜合教程

MySql視圖原理講解與使用大全

摘要：本文主要講了MySql中視圖的定義、原理和如何使用、創建、刪除等

一．視圖概述

視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數據。但是，視圖並不在數據庫中以存儲的數據值集形式存在。行和列數據來自由定義視圖的查詢所引用的表，並且在引用視圖時動態生成。
對其中所引用的基礎表來說，視圖的作用類似於篩選。定義視圖的篩選可以來自當前或其它數據庫的一個或多個表，或者其它視圖。通過視圖進行查詢沒有任何限制，通過它們進行數據修改時的限制也很少。
視圖是存儲在數據庫中的查詢的SQL 語句，它主要出於兩種原因：安全原因，視圖可以隱藏一些數據，如：社會保險基金表，可以用視圖只顯示姓名，地址，而不顯示社會保險號和工資數等，另一原因是可使復雜的查詢易於理解和使用。這個視圖就像一個“窗口”，從中只能看到你想看的數據列。這意味著你可以在這個視圖上使用SELECT *，而你看到的將是你在視圖定義裡給出的那些數據列：

既然視圖的定義是基於基本表的，哪為什麼還要定義視圖呢？這是因為合理地使用視圖能夠帶來許多好處：
1、視圖能簡化用戶操作
視圖機制使用戶可以將注意力集中在所關心地數據上。如果這些數據不是直接來自基本表，則可以通過定義視圖，使數據庫看起來結構簡單、清晰，並且可以簡化用戶的的數據查詢操作。例如，那些定義了若干張表連接的視圖，就將表與表之間的連接操作對用戶隱藏起來了。換句話說，用戶所作的只是對一個虛表的簡單查詢，而這個虛表是怎樣得來的，用戶無需了解。
2、視圖使用戶能以多種角度看待同一數據
視圖機制能使不同的用戶以不同的方式看待同一數據，當許多不同種類的用戶共享同一個數據庫時，這種靈活性是非常必要的。
3、視圖對重構數據庫提供了一定程度的邏輯獨立性
數據的物理獨立性是指用戶的應用程序不依賴於數據庫的物理結構。數據的邏輯獨立性是指當數據庫重構造時，如增加新的關系或對原有的關系增加新的字段，用戶的應用程序不會受影響。層次數據庫和網狀數據庫一般能較好地支持數據的物理獨立性，而對於邏輯獨立性則不能完全的支持。
在關許數據庫中，數據庫的重構造往往是不可避免的。重構數據庫最常見的是將一個基本表“垂直”地分成多個基本表。例如：將學生關系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兩個表了），但應用程序不必修改，因為新建立的視圖定義為用戶原來的關系，使用戶的外模式保持不變，用戶的應用程序通過視圖仍然能夠查找數據。
當然，視圖只能在一定程度上提供數據的邏輯獨立，比如由於視圖的更新是有條件的，因此應用程序中修改數據的語句可能仍會因為基本表構造的改變而改變。
4、視圖能夠對機密數據提供安全保護
有了視圖機制，就可以在設計數據庫應用系統時，對不同的用戶定義不同的視圖，使機密數據不出現在不應該看到這些數據的用戶視圖上。這樣視圖機制就自動提供了對機密數據的安全保護功能。例如，Student表涉及全校15個院系學生數據，可以在其上定義15個視圖，每個視圖只包含一個院系的學生數據，並只允許每個院系的主任查詢和修改本原系學生視圖。
5、適當的利用視圖可以更清晰地表達查詢
例如經常需要執行這樣的查詢“對每個學生找出他獲得最高成績的課程號”。可以先定義一個視圖，求出每個同學獲得的最高成績：

CREATE VIEW VMGRADE
AS
SELECT Sno，MAX(Grade) Mgrade
FROM SC
GROUP BY Sno

然後用如下的查詢語句完成查詢：

SELECT SC.Sno，Cno FROM SC，VMGRADE WHERE SC.Sno = VMGRADE.Sno AND SC.Grade = VMGRADE.Mgrade；

二、數據准備

1、員工表

CREATE TABLE t_employee(
        ID INT  PRIMARY KEY  AUTO_INCREMENT,
        NAME CHAR(30) NOT NULL,
        SEX  CHAR(2) NOT NULL,
        AGE INT NOT NULL,
        DEPARTMENT CHAR(10) NOT NULL,
        SALARY  INT NOT NULL,
        HOME CHAR(30),
        MARRY CHAR(2) NOT NULL DEFAULT  '否',       
        HOBBY CHAR(30)
 );

插入數據：

INSERT INTO learning.t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'小紅','女',20,'人事部','4000','廣東','否','網球');
INSERT INTO learning.t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'明日','女',21,'人事部','9000','北京','否','網球');
INSERT INTO learning.t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'天天','男',22,'研發部','8000','上海','否','音樂');
INSERT INTO learning.t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'大大','女',23,'研發部','9000','重慶','否','無');
INSERT INTO learning.t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'王下','女',24,'研發部','9000','四川','是','足球');
INSERT INTO learning.t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'無名','男',25,'銷售部','6000','福建','否','游戲');
INSERT INTO learning.t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'不知道','女',26,'銷售部','5000','山西','否','籃球');

插入的結果：

然後再定義一張員工信息表：

create TABLE t_employee_detail(
ID INT PRIMARY KEY,
POS CHAR(10) NOT NULL,
EXPERENCE CHAR(10) NOT NULL,
CONSTRAINT `FK_ID` FOREIGN KEY(ID) REFERENCES t_employee(ID)
)

插入如下：

INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(1,'人事管理','工作二年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(2,'人事招聘','工作二年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(3,'初級工程師','工作一年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(4,'中級工程師','工作二年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(5,'高級工程師','工作三年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(6,'銷售代表','工作二年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(7,'銷售員','工作一年');

內容：

三、使用案例

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是一個查詢語句，這個查詢語句可從表或其它的視圖中查詢。視圖屬於數據庫，因此需要指定數據庫的名稱，若未指定時，表示在當前的數據庫創建新視圖。
表和數據庫共享數據庫中相同的名稱空間，因此，數據庫不能包含相同名稱的表和視圖，並且，視圖的列名也不能重復。

在創建視圖前應先看看是否有權限：

SELECT SELECT_priv,create_view_priv from mysql.user WHERE user='root'

Y表示有創建的權限

2、單表上創建視圖

在員工表是創建視圖

CREATE VIEW V_VIEW1(ID, NAME, SEX, AGE,DEPARTMENT) AS SELECT ID, NAME, SEX, AGE,DEPARTMENT FROM learning.t_employee;

然後是顯示內容：

SELECT * FROM V_VIEW1

3、多表上創建視圖

CREATE VIEW V_VIEW2(ID, NAME, SEX, AGE,DEPARTMENT,POS,EXPERENCE) AS SELECT a.ID, a.NAME, a.SEX, a.AGE,a.DEPARTMENT,b.POS,b.EXPERENCE FROM learning.t_employee a,learning.t_employee_detail b WHERE a.ID=b.ID;

顯示結果

SELECT * FROM V_VIEW2

4、查看視圖

（1）DESCRIBE 命令

DESCRIBE V_VIEW2

（2）SHOW TABLE STATUS

show TABLE status LIKE 'V_VIEW2'

（3）SHOW CREATE view命令

show CREATE view V_VIEW2

5、修改視圖

(1)CREATE OR REPLACE命令

CREATE OR REPLACE VIEW V_VIEW1(ID, NAME, SEX) AS SELECT ID, NAME, SEX  FROM learning.t_employee;

(2) ALTER 命令

ALTER VIEW  V_VIEW1(ID, NAME) AS SELECT ID, NAME  FROM learning.t_employee;
SELECT * FROM learning.v_view1

6、更新視圖

在MySQL中，更新視圖是指通過視圖來插入（INSERT）、更新（UPDATE）和刪除（DELETE）表中的數據。因為視圖是一個虛擬表，其中沒有數據，所以通過視圖更新時，都是轉換到基本表來更新。
更新視圖時，只能更新權限范圍內的數據。超出了范圍，就不能更新。

更新前：

更新後：

UPDATE V_VIEW2 SET POS='高級工程師' WHERE NAME='天天'

對應的真實表上的數據也發生改變了

SELECT * FROM learning.t_employee_detail WHERE t_employee_detail.ID=3

不可更新的視圖：
某些視圖是可更新的。也就是說，可以在諸如UPDATE、DELETE或INSERT等語句中使用它們，以更新基表的內容。對於可更新的視圖，在視圖中的行和基表中的行之間必須具有一對一的關系。還有一些特定的其他結構，這類結構會使得視圖不可更新。更具體地講，如果視圖包含下述結構中的任何一種，那麼它就是不可更新的：

· 聚合函數（SUM(), MIN(), MAX(), COUNT()等）。
· DISTINCT
· GROUP BY
· HAVING
· UNION或UNION ALL
· 位於選擇列表中的子查詢
· Join
· FROM子句中的不可更新視圖
· WHERE子句中的子查詢，引用FROM子句中的表。
· 僅引用文字值（在該情況下，沒有要更新的基本表）。
· ALGORITHM = TEMPTABLE（使用臨時表總會使視圖成為不可更新的）。

注意

視圖中雖然可以更新數據，但是有很多的限制。一般情況下，最好將視圖作為查詢數據的虛擬表，而不要通過視圖更新數據。因為，使用視圖更新數據時，如果沒有全面考慮在視圖中更新數據的限制，就可能會造成數據更新失敗。

CASCADED和LOCAL能不能決定視圖是否能更新？
WITH[CASCADED|LOCAL] CHECK OPTION能不能決定視圖是否能更新？這兩個參數的基本定義如下：
LOCAL參數表示更新視圖時只要滿足該視圖本身定義的條件即可。
CASCADED參數表示更新視圖時需要滿足所有相關視圖和表的條件。沒有指明時，該參數為默認值。

With check option的用法：
（with check option對於沒有where條件的視圖不起作用的）

CREATE VIEW V_VIEW3(ID, NAME,SEX,AGE,DEPARTMENT,SALARY, HOME, MARRY, HOBBY) AS SELECT ID, NAME, SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY FROM learning.t_employee WHERE DEPARTMENT='人事部' WITH LOCAL CHECK OPTION;

表示只限定插入部門為人事部的人。

然後插入一條：

INSERT INTO learning.V_VIEW3(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'會會會會','女',20,'人事部','4500','廣東','否','網球');

看下結果：

SELECT * FROM learning.V_VIEW3

同時看真實表中的數據：

再來插入一條：

INSERT INTO learning.V_VIEW3(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'qqqqvasvas','女',20,'研發部','4500','上海','否','網球');

結果顯示插入失敗
對於with check option用法，總結如下：
通過有with check option選項的視圖操作基表(只是面對單表，對連接多表的視圖正在尋找答案)，有以下結論：插入後的數據，通過視圖能夠查詢出來就符合WITH CHECK OPTION 否則就不符合；
首先視圖只操作它可以查詢出來的數據，對於它查詢不出的數據，即使基表有，也不可以通過視圖來操作。
1.對於update,有with check option，要保證update後，數據要被視圖查詢出來
2.對於delete,有無with check option都一樣
4.對於insert,有with check option，要保證insert後，數據要被視圖查詢出來
對於沒有where 子句的視圖，使用with check option是多余的

7、刪除視圖