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

1、石家莊經(jīng)濟學(xué)院實 驗 報 告學(xué) 院: 信息工程學(xué)院 專 業(yè): 網(wǎng)絡(luò)工程 信息工程學(xué)院計算機實驗中心制學(xué)號411109030110姓名馬立杰實驗日期2014-4-28實驗室260實驗室指導(dǎo)教師張有華設(shè)備編號22實驗內(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)容1. 索引的建立和刪除操作(1) 在S表中，建立按照sno升序的惟一性索引snoIDX。(2)

2、在SC表中，建立按照學(xué)號升序和課程號降序的唯一性索引scIDX。(3) 在S表中，按照生日建立一個非聚簇索引birthdayIDX。(4) 在C表中，建立一個按照課程名升序的聚簇索引cnameIDX。(5) 刪除索引cnameIDX。2. 視圖的創(chuàng)建、修改、更新和查詢操作(1) 建立一個關(guān)于所有女生信息的視圖S_GIRL。(2) 將各系學(xué)生人數(shù)，平均年齡定義為視圖V_NUM_AVG(3) 建立一個視圖反映學(xué)生所選課程的總學(xué)分情況TOTAL_CREDIT。(4) 建立一個所有學(xué)生課程成績的視圖S_GRADE，包括基本學(xué)生信息，課程信息和成績。(5) 在視圖S_GRADE基礎(chǔ)之上，建立一個兩門課以

3、上成績不及格的學(xué)生情況視圖FAIL_GRADE。(6) 建立一個至少選修了4門課及4門課以上的學(xué)生信息的視圖SC_FOUR。(7) 修改視圖S_GIRL，要求只顯示1997年以前出生的女生信息。(8) 在視圖FAIL_GRADE查詢不及格超過2門課的學(xué)生信息。(9) 刪除視圖S_GRADE。(10) 通過視圖S_GIRL，將“王丹”的名字修改為“汪丹”，并查詢結(jié)果。(11) 通過視圖S_GIRL，新增一個學(xué)生信息（“劉蘭蘭”，“女”，“計算機學(xué)院”，1996-8-8），并查詢結(jié)果。(12) 通過視圖S_GIRL，刪除1995年出生的女生信息，并查詢結(jié)果。(13) 通過視圖S_GRADE，將“汪

4、丹”的名字修改為“王丹”，是否可以實現(xiàn)，請說明原因。(14) 通過視圖COMPUTE_AVG_GRADE，將“4121090301”學(xué)生的平均分改為90分，是否可以實現(xiàn)，請說明原因。四 實驗要求1 要求掌握索引的類型，以及創(chuàng)建索引時的注意事項，例如每個表只能創(chuàng)建一個聚集索引，可以創(chuàng)建非聚集索引最多為249個，等等。2 理解創(chuàng)建視圖的目的和意義。掌握創(chuàng)建視圖時需要考慮的原則：只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖、視圖名不得與該用戶的表名相同、可在視圖上建立視圖、定義視圖不能包括ORDER BY等關(guān)鍵字、不能建立臨時視圖，等等。3 報告中由同學(xué)寫明具體的操作意圖（文字描述）、操作命令（SQL語句）、和執(zhí)行結(jié)

5、果（文字描述+適當(dāng)截圖）。4 對于重要的運行界面和結(jié)果窗口，可以用Alt+PrintScreen來截取當(dāng)前窗口，并粘貼到實驗報告中。五 實驗步驟1. 索引的建立和刪除操作(1) 在STU表中，建立按照sno升序的惟一性索引snoIDX。SQL語句為：create unique index snoIDX on STU(Sno);運行結(jié)果如1- 1按所示：命令已成功完成。圖1- 1按學(xué)號升序建立的唯一性索引(2) 在SC表中，建立按照學(xué)號升序和課程號降序的唯一性索引scIDX。SQL語句為：create unique index scIDX on SC(Sno ASC,Cno DESC);運行結(jié)果

6、如1- 2按所示：命令已成功完成。圖1- 2按學(xué)號升序課程號降序建立索引(3) 在S表中，按照生日建立一個非聚簇索引birthdayIDX。SQL語句為：create nonclustered index birthdayIDX on STU(Sbirthday);執(zhí)行結(jié)果如1- 3對所示：命令已成功完成。圖1- 3對生日建立非聚簇索引(4) 在Course表中，建立一個按照課程名升序的聚簇索引cnameIDX。SQL語句為：create clustered index cnameIDX on Course(Cname);運行結(jié)果如1- 4按所示：命令已成功完成。圖1- 4按課程名升序建立聚簇

7、索引(5) 刪除索引cnameIDX。SQL語句為：drop index CameIDX;運行結(jié)果如1- 5刪所示：表中索引cnameIDX不存在。圖1- 5刪除cnameIDX索引2. 視圖的創(chuàng)建、修改、更新和查詢操作(1) 建立一個關(guān)于所有女生信息的視圖STU_GIRL。SQL語句為：create view STU_GIRL(Sno,SID,Sname,Ssex,Sbirthday,Sdept,Saddress,Smajor)asselect *from STUwhere Ssex='女'運行結(jié)果如1- 6建所示：命令已成功完成。圖1- 6建立女生信息的視圖(2) 將各系學(xué)

