視圖及其應(yīng)用

1、第5章 視圖及其應(yīng)用 教學(xué)目標(biāo)通過本章學(xué)習(xí)，使學(xué)生掌握視圖的基本概念和作用，掌握視圖的建立、修改和操作方法，會(huì)根據(jù)實(shí)際問題的需要，能夠熟練地建立相關(guān)視圖，會(huì)利用視圖查詢數(shù)據(jù)和修改數(shù)據(jù)。 教學(xué)要求知識(shí)要點(diǎn)能力要求關(guān)聯(lián)知識(shí)視圖概念和作用(1)掌握視圖的基本概念和作用視圖概念和作用視圖的建立和操作(1)掌握視圖的建立方法(2)掌握視圖的操作方法SQL Server Management Studio建立和操作視圖，CREATE VIEW，ALTER VIEW等SQL命令視圖的應(yīng)用(1) 掌握利用視圖查詢數(shù)據(jù)的方法(2) 掌握利用視圖修改數(shù)據(jù)的方法視圖的應(yīng)用 重點(diǎn)難點(diǎn) 視圖的概念和作用 視圖的建立和操

2、作方法 利用視圖查詢數(shù)據(jù)的方法175.1任務(wù)描述本章完成項(xiàng)目的第5個(gè)任務(wù)：在大學(xué)生選課管理數(shù)據(jù)庫Student中，完成如下操作：1建立教師的有關(guān)任課信息視圖。2建立學(xué)生的有關(guān)選課信息視圖。3建立每門課程被選修的狀況視圖。5.2視圖綜述5.2.1 視圖的基本概念視圖可以被看成是虛擬表或存儲(chǔ)查詢。除非是索引視圖，否則視圖的數(shù)據(jù)不會(huì)作為非重復(fù)對(duì)象存儲(chǔ)在數(shù)據(jù)庫中。數(shù)據(jù)庫中存儲(chǔ)的是Select 語句。Select語句的結(jié)果集構(gòu)成視圖所返回的虛擬表。用戶可以采用引用表所使用的方法，在SQL語句中引用視圖名稱來使用此虛擬表。視圖是從一個(gè)或者多個(gè)表或視圖中導(dǎo)出的表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對(duì)表的查詢基礎(chǔ)上的。和真

3、實(shí)的表一樣，視圖也包括幾個(gè)被定義的數(shù)據(jù)列和多個(gè)數(shù)據(jù)行，但從本質(zhì)上講，這些數(shù)據(jù)列和數(shù)據(jù)行來源于其所引用的表。因此，視圖不是真實(shí)存在的基礎(chǔ)表而是一個(gè)虛擬表，視圖所對(duì)應(yīng)的數(shù)據(jù)并不實(shí)際地以視圖結(jié)構(gòu)存儲(chǔ)在數(shù)據(jù)庫中，而是存儲(chǔ)在視圖所引用的表中。 5.2.2 視圖的優(yōu)點(diǎn)和作用1可以使用視圖集中數(shù)據(jù)、簡化和定制不同用戶對(duì)數(shù)據(jù)庫的不同數(shù)據(jù)要求。用戶使用數(shù)據(jù)庫中的數(shù)據(jù)時(shí)，最關(guān)心對(duì)自己有用的信息。對(duì)于龐大的數(shù)據(jù)，用戶可只將自己所需的數(shù)據(jù)集中到一個(gè)視圖內(nèi)，而那些不需要的或者無用的數(shù)據(jù)則不在視圖中顯示，從而集中精力處理有用的數(shù)據(jù)。2使用視圖可以屏蔽數(shù)據(jù)的復(fù)雜性，方便用戶對(duì)數(shù)據(jù)的操作，用戶不必了解數(shù)據(jù)庫的結(jié)構(gòu)，就可以方便

