任务 11 创建视图

任务11 创建视图 • 11.1 场景引入 • 问题：在公司的数据管理系统中，雇员表存储的数据有雇员ID、姓名、身份证号码、工资、职务、出生日期、家庭电话、办公电话。但身份证号码，工资、出生日期是保密数据，不是任何人都可以查看的，只有特定的人才可以查看这些列的数据，而雇员ID、姓名、职务、办公电话任何人都可以查看。为解决该问题，请提出一种解决方案。 • 要解决上述问题，可以使用视图。若要限制用户可使用的数据，如用户可以访问某些数据，进行查询和修改，但是表或数据库的其余部分是不可见的，也不能进行访问，这时可使用视图。上述问题中，雇员表的身份证号码、工资、出生日期列中含有保密信息，不应对所有用户公开，但其余列中含有的信息可以由所有用户使用，此时，可以定义一个视图，它包含表中保密列外所有的列。

11.2 理解视图 • 视图是数据库对象之一，下面的任务将要讨论什么是视图和为什么要创建视图。

11.2.1 了解视图的概念 • 视图是一个虚拟表，其内容由查询定义。视图一旦被建立，其作用与表没有什么两样。但是视图本质上并不保存任何数据值，视图的行和列数据来自定义视图的查询所引用的表，并且在引用视图时动态生成。表是实际存储数据的地方。 • 对视图所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或其他视图。

11.2.1 了解视图的概念 • 任务11.1 了解视图的创建和使用视图 • 问题描述 • 用SQL命令创建视图CustomerView，然后分别对CustomerView和其引用的基表Customers进行同样的操作，然后观察其结果。 • 解决方案 • (1) 创建CustomerView视图。 • (2) 下面两个 SQL 命令在基表上执行相同的操作——检索数据。 • (3) 下面两个 SQL 命令在基表上执行相同的操作——添加数据。 • (4) 下面两个 SQL 命令在基表上执行相同的操作——修改数据。 • (5) 下面两个 SQL 命令在基表上执行相同的操作——删除记录。

11.2.2 理解视图的作用 • 视图通常用来供不同用户选择、简化和自定义数据库中的不同数据。视图可用作安全机制，方法是允许用户通过视图访问数据，而不是授予用户直接访问视图基表的权限。 • 1. 选择特定数据 • 2. 简化数据操作 • 3. 自定义数据

11.3 创建视图 • 视图可以被看做虚拟表或存储查询。可通过视图访问的数据并不作为独特的对象存储在数据库内。数据库内存储的是 SELECT 语句。SELECT 语句的结果集构成视图所返回的虚拟表。用户可以用引用表时所使用的方法，在SQL 语句中通过引用视图名称来使用虚拟表。创建视图的一般格式为： • CREATE VIEW 视图名[ ( 字段别名1 , 字段别名2 ,... ) ] • [ WITH ENCRYPTION ] • AS • SELECT语句 • [ WITH CHECK OPTION ]

11.3.1 创建简单视图 • 任务11.2 使用视图查询每笔订单的小计 • 问题描述 • 创建视图，计算每笔订单的小计。 • 解决方案 • CREATE VIEW [Order Subtotals] • AS • SELECT OrderID, • SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) • AS Subtotal • FROM [Order Details] • GROUP BY OrderID

11.3.2 使用视图 • 任务11.3 查询各国雇员每笔订单的销售额 • 问题描述 • 使用视图Order Subtotals查询各国雇员每笔订单的销售额。 • 解决方案 • /* 各国雇员销售额 */ • SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount • FROM Employees INNER JOIN • (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) • ON Employees.EmployeeID = Orders.EmployeeID

11.3.2 使用视图 • 任务11.4 查询每年每笔订单的销售额 • 问题描述 • 使用视图Order Subtotals查询每年每笔订单的销售额。 • 解决方案 • /* 每年每笔订单的销售额 */ • SELECT Orders.ShippedDate, • Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year • FROM Orders INNER JOIN "Order Subtotals" • ON Orders.OrderID = "Order Subtotals".OrderID

11.3.3 创建具有计算列的视图 • 通过定义 SELECT 语句检索将在视图中显示的数据来创建视图。SELECT 语句引用的数据表称为视图的基表。 • 在下例中，数据库中的Product Sales for 1998是一个视图，该视图选择3个基表中的数据来显示包含常用数据的虚拟表。视图的数据是定义视图的查询语句决定的，我们可以创建具有计算列的查询，因此视图的列也可以是计算列。

