視圖和索引的創(chuàng)建和使用實(shí)驗(yàn)報(bào)告

1、信息工程學(xué)院實(shí)驗(yàn)報(bào)告成 績：指導(dǎo)老師(簽名)：課程名稱：數(shù)據(jù)庫原理 實(shí)驗(yàn)項(xiàng)目名稱：視圖和索引的創(chuàng)建和使用 一、實(shí) 驗(yàn) 目 的: （1）掌握創(chuàng)建視圖的SQL語句的用法。（2）掌握修改視圖的方法。（3）熟悉視圖更新與基本表更新的區(qū)別與聯(lián)系；認(rèn)識(shí)視圖的作用。（4）熟悉索引的作用，以及不同類型索引的區(qū)別。（5）學(xué)會(huì)用T-SQL語句對(duì)表創(chuàng)建和刪除索引二、實(shí) 驗(yàn) 設(shè) 備 與 器 件Win7 +Sql server 2008三、實(shí) 驗(yàn) 內(nèi) 容 與 步 驟 利用備份文件school，還原數(shù)據(jù)庫，然后完成以下實(shí)驗(yàn)內(nèi)容：（1）用T-SQL語句，定義信息系學(xué)生基本情況視圖V_IS。（2）用T-SQL語句，將Stud

2、ent，Course 和SC表中學(xué)生的學(xué)號(hào)，姓名，課程號(hào)，課程名，成績定義為視圖V_S_C_G。（3）用T-SQL語句，將各系學(xué)生人數(shù)，平均年齡定義為視圖V_NUM_AVG。（4）用T-SQL語句，定義一個(gè)反映學(xué)生出生年份的視圖V_YEAR。（5）用T-SQL語句，將各位學(xué)生學(xué)號(hào)、選修課程的門數(shù)及平均成績定義為視圖V_AVG_S_G。（6）用T-SQL語句，將各門課程的課程號(hào)、選修人數(shù)及平均成績定義為視圖V_AVG_C_G。（7）用T-SQL語句完成以下視圖操作，查看結(jié)果，并分析原因。通過視圖V_IS，將學(xué)號(hào)為“200215125”的學(xué)生姓名更改為“張小立”,并查看結(jié)果;通過視圖V_IS，新增

3、加一個(gè)學(xué)生記錄 ('200215126','黃笑',19, '男','IS')，并查看結(jié)果。通過視圖V_IS，新增加一個(gè)學(xué)生記錄 ('200215127','李霞',19, '女','MA')，并查看結(jié)果。通過視圖V_IS，刪除學(xué)號(hào)為“200215126”的學(xué)生信息，并查看結(jié)果。通過視圖V_S_C_G，將學(xué)號(hào)“200215122”的姓名改為“劉曉晨”，能否實(shí)現(xiàn)？若無法實(shí)現(xiàn)說明原因。通過視圖V_AVG_S_G，將學(xué)號(hào)“200215121”的平均成績改為90，能否實(shí)現(xiàn)？若

4、無法實(shí)現(xiàn)說明原因。修改視圖V_IS定義，添加WITH CHECK OPTION語句，然后用update語句修改“200215125”的學(xué)生所在系，改為MA, 能否實(shí)現(xiàn)？若無法實(shí)現(xiàn)說明原因。修改視圖V_YEAR，對(duì)其進(jìn)行加密處理，并查看結(jié)果。（8）寫出T-SQL語句：利用表和前面所建視圖，查詢平均成績?yōu)?0分以上的學(xué)生學(xué)號(hào)、姓名和成績；（9）寫出T-SQL語句：利用表和前面所建視圖，查詢成績大于課程平均成績的學(xué)生學(xué)號(hào)、課程號(hào)和成績；（10）寫出T-SQL語句：利用表和前面所建視圖，按系分組統(tǒng)計(jì)平均成績80分以上的人數(shù)，按降序排列。（11）為student表創(chuàng)建一個(gè)復(fù)合索引i_sdept_sno，

5、以院系升序、學(xué)號(hào)降序。（12）在student表的sname列上建立普通降序索引。（13）在course表的cname列上建立唯一索引。（14）將sc表的原先在sno,cno列上的聚集索引，改為在sno列上建立。（15）用系統(tǒng)過程sp_helpindex查看表student中的索引信息。（16）用系統(tǒng)過程sp_rename，將索引i_sdept_sno的名稱改為i_s。（17）刪除索引i_s。四、實(shí) 驗(yàn) 總 結(jié)：（1）用T-SQL語句，定義信息系學(xué)生基本情況視圖V_IS。CREATE VIEW V_ISASSELECT* FROM studentWHERE sdept='IS'

