通往T-SQL的阶梯:超越基本的4级:使用视图简化您的查询

通往T-SQL的阶梯:超越基本的4级:使用视图简化您的查询

 作者Gregory Larsen, 2016/07/22(第一次出版:2014/03/19)

The Series

这篇文章是楼梯系列的一部分:T-SQL的阶梯:超越基础。

从他的楼梯到T-SQL DML，格雷戈里·拉森涵盖了T-SQL语言的更高级的方面，比如子查询。

在这个阶梯级别，我将讨论如何使用数据库视图来简化Transact-SQL (T-SQL)代码。

通过了解如何使用视图，您将能够更好地支持编写T-SQL代码以满足复杂的业务需求。

在本文中，我将讨论数据库视图是什么，然后提供一些示例来帮助您理解如何使用视图来实现不同的编码场景。

什么是视图?

视图是由行和列组成的虚拟表。

数据可以来自单个表，也可以来自多个表。

查询一个视图，就像正常的表一样。

视图是用CREATE view语句创建的，并存储在创建它的数据库中。

下面是一些视图可以帮助您编写代码逻辑的情况:

l 不希望将表的所有列公开给查询表的用户。

l 我们的数据库模式设计非常复杂，因此您可以构建视图来简化用户访问。

l 你想改变你的数据库模式设计,但要保持向后兼容现有代码不需要重写。

要更好地理解如何使用视图，最好的方法是通过一些使用视图的例子来满足不同的业务需求。

样本数据 

为了演示视图如何工作以及如何简化T-SQL代码，我需要一些测试数据来支持这些视图。

与其创建我自己的测试数据，我的大多数示例将使用AdventureWorks2008R2数据库。

如果您想在您的环境中跟踪和运行我的示例，那么您可以从这里下载AdventureWorks2008R2数据库: http://msftdbprodsamples.codeplex.com/releases/view/93587 

使用视图简化SQL代码的示例 

通过使用视图，您可以返回列的列表，这些列是表列的子集，一组来自多个表的列，一组基于某些标准的约束列，或者一些其他不同的需求。

在本节中，我将为您提供一些不同的使用视图来满足不同业务需求的示例。

对于我的第一个示例，我们假设您有一个要求，即不要将单个表中的所有列都呈现给应用程序或临时查询。

对于本例，我们假设您只想从HumanResource返回非个人信息。

Employee表如清单1所示。

