视图

概述

从表的角度看,每一个表都存储了大量数据,通过表可以查看这些数据.但是通过仅表操纵数据,会带来一系列的性能、安全、效率等问题.下面就从这些角度着手分析一下这些问题.

从业务数据角度来看,由于数据库设计时要考虑到数据异常的问题,同一种业务数据可能分散在不同表中,但是这些业务数据又通常是同时使用的.前面说过的连接、子查询、联合等技术就是解决问题的一个手段,但是这些操作往往涉及复杂的逻辑运算.如何从一个数据库对象中查看这些分散存储的数据呢?如果可以做到的话,将会大大简化查询语句的复杂程度.

从安全角度来看,不同操作人员只能看到表中不同部分的数据.如何有效实现这种不同操作人员查看不同数据的设计?

从数据应用的角度来看,一个报表中的数据往往来自多个不同的表中,生成报表需要指明数据来源,如何提高报表的设计效率呢?

显然,视图是解决这些问题的有效手段,那么什么是视图呢?

视图的概念、特点和类型

视图是查看数据库表中数据的一种方式.视图提供了存储预定义的查询语句作为数据库中的对象以备以后使用的能力.视图是一种逻辑对象,是虚拟表.除了索引视图,视图都不占用物理存储空间.在视图中被查询的表称为基表.大多数SELECT语句都可以在视图中先创建好,以后就可以直接使用.

一般地,视图的内容包括以下几项:

• 基表的列子集或行子集,即视图可以是基表的一部分.
• 两个或多个基表的联合,即视图可以是对多个基表进行联合运算检索的SELECT语句.
• 两个或多个基表的连接,即视图可以是多个基表连接生成的.
• 基表的统计汇总,即视图可以是基表经过各种复杂运算的结果集.
• 另一个视图的子集,即视图既可以基于表,也可以基于其它视图.
• 来自于函数/同义词的数据.
• 视图和基表的混合.

从技术上讲,视图是SELECT语句的存储定义.最多可以在1024列和无限制的行上定义视图.视图有许多优点,如:

集中用户使用的数据、掩码数据库的复杂性、简化权限管理、向其他程序输出重新组织的数据等.

---

1. 集中用户使用的数据

视图创建了一个可以控制的环境,即表中一部分(视图包含的部分)数据可以访问,另一部分不允许访问.那些不必要的、安全性敏感的数据都从视图中剔除掉了.用户可以像操纵表中数据那样操纵视图中的数据.另外,如果有一定权限和了解一些限制,可以修改视图中显示出来的数据.

2. 掩码数据库的复杂性

视图把数据库设计的复杂性和用户的使用方式屏蔽开来.这样就为开发人员提供了一种改变数据库设计而不影响用户使用的能力.另外,在使用数据库时,经常使用的名称往往是难以理解记忆的,而在视图中可以把这些列的名称替换为容易理解记忆的友好名称,从而为用户的使用提供便利.复杂的查询也可以通过视图掩码,这样用户就不必写复杂的查询语句来得到想要的数据.

3. 简化权限管理

数据库所有者可以把视图的权限授予需要查询的用户,而不必将基表中某些列的查询权限授予用户.这样就能保护修改基表的设计,又不影响用户正常查询数据.

4. 向其他程序输出重新组织的数据

可以创建一个基于两个或多个基表复杂查询的视图,然后把视图中的数据导出到另一个应用程序,以便对这些数据进一步分析.

---

SQL server中,一般把视图分为三类: 标准视图、索引视图、分区视图.一般情况下我们使用的视图都是标准视图,它是一个虚拟表,不占物理存储空间.如果要提高聚合多行数据的视图性能,可以创建索引视图.索引视图是被物理化的视图,它包含经过计算的物理数据.使用分区视图可以连接一台或多台服务器中成员表的分区数据,使得这些数据看起来就像来自一个表中一样.

创建视图

使用CREATE VIEW语句创建视图,只能在当前数据库创建视图.创建视图时,SQL server先检验创建视图所引用的对象是否存在.

视图应符合命名规则,而是否指定视图的架构是可选的.因为视图的外表和表的外表完全一致,因此为了区分表和视图,应为视图采用一种特殊的命名机制,例如可以在视图前添加一个vw或v前缀.创建标准视图的基本语法如下:

CREATE VIEW view_name
AS
SELECT statement;

视图的内容就是SELECT的结果集.根据SELECT的不同,视图既可以很简单也可以很复杂.以下情况中,必须在定义视图时指定列名:

• 由算术表达式、系统内置函数或常量得到的列.
• 共享同一个表名连接的到得列.
• 希望视图中的列名不同于表中列名.

注意在视图定义中的SELECT语句中不能包含以下内容:

• COMPUTE/COMPUTE BY语句(已过时).
• ORDER BY语句,除非配合TOP字句一起使用.
• INTO关键字.
• OPTION字句.
• 引用临时表或表变列.

现在我们建立一个视图,用于查看员工名称,工号和雇用日期:

USE AdventureWorks2017;
GO

CREATE VIEW vw_EmployeeHireDate (EmployeeName,EmployeeID,HireDate)
AS
SELECT P.FirstName+''+P.MiddleName+''+P.LastName,E.BusinessEntityID,E.HireDate
FROM HumanResources.Employee E JOIN Person.Person P
	ON	E.BusinessEntityID = P.BusinessEntityID;
GO

SELECT * FROM vw_EmployeeHireDate;
GO

运行结果如下:

如果要查看vw_EmployeeHireDate视图的定义文本,可以使用sp_helptext view_name存储过程,或者查看目录视图sys.sql_modules的definition列,如下:

USE AdventureWorks2017;
GO

EXEC sp_helptext vw_EmployeeHireDate;

SELECT definition FROM sys.sql_modules
GO

运行结果如下:

能够调用系统目录视图的一般都是系统管理员,对此我们无能为力,但是非管理员可以通过sp_helptext view_name来查看视图的定义文本,对此我们可以加密这个定义文本,不让其他用户查看它,这时就有点像面向对象的习惯了: 开放使用API,却不让用户查看其内部实现.加密定义文本只需要在创建视图时使用WITH ENCRYPTION子句即可,如:

USE AdventureWorks2017;
GO
--首先用GUI删除了视图vw_EmployeeHireDate;
--重新创建视图vw_EmployeeHireDate;

CREATE VIEW vw_EmployeeHireDate (EmployeeName,EmployeeID,HireDate)
WITH ENCRYPTION
AS
SELECT P.FirstName+''+P.MiddleName+''+P.LastName,E.BusinessEntityID,E.HireDate
FROM HumanResources.Employee E JOIN Person.Person P
	ON	E.BusinessEntityID = P.BusinessEntityID;
GO

EXEC sp_helptext vw_EmployeeHireDate;
GO

运行结果如下:

修改/删除视图

可以使用ALTER VIEW语句修改视图的定义.如果删除一个视图,然后又重建该视图,那么该视图所有的权限需要重新指定,但是修改视图会保留视图原有的权限.

如果使用诸如SELECT * 语句创建了一个视图,那么对基表新增一列,该列是不会同步到视图中去的,必须手动修改视图才能新增列.

如果希望创建了视图以后对基表不再进行结构上的改变,可以在定义视图时使用WITH SCHEMABINDING关键字,此时创建的视图是一个索引视图,占用物理存储空间.如果在建立索引视图后又想要更改基表的结构,则需要删除索引视图并重建.这种视图适用于视图和基表必须紧密一致的场合.此时定义视图必须指定一个架构,语法如下:

CREATE VIEW schema_name.view_name
WITH SCHEMABINDING
AS
SELECT statement;

前面说过,视图的架构是可选的,可见有架构的视图从标准视图变成了索引视图,且为了安全考虑,视图架构应和基表一致(或者说视图架构拥有的权限应 $\le$ 基表所在架构).

我们再次重新创建vw_EmployeeHireDate视图,这次创建成索引视图:

USE AdventureWorks2017;
GO
--首先用GUI删除了视图vw_EmployeeHireDate;
--重新创建视图vw_EmployeeHireDate;

