SQL Server 视图

2022-11-15 21:24:15

　　视图实际上就是一个存储查询，重点是是可以混合和匹配来自基本表(或者其他视图)的数据，从而创建在很多方面像另一个基表那样起作用的对象。可以创建一个简单的查询，仅仅从一个表中选择几列，而忽略其他列；或者也可以创建一个复杂的查询，连接几个表，使的这些表看起来像一个表。

一、简单的视图

　　视图的语法如下：

CREATE VIEW <view name>

AS

<SELECT statement>

　　具体语法：

CREATE VIEW [schema_name].<view name> [(<column name list>)]

[WITH [ENCRYPTION][,SCHEMABINDING][,VIEW_METADATA]]

<SELECT statement>

WITH CHECK OPTION

　　示例：

CREATE VIEW columnNamePath_vw

AS

SELECT col_name,col_path

From Nx_column

　　这种形式的视图可以达到的目的是，向特定的用户屏蔽一部分列信息，只显示他需要的列，对于敏感的列屏蔽。

　　还有一种就是作为过滤的视图，通过使用where子句，达到屏蔽一部分行的信息，只显示他需要的行，对于敏感的行屏蔽。

二、复杂点的视图

　　复杂点的视图，其实也复杂不到哪里去，无非是添加一些inner join，left join之类的东西而已。

　　此外，还可以通过使用各种函数实现，只返回某一天的数据，或格式经过整理的数据等等。

　　视图与的工作方式与表很像，但是还是有一些不同之处。在对视图执行INSERT,UPDATE以及DELETE语句时，要记住以下一些内容

　　如果视图包含连接，在大多数情况下，除非使用INSTEAD OF触发器，否则不能对数据执行INSERT或DELETE操作。有时(只要只更新来自单个表的列)，UPDATE可以　　不使用INSTEAD OF触发器来工作，但是这需要一些规划，否则很快会遇到问题。
　　如果视图仅仅引用单个表，那么在表中的所有必需字段都在视图中或者有默认值的情况下，可以通过使用视图而不用INSTEAD OF触发器来对数据执行INSERT操作。几时对于单个表的视图来说，如果有一列没有出现在不具有默认值的视图中，那么如果想要允许INSERT操作，必须使用INSTEAD OF触发器。
　　可在有限范围内限制是否可以在视图中插入或更新内容。

　　1、用连接的数据处理视图变化

　　如果视图有多个表，那么在很多情况下使用视图来修改数据是不允许的-除非使用INSEEAD OF触发器。

　　2、必需字段必须在视图中出现或者具有默认值

　　在默认情况下，如果使用视图来插入数据(内部查询必定有一个单个表的SELECT操作或者至少必须限制插入只影响到一个表，并且使所有必需的列出现)，那么必须能为所有的必需字段(不允许为空的字段)提供一些值。不过要认识到任何没有数据以及不接受NULL数值的列需要出现在视图中。以通过视图来执行INSERT操作。

　　3、通过WITH CHECK OPTION限制插入到视图中的内容

　　WITH CHECK OPTION是SQL Server中鲜为人知的功能之一。规则很简单-为了通过使用视图更新或者插入数据，结果行必须符合要求以显示在视图结果中。

　　示例：

CREATE VIEW PortlandAreaAddress_vw

AS

SELECT AddressId,AddressLine1,City,StateProviceID,PostalCode,ModifiedDate

FROM Person.Address

WHERE PostalCode LIKE '970%' OR PostalCode LIKE '971%' OR PostalCode LIKE '972%' OR PostalCode LIKE '986[6-9]%'

WITH CHECK OPTION

　　如果尝试通过对以上视图来更新一行，将PostalCode设置为不是以97或98开头：

　　UPDATE PortlandAreaAddress_vw

　　SET PostalCode = ''　　--不以97 98 开头

　　WHERE AddressID = 22

　　SQL Server会报错

　　视图进行的插入或更新已失败，原因是目标视图或目标视图所跨越的某一视图指定了WITH CHECK OPTION,而该操作的一个或多个结果行又不符合CHECK OPTION约束。

　　语句已经终止。