4、地使用和管理數(shù)據(jù)，簡化數(shù)據(jù)權(quán)限管理和重新組織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。3視圖便于組織數(shù)據(jù)導(dǎo)出，當(dāng)需要將多個(gè)表中的相關(guān)數(shù)據(jù)導(dǎo)出時(shí)，可以將數(shù)據(jù)集中到一個(gè)視圖內(nèi)，通過視圖導(dǎo)出相關(guān)數(shù)據(jù)，從而簡化了數(shù)據(jù)的交換操作，也大大地簡化了用戶對(duì)數(shù)據(jù)的操作。 4在某些情況下，由于表中數(shù)據(jù)量太大，因此在表的設(shè)計(jì)時(shí)常將表進(jìn)行水平或者垂直分割，但表的結(jié)構(gòu)的變化會(huì)對(duì)應(yīng)用程序產(chǎn)生不良的影響。 5視圖提供了一個(gè)簡單而有效的安全機(jī)制，能夠?qū)?shù)據(jù)提供安全保護(hù)，視圖可以定制顯示數(shù)據(jù)庫中的數(shù)據(jù)信息。因此，數(shù)據(jù)庫管理者為用戶創(chuàng)建視圖時(shí)，就可以只將允許用戶使用的數(shù)據(jù)加入視圖。再通過設(shè)置有關(guān)權(quán)限，使用戶不能訪問基表。6視圖可以跨服務(wù)器組

5、合分區(qū)數(shù)據(jù)，在視圖中可以使用UNION集合運(yùn)算符，將兩個(gè)或多個(gè)查詢結(jié)果集組合到一個(gè)單一的結(jié)果集中，方便用戶使用。視圖還可以讓不同的用戶以不同的方式看到不同或者相同的數(shù)據(jù)集。5.3創(chuàng)建視圖創(chuàng)建視圖時(shí)應(yīng)該注意以下情況： （1）只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖。（2）如果視圖引用的基表或者視圖被刪除，則該視圖不能再被使用，直到創(chuàng)建新的基表或者視圖。（3）如果視圖中某一列是函數(shù)、數(shù)學(xué)表達(dá)式、常量或者來自多個(gè)表的列名相同，則必須為列定義別名。（4）不能在視圖上創(chuàng)建索引，不能在規(guī)則、缺省、觸發(fā)器的定義中引用視圖。（5）當(dāng)通過視圖查詢數(shù)據(jù)時(shí)，SQL Server要檢查以確保語句中涉及的所有數(shù)據(jù)庫對(duì)象存在，而且數(shù)據(jù)

6、修改語句不能違反數(shù)據(jù)完整性規(guī)則。（6）視圖的名稱必須遵循標(biāo)識(shí)符的規(guī)則，且對(duì)每個(gè)用戶必須是唯一的。此外，該名稱不得與該用戶擁有的任何表的名稱相同。5.3.1 使用SQL Server Management Studio創(chuàng)建視圖1啟動(dòng)SQL Server Management Studio，并連接到SQL Server 2008中的數(shù)據(jù)庫，在“對(duì)象資源管理器”窗口中展開“數(shù)據(jù)庫”節(jié)點(diǎn)，再展開新建視圖所屬的數(shù)據(jù)庫名（比如Student），右擊其“視圖”節(jié)點(diǎn)，出現(xiàn)彈出菜單，如圖5-1所示。圖5-1 新建視圖2在彈出菜單中，執(zhí)行【新建視圖】命令，系統(tǒng)彈出“添加表”對(duì)話框,如圖5-2所示。圖5-2“添加表

7、”對(duì)話框3在添加表對(duì)話框中，選擇創(chuàng)建視圖所用的表名和視圖名，單擊“添加”按鈕，將表添加到視圖設(shè)計(jì)器中，在視圖設(shè)計(jì)器窗口的顯示區(qū)域內(nèi)顯示出新加表的所有字段。添加完畢后，最后關(guān)閉“添加表”對(duì)話框，系統(tǒng)出現(xiàn)“視圖設(shè)計(jì)器”界面窗口，如圖5-3所示。圖5-3“視圖設(shè)計(jì)器”窗口4在視圖設(shè)計(jì)器窗口中，從添加表中選擇視圖中顯示的字段顯示于“列”項(xiàng)中，可通過“別名”項(xiàng)設(shè)置相關(guān)字段的別名，可通過“輸出”選項(xiàng)設(shè)置是否輸出相關(guān)字段的值，可通過“排序類型”項(xiàng)下的下拉框設(shè)置視圖的排序字段及排序類型，可通過“篩選器”項(xiàng)下的輸入框輸入視圖記錄的篩選條件等，如圖5-4所示。 圖5-4“視圖設(shè)計(jì)器”窗口5設(shè)置完成后，單擊工具欄上

