《SQL server數(shù)據(jù)庫》課件第5章 視圖及應(yīng)用

第5章視圖及其應(yīng)用

5.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ǔ)上的。和真實(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ù)、簡(jiǎn)化和定制不同用戶對(duì)數(shù)據(jù)庫的不同數(shù)據(jù)要求。2．使用視圖可以屏蔽數(shù)據(jù)的復(fù)雜性，方便用戶對(duì)數(shù)據(jù)的操作，用戶不必了解數(shù)據(jù)庫的結(jié)構(gòu)，就可以方便地使用和管理數(shù)據(jù)，簡(jiǎn)化數(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ù)，從而簡(jiǎn)化了數(shù)據(jù)的交換操作，也大大地簡(jiǎn)化了用戶對(duì)數(shù)據(jù)的操作。4．在某些情況下，由于表中數(shù)據(jù)量太大，因此在表的設(shè)計(jì)時(shí)常將表進(jìn)行水平或者垂直分割，但表的結(jié)構(gòu)的變化會(huì)對(duì)應(yīng)用程序產(chǎn)生不良的影響。5．視圖提供了一個(gè)簡(jiǎn)單而有效的安全機(jī)制，能夠?qū)?shù)據(jù)提供安全保護(hù)，視圖可以定制顯示數(shù)據(jù)庫中的數(shù)據(jù)信息。6．視圖可以跨服務(wù)器組合分區(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í)，SQLServer要檢查以確保語句中涉及的所有數(shù)據(jù)庫對(duì)象存在，而且數(shù)據(jù)修改語句不能違反數(shù)據(jù)完整性規(guī)則。（6）視圖的名稱必須遵循標(biāo)識(shí)符的規(guī)則，且對(duì)每個(gè)用戶必須是唯一的。此外，該名稱不得與該用戶擁有的任何表的名稱相同。5.3.1使用SQLServerManagementStudio創(chuàng)建視圖

1．啟動(dòng)SQLServerManagementStudio，并連接到SQLServer2008中的數(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“添加表”對(duì)話框5.3.1使用SQLServerManagementStudio創(chuàng)建視圖

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ì)器”窗口5.3.1使用SQLServerManagementStudio創(chuàng)建視圖

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.3.1使用SQLServerManagementStudio創(chuàng)建視圖

5．設(shè)置完成后，單擊工具欄上的【保存】按鈕，保存視圖，完成視圖的創(chuàng)建，如圖5-5所示。

圖5-5保存視圖5.3.1使用SQLServerManagementStudio創(chuàng)建視圖

5.3.2使用SQL命令創(chuàng)建視圖

語法形式：CREATEVIEWview_name[(column[,...n])]

[WITHENCRYPTION]AS

select_statement

[WITHCHECKOPTION]

其中：view_name指定視圖的名稱。column指定視圖中的列名。若沒有指定，其列名由SELECT命令指派，

即為SELECT命令中的列名。注意：視圖中的列名個(gè)數(shù)得與SELECT命令中的列項(xiàng)數(shù)相同。WITHENCRYPTION表示SQLServer加密包含CREATEVIEW語句文本在內(nèi)的系統(tǒng)表列。select_statement用于創(chuàng)建視圖的SELECT語句，利用SELECT命令可以從表中或者視圖中選擇列構(gòu)成新視圖的列。WITHCHECKOPTION用于強(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é)及這三門的平均成績(jī)。UseTeachingGoCreateViewsview_1ASSelectxh,zw,yw,sx,(zw+yw+sx)/3pjcjFromsgradeWherezw>=60andyw>=60andsx>=60Go【例5-2】在數(shù)據(jù)庫Teaching中，基于表sgrade建立一視圖，視圖名為sview_2，要求只能是各個(gè)系和各個(gè)系的學(xué)生人數(shù)、學(xué)生的平均英語成績(jī)。要求重新命名視圖的列，并加密視圖。(學(xué)生自主練習(xí)，具體實(shí)現(xiàn)參考教材P103)5.3.2使用SQL命令創(chuàng)建視圖

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

Fromttab,tctabWherettab.jsh=tctab.jshANDttab.zcIN(‘教授’,‘副教授’)Go【例5-4】在大學(xué)生選課管理數(shù)據(jù)庫Student中，建立一視圖，視圖名為st_view2，該視圖含有所選修的課程至少有一門及格的學(xué)生的學(xué)號(hào)、姓名、入學(xué)時(shí)間和所屬系，且按他們?nèi)雽W(xué)時(shí)間的先后順序排列。(學(xué)生自主練習(xí)，具體實(shí)現(xiàn)參考教材P104)5.3.2使用SQL命令創(chuàng)建視圖

