SQL Server 2005 视图

（《SQL Server 2005 编程入门经典》 第10章）

视图的核心实际上仅仅是一个存储的查询。重要的是可以将来自于基本表（或者其他视图）的数据混合以及匹配，以创建在大多数方面上像另一个基本表那样起作用的对象。

10.1 简单的视图

视图语法的基本形式：

CREATE VIEW <view name>

AS

<select statement>

扩展的语法如下：

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

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

AS

<select statement>

WITH CHECK OPTION

示例——创建简单的视图：

USE Accounting

GO

 

CREATE VIEW CustomerPhoneList_vw

AS

    SELECT CustomerName, Contact, Phone

    FROM Customers

对Employees表创建视图，隐藏隐私信息：

USE Accounting

GO

 

CREATE VIEW Employees_vw

AS

SELECT EmployeeID,

    FirstName,

    MiddleInitial,

    LastName,

    Title,

    HireDate,

    ManagerEmpID,

    Department

FROM Employees

作为过滤器的视图

在创建视图时使用WHERE字句来过滤查询中的结果。

示例：

CREATE VIEW CurrentEmployees_vw

AS

SELECT EmployeeID,

    FirstName,

    MiddleInitial,

    LastName,

    Title,

    HireDate,

    TerminationDate,

    ManagerEmpID,

    Department

FROM Employees

WHERE TerminationDate IS NULL

10.2 更加复杂的视图

在简单视图的基础上添加连接、概要、以及一些列的重命名。

示例——查询订单、零件和消费者信息：

USE Northwind

GO

 

CREATE VIEW CustomerOrders_vw

AS

SELECT cu.CompanyName,

    o.OrderID,

    o.OrderDate,

    od.ProductID,

    p.ProductName,

    od.Quantity,

    od.UnitPrice,

    od.Quantity * od.UnitPrice AS ExtendedPrice

FROM Customers AS cu

INNER JOIN Orders AS o

    ON cu.CustomerID = o.CustomerID

INNER JOIN OrderDetails AS od

    ON o.OrderID = od.OrderID

INNER JOIN Products AS p

    ON od.ProductID = p.ProductID

数据库的用户可以方便地查询到消费者的订单信息，而不需要关心四个表的连接：

SELECT CompanyName, ExtendedPrice

FROM CustomerOrders_vw

WHERE OrderDate = '1996-9-3'

另外，可以使得查询更加有目的性，例如查询昨天的订单：

USE Northwind

GO

 

CREATE VIEW YesterdaysOrders_vw

AS

SELECT cu.CompanyName,

    o.OrderID,

    o.OrderDate,

    od.ProductID,

    p.ProductName,

    od.Quantity,

    od.UnitPrice,

    od.Quantity * od.UnitPrice AS ExtendedPrice

FROM Customers AS cu

INNER JOIN Orders AS o

    ON cu.CustomerID = o.CustomerID

INNER JOIN OrderDetails AS od

    ON o.OrderID = od.OrderID

INNER JOIN Products AS p

    ON od.ProductID = p.ProductID

WHERE CONVERT(VARCHAR(12), o.OrderDate, 101) =

    CONVERT(VARCHAR(12), DATEADD(day, -1, GETDATE()), 101)

DATEADD和CONVERT函数

如上例所示，DATEADD(day, -1, GETDATE())返回昨天的DATETIME类型的值。DATEADD()函数用来做日期的加减计算，其中第一个参数指定加减的单位为天，第二个参数为相对指定日期的便宜量，第三个参数为指定日期。

CONVERT()函数的用法之前已经介绍。

触发器之前，使用视图改变数据

从使用的观点来看，视图非常像表那样地工作。但是，现在来看看一些不同之处。

你可以成功地对视图执行INSERT、UPDATE以及DELETE语句。但是，当通过视图改变数据的时候，有一些内容需要注意：

  • 如果视图包含连接，在大多数情况下，除非使用INSTEAD OF触发器，否则不能对数据执行INSERT或者DELETE操作。在一些情况下，UPDATE可以不使用INSTEAD OF触发器来工作，但是仅限于个别情况。
  • 如果视图仅仅引用单个的表，那么在表中所有需要的字段都在视图中或者有默认值得情况下，可以直接执行INSERT操作。否则，需要使用INSTEAD OF触发器。
  • 在一个有限的范围内，可以限制在视图中可以插入和更新的内容以及不可以插入和更新的内容。

 

1. 以连接的数据方式处理视图的变化

