【SQL Server数据库】视图的使用

一、用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操作会导致数据来源的不唯一性，无法直接确定要更新哪个底层表的数据。

使用了子查询的视图：如果视图中包含了子查询，用于从其他表中检索数据，那么该视图通常是不可更新的。因为子查询的结果集无法直接映射到底层表中。

需要注意的是，即使视图本身是可更新的，也需要满足一些额外的条件才能进行更新，例如视图必须基于单个表、视图必须包含主键等。