8、的【保存】按鈕，保存視圖，完成視圖的創(chuàng)建，如圖5-5所示。圖5-5 保存視圖5.3.2 使用SQL命令創(chuàng)建視圖語法形式：CREATE VIEW view_name ( column ,.n ) WITH ENCRYPTION AS select_statement WITH CHECK OPTION 其中：l view_name指定視圖的名稱。l column指定視圖中的列名。若沒有指定，其列名由SELECT命令指派，即為SELECT命令中的列名(注意：視圖中的列名個(gè)數(shù)要與SELECT命令中的列項(xiàng)數(shù)相同)。l WITH ENCRYPTION表示 SQL Server 加密包含 CREATE V

9、IEW 語句文本在內(nèi)的系統(tǒng)表列。l select_statement用于創(chuàng)建視圖的SELECT語句，利用SELECT命令可以從表中或者視圖中選擇列構(gòu)成新視圖的列。 l WITH CHECK OPTION用于強(qiáng)制視圖上執(zhí)行的所有數(shù)據(jù)修改語句都必須符合由 select_statement 設(shè)置的準(zhǔn)則?！纠?-1】在數(shù)據(jù)庫Teaching中，基于表sgrade建立一視圖，視圖名sview_1，要求只能是中文、英文和數(shù)學(xué)都及格的學(xué)生記錄的學(xué)號(hào)，中文、英文、數(shù)學(xué)成績及這三門課程的平均成績。Use TeachingGo Create View sview_1 AS Select xh , zw , yw

10、, sx , (zw+yw+sx)/3 pjcj From sgrade Where zw>=60 and yw>=60 and sx>=60Go【例5-2】在數(shù)據(jù)庫Teaching中，基于表sgrade建立一視圖，視圖名為sview_2，要求該視圖含有各個(gè)系和各個(gè)系的學(xué)生人數(shù)、學(xué)生的平均英語成績。要求重新命名視圖的列，并加密視圖。Use TeachingGo Create View sview_2 (szx , xsrs , pjyw) WITH ENCRYPTION /*加密視圖*/AS Select szx , Count(*) , Avg(yw) From sgrad

11、e Group By szxGo【例5-3】在大學(xué)生選課管理數(shù)據(jù)庫Student中， 建立一視圖，視圖名為st_view1，要求該視圖含有所有教授和副教授的編號(hào)、姓名、年齡和他們所能夠講授課程的編號(hào)及相應(yīng)酬金。Use StudentGo Create View st_view1ASSelect ttab.jsh 教師號(hào) , ttab.xm 姓名 , year(getdate()-year(ttab.csrq) 年齡 , tctab.kch 課程號(hào) , tctab.cj 酬金 From ttab , tctab Where ttab.jsh=tctab.jsh AND ttab.zc IN(教授

12、 , 副教授)Go【例5-4】在大學(xué)生選課管理數(shù)據(jù)庫Student中， 建立一視圖，視圖名為st_view2，要求該視圖含有所選修的課程至少有一門及格的學(xué)生的學(xué)號(hào)、姓名、入學(xué)時(shí)間和所屬系，且按他們?nèi)雽W(xué)時(shí)間的先后順序排列。Use StudentGo Create View st_view2ASSelect xh , xm , rxsj , ssx From stab Where xh IN(Select Distinct xh From sctab Where cj>=60) Order By rxsj ASCGo5.4操作視圖5.4.1 使用SQL Server Management S