如果视图包含连接，在大多数情况下，除非使用INSTEAD OF触发器，否则不能对数据执行INSERT或者DELETE操作。

 

2. 必要的字段必须在视图中出现或者具有默认值

 

3. 约束插入到视图中的内容——WITH CHECK OPTION

WITH CHECK OPTION的规则很简单——为了通过使用视图更新或者插入数据，结果行必须符合要求以出现在视图结果中。

10.3 使用T-SQL编辑视图

注意，ALTER语句会完全替换现有的视图。使用ALTER VIEW语句和先删除后新建视图的区别在于：

  • ALTER VIEW期望找到一个已存在的视图，而CREATE则相反。
  • ALTER VIEW保留了视图上任何已经建立的许可。
  • ALTER VIEW保留了任何依赖信息。

10.4 删除视图

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

10.6 审核：显示现存的代码

两个可以得到视图实际定义的方法：

  • sp_helptext
  • syscomments系统表

使用sp_helptext更可取，因为新版本的SQL Server也许会移动视图代码的位置。

sp_helptext的简单语法规则如下：

EXEC sp_helptext <view name>

10.7 保护代码：加密视图

使用WITH ENCRYPTION选项加密视图，注意和WITH CHECK OPTION出现的位置不同：

ALTER VIEW CustomerOrders_vw

WITH ENCRYPTION

AS

SELECT cu.COmpanyName,

    o.OrderDate,

    od.ProductID,

    p.ProductName,

    od.Quantity,

    od.UnitPrice,

    od.Quantity * od.UnitPrice AS ExtendedPrice

FROM Customers AS cu

INNER JOIN Orders AS o

    ON cu.CustomerID = o.CustomerID

INNER JOIN OrderDetails AS od

    ON o.OrderID = od.OrderID

INNER JOIN Products AS p

    ON od.ProductID = p.ProductID

现在在CustomerOrders_vw上执行sp_helptext：

EXEC sp_helptext CustomerOrders_vw

SQL Server会告诉我们它不能这么做：

The object comments have been encrypted.

注意，一旦源代码被加密了，就没有办法恢复。

10.8 关于模式绑定

模式绑定本质上获得视图依赖的事物，并且将它们"绑定"到视图上。除非首先删除模式绑定视图，否则没有人可以修改那些对象（CREATE、ALTER）。

10.9 使用VIEW_METADATA使视图看起来类似于表

没有这个选项，传回到客户端API的元数据是视图所依赖的基本表的元数据。

10.10 带索引的（具体化的）视图

带索引的视图在本质上是已经具有"被具体化"成为簇索引形式的一组独特的数值的视图。这个优点是它将视图后面信息聚集到一起从而提供了一个非常快的查找方法。在第一个索引之后，通过使用来自第一个索引的簇键作为参考点，SQL Server还能在视图上建立额外的索引。

对于何时可以或者不可以在视图上建立索引的一些限制：

  • 视图必须使用SCHEMABINDING选项。
  • 如果视图引用了任何用户定义的函数，那么这些函数必须也是模式绑定的。
  • 视图不可以引用任何其他的视图——只能引用表和UDF。
  • 所有的视图中引用的表和UDF必须使用两部分的命名惯例（不允许三部分和四部分的名称）而且也必须具有和视图相同的所有者。
  • 视图和视图引用的所有对象必须在相同的数据库里。
  • 在创建视图和所有内在的表的时候，必须打开ANSI_NULLS以及QUOTED_IDENTIFIER选项。
  • 任何视图引用的函数必须是确定的。

示例：

ALTER VIEW CustomerOrders_vw

WITH SCHEMABINGDING

AS

SELECT cu.CompanyName,

    o.OrderID,

    o.OrderDate,

    od.ProductID,

    p.ProductName,

    od.Quantity,

    od.UnitPrice

FROM dbo.Customers AS cu

INNER JOIN dbo.Orders AS o

    ON cu.CustomerID = o.CustomerID

INNER JOIN dbo.OrderDetails AS od

    ON o.OrderID = od.OrderID

INNER JOIN dbo.Products AS p

    ON od.ProductID = p.ProductID

这样就创建了一个可以被索引的视图。在视图上创建的第一个索引必须既是集群的又是独特的：

CREATE UNIQUE CLUSTERED INDEX ivCustomerOrders

ON CustomerOrders_vw(CompanyName, OrderID, ProductID)

posted @ 2013-04-30 12:30  qwertWZ  阅读(1762)  评论(0)  编辑  收藏  举报