單元6數(shù)據(jù)視圖

1、單元六單元六數(shù)據(jù)視圖項(xiàng)目知識(shí)要點(diǎn)與目標(biāo)項(xiàng)目知識(shí)要點(diǎn)與目標(biāo)項(xiàng)目知識(shí)要點(diǎn)項(xiàng)目知識(shí)要點(diǎn)知識(shí)能力目標(biāo)知識(shí)能力目標(biāo)學(xué)時(shí)學(xué)時(shí)6.1 6.1 任務(wù)任務(wù)1 1 創(chuàng)建視圖創(chuàng)建視圖 6.1.1 6.1.1 視圖概念視圖概念 5.1.2 5.1.2 創(chuàng)建視圖創(chuàng)建視圖 5.1.3 5.1.3 查詢視圖查詢視圖 6.2 6.2 任務(wù)任務(wù)2 2 操作視圖操作視圖 6.2.1 6.2.1 通過(guò)視圖操作數(shù)據(jù)圖通過(guò)視圖操作數(shù)據(jù)圖 6.2.2 6.2.2 修改視圖定義修改視圖定義 6.2.3 6.2.3 刪除視圖刪除視圖 6.36.3綜合實(shí)例綜合實(shí)例 PetStorePetStore數(shù)據(jù)視圖數(shù)據(jù)視圖 【情境情境】：Petstor

2、ePetstore數(shù)據(jù)視圖數(shù)據(jù)視圖【職業(yè)崗位職業(yè)崗位】：操作員、程序員：操作員、程序員【典型工作任務(wù)典型工作任務(wù)】：創(chuàng)建視圖、操：創(chuàng)建視圖、操作視圖作視圖【技能要求技能要求】：1 1、使用、使用MySQLMySQL命令創(chuàng)建和操作視圖命令創(chuàng)建和操作視圖 2 2、使用圖形工具創(chuàng)建和操作視圖、使用圖形工具創(chuàng)建和操作視圖【知識(shí)要求知識(shí)要求】：1 1、視圖的作用與使用、視圖的作用與使用2 2、修改及刪除視圖的、修改及刪除視圖的SQLSQL語(yǔ)句語(yǔ)句 2 2項(xiàng)目實(shí)訓(xùn)六項(xiàng)目實(shí)訓(xùn)六“員工管理系統(tǒng)員工管理系統(tǒng)”數(shù)據(jù)視圖操作數(shù)據(jù)視圖操作2 2基本表基本表 視圖是數(shù)據(jù)庫(kù)的用戶使用數(shù)據(jù)庫(kù)的觀點(diǎn)。例如，對(duì)于一個(gè)學(xué)校，其學(xué)

3、視圖是數(shù)據(jù)庫(kù)的用戶使用數(shù)據(jù)庫(kù)的觀點(diǎn)。例如，對(duì)于一個(gè)學(xué)校，其學(xué)生的情況存于數(shù)據(jù)庫(kù)的一個(gè)或多個(gè)表中，而作為學(xué)校的不同職能部門，所生的情況存于數(shù)據(jù)庫(kù)的一個(gè)或多個(gè)表中，而作為學(xué)校的不同職能部門，所關(guān)心的學(xué)生數(shù)據(jù)的內(nèi)容是不同的。即使是同樣的數(shù)據(jù)，也可能有不同的操關(guān)心的學(xué)生數(shù)據(jù)的內(nèi)容是不同的。即使是同樣的數(shù)據(jù)，也可能有不同的操作要求，于是就可以根據(jù)他們的不同需求，在物理的數(shù)據(jù)庫(kù)上定義他們對(duì)作要求，于是就可以根據(jù)他們的不同需求，在物理的數(shù)據(jù)庫(kù)上定義他們對(duì)數(shù)據(jù)庫(kù)所要求的數(shù)據(jù)結(jié)構(gòu)，這種根據(jù)用戶觀點(diǎn)所定義的數(shù)據(jù)結(jié)構(gòu)就是視圖。數(shù)據(jù)庫(kù)所要求的數(shù)據(jù)結(jié)構(gòu)，這種根據(jù)用戶觀點(diǎn)所定義的數(shù)據(jù)結(jié)構(gòu)就是視圖。 基本表基本表用戶一視圖

