一、实验目的
1.熟悉视图的操作。
二、实验内容及要求
用SQL语句完成下列功能。使用数据库为SCHOOL数据库。
1.建立一视图View_CSTeacher,列出计算机系各个老师的资料(姓名、性别、职称)。
(我使用的是嵌套查询,也可使用连接查询 )
create view View_CSTeacher
as select Teac_name,Teac_sex,TechPost from Teacher
where Depar_id=(select Depar_id from Deparment
where Depar_name='计算机系')
2.建立一视图View_Class,列出每个班级的名称、系别和班级人数。
create view View_Class as select Class_name as 班级名称,Class.Depar_id as 系别,count(Student.Class_id) as 班级人数 from Teacher,Class,Student where Class.Depar_id=Teacher.Depar_id and Class.Class_id=Student.Class_id
消息 8120,级别 16,状态 1,过程 View_Class,行 2 [批起始行 0]
选择列表中的列 'Class.Class_name' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。包含count()等聚合函数的查询语句,select 后面的字段必须要么出现在group by中,要么出现在函数里。
因此加一个group by语句即可
create view View_Class
as select Class_name as 班级名称,Class.Depar_id as 系别,count(Student.Class_id) as 班级人数
from Teacher,Class,Student
where Class.Depar_id=Teacher.Depar_id and Class.Class_id=Student.Class_id
group by Class_name,Class.Depar_id
3.建立一视图View_Student,列出每个学生的学号、选修课程门数和平均成绩,并按平均成绩从大到小排列。(体验在视图中使用order by。注意观察视图中数据是否实现按成绩排序?
视图不能使用order by 语句,报错如下
create view View_Student as select Student.Stu_id as 学生学号,count(StudentGrade.Stu_id) as 选修课程门数,avg(Grade) as 平均成绩 from Student,StudentGrade where StudentGrade.Stu_id=Student.Stu_id group by Student.Stu_id order by avg(Grade)
消息 1033,级别 15,状态 1,过程 View_Student,行 6 [批起始行 0]
除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
删去order by 语句,再使用如下代码查找视图数据,并使用order by 语句排序,即可得到
select * from View_Student order by 平均成绩 desc
4.查询平均成绩大于85分的学生的所有信息。(要求使用视图View_Student完成查询)
select *
from View_Student
where 平均成绩>85
5.修改视图View_Student(修改视图定义),列出每个学生的学号、姓名、选修课程门数和平均成绩。
alter view View_Student
as select Student.Stu_id as 学生学号,Stu_name as 学生姓名,count(StudentGrade.Stu_id) as 选修课程门数,avg(Grade) as 平均成绩
from Student,StudentGrade
where StudentGrade.Stu_id=Student.Stu_id
group by Student.Stu_id,Stu_name
6.要通过视图View_Student,将学号为“000503002”的平均成绩改为90分,是否可以实现?并说明原因
update View_Student set 平均成绩=90 where 学生学号=000503002
消息 4406,级别 16,状态 1,第 1 行
对视图或函数 'View_Student' 的更新或插入失败,因其包含派生域或常量域。
因为视图里的数据是从其他表中得出的数据,修改视图中的数据最终表现为对表的修改,而表中不存在视图的某一属性,或属性的性质不相同,则无法更改,因此不能修改视图
解决方法:重新创建一个结构内容都一样的表,再进行修改
7.删除视图View_CSTeacher。
drop view View_CSTeacher
8.建一视图View_Studentnum,列出每个班级信息以及班级人数。
直接查找class的所有列名:
create view View_Studentnum
as select Class.Class_id,Class_name,Director,Monitor,Depar_id,count(Stu_id) as 班级人数
from Student,Class
where Class.Class_id=Student.Class_id
group by Class.Class_id,Class_name,Director,Monitor,Depar_id
使用自身连接 :
create view View_Studentnum
as select c.*,count(Stu_id) as 班级人数
from Student s,Class c
where c.Class_id=s.Class_id
group by c.*
三、实验小结
1.试述视图的优点;思考基本表与视图的区别和联系。
基本表是本身独立存在的表,在 SQL 中一个关系就对应一个表。
视图是从一个或几个基本表导出的表。它与基本表不同,是一个虚表。数据库中只存放视图的定义,而不存放视图中对应的数据,这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化,从视图中查询出来的数据也就随之改变了
视图的优点:
1.视图能够简化用户的操作
2.视图使用户能以多种角度看待同一数据
3.视图对重构数据库提供了一定程度的逻辑独立性
4.视图能够对机密数据提供安全保护
5.适当利用视图可以更清晰地表达查询
2.是否所有视图都可以更新?为什么?举例说明哪些视图不能更新。
不是,视图是不实际存储数据的虚表,因此对视图的更新,最终要转换为对基本表的更新。因为有些视图的更新不能唯一有意义地转换成对相应基本表的更新,所以,并不是所有的视图都可以更新的。
不能更新的视图如下:
1.若视图是由两个以上的基表导出的,则此视图不允许更新;
2.若视图的字段是来自字段表达式或常数,则不允许对此视图执行INSERT、UPDATE操作,允许执行DELETE操作;
3.若视图的字段是来自聚集函数,则此视图不允许更新;
4.若视图的定义中含有GROUP BY子句,则此视图不允许更新;
5.若视图的定义中含有DISTINCT短语,则此视图不允许更新;
6.若视图的定义中有嵌套查询,并且嵌套查询的FROM子句中涉及的表也是导出该视图的基表,则此视图不允许更新;
7.一个不允许更新的视图上定义的视图也不允许更新;
(由一个基表定义的视图,只含有基表的主键或候补键,并且视图中没有用表达式或函数定义的属性,才允许更新。)