第6章 视图的创建与使用课件.ppt

《第6章 视图的创建与使用课件.ppt》由会员分享，可在线阅读，更多相关《第6章 视图的创建与使用课件.ppt（75页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL SERVER应应 用用 与与 开开 发发计算机工程技术学院（软件学院） 任淑美二00七年九月（六）（六）本课主要内容本课主要内容 视图的概念及优点创建视图查看视图信息使用视图删除视图本课核心知识点本课核心知识点视图和数据表之间的主要区别 视图的概念及优点 创建、修改和删除视图的方法 本课重点难点本课重点难点视图的概念及优点 创建、修改和删除视图的方法 通过视图修改数据表的方法 本课教学目的本课教学目的了解视图的概念和优点，能够在实际应用中设计合适的视图 了解视图和数据表之间的主要区别 掌握如何创建、修改和删除视图 掌握如何查看视图的信息 掌握如何通过视图操作数据表 第第6章章 视图的创

2、建和使用视图的创建和使用 视图就是查看表的一种方法。是对根据预定义的选择标准由一个或多个行的集合建立起来的动态表的静态定义。 6.1 概述概述 视图是查看数据库表中数据的一种方法。视图提供了存储预定义的查询语句作为数据库中的对象以备以后使用的能力。视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。视图的结果集通常不保存在数据库中，因此视图也称为虚拟表。视图的结果集动态包含在语句逻辑中并在运行时动态生成。 对其中所引用的基础表来说，视图的作用

3、类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。分布式查询也可用于定义使用多个异类源数据的视图。6.1.1 理解视图的概念理解视图的概念 【问题6-1】每天登陆BBS时，用户第一个浏览到的就是今日“十大热点”，该如何正确及时地显示出每天的十大热点新闻？ 较优的解决方案就是采用视图。视图的内容通常包括:1）基表的列的子集或行的子集2）两个或多个基表的联合3）两个或多个基表的连接4）基表的统计汇总（可以是经过对基表的各种复杂运算的结果）5）另外一个视图的子集（可基于表，也可基于另外一个视图）6）视图和基表的混合Topic帖子表。Users论坛用户表 Section版

4、块表 “十大热点”视图 。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。对其中所引用的基表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。视图被定义后便存放在数据库中，对视图中的数据的操作与对表的操作一样，可以对其进行查询、修改和删除，但对数据的操作要满足一定的条件。当对视图所看到的数据进行修改时，相应的基表的数据也会发生变化，同时，若基表的数据发生变化，这种变化也会自动地反映到视图中 。6.1.2 认识视图和查询的区别认识视图和查询的区别 虽然视图和查询有如此多的相似性，但是他们之间还是有很大的差异的，它们之间的差异体现在下

5、面几个方面。 存储，视图存储为数据库设计的一部分，而查询则不是。 更新结果，对视图和查询的结果集更新限制是不同的 。排序结果，可以排序任何查询结果生成查询计划数据库服务器可以在保存视图后立即为视图建立查询计划。加密，可以加密视图，但不能加密查询 6.1.3 理解视图的优点理解视图的优点 用户可以根据自己的实际需要创建视图，使用视图有很多优点，主要有以下几点：1、简单性视图可以屏蔽数据的复杂性，简化用户对数据库的操作。使用视图，用户可以不必了解数据库的结构，就可以方便地使用和管理数据。2、逻辑数据独立性视图可以使应用程序和数据库表在一定程度上独立。程序可以建立在视图之上，从而程序与数据库表被视图

6、分割开来。3、安全性通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。通过视图，用户可以被限制在数据的不同子集上。6.2 创建视图创建视图 创建视图的方法共有三种：使用企业管理器创建视图使用Transact-SQL语句中CREATE VIEW创建视图使用企业管理器的创建视图向导来创建视图在创建视图前请考虑如下原则：在创建视图前请考虑如下原则： 只能在当前数据库中创建视图。视图名称必须遵循标识符的规则，且对每个用户必须为唯一。可以在其它视图和引用视图的过程之上建立视图。SQL Server 2000 允许嵌套多达 32 级视图。不能将规则或 DEFAULT 定义