13、tudio操作視圖1修改視圖（1）啟動(dòng)SQL Server Management Studio，并連接到SQL Server 2008中的數(shù)據(jù)庫，在“對(duì)象資源管理器”窗口中展開“數(shù)據(jù)庫”節(jié)點(diǎn)，再展開修改的視圖所屬的數(shù)據(jù)庫名（比如Student），再展開其“視圖”節(jié)點(diǎn)，右擊要修改的視圖名，系統(tǒng)出現(xiàn)彈出菜單，如圖5-6所示。圖5-6 修改視圖（2）執(zhí)行彈出菜單中的【設(shè)計(jì)】命令，則進(jìn)入“視圖設(shè)計(jì)器”窗口，如上圖5-4所示，在視圖設(shè)計(jì)器窗口中，修改視圖的有關(guān)選項(xiàng)即可，同創(chuàng)建視圖一樣，修改完畢后，要重新保存視圖。2查看視圖的定義信息同修改視圖的操作一樣，只是查看視圖的定義信息，而不進(jìn)行修改。3刪除或重命

14、名視圖或查看視圖屬性（1）啟動(dòng)SQL Server Management Studio，并連接到SQL Server 2008中的數(shù)據(jù)庫，在“對(duì)象資源管理器”窗口中展開“數(shù)據(jù)庫”節(jié)點(diǎn)，再展開操作的視圖所屬的數(shù)據(jù)庫名（比如Student），展開其“視圖”節(jié)點(diǎn)，右擊要?jiǎng)h除或重命名或查看屬性的視圖名，系統(tǒng)出現(xiàn)彈出菜單，如上圖5-6所示。（2）執(zhí)行彈出菜單中的【刪除】命令，可刪除視圖。（3）執(zhí)行彈出菜單中的【重命名】命令，可重命名視圖。（4）執(zhí)行彈出菜單中的【屬性】命令，可查看視圖的屬性。4查看視圖的內(nèi)容因?yàn)橐晥D本身也是一個(gè)表，因此查看其內(nèi)容同表操作一樣，執(zhí)行上圖5-6彈出菜單中的【編輯前200行】命

15、令即可查看視圖內(nèi)容。5.4.2 使用SQL命令操作視圖1查看視圖內(nèi)容使用視圖查看有關(guān)數(shù)據(jù)信息，同查看表內(nèi)容一樣。語法格式：Select * From 視圖名【例5-5】在大學(xué)生選課管理數(shù)據(jù)庫Student中， 通過視圖st_view1查看所有教授和副教授的編號(hào)、姓名、年齡和他們所能夠講授課程的編號(hào)及相應(yīng)酬金。Use StudentGoSelect * From st_view1Go2. 修改視圖語法格式：ALTER VIEW view_name (column,.n) WITH ENCRYPTION ASselect_statement WITH CHECK OPTION 其中各項(xiàng)參數(shù)同創(chuàng)建視

16、圖SQL命令中的一樣?！纠?-6】將大學(xué)生選課管理數(shù)據(jù)庫Student中的視圖st_view2的篩選條件變?yōu)椤皒h NOT IN(Select Distinct xh From sctab)”，并給視圖加密。Use StudentGo Alter View st_view2WITH ENCRYPTIONASSelect xh , xm , rxsj , ssx From stab Where xh NOT IN(Select Distinct xh From sctab ) Order By rxsj ASCGo3刪除視圖語法格式：DROP VIEW view_name 【例5-7】將上述數(shù)據(jù)

17、庫Teaching中的視圖sview_1刪掉。 Use Teaching Go Drop View sview_1 Go4重命名視圖語法格式：EXEC sp_rename 源視圖名 , 新視圖名5查看視圖定義信息EXEC sp_helptext 視圖名5.5視圖的應(yīng)用5.5.1 利用視圖查詢數(shù)據(jù)因?yàn)橐晥D本身也是一個(gè)表，因此利用視圖查詢數(shù)據(jù)同表的查詢一樣。這里只介紹利用視圖完成一些復(fù)雜的查詢操作，即先查詢所需的中間數(shù)據(jù)，并形成一視圖，然后再對(duì)該視圖進(jìn)行所需的查詢操作，待查詢完畢后，最后將視圖刪除?！纠?-8】從數(shù)據(jù)庫Teaching中的表sgrade中，查詢系學(xué)生人數(shù)在所有系的平均學(xué)生人數(shù)之上的