11.3.3 创建具有计算列的视图 • 任务11.5 查询1998年各类产品的销售额 • 问题描述 • 创建视图，查询1998年各类产品的销售额。 • 解决方案 • CREATE VIEW [Product Sales for 1998] • AS • SELECT Categories.CategoryName, Products.ProductName, • SUM(CONVERT(money, (.[Order Details].UnitPrice * [Order Details].Quantity) • * (1 - [Order Details].Discount) / 100) * 100) AS ProductSales • FROM Categories INNER JOIN • Products ON Categories.CategoryID = Products.CategoryID INNER JOIN • Orders INNER JOIN • [Order Details] ON Orders.OrderID = [Order Details].OrderID ON • Products.ProductID = [Order Details].ProductID • WHERE (Orders.ShippedDate BETWEEN '1998-1-1' AND '1998-12-31') • GROUP BY Categories.CategoryName, Products.ProductName

11.3.4 创建视图列的别名 • 如果创建视图时不指定视图列的别名，则视图列将获得与 SELECT 语句中的列相同的名称。创建视图时可以在创建视图的查询中给列定义别名，也可以在视图定义中指定每列的名称。 • 下列情况下必须给视图列指定别名。 • 视图中有任何从算术表达式、内置函数或常量派生出的列。 • 视图中有两列或多列具有相同名称(通常由于视图定义包含连接，而来自两个或多个不同表的列具有相同的名称)。 • 希望使视图中的列名与它的源列名不同。 • 当给视图中的列指定了别名时，在选择视图中的列时应使用别名。

11.3.4 创建视图列的别名 • 任务11.6 查询1998年各类销售总额 • 问题描述 • 使用1998年各类产品的销售额视图创建视图，查询1998年各类销售总额。 • 解决方案 • CREATE VIEW [Category Sales for 1998 ](CategoryName, CategorySales) • AS • SELECT CategoryName, SUM(ProductSales) • FROM [Product Sales for 1998] • GROUP BY CategoryName

11.3.4 创建视图列的别名 • 任务11.7 查询1998订单销售额大于2 500的订单的信息 • 问题描述 • 使用每笔订单小计视图Order Subtotals创建视图，查询1998年订单销售额大于2 500的订单的销售额、订单号、发货日期及客户的公司名称。 • 解决方案 • CREATE VIEW [Sales Totals by Amount for 1998] • (SaleAmount, OrderID, CompanyName, ShippedDate) • AS • SELECT [Order Subtotals].Subtotal, Orders.OrderID, • Customers.CompanyName, Orders.ShippedDate • FROM Customers INNER JOIN • Orders INNER JOIN • [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID ON • Customers.CustomerID = Orders.CustomerID • WHERE ([Order Subtotals].Subtotal > 2500) AND (Orders.ShippedDate BETWEEN • '1998-01-01' AND '1990-12-31')

11.3.5 加密视图 • 任务11.8 加密视图 • 问题描述 • 创建视图，查询客户的订单信息及该客户的信息，并加密该视图。 • 解决方案 • CREATE VIEW [ Orders Qury ] • WITH ENCRYPTION • AS • SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, • Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, • Orders.ShipVia, Orders.Freight, Orders.ShipName, • Orders.ShipAddress,.Orders.ShipCity, Orders.ShipRegion, • Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, • Customers.Address, Customers.City, Customers.Region, • Customers.PostalCode, Customers.Country • FROM Customers INNER JOIN • Orders ON Customers.CustomerID = Orders.CustomerID

11.3.6 创建具有数据约束的视图 • 如果在创建视图时使用了WITH CHECK OPTION子句，则当通过该视图添加或修改记录时，必须符合创建视图的 SELECT 语句中所设定的条件。若不符合创建视图时所设定的条件，则拒绝执行，并显示错误信息。

11.3.6 创建具有数据约束的视图 • 任务11.9 创建具有数据约束的视图 • 问题描述 • 创建视图，查询未被中止的产品及其类别名称。 • 解决方案 • CREATE VIEW ProductList • AS • SELECT Products.*, Categories.CategoryName • FROM Categories INNER JOIN.Products • ON Categories.CategoryID = Products.CategoryID • WHERE Products.Discontinued = 0 • WITH CHECK OPTION

11.3.7 独立实践 • 1. 创建简单视图 • 2. 使用视图 • 3. 创建具有计算列的视图 • 4. 创建视图列的别名 • 5. 加密视图 • 6. 创建具有数据约束的视图 • 7. 修改和重命名视图 • 8. 通过视图修改数据

11.4 修改和重命名视图 • 在完成视图定义后，可以在不去除和重新创建视图的条件下更改视图名称或修改其定义，而不影响相关对象和与之相关联的权限。

11.4.1 修改视图 • 可用ALTER VIEW命令更改一个先前创建的视图。更改先前创建的视图不影响相关的对象(如存储过程或触发器)，除非视图定义的更改使该相关对象不再有效。ALTER VIEW命令的一般格式为： • ALTER VIEW view_name [ ( 字段别名1 , 字段别名2 ,... ] ) ] • [ WITH ENCRYPTION ] • AS • SELECT语句 • [ WITH CHECK OPTION ] • 其中，view_name为要修改的视图的名称。

