實(shí)驗(yàn)4-數(shù)據(jù)庫(kù)索引、視圖與觸發(fā)器

石家莊經(jīng)濟(jì)學(xué)院實(shí)驗(yàn)報(bào)告學(xué)院:信息工程學(xué)院專業(yè):計(jì)算機(jī)應(yīng)用信息工程學(xué)院計(jì)算機(jī)實(shí)驗(yàn)中心制學(xué)號(hào)姓名張雪潔實(shí)驗(yàn)日期2014-4-28實(shí)驗(yàn)室260指導(dǎo)教師張有華設(shè)備編號(hào)JF260-11實(shí)驗(yàn)內(nèi)容實(shí)驗(yàn)4數(shù)據(jù)庫(kù)索引、視圖與觸發(fā)器一實(shí)驗(yàn)題目1．索引的建立和刪除操作2．視圖的創(chuàng)立、修改、更新和查詢操作二實(shí)驗(yàn)?zāi)康?．掌握數(shù)據(jù)庫(kù)索引建立與刪除操作，掌握數(shù)據(jù)庫(kù)索引的分類，并了解建立數(shù)據(jù)庫(kù)索引的意義、作用。2．掌握視圖的創(chuàng)立和查詢操作，理解視圖的使用，理解實(shí)圖在數(shù)據(jù)庫(kù)平安性中的作用。三實(shí)驗(yàn)內(nèi)容索引的建立和刪除操作在S表中，建立按照sno升序的惟一性索引snoIDX。在SC表中，建立按照學(xué)號(hào)升序和課程號(hào)降序的唯一性索引scIDX。在S表中，按照生日建立一個(gè)非聚簇索引birthdayIDX。在C表中，建立一個(gè)按照課程名升序的聚簇索引cnameIDX。刪除索引cnameIDX。視圖的創(chuàng)立、修改、更新和查詢操作建立一個(gè)關(guān)于所有女生信息的視圖S_GIRL。將各系學(xué)生人數(shù)，平均年齡定義為視圖V_NUM_AVG建立一個(gè)視圖反映學(xué)生所選課程的總學(xué)分情況TOTAL_CREDIT。建立一個(gè)所有學(xué)生課程成績(jī)的視圖S_GRADE，包括根本學(xué)生信息，課程信息和成績(jī)。在視圖S_GRADE根底之上，建立一個(gè)兩門課以上成績(jī)不及格的學(xué)生情況視圖FAIL_GRADE。建立一個(gè)至少選修了4門課及4門課以上的學(xué)生信息的視圖SC_FOUR。修改視圖S_GIRL，要求只顯示1997年以前出生的女生信息。在視圖FAIL_GRADE查詢不及格超過2門課的學(xué)生信息。刪除視圖S_GRADE。通過視圖S_GIRL，將“王丹〞的名字修改為“汪丹〞，并查詢結(jié)果。通過視圖S_GIRL，新增一個(gè)學(xué)生信息〔“劉蘭蘭〞，“女〞，“計(jì)算機(jī)學(xué)院〞，1996-8-8〕，并查詢結(jié)果。通過視圖S_GIRL，刪除1995年出生的女生信息，并查詢結(jié)果。通過視圖S_GRADE，將“汪丹〞的名字修改為“王丹〞，是否可以實(shí)現(xiàn)，請(qǐng)說(shuō)明原因。通過視圖COMPUTE_AVG_GRADE，將“4121090301〞學(xué)生的平均分改為90分，是否可以實(shí)現(xiàn)，請(qǐng)說(shuō)明原因。四實(shí)驗(yàn)要求1．要求掌握索引的類型，以及創(chuàng)立索引時(shí)的考前須知，例如每個(gè)表只能創(chuàng)立一個(gè)聚集索引，可以創(chuàng)立非聚集索引最多為249個(gè)，等等。2．理解創(chuàng)立視圖的目的和意義。掌握創(chuàng)立視圖時(shí)需要考慮的原那么：只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)立視圖、視圖名不得與該用戶的表名相同、可在視圖上建立視圖、定義視圖不能包括ORDERBY等關(guān)鍵字、不能建立臨時(shí)視圖，等等。3．報(bào)告中由同學(xué)寫明具體的操作意圖〔文字描述〕、操作命令〔SQL語(yǔ)句〕、和執(zhí)行結(jié)果〔文字描述+適當(dāng)截圖〕。4．對(duì)于重要的運(yùn)行界面和結(jié)果窗口，可以用Alt+PrintScreen來(lái)截取當(dāng)前窗口，并粘貼到實(shí)驗(yàn)報(bào)告中。五實(shí)驗(yàn)步驟索引的建立和刪除操作CREATEINDEX—?jiǎng)?chuàng)立索引為給定表或視圖創(chuàng)立索引。只有表或視圖的所有者才能為表創(chuàng)立索引。表或視圖的所有者可以隨時(shí)創(chuàng)立索引，無(wú)論表中是否有數(shù)據(jù)?？梢酝ㄟ^指定限定的數(shù)據(jù)庫(kù)名稱，為另一個(gè)數(shù)據(jù)庫(kù)中的表或視圖創(chuàng)立索引。語(yǔ)法CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_name