18、系和該系學(xué)生的人數(shù)，及學(xué)生平均中文成績、平均英文成績、平均數(shù)學(xué)成績。分析：顯然不能直接查詢所需的數(shù)據(jù)，可借助于視圖完成。即先查詢各個(gè)系和各個(gè)系的學(xué)生人數(shù)，及平均中文成績，平均英文成績、平均數(shù)學(xué)成績，并將其形成一視圖sview_3。Use TeachingGoCreate View sview_3(szx , xsrs , pjzw , pjyw , pjsx)ASSelect szx , Count(*) , Avg(zw) , Avg(yw) , Avg(sx) From sgrade Group By szxGo/*從視圖sview_3中查詢所需的最終數(shù)據(jù)*/Select szx 所在系

19、, xsrs 學(xué)生人數(shù) , pjzw 平均中文 , pjyw平均英文 , pjsx平均數(shù)學(xué) From sview_3 Where xsrs>=(Select Avg(xsrs) From sview_3)GoDrop View sview_3Go【例5-9】在大學(xué)生選課管理數(shù)據(jù)庫Student中，查詢選課門數(shù)最多的學(xué)生的學(xué)號(hào)、姓名、所選課程的門數(shù)及選課平均成績，并將查詢結(jié)果按所選課的平均成績降序排列。分析：顯然不能直接查詢所需的數(shù)據(jù)，可借助于視圖完成。即先查詢每個(gè)選課學(xué)生的學(xué)號(hào)、姓名、所選課程的門數(shù)及選課平均成績，并將其形成一視圖st_view3。 Use Student Go Crea

20、te View st_view3ASSelect stab.xh 學(xué)號(hào) , stab.xm 姓名 , Count(*) 選課門數(shù) , Avg(sctab.cj) 平均成績 From stab , sctab Where stab.xh=sctab.xh Group By stab.xh , stab.xmGo/*從視圖st_view3中查詢所需的最終數(shù)據(jù)*/ Select * From st_view3 Where 選課門數(shù)>=ALL(Select 選課門數(shù) From st_view3) Order By 平均成績 Desc Go Drop View st_view3 Go附注：在進(jìn)行復(fù)

21、雜的查詢時(shí)，若使用一次查詢不易實(shí)現(xiàn)，可使用多次查詢來完成。一般借助于臨時(shí)視圖來完成，即先查詢所需中間數(shù)據(jù)形成一視圖，然后再借助于視圖查詢所需最終數(shù)據(jù)。查詢完成后，最后將中間視圖刪除。5.5.2 利用視圖修改數(shù)據(jù)使用視圖修改數(shù)據(jù)時(shí)，需要注意以下幾點(diǎn)： （1）修改視圖中的數(shù)據(jù)時(shí)，不能同時(shí)修改兩個(gè)或者多個(gè)基表，可以對(duì)基于兩個(gè)、多個(gè)基表或者視圖的視圖進(jìn)行修改，但是每次修改都只能影響一個(gè)基表。（2）不能修改那些通過計(jì)算得到的字段。（3）如果在創(chuàng)建視圖時(shí)指定了WITH CHECK OPTION選項(xiàng)，那么在使用視圖修改數(shù)據(jù)庫信息時(shí)，必須保證修改后的數(shù)據(jù)滿足視圖定義的范圍。（4）執(zhí)行UPDATE、DELETE

22、命令時(shí)，所刪除與更新的數(shù)據(jù)必須包含在視圖的結(jié)果集中。（5）如果視圖引用多個(gè)表時(shí)，無法用DELETE命令刪除數(shù)據(jù)。1. 通過視圖插入記錄 【例5-10】在數(shù)據(jù)庫Teaching中，利用視圖向表sgrade中添加一個(gè)學(xué)生記錄。Use TeachingGo/*先創(chuàng)建一個(gè)基于表sgrade的視圖sview_4*/Create View sview_4ASSelect * From sgrade Where xm=李芳 Go /*通過視圖sview_4向表sgrade中添加記錄*/Insert into sview_4 values（100022 , 孫大名 , 男 , 2008-9-1 , 機(jī)械系 ,

