MySQL數(shù)據(jù)庫高級（三）——視圖

MySQL數(shù)據(jù)庫高級（三）——視圖

一、視圖簡介

1、視圖簡介

視圖是由SELECT查詢語句所定義的一個虛擬表，是查看數(shù)據(jù)的一種非常有效的方式。視圖包含一系列帶有名稱的數(shù)據(jù)列和數(shù)據(jù)行，但視圖中的數(shù)據(jù)并不真實(shí)存在于數(shù)據(jù)庫中，視圖返回的是結(jié)果集。

2、創(chuàng)建視圖的目的

視圖是存儲在數(shù)據(jù)庫中的查詢的SQL語句，創(chuàng)建視圖主要出于兩種原因：
A、實(shí)現(xiàn)安全。視圖可設(shè)置用戶對視圖的訪問權(quán)限。
創(chuàng)建查詢是JAVA班學(xué)生成績的視圖javaview、NET班學(xué)生成績的視圖netview，授權(quán)java能夠訪問javaview視圖，授權(quán)net可以訪問netview視圖。

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

create view netview
as
select a.StudentID,a.sname,email,c.subJectName,a.class,b.mark 
 from TStudent a join TScore b on a.StudentID=b.StudentID
join TSubject c on b.subJectID=c.subJectID where a.class='NET';

授權(quán)java用戶訪問 schoolDB.javaview視圖
grant select on schoolDB.javaview to 'java'@'%' identified by '123456';
授權(quán)net用戶訪問 schoolDB.netview視圖
grant select on schoolDB.netview to 'net'@'%' identified by '123456';
使用SQL Manager客戶端連接數(shù)據(jù)庫時，java、net用戶分別可以訪問javaview視圖和netview視圖。
B、隱藏數(shù)據(jù)復(fù)雜性。視圖可以隱藏一些數(shù)據(jù)，如：社會保險基金表，可以用視圖只顯示姓名，地址，而不顯示社會保險號和工資數(shù)等。視圖就像一個視口，從視口中只能看到過濾后的某些數(shù)據(jù)列。

3、視圖的優(yōu)點(diǎn)

A、視圖能簡化用戶操作
視圖機(jī)制使用戶可以將注意力集中在所關(guān)心地數(shù)據(jù)上。如果數(shù)據(jù)不是直接來自基本表，則可以通過定義視圖，使數(shù)據(jù)庫看起來結(jié)構(gòu)簡單、清晰，并且可以簡化用戶的的數(shù)據(jù)查詢操作。例如，定義了若干張表連接的視圖，就將表與表之間的連接操作對用戶隱藏。用戶所作的只是對一個虛表的簡單查詢，而虛表是怎樣得來的，用戶無需了解。
B、視圖使用戶能以多種角度看待同一數(shù)據(jù)
視圖機(jī)制能使不同的用戶以不同的方式看待同一數(shù)據(jù)，當(dāng)許多不同種類的用戶共享同一個數(shù)據(jù)庫時。
C、視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性
數(shù)據(jù)的物理獨(dú)立性是指用戶的應(yīng)用程序不依賴于數(shù)據(jù)庫的物理結(jié)構(gòu)。數(shù)據(jù)的邏輯獨(dú)立性是指當(dāng)數(shù)據(jù)庫重構(gòu)造時，如增加新的關(guān)系或?qū)υ械年P(guān)系增加新的字段，用戶的應(yīng)用程序不會受影響。層次數(shù)據(jù)庫和網(wǎng)狀數(shù)據(jù)庫一般能較好地支持?jǐn)?shù)據(jù)的物理獨(dú)立性，而對于邏輯獨(dú)立性則不能完全的支持。
在關(guān)系數(shù)據(jù)庫中，數(shù)據(jù)庫的重構(gòu)造往往是不可避免的。重構(gòu)數(shù)據(jù)庫最常見的是將一個基本表“垂直”地分成多個基本表。例如：將學(xué)生關(guān)系student(sid,sname,sex,age,dept,leader)，分為studentinfo(sid,sname,sex,age)和deptinfo(sid,dept)兩個關(guān)系。原表student為studentinfo表和deptinfo表自然連接的結(jié)果。如果建立一個視圖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;