　　WHERE子句过滤视图的内容，以便只显示970、971、972或9866~9869之间的邮编，并且WITH CHECK OPTION说明任何INSERT或者UPDATE语句必须满足WHERE之间的条件(而33333这个邮编不满足这个条件)。

　　以上语句如果用表执行是正常的。

三、使用T-SQL编辑视图

　　在使用T-SQL编辑视图时要记住，这是在完全替换现有的视图。使用ALTER VIEW语句和CREATE VIEW语句的区别主要有以下几点：

　　ALTER VIEW期望找到一个已有的视图，而CREATE则不是。
　　ALTER VIEW保留了视图上已经建立的任何权限。
　　ALTER VIEW保留了任何依赖信息。

　　要切记第二点，如果删除了视图，然后在CREATE，那么其效果与ALTER VIEW语句基本一样，只是权限信息要全部重建。

　　删除视图的语法如下：

DROP VIEW <view name>,[<view name>,[...n]]

四、审查：显示现有代码

　　有两种得到实际视图定义的方法：

　　sp_helptext
　　sys.modules元数据函数

　　sp_helptext示例：

EXEC sp_helptext PortlandAreaAddress_vw

　　SQL Server返回创建视图的代码

　　sys.modules示例：

　　使用该函数的主要问题在于所有对象都用对象ID编码，对象ID是SQL Server跟踪事物的内部方法，他们是整型值而不是用于对象的名称。可以通过使用OBJECT_ID()函数来避开这个问题。

　　SELECT *

　　FROM sys.sql_modules

　　WHERE　　object_id = OBJECT_ID('dbo.PortlandAreaAddress_vw')

　　再次得到SQL Server创建视图的代码，和sp_helptext一样。

五、保护代码：加密视图

　　加密视图所有必须做的是使用WITH ENCRYPTION选项。技巧：

　　WITH ENCRYPTION跟在视图名称之后，但在AS关键字之前。
　　WITH ENCRYPTION不使用OPTION关键字

　　如果使用ALTER VIEW语句，那么意味着除了访问权限外，完全替换了现有的视图。那么意味着加密方法也被替换了。如果想对被改变的视图加密，那么必须在ALTER VIEW语句中使用WITH ENCRYPTION子句。　　

　　示例：

　　ALTER VIEW CustomerOrders_vw

　　WITH ENCRYPTION

　　AS

　　SELECT.....

　　现在再对以上视图查看信息：

　　EXEC sp_helptext CustomerOrders_vw

　　SQL Server提示如下：

　　对象'CustomerOrders_vw'的文本已加密。

　　注意：在使用WITH ENCRYPTION之前，必须先备份好源代码，一旦源代码被加密，就没有办法恢复。如果没有在其他地方存储代码，又需要改变代码只有重新编写。

六、模式绑定

　　模式绑定实际上就是将视图所依赖的事物(表或者其他视图)“绑定”到视图。起重要意义在于除非首先删除模式绑定的视图，否则没有人可以修改那些对象(CREATE、ALTER)。

　　其作用如下：

　　可以防止修改底层对象时使用视图"孤立"。如删除了表，但没考虑到视图。
　　为了允许创建索引视图：如果想要在视图上创建索引，那么必须使用SCHEMABINDING选项来创建视图。
　　如果要创建一个模式绑定的用户自定义函数来引用视图，那么视图也必须是绑定的。

　　视图注意事项总结：

　　避免基于视图构建视图-而应该将来自第一个视图的合适的查询信息应用到新的视图中。
　　记住使用WITH CHECK OPTION的视图提供了一些普通CHECK约束所不具备的灵活性
　　如果不希望其他人能看见你的源代码，就加密视图，但要记得备份未加密的代码；因为加密后就不能恢复为加密的代码了。
　　除了权限之外，使用ALTER VIEW意味着完全替换了现有的视图。这意味着如果要使被修改的视图里的加密和限制仍然是有效的，那么必须在ALTER语句里包含WITH ENCRYPTION和WITH CHECK OPTION子句。
　　使用sel_helptext显示视图支持的代码-避免使用系统表。
　　最小化用于生产查询的视图的用户-因为他们增加了额外的系统开销并且危害性能。

　　视图一般用于以下情况：