6、 （2） 用T-SQL語句，將Student，Course 和SC表中學(xué)生的學(xué)號(hào)，姓名，課程號(hào)，課程名，成績定義為視圖V_S_C_G。CREATE VIEW V_S_C_GASSELECT STUDENT.SNO,SNAME,COURSE.CNO,CNAME,SC.GRADEFROM Student,Course,SCWHERE student.SNO=SC.sno AND SC.CNO=COURSE.CNO（3）用T-SQL語句，將各系學(xué)生人數(shù)，平均年齡定義為視圖V_NUM_AVG。CREATE VIEW V_NUM_AVGASSELECT COUNT(SNO)DEPTNUM,AVG(SAG

7、E)AGE,SDEPTFROM StudentGROUP BY SDEPT （4） 用T-SQL語句，定義一個(gè)反映學(xué)生出生年份的視圖V_YEAR。CREATE VIEW V_YEARASSELECT SNAME,2014-SAGE 出生年份FROM Student （5）用T-SQL語句，將各位學(xué)生學(xué)號(hào)、選修課程的門數(shù)及平均成績定義為視圖V_AVG_S_G。CREATE VIEW V_AVG_S_GASSELECT STUDENT.SNO '學(xué)號(hào)',SNAME '姓名',COUNT(*) '選修門數(shù)',AVG(GRADE) '平均成績&#

8、39;FROM student,course,SCWHERE Student.SNO=SC.SNO AND SC.CNO=COURSE.CNO GROUP BY STUDENT.SNO,SNAME（6）用T-SQL語句，將各門課程的課程號(hào)、選修人數(shù)及平均成績定義為視圖V_AVG_C_G。CREATE VIEW V_AVG_C_GASSELECT SC.CNO'課程號(hào)',AVG(GRADE)'平均成績',COUNT(CNO)'選修人數(shù)'FROM SCGROUP BY CNO（7）用T-SQL語句完成以下視圖操作，查看結(jié)果，并分析原因。通過視圖V_I

9、S，將學(xué)號(hào)為“200215125”的學(xué)生姓名更改為“張小立”,并查看結(jié)果;UPDATE V_ISSET SNAME=張小立WHERE SNO=200215125視圖結(jié)果：Student 表結(jié)果顯示在視圖操作，會(huì)影響到建立視圖時(shí)用的STUDENT 這張表格。通過視圖V_IS，新增加一個(gè)學(xué)生記錄 ('200215126','黃笑',19, '男','IS')，并查看結(jié)果。INSERT INTO V_IS(SNO,SNAME,SSEX,SAGE,SDEPT)VALUES(200215126,黃笑,男,19,IS)SELECT*FROM

10、V_ISWHERE SNO=200215126通過視圖V_IS，新增加一個(gè)學(xué)生記錄 ('200215127','李霞',19, '女','MA')，并查看結(jié)果。INSERT INTO V_IS(SNO,SNAME,SSEX,SAGE,SDEPT)VALUES(200215127,李霞,女,19,MA)SELECT*FROM V_ISWHERE SNO=200215127但是視圖沒有此條數(shù)據(jù)通過視圖V_IS，刪除學(xué)號(hào)為“200215126”的學(xué)生信息，并查看結(jié)果。DELETEFROM V_ISWHERE SNO=200215126通

11、過視圖V_S_C_G，將學(xué)號(hào)“200215122”的姓名改為“劉曉晨”，能否實(shí)現(xiàn)？若無法實(shí)現(xiàn)說明原因。UPDATE V_S_C_GSET SNAME=劉曉晨WHERE SNO=200215122 可實(shí)現(xiàn)此次操作通過視圖V_AVG_S_G，將學(xué)號(hào)“200215121”的平均成績改為90，能否實(shí)現(xiàn)？若無法實(shí)現(xiàn)說明原因。UPDATE V_AVG_S_GSET 平均成績='90'WHERE 學(xué)號(hào)='200215121'不能實(shí)現(xiàn)，原因是系統(tǒng)無法修改各科成績，以使平均成績?yōu)?0，所以V_AVG_S_G視圖時(shí)不可更新的。修改視圖V_IS定義，添加WITH CHECK OPTI

12、ON語句，然后用update語句修改“200215125”的學(xué)生所在系，改為MA, 能否實(shí)現(xiàn)？若無法實(shí)現(xiàn)說明原因。ALTER VIEW V_ISASSELECT * FROM STUDENTWHERE SDEPT=ISWITH CHECK OPTIONUPDATE V_ISSET SDEPT=MAWHERE SNO=200215125無法實(shí)現(xiàn)此操作，原因是目標(biāo)視圖所跨越的某一視圖指定了WITH CHECK OPTION，而該操作的一個(gè)或多個(gè)結(jié)果又不符合WITH CHECK OPTION約束的條件。修改視圖V_YEAR，對(duì)其進(jìn)行加密處理，并查看結(jié)果。ALTER VIEW V_YEARWITH ENCRYPTIONASSELECT SNAME,2014-SAGE 出生年份FROM STUDENT（8）寫出T-SQL語句：利用表和前面所建視圖，查詢平均成績?yōu)?0分以上的學(xué)生學(xué)號(hào)、姓名和成績；SELECT V_AVG_S_G.學(xué)號(hào),student.sname,V_AVG_S_G.平均成績from student,V_AVG_S_Gwhere student.sno=V_AVG_S_G.學(xué)號(hào) and V_AVG_S_G.平均成績>