通往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选项。