4、基本表基本表用戶二視圖優(yōu)點(diǎn)優(yōu)點(diǎn)視圖與表（有時(shí)為與視圖區(qū)別，也稱表為基本表視圖與表（有時(shí)為與視圖區(qū)別，也稱表為基本表Base TableBase Table）不）不同，視圖是一個(gè)虛表，即視圖所對(duì)應(yīng)的數(shù)據(jù)不進(jìn)行實(shí)際存儲(chǔ)，數(shù)據(jù)庫(kù)中只同，視圖是一個(gè)虛表，即視圖所對(duì)應(yīng)的數(shù)據(jù)不進(jìn)行實(shí)際存儲(chǔ)，數(shù)據(jù)庫(kù)中只存儲(chǔ)視圖的定義，對(duì)視圖的數(shù)據(jù)進(jìn)行操作時(shí)，系統(tǒng)根據(jù)視圖的定義去操作存儲(chǔ)視圖的定義，對(duì)視圖的數(shù)據(jù)進(jìn)行操作時(shí)，系統(tǒng)根據(jù)視圖的定義去操作與視圖相關(guān)聯(lián)的基本表。與視圖相關(guān)聯(lián)的基本表。視圖是從一個(gè)或多個(gè)表（或視圖）導(dǎo)出的表。視圖一經(jīng)定義以后，就視圖是從一個(gè)或多個(gè)表（或視圖）導(dǎo)出的表。視圖一經(jīng)定義以后，就可以像表一樣被查詢

5、、修改、刪除和更新。使用視圖有下列優(yōu)點(diǎn)：可以像表一樣被查詢、修改、刪除和更新。使用視圖有下列優(yōu)點(diǎn)：（1 1）為用戶集中數(shù)據(jù)，簡(jiǎn)化用戶的數(shù)據(jù)查詢和處理。有時(shí)用戶所需）為用戶集中數(shù)據(jù)，簡(jiǎn)化用戶的數(shù)據(jù)查詢和處理。有時(shí)用戶所需要的數(shù)據(jù)分散在多個(gè)表中，定義視圖可將它們集中在一起，從而方便用戶要的數(shù)據(jù)分散在多個(gè)表中，定義視圖可將它們集中在一起，從而方便用戶的數(shù)據(jù)查詢和處理。的數(shù)據(jù)查詢和處理。（2 2）屏蔽數(shù)據(jù)庫(kù)的復(fù)雜性。用戶不必了解復(fù)雜的數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)，）屏蔽數(shù)據(jù)庫(kù)的復(fù)雜性。用戶不必了解復(fù)雜的數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)，并且數(shù)據(jù)庫(kù)表的更改也不影響用戶對(duì)數(shù)據(jù)庫(kù)的使用。并且數(shù)據(jù)庫(kù)表的更改也不影響用戶對(duì)數(shù)據(jù)庫(kù)的使用。（

6、3 3）簡(jiǎn)化用戶權(quán)限的管理。只需授予用戶使用視圖的權(quán)限，而不必）簡(jiǎn)化用戶權(quán)限的管理。只需授予用戶使用視圖的權(quán)限，而不必指定用戶只能使用表的特定列，也增加了安全性。指定用戶只能使用表的特定列，也增加了安全性。（4 4）便于數(shù)據(jù)共享。各用戶不必都定義和存儲(chǔ)自己所需的數(shù)據(jù)，可）便于數(shù)據(jù)共享。各用戶不必都定義和存儲(chǔ)自己所需的數(shù)據(jù)，可共享數(shù)據(jù)庫(kù)的數(shù)據(jù)，這樣同樣的數(shù)據(jù)只需存儲(chǔ)一次。共享數(shù)據(jù)庫(kù)的數(shù)據(jù)，這樣同樣的數(shù)據(jù)只需存儲(chǔ)一次。（5 5）可以重新組織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。）可以重新組織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。MySQL MySQL 在在5.05.0版中已經(jīng)實(shí)現(xiàn)了視圖功能（包括可更新視圖）。版中

