視圖及其應(yīng)用

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

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

3、實的表一樣，視圖也包括幾個被定義的數(shù)據(jù)列和多個數(shù)據(jù)行，但從本質(zhì)上講，這些數(shù)據(jù)列和數(shù)據(jù)行來源于其所引用的表。因此，視圖不是真實存在的基礎(chǔ)表而是一個虛擬表，視圖所對應(yīng)的數(shù)據(jù)并不實際地以視圖結(jié)構(gòu)存儲在數(shù)據(jù)庫中，而是存儲在視圖所引用的表中。 5.2.2 視圖的優(yōu)點和作用1可以使用視圖集中數(shù)據(jù)、簡化和定制不同用戶對數(shù)據(jù)庫的不同數(shù)據(jù)要求。用戶使用數(shù)據(jù)庫中的數(shù)據(jù)時，最關(guān)心對自己有用的信息。對于龐大的數(shù)據(jù)，用戶可只將自己所需的數(shù)據(jù)集中到一個視圖內(nèi)，而那些不需要的或者無用的數(shù)據(jù)則不在視圖中顯示，從而集中精力處理有用的數(shù)據(jù)。2使用視圖可以屏蔽數(shù)據(jù)的復(fù)雜性，方便用戶對數(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)需要將多個表中的相關(guān)數(shù)據(jù)導(dǎo)出時，可以將數(shù)據(jù)集中到一個視圖內(nèi)，通過視圖導(dǎo)出相關(guān)數(shù)據(jù)，從而簡化了數(shù)據(jù)的交換操作，也大大地簡化了用戶對數(shù)據(jù)的操作。 4在某些情況下，由于表中數(shù)據(jù)量太大，因此在表的設(shè)計時常將表進行水平或者垂直分割，但表的結(jié)構(gòu)的變化會對應(yīng)用程序產(chǎn)生不良的影響。 5視圖提供了一個簡單而有效的安全機制，能夠?qū)?shù)據(jù)提供安全保護，視圖可以定制顯示數(shù)據(jù)庫中的數(shù)據(jù)信息。因此，數(shù)據(jù)庫管理者為用戶創(chuàng)建視圖時，就可以只將允許用戶使用的數(shù)據(jù)加入視圖。再通過設(shè)置有關(guān)權(quán)限，使用戶不能訪問基表。6視圖可以跨服務(wù)器組

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

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

7、”對話框3在添加表對話框中，選擇創(chuàng)建視圖所用的表名和視圖名，單擊“添加”按鈕，將表添加到視圖設(shè)計器中，在視圖設(shè)計器窗口的顯示區(qū)域內(nèi)顯示出新加表的所有字段。添加完畢后，最后關(guān)閉“添加表”對話框，系統(tǒng)出現(xiàn)“視圖設(shè)計器”界面窗口，如圖5-3所示。圖5-3“視圖設(shè)計器”窗口4在視圖設(shè)計器窗口中，從添加表中選擇視圖中顯示的字段顯示于“列”項中，可通過“別名”項設(shè)置相關(guān)字段的別名，可通過“輸出”選項設(shè)置是否輸出相關(guān)字段的值，可通過“排序類型”項下的下拉框設(shè)置視圖的排序字段及排序類型，可通過“篩選器”項下的輸入框輸入視圖記錄的篩選條件等，如圖5-4所示。 圖5-4“視圖設(shè)計器”窗口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命令中的列名(注意：視圖中的列名個數(shù)要與SELECT命令中的列項數(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用于強制視圖上執(zhí)行的所有數(shù)據(jù)修改語句都必須符合由 select_statement 設(shè)置的準(zhǔn)則。【例5-1】在數(shù)據(jù)庫Teaching中，基于表sgrade建立一視圖，視圖名sview_1，要求只能是中文、英文和數(shù)學(xué)都及格的學(xué)生記錄的學(xué)號，中文、英文、數(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，要求該視圖含有各個系和各個系的學(xué)生人數(shù)、學(xué)生的平均英語成績。要求重新命名視圖的列，并加密視圖。Use TeachingGo Create View sview_2 (szx , xsrs , pjyw) WITH ENCRYPTION /*加密視圖*/AS Select szx , Count(*) , Avg(yw) From sgrade Group By s

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

12、-4】在大學(xué)生選課管理數(shù)據(jù)庫Student中， 建立一視圖，視圖名為st_view2，要求該視圖含有所選修的課程至少有一門及格的學(xué)生的學(xué)號、姓名、入學(xué)時間和所屬系，且按他們?nèi)雽W(xué)時間的先后順序排列。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 Studio操作視圖1修改視圖（1

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

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

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

16、】將大學(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ù)庫Teaching中的視圖svi

17、ew_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ù)因為視圖本身也是一個表，因此利用視圖查詢數(shù)據(jù)同表的查詢一樣。這里只介紹利用視圖完成一些復(fù)雜的查詢操作，即先查詢所需的中間數(shù)據(jù)，并形成一視圖，然后再對該視圖進行所需的查詢操作，待查詢完畢后，最后將視圖刪除。【例5-8】從數(shù)據(jù)庫Teaching中的表sgrade中，查詢系學(xué)生人數(shù)在所有系的平均學(xué)生人數(shù)之上的系和該系學(xué)生的人數(shù)，及學(xué)生平均中

18、文成績、平均英文成績、平均數(shù)學(xué)成績。分析：顯然不能直接查詢所需的數(shù)據(jù)，可借助于視圖完成。即先查詢各個系和各個系的學(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 所在系 , xsrs 學(xué)生人數(shù) , pj

19、zw 平均中文 , 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é)號、姓名、所選課程的門數(shù)及選課平均成績，并將查詢結(jié)果按所選課的平均成績降序排列。分析：顯然不能直接查詢所需的數(shù)據(jù)，可借助于視圖完成。即先查詢每個選課學(xué)生的學(xué)號、姓名、所選課程的門數(shù)及選課平均成績，并將其形成一視圖st_view3。 Use Student Go Create View st_view3ASSe

20、lect stab.xh 學(xué)號 , 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附注：在進行復(fù)雜的查詢時，若使用一次查詢不易實現(xiàn)，可使用多次查

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

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

23、. 通過視圖更新記錄內(nèi)容使用視圖可以更新數(shù)據(jù)記錄，但應(yīng)該注意的是，更新的只是數(shù)據(jù)庫中的基表?！纠?-11】在數(shù)據(jù)庫Teaching中，創(chuàng)建一個基于表sgrade的視圖sview_5，然后通過該視圖修改表sgrade中的記錄。Use TeachingGo/*先創(chuàng)建一個基于表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ù)實現(xiàn)1建立教師的有關(guān)任課信息視圖（1）在數(shù)據(jù)庫Student中，建立一視圖st_view4，該視圖能查詢當(dāng)前所有任課教師的編號、姓名、性別、職稱和年齡信息。Use StudentGoCreate View st_view4 AS Select jsh 教師號 , xm 姓名 , xb 性別 , z

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

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

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

28、 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，該視圖能查詢每個選課學(xué)生的學(xué)號、姓名、所選課程和該課程所選的任課教師及所選課程成績。Use StudentGoCreate View st_view8ASSelect stab.xh 學(xué)號 , 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，該視圖能查詢每個學(xué)生的學(xué)號、姓名、所屬系和所選課程的門數(shù)及平均成績。Use StudentGoCreate V