1、 實驗報告學院(xuyun)（系）名稱：計算機與通信(tng xn)工程學院姓名Touchkiss學號20125577專業計算機科學與技術班級2班實驗項目查詢優化課程名稱數據庫系統課程代碼0668026實驗時間2014/12/5實驗地點7#215批改意見成績教師簽字：實驗環境（軟、硬件環境）實驗目的了解數據庫查詢優化方法和查詢計劃的概念學會分析查詢的代價，并通過建立索引或者修改SQL語句來降低查詢代價實驗內容與要求基于教材中的student、course、SC表，通過存儲過程（要求在報告中寫出存儲過程），插入多條元組（1萬條以上），考慮不同的SQL操作，分析比較執行代價。單表查詢直接查詢：查詢

2、student表中年齡在20歲以上的學生記錄建立索引后，再查詢：查詢student表中年齡在20歲以上的學生記錄表中元組數量少，查詢結果所占比例大：查詢student表中年齡在20歲以上的學生記錄表元組數量多，查詢結果所占比例小：查詢student表中年齡在20歲以上的學生記錄分析以上四種SQL查詢的執行效率，并做總結：多表查詢：基于student、course、SC表，按照以下要求，實現多表查詢，并分析比較執行效率。（自行設計查詢語句，要求針對同一查詢內容，使用以下四種方式）多表連接查詢嵌套查詢建立索引使用游標分析比較：3綜合練習(1) 對于student表，不按照姓名創建索引，查詢某個姓名

3、，所需要的時間。(2) 對于student表，按照姓名創建索引，查詢某個姓名，所需要的時間。(3) 對于student表，不按照系別創建索引，查詢某個系所有學生，所需要的時間。(4) 對于student表，按照系別創建各種索引，查詢某個系所有學生，所需要的時間。(5) 查詢sc表所需時間。(6) 將student和sc連接所需時間。(7) 將student和sc和course連接所需時間。(8)查詢選修了“數據庫”學生的學號姓名，分別用嵌套和連接的方法，觀察兩種方法所用的時間。本次實驗總結： 基于教材中的student、course、SC表，通過(tnggu)存儲過程（要求(yoqi)在報告中

4、寫出存儲過程），插入多條元組（1萬條以上(yshng)），考慮不同的SQL操作，分析比較執行代價。存儲過程：create procedure insert_stusexflag nvarchar=男, age int=0, dept char(8)=MA,i int=1aswhile i600000beginset age = 20if i%4 = 0 set age=17if i%4 = 1set age=18if i%4 = 2set age = 19if i%2 = 0set sexflag = 男elseset sexflag = 女set dept = MAif i%3 = 0set

5、 dept = CSif i%3 = 1 set dept = ISinsert into dbo.Student values(i, Name+cast(i as char), sexflag, age, dept)set i=i+1end create procedure insert_coui int = 1aswhile i1000begininsert into dbo.Course (Cno,Cname,Ccredit) values(i, Course+ cast(i as char), i%5)set i=i+1endUSE labGOSET ANSI_NULLS ONGOSE

6、T QUOTED_IDENTIFIER ONGOALTER procedure dbo.insert_s_ci int =1, j int =1aswhile i6000 beginset j=1while j= 20go set statistics io off set statistics time off (2)建立索引后，再查詢：查詢student表中年齡在20歲以上(yshng)的學生記錄(3)表中元組數量少，查詢結果(ji gu)所占比例大：查詢student表中年齡在19歲以下(yxi)的學生(xu sheng)記錄STU表中共1000條數據(4)表元組數量多，查詢結果所占比例

7、小：查詢student表中年齡在20歲以上的學生記錄Student表中有600000條數據分析以上四種SQL查詢的執行效率，并做總結：2.多表查詢：基于student、course、SC表，按照以下要求，實現多表查詢，并分析比較執行效率。（自行設計查詢語句，要求針對同一查詢內容，使用以下四種方式）（1）多表連接查詢set statistics io on set statistics time on go select Sname from dbo.Student,dbo.Course,dbo.SCwhere Student.Sno=SC.Sno and SC.Cno = Course.Cno

8、 and Cname=Course1 and Grade90go set statistics io off set statistics time off（2）嵌套查詢(chxn)set statistics io on set statistics time on go select Sname from Student where Sno in (select Sno from SC where Grade 90 and Cno =(select Cno from Course where Cname = Courserse1) go set statistics io off set

9、statistics time off（3）建立(jinl)索引set statistics io on set statistics time on go create index Studentgra1 on SC(Grade)select Sname from dbo.Student,dbo.Course,dbo.SCwhere Student.Sno=SC.Sno and SC.Cno = Course.Cno and Cname=Courserse1 and Grade90go set statistics io off set statistics time off（4）使用(sh

10、yng)游標set statistics io on set statistics time on go declare my_cursor cursor scroll dynamicforselect Sname from dbo.Student,dbo.Course,dbo.SCwhere Student.Sno=SC.Sno and SC.Cno = Course.Cno and Cname=Course1 and Grade90open my_cursordeclare name sysnamefetch next from my_cursor into namewhile(fetch

11、_status=0)beginprint namefetch next from my_cursor into nameendclose my_cursordeallocate my_cursorgo set statistics io off set statistics time off分析(fnx)比較：3綜合(zngh)練習(1) 對于(duy)student表，不按照姓名創建索引，查詢某個姓名，所需要的時間。set statistics io on set statistics time on go select * from dbo.Student where Sname = Na

12、me1999go set statistics io off set statistics time off(2) 對于student表，按照姓名創建索引，查詢某個姓名，所需要的時間。set statistics io on set statistics time on go create index Snameon Student(Sname)select * from dbo.Student where Sname=Name1999go set statistics io off set statistics time off(3) 對于(duy)student表，不按照系別創建索引，查詢

13、某個系所有學生，所需要的時間。set statistics io on set statistics time on go select * from dbo.Student where Sdept=CSgo set statistics io off set statistics time off(4) 對于student表，按照系別創建各種索引，查詢某個(mu )系所有學生，所需要的時間。set statistics io on set statistics time on go create index Sdepton dbo.Student (Sdept)select * from d

14、bo.Student where Sdept=CSgo set statistics io off set statistics time off(5) 查詢(chxn)sc表所需時間。set statistics io on set statistics time on go select * from dbo.SCgo set statistics io off set statistics time off(6) 將student和sc連接(linji)所需時間。set statistics io on set statistics time on go select * from db

15、o.Student,dbo.SC where Student.Sno=SC.Snogo set statistics io off set statistics time off(7) 將student和sc和course連接(linji)所需時間。set statistics io on set statistics time on go select * from dbo.Student,dbo.SC,dbo.Coursewhere Student.Sno=SC.Sno and SC.Cno=Course.Cnogo set statistics io off set statistics time off(8)查詢(chxn)選修了“數據庫”學生的學號姓名，分別用嵌套和連接的方法(fngf)，觀察兩種方法所用的時間。set statistics io on set statistics time on go select Sno,Sname from dbo.Student where Sno in(select sno from