5.4操作視圖

5.4.1使用SQLServerManagementStudio操作視圖

1．修改視圖（1）啟動(dòng)SQLServerManagementStudio，并連接到SQLServer2008中的數(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)行修改。5.4.1使用SQLServerManagementStudio操作視圖3．刪除或重命名視圖或查看視圖屬性（1）啟動(dòng)SQLServerManagementStudio，并連接到SQLServer2008中的數(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行】命令即可查看視圖內(nèi)容。5.4.1使用SQLServerManagementStudio操作視圖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)酬金。UseStudentGoSelect*Fromst_view1Go2.修改視圖語法格式：ALTERVIEWview_name[(column[,...n])]

[WITHENCRYPTION]

AS

select_statement

[WITHCHECKOPTION]其中各項(xiàng)參數(shù)同創(chuàng)建視圖SQL命令中的一樣?！纠?-6】將大學(xué)生選課管理數(shù)據(jù)庫Student中的視圖st_view2的篩選條件變?yōu)椤皒hNOTIN(SelectDistinctxhFromsctab)”，并給視圖加密。UseStudentGoAlterViewst_view2WITHENCRYPTIONASSelectxh,xm,rxsj,ssxFromstabWherexhNOTIN(SelectDistinctxhFromsctab)OrderByrxsjASCGo5.4.2使用SQL命令操作視圖3．刪除視圖語法格式：DROPVIEWview_name【例5-7】將上述數(shù)據(jù)庫Teaching中的視圖sview_1刪掉。

UseTeachingGoDropViewsview_1Go4．重命名視圖語法格式：EXECsp_rename源視圖名,新視圖名5．查看視圖定義信息EXECsp_helptext視圖名5.4.2使用SQL命令操作視圖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ù)之上的系和該系學(xué)生的人數(shù)、平均中文，平均英文及平均數(shù)學(xué)成績(jī)。分析：顯然不能直接查詢所需的數(shù)據(jù)，我們可借助于視圖完成。即先查詢各個(gè)系和各個(gè)系的學(xué)生人數(shù)、平均中文，平均英文及平均數(shù)學(xué)成績(jī)，并將其形成一視圖st_view3。UseTeachingGoCreateViewsview_3(szx,xsrs,pjzw,pjyw,pjsx)ASSelectszx,Count(*),Avg(zw),Avg(yw),Avg(sx)FromsgradeGroupByszxGo/*從視圖sview_3中查詢所需的最終數(shù)據(jù)*/Selectszx所在系,xsrs學(xué)生人數(shù),pjzw平均中文,pjyw平均英文,pjsx平均數(shù)學(xué)

Fromsview_3Wherexsrs>=(SelectAvg(xsrs)Fromsview_3)GoDropViewsview_3Go

5.5.1利用視圖查詢數(shù)據(jù)【例5-9】在大學(xué)生選課管理數(shù)據(jù)庫Student中，查詢選課門數(shù)最多的學(xué)生的學(xué)號(hào)、姓名和所選課程的門數(shù)及選課平均成績(jī)，并將查詢結(jié)果按所選課的平均成績(jī)降序排列。UseStudentGoCreateViewst_view3ASSelectstab.xh學(xué)號(hào),stab.xm姓名,Count(*)選課門數(shù),Avg(sctab.cj)平均成績(jī)

Fromstab,sctabWherestab.xh=sctab.xhGroupBystab.xh,stab.xmGo/*從視圖st_view3中查詢所需的最終數(shù)據(jù)*/Select*Fromst_view3Where選課門數(shù)>=ALL(Select選課門數(shù)Fromst_view3)OrderBy平均成績(jī)DescGoDropViewst_view3Go

5.5.1利用視圖查詢數(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í)指定了WITHCHECKOPTION選項(xiàng)，那么所有使用視圖修改數(shù)據(jù)庫信息時(shí)，必須保證修改后的數(shù)據(jù)滿足視圖定義的范圍。（4）執(zhí)行UPDATE、DELETE命令時(shí)，所刪除與更新的數(shù)據(jù)必須包含在視圖的結(jié)果集中。（5）如果視圖引用多個(gè)表時(shí)，無法用DELETE命令刪除數(shù)據(jù)。1.通過視圖插入記錄【例5-10】在數(shù)據(jù)庫Teaching中，利用視圖向表sgrade中添加一個(gè)學(xué)生記錄。UseTeachingGo/*先創(chuàng)建一個(gè)基于表sgrade的視圖sview_4*/CreateViewsview_4ASSelect*FromsgradeWherexm=’李芳’

