数据库实验7.docx
《数据库实验7.docx》由会员分享，可在线阅读，更多相关《数据库实验7.docx（15页珍藏版）》请在冰点文库上搜索。
数据库实验7
实验项目
名称
实验7视图的创建与维护
实验
目的
及要求
实验7视图的创建与维护
1.在实验开始之前做好准备工作。
2.用不同的方法创建视图。
3.思考视图和基本表的区别。
2．提交并验收实验结果。
3．将完成的上述内容写入“实验7视图的创建与维护”的实验报告。
实验环境
PC、Windows2000以上操作系统和MicrosoftSQLServer2005
实验步骤
（注：
请按要求记录完成该子项目的详细实验步骤。
）
实验7视图的创建与维护
1.创建视图
（1）使用企业管理器创建视图
（2）使用SQL语句创建视图
2.修改视图
（1）使用企业管理器修改视图
（2）使用SQL语句修改视图
3.删除视图
（1）使用企业管理器删除视图
用企业管理器删除视图“V_计算机系学生”
（2）使用SQL语句删除视图
用SQL语句删除视图COMPUTE_AVG_GRADE;
4.定义视图（使用企业管理器）
（1）定义计算机系学生基本情况视图V_Computer；
createviewComputer
as
selectstudents.*fromstudents,department
wherespname='信息学院'andstudents.spno=department.spno
（2）将Student、Course和Student_course表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G
createviewV_S_C_G
as
selectstudents.sno,sname,o,cname,scorefromstudents,course,enrollment
wherestudents.sno=enrollment.snoandcourse.cno=enrollment.cno
（3）将各系学生人数，平均年龄定义为视图V_NUM_AVG
createviewV_NUM_AVG
as
selectdepartment.spno,spname,count（*）as'学生人数',
avg（datediff（year,birthday,entime））as'平均年龄'
fromdepartment,students
wheredepartment.spno=students.spno
groupbydepartment.spno,spname
（4）定义一个反映学生出生年份的视图V_YEAR
createviewV_YEAR（学号,姓名,出生年份）
as
selectsno,sname,year（birthday）
fromstudents
（5）将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G
createviewV_AVG_S_G
as
selectstudents.sno,sname,count（*）as'选修课程的门数',avg（score）as'平均成绩'
fromstudents,enrollment
wherestudents.sno=enrollment.sno
groupbystudents.sno,sname
（6）将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G
createviewV_AVG_C_G
as
selecto,cname,count（*）as'选课的人数',avg（score）as'平均成绩'
fromcourse,enrollment
whereo=o
groupbyo,cname
（7）定义学生信息视图V_IS（学号、姓名、年龄、院系）
createviewV_IS
as
selectsno,sname,year（getdate（））-year（birthday）as'年龄',spnamefromstudents,department
wherestudents.spno=department.spno
5.使用视图
（1）查询以上所建的视图结果。
selectsno,snamefromV_IS
（2）查询平均成绩为90分以上的学生学号、姓名和成绩；
selectV_AVG_C_G.sno,V_AVG_C_G.sname,V_AVG_C_G.平均成绩
fromV_AVG_C_G
whereV_AVG_C_G.平均成绩>90
（3）查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；
selectV_AVG_S_G.sno,V_AVG_S_G.sname,V_S_C_G.cname,V_S_C_G.score,V_AVG_S_G.平均成绩
fromV_S_C_G,V_AVG_S_G
whereV_AVG_S_G.sname=V_AVG_S_G.sname
andV_S_C_G.score>V_AVG_S_G.平均成绩
（4）按系统计各系平均成绩在80分以上的人数，结果按降序排列；
selectspname,count（*）num
fromV_AVG_S_G,V_IS
whereV_AVG_S_G.平均成绩>80andV_AVG_S_G.sno=V_IS.sno
groupbyspname
orderbynumdesc;
6.修改视图
（1）通过视图V_IS，分别将学号为“01”和“04”的学生姓名更改为“01_MMM”,”04_MMM”并查询结果;
updateV_ISsetsname='S1_MMM'wheresno='01'
updateV_ISsetsname='S4_MMM'wheresno='04'
select*fromV_IS
（2）通过视图V_IS，新增加一个学生记录（'S12','YANXI','IS'），并查询结果
createtriggerchenonV_IS
insteadofinsert
as
declare@snochar（8）,@snamechar（8）
declare@spnamechar（20）,@temp_spnochar（8）
select@sno=学号,@sname=姓名,@spname=院系from
insertedselect@temp_spno=spnofromdbo.department
wherespnamelike@spnameinsertdbo.students（sno,sname,spno）
values（@sno,@sname,@temp_spno）
插入代码：
insertintoV_IS
values（'S12','YANXI','IS'）
select*fromV_IS
（3）通过视图V_IS，新增加一个学生记录（'S13','YANXI','MA'），并查询结果
insertintoV_IS
values（'S13','YANXI','MA'）
select*fromV_IS
（4）通过视图V_IS，删除学号为“S12”和“S13”的学生信息，并查询结果
createtriggerabsonV_IS
insteadofdelete
as
declare@snochar（8）,@snamechar（8）
declare@spnamechar（20）,@temp_spnochar（8）
select@sno=学号,@sname=姓名,@spname=院系from
deletedselect@temp_spno=spnofromdbo.department
wherespnamelike@spnamedeletefromstudentswheresno=@sno
删除代码：
deleteV_ISwhere学号='S12'
deleteV_ISwhere学号='S13'
GO
select*fromV_IS
（5）要通过视图V_S_C_G，将学号为“01”的姓名改为“01_MMM”，是否可以实现？
并说明原因
updateV_S_C_G
setsname='01_MMM'wheresno='01'
go
select*fromstudents
可以，不影响基本表的话可以直接通过视图更改表中数据
（6）要通过视图V_S_C_G，将学号为“01”的姓名改为“01_MMM”，同时将课程名称前加上“重修”，是否可以实现？
并说明原因
updateV_S_C_G
setsname='01_MMM'wheresno='01'
updateV_S_C_G
setcname='重修'+cname
select*fromV_S_C_G
wheresnoin（selectsnofromstudentswheresno='01'）
（7）要通过视图V_S_C_G，新增一条记录，其中学号是20,姓名是玛雅,课程号是C009,课程名称是数理逻辑,成绩是99，是否可以实现？
并说明原因
createtriggerqqwonV_S_C_G
insteadofinsert,updateas
declare@snochar（8）,@snamechar（8）
declare@cnochar（10）,@cnamechar（20）
declare@scoretinyint
insertenrollment（sno,cno,score）selectsno,cno,scorefrominserted
insertstudents（sno,sname）selectsno,snamefrominserted
insertcourse（cno,cname）selectcno,cnamefrominserted
代码：
insertintoV_S_C_G
values（'20','玛雅','C009','数理逻辑','99'）
go
select*fromV_S_C_G
可以，因为inserted可以在以视图为基础的基表中插入或更新数据
（8）要通过视图V_AVG_S_G，将学号为“01”的平均成绩改为90分，是否可以实现？
并说明原因。
updateV_AVG_S_G
set平均成绩='90'wheresno='01'
不可以，视图或函数'V_AVG_S_G'不可更新，因为它包含聚合。
注：
可根据实际情况加页
升级会员 