ON{table|view}(column[ASC|DESC][,...n])

[WITH<index_option>[,...n]]

[ONfilegroup]<index_option>::=

{PAD_INDEX|

FILLFACTOR=fillfactor|

IGNORE_DUP_KEY|

DROP_EXISTING|

STATISTICS_NORECOMPUTE|

SORT_IN_TEMPDB

}DROPINDEX—?jiǎng)h除索引從當(dāng)前數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)索引。DROPINDEX語(yǔ)句不適用于通過定義PRIMARYKEY或UNIQUE約束創(chuàng)立的索引〔通過分別使用CREATETABLE或ALTERTABLE語(yǔ)句的PRIMARYKEY或UNIQUE選項(xiàng)創(chuàng)立〕。有關(guān)PRIMARY或UNIQUEKEY約束的更多信息，請(qǐng)參見本卷中的"CREATETABLE"或"ALTERTABLE"。語(yǔ)法DROPINDEX'table.'[,...n]參數(shù)table|view是索引列所在的表或索引視圖。假設(shè)要查看在表或視圖上存在的索引列表，請(qǐng)使用sp_helpindex并指定表名或視圖名稱。表名和視圖名稱必須符合標(biāo)識(shí)符規(guī)那么。有關(guān)更多信息，請(qǐng)參見使用標(biāo)識(shí)符?？梢赃x擇是否指定表或視圖所有者名稱。Index是要除去的索引名稱。索引名必須符合標(biāo)識(shí)符的規(guī)那么。N是表示可以指定多個(gè)索引的占位符。在S表中，建立按照sno升序的惟一性索引snoIDX。SQL語(yǔ)句：CREATEUNIQUEINDEXsnoIDXONS(sno);運(yùn)行結(jié)果如REF_Ref386563552\h圖1-1所示：圖1-SEQ圖1-\*ARABIC1索引snoIDX在SC表中，建立按照學(xué)號(hào)升序和課程號(hào)降序的唯一性索引scIDX。SQL語(yǔ)句：CREATEUNIQUEINDEXscIDXONSC(snoASC,cnoDESC);運(yùn)行結(jié)果如REF_Ref386563576\h圖1-2所示：圖1-SEQ圖1-\*ARABIC2索引scIDX在S表中，按照生日建立一個(gè)非聚簇索引birthdayIDX。SQL語(yǔ)句：CREATENONCLUSTEREDINDEXbirthdayIDXONS(Sbirthday);運(yùn)行結(jié)果如REF_Ref386563620\h圖1-3所示：圖1-SEQ圖1-\*ARABIC3索引birthdayIDX在C表中，建立一個(gè)按照課程名升序的聚簇索引cnameIDX。SQL語(yǔ)句：CREATECLUSTEREDINDEXcnameIDXonC(cname);結(jié)果框顯示為：效勞器:消息1902，級(jí)別16，狀態(tài)3，行1效勞器:消息1902，級(jí)別16，狀態(tài)3，行1不能在表'C'上創(chuàng)立多個(gè)聚集索引。請(qǐng)?jiān)趧?chuàng)立新聚集索引前除去現(xiàn)有的聚集索引'PK__C__22AA2996'。查詢后，發(fā)現(xiàn)，一個(gè)表創(chuàng)立時(shí)，如果不定義索引類型。會(huì)把主鍵默認(rèn)設(shè)置為聚集索引，如所示，而在一個(gè)表中只能存在一個(gè)聚集索引，所以此題，建立聚集索引時(shí)會(huì)提示以上錯(cuò)誤，此時(shí)要將表中默認(rèn)的聚集索引刪除，語(yǔ)句如下：結(jié)果框顯示為：效勞器:消息3723，級(jí)別16，狀態(tài)4，行1效勞器:消息3723，級(jí)別16，狀態(tài)4，行1不允許對(duì)索引'C.PK__C__22AA2996'顯式地使用DROPINDEX。該索引正用于PRIMARYKEY約束的強(qiáng)制執(zhí)行。解決方法如REF_Ref386563811\h圖1-4所示：圖1-SEQ圖1-\*ARABIC4編輯索引PK_C_22AA2996將聚集索引前的對(duì)勾去掉后，提示錯(cuò)誤，如REF_Ref386563850\h圖1-5所示：圖1-SEQ圖1-\*ARABIC5錯(cuò)誤翻開C表設(shè)計(jì)表，將主鍵去除，如REF_Ref386563873\h圖1-6所示：圖1-SEQ圖1-\*ARABIC6去除主鍵此時(shí)在創(chuàng)立聚集索引，運(yùn)行結(jié)果如REF_Ref386563908\h圖1-7所示：圖1-SEQ圖1-\*ARABIC7索引CnameIDX刪除索引cnameIDXSQL語(yǔ)句：結(jié)果框顯示為：命令已成功完成。命令已成功完成。 運(yùn)行結(jié)果如REF_Ref386563947\h圖1-8所示：圖1-SEQ圖1-\*ARABIC8索引被刪除視圖的創(chuàng)立、修改、更新和查詢操作CREATEVIEW—?jiǎng)?chuàng)立視圖創(chuàng)立一個(gè)虛擬表，該表以另一種方式表示一個(gè)或多個(gè)表中的數(shù)據(jù)。CREATEVIEW必須是查詢批處理中的第一條語(yǔ)句。語(yǔ)法CREATEVIEW[<database_name>.][<owner>.]view_name[(column[,...n])]