11.4.1 修改视图 • 任务11.10 修改视图(1) • 问题描述 • 修改任务11.9创建的视图ProductList，查询所有的产品名称及其类别的名称。 • 解决方案 • ALTER VIEW ProductList • AS • SELECT Products. ProductName, Categories.CategoryName • FROM Categories INNER JOIN.Products • ON Categories.CategoryID = Products.CategoryID

11.4.1 修改视图 • 任务11.11 修改视图(2) • 问题描述 • 修改任务11.9创建的视图ProductList，查询未被中止的产品及其类别名称，使该视图具有数据约束并加密该视图。 • 解决方案 • ALTER VIEW ProductList • WITH ENCRYPTION • AS • SELECT Products.*, Categories.CategoryName • FROM Categories INNER JOIN.Products • ON Categories.CategoryID = Products.CategoryID • WHERE Products.Discontinued = 0 • WITH CHECK OPTION

11.4.2 重命名视图 • 可以使用sp_rename重命名视图。其一般格式为： • sp_rename '视图名', '视图名新名' • 任务11.12 重命名视图 • 问题描述 • 将视图ProductList重命名为 plist。 • 解决方案 • EXEC sp_rename 'ProductList', 'plist'

11.5 通过视图修改数据 • 视图是虚拟表，它具有了原始表的所有功能。在SQL语句中，视图的使用与表的使用相似。但由于视图中所包含的列可能是某个表的部分列，也可能是某些表的部分列，所以，从视图中修改数据时需要注意以下一些事项。 • (1)SQL Server 必须能够明确地解析对视图所引用基表中的特定行所做的修改操作。 • (2)在视图中修改的列必须直接引用表列中的基础数据。 • ① 聚合函数(AVG、COUNT、SUM、MIN、MAX)。 • ② 通过表达式并使用列计算出其他列。 • (3)对于基表中需更新而又不允许空值的所有列，它们的值在 INSERT 语句或 DEFAULT 定义中指定。这将确保基表中所有需要值的列都可以获取值。 • (4)在基表的列中修改的数据必须符合对这些列的约束，如为空值、约束、DEFAULT 定义等。 • (5)如果在视图定义中使用了 WITH CHECK OPTION 子句，则所有在视图上执行的数据修改语句都必须符合定义视图的 SELECT 语句中所设定的条件。如果使用了 WITH CHECK OPTION 子句，修改行时需注意不要让它们在修改完成后从视图中消失。任何可能导致行消失的修改都会被取消，并显示错误信息。 • (6)被修改的列不受 GROUP BY、HAVING 或 DISTINCT 子句的影响。

11.6 比较视图和查询 • 查询是对数据库内的数据进行检索、创建、修改或删除的特定请求。数据库接受用 SQL语言编写的查询。 • SQL命令也用于创建视图，视图是数据库数据的特定子集。视图和检索查询是用相同的语句(SQL SELECT 语句)定义的，因此非常相似。但在查询和视图之间也有很大的差别，下面列出了两者的几个区别。 • 1. 存储 • 2. 视图可以隐藏基表 • 3. 排序结果 • 4. 加密

11.7 实例研究 • 下面给出了几个在数据库中定义和使用视图的理由。 • 可以同时使用多个表中的数据。视图提供了一种机制，可将多个表中的列连接起来，使它们看起来像一个表，这样允许同时处理两个或多个相关表中的数据。 • 视图反映了最新信息。每次访问视图时，都会重新构建该视图，所以视图显示了基表中最新变化后的信息。 • 可以达到安全性或保密性的目的。从基表中选择特定字段定义视图，控制特定用户或用户组所能访问的数据。例如，只允许雇员看见工作跟踪表内记录其工作的行。又如，对于那些不负责处理工资单的雇员，只允许他们看见雇员表中的姓名列、办公室列、工作电话列和部门列，而不能看见任何包含工资信息或个人信息的列。 • 汇总信息而不提供明细信息。

任务 11 创 建 视 图