SQL Server的视图

SQL Server的视图

一、基础

SQL 视图（Views）是一种虚拟表，是基于 SQL 查询结果生成的。这些虚拟表可以包含来自一个或多个表的数据，并且可以像表一样查询；视图是一个表中的数据经过某种筛选后的显示方式，或者多个表中的数据经过连接筛选后的显示方式。使用视图可以简化复杂查询，提高查询的可读性和可维护性，并提供数据的不同视图。

视图由一个预定义的查询（SELECT语句）组成，可以像基本表一样用于SELECT语句中；如果视图满足一定条件，还可以用在INSERT、UPDATE和DELETE语句中，对视图所调用的基本表进行插入、更新和删除数据操作。

1.1、视图引例

• 示例：查询“心理学”考试成绩大于等于90的学生的“学号”、“姓名”和“所属院系”三个字段。

分析：“心理学”是stu_course表中“课名”字段的值，考试成绩是score表中“考试成绩”字段的值，而“学号”、“姓名”和“所属院系”是stu_info表中的字段。因此，想要得到本例要求的结果，则必须对stu_course、score和stu_info三个表进行连接查询。

SELECT stu_info.id AS 学号,stu_info.name AS 姓名,stu_info.institute AS 所属院系 FROM stu_info, stu_course, score WHERE stu_course.course = '心理学'    AND score.result1 >= 90    AND stu_info.id = score.s_id        AND stu_course.ID = score.c_id;

编写该SELECT语句时，首先需要了解基本表的结构，然后还要知道表之间连接的方法，最后还要编写复杂的SELECT语句。如果用户经常使用上面的查询，并且每次都要编写这一复杂的SELECT语句，如果将上面的SELECT语句保存到数据库里，每次使用时直接读取岂不是很方便，视图就是为了这种目的而诞生的。

视图里存放了SELECT语句，而并非是查询结果，每次在SQL语句中使用视图，其实就是在执行视图内存放的SELECT语句，因此通过视图总能够得到最新的数据。

• 示例2：定义一个视图vw1，将上例的SELECT语句存放到该视图内。

CREATE VIEW vw1 AS SELECT stu_info.id AS 学号,stu_info.name AS 姓名,stu_info.institute AS 所属院系 FROM stu_info, stu_course, score WHERE stu_course.course = '心理学'     AND score.result1 >= 90     AND stu_info.id = score.s_id     AND stu_course.ID = score.c_id;

视图被定义后可以像基本表一样使用。

因为视图本身不包含数据，其数据属于实际的基本表，所以如果改变了基本表中的数据，则视图返回的数据也会随之改变。目前所有主流数据库系统都支持视图。

注意：视图不是SELECT语句执行后的查询结果，即视图中不存在数据，它只是存放了SELECT语句；调用视图要考虑效率的损耗。例如，执行SELECT * FROM vw1时，实际上执行了两个SELECT语句，一个是该语句本身，另外一个是视图中存放的复杂连接的SELECT语句。

1. 改变基本表的结构后应当删除视图并重建视图。视图是基于基本表创建的。如果基本表的结构发生变化（如添加、删除或修改列），可能会导致视图无法正常工作。由于视图的定义是静态的，它不会自动适应基础表的变化，因此需要手动删除并重新创建视图以确保视图定义与基本表结构一致。
2. 删除基本表时应当删除视图。视图本身没有数据，其数据源自基础表。如果基础表被删除，视图将无法正常工作并导致错误。因此，在删除基础表之前，应先删除依赖于这些表的视图。
3. 潜在的复杂性带来的性能下降问题。如果视图的定义包含复杂的查询（如多个表的连接、嵌套查询等），数据库在访问视图时必须执行这些复杂的查询，这可能会导致性能下降。为了减轻性能问题，可以优化视图的定义，或者考虑使用索引视图（在支持的情况下）来提高性能。

• SQL Server不允许在视图定义中直接使用 ORDER BY 子句。这是因为视图本质上是一个虚拟表，其数据的排序应由查询视图时的 ORDER BY 子句来决定，而不是在定义视图时决定。
• 视图本身是不可索引的。但是，在SQL Server中，可以创建索引视图（Indexed Views），也称为物化视图，这是对某些复杂查询进行优化的一种方法。索引视图在创建时需要满足一些特定的条件，比如视图必须使用 WITH SCHEMABINDING 选项，并且视图中的表必须包含唯一的聚集索引。
• 视图不能直接拥有触发器或默认值。触发器和默认值只能应用于基础表。可以在基础表上定义触发器或默认值来实现相关功能，而不是在视图上。

二、视图的创建

2.1、利用视图提高数据安全性

利用视图可以提高数据安全性。视图可以使不同权限的用户只能操作相应权限范围内的数据，而对于权限外的数据则不可访问；例如，计科系的数据管理员只能操作计科系的学生信息，中文系的数据管理员只能操作中文系学生信息；对于计科系的数据管理员而言，其他系的学生信息是不可访问的、隐藏的，这就提高了数据安全性，大大减少了对数据误操作的概率。

1. 隐藏列数据

有时需要将表中的某些列隐藏起来，只显示指定的列，这时可以使用视图达到这种目的。