7、已經(jīng)實(shí)現(xiàn)了視圖功能（包括可更新視圖）。1. 1. 使用使用CREATE VIEWCREATE VIEW語(yǔ)句創(chuàng)建視圖語(yǔ)句創(chuàng)建視圖語(yǔ)法格式：語(yǔ)法格式： CREATE OR REPLACE VIEW 視圖名視圖名 (列名列表列名列表) AS select語(yǔ)句語(yǔ)句 WITH CASCADED | LOCAL CHECK OPTION 列列名列表名列表：要想為視圖的列定義明確的名稱，可使用可選的列名列：要想為視圖的列定義明確的名稱，可使用可選的列名列表子句，列出由逗號(hào)隔開(kāi)的列名。列名列表中的名稱數(shù)目必須等于表子句，列出由逗號(hào)隔開(kāi)的列名。列名列表中的名稱數(shù)目必須等于SELECTSELECT語(yǔ)句檢索的列數(shù)。

8、若使用與源表或視圖中相同的列名時(shí)可以省語(yǔ)句檢索的列數(shù)。若使用與源表或視圖中相同的列名時(shí)可以省略列名列表。略列名列表。 WITH CHECK OPTIONWITH CHECK OPTION：指出在可更新視圖上所進(jìn)行的修改都要符合：指出在可更新視圖上所進(jìn)行的修改都要符合selectselect語(yǔ)句所指定的限制條件，這樣可以確保數(shù)據(jù)修改后，仍可通過(guò)語(yǔ)句所指定的限制條件，這樣可以確保數(shù)據(jù)修改后，仍可通過(guò)視圖看到修改的數(shù)據(jù)。視圖看到修改的數(shù)據(jù)。創(chuàng)建視圖舉例創(chuàng)建視圖舉例【例例6.16.1】創(chuàng)建創(chuàng)建BookstoreBookstore數(shù)據(jù)庫(kù)上的數(shù)據(jù)庫(kù)上的jsj_selljsj_sell視圖，包括計(jì)算機(jī)類圖視

9、圖，包括計(jì)算機(jī)類圖書的銷售的訂單號(hào)，圖書編號(hào)，書名，訂購(gòu)冊(cè)數(shù)等情況。要保證對(duì)該書的銷售的訂單號(hào)，圖書編號(hào)，書名，訂購(gòu)冊(cè)數(shù)等情況。要保證對(duì)該視圖的訂單修改都要符合計(jì)算機(jī)類這個(gè)條件。視圖的訂單修改都要符合計(jì)算機(jī)類這個(gè)條件。 CREATE OR REPLACE VIEW jsj_sell CREATE OR REPLACE VIEW jsj_sell AS AS SELECT SELECT 訂單號(hào)訂單號(hào),Sell.,Sell.圖書編號(hào)圖書編號(hào), ,書名書名, ,訂購(gòu)冊(cè)數(shù)訂購(gòu)冊(cè)數(shù) FROM Book, Sell FROM Book, Sell WHERE Book. WHERE Book.圖書編號(hào)圖書

