翻译Stairway to T-SQL: Beyond The Basics Level 4: Using Views to Simplify Your Query

时间：2021-10-14 16:22:55

楼梯到T-SQL：超越基本4级：使用视图简化查询

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

系列

本文是楼梯系列：楼梯到T-SQL：超越基础的一部分

从楼梯到T-SQL DML，GregoryLarsen介绍了T-SQL语言的更高级方面，比如子查询。

在这个层次上，我将讨论如何使用数据库视图来简化Transact-SQL(T-SQL)代码。通过了解如何使用视图，您将能够更好地支持编写T-SQL代码以满足复杂的业务需求。在本文中，我将讨论什么是数据库视图，然后提供一些示例来帮助您理解如何使用视图实现不同的编码场景。

什么是视图？

视图是由行和列组成的虚拟表。数据可以来自单个表，也可以来自多个表。查询视图就像查询普通表一样。视图由CREATE VIEW语句创建，并存储在创建视图的数据库中。

以下是视图可以帮助您实现编码逻辑的一些情况：

您不希望向查询表的用户公开表的所有列。

您的数据库架构设计很复杂，因此您可以构建视图以简化用户访问。

您希望更改数据库架构设计，但希望保持向后兼容性，这样就不必重写现有代码。

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

样本数据

为了演示视图是如何工作的，以及它们如何简化您的T-SQL代码，我需要一些测试数据来运行这些视图。我的大多数示例将使用AdventureWorks2008R2数据库，而不是创建自己的测试数据。如果您想继续学习并在您的环境中运行我的示例，那么您可以从这里下载AdventureWorks2008R2数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587

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

通过使用视图，您可以返回作为表列的子集的列表、来自多个表的一组列、基于某些WHERE条件的一组受约束的列，或许多其他不同的要求。在本节中，我将为您提供许多不同的示例，说明如何使用视图来满足不同的业务需求。

对于我的第一个示例，让我们假设您需要不将单个表中的所有列呈现给应用程序或临时查询。对于这个例子，让我们假设您只想从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：HumanResources.Employee表的表定义

应用程序和临时用户需要的非个人信息如下：BusinessEntityId、NationalIDNumber、LoginID、OrganizationNode、OrganizationLevel、JobTitle和HireDate。

为了创建一个只返回HumanResources.Employee表中列子集的视图，我将使用清单2中的代码。

CREATE VIEW [HumanResources].[EmployeeInfo]
AS
	SELECT [BusinessEntityID] 
	      ,[NationalIDNumber]  
	      ,[LoginID]  
	      ,[OrganizationNode]  	
	      ,[OrganizationLevel]
	      ,[JobTitle] 
	      ,[HireDate]  
	      ,[CurrentFlag]  
    FROM [HumanResources].[Employee];

清单2：从HumanResources.Employee表创建非个人信息视图的脚本

通过查看清单2中的CREATE VIEW语句，您可以看到它非常简单。视图的代码只是一个简单的SELECT语句，它包含了我希望视图在选择条件中公开的列。一旦我创建了这个视图，我就可以像普通的表一样查询它。清单3中的脚本演示了两个不同的SELECT语句，它们使用我用清单2中的代码创建的视图从HumanResources.Employee表中检索数据。

SELECT * FROM [HumanResources].[EmployeeInfo];
SELECT * FROM [HumanResources].[EmployeeInfo]
	WHERE JobTitle like '%Manager%';

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

通过查看清单3中的代码，您可以看到在FROM子句之后引用的对象是我在清单2中创建的视图的名称。我引用了SELECT语句中的视图，就像引用表一样。清单3中的第一个SELECT语句返回了HumanResources.Employee表中的所有行，但只返回了我视图中SELECT子句中的那些非个人列。清单3中的第二个SELECT语句演示了我可以使用WHERE语句约束返回的行，就像引用表时一样。

有时，数据库设计相当复杂，这可能会使构建查询变得复杂，从而访问数据库中所需的数据。这些复杂的设计可能需要复杂的多表连接才能真正返回数据。这就是视图可以帮助的地方。通过使用视图，可以在视图中构建复杂的多表连接，然后使用视图查询数据。通过这样做，您可以简化查询数据库的代码，并在视图中隐藏数据库设计的复杂性。为了演示这一点，我创建了一个视图清单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中的SalesOrderCombined 2005视图将多个表连接在一起，并且只从这些表返回一个列的子集。此外，视图还有WHERE子句。WHERE子句只返回与2005年下的销售订单相关的数据。此视图无需理解如何使用不同的键列将多个表连接在一起。通过对SalesOrderCombined2005视图执行SELECT语句，可以完成所有这些联接，而不必在SELECT语句中引用它们。通过在视图中放置复杂的联接语法，可以简化从复杂数据库设计中检索数据的代码。此外，这些类型的视图确保对数据库的所有查询都将使用相同的联接语法。通过提供和使用视图查询数据，您可以消除不正确编写联接条件的可能性。

