细说SQL Server中的视图

编辑: admin 分类: .net 发布时间: 2022-03-10 来源:互联网

1,什么是视图?

2,为什么要用视图;

3,视图中的ORDER BY;

4,刷新视图;

5,更新视图;

6,视图选项;

7,索引视图;

1.什么是视图

  视图是由一个查询所定义的虚拟表,它与物理表不同的是,视图中的数据没有物理表现形式,除非你为其创建一个索引;如果查询一个没有索引的视图,Sql Server实际访问的是基础表。

  如果你要创建一个视图,为其指定一个名称和查询即可。Sql Server只保存视图的元数据,用户描述这个对象,以及它所包含的列,安全,依赖等。当你查询视图时,无论是获取数据还是更新数据,Sql server都用视图的定义来访问基础表;

  视图在我们日常操作也扮演着许多重要的角色,比如可以利用视图访问经过筛选和处理的数据,而不是直接访问基础表,以及在一定程度上也保护了基础表。

我们在创建视图的时候,也要遵守三个规则:

不能在视图定义中指定ORDER BY ,除非定义中包含Top或For Xml 说明;
所有的列必须有列名;
这些所有的列名必须唯一;

  对于视图表中在没有top或for xml说明的情况下,不能有Order by 语句,这是因为视图被认为是一个表,表是一个逻辑的实体,它的行是没有顺序的。视图中所有列必须有列名,且唯一的情况我想大家都理解;

下面的sql语句表示创建一个简单的视图:

复制代码 代码如下:CREATE VIEW dbo.V1
AS
SELECT CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)

2.为什么要使用视图(更新)

  SqlServer既然给我们提供这样的对象,就一定有它的作用。而我们在使用视图上,要么用的过多,要么用的不够,所以一部分人建议不要用视图,而一部分人又建议少用。那我们听谁的呢?

  其实我们要是掌握了用视图的目的,就能在正确的地方,用正确的视图;那么视图能给我们解决什么问题呢?

1),为最终用户减少数据库呈现的复杂性。客户端只要对视图写简单的代码,就能返回我所需要的数据,一些复杂的逻辑操作,放在了视图中来完成;

2),防止敏感的列被选中,同时仍然提供对其他重要数据的访问;

3),对视图添加一些额外的索引,来提高查询的效率;

  视图其实没有改变任何事情,只是对访问的数据进行了某种形式的筛选。考虑一下视图的作用,你应该能看到视图的概念如何为缺乏经验的用户简化数据(只显示他们关心的数据),或者不给予用户访问基础表的
权利,但授予他们访问不包含敏感数据视图的权力,从而提前隐藏敏感数据。

  要知道,在默认的情况下,视图没有做什么特殊的事情。视图就好象一个查询那样从命令行运行(这里不存在任何形式的预先优化),这意味着在数据请求和将被交付的数据之间多加了一层开销。这表明视图绝不可能像
只是直接运行底层SELECT语句那样快。不过,视图存在有一个原因--这就是它的安全性或为用户所做的简化,在你的需要和开销之间权衡,找到最适合特定情况的解决方案。

3.视图中的ORDER BY

视图表示一个逻辑实体,它与表非常类似;

如果我们在上面的创建的sql语句中加一个Order BY 语句,看看有什么效果:

复制代码 代码如下:ALTER VIEW dbo.V1
AS
SELECT CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID) 
ORDER BY CompanyName

运行该语句将会失败,回收到以下的提示:

Msg 1033, Level 15, State 1, Procedure V1, Line 5
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

根据提示,ORDER By 也不是不能用,只有指定了Top或for xml语句后,ORDER BY 才能使用,如:

复制代码 代码如下:ALTER VIEW dbo.V1
AS
SELECT TOP(10) CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID) 
ORDER BY CompanyName

但是,并不建议在视图中使用ORDER BY ,这是因为视图表示一个表,而对于表来说,是不会有排序的;所以建议在查询视图的时候,用ORDER BY;

SQL Server2005联机丛书有一段这样的描述:“在视图、内联函数、派生表或子查询的定义中使用ORDER BY 字句,子句只能用户确定TOP子句返回的行。ORDER BY 不保证在查询这些构造时得到有序结果,除非在查询本省也指定了ORDER BY.”

4.刷新视图

我在上面说过,视图会保存元数据,列,安全,以及依赖等信息,如果我们把基础表的架构更改了,并不会直接反映到视图上来;更改架构后,使用sp_refreshview存储过程刷新视图的元数据是一个好习惯;

比如我们创建了一个表T1和一个T1的视图V1,然后更改T1,再看V1的结果:

首先创建表T1:

复制代码 代码如下:IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
CREATE TABLE T1(col1 INT,col2 INT)
INSERT INTO T1(col1,col2) VALUES(1,2)
GO

然后创建T1的视图V1:

复制代码 代码如下:CREATE VIEW V1
AS
SELECT * FROM T1

在现实实践中,要避免在视图中的SELECT语句中使用*,在这只是演示。如果你查询视图V1就会出现以下结果:
http://img.hwidc.net/net-1/201652395552258.png

接下来,我们对表T1添加一列col3:

