第9章 视图与索引

第9章视图与索引教学提示：在对数据库进行操作时，用户总是希望能够快速并准确得到所要求的数据，而适当使用视图和索引可以提高数据存取的性能及操作速度，加快查询数据的效率。教学目标：本章将详细介绍视图和索引的概念，以及创建和管理索引和视图的方法。9.1视图的基础知识1.什么是视图？2.视图的作用、优点？9.1视图的基础知识视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图实际上就是给查询语句指定一个名字，将查询语句定义为一个独立的对象保存。9.1.1视图的概念视图是一个虚拟表，并不表示任何物理数据，只是用来查看数据的窗口而已视图并不是以一组数据的形式存储在数据库中，数据库中只存储视图的定义，而不存储视图对应的数据视图中的数据行和列都来自于基本表，是在视图被引用时动态生成的视图由视图名和视图定义两部分组成可以在视图的基础上再定义视图。9.1.2视图的作用使用视图主要有以下几个方面的作用：（P132-133页）(1)为用户集中数据，简化用户的数据查询和处理(2)简化用户权限的管理，也增加了安全性(3)屏蔽数据库的复杂性(4)可重新组织数据以便输出到其他应用程序中9.1.3视图的类型SQLServer2008中，视图可以分为标准视图、索引视图和分区视图。1.标准视图（本阶段主要学习使用）标准视图组合了一个或多个表中的数据，可以获得使用视图的大多数好处，可以实现对数据库的查询、修改和删除等基本操作。2.索引视图索引视图是被具体化了的视图，它已经过计算并存储。可以为视图创建索引，即对视图创建一个唯一的聚集索引。索引视图可以显著提高某些类型查询的性能。索引视图尤其适于聚合许多行的查询。但不太适合于经常更新的基本数据集。3.分区视图（本阶段不学习使用）分区视图在一台或多台服务器间水平连接一组成员表中分区数据这样，数据看上去如同来自于一个表。9.2创建视图SQLServer提供两种方法创建视图：1.使用SQLServerManagementStudio工具创建视图2.使用Transact-SQL语句中的CREATEVIEW创建视图9.2.1使用SSMS工具创建视图在SSMS中创建视图简单直观且方便，步骤如下：1)打开“SSMS”窗口，“对象资源管理器”“数据库”选项，展开要建立视图的具体数据库“视图”“新建视图”2)在打开的“添加表”选中创建视图的表，可以用Ctrl键和Shift键配合鼠标以选择多张表3)到视图的设计窗口，设计视图4)可以继续添加表或移除表5)可以为创建的视图添加或删除列，还可以修改某些列的属性，如列别名、排序方式和顺序、一些约束等，第三个窗格中的SQL语句有相应变化6)在创建的视图中添加好列和列的属性之后，单击执行SQL语句创建视图。这时在第四个窗口中会看到查询语句执行的结果。如图9-3所示。7)单击保存按钮，在弹出的图9-4的选择名称对话框中输入视图的名字9.2.2使用TSQL语句创建视图使用CREATEVIEW语句创建视图的语法如下：CREATEVIEW[schema_name]view_name[(column[，…n])][WITHview_attribute[,…n]]ASselect_statement[WITHCHECKOPTION]其中参数解释参见P135页视图的创建创建视图的基本语法如下：CREATEVIEW视图名[(视图列名表)][WITHENCRYPTION]ASSelect_语句其中：WITHENCRYPTION子句对视图进行加密。此命令必须是GO指令后的第一条！注意：CREATEVIEW必须是查询批处理中的第一句。视图定义中的SELECT子句不能包含下列内容：1)COMPUTE或COMPUTEBY子句。2)ORDERBY子句，除非在SELECT语句的选择列表中也有一个TOP子句。3)INTO关键字。4)OPTION子句。5)引用临时表或表变量。如果视图中的某一列是一个算术表达式、构造函数或者常数，而且视图中两个或者更多的不同列拥有一个相同的名字(这种情况通常是因为在视图的定义中有一个连接，而且这两个或者多个来自不同表的列拥有相同的名字)，此时，用户需要为视图的每一列指定列的名称。【例9-1】创建完整的计算机系部门的读者借阅信息视图“计算机系读者借阅情况”，并禁止用户查看视图的定义语句。创建视图可以使用如下语句：CREATEVIEW计算机系读者借阅情况WITHENCRYPTION--加密ASSELECTU.UserId,U.UserName,UserSex,CateName,UserDep,UserAdd,UserTel,UserReg,UserBkNum,BookId,LendDate,RtnDateFROMUserTbUjoinLendingLonU.UserId=L.UserIdWHEREUserDeplike'%计算机系%'GO执行查询视图：SELECT*FROM计算机系读者借阅情况【例】使用T-SQL语句在book数据库中创建一个名为v_book2的视图。该视图仅显示book2表中的“书名”和“定价”列。(本例学习视图应用——基本表的列的子集)GOCREATEVIEWv_book2ASSELECT书名,定价FROMbook2【例】使用T-SQL语句创建视图v_bookbycbs，使其能显示各出版社已出版书的本数(本例学习视图应用——基本表的统计汇总)goCREATEVIEWv_bookbycbsASSELECT出版社,COUNT(*)as‘出版总数’FROMbook1GROUPBY出版社再运行如下命令：select*fromv_bookbycbs要点：计算结果一定要指定列名9.3修改、删除和重命名视图9.3.1修改视图修改视图的定义有两种方法：1.使用SSMS管理器工具2.使用Transact-SQL语句修改视图定义使用ALTERVIEW语句修改视图定义：使用Transact-SQL语句修改视图定义需要使用ALTERVIEW语句，ALTERVIEW语句的语法格式如下：ALTERVIEW[schema_name]view_name[(column[,…n])][WITHview_attribute[,…n]]ASselect_statement[WITHCHECKOPTION]各个子句的说明与创建视图的语句一致!!!视图是虚表，其中并无数据，建议删除再创建，无需记忆修改指令句法。【例9-2】修改9.2.2节【例9-1】所创建的“计算机系读者借阅情况”视图，使其显示计算机系教师读者的借阅情况，且不需要显示读者的地址和电话信息。修改视图定义的代码如下：ALTERVIEW计算机系读者借阅情况WITHENCRYPTIONASSELECTU.UserId,U.UserName,UserSex,CateName,UserDep,UserReg,UserBkNum,BookId,LendDate,RtnDateFROMUserTbUjoinLendingLonU.UserId=L.UserIdWHEREUserDeplike'%计算机系%'andCateName='教师'GOSELECT*FROM计算机系读者借阅情况9.3.2删除视图1.使用SSMS工具删除视图2.使用DROPVIEW语句删除视图语法形式如下：DROPVIEW[schema_name]view_name[,…n]9.3.3重命名视图1.在管理器中，选择要重命名的视图，右键单击，在弹出的快捷菜单中选择“重命名”选项即可。2.EXECsp_rename'object_name','new_name'其中：object_name：当前的视图名new_name：指定对象的新名称。【例9-3】将视图“计算机系读者借阅情况”重命名为“计算机系教师借阅情况”EXECsp_rename'计算机系读者借阅情况','计算机系教师借阅情况'9.4使用视图操作数据表除了在SELECT中使用视图作为数据源进行查询以外，还可以通过视图对数据表的数据进行添加、修改和删除的操作。使用视图对数据表的记录进行操作时，所创建的视图必须满足的要求：1)修改视图中的数据时，不能同时修改两个或者多个基本表，当对基于两个或多个表创建的视图进行修改时，每次的修改只能影响一张基本表。2)视图的字段中不能包含计算列，计算列是不能更新的。9.4使用视图操作数据表3)如果在创建视图时指定了WITHCHECKOPTION选项，那么使用视图修改数据库时，必须保证修改后的数据满足视图定义的要求。4)如果在视图定义中使用了GROUPBY、UNION、DISTINCT或TOP子句，则视图不允许更新。5)如果在视图定义中有嵌套查询，并且内层查询的FROM子句涉及的表也是导出该视图的基本表，则视图不允许更新。通过视图插入数据1.插入(INSERT)条件：视图来自单表，且非空项均需在视图中。【例】创建视图v_book2CREATEVIEWv_book2ASSELECT书名,定价FROMbook2【例】向视图v_book2中插入一本书('硬件测试'，50)。INSERTINTOv_book2VALUES('硬件测试',50)错误结果。原表的主键：编号为非空约束，但未出现在视图中USELibraryGOCREATEVIEWv_book2_iASSELECT编号,书名FROMbook2gogoINSERTINTOv_book2_iVALUES('i002','AAA')go通过视图更新数据2.修改(UPDATE)和删除(DELETE)updatev_book2_iset书名='数据库'where编号='i002‘goDeletefromv_book2_iwhere编号='i002'9.5查看视图信息SQLServer允许用户获得视图的一些信息，如视图的基本信息、的定义信息、视图与其他对象间的依赖关系等。这些信息可以通过相应的系统存储过程来查看。1.查看视图的基本信息可以使用系统存储过程sp_help来显示视图的名称、所有者、创建时间、列信息等。如图9-7所示，查看视图book_view的基本信息。2.查看视图的定义信息如果视图在创建的时候没有被加密，则可以使用系统存储过程sp_helptext显示视图的定义信息。【例9-4】查看视图“book_view”的定义信息EXECsp_helptextbook_view在查询页中输入以上代码，单击按钮，结果如图9-8所示。2.查看视图的定义信息【例9-5】查看视图“计算机系教师借阅信息”的定义信息EXECsp_helptext计算机系教师借阅情况在查询页中输入以上代码，单击按钮，结果如图9-9所示。因为在创建视图“计算机系教师借阅情况”时已加密，所以查询结果给出文本已加密的提示。3.查看视图与其他对象间的依赖关系使用系统存储过程sp_depends查看视图与其他对象间的依赖关系，如，视图在哪些表的基础上创建、有哪些数据库对象的定义引用了该视图等。【例9-6】查看视图“计算机系教师借阅情况”的依赖关系EXECsp_depends计算机系教师借阅情况在查询页中输入以上代码，单击按钮，结果如图9-10所示。视图的应用练习：例create_view语句1）每位同学的平均成绩视图A_GRADE1(姓名,平均成绩)CREATEVIEWA_GRADE1(姓名,平均成绩)ASSELECTS.姓名,AVG(成绩)FROM学生信息表S,成绩表SCWHERES.学号=SC.学号AND成绩ISNOTNULLGROUPBYS.学号;视图的应用练习：例create_view语句2）每门课的平均成绩A_GRADE2(课程名称,平均成绩)CREATEVIEWA_GRADE2ASSELECT名称as课程名称,AVG(成绩)as平均成绩FROM成绩表SC,课程信息表CWHERESC.课程代码=C.课程代码AND成绩ISNOTNULLGROUPBY名称;视图的应用练习：例create_view语句3）成绩视图CG(学