有时候，您希望随着时间的推移来改进您的数据库设计，但是您不想破坏现有的代码。视图可以满足此业务需求。要演示这一点，请查看清单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表(反正已经删除了)，而是更新了基础表DOWTitle，它是DateDimold视图定义的一部分。在运行清单6中的INSERT语句之后，在DOWTitle表中创建了一行，该表包含DOWTitle列中的值“Wednesday”。由于DateDimold是规范化日期维度表的视图，因此还需要在表DateDimNew中放置另一行，以便视图DateDimold显示“Wednesday”值。为此，我运行清单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中的代码来直接引用基础视图表。

在使用视图更新视图的基础表方面存在一些限制。以下是这些限制：

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

正在更新的列必须在视图中直接引用，而不对其进行任何计算。

被修改的列不受GROUPBY, DISTINCT, or HAVING子句的影响。

在使用CHECK选项(下面更多有关此选项)时，视图不包含TOP子句。

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

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

在我已经向你展示的CREATE VIEW语句中，所创建的视图不会限制你对底层表所做的操作。你可以对基础表进行一些更改，以便查看可能会破坏视图的视图，或者返回意外的结果。一个这样的更改会破坏一个视图，即删除一个视图引用的列。有些情况下，你可能想要确保你的观点不受这些问题的影响。当创建一个视图时，你可以在CREAT视图或SELECT语句中添加一些额外的子句，以帮助消除这些令人烦恼的潜在问题。 

你可以做的第一件事是你定您的视图底层的表模式。通过将表绑定到底层模式，可以限制任何可能会破坏视图的表更改。为了演示，让我运行清单8中的代码。

ALTER VIEW DateDimOld WITH SCHEMABINDING AS 
SELECT DDN.ID, DDN.DT, DOWT.DOWTitle 
	FROM dbo.DateDimNew AS DDN
	JOIN dbo.DOWTitle AS DOWT
	ON DDN.DOWTitleID = DOWT.DowTitleID;
GO

清单8:使用模式绑定创建视图。

在清单8中，我删除并重新创建了DateDimOld视图。当我重新创建它时，我添加了SCHEMABINDING子句。

这创建了一个模式绑定视图。当我做出这个改变时，我还需要稍微修改一下视图中的SELECT语句。我所做的更改是为所有表有两个部分的名称。建议您在引用SQL Server表时总是使用两部分命名，而不管SQL Server在技术上是否需要它。这个要求意味着我必须添加“dbo”。在我的原始视图的两个表名前面。除此之外，这一观点与最初的观点完全一致。 

为了说明模式绑定如何限制我对底层表的操作，让我运行清单9中的代码。 

ALTER TABLE dbo.DateDimNew 
  ALTER COLUMN DT INT;

清单9:尝试使用模式绑定修改表。

当运行清单9中的代码时，我将得到报告1中显示的错误。

Msg 5074, Level 16, State 1, Line 1
The object 'DateDimOld' is dependent on column 'DT'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN DT failed because one or more objects access this column.

报告1:更改模式绑定视图的列时收到的错误。

通过检查报告1中的输出，你可以看到数据库引擎阻止了我修改DT列，它包含在视图定义中。通过创建一个模式绑定视图，我确保某人不会出现并修改可能影响我的 DateDimOld 视图的任何部分。 

创建视图的另一个选项是WITH CHECK选项。带有CHECK选项的选项允许你对视图进行约束，以确保对基础表进行的任何更新都可以使用视图进行选择。为了向你展示使用CHECK选项的方式，请查看清单10中的代码。 

CREATE TABLE DayOfTheWeek(DayOfTheWeek varchar (10), 
              DayOfTheWeekNum int);
INSERT INTO  DayOfTheWeek VALUES
    ('Monday',0),
    ('Tuesday',1),
    ('Wednesday',2),
    ('Thursday',3),
    ('Friday',4);
GO
CREATE VIEW DisplayDayOfTheWeek 
AS 
SELECT DayOfTheWeek, DayOfTheWeekNum FROM DayOfTheWeek
WHERE DayOfTheWeekNum < 5
	WITH CHECK OPTION;

清单10:使用CHECK选项创建视图。

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

INSERT INTO  DisplayDayOfTheWeek VALUES
    ('Saturday',5);
UPDATE DisplayDayOfTheWeek
SET DayOfTheWeekNum = 5
WHERE DayOfTheWeek = 'Friday';