• 示例：创建一个只能查看“学号”、“姓名”和“性别”三个列的视图vw_stu1

CREATE VIEW vw_stu1 AS SELECT id AS 学号,name AS 姓名,sex AS 性别 FROM stu_info;

1. 隐藏行数据

• 示例2：创建一个只能查看计科系学生信息的视图vw_stu2

CREATE VIEW vw_stu2 AS SELECT * FROM stu_info WHERE institute = '计科系';

将视图vw_stu2上的权限授予计科系的数据管理员，则该管理员只能操作计科系学生的信息，而对stu_info表中其他院系的学生信息都是不可访问的。

2.2、利用视图得到汇总数据

可以使用视图对表中的数据进行及时汇总，当基本表中的底层数据被改变时，通过视图得到的是最新的数据。

• 示例：创建一个视图vw_stu3，显示每个不同院系的学生人数。

CREATE VIEW vw_stu3 AS SELECT institute AS 所属院系,COUNT(*) AS 人数 FROM stu_info GROUP BY institute;

说明：

TIMESTAMPDIFF()函数是MySQL的函数，其返回值是两个日期型数据之间的差值。CURDATE()函数也是MySQL的函数，其返回值为当前系统时间。

DATEDIFF()函数是SQL Server的函数，其返回值是两个日期型数据之间的差值。GETDATE()函数也是SQL Server的函数，其返回值为当前系统时间。

WHERE子句的条件表达式中不可以使用别名，因为SELECT子句的执行顺序在WHERE子句之后，而ORDER BY子句中能够使用别名的原因是ORDER BY子句在所有子句中最后一个执行。

三、视图的修改/删除

当提供视图的底层数据的基本表（基础表）的结构发生变化时，可能需要修改/删除视图，以确保视图仍然正确地反映表的数据结构和内容。 以下是一些常见的基本表结构变化的情况：

1. 添加新列
   • 如果基本表中添加了新的列，现有视图可能不会自动包含这些新列。 如果视图需要这些新列的数据，必须重新创建/修改视图。
2. 删除列
   • 如果从基本表中删除了视图引用的某些列，视图将变得无效或无法正常工作，因为它引用的列不再存在。
3. 修改列的数据类型
   • 如果基本表中某列的数据类型发生了变化，可能会导致视图中的数据类型不匹配，需要重新创建/修改视图以匹配新的数据类型。
4. 重名名列
   • 如果基本表中某列被重命名，视图引用的旧列名将失效，视图需要更新以引用新的列名。
5. 修改列的约束
   • 修改列的约束（例如添加或删除主键、外键、唯一约束等）可能会影响视图的逻辑，需要重新创建/修改视图以适应新的约束条件。
6. 删除或重命名基本表
   • 如果删除了基本表或者重命名了基本表，所有基于该表的视图都将失效，必须更新视图定义以匹配新的表名或者删除并重新创建视图。

3.1、修改视图

修改视图可以适应基础表的变化、满足新的业务需求、优化查询性能以及增强安全性和权限管理。 在修改视图时，确保视图定义与基础表的结构和业务逻辑保持一致，以避免数据不一致或查询错误。

• 示例：修改 employee_view 视图以包含 salary 排

ALTER VIEW employee_view AS SELECT id, name, age, department, salary FROM employees;

3.2、删除视图

删除视图是一个相对简单的操作，但在执行之前要确保该视图不再需要并且没有其他对象依赖于它。

可以使用 DROP VIEW 语句来删除视图：

DROP VIEW view_name;

DROP VIEW view_name1, view_name2;    --如果您需要删除多个视图，可以将视图名称用逗号分隔

注意事项

1. 依赖关系：在删除视图之前，确保没有其他对象（如其他视图、存储过程、触发器等）依赖于该视图。
2. 权限：确保您具有删除视图的权限。
3. 备份：如果不确定是否应该删除视图，可以先备份视图的定义，以防需要恢复。

3.3、视图定义

视图的定义是指创建视图时所使用的 SQL 查询语句。它描述了视图的逻辑结构和内容，即视图从基础表中选择的数据，以及选择和过滤这些数据的条件。视图的定义决定了视图返回的数据集。

• MySQL

在 MySQL 中，可以使用 SHOW CREATE VIEW 命令来获取视图的定义。

SHOW CREATE VIEW employee_view;

这个命令将返回视图的创建语句：

• SQL Server

在 SQL Server 中，可以使用系统存储过程 sp_helptext 来获取视图的定义。

sp_helptext 'employee_view';

这个命令将返回视图的创建语句

3.4、备份视图定义到文件

• MySQL

可以使用 MySQL 客户端命令行工具将输出重定向到文件：

mysql -u username -p -D database_name -e "SHOW CREATE VIEW employee_view" > employee_view_backup.sql

cat employee_view_backup.sql

• username：您的 MySQL 用户名
• database_name：包含视图的数据库名称

• SQL Server

可以使用 SQL Server Management Studio (SSMS) 或 SQLCMD 将输出保存到文件：

sqlcmd -S servername -U username -P password -Q "sp_helptext 'employee_view'" > employee_view_backup.sql

• -S为实例名称：默认可以写localhost
• -U为用户名
• -P为密码
• 视图名称为示例 employee_view