Go/*通過視圖sview_4向表sgrade中添加記錄*/Insertintosview_4values（‘100022’,’孫大名’,’男’,’2008-9-1’,‘機(jī)械系’,‘08制造’,80,50,70）Go5.5.2利用視圖修改數(shù)據(jù)

2.通過視圖更新記錄內(nèi)容使用視圖可以更新數(shù)據(jù)記錄，但應(yīng)該注意的是，更新的只是數(shù)據(jù)庫中的基表?！纠?-11】在數(shù)據(jù)庫Teaching中，創(chuàng)建一個(gè)基于表sgrade的視圖sview_5，然后通過該視圖修改表sgrade中的記錄。UseTeachingGo/*先創(chuàng)建一個(gè)基于表sgrade的視圖sview_5*/CreateViewsview_5ASSelect*FromsgradeGo/*通過視圖sview_5修改表sgrade中的記錄*/Updatesview_5Setxm=’李麗霞’Wherexm=’李芳’Go5.5.2利用視圖修改數(shù)據(jù)

3.通過視圖刪除記錄

使用視圖刪除記錄，可以刪除任何基表中的記錄，直接利用DELETE語句刪除記錄即可。但應(yīng)該注意，必須指定在視圖中定義過的字段來刪除記錄?！纠?-12】在數(shù)據(jù)庫Teaching中，利用視圖sview_5刪除表sgrade中姓名為李麗霞的記錄。UseTeachingGoDeleteFromsview_5Wherexm=’李麗霞’

Go5.5.2利用視圖修改數(shù)據(jù)

5.6任務(wù)實(shí)現(xiàn)

1．建立教師的有關(guān)任課信息視圖（1）在數(shù)據(jù)庫Student中，建立一視圖st_view4，該視圖能查詢當(dāng)前所有任課教師的編號(hào)、姓名、性別、職稱和年齡信息。UseStudentGoCreateViewst_view4ASSelectjsh編號(hào),xm姓名,xb性別,zc職稱,year(getdate())-year(csrq)年齡

FromttabWherejshIN(SelectDistinctjshFromsctab)Go（2）在數(shù)據(jù)庫Student中，建立一視圖st_view5，該視圖能查詢每個(gè)能任課教師的編號(hào)、姓名、職稱和所能夠擔(dān)任的課程。

UseStudentGoCreateViewst_view5ASSelectttab.jch教師號(hào),ttab.xm姓名,ttab.zc職稱,ctab.kcm能任課程

Fromttab,ctab,tctabWherettab.jch=tctab.jshANDctab.kch=tctab.kchGo5.6任務(wù)實(shí)現(xiàn)

（3）在數(shù)據(jù)庫Student中，建立一視圖st_view6，該視圖能查詢每個(gè)教師的編號(hào)、姓名、職稱和所能夠擔(dān)任課程的門數(shù)。UseStudentGoCreateViewst_view6ASSelectttab.jsh教師號(hào),ttab.xm姓名,ttab.zc職稱,Count(*)任課門數(shù)

Fromttab,tctabWherettab.jsh=tctab.jshGroupByttab.jsh,ttab.xm,ttab.zcGo附注：也可使用子查詢完成。(學(xué)生自主練習(xí)，具體實(shí)現(xiàn)參考教材P110)5.6任務(wù)實(shí)現(xiàn)

（4）在數(shù)據(jù)庫Student中，建立一視圖st_view7，該視圖能查詢當(dāng)前每個(gè)任課教師的編號(hào)、姓名和所正講授的課程門數(shù)及所教的學(xué)生人數(shù)。UseStudentGoCreateViewst_view7ASSelectttab.jsh教師號(hào),ttab.xm姓名,Count(DISTINCTsctab.kch)講課門數(shù),Count(DISTINCTsctab.xh)學(xué)生人數(shù)

Fromttab,sctabWherettab.jsh=sctab.jshGroupByttab.jsh,ttab.xmGo5.6任務(wù)實(shí)現(xiàn)

2．建立學(xué)生的有關(guān)選課信息視圖（1）在數(shù)據(jù)庫Student中，建立一視圖st_view8，該視圖能查詢每個(gè)選課學(xué)生的學(xué)號(hào)、姓名、所選課程和該課程所選的任課教師及所選課程成績(jī)。UseStudentGoCreateViewst_view8ASSelectstab.x