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

1、單元六單元六數(shù)據(jù)視圖項(xiàng)目知識要點(diǎn)與目標(biāo)項(xiàng)目知識要點(diǎn)與目標(biāo)項(xiàng)目知識要點(diǎn)項(xiàng)目知識要點(diǎn)知識能力目標(biāo)知識能力目標(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 通過視圖操作數(shù)據(jù)圖通過視圖操作數(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)建和操作視圖【知識要求知識要求】：1 1、視圖的作用與使用、視圖的作用與使用2 2、修改及刪除視圖的、修改及刪除視圖的SQLSQL語句語句 2 2項(xiàng)目實(shí)訓(xùn)六項(xiàng)目實(shí)訓(xùn)六“員工管理系統(tǒng)員工管理系統(tǒng)”數(shù)據(jù)視圖操作數(shù)據(jù)視圖操作2 2基本表基本表 視圖是數(shù)據(jù)庫的用戶使用數(shù)據(jù)庫的觀點(diǎn)。例如，對于一個(gè)學(xué)校，其學(xué)

3、視圖是數(shù)據(jù)庫的用戶使用數(shù)據(jù)庫的觀點(diǎn)。例如，對于一個(gè)學(xué)校，其學(xué)生的情況存于數(shù)據(jù)庫的一個(gè)或多個(gè)表中，而作為學(xué)校的不同職能部門，所生的情況存于數(shù)據(jù)庫的一個(gè)或多個(gè)表中，而作為學(xué)校的不同職能部門，所關(guān)心的學(xué)生數(shù)據(jù)的內(nèi)容是不同的。即使是同樣的數(shù)據(jù)，也可能有不同的操關(guān)心的學(xué)生數(shù)據(jù)的內(nèi)容是不同的。即使是同樣的數(shù)據(jù)，也可能有不同的操作要求，于是就可以根據(jù)他們的不同需求，在物理的數(shù)據(jù)庫上定義他們對作要求，于是就可以根據(jù)他們的不同需求，在物理的數(shù)據(jù)庫上定義他們對數(shù)據(jù)庫所要求的數(shù)據(jù)結(jié)構(gòu)，這種根據(jù)用戶觀點(diǎn)所定義的數(shù)據(jù)結(jié)構(gòu)就是視圖。數(shù)據(jù)庫所要求的數(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è)虛表，即視圖所對應(yīng)的數(shù)據(jù)不進(jìn)行實(shí)際存儲，數(shù)據(jù)庫中只同，視圖是一個(gè)虛表，即視圖所對應(yīng)的數(shù)據(jù)不進(jìn)行實(shí)際存儲，數(shù)據(jù)庫中只存儲視圖的定義，對視圖的數(shù)據(jù)進(jìn)行操作時(shí)，系統(tǒng)根據(jù)視圖的定義去操作存儲視圖的定義，對視圖的數(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ù)，簡化用戶的數(shù)據(jù)查詢和處理。有時(shí)用戶所需）為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。有時(shí)用戶所需要的數(shù)據(jù)分散在多個(gè)表中，定義視圖可將它們集中在一起，從而方便用戶要的數(shù)據(jù)分散在多個(gè)表中，定義視圖可將它們集中在一起，從而方便用戶的數(shù)據(jù)查詢和處理。的數(shù)據(jù)查詢和處理。（2 2）屏蔽數(shù)據(jù)庫的復(fù)雜性。用戶不必了解復(fù)雜的數(shù)據(jù)庫中的表結(jié)構(gòu)，）屏蔽數(shù)據(jù)庫的復(fù)雜性。用戶不必了解復(fù)雜的數(shù)據(jù)庫中的表結(jié)構(gòu)，并且數(shù)據(jù)庫表的更改也不影響用戶對數(shù)據(jù)庫的使用。并且數(shù)據(jù)庫表的更改也不影響用戶對數(shù)據(jù)庫的使用。（

6、3 3）簡化用戶權(quán)限的管理。只需授予用戶使用視圖的權(quán)限，而不必）簡化用戶權(quán)限的管理。只需授予用戶使用視圖的權(quán)限，而不必指定用戶只能使用表的特定列，也增加了安全性。指定用戶只能使用表的特定列，也增加了安全性。（4 4）便于數(shù)據(jù)共享。各用戶不必都定義和存儲自己所需的數(shù)據(jù)，可）便于數(shù)據(jù)共享。各用戶不必都定義和存儲自己所需的數(shù)據(jù)，可共享數(shù)據(jù)庫的數(shù)據(jù)，這樣同樣的數(shù)據(jù)只需存儲一次。共享數(shù)據(jù)庫的數(shù)據(jù)，這樣同樣的數(shù)據(jù)只需存儲一次。（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語句創(chuàng)建視圖語句創(chuàng)建視圖語法格式：語法格式： CREATE OR REPLACE VIEW 視圖名視圖名 (列名列表列名列表) AS select語句語句 WITH CASCADED | LOCAL CHECK OPTION 列列名列表名列表：要想為視圖的列定義明確的名稱，可使用可選的列名列：要想為視圖的列定義明確的名稱，可使用可選的列名列表子句，列出由逗號隔開的列名。列名列表中的名稱數(shù)目必須等于表子句，列出由逗號隔開的列名。列名列表中的名稱數(shù)目必須等于SELECTSELECT語句檢索的列數(shù)。

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

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

10、編號=Sell. =Sell. 圖書編號圖書編號 AND Book. AND Book.圖書類別圖書類別= = 計(jì)算機(jī)計(jì)算機(jī) WITH CHECK OPTION; WITH CHECK OPTION;【例例6.26.2】 創(chuàng)建創(chuàng)建BookstoreBookstore數(shù)據(jù)庫中計(jì)算機(jī)類圖書銷售視圖數(shù)據(jù)庫中計(jì)算機(jī)類圖書銷售視圖sale_AVGsale_AVG，包括書名（在視圖中列名為包括書名（在視圖中列名為namename）和該圖書的平均訂購冊數(shù)（在視圖中）和該圖書的平均訂購冊數(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(訂購冊數(shù)訂購冊數(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,包含所有男員工的員工號

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

13、可更新視圖，即可以在要通過視圖更新基本表數(shù)據(jù)，必須保證視圖是可更新視圖，即可以在INSETINSET、UPDATEUPDATE或或DELETEDELETE等語句當(dāng)中使用它們。對于可更新的視圖，在視圖中的行和基表等語句當(dāng)中使用它們。對于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對一的關(guān)系。還有一些特定的其他結(jié)構(gòu)，這類結(jié)構(gòu)會使得中的行之間必須具有一對一的關(guān)系。還有一些特定的其他結(jié)構(gòu)，這類結(jié)構(gòu)會使得視圖不可更新。如果視圖包含下述結(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語句中引用了不可更新視圖；語句中引用了不可更新視圖；通過視圖插入數(shù)據(jù)通過視圖插入數(shù)據(jù)2. 2. 插入數(shù)據(jù)插入數(shù)據(jù)使用使用INSERTINSERT語句通過視圖向基本表插入數(shù)據(jù)語句通過視圖向基本表插入數(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;接下來插入記錄：接下來插入記錄：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 ); );通過視圖修改數(shù)據(jù)通過視圖修改數(shù)據(jù)當(dāng)視圖所依賴的基本表有多個(gè)時(shí)，不能向該視圖插入數(shù)據(jù)，因?yàn)楫?dāng)視圖所依賴的基本表有多個(gè)時(shí)，不能向該視圖插入數(shù)據(jù)，因?yàn)檫@將會影響多個(gè)基本表。這將會影響多個(gè)基本表。對對INSERTINSERT語句還有一個(gè)限制：語句還有一個(gè)限制：SELECTSELECT語句中必須包含語句中必須包含F(xiàn)ROMFROM子句中子句中指定表的所有不能為空的列。指定表的所有不能為空的列。3. 3. 修改數(shù)據(jù)修改數(shù)據(jù)使用

18、使用UPDATEUPDATE語句可以通過視圖修改基本表的數(shù)據(jù)語句可以通過視圖修改基本表的數(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);該語句實(shí)際上是將該語句實(shí)際上是將jsj_bookjsj_book視圖所依賴的基本表視圖所依賴的基本表BookBook中所有計(jì)算中所有計(jì)算機(jī)類圖書的單價(jià)也都降低了機(jī)類圖書的單價(jià)也都降低了5%5%。若一個(gè)視圖依賴于多個(gè)基本表，則一次修改該視圖只能變動一個(gè)基若一

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

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

21、 出版社出版社 = =中國青年出版社中國青年出版社;對依賴于多個(gè)基本表的視圖，不能使用對依賴于多個(gè)基本表的視圖，不能使用DELETEDELETE語句。例如，不能通語句。例如，不能通過對過對jsj_selljsj_sell視圖執(zhí)行視圖執(zhí)行DELETEDELETE語句而刪除與之相關(guān)的基本表語句而刪除與之相關(guān)的基本表BookBook及及SellSell表的數(shù)據(jù)。表的數(shù)據(jù)。1 1、創(chuàng)建視圖、創(chuàng)建視圖Emp_view3,Emp_view3,包含所有工作年限包含所有工作年限2 2年以上的員工的員工號年以上的員工的員工號碼、姓名、學(xué)歷、出生日期、性別和工作年限，所在部門編號。在創(chuàng)建碼、姓名、學(xué)歷、出生日期、

22、性別和工作年限，所在部門編號。在創(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é)歷的員工使用使用ALERTALERT語句可以對已有視圖的定義進(jìn)行修改。語句可以對已有視圖的定義進(jìn)行修改。語法格式：語法格式： ALTER VIEW ALTER VIEW 視圖名視圖名 ( (列名列表列名列表) AS select AS select語句語句 WITH CASCADED | LOCAL C