【实验目的】
1.掌握使用SSMS创建视图的方法。
2.掌握修改视图的方法。
3.熟悉和掌握对数据表中视图的查询操作和SQL命令的使用。
【实验环境】
Windows 10系统 + SQL Server 2012
【实验内容】
基于实验一EDUC数据库中建立的视图，完成以下实验内容：
1.创建视图
（1）使用SSMS创建视图
在EDUC 库中以student表为基础，建立一个包含计算机系学生信息的视图，名为“V_计算机系学生”，显示student表中的所有字段。
（2）使用SQL语句创建视图
①在查询分析器中建立一个每个学生的学号、姓名及成绩的视图S_C_GRADE。
②建立一个所有计算机系学生的学号以及平均成绩的视图COMPUTE_AVG_GRADE。
2.修改视图
（1）使用SSMS将视图COMPUTE_AVG_GRADE 中改成建立在信息系的学生学号以及平均成绩的视图。
（2）使用SQL语句将上面建立的视图“V_计算机系学生”更名为“V_计算机系男生”。
3.使用视图
（1）查询以上所建的视图结果。
（2）查询平均成绩为 9分以上的学生学号、姓名和成绩。
（3）按系统计各系平均成绩在80分以上的人数，结果按人数降序排列。
4.删除视图
（1）使用SSMS删除视图“V_计算机系学生”。
（2）使用SQL语句删除视图COMPUTE_AVG_GRADE。
【实验方法和步骤】
1.在开始菜单中选中SQL Server Management Studio图标，启动SSMS。启动后，单击“连接”按钮，进入SSMS窗口。如下图所示：
图1
2.本次实验均基于EDUC数据库中student表，其原数据内容如下图所示：
图2
说明：以下部分题目使用SSMS和SQL语句两种方式实现，部分题目建立两个视图完成目标任务
3.建立一个包含计算机系学生信息的视图，名为“V_计算机系学生”，显示student表中的所有字段。
①可视化窗口（SSMS）：
在对象资源管理器中，展开EDUC子目录，右击“视图”，选择“新建视图”。然后再选择添加student，选择student 所有字段，然后在下面继续输入select dbo.student.* from dbo.student where dno='计算机'，关闭保存为“V_计算机系学生”，刷新。右键点击展开EDUC子目录，右键点击展开视图，再次右击“V_计算机系学生”，选择打开视图查看即可。
结果如下图所示：
图1 
图2
②SQL语句：
create view V_计算机系学生 as select * from student
where dno='计算机'
结果如下图所示：
图3
4.建立一个每个学生的学号、姓名及成绩的视图S_C_GRADE。
create view S_C_GRADE
as select s.sno,sname,sc.tcid,cname,score from student s
join student_course sc on s.sno=sc.sno
join teacher_course tc on sc.tcid=tc.tcid
join course c on c.cno=tc.cno
结果如下图所示：
图4
5.建立一个所有计算机系学生的学号以及平均成绩的视图COMPUTE_AVG_GRADE。
create view COMPUTE_GRADE
as select s.sno,score from student s left join student_course sc on s.sno=sc.sno
where dno='计算机'
select * from COMPUTE_GRADE
create view COMPUTE_AVG_GRADE
as select sno,avg(score)平均成绩 from COMPUTE_GRADE
group by sno
结果如下图所示：
图5 
图6
6.将视图COMPUTE_AVG_GRADE中改成建立在信息系的学生学号以及平均成绩的视图。
alter view COMPUTE_GRADE
as select s.sno,score from student s left join student_course sc
on s.sno=sc.sno
where dno='信息'
select * from COMPUTE_GRADE
alter view COMPUTE_AVG_GRADE
as select sno,avg(score)平均成绩 from COMPUTE_GRADE
group by sno
结果如下图所示：
图7 
图8
7.将上面建立的视图“V_计算机系学生”更名为“V_计算机系男生”。
exec sp_rename 'V_计算机系学生','V_计算机系男生'
结果如下图所示：
图9
图10
8.查询以上所建的视图结果。
select * from V_计算机系学生
select * from S_C_GRADE
select * from COMPUTE_AVG_GRADE
在上述题目中已显示全部结果图片，故此处不再附加。
9.查询平均成绩为90分以上的学生学号、姓名和成绩。
create view form(sno,sname,平均成绩)
as select sno,sname,avg(score) from S_C_GRADE
group by sno,sname
having avg(score)>=90
select * from form
结果如下图所示：
图11 
图12
10.按系统计各系平均成绩在80分以上的人数，结果按人数降序排列。
①建立视图：
alter view person1
as select sno,avg(score)平均成绩 from student_course
group by sno
having avg(score)>80
create view person2(dno,num)
as select dno,count(*) from student s join person1 p on s.sno=p.sno
group by dno
select dno,num from person2
order by num desc
②多表连接查询：
select dno ,count(*) num from student
where sno in (select sno from S_C_GRADE group by sno having AVG(score)>80)
group by dno ,sname
order by count(*) desc
结果如下图所示：
图13 
图14
11.删除视图“V_计算机系学生”。
右键点击所要删除的视图名称，选择“删除”。
或者drop view V_计算机系学生
12.删除视图COMPUTE_AVG_GRADE。
右键点击所要删除的视图名称，选择“删除”。
或者drop view COMPUTE_AVG_GRADE
结果如下图所示：
图15
【实验结果】
以下为实验过程中所建立过的视图：
视图1“COMPUTE_AVG_GRADE”（计算机系） 视图2“form”
视图3“V_计算机系学生”或“V_计算机系男生”
视图4“person1” 视图5“person2”
视图6“S_C_GRADE” 视图7“COMPUTE_AVG_GRADE”（数学系）
【总结】
并不是所有的视图都可以更新数据，只有对满足更新条件的视图才能更新数据。可更新条件为：
①任何通过视图的数据修改（包括update、insert和 delete语句）都只能引用一个基本表的列。如果视图数据为一个表的行、列子集，则此视图可更新（包括update、insert和 delete语句）。但如果视图中没有包含表中某个不允许取空值又没有默认值约束的列，则不能利用视图插入数据。如果视图所依赖的基本表有多个时，完全不能向该视图insert数据。若视图依赖于多个基本表，那么一次修改只能update一个基本表中的数据。若视图依赖于多个基本表，那么不能通过视图delete数据。
②视图中被修改的列必须直接引用表列中的基础数据。不能是通过任何其他方式对这些列进行派生而来的数据，比如通过聚合函数、计算（如表达式计算）、集合运算等。
③被修改的列不应是在创建视图时受group by、having、distinct或top子句影响的。
④有可能插入并不满足视图查询的where子句条件中的一行。为了进行限制此操作，可以在创建视图时使用with check option选项。