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

1、單元六單元六數(shù)據(jù)視圖項目知識要點與目標項目知識要點與目標項目知識要點項目知識要點知識能力目標知識能力目標學時學時6.1 6.1 任務任務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 任務任務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綜合實例綜合實例 PetStorePetStore數(shù)據(jù)視圖數(shù)據(jù)視圖 【情境情境】：Petstor

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

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

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

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

6、3 3）簡化用戶權限的管理。只需授予用戶使用視圖的權限，而不必）簡化用戶權限的管理。只需授予用戶使用視圖的權限，而不必指定用戶只能使用表的特定列，也增加了安全性。指定用戶只能使用表的特定列，也增加了安全性。（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ù)以便輸出到其他應用程序中。）可以重新組織數(shù)據(jù)以便輸出到其他應用程序中。MySQL MySQL 在在5.05.0版中已經(jīng)實現(xiàn)了視圖功能（包括可更新視圖）。版中

7、已經(jīng)實現(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ù)。若使用與源表或視圖中相同的列名時可以省略列名列表。略列名列表。 WITH CHECK OPTIONWITH CHECK OPTION：指出在可更新視圖上所進行的修改都要符合：指出在可更新視圖上所進行的修改都要符合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視圖，包括計算機類圖視

9、圖，包括計算機類圖書的銷售的訂單號，圖書編號，書名，訂購冊數(shù)等情況。要保證對該書的銷售的訂單號，圖書編號，書名，訂購冊數(shù)等情況。要保證對該視圖的訂單修改都要符合計算機類這個條件。視圖的訂單修改都要符合計算機類這個條件。 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.圖書類別圖書類別= = 計算機計算機 WITH CHECK OPTION; WITH CHECK OPTION;【例例6.26.2】 創(chuàng)建創(chuàng)建BookstoreBookstore數(shù)據(jù)庫中計算機類圖書銷售視圖數(shù)據(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īng)創(chuàng)建了計算機類圖書銷售視圖jsj_selljsj_sell，這里可以直接從，這里可以直接從jsj_selljsj_sell視圖中查詢信息生成新視圖。視圖中查詢信息生成新視圖。1 1、創(chuàng)建視圖、創(chuàng)建視圖Emp_view1,Emp_view1,包含所有男員工的員工號

12、碼、姓名、工作包含所有男員工的員工號碼、姓名、工作年限和學歷，字段名用中文表示年限和學歷，字段名用中文表示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ā)部的員工號碼、姓名和收入。任務二任務二 操作視圖操作視圖 1. 1. 可更新視圖可更新視圖要通過視圖更新基本表數(shù)據(jù)，必須保證視圖是

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

14、NCTDISTINCT關鍵字；關鍵字；（3 3）GROUP BYGROUP BY子句；子句；（4 4）ORDER BYORDER BY子句；子句；（5 5）HAVINGHAVING子句；子句；（6 6）UNIONUNION運算符；運算符；（7 7）位于選擇列表中的子查詢；）位于選擇列表中的子查詢；（8 8）FROMFROM子句中包含多個表；子句中包含多個表；（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，視圖中包含計算機類圖書的信息，視圖中包含計算機類圖書的信息，并向并向jsj_bookjsj_book視圖中插入一條記錄：（視圖中插入一條記錄：（ISBN7-165-12683-7ISBN7-165-12683-7， 計算機計算機 ，OfficeOffice應用實例應用實例 ， 張瑜海張瑜海, , 人民郵電出版社人民郵電出版社 ，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 圖書類別圖書類別 = = 計算機計算機 WITH CHECK OPTION; WITH CHECK OPTION;接下來插入記錄：接下來插入記錄：INSERT INTO jsj_bookINSERT INTO jsj_book VALUES(ISBN7-165-12683-7, VALUES(ISBN7-165-12683-7,計算機計算機,Office,Office應用實例應用實

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

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

19、個視圖依賴于多個基本表，則一次修改該視圖只能變動一個基本表的數(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、 出版社出版社 = =中國青年出版社中國青年出版社;對依賴于多個基本表的視圖，不能使用對依賴于多個基本表的視圖，不能使用DELETEDELETE語句。例如，不能通語句。例如，不能通過對過對jsj_selljsj_sell視圖執(zhí)行視圖執(zhí)行DELETEDELETE語句而刪除與之相關的基本表語句而刪除與之相關的基本表BookBook及及SellSell表的數(shù)據(jù)。表的數(shù)據(jù)。1 1、創(chuàng)建視圖、創(chuàng)建視圖Emp_view3,Emp_view3,包含所有工作年限包含所有工作年限2 2年以上的員工的員工號年以上的員工的員工號碼、姓名、學歷、出生日期、性別和工作年限，所在部門編號。在創(chuàng)建碼、姓名、學歷、出生日期、性別和工作年限，所在部門編號。在創(chuàng)建視圖的時候加上視圖的時候加上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中中“本科本科”學歷的員工學歷的