清单11:用CHECK选项测试代码。

当清单11中的代码尝试插入一个值大于5的新行，或者将我现有的周五行更新为大于5的DayOfTheWeekNum值时，我得到了报告2中所示的错误。实际上，清单11中的代码将两次生成此消息，一次用于插入，一次用于更新。

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

报告2:用CHECK选项测试代码。

通过查看消息，您可以看到带有CHECK选项的消息，导致我的INSERT和UPDATE语句在清单11中失败。如果您想实际插入或更新这些行，您有两个选项。一个选项是用CHECK选项删除。这将允许您通过视图更改基础表，但是从视图中选择仍然不会显示那些符合视图定义条件的值。如果您想要插入和更新这些行，并让视图显示它们，那么第二个选项将是更改视图中的WHERE条件，以允许选择新的值。(请记住，使用CHECK选项只适用于通过视图进行的更改;is不阻止直接向底层表的更新或插入。 
如果您想要控制可能影响您的视图的语句类型，那么您应该考虑使用模式绑定和/或WITH CHECK选项。 
使用视图时的性能考虑 
使用视图有性能问题吗?与大多数SQL Server问题一样，答案是“看情况”。 
视图的性能取决于视图在做什么。一个简单的视图，它读取一个没有连接子句的表，它很可能与只引用单个表的查询执行类似的操作。但是，如果您有一个视图，它引用了一个引用视图的视图，而这些视图包含多个连接子句，该怎么办呢?通过简单的SELECT语句实际执行的底层查询可能会被扩展成一个非常复杂的SELECT语句，其中包含多个连接子句，并且可能会比您预期的做更多的工作。 
其他值得一提的关于视图性能问题的东西是，当一个视图包含多个表连接在一起时，但您只想从视图中的单个表返回数据。在这种情况下，SQL Server仍然需要连接视图中的所有表，以从单个表返回数据。这可能导致SQL Server的额外工作加入到视图中所有的表中，而对于那些只希望从视图中的单个表返回数据的查询的响应时间较慢。如果您发现只从一个表中返回数据，并且性能很重要，那么您最好将查询写在单个表上，而不是使用包含多个表连接的视图。 
CVews是一种简化代码并隐藏数据库模式复杂性的好方法。但是隐藏这种复杂性会导致严重的性能问题。如果您打算使用视图，请确保您知道视图在幕后做什么。理解查询引擎必须执行的工作，以执行针对您的视图的查询将帮助您开发性能良好的代码。 
使用视图保护数据。 

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

总结

视图是实现安全性、简化查询复杂数据库模式和/或提供向后功能的好方法。但是，如果您在不理解这可能导致的性能影响的情况下开始嵌套视图，那么就会有一个负面的视图。当您看到一个给定的业务需求需要一个T-SQL解决方案时，请将视图视为您可能能够用于实现解决方案的众多工具中的一个。 

问题和答案 
在本节中，你可以通过回答下列问题，来回顾你对使用视图查询数据库的理解程度。 
问题1: 
一个视图可以帮助您实现哪些好的业务需求? 
需要保持应用程序或特定查询访问表中的底层列。 
需要简化查询复杂数据库结构所需的代码。 
需要提供向后兼容性。 
上面所有的 
问题2: 
您需要确保当一个列值被更新或插入时，通过视图可以选择它。哪个子句提供了这个功能? 
创建视图 
与SCHEMABINDING 
检查选项 
以上都不是 
问题3: 
您需要在表中限制对机密数据的访问。可以使用什么方法来限制对这些数据的访问? 
使用WITH CHECK选项创建视图。 
创建一个使用带有SCHEMABINDING选项的视图。 
创建一个视图，该视图不包含表中的机密列，并且不能被证明选择访问该表。 
创建一个视图，该视图排除表中的机密列，并证明选择访问表。 
答案: 
问题1: 
答案是d。在直接查询中使用视图有很多原因。a、b和c是其中的一些原因。 
问题2: 
正确的答案是c. CREATE VIEW不是提供任何额外数据完整性检查的子句。WITH SCHEMABINDING子句确保任何ALTER TABLE语句不会在视图的底层表结构发生更改时引起视图问题。它是带有CHECK选项的，它确保您不能更新基础表，除非更改立即可以使用视图进行查询。 
问题3: 

正确答案是c.答案a和b没有特别限制对机密列的访问，因为他们没有提到从视图中排除机密列。答案d是不正确的，因为如果人们可以访问包含机密数据的底层表，那么他们仍然可以通过编写直接针对表的查询来选择机密列。

这篇文章是通往T-SQL的阶梯的一部分:超越基本的阶梯。

标签：

相关文章