第6章 视图的创建与使用

SQLServer20052020/1/281第6章视图的创建与使用SQLServer200522020/1/28第6章视图的创建与使用视图简介1创建视图2管理视图3通过视图修改数据45SQLServer200532020/1/286.1视图简介视图作为一种数据库对象，可以让用户对数据源进行查询和修改。视图的数据源可以是一个或多个表，也可以是其他的视图，这取决于对视图的定义。视图可以被看成是虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。数据库中存储的是SELECT语句。SQLServer200542020/1/28视图的形成SQLServer200552020/1/28用户利用视图对数据进行操作比用户直接对数据源表操作有较多的优势，其主要优点如下：简化数据操作数据安全访问机制自定义所需数据从多个表中汇总数据通过视图可以修改数据SQLServer200562020/1/286.2创建视图用户可以利用SQLServerManagementStudio对象资源管理器和CREATEVIEW命令创建视图。在创建视图前应注意如下原则：只能在当前数据库中创建视图。但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其他数据库甚至其他服务器中。视图名称必须遵循标识符的规则，且对每个架构都必须唯一。此外，该名称不得与该架构包含的任何表的名称相同。视图仅当在基表上有SELECT权限是才能被创建。定义视图的查询不能包含COMPUTE子句、COMPUTEBY子句或INTO关键字。定义视图的查询不能包含ORDERBY子句，除非在SELECT语句的选择列表中还有一个TOP子句。不能创建临时视图，也不能对临时表创建视图。SQLServer200572020/1/286.2.1使用SQLServerManagementStudio创建视图具体操作步骤如下：进入新建视图对话框：打开SQLServerManagementStudio对象资源管理器，将要创建视图的数据库展开，右击视图图标，从弹出的快捷菜单中单击“新建视图”选项，进入新建视图对话框，如图6-2所示。SQLServer200582020/1/28为视图选择数据源：在打开的新建视图对话框中，在其上部的空白处单击鼠标右键，从弹出的快捷菜单中选择“添加表”选项，或者点击“查询设计器”菜单中的“添加表”菜单项，将出现“添加表”对话框，如图6-3所示。SQLServer200592020/1/28选择视图的输出字段（表达式）：通过单击字段左边的复选框选择需要的字段。SQLServer2005102020/1/28单击工具栏中的“执行”按钮，或者右击视图设计窗口的空白区域，在弹出的快捷菜单中选择“执行SQL”菜单项，则可以运行视图，在窗口的下面将显示查询结果。单击工具栏菜单中的“保存”按钮，或者点击“文件”菜单中的“保存视图”。SQLServer2005112020/1/286.2.2使用Transact-SQL语句创建视图使用CREATEVIEW语句创建视图，其语法格式如下：CREATEVIEW[schema_name.]view_name[(column[,...n])][WITHview_attribute[,...n]]ASselect_statement[WITHCHECKOPTION][;]view_attribute::={[ENCRYPTION][SCHEMABINDING]}指定视图所属架构的名称用于指定新建视图的名称用于指定视图中的字段的名称用于创建视图的SELECT语句强制视图上执行的所有数据修改语句都必须符合由select_statement设置的准则将新建的视图加密SQLServer2005122020/1/28例6.1利用Seller表查询销售员的编号、姓名、性别、地址。UsesalesGOCREATEVIEWdbo.V_Seller(编号,姓名,性别,地址)ASSELECTSaleID,Salename,Sex,AddressFROMSellerSQLServer2005132020/1/28例6.2在sales数据库中创建如下视图：利用Custmoer、Orders、OrderDetail三个表来查询定单数量在50和100之间的客户编号、公司名称、产品编号、定单编号，订单日期，订单数量。CREATEVIEWV_customerASSELECTCustomer.CustomerID,Customer.CompanyName,OrderDetail.ProductID,Orders.OrderIDASOrder_ID,Orders.OrderDate,OrderDetail.QuantityFROMCustomerINNERJOINOrdersONCustomer.CustomerID=Orders.CustomerIDINNERJOINOrderDetailONOrders.OrderID=OrderDetail.OrderIDWHERE(OrderDetail.Quantity50)AND(OrderDetail.Quantity100)SQLServer2005142020/1/28例6.3在sales数据库中创建如下视图：利用OrderDetail表和Product表查询每种产品销售的总价值。USEsalesGOCREATEVIEWV_Sale_Total(定单编号,产品名称,销售总价值)WITHENCRYPTIONASSELECTOrderID,ProductName,Price*QuantityFROMOrderDetailINNERJOINProductONOrderDetail.ProductID=Product.ProductIDSQLServer2005152020/1/286.2.3创建索引视图对于标准视图而言，为每个引用视图的查询动态生成结果集的开销很大，特别是对于那些涉及对大量行进行复杂处理（如聚合大量数据或联接许多行）的视图。如果在查询中频繁地引用这类视图，可通过对视图创建唯一聚集索引来提高性能。对视图创建唯一聚集索引后，结果集将存储在数据库中，就像带有聚集索引的表一样。SQLServer2005162020/1/28创建索引视图有许多限制条件：创建索引视图的前后必须确认会话的SET选项的设置是否正确。必须使用WITHSCHEMABINDING选项创建视图。在视图上必须先创建唯一的聚集索引等。SQLServer2005172020/1/28例6.4本例中创建了数据源表student,以及基于该表的索引视图view1。CREATETABLEstudent(numintPRIMARYKEY,namevarchar(8),class_namevarchar(20))GOCREATEVIEWview1WITHSCHEMABINDINGASSELECTnum,name,class_nameFROMdbo.studentWHEREnumBETWEEN1AND100GOCREATEUNIQUECLUSTEREDINDEXindex_numONview1(num)SQLServer2005182020/1/286.2.4创建分区视图分区视图是通过对成员表使用UNIONALL所定义的视图，这些成员表的结构相同，但作为多个表分别存储在同一个SQLServer实例中，或存储在称为联合数据库服务器的自主SQLServer服务器实例组中。本地分区视图：本地分区视图中的所有参与表和视图都位于同一个SQLServer实例上。分布式分区视图：分布式分区视图中，至少有一个参与表位于不同的（远程）服务器上。SQLServer2005192020/1/28例如，Customer表的数据分布在三个服务器位置的三个成员表中：Server1上的Customer_33、Server2上的Customer_66和Server3上的Customer_99。那么在Server1的分区视图是通过以下方式定义的：CREATEVIEWview_CustomerASSELECT*FROMCompanyData.dbo.Customer_33UNIONALLSELECT*FROMServer2.CompanyData.dbo.Customer_66UNIONALLSELECT*FROMServer3.CompanyData.dbo.Customer_99服务器名数据库名表名SQLServer2005202020/1/286.3管理视图查看视图信息使用sp_help显示视图的特征例6.5显示视图V_Quantity的特征信息。USEsalesGOsp_helpV_QuantitySQLServer2005212020/1/28使用sp_helptext显示视图在系统表中的定义例6.6显示视图V_Quantity的在系统表中的定义。UsesalesGosp_helptextV_Quantity使用sp_depends显示视图对表的依赖关系和引用的字段例6.7显示视图V_Quantity的表依赖关系和引用的字段情况。UsesalesGosp_dependsV_QuantitySQLServer2005222020/1/28修改视图ALTERVIEW[schema_name.]view_name[(column[,...n])][WITHview_attribute[,...n]]ASselect_statement[WITHCHECKOPTION][;]其中ALTERVIEW中各参数的含义与创建视图CREATEVIEW命令中的参数含义相同。SQLServer2005232020/1/28例6.8利用ALTER命令去除视图V_Sale_Total的加密属性。UsesalesGOAlterviewV_Sale_Total(定单编号,产品名称,销售总价值)ASSELECTOrderID,ProductName,Price*QuantityFROMOrderDetailINNERJOINProductONOrderDetail.ProductID=Product.ProductIDSQLServer2005242020/1/28重命名视图：sp_renameold_name,new_name例6.9将视图V_customer重命名为V_customer1sp_renameV_customer,V_customer1删除视图：DROPVIEW{view}[,...n]例6.10删除视图V_Customer。DROPVIEWV_CustomerSQLServer2005252020/1/286.4通过视图修改数据利用视图对创建它的数据源（表或其他视图）进行一定的修改，但使用视图修改数据源时需要注意以下几点：对视图中的数据进行修改（包括UPDATE、INSERT和DELETE），不能同时修改两个或多个基表中的数据。也就是说利用视图修改数据时每次修改只能影响一个基表。不能修改那些通过表达式计算得到的字段值，例如包含计算值或者合计函数的字段。若用户在创建视图时，指定了WITHCHECKOPTION选项，那么对视图进行UPDAT或INSERT操作时，要保证更新或插入的数据满足视图定义的范围。用户如果想通过视图执行更新和删除命令时，则要操作的数据必须包含在视图的结果集中，否则不能完成该操作。SQLServer2005262020/1/286.4.1利用视图插入新记录插入新记录时须注意以下内容：1）如果新插入的记录不符合视图定义的查询条件，但是视图定义时没有使用WITHCHECKOPTION选项，那么该记录仍然可以通过视图插入到基表中，但再次运行视图时，新记录不会在视图中显示。2）如果在视图的定义中使用了WITHCHECKOPTION选项，则当向视图中插入不符合视图查询条件