8、生人數(shù)，平均年齡定義為視圖V_NUM_AVGSQL語句為：create view V_NUM_AVG(Sdept,STU_count,A_avg)asselect Sdept,count(Sdept),avg(datediff(year,Sbirthday,getdate()from STUgroup by Sdept執(zhí)行結(jié)果如1- 7建所示：圖1- 7建立視圖V_NUM_AVG(3) 建立一個視圖反映學(xué)生所選課程的總學(xué)分情況TOTAL_CREDIT。SQL語句為：create view TOTAL_CREDIT(Sno,sum_credit)asselect SC.Sno,sum(Ccred

9、it)from SC,Coursewhere SC.Cno=Course.Cno group by SC.Sno執(zhí)行結(jié)果如1- 8建所示：圖1- 8建立視圖TOTAL_CREDIT(4) 建立一個所有學(xué)生課程成績的視圖STU_GRADE，包括基本學(xué)生信息，課程信息和成績。SQL語句為：create view STU_GRADEasselect STU.*,Course.*,SC.Sgradefrom SC full join STU on SC.Sno=STU.Sno full join Course on Course.Cno=SC.Cno運行結(jié)果如1- 9所所示：圖1- 9所有學(xué)生課程信息

10、視圖(5) 在視圖S_GRADE基礎(chǔ)之上，建立一個兩門課以上成績不及格的學(xué)生情況視圖FAIL_GRADE。SQL語句為：create view FAIL_GRADEasselect Sno,SID,Sname,Ssex,Sbirthday,Sdept,Saddress,Smajor,count(Cno) as STU_count from STU_GRADEwhere Grade<60group by Sno,SID,Sname,Ssex,Sbirthday,Sdept,Saddress having count(Cno)>=2運行結(jié)果如1- 10建所示：圖1- 10建立視圖FAI

11、L_GRADE(6) 建立一個至少選修了4門課及4門課以上的學(xué)生信息的視圖SC_FOUR。SQL語句為：create view SC_FOURasselect STU.*from STUwhere Sno in(select Snofrom SCgroup by Snohaving count(SC.Sno)>=4)運行結(jié)果如1-11所示：圖1- 11建立至少選修4門課程的學(xué)生信息視圖(7) 修改視圖S_GIRL，要求只顯示1997年以前出生的女生信息。SQL語句為：create view STU_GIRL(Sno,SID,Sname,Ssex,Sbirthday,Sdept,Saddr

12、ess)asselect *from STUwhere Ssex='女' and datepart(year,Sbirthday)<1997運行結(jié)果如1- 12修所示：圖1- 12修改S_GIRL視圖信息(8) 在視圖FAIL_GRADE查詢不及格超過2門課的學(xué)生信息。SQL語句為：select * from FAIL_GRADE where STU_count>2;運行結(jié)果如1- 13從所示：圖1- 13從視圖中查詢信息(9) 刪除視圖S_GRADE。SQL語句為：drop view STU_GRADE執(zhí)行結(jié)果如1- 14視所示：圖1- 14視圖列表(10) 通過

13、視圖S_GIRL，將“劉丹”的名字修改為“汪丹”，并查詢結(jié)果。SQL語句為：update STU_GIRLset Sname='汪丹'where Sname='劉丹'運行結(jié)果如圖1-15所示：圖1- 15修改信息(11) 通過視圖S_GIRL，新增一個學(xué)生信息（“劉蘭蘭”，“女”，“計算機學(xué)院”，1996-8-8），并查詢結(jié)果。SQL語句為：insert into STU_GIRLvalues('00009','23','劉蘭蘭','女','1996-8-8','信工'

14、;,'山東',)執(zhí)行結(jié)果如1-16所示：圖1- 16向視圖中添加信息(12) 通過視圖S_GIRL，刪除1995年出生的女生信息，并查詢結(jié)果。SQL語句為：delete from STU_GIRLwhere datepart(year,Sbirthday)=1995執(zhí)行結(jié)果如1- 17刪所示：圖1- 17刪除并查詢信息(13) 通過視圖S_GRADE，將“汪丹”的名字修改為“劉丹”，是否可以實現(xiàn)，請說明原因。SQL語句為：update STU_Girlset Sname='王丹'where Sname='汪丹'執(zhí)行結(jié)果如1-18所示：圖1- 18修改姓名 實現(xiàn)原因：該視圖為行列子集視圖，是從S和C、SC三個基本表中導(dǎo)出，并且僅是去掉了基本表的某些列，但是主屬性所在列沒有去掉。此類視圖可以更新(14) 通過視圖COMPUTE_AVG_GRADE，將“00009”學(xué)生的平均分改為90分，是否可以實現(xiàn)，請說明原因。SQL語句為：create view COMPUTE_AVG_GRADE(Sno,Gavg)as select Sno,avg(Grade)from SCgroup by Snoupdate COMPUTE_AVG_GRADEset Gavg=90wher