(注意这张表已经在AdventureWorks2008R2数据库中出现了;

这里列出的定义仅供参考。

CREATE TABLE [HumanResources].[Employee](

[BusinessEntityID] [int] NOT NULL,

[NationalIDNumber] [nvarchar](15) NOT NULL,

[LoginID] [nvarchar](256) NOT NULL,

[OrganizationNode] [hierarchyid] NULL,

[OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),

[JobTitle] [nvarchar](50) NOT NULL,

[BirthDate] [date] NOT NULL,

[MaritalStatus] [nchar](1) NOT NULL,

[Gender] [nchar](1) NOT NULL,

[HireDate] [date] NOT NULL,

[SalariedFlag] [dbo].[Flag] NOT NULL,

[VacationHours] [smallint] NOT NULL,

[SickLeaveHours] [smallint] NOT NULL,

[CurrentFlag] [dbo].[Flag] NOT NULL,

[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

[ModifiedDate] [datetime] NOT NULL);

清单1:人力资源的表定义。

员工表

应用程序和临时用户需要的非个人信息包括以下列:BusinessEntityId、NationalIDNumber、LoginID、OrganizationNode、OrganizationLevel、JobTitle和HireDate。

为了创建一个视图，它只返回来自人力资源的列的子集。

Employee表我将使用清单2中的代码。

CREATE VIEW [HumanResources].[EmployeeInfo]

AS

SELECT [BusinessEntityID]

      ,[NationalIDNumber]  

      ,[LoginID]  

      ,[OrganizationNode]  

      ,[OrganizationLevel]

      ,[JobTitle]

      ,[HireDate]  

      ,[CurrentFlag]  

    FROM [HumanResources].[Employee];

清单2:从人力资源创建非个人信息视图的脚本。

员工表

通过查看清单2中的CREATE VIEW语句，您可以看到它非常简单。

视图的代码只是一个简单的SELECT语句，其中包含了我希望在选择条件中公开的视图。

一旦我创建了这个视图，我就可以像普通表一样查询它。

清单3中的脚本演示了两个不同的SELECT语句，它们从人力资源中检索数据。

Employee表使用清单2中的代码创建的视图。

SELECT * FROM [HumanResources].[EmployeeInfo];

SELECT * FROM [HumanResources].[EmployeeInfo]

WHERE JobTitle like '%Manager%';

清单 3: 使用视图返回数据的两个 SELECT 语句

通过查看清单3中的代码, 您可以看到在从子句之后引用的对象是我在清单2中创建的视图的名称。我引用了选择语句中的视图, 就像引用表一样。清单3中的第一个选择语句返回了人力资源表中的所有行, 但只返回了 "我的视图" 中选择子句中的非个人列。清单3中的第二个选择语句演示了如何使用语句约束返回的行, 就像引用表时一样。

有时, 您的数据库设计相当复杂, 这可能会使生成查询更复杂, 从而访问数据库中所需的数据。这些复杂的设计可能需要复杂的多表连接才能实际返回数据。这是一个视图可以帮助的地方。通过使用视图, 可以在视图中生成复杂的多表联接, 然后使用视图查询数据通过这样做, 您可以简化代码以查询数据库, 并在视图中隐藏数据库设计的复杂性为了演示这一点, 我创建了一个视图清单 4, 它检索多个表中包含的销售订单数据。

CREATE VIEW SalesOrderCombined2005

AS

SELECT

       OH.SalesOrderID

      ,OH.OrderDate

      ,OH.ShipDAte

      ,ST.Name AS TerritoryName

      ,BTA.City AS BillToCity

      ,STA.City AS ShiptToCity

      ,OH.TotalDue

FROM Sales.SalesOrderHeader OH

       JOIN Sales.SalesTerritory ST

       ON OH.TerritoryID = ST.TerritoryID

       JOIN Person.Address BTA

       ON OH.BillToAddressID = BTA.AddressID

       JOIN Person.Address STA

       ON OH.ShipToAddressID = STA.AddressID

WHERE YEAR(OH.OrderDate) = 2005;

清单 4: 包含多表联接的视图

清单4中的SalesOrderCombined2005视图将多个表连接在一起, 并只返回这些表中的列的子集此外, 视图还有在那里子句。哪里子句仅在与2005年中放置的销售订单相关的情况下返回数据。此视图无需了解如何使用不同的键列将多个表联接在一起。通过对SalesOrderCombined2005视图执行选择语句, 所有这些联接都是完成的, 而不必在选择语句中引用它们。通过在视图中放置复杂的连接语法, 可以简化代码, 以便从复杂的数据库设计中检索数据。其他这些类型的视图确保对数据库的所有查询都使用相同的联接语法。通过提供和使用视图来查询您的数据, 您可以消除连接条件被错误写入的可能性

有时, 您希望随着时间的推移开发数据库设计, 但不希望中断现有代码。视图可以处理满足此业务需求。为了演示这一点, 请查看清单5中的代码

--- Begin Old Schema

CREATE TABLE DateDimOld (

ID INT IDENTITY,

DT DATE,

DOWTitle varchar(10));

GO

-- Populate DateDimOld

INSERT INTO DateDimOld(DT, DOWTitle) VALUES

  ('12/1/2013',DATENAME(DW,'12/1/2013')),

  ('12/2/2013',DATENAME(DW,'12/2/2013')),

  ('12/3/2013',DATENAME(DW,'12/3/2013'));

GO

SELECT * FROM DateDimOld;

GO

--- End Old Schema 

--  Begin New Schema 

CREATE TABLE DOWTitle (

DowTitleID INT IDENTITY PRIMARY KEY,

DOWTitle VARCHAR(10));

GO

CREATE TABLE DateDimNew (

ID INT IDENTITY,

DT DATE,

DOWTitleID INT);

GO

ALTER TABLE DateDimNew  WITH CHECK ADD  CONSTRAINT [FK_DateDimNew_DOWTitle_DOWTitleID] FOREIGN KEY(DOWTitleID)

REFERENCES DOWTitle (DOWTitleID)

GO

-- Populate DOWTitle

INSERT INTO DOWTitle (DOWTitle) VALUES

  (DATENAME(DW,'12/1/2013')),

  (DATENAME(DW,'12/2/2013')),

  (DATENAME(DW,'12/3/2013'));

GO

-- Populate DateDimNew

INSERT INTO DateDimNew (DT,DOWTitleID) VALUES

  ('12/1/2013', 1),

  ('12/2/2013', 2),

  ('12/3/2013', 3);

GO

-- Remove Old Schema

DROP TABLE DateDimOld

GO

-- Create view to similate Old Schema

CREATE VIEW DateDimOld AS

SELECT DDN.ID, DDN.DT, DOWT.DOWTitle

      FROM DateDimNew AS DDN

      JOIN DOWTitle AS DOWT

      ON DDN.DOWTitleID = DOWT.DowTitleID;

GO

-- Show how VIEW and Simulate Old Schema

SELECT * FROM DateDimOld

-- End New Schema

清单 5: 旧的和新的架构结构

通过查看清单5中的代码, 您可以看到有两个不同的代码段在我定义的第一个部分中, 填充并显示了一个名为DateDimOld的单个表的旧架构中的一些数据。此表包含名为DT的日期列, 以及名为DOWTitle的周列的一天, 并将这些列与ID列关联。在第二节中, 我定义了一个新架构以替换第一节中的旧架构在第二节中, 我创建了两个表第一个表名为DOWTitle, 其中包含DOWTitle和DOWTitleID 列。第二个表名为DateDimNew。此表包含ID、DT、和DOWTitleID列。DOWTitleID列是DOWTitle表中的外键列。此新架构是规范化架构, 而旧架构是非规范化架构在代码的第二部分中, 实际上删除在代码的第一节中创建的表, 并创建一个同名的视图DateDimOld。DateDimOld视图允许我查询新的规范化架构, 就像我在旧架构中查询DateDimOld表一样。此新视图DateDimOld允许我为使用旧架构设计的任何代码提供向后兼容性。

您可以看到, 可以使用多种不同的视图方式在我的示例中, 我只显示您从视图中选择数据视图也可用于更新表。此外, 还可以在创建视图时使用其他选项更新视图的基础表

视图还可用于更新表中的数据。为了演示这一点, 我将运行清单6中的代码

INSERT INTO DateDimOld (DOWTitle)

VALUES (DATENAME(DW,'12/4/2013'));

清单 6: 使用视图将数据插入基础表中

清单6中的代码没有真正更新DateDimOld表 (反正它已经被删除了), 而是更新了作为DateDimOld的视图定义一部分的基础表DOWTitle 。运行清单6中的插入语句后, 在DOWTitle表中创建了一行, 其中包含DOWTitle列中的值 "星期三"。因为DateDimOld是我的规范化日期维度表的视图, 所以我还需要在表的DateDimNew中放置另一行, 以便视图DateDimOld显示 "星期三" 值。为此, 我运行清单7中的代码

INSERT INTO DateDimNew (DT, DOWTitleID)

   SELECT '12/4/2013', DOWTitleID FROM DOWTitle

       WHERE DOWTitle = DATENAME(DW,'12/4/2013');

清单 7: 向添加行 DateDimNew 表

因为列DOWTitleID不是DateDimOld视图的一部分, 所以无法使用该视图更新DateDimNew表。相反, 我必须编写清单7中的代码来直接引用基础视图表

使用视图更新视图的基础表有一些限制。以下是这些限制:

·         只能更新视图中的单个基础表

·         必须在视图中直接引用要更新的列, 而无需对其进行任何计算

·         被修改的列不能受组按、不同或具有子句的影响

·         当使用 "检查" 选项 (下面的选项更多) 时, 您的视图不包含顶部子句

有关限制的更多信息, 请参阅联机丛书文档。

 

确保视图不受其他表更改或更新的影响

在我到目前为止向您展示的CREATEVIEW语句中，创建的视图不会限制您对底层表所能做的事情。您可以对视图使用的基础表进行一些更改，这些更改可能会破坏视图，或者返回意外的结果。其中一个会破坏视图的更改是删除视图引用的列。在某些情况下，您可能希望确保您的观点不受此类问题的影响。创建视图时，可以在CREATEVIEW或SELECT语句上添加一些附加子句，以帮助消除这些恼人的潜在问题。您可以做的第一件事是绑定您的视图基础表模式。通过将表绑定到基础架构，可以限制可能破坏视图的任何表更改。为了演示，让我运行清单8中的代码。

 

清单8：创建一个带有模式绑定的视图

在清单8中，我删除并重新创建了DateDimold视图。当我重新创建它时，我添加了WITH SCHEMABINDING子句。这创建了架构绑定视图。当我做这个更改时，我还必须稍微修改视图中的SELECT语句。我所做的改变是为所有表都取了两个部件名。建议在引用SQLServer表时始终使用两个部件命名，无论SQLServer技术上是否需要它。这个要求意味着我必须在原始视图中的两个表名前面添加“DBO”。除此之外，这个观点和原来的完全一样。为了显示模式绑定如何限制对底层表的操作，让我运行清单9中的代码。

 

清单9：尝试修改带有模式绑定的表当运行清单9中的代码时，我会得到报告1中显示的错误。

 

报表1：更改模式绑定视图的列时收到的错误

通过查看Report 1中的输出，您可以看到数据库引擎阻止我修改DT列，该列包含在视图定义中。通过创建模式绑定视图，我确保没有人出现并修改可能影响我的视图的表的任何部分。创建视图的另一个可用选项是WITH CHECK选项。WITH CHECK选项允许您在视图上设置约束，以确保对底层表的任何更新都可以使用视图进行选择。要向您展示WITH CHECK选项是如何使用的，请查看清单10中的代码。

 

 

清单10：创建带有CHECK选项的视图在清单10中的代码中，可以看到我创建了一个表并填充了一个名为DayOfTheWeek的表。我还创建了一个名为DisplayDayOfTheWeek的视图，它通过使用WHERE子句限制返回的天数，并添加了WITH CHECK选项。通过添加WITH CHECK选项，SQLServer将不允许我使用DisplayDayOfTheWeek视图插入或更新行，除非DayOfTheWeekNum值小于5。为了测试这一点，我可以运行清单11中的代码。

 

清单11：测试WITH CHECK选项的代码当清单11中的代码试图插入值大于5的新行时，或者将现有的星期五行更新为DayOfTheWeekNum值大于5时，我将得到报告2中显示的错误。实际上，清单11中的代码将生成此消息两次，一次用于插入，一次用于更新。

 

Report 2：测试WITH CHECK选项的代码通过查看消息，您可以看到WITH CHECK选项导致清单11中的INSERT和UPDATE语句失败。如果要实际插入或更新这些行，则有两个选项。一个选项是删除WITH CHECK选项。Thi允许您通过视图更改基础表，但从视图中进行选择仍然不能显示那些符合视图定义条件的值。如果希望插入和更新这些行并让视图显示它们，则第二个选项是更改视图中的WHERE条件，以允许选择新值。(请记住，WITCHECK选项仅适用于通过视图所做的更改；IS并不阻止直接对基础表进行更新或插入。如果希望控制可能影响视图的语句类型，则应考虑使用模式绑定和/或WITH CHECK选项。

使用视图时的性能考虑

              使用视图是否存在性能问题？与大多数SQL Server问题一样，答案是“它取决于”。

              视图的性能将取决于视图所做的操作。一个简单的视图，读取一个没有联接子句的单个表将很可能与引用单个表的查询非常类似。但是，如果有一个视图引用引用视图的视图，那么这些视图包含多个连接子句呢？由引用视图的简单SELECT语句实际执行的基础查询可能会爆炸成具有多个联接子句的非常复杂的SELECT语句，最终可能会比预期的要做更多的工作。

              关于视图的性能问题，值得注意的是，当视图包含多个表连接在一起时，您只想从视图中的单个表返回数据。在这种情况下，SQLServer仍然需要加入视图中的所有表以从单个表返回数据。这会导致SQLServer添加额外的工作，以便在视图中加入所有这些表，对于那些只想从视图中的单个表返回数据的查询，响应时间较慢。如果发现只从视图中返回单个表中的数据，性能很重要，那么最好将查询写入单个表，而不是使用包含多个表连接的视图。

              CVES是一种简化代码并隐藏数据库模式复杂性的好方法。但是隐藏这种复杂性会导致严重的性能问题。如果您计划使用视图，请确保您知道视图在幕后所做的事情。了解查询引擎将不得不执行的任务来执行对视图的查询，将有助于开发执行良好的代码。              使用视图保护数据

              人们使用视图的另一个原因是确保访问表中的某些列。假设您有业务需求，允许用户对包含机密数据的表（如社会安全号或信用卡号）进行报告。你可能不希望他们有权访问这些机密栏目。确保他们不能读取这些机密数据列的一种方法是创建排除这些机密列的表的视图，并且不提供用户在基础表上选择权限。

总结

             视图是实现安全性、简化查询复杂数据库模式和/或提供向后能力的一种很好的方式。但是如果你开始嵌套视图而不理解这可能导致的性能影响，那就有一个邪恶的观点。当您查看需要T-SQL解决方案的特定业务需求时，将视图视为您可以用来实现解决方案的众多工具中的一种。

问答

              在本节中，您可以通过回答下列问题来查看您已经理解了如何使用视图来查询数据库。

问题1：

              什么是良好的业务需求，视图可以帮助您实现？

              需要保持应用程序或自组织查询访问表中的底层列。

              需要简化查询复杂数据库结构所需的代码。

              需要提供向后兼容性。

              以上所有

问题2：

              您需要确保当列值被更新或插入时，可以通过视图来选择它。哪一个条款提供了这个功能？

              创建视图

              具有模式化

              带有支票选项

              以上均无

问题3：

              您需要限制对表中的机密数据的访问。可以使用什么方法来限制对该数据的访问？

              使用“带检查”选项创建视图

              创建一个使用带有示意图选项的视图

              创建一个排除表中的机密列的视图，并且没有证明选择对表的访问。              创建一个排除表中的机密列的视图，并证明对表的选择访问。

答案：

问题1：

              答案是D。在直接查询上使用视图有很多原因。A、B和C是这些原因中的一部分。

问题2：

              正确的答案是C.创建视图不是提供任何附加数据完整性检查的子句。使用示意图子句确保在视图的基础表结构发生更改时，任何变更表语句不会导致视图出现问题。使用“检查选项”确保您无法更新基础表，除非使用视图立即可以更改这些查询。

问题3：

              正确答案是C.回答A和B不特别限制对机密列的访问，因为他们没有提到从视图中排除机密列。回答D是不正确的，因为如果人们访问包含机密数据的基础表，那么他们仍然可以通过编写直接与表直接相关的查询来选择机密列。