[WITH<view_attribute>[,...n]]

AS

select_statement

[WITHCHECKOPTION]

<view_attribute>::=

{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}參數(shù)view_name是視圖的名稱。視圖名稱必須符合標(biāo)識(shí)符規(guī)那么?？梢赃x擇是否指定視圖所有者名稱。Column是視圖中的列名。只有在以下情況下，才必須命名CREATEVIEW中的列：當(dāng)列是從算術(shù)表達(dá)式、函數(shù)或常量派生的，兩個(gè)或更多的列可能會(huì)具有相同的名稱〔通常是因?yàn)槁?lián)接〕，視圖中的某列被賦予了不同于派生來(lái)源列的名稱。還可以在SELECT語(yǔ)句中指派列名。如果未指定column，那么視圖列將獲得與SELECT語(yǔ)句中的列相同的名稱。WITHCHECKOPTION強(qiáng)制視圖上執(zhí)行的所有數(shù)據(jù)修改語(yǔ)句都必須符合由select_statement設(shè)置的準(zhǔn)那么。通過視圖修改行時(shí)，WITHCHECKOPTION可確保提交修改后，仍可通過視圖看到修改的數(shù)據(jù)。WITHENCRYPTION表示SQLServer加密包含CREATEVIEW語(yǔ)句文本的系統(tǒng)表列。使用WITHENCRYPTION可防止將視圖作為SQLServer復(fù)制的一局部發(fā)布。DROPVIEW—?jiǎng)h除視圖從當(dāng)前數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)視圖?？蓪?duì)索引視圖執(zhí)行DROPVIEW。語(yǔ)法DROPVIEW{view}[,...n]參數(shù)View是要?jiǎng)h除的視圖名稱。視圖名稱必須符合標(biāo)識(shí)符規(guī)那么。有關(guān)更多信息，請(qǐng)參見使用標(biāo)識(shí)符?？梢赃x擇是否指定視圖所有者名稱。假設(shè)要查看當(dāng)前創(chuàng)立的視圖列表，請(qǐng)使用sp_help。N是表示可以指定多個(gè)視圖的占位符。ALTERVIEW—修改視圖更改一個(gè)先前創(chuàng)立的視圖〔用CREATEVIEW創(chuàng)立〕，包括索引視圖，但不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器，也不更改權(quán)限。有關(guān)ALTERVIEW語(yǔ)句中所用參數(shù)的更多信息，請(qǐng)參見CREATEVIEW。語(yǔ)法ALTERVIEW[<database_name>.][<owner>.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[WITHCHECKOPTION]<view_attribute>::=

{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}參數(shù)view_name是要更改的視圖。Column是一列或多列的名稱，用逗號(hào)分開，將成為給定視圖的一局部。只有在ALTERVIEW執(zhí)行前后列名稱不變的情況下，列上的權(quán)限才會(huì)保持不變。建立一個(gè)關(guān)于所有女生信息的視圖S_GIRL。SQL語(yǔ)句：createviewS_GIRLASselect*fromSwheressex='女'原S表中的數(shù)據(jù)為：select*fromS如REF_Ref386564335\h圖2-1所示：圖2-SEQ圖2-\*ARABIC1原S表中的數(shù)據(jù)查詢視圖S_GIRL中的數(shù)據(jù)：select*FROMS_GIRL如REF_Ref386564360\h圖2-2所示：圖2-SEQ圖2-\*ARABIC2視圖S_GIRL中的數(shù)據(jù)將各系學(xué)生人數(shù)，平均年齡定義為視圖V_NUM_AVGSQL語(yǔ)句：createviewV_NUM_AVGASselectcount(sdept)asnum,avg(2023-year(sbirthday))asagefromSgroupbysdept原S表中數(shù)據(jù)為：Selectsname,sdept,2023-year(sbirthday)asageFROMS如REF_Ref386565124\h圖2-3所示：圖2-SEQ圖2-\*ARABIC3原S表中數(shù)據(jù)查詢視圖V_NUM_AVG中的數(shù)據(jù)為：Select*FROMV_NUM_AVG如REF_Ref386565145\h圖2-4所示：圖2-SEQ圖2-\*ARABIC4視圖V_NUM_AVG中的數(shù)據(jù)建立一個(gè)視圖反映學(xué)生所選課程的總學(xué)分情況TOTAL_CREDIT。SQL語(yǔ)句：CreateviewTOTAL_CREDITASSelectSC.sno,sum(ccredit)assumFromSC,CWhereGroupbysno原表SC和C連接后的數(shù)據(jù)：SelectSC.sno,Co,cname,ccreditFromSC,CWhereOrderbySC.snoasc如REF_Ref386565921\h圖2-5所示：圖2-SEQ圖2-\*ARABIC5表SC和C連接后的數(shù)據(jù)查詢視圖TOTAL_CREDIT中的數(shù)據(jù)為：Select*FROMTOTAL_CREDIT如REF_Ref386565941\h圖2-6所示：圖2-SEQ圖2-\*ARABIC6視圖TOTAL_CREDIT中的數(shù)據(jù)建立一個(gè)所有學(xué)生課程成績(jī)的視圖S_GRADE，包括根本學(xué)生信息，課程信息和成績(jī)。SQL語(yǔ)句：CreateviewS_GRADEASSelectFromSC,C,SWhere查詢視圖S_GRADE中的數(shù)據(jù)：Select*FROMS_GRADE如REF_Ref386566306\h圖2-7所示：圖2-SEQ圖2-\*ARABIC7視圖S_GRADE中的數(shù)據(jù)在視圖S_GRADE根底之上，建立一個(gè)兩門課以上成績(jī)不及格的學(xué)生情況視圖FAIL_GRADE。SQL語(yǔ)句：CreateviewFAIL_GRADEASSelectsno,count(sno)asnumFromS_GRADE Wheregrade<60GroupbysnoHavingcount(sno)>=2原視圖S_GRADE中的數(shù)據(jù)：Selectsno,cno,gradeFROMS_GRADE如REF_Ref386567935\h圖2-8所示：圖2-SEQ圖2-\*ARABIC8視圖S_GRADE中的數(shù)據(jù)原視圖S_GRADE中成績(jī)小于60的數(shù)據(jù)：Selectsno,cno,gradeFROMS_GRADEWheregrade<60如REF_Ref386567961\h圖2-9所示：圖2-SEQ圖2-\*ARABIC9視圖S_GRADE中成績(jī)小于60的數(shù)據(jù)查詢視圖FAIL_GRADE中的數(shù)據(jù)：Select*FROMFAIL_GRADE如REF_Ref386567981\h圖2-10所示：圖2-SEQ圖2-\*ARABIC10視圖FAIL_GRADE中的數(shù)據(jù)建立一個(gè)至少選修了4門課及4門課以上的學(xué)生信息的視圖SC_FOUR。SQL語(yǔ)句：createviewSC_FOURASselectS.*fromSwhereS.snoin( selectdistinctS.sno--,count(S.sno)asnum fromS,SC,C havingcount(S.sno)>=4 )子查詢中學(xué)生選修大于4門的學(xué)生學(xué)號(hào)：selectdistinctS.sno,count(S.sno)asnum fromS,SC,C havingcount(S.sno)>=4如REF_Ref386568951\h圖2-11所示：圖2-SEQ圖2-\*ARABIC11子查詢中學(xué)生選修大于4門的學(xué)生學(xué)號(hào)查詢視圖SC_FOUR中的數(shù)據(jù)：select*FROMSC_FOUR如REF_Ref386569039\h圖2-12所示：圖2-SEQ圖2-\*ARABIC12視圖SC_FOUR中的數(shù)據(jù)修改視圖S_GIRL，要求只顯示1997年以前出生的女生信息。SQL語(yǔ)句：alterviewS_GIRLASselect*fromSwheressex='女'andyear(sbirthday)<1997查詢視圖S_GIRL中的數(shù)據(jù)：select*FROMS_GIRL如REF_Ref386569366\h圖2-13所示：圖2-SEQ圖2-\*ARABIC13視圖S_GIRL中的數(shù)據(jù)在視圖FAIL_GRADE查詢不及格超過2門課的學(xué)生信息。SQL語(yǔ)句：selectS.*fromFAIL_GRADE,S查詢結(jié)果如REF_Ref386569850\h圖2-14所示：圖2-SEQ圖2-\*ARABIC14視圖FAIL_GRADE查詢刪除視圖S_GRADE。SQL語(yǔ)句：dropviewS_GRADE刪除前如REF_Ref386569878\h圖2-15所示：圖2-SEQ圖2-\*ARABIC15刪除前刪除后如REF_Ref386569899\h圖2-16所示：圖2-SEQ圖2-\*ARABIC16刪除后通過視圖S_GIRL，將“王丹〞的名字修改為“汪丹〞，并查詢結(jié)果。SQL語(yǔ)句：updateS_GIRLsetsname='王丹'wheresname='汪丹'查詢視圖S_GIRL中的數(shù)據(jù)：select*fromS_GIRL如REF_Ref386570961\h圖2-17所示：圖2-SEQ圖2-\*ARABIC17視圖S_GIRL中的數(shù)據(jù)通過視圖S_GIRL，新增一個(gè)學(xué)生信息〔“劉蘭蘭〞，“女〞，“計(jì)算機(jī)學(xué)院〞，1996-8-8〕，并查詢結(jié)果。SQL語(yǔ)句：insertintoS_GIRL(sno,sid,sname,ssex,sdept,sbirthday)1996-8-8)查詢視圖S_GIRL中的數(shù)據(jù)：select*fromS_GIRL如REF_Ref386571031\h圖2-18所示：圖2-SEQ圖2-\*ARABIC18視圖S_GIRL中的數(shù)據(jù)通過視圖S_GIRL，刪除1995年出生的女生信息，并查詢結(jié)果。SQL語(yǔ)句：deletefromS_GIRLwhereyear(sbirthday)='1995'原視圖S_GIRL中的數(shù)據(jù)：select*fromS_GIRL如REF_Ref386629483\h圖2-19所示：圖2-SEQ圖2-\*ARABIC19刪除前視圖S_GIRL中的數(shù)據(jù)語(yǔ)句執(zhí)行后，結(jié)果框提示錯(cuò)誤：效勞器:消息5