23、 08制造 , 80 , 50 , 70）Go2. 通過視圖更新記錄內(nèi)容使用視圖可以更新數(shù)據(jù)記錄，但應(yīng)該注意的是，更新的只是數(shù)據(jù)庫中的基表?！纠?-11】在數(shù)據(jù)庫Teaching中，創(chuàng)建一個(gè)基于表sgrade的視圖sview_5，然后通過該視圖修改表sgrade中的記錄。Use TeachingGo/*先創(chuàng)建一個(gè)基于表sgrade的視圖sview_5*/Create View sview_5ASSelect * From sgrade Go/*通過視圖sview_5修改表sgrade中的記錄*/Update sview_5 Set xm=李麗霞 Where xm=李芳Go3. 通過視圖刪除記錄

24、 使用視圖刪除記錄，可以刪除任何基表中的記錄，直接利用DELETE語句刪除記錄即可。但應(yīng)該注意，必須指定在視圖中定義過的字段來刪除記錄?！纠?-12】在數(shù)據(jù)庫Teaching中，利用視圖sview_5刪除表sgrade中姓名為李麗霞的記錄。Use TeachingGoDelete From sview_5 Where xm=李麗霞 Go5.6 任務(wù)實(shí)現(xiàn)1建立教師的有關(guān)任課信息視圖（1）在數(shù)據(jù)庫Student中，建立一視圖st_view4，該視圖能查詢當(dāng)前所有任課教師的編號(hào)、姓名、性別、職稱和年齡信息。Use StudentGoCreate View st_view4 AS Select jsh

25、 教師號(hào) , xm 姓名 , xb 性別 , zc 職稱 ,year(getdate()-year(csrq) 年齡 From ttab Where jsh IN(Select Distinct jsh From sctab)Go（2）在數(shù)據(jù)庫Student中，建立一視圖st_view5，該視圖能查詢每個(gè)能任課教師的編號(hào)、姓名、職稱和所能夠擔(dān)任的課程。 Use StudentGoCreate View st_view5 AS Select ttab.jch 教師號(hào) , ttab.xm 姓名 , ttab.zc 職稱 , ctab.kcm 能任課程 From ttab , ctab , tcta

26、b Where ttab.jch=tctab.jsh AND ctab.kch=tctab.kchGo（3）在數(shù)據(jù)庫Student中，建立一視圖st_view6，該視圖能查詢每個(gè)教師的編號(hào)、姓名、職稱和所能夠擔(dān)任課程的門數(shù)。Use StudentGoCreate View st_view6 AS Select ttab.jsh教師號(hào) , ttab.xm 姓名 , ttab.zc職稱 , Count(*) 任課門數(shù) From ttab , tctab Where ttab.jsh=tctab.jsh Group By ttab.jsh , ttab.xm , ttab.zc Go 附注：也可使用

27、子查詢完成Use StudentGoCreate View st_view6ASSelect jsh 教師號(hào) , xm 姓名 , zc 職稱 , (Select Count(*) From tctab Where jsh=ttab.jsh) 任課門數(shù) From ttab Go（4）在數(shù)據(jù)庫Student中，建立一視圖st_view7，該視圖能查詢當(dāng)前每個(gè)任課教師的編號(hào)、姓名和所正講授的課程門數(shù)及所教的學(xué)生人數(shù)。Use StudentGoCreate View st_view7ASSelect ttab.jsh 教師號(hào) , ttab.xm 姓名 , Count(DISTINCT sctab.kc

28、h) 講課門數(shù) , Count(DISTINCT sctab.xh) 學(xué)生人數(shù) From ttab , sctab Where ttab.jsh=sctab.jsh Group By ttab.jsh , ttab.xmGo2建立學(xué)生的有關(guān)選課信息視圖（1）在數(shù)據(jù)庫Student中，建立一視圖st_view8，該視圖能查詢每個(gè)選課學(xué)生的學(xué)號(hào)、姓名、所選課程和該課程所選的任課教師及所選課程成績。Use StudentGoCreate View st_view8ASSelect stab.xh 學(xué)號(hào) , stab.xm 姓名 , ctab.kcm 課程 , ttab.xm 教師 , sctab.cj 成績 From stab , ctab , ttab , sctab Where stab.xh=sctab.xh AND ctab.kch=sctab.kch AND ttab.jsh=sctab.jshGo（2）在數(shù)據(jù)庫Student中，建立一視圖st_view9，該視圖能查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、所屬系和所選課程的門數(shù)及平均成績。Use StudentGoCreate