數(shù)據(jù)庫實驗報告 (4)
一 實驗題目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) 在SC表中,建立按照學號升序和課程號降序的唯一性索引scIDX。(3) 在S表中,按照生日建立一個非聚簇索引birthdayIDX。(4) 在C表中,建立一個按照課程名升序的聚簇索引cnameIDX。(5) 刪除索引cnameIDX。2 視圖的創(chuàng)建、修改、更新和查詢操作(1) 建立一個關(guān)于所有女生信息的視圖S_GIRL。(2) 將各系學生人數(shù),平均年齡定義為視圖V_NUM_AVG(3) 建立一個視圖反映學生所選課程的總學分情況TOTAL_CREDIT。(4) 建立一個所有學生課程成績的視圖S_GRADE,包括基本學生信息,課程信息和成績。(5) 在視圖S_GRADE基礎(chǔ)之上,建立一個兩門課以上成績不及格的學生情況視圖FAIL_GRADE。(6) 建立一個至少選修了4門課及4門課以上的學生信息的視圖SC_FOUR。(7) 修改視圖S_GIRL,要求只顯示1997年以前出生的女生信息。(8) 在視圖FAIL_GRADE查詢不及格超過2門課的學生信息。(9) 刪除視圖S_GRADE。(10) 通過視圖S_GIRL,將“王丹”的名字修改為“汪丹”,并查詢結(jié)果。(11) 通過視圖S_GIRL,新增一個學生信息(“劉蘭蘭”,“女”,“計算機學院”,1996-8-8),并查詢結(jié)果。(12) 通過視圖S_GIRL,刪除1995年出生的女生信息,并查詢結(jié)果。(13) 通過視圖S_GRADE,將“汪丹”的名字修改為“王丹”,是否可以實現(xiàn),請說明原因。(14) 通過視圖COMPUTE_AVG_GRADE,將“4121090301”學生的平均分改為90分,是否可以實現(xiàn),請說明原因。四 實驗要求1 要求掌握索引的類型,以及創(chuàng)建索引時的注意事項,例如每個表只能創(chuàng)建一個聚集索引,可以創(chuàng)建非聚集索引最多為249個,等等。2 理解創(chuàng)建視圖的目的和意義。掌握創(chuàng)建視圖時需要考慮的原則:只能在當前數(shù)據(jù)庫中創(chuàng)建視圖、視圖名不得與該用戶的表名相同、可在視圖上建立視圖、定義視圖不能包括ORDER BY等關(guān)鍵字、不能建立臨時視圖,等等。3 報告中由同學寫明具體的操作意圖(文字描述)、操作命令(SQL語句)、和執(zhí)行結(jié)果(文字描述+適當截圖)。4 對于重要的運行界面和結(jié)果窗口,可以用Alt+PrintScreen來截取當前窗口,并粘貼到實驗報告中。五 實驗步驟:1. 索引的建立和刪除操作2. 在S表中,建立按照sno升序的惟一性索引snoIDX。create unique index snoIDX on S(Sno asc);運行結(jié)果:命令已成功完成。結(jié)果顯示如圖1:圖表 1 建立索引snoIDX3. 在SC表中,建立按照學號升序和課程號降序的唯一性索引scIDX。代碼:create index scIDX on SC(Sno asc,Cno desc);運行結(jié)果:命令已成功完成。結(jié)果顯示如圖2:圖表 2在SC表建立索引scIDX。4. 在S表中,按照生日建立一個非聚簇索引birthdayIDX。代碼:create NONCLUSTERED index birthdayIDX on S(Sbirthday asc);運行結(jié)果:命令已成功完成。顯示結(jié)果如圖3所示圖表 3建立索引birthdayIDX5. 在C表中,建立一個按照課程名升序的聚簇索引cnameIDX。代碼:create CLUSTERED index cnameIDX on C(Cno asc);運行結(jié)果:命令已成功完成。顯示結(jié)果如圖4:圖表 4按照課程名升序的聚簇索引cnameIDX。6. 刪除索引cnameIDX。代碼:drop index C.cnameIDX運行結(jié)果:命令已成功完成。顯示結(jié)果如圖5,無索引,被刪除:圖表 57. 視圖的創(chuàng)建、修改、更新和查詢操作8. 建立一個關(guān)于所有女生信息的視圖S_GIRL。create view S_GIRL as select * from S where S.Ssex='女'運行結(jié)果:服務(wù)器: 消息 170,級別 15,狀態(tài) 1,過程 S_GIRL,行 3第 3 行: '' 附近有語法錯誤。刪除“;”代碼:create view S_GIRL as select * from S where Ssex='女'運行結(jié)果:命令已成功完成。查詢顯示結(jié)果: select * from S_GIRL符合條件的結(jié)果如下圖6所示:圖表 69. 將各系學生人數(shù),平均年齡定義為視圖V_NUM_AVG代碼:create view V_NUM_AVG as select count(Sno) ,avg(Sage),Sdept from S group by Sdept運行結(jié)果:服務(wù)器: 消息 4511,級別 16,狀態(tài) 1,過程 V_NUM_AVG,行 1創(chuàng)建視圖或函數(shù)失敗,因為沒有為第 1 列指定列名。正確代碼: create view V_NUM_AVG as select count(Sno) DeptNum ,avg(Sage) Dept,Sdept from S group by Sdept運行結(jié)果:命令已成功完成。顯示結(jié)果select * from V_NUM_AVG如圖7所示DeptNum為各系學生人數(shù),Dep為平均年齡圖表 710. 建立一個視圖反映學生所選課程的總學分情況TOTAL_CREDIT。代碼:create view TOTAL_CREDIT as select Sno, sum(Ccredit) as total_credit from SC join C on C.Cno=SC.Cnogroup by Sno運行結(jié)果:命令已成功完成。顯示結(jié)果如圖8所示TOTAL_CREDIT為學生所選課程的總學分情況:圖表 811. 建立一個所有學生課程成績的視圖S_GRADE,包括基本學生信息,課程信息和成績。create view S_GRADE as select S.Sno,S.Sname,C.Cname,C.Ccredit,SC.Gradefrom SC ,C,Swhere S.Sno=SC.Sno and SC.Cno=C.Cno運行結(jié)果:命令已成功完成。顯示結(jié)果:select * from S_GRADE結(jié)果如圖9所示:基本信息,課程信息和成績圖表 912. 在視圖S_GRADE基礎(chǔ)之上,建立一個兩門課以上成績不及格的學生情況視圖FAIL_GRADE。代碼:create view FAIL_GRADE as select Sno,count(*)SnoNum from S_GRADE where Grade<60 group by Sno having count(*)>=2運行結(jié)果:命令已成功完成。查詢視圖符合條件的結(jié)果:select * from FAIL_GRADE如下圖所示,沒有符合選修兩門且不及格的學生信息圖表 1013. 建立一個至少選修了2門課及2門課以上的學生信息的視圖SC_FOUR。代碼:create view SC_FOURas select Sno,count(*)CnoNum from SC group by Sno having count(*)>=2運行結(jié)果:命令已成功完成。查詢視圖符合條件的結(jié)果:select * from SC_FOUR顯示結(jié)果如下圖11:學號為0001得學生選修3門,0002學生和0003學生選修了3門;圖表 11查詢四門以上,數(shù)據(jù)庫中沒有符合條件的學生14. 修改視圖S_GIRL,要求只顯示1997年以前出生的女生信息。代碼:alter view S_GIRL select * from S where Ssex='女'and Sbirthday<='1997-1-1'運行結(jié)果:服務(wù)器: 消息 156,級別 15,狀態(tài) 1,過程 S_GIRL,行 2在關(guān)鍵字 'select' 附近有語法錯誤。修改后代碼:少了AS,修改視圖同ALTERalter view S_GIRL asselect * from S where Ssex='女'and Sbirthday<='1997-1-1'運行結(jié)果:命令已成功完成。顯示結(jié)果學生都滿足1997年之前:圖表 1215. 在視圖FAIL_GRADE查詢不及格超過2門課的學生信息。代碼:select * from FAIL_GRADE where SnoNum>2運行結(jié)果:命令已成功完成。顯示結(jié)果:在視圖FAIL_GRADE不存在不及格超過2門課;圖表 1316. 刪除視圖S_GRADE。修改前數(shù)據(jù)庫中存在的視圖14如下圖所示:圖表 14代碼:drop view S_GRADE刪除視圖后,數(shù)據(jù)庫中存在的視圖如下圖15所示:不存在S_GRADE視圖圖表 1517. 通過視圖S_GIRL,將“王思”的名字修改為“汪思”,并查詢結(jié)果。修改前:視圖S_GIRL基本情況如下圖所示:圖表 16代碼:alter view S_GIRLasset Sname='汪思'where Sname='王思'運行結(jié)果:服務(wù)器: 消息 156,級別 15,狀態(tài) 1,過程 S_GIRL,行 4在關(guān)鍵字 'set' 附近有語法錯誤。語句錯誤:應(yīng)用update ,update 后不用加view as 正確代碼:update S_GIRLset Sname='汪思'where Sname='王思'運行結(jié)果:(所影響的行數(shù)為 1 行)顯示結(jié)果如圖17紅色標記所示:圖表 1718. 通過視圖S_GIRL,新增一個學生信息(“劉蘭蘭”,“女”,“計算機學院”,1996-8-8),并查詢結(jié)果。代碼:insert into S_GIRL (Sno,Sname,Ssex,Sbirthday,Sdept) values ('0020','劉蘭蘭','女','1996-8-8','計算機')運行結(jié)果:(所影響的行數(shù)為 1 行)結(jié)果顯示如圖:19. 通過視圖S_GIRL,刪除1996年出生的女生信息,并查詢結(jié)果。運行前信息如上圖所示:代碼:delete S_GIRL where Sbirthday<='1996-12-31' and Sbirthday>='1996-1-1'運行結(jié)果:(所影響的行數(shù)為 1 行)顯示結(jié)果如下圖:標記所示信息被刪除20. 通過視圖S_GRADE,將“汪思”的名字修改為“王思”,是否可以實現(xiàn),請說明原因代碼:update S_GRADE set Sname='王思' where Sname='汪思'運行結(jié)果:(所影響的行數(shù)為1 行)顯示結(jié)果:若是基于多個表的視圖、涉及關(guān)鍵字段則不能修改表信息21. 通過視圖COMPUTE_AVG_GRADE,將“0001”學生的平均分改為90分,是否可以實現(xiàn),請說明原因。創(chuàng)建視圖:代碼:create view COMPUTE_AVG_GRADE asselect SC.Sno,avg(SC.Grade)AS 平均成績from SC,C,Swhere S.Sno=SC.Sno and SC.Cno=C.Cnogroup by SC.Sno顯示視圖信息;修改視圖:代碼:update COMPUTE_AVG_GRADE set 平均成績='91' where Sno='0001'運行結(jié)果:服務(wù)器: 消息 4403,級別 16,狀態(tài) 1,行 1視圖或函數(shù) 'COMPUTE_AVG_GRADE' 不可更新,因為它包含聚合。視圖不能修改數(shù)據(jù)。關(guān)于可更新視圖有以下三條規(guī)則:(1) 若視圖是基于多個表使用聯(lián)接操作而導(dǎo)出的,那么對這個視圖執(zhí)行更新操作時,每次只能影響其中的一個表。(2) 若視圖導(dǎo)出時包含有分組和聚合操作,則不允許對這個視圖執(zhí)行更新操作。(3) 若視圖是從一個表經(jīng)選擇、投影而導(dǎo)出的,并在視圖中包含了表的主鍵字或某個候選鍵,這類視圖稱為行列子集視圖。對這類視圖可執(zhí)行更新操作。上題不能修改,因為視圖導(dǎo)出時包含有分組和聚合操作,不允許對這個視圖執(zhí)行更新操作