MySQL從刪庫到跑路_高級（三）——視圖

作者：天山老妖S

鏈接：http://blog.51cto.com/9291927

一、視圖簡介

1、視圖簡介

視圖是由SELECT查詢語句所定義的一個虛擬表，是查看數據的一種非常有效的方式。視圖包含一系列有名稱的數據列和數據行，但視圖中的數據並不真實在於資料庫中，視圖返回的是結果集。

2、創建視圖的目的

視圖是存儲在數據中的查詢的SQL語句，創建視圖主要出於兩種原因：

A、實現安全。視圖可設置用戶對視圖的訪問許可權。

創建查詢是JAVA班學生成績的視圖javaview、NET班學生成績的視圖netview，授權java能夠訪問javaview視圖，授權net可以訪問netview視圖。

create view javaviewasselect a.StudentID,a.sname,email,c.subJectName,a.class,b.mark from TStudent a join TScore b on a.StudentID=b.StudentIDjoin TSubject c on b.subJectID=c.subJectID where a.class='JAVA';create view netviewasselect a.StudentID,a.sname,email,c.subJectName,a.class,b.mark 

授權java用戶訪問schoolDB.javaview視圖

grant select on schoolDB.javaview to 'java'@'%' identified by '123456';

授權net用戶訪問schoolDB.netview視圖

grant select on schoolDB.netview to 'net'@'%' identified by '123456';

使用SQL  Manager客戶端連接資料庫時，java、net用戶分別可以訪問javaview視圖和netview視圖。

B、隱藏數據複雜性。視圖可以隱藏一些數據，如：社會保險基金錶，可以用視圖只顯示姓名，地址，而不顯示社會保險號和工資數等。視圖就像一個視口，從視口中只能看到過濾後的某些數據列。

3、視圖的優點

A、視圖能簡化用戶操作

視圖機制使用戶可以將註意力集中在所關心地數據上。如果數據不是直接來自基本表，則可以通過定義視圖，使資料庫看起來結構簡單、清晰，並且可以簡化用戶的數據查詢操作。例如，定義了若幹張表連接的視圖，就將表與表之間的連接操作對用戶隱藏。用戶所作的只是對一個虛表的簡單查詢，而虛表是怎樣來的，用戶無需瞭解。

B、視圖使用戶能以多種角度看待同一數據

視圖機制能使不同的用戶以不同的方式看待同一數據，當許多不同種類的用戶共用同一資料庫時。

C、視圖對重構資料庫提供了一定程度的邏輯獨立性

數據的物理獨立性是指用戶的應用程式不依賴於資料庫的物理結構。數據的邏輯獨立性是指當資料庫重構時，如增加新的關係或對原有的關係增加新的欄位，用戶的應用程式不會受影響。層次資料庫和網狀資料庫一般能較好地支持數據的物理獨立性，而對於邏輯獨立性則不能完全的支持。

在關係資料庫中，資料庫的重構造往往是不可避免的。重構資料庫最常見的是將一個基本表“垂直”地分成多個基本表。例如：將學生關係student(sid,name,sex,age,dept,leader)，分為studentinfo(sid,sname,sex,age) 和deptinfo(sid,dept)兩個關係。原表student為studentinfo表和deptinfo表自然連接的結果。如果建立一個視圖student:

CREATE VIEW student(sid,sname,sex,age,dept) 
AS SELECT studentinfo.sid,studentinfo.sname,studentinfo.sex,studentinfo.age,
deptinfo.dept FROM studentinfo, deptinfo WHERE studentinfo.sid=deptinfo.sid;

儘管資料庫的邏輯結構變為studentinfo和deptinfo兩個表，但應用程式不必修改，因為新建立的視圖定義為用戶原來的關係，使用戶的外模式保持不變，用戶的應用程式通過視圖仍然能夠查找數據。

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

D、視圖能夠對機密數據提供安全保護

在設計資料庫應用系統時，可以對不同的用戶定義不同的視圖，使機密數據不出現在不應該看到機密數據的用戶視圖上。如student表涉及全校15個院系學生數據，可以在其上定義15個視圖，每個視圖只包含一個院系的學生數據，並只允許每個院系的主任查詢和修改本院系學生視圖。

E、適當的利用視圖可以更清晰地表達查詢

例如經常需要執行這樣的查詢“對每個學生找出他獲得最高成績的課程號”。可以定義一個視圖，求出每個同學獲得的最高成績。

4、創建視圖的語法

CREATE VIEW viewname(列1，列2...) AS SELECT (列1，列2...) FROM ...；

創建學生信息的視圖:

create view studentview as select studentID, sname, sex from TStudent;

二、視圖的操作

1、視圖的使用

視圖的使用和普通表一樣。

select * from studentview;

不能在一張由多張關聯表連接而成的視圖上做同時修改兩張表的操作；

視圖與表是一對一關係情況：如果沒有其它約束（如視圖中沒有的欄位，在基本表中是必填欄位情況），可以進行增刪改數據操作。

2、刪除視圖

drop view studentview;

3、通過視圖修改數據

如果視圖的基表是一張表。可以通過視圖向基表插入記錄，要求視圖中的沒有的列允許為空。

A、通過視圖插入數據到表

insert into studentview(studentID,sname,sex) values('01001','孫悟空','男')；

查詢插入的記錄，可以看到通過視圖沒有的列，值為空或預設值。

B、通過視圖刪除表中記錄

視圖的基表只能有一張表，如果有多張表，將不知道從哪一張表刪除。

delete from studentview where studentid = '01001';

C、通過視圖修改表中記錄

只能修改視圖中有的列。

update studentview set sname = '孫悟空' where studentid = '00001';

4、查看視圖的信息

查看視圖的信息

describe viewname; desc scoreview;

查看所有的表和視圖

show tables;

查看視圖的信息

show fields from scoreview;

5、修改視圖

CREATE OR REPLACE VIEW viewname AS SELECT [...] FROM [...];alter view studentview 
as select studentID as 學號, sname as 姓名, sex as 性別 from TStudent;

6、WITH CHECK OPTION

如果在創建視圖的時候指定了“WITH CHECK OPTION”,更新數據時不能插入或更新不符合視圖限制條件的記錄。

三、視圖實例

1、使用視圖創建視圖

創建視圖的查詢的表稱為基表，基表可以是視圖和表。

create view sview as select studentID, sname, sex from studentview where studentID>990 and sex='男';

2、創建學生成績表的視圖

創建一個視圖，視圖包含學生 學號、姓名、學科和成績。

create view view1 as select a.StudentID, a.Sname, c.subJectName, b.mark from TStudent a join TScore b on a.StudentID = b.StudentID join TSubject c on b.subJectID = c.subJectID;

 創建成績視圖,包含學號、姓名、電腦網路課程成績、數據結構成績、JAVA開發成績。

create view scoreview as select stduentid 學號, sname 姓名, AVG(case subjectname when '電腦網路' then mark END) 電腦網路,AVG(case subjectname when '數據結構' then mark END) 數據結構,AVG(case subjectname when 'Java開發' then mark END) JAVA開發 from view1 group by 學號;

喜歡的小伙伴們可以搜索我們個人的微信公眾號“程式員的成長之路”點擊關註或掃描下方二維碼