一、用SQL语句完成下列功能。
1．建立一视图View_CSTeacher，列出计算机系各个老师的资料(姓名、性别、职称)。
create view View_CSTeacher as
select Teac_name, Teac_sex, TechPost
from Teacher
where Depar_id in (select Depar_id from Deparment where Depar_name = '计算机系');
2．建立一视图View_Class，列出每个班级的名称、系别和班级人数。
create view VIEW_Class(Class_name, Depar_id, 班级人数)
as
select c.Class_name, d.Depar_id, count(Stu_id) '班级人数'
from Class c
join Deparment d on c.Depar_id = D.Depar_id
join Student s on c.Class_id = s.Class_id
group by c.Class_name, d.Depar_id;
3．建立一视图View_Student，列出每个学生的学号、选修课程门数和平均成绩，并按平均成绩从大到小排列。
（体验在视图中使用order by。注意观察视图中数据是否实现按成绩排序？为什么？）
/*************************没有包含未选课的****************************8*/
create view VIEW_Student as
select top 100 percent Stu_id, count(course_id) '选修课程门数', avg(Grade) '平均成绩'
from StudentGrade
group by Stu_id
order by avg(Grade) desc;
-- △如果order by没有加上TOP 100 percent：
-- 那么报错：除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
/*************************包含了未选课的******************************/
create view VIEW_Student as
select TOP 100 percent s.Stu_id, count(course_id) '选修课程门数', avg(Grade) '平均成绩'
from Student s
left join StudentGrade sg on s.Stu_id = sg.Stu_id
group by s.Stu_id
order by avg(Grade) desc;视图中的数据没有按成绩排序。
4．查询平均成绩大于85分的学生的所有信息。
（要求使用视图View_Student完成查询）
select s.*,vs.平均成绩
from VIEW_Student vs,
Student s
where vs.平均成绩 > 85
and vs.Stu_id = s.Stu_id;
5．修改视图View_Student（修改视图定义），列出每个学生的学号、姓名、选修课程门数和平均成绩。
/*包含未选课学生*/
alter view View_Student as
select s.Stu_id, s.Stu_name, count(Course_id) '选修课程门数', avg(Grade) '平均成绩'
from Student s
left join StudentGrade SG on s.Stu_id = SG.Stu_id
group by s.Stu_id, s.Stu_name;
6．要通过视图View_Student，将学号为“000503002”的平均成绩改为90分，是否可以实现？并说明原因
UPDATE View_Student
SET 平均成绩 = 90 WHERE Stu_id = '000503002';/* 报错：对视图或函数 'View_Student' 的更新或插入失败，因其包含派生域或常量域。
不可以实现，因为平均成绩是聚合函数查询，而基本表中没有对应的属性，修改的含义不确定，故视图不可更新。*/
7.删除视图View_CSTeacher。
drop view View_CSTeacher;
8.建一视图View_Studentnum，列出每个班级信息以及班级人数。
/*包含没选课的班级*/
create view View_Studentnum as
select c.Class_id, Class_name, Director, Monitor, Depar_id, count(s.Stu_id) '班级人数'
from Class c
left join Student s on c.Class_id = s.Class_id
group by c.Class_id, Class_name, Director, Monitor, Depar_id;
二、小结
1．试述视图的优点；思考基本表与视图的区别和联系。
1.1 视图的优点
数据安全性：视图可以限制用户对数据的访问权限。通过在视图上设置适当的权限，可以控制用户只能访问他们需要的数据，而不是整个数据库。这提高了数据的安全性，防止未经授权的用户访问敏感数据。
简化复杂查询：视图可以通过组合多个表、使用聚合函数等方式，将复杂的查询逻辑封装在一个视图中。这样，用户只需对视图进行简单的查询，而无需了解底层的复杂查询逻辑。视图的存在简化了用户对数据的操作，提高了查询的可读性和易用性。
数据抽象和封装：视图可以隐藏底层数据结构和表之间的关系，为用户提供一个抽象的数据层。通过定义视图，可以将数据的逻辑结构与物理结构分离，使用户更专注于数据的业务逻辑而不需要关注底层的实现细节。这提高了应用程序的模块化和可维护性。
简化数据更新：通过视图，可以简化对数据的更新操作。对于复杂的数据更新操作，可以通过在视图上定义触发器（Trigger）来自动处理。这样，用户只需对视图进行更新，而不需要直接操作底层的表，减少了错误和冗余的代码。
提高性能：视图可以预先计算和缓存查询结果，以提高查询的性能。当多个用户频繁访问相同的查询逻辑时，使用视图可以避免重复计算，提高系统的响应速度。
1.2 基本表与视图的区别和联系
（1）区别：
存储方式：基本表是实际存储数据的物理表，而视图是一个虚拟表，不存储实际的数据，只是对基本表的查询结果进行封装和展示。
数据更新：基本表可以直接进行数据的插入、更新和删除操作，而视图一般只能进行查询操作。对于某些特定的视图，可能允许进行部分的数据更新，但这些更新操作实际上是对基本表的操作。
数据结构：基本表具有自己的列和约束，而视图是基于一个或多个基本表的查询结果，其列和约束是由查询语句决定的。
存储空间：基本表需要占用实际的存储空间，而视图不需要额外的存储空间，只是在查询时动态生成结果。
（2）联系：
数据来源：视图是基于一个或多个基本表的查询结果，因此视图的数据来自于基本表。
数据展示：视图可以对基本表的数据进行加工、过滤和组合，以提供更方便的数据展示形式。
数据访问权限：视图可以通过设置权限来限制用户对基本表的访问。通过视图，可以隐藏基本表的某些列或行，只允许用户访问他们需要的数据。
查询优化：视图可以提前计算和缓存查询结果，以提高查询性能。通过使用视图，可以避免重复执行复杂查询，减少系统的负载。
2．是否所有视图都可以更新？为什么？举例说明哪些视图不能更新。
不是所有视图都可以更新，原因是视图的可更新性取决于视图的定义以及底层表的结构和约束。
以下是一些情况下视图不能更新的示例
包含聚合函数的视图：如果视图中包含了聚合函数（如SUM、COUNT等），则该视图通常是不可更新的，因为聚合函数会导致数据的汇总和计算，无法直接反映到底层表中。
包含GROUP BY子句的视图：如果视图中包含了GROUP BY子句，用于对数据进行分组，那么该视图通常是不可更新的。因为分组操作会导致数据的合并和汇总，无法直接更新到底层表中。
包含DISTINCT关键字的视图：如果视图中使用了DISTINCT关键字，用于去重操作，那么该视图通常是不可更新的。因为去重操作会导致数据的合并和删除，无法直接更新到底层表中。
包含JOIN操作的视图：如果视图中包含了JOIN操作，用于连接多个表的数据，那么该视图通常是不可更新的。因为JOIN操作会导致数据来源的不唯一性，无法直接确定要更新哪个底层表的数据。
使用了子查询的视图：如果视图中包含了子查询，用于从其他表中检索数据，那么该视图通常是不可更新的。因为子查询的结果集无法直接映射到底层表中。
需要注意的是，即使视图本身是可更新的，也需要满足一些额外的条件才能进行更新，例如视图必须基于单个表、视图必须包含主键等。