复制代码 代码如下:ALTER TABLE T1 ADD col3 INT

然后再次查询视图V1,你想这时的结果是三列呢,还是而列呢?答案是二列。T1架构的改变,并没有影响到视图的元数据中,这时候,如果我们要刷新一下视图V1,我们就可以用:EXEC sp_refreshview V1 命令, 再次查询,V1的结果就是三列了。

5.更新视图

  视图是一个虚拟表,我们在查询视图的时候,实际上是对基础表的查询。视图不仅可以作为SELECT查询的目标,也可以作为修改语句的目标。当然,当你修改视图的时候,修改的时候是对基础表的修改,它就好像是一个代理。当然,如果不允许直接修改基础表,只允许修改视图,就可以限制你要公开的数据。这样,就可以对你的数据起到一定的保护作用,不过这种限制的时候很少。

那么在更新视图的时候,有哪些限制条件呢?

1),只要视图有一列不能隐式获取值,你就不能想视图中插入数据,如果列允许NULL、有默认值或者IDETITY属性,则说明它可以隐式获取值;

2),如果视图包含联结,UPDATE或INSERT语句只能影响联结的一端。也就是说,INSERT或UPDATE语句必须定义目标列列表,这些列只能数据联结的一端。你不能从由联结查询定义的视图中删除数据;

3),不能修改作为计算结果的列。如:标量表达式和聚合函数,SqlServer不会尝试改变数据库引擎的计算结果;

4),如果在创建或修改视图时指定了WITH CHECK OPTION选项,与视图的查询筛选器有冲突的INSERT或UPDATE语句将被拒绝;我在“视图选项”一节详细讲解一下。

如果视图上定义了INSERT OF触发器,则违反这些限制的数据修改语句可以被执行。在INSERT OF触发器中你可以用自己的代码替换原始修改;

当你允许对有联结查询定义的视图执行修改的时候,一定要谨慎,比如一对多的关系,如果你根据“多”的某一索引值修改对应“一”端某列值的记录,那么结果就可想而知;

6.视图选项

当你创建或修改视图时,可以指定一些选项,这些选项用户控制视图的行为和功能。

ENCRYPTION、SCHEMABINDING和VIEW_METADATA选项在视图头指定,CHECK OPTION选项则在查询之后指定;

如:       

复制代码 代码如下:CREATE VIEW v2
WITH ENCRYPTION,SCHEMABINDING,VIEW_METADATA
AS
SELECT OrderID FROM dbo.Orders
WITH CHECK OPTION

1),ENCRYPTION

如果你在构建任何类型的商业软件的时候,需要对视图进行加密的时候,这是一个不错的选项。

如果未指定ENCRYPTION选项,SQLSERVEr则以纯文本的形式保存用户定义的语句,如果指定了ENCRYPTION选项,【本文由:http://www.1234xp.com/cdn.html 提供,感谢支持】对象的文本则会被混淆。

SQLSERVER提供了一个系统函数sp_helptext查看视图的文本,如果应用的ENCRYPTION选项,则会得到“The text for object ‘xx' is encrypted”语句;

注:在加密之前一定要先备份你所要加密的视图,一旦加密,就不能回头。

2),SCHEMABINDING

如果你使用SCHEMABINDING选项创建视图,SQLSERVER将不允许删除基础表或修改被引用的列,防止在对底层对象修改时,使视图变得“孤立”,如果某人没有注意到你的视图,执行了DROP,删除视图引用的列或其他一些操作,那就很糟糕。如果使用SCHEMABINDING选项,则就可以避免这种情况。

如果想在视图上创建索引,则必须使用SCHMABINDING选项;

如果应用这个选项,则定义视图的时候要注意两点:

  1,所有对象必须由两部分构成的名称,如:应该使用dbo.Orders 而不能是Orders

  2,不能在SELECT列表使用*,所有的列名必须指定一个名称;

3),CHECK OPTION

使用WITH CHECK OPTION 创建的视图能防止与视图查询筛选器有冲突的INSERT或UPDATE语句。没有该选项,视图可以接受不符合查询筛选器的修改。比如:

我们在Northwind数据库中创建一个CustomWithOrder的视图,现在还没有添加WITH CHECK OPTION选项


复制代码 代码如下:CREATE VIEW CustomerWithOrder
WITH VIEW_METADATA
AS
SELECT Customers.CustomerID,Customers.CompanyName FROM Customers
WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)


该视图的作用是查询所有有订单的客户的id和公司名,接下来我们向视图中插入一条不存在的用户id,和公司名:

复制代码 代码如下:INSERT INTO CustomerWithOrder(CustomerID,CompanyName) VALUES('MYSQL','MyReed')

执行成功,然后在查询这个CustomerWithOrder视图,很明显,查询不到CustomerID为'MySQL'的用户,因为视图只包含发生过订单的用户;如果你直接查询Customers表,就会发现这个新增的用户信息了。

接下来对CustomerWithOrder视图添加WITH CHECK OPTION 选项

复制代码 代码如下:ALTER VIEW CustomerWithOrder
WITH VIEW_METADATA
AS
SELECT Customers.CustomerID,Customers.CompanyName FROM Customers
WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)
WITH CHECK OPTION

