MySQL資料庫進階（三）——視圖

標籤：MySQL 檢視

MySQL資料庫進階（三）——視圖一、視圖簡介1、視圖簡介

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

2、建立視圖的目的

視圖是儲存在資料庫中的查詢的SQL語句，建立視圖主要出於兩種原因：
A、實現安全。視圖可設定使用者對視圖的存取權限。
建立查詢是JAVA班學產生績的視圖javaview、NET班學產生績的視圖netview，授權zhang能夠訪問javaview視圖，授權wangk可以訪問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  from TStudent a join TScore b on a.StudentID=b.StudentIDjoin TSubject c on b.subJectID=c.subJectID where a.class=‘NET‘;

授權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,sname,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 studentviewas 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 sviewas select studentID, sname, sex from studentview where studentID>990 and sex=‘男‘;

2、建立學產生績表的視圖

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

create view view1as 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 scoreviewas select studentid 學號,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 view1group by 學號;

MySQL資料庫進階（三）——視圖