盡管數(shù)據(jù)庫的邏輯結(jié)構(gòu)變?yōu)閟tudentinfo和deptinfo 兩個表，但應(yīng)用程序不必修改，因?yàn)樾陆⒌囊晥D定義為用戶原來的關(guān)系，使用戶的外模式保持不變，用戶的應(yīng)用程序通過視圖仍然能夠查找數(shù)據(jù)。
視圖只能在一定程度上提供數(shù)據(jù)的邏輯獨(dú)立，比如由于視圖的更新是有條件的，因此應(yīng)用程序中修改數(shù)據(jù)的語句可能仍會因?yàn)榛颈順?gòu)造的改變而改變。
D、視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)
在設(shè)計數(shù)據(jù)庫應(yīng)用系統(tǒng)時，可以對不同的用戶定義不同的視圖，使機(jī)密數(shù)據(jù)不出現(xiàn)在不應(yīng)該看到機(jī)密數(shù)據(jù)的用戶視圖上。如student表涉及全校15個院系學(xué)生數(shù)據(jù)，可以在其上定義15個視圖，每個視圖只包含一個院系的學(xué)生數(shù)據(jù)，并只允許每個院系的主任查詢和修改本原系學(xué)生視圖。
E、適當(dāng)?shù)睦靡晥D可以更清晰地表達(dá)查詢
例如經(jīng)常需要執(zhí)行這樣的查詢“對每個學(xué)生找出他獲得最高成績的課程號”?？梢韵榷x一個視圖，求出每個同學(xué)獲得的最高成績。

4、創(chuàng)建視圖的語法

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

創(chuàng)建學(xué)生信息的視圖：

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

二、視圖的操作

1、視圖的使用

視圖的使用和普通表一樣。
select * from studentview;
不能在一張由多張關(guān)聯(lián)表連接而成的視圖上做同時修改兩張表的操作；
視圖與表是一對一關(guān)系情況：如果沒有其它約束（如視圖中沒有的字段，在基本表中是必填字段情況），可以進(jìn)行增刪改數(shù)據(jù)操作。

2、刪除視圖

drop view studentview;

3、通過視圖修改數(shù)據(jù)

如果視圖的基表是一張表，可以通過視圖向基表插入記錄，要求視圖中的沒有的列允許為空。
A、通過視圖插入數(shù)據(jù)到表
insert into studentview(studentID, sname, sex)VALUES('01001', '孫悟空', '男');
查詢插入的記錄，可以看到通過視圖沒有的列，值為空或默認(rèn)值。

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 學(xué)號, sname as 姓名, sex as 性別 from TStudent;

6、WITH CHECK OPTION

如果在創(chuàng)建視圖的時候指定了“WITH CHECK OPTION”，更新數(shù)據(jù)時不能插入或更新不符合視圖限制條件的記錄。

三、視圖實(shí)例

1、使用視圖創(chuàng)建視圖

創(chuàng)建視圖的查詢的表稱為基表，基表可以是視圖和表。

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

2、創(chuàng)建學(xué)生成績表的視圖

創(chuàng)建一個視圖，視圖包含學(xué)生 學(xué)號、姓名、學(xué)科和成績。

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;

創(chuàng)建成績視圖，包含學(xué)號、姓名、計算機(jī)網(wǎng)絡(luò)課程成績、數(shù)據(jù)結(jié)構(gòu)成績、JAVA開發(fā)成績。

create view scoreview
as select studentid 學(xué)號,sname 姓名,
AVG(case subjectname when '計算機(jī)網(wǎng)絡(luò)' then mark END) 計算機(jī)網(wǎng)絡(luò),
AVG(case subjectname when '數(shù)據(jù)結(jié)構(gòu)' then mark END) 數(shù)據(jù)結(jié)構(gòu),
AVG(case subjectname when 'JAVA開發(fā)' then mark END)  JAVA開發(fā) from view1
group by 學(xué)號;