10、編號(hào)=Sell. =Sell. 圖書編號(hào)圖書編號(hào) AND Book. AND Book.圖書類別圖書類別= = 計(jì)算機(jī)計(jì)算機(jī) WITH CHECK OPTION; WITH CHECK OPTION;【例例6.26.2】 創(chuàng)建創(chuàng)建BookstoreBookstore數(shù)據(jù)庫(kù)中計(jì)算機(jī)類圖書銷售視圖數(shù)據(jù)庫(kù)中計(jì)算機(jī)類圖書銷售視圖sale_AVGsale_AVG，包括書名（在視圖中列名為包括書名（在視圖中列名為namename）和該圖書的平均訂購(gòu)冊(cè)數(shù)（在視圖中）和該圖書的平均訂購(gòu)冊(cè)數(shù)（在視圖中列名為列名為sale_avgsale_avg）。）。 CREATE VIEW sale_avg (name, s

11、ale_avg)CREATE VIEW sale_avg (name, sale_avg) AS AS SELECT SELECT 書名書名,avg(,avg(訂購(gòu)冊(cè)數(shù)訂購(gòu)冊(cè)數(shù)) ) FROM jsj_sell FROM jsj_sell GROUP BY GROUP BY書名書名; ;例例6.16.1已經(jīng)創(chuàng)建了計(jì)算機(jī)類圖書銷售視圖已經(jīng)創(chuàng)建了計(jì)算機(jī)類圖書銷售視圖jsj_selljsj_sell，這里可以直接從，這里可以直接從jsj_selljsj_sell視圖中查詢信息生成新視圖。視圖中查詢信息生成新視圖。1 1、創(chuàng)建視圖、創(chuàng)建視圖Emp_view1,Emp_view1,包含所有男員工的員工號(hào)

12、碼、姓名、工作包含所有男員工的員工號(hào)碼、姓名、工作年限和學(xué)歷，字段名用中文表示年限和學(xué)歷，字段名用中文表示2 2、從、從Emp_view1Emp_view1查詢工作年限在兩年以上的員工信息。查詢工作年限在兩年以上的員工信息。3 3、創(chuàng)建創(chuàng)建視圖、創(chuàng)建創(chuàng)建視圖Emp_view 2Emp_view 2、包含員工號(hào)碼、姓名、所在部門名、包含員工號(hào)碼、姓名、所在部門名稱和收入稱和收入4 4、從、從Emp_view2Emp_view2查詢研發(fā)部的員工號(hào)碼、姓名和收入。查詢研發(fā)部的員工號(hào)碼、姓名和收入。任務(wù)二任務(wù)二 操作視圖操作視圖 1. 1. 可更新視圖可更新視圖要通過(guò)視圖更新基本表數(shù)據(jù)，必須保證視圖是

13、可更新視圖，即可以在要通過(guò)視圖更新基本表數(shù)據(jù)，必須保證視圖是可更新視圖，即可以在INSETINSET、UPDATEUPDATE或或DELETEDELETE等語(yǔ)句當(dāng)中使用它們。對(duì)于可更新的視圖，在視圖中的行和基表等語(yǔ)句當(dāng)中使用它們。對(duì)于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對(duì)一的關(guān)系。還有一些特定的其他結(jié)構(gòu)，這類結(jié)構(gòu)會(huì)使得中的行之間必須具有一對(duì)一的關(guān)系。還有一些特定的其他結(jié)構(gòu)，這類結(jié)構(gòu)會(huì)使得視圖不可更新。如果視圖包含下述結(jié)構(gòu)中的任何一種，那么它就是不可更新的：視圖不可更新。如果視圖包含下述結(jié)構(gòu)中的任何一種，那么它就是不可更新的： （1 1）聚合函數(shù)；）聚合函數(shù)；（2 2）DISTI

14、NCTDISTINCT關(guān)鍵字；關(guān)鍵字；（3 3）GROUP BYGROUP BY子句；子句；（4 4）ORDER BYORDER BY子句；子句；（5 5）HAVINGHAVING子句；子句；（6 6）UNIONUNION運(yùn)算符；運(yùn)算符；（7 7）位于選擇列表中的子查詢；）位于選擇列表中的子查詢；（8 8）FROMFROM子句中包含多個(gè)表；子句中包含多個(gè)表；（9 9）SELECTSELECT語(yǔ)句中引用了不可更新視圖；語(yǔ)句中引用了不可更新視圖；通過(guò)視圖插入數(shù)據(jù)通過(guò)視圖插入數(shù)據(jù)2. 2. 插入數(shù)據(jù)插入數(shù)據(jù)使用使用INSERTINSERT語(yǔ)句通過(guò)視圖向基本表插入數(shù)據(jù)語(yǔ)句通過(guò)視圖向基本表插入數(shù)據(jù)【例例

