《视图的创建与使用汇总课件.ppt》由会员分享，可在线阅读，更多相关《视图的创建与使用汇总课件.ppt（40页珍藏版）》请在三一办公上搜索。
1、2022/12/7,1,第6章 视图的创建与使用,2,2022/12/7,用户对数据源进行查询时，常常会对数据进行更新、删除及添加新记录等操作，前面介绍的查询得到的结果是只读的，当需要对查找结果进行修改并将其返回数据源表时，就需要用到视图。,3,2022/12/7,主要内容,视图概述,1,创建视图,2,管理视图,3,通过视图修改数据,4,5,4,2022/12/7,6.1 视图概述,视图是从一张或多张表中导出的表，所以称为虚拟表，没有自己的真实数据，是一些SQL查询语句的集合，其结构和内容是建立在对表的查询基础之上的，和表一样包括行和列。在SQL语句中引用视图的方式也与引用表的方式相同。 视图
2、是一个虚拟表，其内容由查询语句定义生成。表是视图的基础。数据库中只存储了视图定义，而不存放视图所对应的数据，视图所对应的数据仍存放在视图所引用的基表中。 视图中的数据是视图在被使用时动态生成的,它随着基表数据的变化而发生变化。,5,2022/12/7,视图的形成,6,2022/12/7,视图为数据的呈现提供了多种多样的表现形式，用户可以通过它浏览表中感兴趣的数据，用户利用视图对数据进行操作比用户直接对数据源表操作有较多的优势，其主要优点如下： 简化数据操作 数据安全访问机制 自定义所需数据 从多个表中汇总数据 通过视图可以修改数据,7,2022/12/7,在SQL Server 2005中视图
3、分为以下3类。 （1）标准视图 （2）索引视图 （3）分区视图,视图分类,8,2022/12/7,创建视图时的注意事项,创建者必须拥有创建视图的权限，对视图中引用的基表或视图有许可权；只能在当前数据库中创建视图；但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其他数据库甚至其他服务器中。在一个视图中最多引用1024列，视图中记录的行数限制由基表中记录数目决定； 视图的名称必须遵循标识符的定义规则，对于每个用户来说也必须唯一，而且视图名称不允许与该用户拥有的表重名；视图中列的名称一般继承其基表中列的名称，如果视图中某一列是算术表达式、函数、常量或者来自多个表的列名相同，必须要
4、为视图中的列重新定义名称；,9,2022/12/7,可以将视图创建在其他视图上，SQL Server 2005中允许32层的视图嵌套。不能在视图上创建全文索引，不能将规则、默认绑定在视图上。不能在临时表上创建视图，也不能创建临时视图。 定义视图的查询语句中不能包含计算子句COMPUTE、COMPUTE BY子句和INTO关键字。,10,2022/12/7,6.2 创建视图,用户可以利用SSMS对象资源管理器和CREATE VIEW命令创建视图。利用SSMS创建视图的具体操作步骤：进入新建视图对话框：将要创建视图的数据库展开，右击视图图标，从弹出的快捷菜单中单击“新建视图”选项，进入新建视图对话
5、框，如图6-2所示。,11,2022/12/7,6.2.1 使用SQL Server Management Studio创建视图,12,2022/12/7,为视图选择数据源 ：在打开的新建视图对话框中，在其上部的空白处单击鼠标右键，从弹出的快捷菜单中选择“添加表”选项，或者点击“查询设计器”菜单中的“添加表”菜单项，将出现“添加表”对话框，如图6-3所示。,13,2022/12/7,选择视图的输出字段（表达式）：通过单击字段左边的复选框选择需要的字段 。,14,2022/12/7,单击工具栏中的“执行”按钮，或者右击视图设计窗口的空白区域，在弹出的快捷菜单中选择“执行SQL”菜单项，则可以运行
6、视图，在窗口的下面将显示查询结果。单击工具栏菜单中的“保存”按钮，或者点击“文件”菜单中的“保存视图”。,15,2022/12/7,6.2.2 使用Transact-SQL语句创建视图,使用CREATE VIEW语句创建视图，其语法格式如下：CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION ; := ENCRYPTION SCHEMABINDING ,指定视图所属架构的名称,用于指定新建视图的名称,用于指定视图中的字段的名称 ，可省略,用于创建视图的S
7、ELECT语句,强制视图上执行的所有数据修改语句都必须符合由select_statement设置的准则,将新建的视图加密,16,2022/12/7,例6.1 利用Seller表查询销售员的编号、姓名、性别、地址。Use sales GOCREATE VIEW dbo.V_Seller (编号,姓名,性别,地址)ASSELECT SaleID, Salename, Sex, AddressFROM Seller,17,2022/12/7,例6.2 在sales数据库中创建如下视图：利用Custmoer、Orders、OrderDetail三个表来查询定单数量在50和100之间的客户编号、公司名称
8、、产品编号、定单编号，订单日期，订单数量。CREATE VIEW V_customer ASSELECT Customer.CustomerID, Customer.CompanyName, OrderDetail.ProductID, Orders.OrderID AS Order_ID, Orders.OrderDate, OrderDetail.QuantityFROM Customer INNER JOIN Orders ONCustomer.CustomerID = Orders.CustomerID INNER JOIN OrderDetail ON Orders.OrderID
9、= OrderDetail.OrderIDWHERE (OrderDetail.Quantity 50) AND(OrderDetail.Quantity 100),18,2022/12/7,例6.3 在sales数据库中创建如下视图：利用OrderDetail表和Product表查询每种产品销售的总价值。USE salesGOCREATE VIEW V_Sale_Total (定单编号,产品名称,销售总价值) WITH ENCRYPTIONASSELECT OrderID, ProductName, Price *QuantityFROM OrderDetail INNER JOIN Pro
10、duct ON OrderDetail.ProductID =Product.ProductID,19,2022/12/7,6.2.3 创建索引视图,对于标准视图而言，为每个引用视图的查询动态生成结果集的开销很大，特别是对于那些涉及对大量行进行复杂处理（如聚合大量数据或联接许多行）的视图。如果在查询中频繁地引用这类视图，可通过对视图创建唯一聚集索引来提高性能。对视图创建唯一聚集索引后，结果集将存储在数据库中，就像带有聚集索引的表一样。,20,2022/12/7,创建索引视图有许多限制条件：创建索引视图的前后必须确认会话的 SET 选项的设置是否正确；必须使用 WITH SCHEMABINDIN
11、G 选项创建视图，语句中引用到的对象必须由架构名和对象名两部分组成；在视图上必须先创建唯一的聚集索引等。,21,2022/12/7,例6.4 本例中创建了数据源表student,以及基于该表的索引视图view_stu。CREATE TABLE student(num int PRIMARY KEY, name varchar(8),class_name varchar(20)GOCREATE VIEW view_stu WITH SCHEMABINDING ASSELECT num, name, class_nameFROM dbo.student WHERE num BETWEEN 1 AN
12、D 100GOCREATE UNIQUE CLUSTERED INDEX index_num ON view_stu(num),22,2022/12/7,6.2.4 创建分区视图,分区视图是通过对成员表使用 UNION ALL 所定义的视图，这些成员表的结构相同，但作为多个表分别存储在同一个 SQL Server 实例中，或存储在称为联合数据库服务器的自主 SQL Server 服务器实例组中。 本地分区视图：本地分区视图中的所有参与表和视图都位于同一个 SQL Server 实例上。分布式分区视图：分布式分区视图中，至少有一个参与表位于不同的（远程）服务器上。,23,2022/12/7,例：
13、Customer表的数据分布在三个服务器位置的三个成员表中：Server1 上的 Customer_33、Server2 上的 Customer_66 和 Server3 上的 Customer_99。 那么在Server1 的分区视图是通过以下方式定义的：CREATE VIEW view_CustomerASSELECT * FROM CompanyData.dbo.Customer_33UNION ALLSELECT * FROM Server2.CompanyData.dbo.Customer_66UNION ALLSELECT * FROM Server3.CompanyData.db
14、o.Customer_99,服务器名,数据库名,表名,24,2022/12/7,6.3 管理视图,查看视图信息 使用sp_help显示视图的特征 例6.5 显示视图view_stu的特征信息。USE studentGO sp_help view_stu,25,2022/12/7,26,2022/12/7,使用sp_helptext显示视图在系统表中的定义 例6.6 显示视图view_stu的在系统表中的定义。Use studentGo sp_helptext view_stu,27,2022/12/7,使用sp_depends显示视图对表的依赖关系和引用的字段例6.7 显示视图view-stu
15、的表依赖关系和引用的字段情况。Use studentGo sp_depends view_stu,28,2022/12/7,修改视图,ALTER VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION ; ,29,2022/12/7,例6.8 利用ALTER命令去除视图V_Sale_Total的加密属性。Use sales GOAlter view V_Sale_Total (定单编号, 产品名称,销售总价值)ASSELECT OrderID,ProductName, P
16、rice * Quantity FROM OrderDetail INNER JOIN Product ON OrderDetail.ProductID = Product.ProductID,30,2022/12/7,重命名视图和删除视图,重命名视图sp_rename old_name, new_name例6.9 将视图V_customer重命名为V_customer1sp_rename V_customer, V_customer1 删除视图 ：DROP VIEW view ,.n 例6.10 删除视图V_Customer。DROP VIEW V_Customer删除视图时，底层数据表不受
17、影响，但会造成与该视图关联的权限丢失。,31,2022/12/7,先判断，再删除，避免重复删除出错,use studentgoif EXISTS(select name from sys.views where name=view_stu) 先判断如果系统中有名为view_stu 的视图，再删除DROP view view_stu,32,2022/12/7,6.4 通过视图修改数据,可以利用视图对创建它的数据源（表或其他视图）进行一定的修改，但使用视图修改数据源时需要注意以下几点： 对视图中的数据进行修改（包括 UPDATE、INSERT 和 DELETE ），不能同时修改两个或多个基表中的数
18、据。不能修改那些通过表达式计算得到的字段值，例如包含计算值或者聚合函数的字段。若用户在创建视图时，指定了WITH CHECK OPTION 选项，那么对视图进行UPDAT或INSERT操作时，要保证更新或插入的数据满足视图定义的范围。用户如果想通过视图执行更新和删除命令时，则要操作的数据必须包含在视图的结果集中，否则不能完成该操作。,33,2022/12/7,6.4.1 利用视图插入新记录,插入新记录时须注意以下内容：1）如果新插入的记录不符合视图定义的查询条件，但是视图定义时没有使用WITH CHECK OPTION选项，那么该记录仍然可以通过视图插入到基表中，但再次运行视图时，新记录不会在
19、视图中显示。2）如果在视图的定义中使用了WITH CHECK OPTION选项，则当向视图中插入不符合视图查询条件的记录时，则系统报错。3）如果基表中定义了某些约束条件或触发器，当插入的记录违反这些限制时，则不能将该记录插入到视图和基表中。,34,2022/12/7,例6.11 首先基于Product表创建一个新视图文件V_stocks，要求包含库存stocks值在300以上的记录。CREATE VIEW V_stocks ASSELECT *FROM Product WHERE stocks300创建视图后，输入以下命令，向视图中插入一条新数据。INSERT INTO V_stocks VA
20、LUES (P03008,冰糖,3,6.0,200),35,2022/12/7,给 使用了WITH CHECK OPTION选项的视图 添加数据,例6.12 CREATE VIEW V_stocks ASSELECT *FROM Product WHERE stocks300WITH CHECK OPTION创建视图后，输入以下命令，向视图中插入一条新数据。INSERT INTO V_stockSVALUES (P03010,雪饼,3,15.0,200)SELECT * FROM V_stocks查看是否有刚插入的记录,36,2022/12/7,6.4.2 利用视图更新记录,使用视图可以更新数
21、据，但更新的只是数据库中基表的数据记录。更新的列应属于同一个表，而且修改后的值同样会受到WITH CHECK OPTION选项和数据源的约束的影响。,37,2022/12/7,例6.13 用前面已经创建的视图V_Seller，输入如下命令：USE salesGOUPDATE V_SellerSET 地址=保定职大路1号WHERE 编号=s10执行以上命令后，把职工编号为s10的记录中的地址字段值进行更改。,38,2022/12/7,例6.14 用前面已经创建的视图V_stocks，输入如下命令：USE salesGOUPDATE V_stocksSET stocks=200WHERE Prod
22、uctID=p01006执行以上命令行时，系统将弹出错误信息“服务器: 消息 550，级别 16，状态 1，行 1试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束的条件。语句已终止。”,39,2022/12/7,6.4.3 利用视图删除记录,使用视图删除记录的方法与删除表中记录相同，删除的只是数据库中基表的数据记录。例6.15 利用视图V_stocks删除满足一定条件的记录。USE salesGODELETE FROM V_stocks WHERE ProductID=p03005,40,2022/12/7,6.5 本章小结,利用SSMS和T-SQL语句创建视图；视图的管理，即查看、修改和删除视图，利用视图来修改数据源数据。,