7、与视图相关联 不能创建临时视图，也不能在临时表上创建视图 若要创建视图，数据库所有者必须授予用户创建视图的权限，并且用户对视图定义中所引用的表或视图要有适当的权限 6.2.1 利用企业管理器创建视图利用企业管理器创建视图 使用企业管理器创建视图的具体操作步骤如下：1、打开企业管理器窗口，打开【新建视图】对话框。方法一：启动SQL SERVER企业管理器，依次单击【服务器组】【服务器】【数据库】【展开要创建视图的数据库】，在要创建视图的数据库上单击右键，依次单击【新建】【视图】，如图所示。方法二：启动SQL SERVER企业管理器，依次单击【服务器组】【服务器】【数据库】【展开要创建视图的数据库

8、】【视图】，单击右键，在弹出的快捷菜单中单击【新建视图】，如图 所示 。2、在【新建视图】对话框中，可以输入相应的SQL语句，如图所示。在【新建视图】对话框上部的空白部分，单击右键，选择【添加表】，或者单击工具栏上的按钮，出现【添加表】对话框，如图4所示。在【表】选项卡的列表框中选择相应的表作为创建视图的基表。在【视图】和【函数】选项卡中，可以选择需要的视图或者函数，并依此创建新的视图。 3、选中3个表Topic、Section和Users。再选择Topic表的ttopic字段、Section表中的Sname和SclickCount字段以及Users表中的uanme字段，如图所示。在【输出】中

9、，可以选择显示或者不显示该字段。在【排序类型】中可以选择升序或者降序。在【准则】中输入限制条件，可以限制输出的记录。或者在定义视图的查询语句中加入WHRER子句进行限制。4、右击字段定义对话框，选择【属性】进行设置，如图所示。其中“DISTINCE值”选择不输出重复的记录，“加密浏览”实现对视图定义加密，“顶端”限制视图最多输出的记录数目。5、单击 按钮或者右击空白处选择【运行】，可以在窗口的最下面看到结果数据的显示。6、单击 按钮或者右击空白处选择【保存】，在弹出的【另存为】对话框中输入视图的名字，并单击【确定】，即可完成视图的创建。 6.2.2 使用使用Transact-SQL语言创建视图

10、语言创建视图 用CREATE VIEW语句创建视图。语法格式语法格式:CREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 参数说明：参数说明：database_name:是创建视图的数据库名称。database_name必须是现有数据库的名称。如果不指定数据库，database_name默认为当前数据库。owner:是指定创建视图所有者的名字，owner必须是database_na

11、me所指定的数据库中的现有用户，owner默认为database_name所指定的数据库中与当前连接相关联的用户名。view_name：是视图的名称。视图名称必须符合标识符规则。可以选择是否指定视图所有者名称。column：是视图中的列名。只有在下列情况下，才必须命名 CREATE VIEW 中的列：当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。还可以在 SELECT 语句中指派列名。如果未指定 column，则视图列将获得与 SELECT 语句中的列相同的名称。 select_statement：是定义

12、视图的 SELECT 语句。该语句可以使用多个表或其它视图。若要从创建视图的 SELECT 子句所引用的对象中选择，必须具有适当的权限。视图不必是具体某个表的行和列的简单子集。可以用具有任意复杂性的 SELECT 子句，使用多个表或其它视图来创建视图。 对于视图定义中的 SELECT 子句有几个限制。CREATE VIEW 语句不能：语句不能： 包含 COMPUTE 或 COMPUTE BY 子句 。包含 ORDER BY 子句，除非在 SELECT 语句的选择列表中也有一个 TOP 子句 包含 INTO 关键字 引用临时表或表变量 WITH CHECK OPTION：强制视图上执行的所有数据