15、6.56.5】 創(chuàng)建視圖創(chuàng)建視圖jsj_bookjsj_book，視圖中包含計(jì)算機(jī)類圖書的信息，視圖中包含計(jì)算機(jī)類圖書的信息，并向并向jsj_bookjsj_book視圖中插入一條記錄：（視圖中插入一條記錄：（ISBN7-165-12683-7ISBN7-165-12683-7， 計(jì)算機(jī)計(jì)算機(jī) ，OfficeOffice應(yīng)用實(shí)例應(yīng)用實(shí)例 ， 張瑜海張瑜海, , 人民郵電出版社人民郵電出版社 ，2011-10-212011-10-21，34.534.5，NULLNULL，NULLNULL，NULLNULL）。）。首先創(chuàng)建視圖首先創(chuàng)建視圖jsj_bookjsj_book： CREATE OR RE

16、PLACE VIEW jsj_book CREATE OR REPLACE VIEW jsj_book AS AS SELECT SELECT * * FROM Book FROM Book WHERE WHERE 圖書類別圖書類別 = = 計(jì)算機(jī)計(jì)算機(jī) WITH CHECK OPTION; WITH CHECK OPTION;接下來(lái)插入記錄：接下來(lái)插入記錄：INSERT INTO jsj_bookINSERT INTO jsj_book VALUES(ISBN7-165-12683-7, VALUES(ISBN7-165-12683-7,計(jì)算機(jī)計(jì)算機(jī),Office,Office應(yīng)用實(shí)例應(yīng)用實(shí)

17、例, 張瑜海張瑜海,人民郵電出版社人民郵電出版社,2011-10-,2011-10-21,34.5,NULL,NULL,NULL21,34.5,NULL,NULL,NULL ); );通過(guò)視圖修改數(shù)據(jù)通過(guò)視圖修改數(shù)據(jù)當(dāng)視圖所依賴的基本表有多個(gè)時(shí)，不能向該視圖插入數(shù)據(jù)，因?yàn)楫?dāng)視圖所依賴的基本表有多個(gè)時(shí)，不能向該視圖插入數(shù)據(jù)，因?yàn)檫@將會(huì)影響多個(gè)基本表。這將會(huì)影響多個(gè)基本表。對(duì)對(duì)INSERTINSERT語(yǔ)句還有一個(gè)限制：語(yǔ)句還有一個(gè)限制：SELECTSELECT語(yǔ)句中必須包含語(yǔ)句中必須包含F(xiàn)ROMFROM子句中子句中指定表的所有不能為空的列。指定表的所有不能為空的列。3. 3. 修改數(shù)據(jù)修改數(shù)據(jù)使用

18、使用UPDATEUPDATE語(yǔ)句可以通過(guò)視圖修改基本表的數(shù)據(jù)語(yǔ)句可以通過(guò)視圖修改基本表的數(shù)據(jù)【例例6.66.6】 將將jsj_bookjsj_book視圖中所有單價(jià)降低視圖中所有單價(jià)降低% %。 UPDATE jsj_book UPDATE jsj_book SET SET 單價(jià)單價(jià) = = 單價(jià)單價(jià)* *(1-0.05);(1-0.05);該語(yǔ)句實(shí)際上是將該語(yǔ)句實(shí)際上是將jsj_bookjsj_book視圖所依賴的基本表視圖所依賴的基本表BookBook中所有計(jì)算中所有計(jì)算機(jī)類圖書的單價(jià)也都降低了機(jī)類圖書的單價(jià)也都降低了5%5%。若一個(gè)視圖依賴于多個(gè)基本表，則一次修改該視圖只能變動(dòng)一個(gè)基若一

