数据库



楼梯到T-SQL：超越基本4级：使用视图简化查询Gregory Larsen著，2016/07/22(首次出版：2014/03/19)系列本文是楼梯系列的一部分：楼梯到T-SQL：超越基础从楼梯到T-SQLDML，GregoryLarsen介绍了T-SQL语言的更高级方面，比如子查询。在这个层次上，我将讨论如何使用数据库视图来简化Transact-SQL(T-SQL)代码。通过了解如何使用视图，您将能够更好地支持编写T-SQL代码以满足复杂的业务需求。在本文中，我将讨论什么是数据库视图，然后提供一些示例来帮助您理解如何使用视图实现不同的编码场景。什么是视图？视图是由行和列组成的虚拟表。数据可以来自单个表，也可以来自多个表。查询视图就像查询普通表一样。视图由CREATEVIEW语句创建，并存储在创建视图的数据库中。以下是视图可以帮助您实现编码逻辑的一些情况：您不希望向查询表的用户公开表的所有列。您的数据库架构设计很复杂，因此您可以构建视图以简化用户访问。您希望更改数据库架构设计，但希望保持向后兼容性，这样就不必重写现有代码。更好地理解如何使用视图的最佳方法是通过一些使用视图来满足不同业务需求的示例。样本数据为了演示视图是如何工作的，以及它们如何简化您的T-SQL代码，我需要一些测试数据来运行这些视图。

我的大多数示例将使用AdventureWorks2008R2数据库，而不是创建自己的测试数据。如果您想继续学习并在您的环境中运行我的示例，那么您可以从这里下载AdventureWorks2008R2数据库：http：//msftdbprodsamples.codeplex.com/releases/view/93587使用视图简化SQL代码的示例通过使用视图，您可以返回作为表列子集的列表、来自多个表的一组列、基于某些WHERE条件的一组受约束的列，或许多其他不同的要求。在本节中，我将为您提供许多不同的示例，说明如何使用视图来满足不同的业务需求。对于我的第一个示例，让我们假设您需要不将单个表中的所有列呈现给应用程序或临时查询。对于这个例子，让我们假设您只想从HumanResource.Employee表中返回非个人信息，如清单1所示。(请注意，这个表已经存在于AdventureWorks2008R2数据库中；此处列出的定义仅供参考之用。)创建表[HumanResources].[Employee]([BusinessEntityID][int]不为空，[NationalIDNumber][nvarchar](15)NOTNULL，[LoginID][nvarchar](256)NOTNULL，[OrganizationNode][层次结构]NULL，[OrganizationLevel]as([OrganizationNode].[GetLevel]()[职称][nvarchar](50)非空，[生日][日期]不无效，[MaritalStatus][nchar](1)NOTNULL，[性别][Nchar](1)非零，[HireDate][日期]不为空，[工资旗][DBO].[标志]不为空，[休假][小]不无效，[SickLeaveHours][Smallint]不零，[Current国旗][DBO].[标志]不为空，[rowguid][unique标识符]ROWGUIDCOL NOT NULL，[ModifiedDate][datetime]NOTNULL)；清单1：HumanResources.Employee表的表定义应用程序和临时用户需要的非个人信息如下：BusinessEntityId、NationalIDNumber、LoginID、OrganizationNode、OrganizationLevel、职务头衔和HireDate。

为了创建一个只返回HumanResources.Employee表中列子集的视图，我将使用清单2中的代码。创建视图[人力资源]。[EmployeeInfo]如选择[BusinessEntityID]，[国民编号]，[LoginID]，[组织节点]，[组织级别]，[职称]，[HireDate]，[现行旗帜]来自[人力资源][雇员]；清单2：从HumanResources.Employee表创建非个人信息视图的脚本通过查看清单2中的CREATEVIEW语句，您可以看到它非常简单。视图的代码只是一个简单的SELECT语句，它包含了我希望视图在选择条件中公开的列。一旦我创建了这个视图，我就可以像普通的表一样查询它。清单3中的脚本演示了两个不同的SELECT语句，它们使用我用清单2中的代码创建的视图从HumanResources.Employee表中检索数据。选择*从[人力资源]。[EmployeeInfo]；选择*从[人力资源]。[EmployeeInfo]如“%经理%”这样的职位；清单3：使用视图返回数据的两个SELECT语句通过查看清单3中的代码，您可以看到在FROM子句之后引用的对象是我在清单2中创建的视图的名称。我引用了SELECT语句中的视图，就像引用表一样。清单3中的第一个SELECT语句返回了HumanResources.Employee表中的所有行，但只返回了我视图中SELECT子句中的那些非个人列。清单3中的第二个SELECT语句演示了我可以使用WHERE语句约束返回的行，就像引用表时一样。有时，数据库设计相当复杂，这可能会使构建查询变得复杂，从而访问数据库中所需的数据。这些复杂的设计可能需要复杂的多表连接才能真正返回数据。这就是视图可以帮助的地方。通过使用视图，可以在视图中构建复杂的多表连接，然后使用视图查询数据。通过这样做，您可以简化查询数据库的代码，并在视图中隐藏数据库设计的复杂性。为了演示这一点，我创建了一个视图清单4，它检索多个表中包含的销售订单数据。