13、修改语句都必须符合由 select_statement 设置的准则。通过视图修改行时，WITH CHECK OPTION 可确保提交修改后，仍可通过视图看到修改的数据 WITH ENCRYPTION：表示 SQL Server 加密包含 CREATE VIEW 语句文本的系统表列。使用 WITH ENCRYPTION 可防止将视图作为 SQL Server 复制的一部分发布 SCHEMABINDING：将视图绑定到架构上。指定 SCHEMABINDING 时，select_statement 必须包含所引用的表、视图或用户定义函数的两部分名称 (owner.object) 不能除去参与用架构绑

14、定子句创建的视图中的表或视图，除非该视图已被除去或更改，不再具有架构绑定。否则，SQL Server 会产生错误。另外，如果对参与具有架构绑定的视图的表执行 ALTER TABLE 语句，而这些语句又会影响该架构绑定视图的定义，则这些语句将会失败 VIEW_METADATA：指定为引用视图的查询请求浏览模式的元数据时，SQL Server 将向 DBLIB、ODBC 和 OLE DB API 返回有关视图的元数据信息，而不是返回基表或表。浏览模式的元数据是由 SQL Server 向客户端 DB-LIB、ODBC 和 OLE DB API 返回的附加元数据，它允许客户端 API 实现可更新的客

15、户端游标。浏览模式的元数据包含有关结果集内的列所属的基表信息 。说明：说明： 只能在当前数据库中创建视图。视图最多可以引用 1,024 列 CREATE VIEW 权限默认授予 db_owner 和 db_ddladmin 固定数据库角色成员。sysadmin 固定服务器角色成员和 db_owner 固定数据库角色成员可以将 CREATE VIEW 权限转让给其他用户 若要创建视图，用户必须具有 CREATE VIEW 权限，还必须在视图所引用的表、视图和表值函数上具有 SELECT 权限，在视图所唤醒调用的标量值函数上具有 EXECUTE 权限。如果某个视图依赖于已除去的表（或视图），则当有

16、人试图使用该视图时，SQL Server 将产生错误信息。如果创建了新表或视图（该表的结构与以前的基表没有不同之处）以替换除去的表或视图，则视图将再次可用。如果新表或视图的结构发生更改，则必须除去并重新创建该视图。【例6-1】利用论坛用户表Users创建一个新的视图，命名为v_user1。要求显示级别大于等于3的用户的昵称，性别，等级和备注信息。USE bbsDBGOCREATE VIEW v_user1ASSELECT UID AS 作者编号 ,UName AS 作者 ,USex AS 性别 ,UClass AS 等级,UState AS 备注FROM Users WHERE UClass=

17、3【例6-2】利用论坛用户表Users、版块表Session和发帖表Topic创建一个新的视图，命名为v_toptopic。要求显示版块，主题，发帖人昵称以及帖子的点击数。并且对视图进行加密。USE bbsDBGOCREATE VIEW v_toptopicWITH ENCRYPTIONASSELECT Section.SName AS 版块 , Topic.TTopic AS 主题 , Users.UName AS 作者 , Topic.TClickCount AS 点击数FROM Section,Topic,UsersWHERE Topic.tuid=Users.uid AND Topic

18、.tsid=Section.sid 6.2.3 使用向导创建视图使用向导创建视图 1、在企业管理器中打开“创建视图向导”对话框。方法一：启动企业管理器，单击控制台树上的【服务器组】【服务器】【数据库】【展开要创建视图的数据库】，单击【工具】菜单【向导】，如图所示。在出现的“选择向导”对话框中单击“数据库”选项左侧的加号，在下一级选项中就可以看到“创建视图向导”选项，如图所示。方法二：启动SQL SERVER企业管理器，单击工具栏中的图标，就可以直接打开“选择向导”对话框。2、在“选项向导”对话框中双击【创建视图向导】选项，或者先选中【创建视图向导】选项，再单击【确定】按钮，会出现“欢迎使用创建

