视图和索引的创建和使用实验报告

更新时间：2023-12-05 01:24:01 阅读量：教育文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

信息工程学院实验报告

课程名称：《数据库原理》实验项目名称：视图和索引的创建和使用

成绩：指导老师 ( 签名 ) ：一、实验目的:

（1）掌握创建视图的SQL语句的用法。（2）掌握修改视图的方法。

（3）熟悉视图更新与基本表更新的区别与联系；认识视图的作用。（4）熟悉索引的作用，以及不同类型索引的区别。（5）学会用T-SQL语句对表创建和删除索引

二、实验设备与器件 Win7 +Sql server 2008 三、实验内容与步骤

利用备份文件school，还原数据库，然后完成以下实验内容：（1）用T-SQL语句，定义信息系学生基本情况视图V_IS。

（2）用T-SQL语句，将Student，Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为

视图V_S_C_G。

（3）用T-SQL语句，将各系学生人数，平均年龄定义为视图V_NUM_AVG。（4）用T-SQL语句，定义一个反映学生出生年份的视图V_YEAR。

（5）用T-SQL语句，将各位学生学号、选修课程的门数及平均成绩定义为视图V_AVG_S_G。（6）用T-SQL语句，将各门课程的课程号、选修人数及平均成绩定义为视图V_AVG_C_G。（7）用T-SQL语句完成以下视图操作，查看结果，并分析原因。

①通过视图V_IS，将学号为“200215125”的学生姓名更改为“张小立”,并查看结果; ②通过视图V_IS，新增加一个学生记录 ('200215126','黄笑',19, '男','IS')，并查看结果。 ③通过视图V_IS，新增加一个学生记录 ('200215127','李霞',19, '女','MA')，并查看结果。 ④通过视图V_IS，删除学号为“200215126”的学生信息，并查看结果。

⑤通过视图V_S_C_G，将学号“200215122”的姓名改为“刘晓晨”，能否实现？若无法实现说明原因。 ⑥通过视图V_AVG_S_G，将学号“200215121”的平均成绩改为90，能否实现？若无法实现说明原因。 ⑦修改视图V_IS定义，添加WITH CHECK OPTION语句，然后用update语句修改“200215125”的学生所在系，改为’MA’, 能否实现？若无法实现说明原因。 ⑧修改视图V_YEAR，对其进行加密处理，并查看结果。

（8）写出T-SQL语句：利用表和前面所建视图，查询平均成绩为90分以上的学生学号、姓名和成绩；（9）写出T-SQL语句：利用表和前面所建视图，查询成绩大于课程平均成绩的学生学号、课程号和成绩；（10）写出T-SQL语句：利用表和前面所建视图，按系分组统计平均成绩80分以上的人数，按降序排列。（11）为student表创建一个复合索引i_sdept_sno，以院系升序、学号降序。（12）在student表的sname列上建立普通降序索引。（13）在course表的cname列上建立唯一索引。

（14）将sc表的原先在sno,cno列上的聚集索引，改为在sno列上建立。（15）用系统过程sp_helpindex查看表student中的索引信息。（16）用系统过程sp_rename，将索引i_sdept_sno的名称改为i_s。

（17）删除索引i_s。

四、实验总结：

（1）用T-SQL语句，定义信息系学生基本情况视图V_IS。

CREATE VIEW V_IS AS

SELECT* FROM student WHERE sdept='IS'

视图V_S_C_G。

（2）用T-SQL语句，将Student，Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为

CREATE VIEW V_S_C_G AS

SELECT STUDENT.SNO,SNAME,COURSE.CNO,CNAME,SC.GRADE FROM Student,Course,SC

WHERE student.SNO=SC.sno AND SC.CNO=COURSE.CNO

（3）用T-SQL语句，将各系学生人数，平均年龄定义为视图V_NUM_AVG。

CREATE VIEW V_NUM_AVG AS

SELECT COUNT(SNO)DEPTNUM,AVG(SAGE)AGE,SDEPT FROM Student GROUP BY SDEPT

（4）用T-SQL语句，定义一个反映学生出生年份的视图V_YEAR。

CREATE VIEW V_YEAR AS

SELECT SNAME,2014-SAGE ‘出生年份’ FROM Student

（5）用T-SQL语句，将各位学生学号、选修课程的门数及平均成绩定义为视图V_AVG_S_G。

CREATE VIEW V_AVG_S_G AS

SELECT STUDENT.SNO '学号',SNAME '姓名',COUNT(*) '选修门数',AVG(GRADE) '平均成绩' FROM student,course,SC

WHERE Student.SNO=SC.SNO AND SC.CNO=COURSE.CNO GROUP BY STUDENT.SNO,SNAME

（6）用T-SQL语句，将各门课程的课程号、选修人数及平均成绩定义为视图V_AVG_C_G。

CREATE VIEW V_AVG_C_G AS

SELECT SC.CNO'课程号',AVG(GRADE)'平均成绩',COUNT(CNO)'选修人数' FROM SC

GROUP BY CNO

（7）用T-SQL语句完成以下视图操作，查看结果，并分析原因。

①通过视图V_IS，将学号为“200215125”的学生姓名更改为“张小立”,并查看结果; UPDATE V_IS

SET SNAME=’张小立’ WHERE SNO=200215125

视图结果：

Student 表

结果显示在视图操作，会影响到建立视图时用的STUDENT 这张表格。

②通过视图V_IS，新增加一个学生记录 ('200215126','黄笑',19, '男','IS')，并查看结果。 INSERT INTO V_IS(SNO,SNAME,SSEX,SAGE,SDEPT) VALUES(‘200215126’,’黄笑’,’男’,19,’IS’) SELECT* FROM V_IS

WHERE SNO=’200215126’

③通过视图V_IS，新增加一个学生记录 ('200215127','李霞',19, '女','MA')，并查看结果。 INSERT INTO V_IS(SNO,SNAME,SSEX,SAGE,SDEPT) VALUES(‘200215127’,’李霞’,’女’,19,’MA’) SELECT* FROM V_IS

WHERE SNO=’200215127’

但是视图没有此条数据

④通过视图V_IS，删除学号为“200215126”的学生信息，并查看结果。 DELETE FROM V_IS

WHERE SNO=’200215126’

⑤通过视图V_S_C_G，将学号“200215122”的姓名改为“刘晓晨”，能否实现？若无法实现说明原因。 UPDATE V_S_C_G SET SNAME=’刘晓晨’ WHERE SNO=’200215122’

可实现此次操作

⑥通过视图V_AVG_S_G，将学号“200215121”的平均成绩改为90，能否实现？若无法实现说明原因。

UPDATE V_AVG_S_G SET [平均成绩]='90'

WHERE [学号]='200215121'

不能实现，原因是系统无法修改各科成绩，以使平均成绩为90，所以V_AVG_S_G视图时不可更新的。

⑦修改视图V_IS定义，添加WITH CHECK OPTION语句，然后用update语句修改“200215125”的学生所在系，改为’MA’, 能否实现？若无法实现说明原因。 ALTER VIEW V_IS AS

SELECT * FROM STUDENT WHERE SDEPT=’IS’ WITH CHECK OPTION UPDATE V_IS SET SDEPT=’MA’ WHERE SNO=’200215125’