创建视图SalesOrderCombined 2005如选择OH.SalesOrderID、OH.OrderDate、OH.ShipDAte，ST.Name作为领土名称，BTA.Cityas BillToCity、STA.City作为ShiptToCity、OH.TotalDue来自SalesOrderHeader OH加入销售。论OH.领土ID=ST.属地ID加入Person.Address BTA关于OH.BillToAddressID=BTA.AddressID连接人员地址STA关于OH.ShipToAddressID=STA.AddressID其中年份(OH.OrderDate)=2005年；清单4：包含多表连接的视图清单4中的SalesOrderCombined 2005视图将多个表连接在一起，并且只从这些表返回一个列的子集。此外，视图还有WHERE子句。WHERE子句只返回与2005年下的销售订单相关的数据。此视图无需理解如何使用不同的键列将多个表连接在一起。通过对SalesOrderCombined2005视图执行SELECT语句，可以完成所有这些联接，而不必在SELECT语句中引用它们。通过在视图中放置复杂的联接语法，可以简化从复杂数据库设计中检索数据的代码。此外，这些类型的视图确保对数据库的所有查询都将使用相同的联接语法。通过提供和使用视图查询数据，您可以消除不正确编写联接条件的可能性。有时候，您希望随着时间的推移来改进您的数据库设计，但是您不想破坏现有的代码。视图可以满足此业务需求。要演示这一点，请查看清单5中的代码。-开始旧模式创建表DateDimold(身份标识，DT日期，DOWTitle varchar(10)；去-DateDimold人口插入DateDimold(DT，DOWTitle)值(“2013年1月12日”，DATENAME(DW，“12/1/2013”)，(“12/2/2013”，DATENAME(DW，“12/2/2013”)，(“12/3/2013”，DATENAME(DW，“12/3/2013”)；去从DateDimold中选择*；去-结束旧模式-开始新模式创建表DOWTitle(属性标识主键，DOWTitle VARCHAR(10)；去创建表DateDimNew(身份标识，DT日期，DOWTitleID int)；去带CHECK ADD约束的ALTERTABLE DateDimNew[FK_DateDimNew_DOWTitle_DOWTitleID]外键(DOWTitleID)参考文献DOWTitle(DOWTitleID)去-填充DOWTitle插入DOWTitle(DOWTitle)值(DATENAME(DW，‘12/1/2013’)，(DATENAME(DW，‘12/2/2013’)，(DATENAME(DW，‘12/3/2013’)；去-DateDimNew人口插入DateDimNew(DT，DOWTitleID)值(“2013年1月12日”，1)(“2013年2月12日”，2)
清单5：新旧模式结构通过查看清单5中的代码，您可以看到代码有两个不同的部分。在第一节中，我定义、填充并显示了一个旧模式中的一些数据，该模式只有一个名为DateDimold的表。此表包含名为DT的日期列和名为DOWTitle的“一周中的一天”列，并将这些列关联到ID列。在第二节中，我定义了一个新模式，以取代第一节中的旧模式。在第二节中，我创建了两个表。第一个表名为DOWTitle，它包含DOWTitle和DOWTitleID列。第二个表名为DateDimNew。此表包含ID、DT和DOWTitleID列。DOWTitleID列是DOWTitle表中的外键列。这个新架构是一个规范化模式，而旧模式是一个非规范化架构。在代码的第二部分中，我实际上删除了在第一部分代码中创建的表，并创建了一个同名的视图DateDimold。DateDimold视图允许我查询新的规范化模式，就像在旧模式中查询DateDimold表一样。这个新视图DateDimold允许我为我可能构建的使用旧模式设计的任何代码提供向后兼容性。正如您所看到的，视图可以使用许多不同的方式。在这里的示例中，我只向您展示了从视图中选择数据。视图也可以用于更新表。此外，在创建视图时还可以使用其他选项。更新视图的底层表视图也可用于更新表中的数据。为了演示这一点，我将运行清单6中的代码。插入DateDimold(DOWTitle)数值(DATENAME(DW，‘12/4/2013’)；清单6：使用视图将数据插入底层表清单6中的代码并没有真正更新DateDimold表(反正已经删除了)，而是更新了基础表DOWTitle，它是DateDimold视图定义的一部分。

在运行清单6中的INSERT语句之后，在DOWTitle表中创建了一行，该表包含DOWTitle列中的值“triiii”。由于DateDimold是规范化日期维度表的视图，因此还需要在表DateDimNew中放置另一行，以便视图DateDimold显示“星期三”值。为此，我运行清单7中的代码。插入DateDimNew(DT，DOWTitleID)选择“12/4/2013”，DOWTitleID从DOWTitle其中DOWTitle=DATENAME(DW，‘12/4/2013’)；清单7：向DateDimNew表添加一行因为DOWTitleID列不是DateDimold视图的一部分，所以我无法使用该视图更新DateDimNew表。相反，我必须编写清单7中的代码来直接引用基础视图表。在使用视图更新视图的基础表方面存在一些限制。以下是这些限制：只能更新视图中的单个基础表。正在更新的列必须在视图中直接引用，而不对其进行任何计算。被修改的列不受组的影响，不受子句的影响。在使用CHECK选项(下面更多有关此选项)时，视图不包含top子句有关限制的更多信息，请参阅联机丛书文档。确保视图不受其他表更改或更新的影响在我到目前为止向您展示的CREATEVIEW语句中，创建的视图不会限制您对底层表所能做的事情。您可以对视图使用的基础表进行一些更改，这些更改可能会破坏视图，或者返回意外的结果。其中一个会破坏视图的更改是删除视图引用的列。在某些情况下，您可能希望确保您的观点不受此类问题的影响。创建视图时，可以在CREATEVIEW或SELECT语句上添加一些附加子句，以帮助消除这些恼人的潜在问题。您可以做的第一件事是绑定您的视图基础表模式。通过将表绑定到基础架构，可以限制可能破坏视图的任何表更改。为了演示，让我运行清单8中的代码。用SCHEMABINDING更改DateDimold作为选择DDN.ID，DDN.DT，DOWT.DOWTitle来自dbo.dateDimNew作为DDN加入dbo.DOWTitle关于DDN.DOWTitleID=DOWT.去清单8：创建一个带有模式绑定的视图在清单8中，我删除并重新创建了DateDimold视图。当我重新创建它时，我添加了WITH SCHEMABINDING子句。这创建了架构绑定视图。当我做这个更改时，我还必须稍微修改视图中的SELECT语句。我所做的改变是为所有表都取了两个部件名。建议在引用SQLServer表时始终使用两个部件命名，无论SQLServer技术上是否需要它。这个要求意味着我必须在原始视图中的两个表名前面添加“DBO”。除此之外，这个观点和原来的完全一样。为了显示模式绑定如何限制对底层表的操作，让我运行清单9中的代码。更改表dbo.DateDimNew改变列DT int；清单9：尝试修改带有模式绑定的表当运行清单9中的代码时，我会得到报告1中显示的错误。MSG 5074，第16级，状态1，第1行对象‘DateDimold’依赖于列‘dt’。MSG 4922，第16级，第9状态，第1行ALTERTABLE ALTER列DT失败，因为一个或多个对象访问该列。报表1：更改模式绑定视图的列时收到的错误通过查看Report 1中的输出，您可以看到数据库引擎阻止我修改DT列，该列包含在视图定义中。通过创建模式绑定视图，我确保没有人出现并修改可能影响我的DateDimold视图的表的任何部分。创建视图的另一个可用选项是WITH CHECK选项。WITH CHECK选项允许您在视图上设置约束，以确保对底层表的任何更新都可以使用视图进行选择。要向您展示WITH CHECK选项是如何使用的，请查看清单10中的代码。创建表DayOfTheWeek(DayOfTheek varchar(10)，DayOfTheWeekNint)；插入DayOfTheWeek值(“星期一”，0)，(“星期二”，1)，(“星期三”，2)，(“星期四”，3)，(“星期五”，4)；去创建视图DisplayDayOfTheWeek如从DayOfTheWeek选择DayOfTheWeek，DayOfTheWeekNum其中DayOfTheWeekNum<5有支票选项；清单10：创建带有CHECK选项的视图在清单10中的代码中，可以看到我创建了一个表并填充了一个名为DayOfTheWeek的表。我还创建了一个名为DisplayDayOfTheWeek的视图，它通过使用WHERE子句限制返回的天数，并添加了WITH CHECK选项。通过添加WITH CHECK选项，SQLServer将不允许我使用DisplayDayOfTheWeek视图插入或更新行，除非DayOfTheWeekNum值小于5。为了测试这一点，我可以运行清单11中的代码。插入DisplayDayOfTheWeek值(“星期六”，5)；更新DisplayDayOfTheWeekSET DayOfTheWeekNum=5其中，DayOfTheWeek=“周五”；

清单11：测试WITH CHECK选项的代码当清单11中的代码试图插入值大于5的新行时，或者将现有的星期五行更新为DayOfTheWeekNum值大于5时，我将得到报告2中显示的错误。实际上，清单11中的代码将生成此消息两次，一次用于插入，一次用于更新。尝试插入或更新失败，因为目标视图指定了WITE CHECK选项，或者跨越了一个使用CHECK选项指定的视图，而操作产生的一个或多个行在CHECKOPTION约束下没有限定。声明已经终止。Report 2：测试WITH CHECK选项的代码通过查看消息，您可以看到WITH CHECK选项导致清单11中的INSERT和UPDATE语句失败。如果要实际插入或更新这些行，则有两个选项。一个选项是删除WITH CHECK选项。Thi允许您通过视图更改基础表，但从视图中进行选择仍然不能显示那些符合视图定义条件的值。如果希望插入和更新这些行并让视图显示它们，则第二个选项是更改视图中的WHERE条件，以允许选择新值。(请记住，WITCHECK选项仅适用于通过视图所做的更改；IS并不阻止直接对基础表进行更新或插入。如果希望控制可能影响视图的语句类型，则应考虑使用模式绑定和/或WITH CHECK选项。使用视图时的性能考虑使用视图是否存在性能问题？与大多数SQLServer问题一样，答案是“它取决于”。视图的性能将取决于视图正在做什么。一个简单的视图读取一个没有JOIN子句的表，它的执行情况很可能与一个只引用单个表的查询非常相似。但是，如果您有一个引用视图的视图，并且这些视图包含多个联接子句，怎么办？引用视图的简单SELECT语句实际执行的底层查询可能会跳出一个非常复杂的SELECT语句，其中包含多个JOIN子句，并且可能会完成比您预期的更多的工作。关于视图的性能问题，还有一点值得一提的是，当视图包含多个连接在一起的表时，您只想从视图中的单个表返回数据。在这种情况下，SQLServer仍然必须连接视图中的所有表才能从单个表返回数据。这可能导致SQLServer加入视图中的所有表的额外工作，而对于只希望从视图中的单个表返回数据的查询，则会导致响应时间变慢。如果您发现只从视图中的单个表返回数据，并且性能很重要，那么最好是针对单个表编写查询，而不是使用包含多个表联接的视图。CVews是简化代码和隐藏数据库模式复杂性的好方法。但是隐藏这种复杂性会导致严重的性能问题。如果您计划使用视图，请确保您知道视图在幕后做什么。了解查询引擎对视图执行查询所必须做的工作，将有助于您开发性能良好的代码。使用视图保护数据人们使用视图的另一个原因是保护对表中某些列的访问。假设您有一个业务要求，允许用户对包含机密数据的表(如社会保险号码或信用卡号码)进行报告。您可能不希望他们访问这些机密列。确保他们不能读取这些机密数据列的一种方法是创建一个表的视图，该视图排除那些机密列，并且不提供用户在基础表上选择权限。摘要视图是实现安全性、简化复杂数据库架构查询和/或提供反向功能的一种很好的方法。但是，如果您开始嵌套视图，而不了解这可能造成的性能影响，那么视图就有其邪恶的一面。当您查看需要T-SQL解决方案的给定业务需求时，请将视图视为您可能用来实现解决方案的许多工具之一。问答在本节中，您可以通过回答以下问题来查看如何使用视图查询数据库。问题1：什么是好的业务需求，视图可以帮助您实现？需要防止应用程序或临时查询访问表中的底层列。需要简化查询复杂数据库结构所需的代码。需要提供向后兼容性。以上所有问题2：您需要确保在通过视图更新或插入列值时，它是可以通过视图选择的。哪个子句提供此功能？创建视图用SCHEMABINDING带检查选项以上都不是问题3：您需要限制对表中机密数据的访问。可以使用什么方法来限制对此数据的访问？使用WITH CHECK选项创建视图创建一个使用WITH SCHEMABINDING选项的视图创建一个视图，该视图将表中的机密列排除在外，但未证明对表的选择访问权限。创建一个视图，该视图排除表中的机密列，并验证对表的选择访问权。回答：问题1：答案是d，在直接查询上使用视图有很多原因。a、b和c是其中一些原因。问题2：正确的答案是c。CREATE视图不是提供任何其他数据完整性检查的子句。WITH SCHEMABINDING子句确保在更改视图的底层表结构时，任何ALTERTABLE语句都不会导致视图问题。只有WITH CHECK选项才能确保您不能更新底层表，除非这些更改可以立即使用视图进行查询。问题3：正确的答案是c。答a和b不具体限制对机密列的访问，因为它们没有提到将机密列排除在视图之外。答案d是不正确的，因为如果人们可以访问包含机密数据的底层表，那么他们仍然可以通过编写直接针对该表的查询来选择机密列。本文是T-SQL楼梯的一部分：超越基本楼梯注册我们的RSS提要，并得到通知，一旦我们发布一个新的层次在楼梯上！