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

石家莊經(jīng)濟學(xué)院實驗報告學(xué)院:信息工程學(xué)院專業(yè):計算機應(yīng)用信息工程學(xué)院計算機實驗中心制學(xué)號姓名張雪潔實驗日期2014-4-28實驗室260指導(dǎo)教師張有華設(shè)備編號JF260-11實驗內(nèi)容實驗4數(shù)據(jù)庫索引、視圖與觸發(fā)器一實驗題目1．索引的建立和刪除操作2．視圖的創(chuàng)立、修改、更新和查詢操作二實驗?zāi)康?．掌握數(shù)據(jù)庫索引建立與刪除操作，掌握數(shù)據(jù)庫索引的分類，并了解建立數(shù)據(jù)庫索引的意義、作用。2．掌握視圖的創(chuàng)立和查詢操作，理解視圖的使用，理解實圖在數(shù)據(jù)庫平安性中的作用。三實驗內(nèi)容索引的建立和刪除操作在S表中，建立按照sno升序的惟一性索引snoIDX。在SC表中，建立按照學(xué)號升序和課程號降序的唯一性索引scIDX。在S表中，按照生日建立一個非聚簇索引birthdayIDX。在C表中，建立一個按照課程名升序的聚簇索引cnameIDX。刪除索引cnameIDX。視圖的創(chuàng)立、修改、更新和查詢操作建立一個關(guān)于所有女生信息的視圖S_GIRL。將各系學(xué)生人數(shù)，平均年齡定義為視圖V_NUM_AVG建立一個視圖反映學(xué)生所選課程的總學(xué)分情況TOTAL_CREDIT。建立一個所有學(xué)生課程成績的視圖S_GRADE，包括根本學(xué)生信息，課程信息和成績。在視圖S_GRADE根底之上，建立一個兩門課以上成績不及格的學(xué)生情況視圖FAIL_GRADE。建立一個至少選修了4門課及4門課以上的學(xué)生信息的視圖SC_FOUR。修改視圖S_GIRL，要求只顯示1997年以前出生的女生信息。在視圖FAIL_GRADE查詢不及格超過2門課的學(xué)生信息。刪除視圖S_GRADE。通過視圖S_GIRL，將“王丹〞的名字修改為“汪丹〞，并查詢結(jié)果。通過視圖S_GIRL，新增一個學(xué)生信息〔“劉蘭蘭〞，“女〞，“計算機學(xué)院〞，1996-8-8〕，并查詢結(jié)果。通過視圖S_GIRL，刪除1995年出生的女生信息，并查詢結(jié)果。通過視圖S_GRADE，將“汪丹〞的名字修改為“王丹〞，是否可以實現(xiàn)，請說明原因。通過視圖COMPUTE_AVG_GRADE，將“4121090301〞學(xué)生的平均分改為90分，是否可以實現(xiàn)，請說明原因。四實驗要求1．要求掌握索引的類型，以及創(chuàng)立索引時的考前須知，例如每個表只能創(chuàng)立一個聚集索引，可以創(chuàng)立非聚集索引最多為249個，等等。2．理解創(chuàng)立視圖的目的和意義。掌握創(chuàng)立視圖時需要考慮的原那么：只能在當前數(shù)據(jù)庫中創(chuàng)立視圖、視圖名不得與該用戶的表名相同、可在視圖上建立視圖、定義視圖不能包括ORDERBY等關(guān)鍵字、不能建立臨時視圖，等等。3．報告中由同學(xué)寫明具體的操作意圖〔文字描述〕、操作命令〔SQL語句〕、和執(zhí)行結(jié)果〔文字描述+適當截圖〕。4．對于重要的運行界面和結(jié)果窗口，可以用Alt+PrintScreen來截取當前窗口，并粘貼到實驗報告中。五實驗步驟索引的建立和刪除操作CREATEINDEX—創(chuàng)立索引為給定表或視圖創(chuàng)立索引。只有表或視圖的所有者才能為表創(chuàng)立索引。表或視圖的所有者可以隨時創(chuàng)立索引，無論表中是否有數(shù)據(jù)?？梢酝ㄟ^指定限定的數(shù)據(jù)庫名稱，為另一個數(shù)據(jù)庫中的表或視圖創(chuàng)立索引。語法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—刪除索引從當前數(shù)據(jù)庫中刪除一個或多個索引。DROPINDEX語句不適用于通過定義PRIMARYKEY或UNIQUE約束創(chuàng)立的索引〔通過分別使用CREATETABLE或ALTERTABLE語句的PRIMARYKEY或UNIQUE選項創(chuàng)立〕。有關(guān)PRIMARY或UNIQUEKEY約束的更多信息，請參見本卷中的"CREATETABLE"或"ALTERTABLE"。語法DROPINDEX'table.'[,...n]參數(shù)table|view是索引列所在的表或索引視圖。假設(shè)要查看在表或視圖上存在的索引列表，請使用sp_helpindex并指定表名或視圖名稱。表名和視圖名稱必須符合標識符規(guī)那么。有關(guān)更多信息，請參見使用標識符?？梢赃x擇是否指定表或視圖所有者名稱。Index是要除去的索引名稱。索引名必須符合標識符的規(guī)那么。N是表示可以指定多個索引的占位符。在S表中，建立按照sno升序的惟一性索引snoIDX。SQL語句：CREATEUNIQUEINDEXsnoIDXONS(sno);運行結(jié)果如REF_Ref386563552\h圖1-1所示：圖1-SEQ圖1-\*ARABIC1索引snoIDX在SC表中，建立按照學(xué)號升序和課程號降序的唯一性索引scIDX。SQL語句：CREATEUNIQUEINDEXscIDXONSC(snoASC,cnoDESC);運行結(jié)果如REF_Ref386563576\h圖1-2所示：圖1-SEQ圖1-\*ARABIC2索引scIDX在S表中，按照生日建立一個非聚簇索引birthdayIDX。SQL語句：CREATENONCLUSTEREDINDEXbirthdayIDXONS(Sbirthday);運行結(jié)果如REF_Ref386563620\h圖1-3所示：圖1-SEQ圖1-\*ARABIC3索引birthdayIDX在C表中，建立一個按照課程名升序的聚簇索引cnameIDX。SQL語句：CREATECLUSTEREDINDEXcnameIDXonC(cname);結(jié)果框顯示為：效勞器:消息1902，級別16，狀態(tài)3，行1效勞器:消息1902，級別16，狀態(tài)3，行1不能在表'C'上創(chuàng)立多個聚集索引。請在創(chuàng)立新聚集索引前除去現(xiàn)有的聚集索引'PK__C__22AA2996'。查詢后，發(fā)現(xiàn)，一個表創(chuàng)立時，如果不定義索引類型。會把主鍵默認設(shè)置為聚集索引，如所示，而在一個表中只能存在一個聚集索引，所以此題，建立聚集索引時會提示以上錯誤，此時要將表中默認的聚集索引刪除，語句如下：結(jié)果框顯示為：效勞器:消息3723，級別16，狀態(tài)4，行1效勞器:消息3723，級別16，狀態(tài)4，行1不允許對索引'C.PK__C__22AA2996'顯式地使用DROPINDEX。該索引正用于PRIMARYKEY約束的強制執(zhí)行。解決方法如REF_Ref386563811\h圖1-4所示：圖1-SEQ圖1-\*ARABIC4編輯索引PK_C_22AA2996將聚集索引前的對勾去掉后，提示錯誤，如REF_Ref386563850\h圖1-5所示：圖1-SEQ圖1-\*ARABIC5錯誤翻開C表設(shè)計表，將主鍵去除，如REF_Ref386563873\h圖1-6所示：圖1-SEQ圖1-\*ARABIC6去除主鍵此時在創(chuàng)立聚集索引，運行結(jié)果如REF_Ref386563908\h圖1-7所示：圖1-SEQ圖1-\*ARABIC7索引CnameIDX刪除索引cnameIDXSQL語句：結(jié)果框顯示為：命令已成功完成。命令已成功完成。 運行結(jié)果如REF_Ref386563947\h圖1-8所示：圖1-SEQ圖1-\*ARABIC8索引被刪除視圖的創(chuàng)立、修改、更新和查詢操作CREATEVIEW—創(chuàng)立視圖創(chuàng)立一個虛擬表，該表以另一種方式表示一個或多個表中的數(shù)據(jù)。CREATEVIEW必須是查詢批處理中的第一條語句。語法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是視圖的名稱。視圖名稱必須符合標識符規(guī)那么。可以選擇是否指定視圖所有者名稱。Column是視圖中的列名。只有在以下情況下，才必須命名CREATEVIEW中的列：當列是從算術(shù)表達式、函數(shù)或常量派生的，兩個或更多的列可能會具有相同的名稱〔通常是因為聯(lián)接〕，視圖中的某列被賦予了不同于派生來源列的名稱。還可以在SELECT語句中指派列名。如果未指定column，那么視圖列將獲得與SELECT語句中的列相同的名稱。WITHCHECKOPTION強制視圖上執(zhí)行的所有數(shù)據(jù)修改語句都必須符合由select_statement設(shè)置的準那么。通過視圖修改行時，WITHCHECKOPTION可確保提交修改后，仍可通過視圖看到修改的數(shù)據(jù)。WITHENCRYPTION表示SQLServer加密包含CREATEVIEW語句文本的系統(tǒng)表列。使用WITHENCRYPTION可防止將視圖作為SQLServer復(fù)制的一局部發(fā)布。DROPVIEW—刪除視圖從當前數(shù)據(jù)庫中刪除一個或多個視圖?？蓪λ饕晥D執(zhí)行DROPVIEW。語法DROPVIEW{view}[,...n]參數(shù)View是要刪除的視圖名稱。視圖名稱必須符合標識符規(guī)那么。有關(guān)更多信息，請參見使用標識符?？梢赃x擇是否指定視圖所有者名稱。假設(shè)要查看當前創(chuàng)立的視圖列表，請使用sp_help。N是表示可以指定多個視圖的占位符。ALTERVIEW—修改視圖更改一個先前創(chuàng)立的視圖〔用CREATEVIEW創(chuàng)立〕，包括索引視圖，但不影響相關(guān)的存儲過程或觸發(fā)器，也不更改權(quán)限。有關(guān)ALTERVIEW語句中所用參數(shù)的更多信息，請參見CREATEVIEW。語法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是一列或多列的名稱，用逗號分開，將成為給定視圖的一局部。只有在ALTERVIEW執(zhí)行前后列名稱不變的情況下，列上的權(quán)限才會保持不變。建立一個關(guān)于所有女生信息的視圖S_GIRL。SQL語句：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語句：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ù)建立一個視圖反映學(xué)生所選課程的總學(xué)分情況TOTAL_CREDIT。SQL語句：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ù)建立一個所有學(xué)生課程成績的視圖S_GRADE，包括根本學(xué)生信息，課程信息和成績。SQL語句：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根底之上，建立一個兩門課以上成績不及格的學(xué)生情況視圖FAIL_GRADE。SQL語句：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中成績小于60的數(shù)據(jù)：Selectsno,cno,gradeFROMS_GRADEWheregrade<60如REF_Ref386567961\h圖2-9所示：圖2-SEQ圖2-\*ARABIC9視圖S_GRADE中成績小于60的數(shù)據(jù)查詢視圖FAIL_GRADE中的數(shù)據(jù)：Select*FROMFAIL_GRADE如REF_Ref386567981\h圖2-10所示：圖2-SEQ圖2-\*ARABIC10視圖FAIL_GRADE中的數(shù)據(jù)建立一個至少選修了4門課及4門課以上的學(xué)生信息的視圖SC_FOUR。SQL語句：createviewSC_FOURASselectS.*fromSwhereS.snoin( selectdistinctS.sno--,count(S.sno)asnum fromS,SC,C havingcount(S.sno)>=4 )子查詢中學(xué)生選修大于4門的學(xué)生學(xué)號：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é)號查詢視圖SC_FOUR中的數(shù)據(jù)：select*FROMSC_FOUR如REF_Ref386569039\h圖2-12所示：圖2-SEQ圖2-\*ARABIC12視圖SC_FOUR中的數(shù)據(jù)修改視圖S_GIRL，要求只顯示1997年以前出生的女生信息。SQL語句：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語句：selectS.*fromFAIL_GRADE,S查詢結(jié)果如REF_Ref386569850\h圖2-14所示：圖2-SEQ圖2-\*ARABIC14視圖FAIL_GRADE查詢刪除視圖S_GRADE。SQL語句：dropviewS_GRADE刪除前如REF_Ref386569878\h圖2-15所示：圖2-SEQ圖2-\*ARABIC15刪除前刪除后如REF_Ref386569899\h圖2-16所示：圖2-SEQ圖2-\*ARABIC16刪除后通過視圖S_GIRL，將“王丹〞的名字修改為“汪丹〞，并查詢結(jié)果。SQL語句：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，新增一個學(xué)生信息〔“劉蘭蘭〞，“女〞，“計算機學(xué)院〞，1996-8-8〕，并查詢結(jié)果。SQL語句：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語句：deletefromS_GIRLwhereyear(sbirthday)='1995'原視圖S_GIRL中的數(shù)據(jù)：select*fromS_GIRL如REF_Ref386629483\h圖2-19所示：圖2-SEQ圖2-\*ARABIC19刪除前視圖S_GIRL中的數(shù)據(jù)語句執(zhí)行后，結(jié)果框提示錯誤：效勞器:消息5