【SQL】实验九 视图的使用

 

一、实验目的

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.一个不允许更新的视图上定义的视图也不允许更新；

（由一个基表定义的视图，只含有基表的主键或候补键，并且视图中没有用表达式或函数定义的属性，才允许更新。）