SqlServer：使用视图 View

2024-03-12 16:36:20

视图 View
创建视图
- SQL 语句
- 创建样例
操作视图
- 插入数据
- 操作样例
参考资料

视图 View

视图 View 与表的级别相同，是数据库的另一种对象，也是以表的方式来呈现。视图是一个虚拟的表，它的数据来源于一个或多个基表或视图。视图的作用类似于筛选，因为表更多地是基于实际情况的设计，呈现的时候或许并不需要将整张表都呈现出来。从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据；从数据库系统内部来看，视图是由 SELECT 语句组成的查询定义的虚拟表；从显示形式看，视图可以看做一张表，对表的增、删、改、查操作都可以应用于视图。
视图是存储在数据库中用于查询的 SQL 语句，它具有安全性和简单性 2 个特点。安全性表现为视图可以隐藏一些数据，仅呈现需要的数据，简单性指的是可使复杂的查询易于理解和使用。使用视图的 4 个原因如下：

视点集中：视图相当于提供了一个特定的“窗口”，用户所看到的数据只跟用户的需求有关系，多余的数据都不显示；
方便操作：视图可以将几个表的数据集中到一起，从而避免了复杂语句的编写，简化 SQL 语句；
数据安全：用户只能查询和修改他们所能看到的数据，不能看到其他没有权限的数据或者敏感信息；
定制数据：视图能够实现让不同的用户以不同的方式，看到不同或相同的数据集。

创建视图

SQL 语句

建立视图的 SQL 语句如下，由于晒图是基于具体的 SELECT 语句，所以建立视图时需要给出指定的 SELECT 语句。使用 WITH ENCYPTION 可以对存放的 CREATE VIEW 的文本加密，但是该选项使用后创建视图的命令不会再显示。WITH CHECK OPTION 表示对视图进行增、删、改操作时，要保证更新、插入或删除的记录满足视图中 SELECT 语句的条件表达式。

CREATE VIEW 视图名
[WITH ENCRYPTION] AS
SELECT 语句
[WITH CHECK OPTION]

创建样例

例如数据库中有 Student、Score、Course 3 张表，3 张表的字段和存在的记录如下。

样例一

创建视图 V 包括学号，姓名，性别，课程号、课程名、成绩，首先要先写出查询这些记录的 SELECT 语句。

CREATE VIEW V_A AS
SELECT s.Sno 学号, s.Sname 姓名, s.Ssex 性别, sc.Cno 课程号, c.Cname 课程名, sc.Degree 成绩
FROM Student s
JOIN Score sc ON sc.Sno = s.Sno
JOIN Course c ON c.Cno = sc.Cno

样例二

创建视图 V_A 包括学号、姓名、性别，和样例一不同在于样例二只有一个基表。

CREATE VIEW V_A AS
SELECT Sno 学号, Sname 姓名, Ssex 性别
FROM Student

样例三

创建视图 V_B 呈现优秀学生（所有成绩不低于 80），包括姓名、课程名、成绩，同时启用 WITH ENCRYPTION。

CREATE VIEW V_B WITH ENCRYPTION AS
SELECT s.Sname 姓名, c.Cname 课程名, sc.Degree 成绩
FROM Student s
JOIN Score sc ON sc.Sno = s.Sno
JOIN Course c ON c.Cno = sc.Cno
WHERE Degree >= 80

检查 WITH ENCRYPTION 效果，此时看到的数据应该是不可见的。

SELECT name,text FROM sysobjects o 
JOIN syscomments c ON o.id = c.id
WHERE name = 'V_B'

样例四

创建视图 V_C 呈现优秀学生（所有成绩不低于80），包括姓名、课程名、成绩，同时启用 WITH check option。

CREATE VIEW V_C AS
SELECT s.Sname 姓名, c.Cname 课程名, sc.Degree 成绩
FROM Student s
JOIN Score sc ON sc.Sno = s.Sno
JOIN Course c ON c.Cno = sc.Cno
WHERE Degree >= 80 WITH check option

操作视图

插入数据

视图是一个虚拟的表，本身并不存储数据，因此对视图的操作实际上就是对表的操作。利用视图进行增加、删除和修改记录的相关列的值会更加简洁方便，命令的格式与表操作相同。使用 INSERT 语句实现对表增加数据，但有以下 8 个要求：

用户具有向数据表插入数据的权限；
插入数据时只能是明确基表应用的字段取值，还要符合建立表的约束；
未引用的字段应具备允许空值、设有默认值、是标识字段、且数据类型是 timestamp 或 uniqueidentifer；
视图不能包含多个字段的组合；
视图不能有聚合函数；
视图不能包含 DISTINCT 或 GROUP BY 子句；
定义视图使用 WITH CHECK OPTION，则插入数据时应符合相应条件；
若视图引用多个表，一条 INSERT 语句只能使用同一个基表中的数据。

操作样例

样例一

此时对 V_A 进行 UPDATE 不仅会影响到视图，也会影响到原表。把学号 103 课程号 3-105 的姓名改为“陆君茹1”，性别为女。

UPDATE V_A SET 姓名 = '陆君茹1', 性别 = '女' WHERE 学号 = 103 AND 课程号 = '3-105'

此时如果要对视图 INSERT 一条记录，会插入失败，因为此时插入数据涉及到多个基表导致不能插入。

INSERT INTO V_A VALUES (100, '张三','男',' 6-166','数字电路', 100)

样例二

如果是创建视图样例二的 V_A，此时就可以 INSERT 数据了，因为只涉及到一个基表。

INSERT INTO V_B VALUES (100, '张三','男')

样例三

对 V_B 执行下面的 UPDATE 时，因为 Degree 字段来自 Score 表，而 Sname 和 Class 字段来自 Student 表，由于对视图的修改涉及了多个基表因此失败。

UPDATE V_B SET Sname='李君帅', Class ='95033', Degree += 1 WHERE Sname = '李君帅1' AND Cname = '数字电路'

想要完成这个修改，就需要分别修改各个基表的值，有多少个基表就写多少个 UPDATE 语句分别修改。

UPDATE V_B SET Sname='李君帅', Class ='95033' WHERE Sname = '李君帅1' AND Cname = '数字电路'
UPDATE V_B SET Degree += 1 WHERE Sname = '李君帅' AND Cname = '数字电路'

样例四

对 V_C 执行下面的 UPDATE 时，因为视图 v_C 指定了 WITH CHECK OPTION，也就是说此处视图中的数据存在 WHER degree > 80 的条件，但是 degree 为 78 < 80，不符合 CHECK OPTION degree > 80 的约束，所以操作失败。

UPDATE V_C SET 成绩 = 78 WHERE 姓名 = '李君帅' AND 课程名 = '[马克思主义基本原理]概论'

参考资料

《SqlServer 2014 数据库技术实用教程》，胡伏湘、肖玉朝主编，清华大学出版社

码农公寓