CREATE VIEW vw_EmployeeHireDate (EmployeeName,EmployeeID,HireDate)	--因为使用了两个基表,不指定视图架构,默认dbo;
WITH ENCRYPTION,SCHEMABINDING
AS
SELECT P.FirstName+''+P.MiddleName+''+P.LastName,E.BusinessEntityID,E.HireDate
FROM HumanResources.Employee E JOIN Person.Person P
	ON	E.BusinessEntityID = P.BusinessEntityID;
GO

EXEC sp_helptext vw_EmployeeHireDate;
GO

运行结果如下(注意看左侧侧边栏,有很多AdventureWorks2017系统自带的索引视图):

如果视图不需要了,可以通过执行DROP VIEW语句删除视图.删除一个视图,就是删除其定义和赋予的全部权限.删除一个表不能删除引用该表的视图,因此视图需要用户手动删除.在DROP VIEW中,可以一次删除多个视图,用逗号隔开即可,基本语法如下:

DROP VIEW view_name;
GO

那我们就删除一下刚刚建立的视图:

USE AdventureWorks2017;
GO

IF OBJECT_ID('dbo.vm_EmployeeHireDate','VIEW') IS NOT NULL
DROP VIEW dbo.vm_EmployeeHireDate;
GO

运行结果如下图:

我们使用OBJECT_ID(‘Object_name’,‘type_name’)判断了对象是否存在,如果对象存在,返回其ID,否则返回NULL.

使用视图修改数据

我们知道,标准视图不占物理空间,它里面含有的数据均来自表,也随着表变化(当然表新增列这种变化不会反映到视图中去).我们对视图的修改,实际上也就是对基表的修改.因此在一定的限制条件下,可以通过视图对基表进行插入、删除和更新数据的操作.

在修改视图时,应该注意以下几点:

• 不能同时影响两个或以上的基表,基于两个或以上基表建立的视图,应保证一次仅修改一个基表的数据.
• 某些列不能修改,这些列包括计算得到的列、有内置函数的列、有聚合函数的列.
• 如果影响到一些没有默认值/不允许空值的列,可能导致错误.如某个列没有出现在视图定义中,且不允许空值/没有默认值,这样在视图中使用INSERT语句插入数据到基表,就会导致这个列出现空值,从而发生错误.
• 定义视图时若使用了WITH CHECK OPTION选项,那么系统验证所修改的数据.WITH CHECK OPTION选项强制对视图的所有修改语句必须满足定义视图使用的SELECT语句标准,否则系统就会拒绝这种修改.

例如,AdventureWorks2017数据库中有一个HumanResources.Employee表,包含公司全体职员的信息,现在要创建一个视图,要求存储公司中所有1980-1-1以后出生的青年职员信息,此时我们先建立一个视图如下:

USE AdventureWorks2017;
GO

CREATE VIEW HumanResources.vYoungEmployee
AS
SELECT BusinessEntityID,BirthDate
FROM HumanResources.Employee
WHERE BirthDate > '1980-1-1'
WITH CHECK OPTION;
GO

SELECT * FROM HumanResources.vYoungEmployee;
GO

运行结果如下:

现在我们尝试,在WITH CHECK OPTION的允许BirthDate范围内($>$ 1980)更新数据:

USE AdventureWorks2017;
GO

UPDATE HumanResources.vYoungEmployee 
	SET BirthDate = '1999-6-23'
WHERE BusinessEntityID = 7;
GO

SELECT * FROM HumanResources.vYoungEmployee;
GO

运行结果如下:

然后,让我们试试在WITH CHECK OPTION的不允许BirthDate范围内($<$ 1980)更新数据:

USE AdventureWorks2017;
GO

UPDATE HumanResources.vYoungEmployee 
	SET BirthDate = '1970-6-23'
WHERE BusinessEntityID = 7;
GO

错误提示如下:

为了验证我们对视图的更新确实作用到了基表上,我们可以查看一下基表:

USE AdventureWorks2017;
GO

SELECT BusinessEntityID,BirthDate
FROM HumanResources.Employee
WHERE BusinessEntityID = 7;

运行结果如下:

可见对视图的修改确实反映到了基表上(这是因为用户有修改基表的权限,修改才能顺利进行下去).

上一篇: 数据完整性
下一篇: 存储过程、触发器和函数