19、個(gè)視圖依賴于多個(gè)基本表，則一次修改該視圖只能變動(dòng)一個(gè)基本表的數(shù)據(jù)。本表的數(shù)據(jù)?！纠?.76.7】 將將jsj_selljsj_sell視圖中圖書編號(hào)為視圖中圖書編號(hào)為ISBN 8-5006-6625-XISBN 8-5006-6625-X的書的書名改為名改為“ASP.NETASP.NET網(wǎng)站制作網(wǎng)站制作”，訂單號(hào)為，訂單號(hào)為6 6的訂購(gòu)冊(cè)數(shù)改為的訂購(gòu)冊(cè)數(shù)改為8 8。 UPDATE jsj_sell UPDATE jsj_sell SET SET 書名書名=ASP.NET=ASP.NET網(wǎng)站制作網(wǎng)站制作 WHERE WHERE 圖書編號(hào)圖書編號(hào)=ISBN 8-5006-6625-X;=ISBN

20、 8-5006-6625-X;和和 UPDATE jsj_sell UPDATE jsj_sell SET SET 訂購(gòu)冊(cè)數(shù)訂購(gòu)冊(cè)數(shù)=8=8 WHERE WHERE 訂單號(hào)訂單號(hào)=6;=6;視圖修改數(shù)據(jù)舉例視圖修改數(shù)據(jù)舉例通過(guò)視圖刪除數(shù)據(jù)通過(guò)視圖刪除數(shù)據(jù)4. 4. 刪除數(shù)據(jù)刪除數(shù)據(jù)使用使用DELETEDELETE語(yǔ)句可以通過(guò)視圖刪除基本表的數(shù)據(jù)語(yǔ)句可以通過(guò)視圖刪除基本表的數(shù)據(jù)【例例6.86.8】 刪除刪除jsj_bookjsj_book中中“中國(guó)青年出版社中國(guó)青年出版社”的記錄。的記錄。 DELETE FROM jsj_book DELETE FROM jsj_book WHERE WHERE

21、 出版社出版社 = =中國(guó)青年出版社中國(guó)青年出版社;對(duì)依賴于多個(gè)基本表的視圖，不能使用對(duì)依賴于多個(gè)基本表的視圖，不能使用DELETEDELETE語(yǔ)句。例如，不能通語(yǔ)句。例如，不能通過(guò)對(duì)過(guò)對(duì)jsj_selljsj_sell視圖執(zhí)行視圖執(zhí)行DELETEDELETE語(yǔ)句而刪除與之相關(guān)的基本表語(yǔ)句而刪除與之相關(guān)的基本表BookBook及及SellSell表的數(shù)據(jù)。表的數(shù)據(jù)。1 1、創(chuàng)建視圖、創(chuàng)建視圖Emp_view3,Emp_view3,包含所有工作年限包含所有工作年限2 2年以上的員工的員工號(hào)年以上的員工的員工號(hào)碼、姓名、學(xué)歷、出生日期、性別和工作年限，所在部門編號(hào)。在創(chuàng)建碼、姓名、學(xué)歷、出生日期、性別和工作年限，所在部門編號(hào)。在創(chuàng)建視圖的時(shí)候加上視圖的時(shí)候加上WITH CHECK OPTIONWITH CHECK OPTION子句子句2 2、從、從Emp_view3Emp_view3插入一條記錄：插入一條記錄：（041110,041110,鐘曉玲鐘曉玲, ,博士博士,1973-12-01,1973-12-01,男男,3,4),3,4)。3 3、 修改修改Emp_view2Emp_view2中將中將“李麗李麗”的收入加的收入加200200元元4 4、刪除視圖、刪除視圖Emp_view3Emp_view3中中“本科本科”學(xué)歷的員工學(xué)歷的