19、视图向导”对话框，如图所示。3、单击【下一步】按钮，出现“选择数据库”对话框，其下拉式对话框中列出了服务器中所有可以选用的数据库（如图所示）。 4、这里选择bbsDB数据库，单击【下一步】按钮，出现“选择基表”对话框，这里选择Seciton和Topic表，如左图所示。确定后单击【下一步】按钮，出现“选择字段”对话框，进行字段的选择，如右图所示。5、单击【下一步】按钮，出现“定义限制”对话框，如图所示。可以在其中的文本输入框中输入查询语句的条件，即SELECT语句中的WHRER子句的内容，用来限制视图中包含的字段的信息。默认时，视图将显示所选中字段的所有信息。6、单击【下一步】按钮，出现“命名视

20、图”对话框，如图所示。系统会自动给出一个默认的视图名称，用户也可以自己输入新的视图名称。7、单击【下一步】按钮，出现“完成创建”对话框。在该对话框的文本框中自动完成相应功能的Transact-SQL语句。6.3 查看视图信息查看视图信息 当视图创建成功后，SQL Server会在系统表中保存该视图的信息。这几个表分别是：Sysobject：存放视图的名称等基本信息。Syscolumn：存放视图中定义的列。Sysdepends：存放视图的依赖关系。Syscomments：存放定义的文本。在SQL Server中，可以使用企业管理器或者系统查询过程来查看系统表中存放的各种视图信息。6.3.1 使用

21、企业管理器查看视图信息使用企业管理器查看视图信息 通过企业管理器查看视图的基本信息，相关性和记录。1、找到要查看的视图启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【展开视图所在的数据库】【视图】【要查看的视图】。2、查看基本信息视图的基本信息包括视图名称、所有者、创建日期和源代码等。右击要查看的视图，在弹出的快捷菜单中选择【属性】选项。如图显示了topten视图的属性。3、查看相关性视图的基本信息包括视图名称、所有者、创建日期等。右击要查看的视图，在弹出的快捷菜单中依次选择【所有任务】【显示相关性】。图显示了topten视图的相关性。4、查看结构视图

22、的基本信息包括视图名称、所有者、创建日期等。右击要查看的视图，在弹出的快捷菜单中选择“设计视图”。5、查看记录视图的基本信息包括视图名称、所有者、创建日期等。右击要查看的视图，在弹出的快捷菜单中依次选择【打开视图】【返回所有行】，或者【返回特定行】即可。注意：注意：视图加密后，不能利用上述操作进行结构的查看。【例6-3】利用企业管理器查看视图v_toptopic的结构。操作：启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【bbsDB数据库】【视图】右击【v_toptopic视图】【打开视图】出现下图所示结果。分析：产生该现象的原因是由于v_toptop

23、ic视图在定义的时候有加密，加密的视图是不能通过企业管理器来查看视图结果的，因此出现该出错提示框。6.3.2 使用系统存储过程查看视图信息使用系统存储过程查看视图信息 有3个系统存储过程，可以显示有关视图的信息：sp_help view_name : 显示视图的所有者和创建时间sp_helptext view_name：显示视图的源代码sp_depends view r_name：显示该视图引用的对象清单【例6-4】查看视图v_user1的源代码。USE bbsDBGOSP_helptext v_user1【例6-5】查看视图v_toptopic的相关性。USE bbsDBGOSP_depen