然后再执行下面的语句:

复制代码 代码如下:INSERT INTO CustomerWithOrder(CustomerID,CompanyName) VALUES('ILSQL','MyReed')

你会收到以下错误:

Msg 550, Level 16, State 1, Line 2
试图进行的插入或更新已失败,原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION,而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。
语句已终止。 

4),VIEW_METADATA

  该选项的作用是,让视图看起来更像一个真正的表。不使用该选项,返回给客户端的api的元数据将是视图所依赖的基础表的数据;

  如果客户端希望SqlServer发送视图的元数据信息,而不是基础表的元数据时,可以在创建或修改视图时指定此选项;是不是听的很费劲,听我慢慢说;

  假设用户拥有对视图的操作权限,而没有对基础表操作的权限,那么用户对视图执行一些操作,如果指定了VIEW_METADATA选项,那么该语句将会违背安全而失败,因为只要指定了VIEW_METADATA那么返回给客户端就是视图的元数据,而不是基础表的元数据。另一方面,如果用户尝试通过视图修改数据,而该操作又与视图上定义的CHECK OPTION有冲突,这种操作只有直接提交到基础表,才有可能成功。

  SqlServer中就有这样的工具,在SqlServer2000中,企业管理器就是,如果我们向视图中插入一条记录,比如向在有WITH CHECK OPTION选项的CustomerWithOrder视图中插入一个任意的消费者无论存在与否,并打开跟踪企业管理器提交到Sql Server中的操作,你会发现操作实际把基础表作为目标提交的,及时他违背CHECK OPTION,也会成功。而在Sql Server2005中的SSMS中,就会不同了,如果在“Modify”视图中,手动插入一条记录,就可以成功,说明虽然指定了VIEW_METADATA和CHECK OPTION选项,它还是插入到了基础表中了,可以跟踪一下提交到Sqlserver的操作(用Sql server Profiler)。但如果在由“Open View”产生的面板中进行操作,将会失败,提示:
http://img.hwidc.net/net-1/201652395601172.png

  可以再次跟踪提交到Sql server的操作,就能看到,他提交到目标对象是视图;

  还是那句话:如果客户端希望SqlServer发送视图的元数据信息,而不是基础表的元数据时,可以在创建或修改视图时指定此选项

  这次明白了吗?

  我个人总结,只要有VIEW_METADATA选项就有必要加上CHECK OPTION选项,而SCHEMABINDING选项,最好也要加上,防止你的视图“孤立”,而在索引视图中SCHEMABINDING选项是必须加上的。

7.索引视图

  如果没有索引,视图中的数据不会有任何物理表现形似,如果加上索引,则就把视图中的数据物理化了,SqlServer会在修改基础表时同步索引视图。但你不能直接同步视图内容。

  我们知道在表上创建索引,能提高性能,相同,在视图也是一样,在视图上创建的第一个索引必须是唯一聚集索引,之后才可以创建其他的非聚集索引。

  索引视图必须使用SCHEMABINDING选项,并且不能引用其他视图,只能引用基础表和UDF,而基础表和UDF必须使用两部分命名约定来引用(参见5.视图选项中的SCHEMABINDING选项)。

  除了性能,你可能还会因为其他原因使用索引视图,比如在一张基础表中有一列我们要强制该列中已知值的唯一性,但是允许出现多次的NULL值,我们怎么办呢,我们首先想到的可能是用UNIQUE约束,但是UNIQUE会认为两个NULL值相等,那么这个不得不放弃了,那还有什么办法呢?

  其实我们可以利用一个索引视图来完成这个任务,利用索引视图筛选所有非NULL的数据,那么这种索引将防止重复的已知值进入基础表,但允许多个NULL,因为NULL不是唯一索引的一部分,我们在向基础表中插入数据的时候,就利用索引视图的UNIQUE来限制我们的数据,来达到某列中强制已知值的唯一性的目的;

我们可以演示一下,首先创建一个基础表T2和一个索引视图V2:

复制代码 代码如下:CREATE TABLE T2(col1 INT,col2 NVARCHAR(50))
CREATE VIEW V2
WITH SCHEMABINDING
AS
SELECT col1 FROM dbo.T2 WHERE col1 IS NOT NULL;
CREATE UNIQUE CLUSTERED INDEX idx_col1 ON dbo.V2(col1);

然后我们向T2表中插入以下数据:

复制代码 代码如下:INSERT INTO t2(col1,col2) VALUES(1,'2')
INSERT INTO t2(col1,col2) VALUES(1,'3')
INSERT INTO t2(col1,col2) VALUES(null,'4')
INSERT INTO t2(col1,col2) VALUES(null,'5')

那么以上4条INSERT哪条会失败呢?答案是2。最后让我们SELECT 一下基础表T2,看实现我们开始那个要求了吗?

复制代码 代码如下:SELECT * FROM t2

执行:
http://img.hwidc.net/net-1/201652395613188.png

【文章原创作者:美国站群多ip服务器 http://www.558idc.com/mgzq.html
更多阅读