24、ds v_toptopic6.4 修改和重命名视图修改和重命名视图 如果已定义的视图不再满足用户的要求，可以使用企业管理器或者使用Transact-SQL语句中的ALTER VIEW修改视图的定义，也可以进行重命名视图。6.4.1 修改视图修改视图 、使用企业管理器修改视图在企业管理器中，右击要进行修改的视图，从弹出的快捷菜单中选择【修改视图】选项，即出现修改视图窗口。修改视图的窗口跟创建视图的窗口相同，可以按照创建视图的方法进行视图的修改。2、使用Transact-SQL语句修改视图可用ALTER VIEW语句修改视图。语法格式：语法格式：ALTER VIEW . . view_name (

25、 column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 参数说明：参数说明：view_name：是视图的名称。column：是视图中的列名。select_statement：是定义视图的 SELECT 语句。 WITH CHECK OPTION：强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。WITH ENCRYPTION：表示 SQL Server 加密包含 CREATE VIEW 语句文本的

26、系统表列。注意：注意： （1）如果原来的视图定义是用 WITH ENCRYPTION 或 CHECK OPTION 创建的，那么只有在 ALTER VIEW 中也包含这些选项时，这些选项才有效。（2）只有在 ALTER VIEW 执行前后列名称不变的情况下，列上的权限才会保持不变。（3）如果使用 ALTER VIEW 更改当前正在使用的视图，则该视图上放一个排它架构锁。【例6-6】修改v_toptopic。要求只显示点击数大于1000的信息，并且取消视图的加密。USE bbsDBGOALTER VIEW v_toptopicASSELECT Section.SName AS 版块 , Topi

27、c.TTopic AS 主题 ,Users.UName AS 作者, Topic.TClickCount AS 点击数 FROM Section,Topic,Users WHERE Topic.tuid=Users.uid AND Topic.tsid=Section.sid AND Topic.TClickCount 1000 6.4.2 重命名视图重命名视图 1、使用企业管理器重命名视图在企业管理器中，右击要进行重命名的视图，从弹出的快捷菜单中选择【重命名】选项，当该视图名称变成可输入状态时，直接输入新的视图即可。2、使用Transact-SQL语句重命名视图可以使用系统存储过程sp_re

28、name重命名视图。具体语法格式如下：Sp_rename old_name,new_name【例6-7】将视图v_toptopic重命名为v_hoptopic。USE bbsDBGOSP_rename v_toptopic,v_hottopic6.5 使用视图使用视图对视图数据的修改实际上是对基表的修改。使用视图进行修改数据时，需要注意以下几点：1、不能影响超过一个的底层的表。可以修改从两个或更多表得到的视图，但是每次更新或修改只能影响一个表。2、不能对某些列进行。不允许改变是某个计算的结果的列。3、若修改影响了在视图中未引用的列，也有可能引起错误。4、若视图定义中指定了 WITH CHECK

29、 OPTION 选项，则修改被校验，以符合 SELECT 语句中指明的标准。6.5.1 在视图中插入数据在视图中插入数据1、使用企业管理器在视图中插入数据在企业管理器中，右击要进行操作的视图，从弹出的快捷菜单中依次选择【打开视图】【返回所有行】，直接在最后一行中直接插入新记录即可。2、使用Transact-SQL语句在视图中插入数据可以直接利用Transact-SQL中的INSERT语句向视图中插入数据。【例6-8】创建一个新视图v_section，要求包含内容有：版块的名称和本版格言，版块的的帖子数量。并利用INSERT语句向视图中插入一条记录。USE bbsDBGOCREATE VIEW

30、v_sectionASSELECT SName AS 版块名称, SStatement AS 本版格言, STopicCount AS 帖子数量FROM SectionINSERT INTO v_section VALUES (利用INSERT生成的新版块,创造全新的版块,2)上述语句执行后，不但在视图中可以看到新插入的记录，而且还可以在视图的基表section表中看到新插入的记录。6.5.2 更新视图中的数据更新视图中的数据1、使用企业管理器在视图中更新数据在企业管理器中，右击要进行操作的视图，从弹出的快捷菜单中依次选择【打开视图】【返回所有行】，在返回的记录中直接进行修改即可。2、使用Tr

31、ansact-SQL语句在视图中更新数据可以直接利用Transact-SQL中的UPDATE语句进行视图数据的更新。【例6-9】用UPDATE语句将上题中插入的记录的帖子数量改为12，然后查看视图和基表section中的所有数据。再修改视图v_section，要求必须视图中的帖子数量必须大于20，并查看相关表结果。USE bbsDBGOUPDATE v_section SET 帖子数量=12WHERE 版块名称=利用INSERT生成的新版块 SELECT * FROM v_sectionSELECT * FROM sectionALTER VIEW v_sectionASSELECT SNam

32、e AS 版块名称, SStatement AS 本版格言, STopicCount AS 帖子数量FROM SectionWHERE StopicCount20SELECT * FROM v_sectionSELECT * FROM section注意：注意： 如果原来的视图创建或者修改的时候定义了限制条，而插入或者修改的记录不满足该条件时，此时仍然可以往视图的基表中插入或者修改数据，只是用视图检索数据的时候不会显示而已。如果不想让这种情况发生，可以使用WITH CHECK OPTION选项限制插入或者修改不符合视图规则的记录。6.5.3 在视图中删除数据在视图中删除数据 1、使用企业管理器

33、在视图中删除数据在企业管理器中，右击要进行操作的视图，从弹出的快捷菜单中依次选择【打开视图】【返回所有行】，在返回的记录中直接进行删除改即可。2、使用Transact-SQL语句在视图中删除数据直接利用Transact-SQL中的DELETE语句删除视图的数据。【例6-10】先利用INSERT语句往视图v_section中插入一条记录，再用DELETE语句将插入的记录删除。USE bbsDBGOINSERT INTO v_sectionVALUES (为了删除而准备的版块 ,练习 ,22)DELETE FROM v_sectionWHERE 版块名称=为了删除而准备的版块6.6 删除视图删除视

34、图当不再需要某个视图时，可将用企业管理器或者使用Transact-SQL语句中的DROP VIEW将其删除。6.6.1 使用企业管理器删除视图使用企业管理器删除视图使用企业管理器删除视图的具体操作步骤如下：启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【展开视图所在的数据库】【视图】右击【要删除的视图】【删除】【全部除去】。6.6.2 使用使用Transact-SQL语言删除视图语言删除视图使用Transact-SQL语句中的DROP VIEW命令删除视图。具体语法格式如下：DROP VIEW view_name ,.n 该命令可以同时删除多个视图，只

35、要在各个视图名称之间用逗号格开即可。【例6-11】删除视图v_section。USE bbsDBGODORP VIEW v_section6.7 综合实例综合实例实例说明利用pubs数据库中的authors表中的数据生成视图，并对它进行查询，插入数据，删除等操作。关键代码分析-创建视图USE pubsIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = myview)DROP VIEW myviewGOCREATE VIEW myviewASSELECT au_lname, au_fname,

36、 ROWCOUNT AS barFROM authors WHERE state = UTGO-查询视图SELECT * FROM myview-在视图中插入数据INSERT INTO myview VALUES（a,b）-查看对应基表中的数据变化SELECT * FROM authors-删除视图DORP VIEW myview6.8 上机实训上机实训实训目的实训目的1了解视图和数据表之间的主要区别2理解视图的优点3掌握创建、修改和删除视图的方法4掌握通过视图修改数据的方法实训要求实训要求1按照题目要求在正确的工具中进行实验内容练习。2所有SQL语句要在查询分析器中编译和执行。3将所有SQL语句以.sql文件形式保存。实训内容实训内容1、使用企业管理器创建视图。2、使用向导创建视图 3、使用T-SQL创建视图 4、查看视图 5、操作视图 6、综合实训练习 本章小结本章小结视图的创建和使用比较简单，重要的是理解视图的概念，在实际的数据库应用系统合理设计视图来简化、优化系统开发。本章的知识点如下：视图是利用基表中的数据生成的一个虚拟的表。视图的主要作用是简化操作。创建视图使用CREATE VIEW语句视图的操作主要是应用INSERT,UPDATE和DELETE语句，和表的操作基本一致。