视图和索引讲义课件

第6章

视图和索引

学习目标

1、熟练掌握视图的创建、修改、删除

2、灵活运用视图简化查询操作

3、使用视图实现数据库的安全管理

4、了解索引的作用、使用索引来提高检索的效率

5、熟练掌握索引的创建、删除

6、分析、规划和维护索引第6章

视图和索引目录小结视图的基本概念6.1视图的创建和查询6.2视图的维护6.3

通过视图修改表数据6.4创建索引6.6管理和维护索引6.7索引概述6.56.1视图的基本概念6.1.1视图的基本概念

视图是一种在一个或多个表上观察数据的途径，可以把视图看做是一个能把焦点定在用户感兴趣的数据上的监视器。视图是一种数据库对象，视图是从一个或者多个表或视图中导出的虚拟表，其结构和数据是建立在对表的查询基础上的。上一页返回目录下一页6.1视图的基本概念

和真实的表一样，视图也包括多个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的基表。

视图中的数据是通过视图定义语句由其基本表中动态查询得来的。

因此，视图不是真实存在的基础表而是一个虚拟表。视图被定义后便存储在数据库中。上一页下一页返回目录6.1视图的基本概念

在视图的实现上就是由SELECT语句构成的，基于选择查询的虚拟表。

其内容是通过选择查询来定义的，数据的形式和表一样由行和列组成，而且可以像表一样作为SELECT语句的数据源。[例1]

在视图设计器下建立货品信息的视图。

（多表查询：货品信息表和供应商信息表）上一页下一页返回目录6.1视图的基本概念6.1.2视图的优点和缺点

（1）隐蔽数据库的复杂性。（2）为用户集中提取数据。（3）简化数据库用户权限的管理。（4）方便数据的交换。

视图的缺点主要表现在对数据修改的限制上。上一页下一页返回目录6.2视图的创建和查询注意事项：①只能在当前数据库中创建视图。②如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。③如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须给列定义名称。上一页下一页返回目录6.2视图的创建和查询创建视图的方法：一、使用SSMS创建视图二、使用Transact-SQL语句创建视图

格式：

CREATEVIEW〈视图名〉

[WITHENCRYPTION

]

——对视图进行加密

AS

〈SELECT语句〉[WITHCHECKOPTION

]——检查上一页下一页返回目录6.2视图的创建和查询[例2]

在查询设计器下建立“客户订购视图”，该视图中包含所有订购货品的客户及他们订购货品的名称和供应商。

上一页返回目录下一页CREATEVIEW

客户订购视图

AS

SELECTD.编号,D.姓名,B.名称AS

货品名称,A.名称AS

供应商

FROM

供应商信息

AINNERJOIN

货品信息BONA.编码=B.供应商编码

INNERJOIN

订单信息CONB.编码=C.货品编码

INNERJOIN

客户信息DONC.客户编号=D.编号6.2视图的创建和查询[例3]在SSMS中查看或修改视图

右击视图——“修改”

[例4]在“客户订购视图”中查询赵英的订货情况。

上一页下一页

SELECT

*

FROM

客户订购视图

WHERE

姓名='赵英'返回目录6.2视图的创建和查询练习1：创建一个视图。该视图能显示每张订单的客户名、货物名称、订货数量、订货日期、销售人员名单、所属部门、供应商名称以及联系人。

上一页返回目录下一页CREATE

VIEW

订单视图ASSELECT

a.订单号,e.姓名AS客户姓名,a.数量AS订货数量,a.订货日期,b.名称AS货物名称,c.姓名AS销售人员姓名,d.名称AS部门名称,f.名称AS供应商名称FROM

订单信息a,货品信息b,销售人员c,部门信息d,客户信息e,供应商信息fWHEREa.销售工号=c.工号ANDa.货品编码=b.编码AND

a.客户编号=e.编号ANDb.供应商编码=f.编码AND

c.部门编号=d.编号6.3视图的维护6.3.1查看视图的定义信息

1、使用SSMS查看视图信息

2、使用系统存储过程查看视图信息

sp_help数据库对象名称

sp_helptext

视图（触发器、存储过程）

[例5]查看货品视图信息。

EXECSP_helptext

货品视图上一页下一页返回目录6.3视图的维护6.3.2查看视图与其他对象的依赖关系

1、使用SSMS查看视图信息

2、使用系统存储过程查看视图信息

sp_depends数据库对象名称[例6]查看客户订购视图与其他对象的依赖关系。

EXECSP_depends

客户订购视图上一页下一页返回目录6.3视图的维护6.3.3修改视图格式：上一页下一页

ALTERVIEW

视图名

[WITHENCRYPTION]

AS

SELECT语句

[WITHCHECKOPTION]返回目录6.3视图的维护6.3.3修改视图[例7]建立客户订购视图3，然后用命令修改，使其包含订货数量，并要求加密。上一页下一页

--先建立视图CREATEVIEW

客户订购视图3

AS

SELECT

A.编号,A.姓名,C.货品名称,C.供应商

FROM

客户信息

A

INNERJOIN

订单信息

B

ON

A.编号=B.客户编号

INNERJOIN

货品视图

C

ON

B.货品编码=C.编码GO返回目录6.3视图的维护上一页下一页--修改视图ALTERVIEW

客户订购视图3

WITHENCRYPTIONAS

SELECTA.编号,A.姓名,C.货品名称,B.数量,C.供应商

FROM客户信息

A

INNERJOIN

订单信息

BONA.编号=B.客户编号

INNERJOIN

货品视图

CONB.货品编码=C.编码GO--使用该视图SELECT*FROM

客户订购视图3--查看该视图，由于已经加密则不能看到定义信息EXECsp_helptext

客户订购视图3返回目录6.3视图的维护6.3.4删除视图使用SSMS删除视图使用Transact-SQL语句删除视图

格式：

DROP

VIEW〈视图名〉[例8]删除客户订购视图

DROP

VIEW客户订购视图

上一页下一页返回目录6.3视图的维护6.3.5重命名视图

1、使用SSMS重命名视图

2、使用系统存储过程重命名视图

sp_rename旧名,新名

[例9]将视图“客户订购视图3”重新命名为客户订购视图。

EXECSP_rename

客户订购视图3，

客户订购视图上一页下一页返回目录6.4通过视图修改表数据对视图进行的修改操作有以下限制：（1）若视图的字段来自表达式或常量，则不允许对该视图执行INSERT和UPDATE操作，但允许执行DELETE操作。（2）若视图的字段来自集合函数，则此视图不允许修改操作。（3）若视图定义中含有GROUPBY子句，则此视图不允许修改操作上一页下一页返回目录6.4通过视图修改表数据（4）若视图定义中含有DISTINCT关键字，则此视图不允许修改操作。（5）若视图的定义不允许被修改，则视图也不允许修改操作。[例10]对视图“客户订购视图2”进行修改操作，修改编号5的客户姓名为“欣明”。上一页下一页UPDATE

客户订购视图2

SET

姓名='欣明'

WHERE

编号=5GO返回目录作业1、创建一个名为”赵英订货”的视图。要求该视图能显示客户“赵英”所订货品的名称、数量、订货日期。2、显示视图“赵英订货”

的信息。3、修改视图“赵英订货”

，要求对其加密。4、删除视图“赵英订货”

。上一页下一页返回目录6.5索引概述索引的概念：

索引与书中的目录类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书的目录是一个章节列表，其中注明了每一章节的页码。而索引则是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

上一页下一页返回目录6.5.1SQLServer2005中数据的存储与访问1．数据的存储

在SQLServer2005中，数据存储的基本单位是页，页的大小是8KB。

每页的开始部分是96个字节的页首，用于存储系统信息，如页的类型、页的可用容量、拥有页的对象ID等。上一页下一页返回目录6.5.1SQLServer2005中数据的存储与访问2．数据的访问

SQLServer2005提供了两种数据访问的方法：表扫描法索引法上一页下一页返回目录6.5.1SQLServer2005中数据的存储与访问（1）表扫描法

在没有建立索引的表内进行数据访问时，SQLServer2005通过表扫描法来获取所需要的数据。

当SQLServer2005执行表扫描时，它从表的第一行开始进行逐行查找，直到找到符合查询条件的行。（2）索引法

在建有索引的表内进行数据访问时，SQLServer2005通过使用索引来获取所需要的数据。上一页下一页返回目录6.5.1SQLServer2005中数据的存储与访问当SQLServer2005使用索引时，它会通过遍历索引树来查找所需行的存储位置，并通过查找的结果提取所需的行。

通常由于索引加速了对表中数据行的检索，所以使用索引可以加快SQLServer2005访问数据的速度，减少数据访问时间。上一页下一页返回目录6.5.2索引的作用创建索引的好处主要有以下两点：（1）加快数据查询。（2）加快表的连接、排序和分组工作。创建索引也有它的不足：

（1）创建索引需要占用数据空间和时间。（2）建立索引会减慢数据修改的速度。上一页下一页返回目录6.5.3索引的分类按照索引值的特点分类，可以将索引分为唯一索引和非唯一索引；

按照索引结构的特点分类，可以将索引分为聚集索引和非聚集索引。

1．唯一索引和非唯一索引

唯一索引要求所有数据行中任意两行中的被索引列或索引列组合不能存在重复值，包括不能有两个空值NULL；而非唯一索引则不存在这样的限制。上一页下一页返回目录6.5.3索引的分类2．聚集索引和非聚集索引

聚集索引对表中的数据按列进行物理排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它存储的是实际的数据。

一个表中只能有一个聚集索引。

当建立主键约束时，如果表中没有聚集索引，SQLServer2005会用主键列作为聚集索引键。上一页下一页返回目录6.5.3索引的分类2．聚集索引和非聚集索引

非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将表中的数据进行物理排序。非聚集索引存储了组成非聚集索引的关键字值和行定位器。

一个表中最多可建立249个非聚集索引

上一页下一页返回目录6.6创建索引SQLServer2005创建索引的方法：（1）在创建表的约束时由系统自动创建（2）利用SSMS创建索引（3）利用T-SQL语句中的CREATEINDEX命令

创建索引上一页下一页返回目录6.6.1系统自动创建索引

在创建或修改表时，如果添加了一个主键或唯一键约束，则系统将自动在该表上，以该键值作为索引列，创建一个唯一索引。[例11]使用存储过程sp_helpindex查看“客户信息”表的索引情况。上一页下一页Sp_helpindex

客户信息返回目录6.6.1系统自动创建索引[例12]在查询设计器下，为“客户信息”表的“电话”字段添加唯一键约束，然后使用存储过程sp_helpindex查看“客户信息”表的索引情况。上一页下一页ALTERTABLE

客户信息

ADDUNIQUE(电话)GOSp_helpindex

客户信息返回目录6.6.1系统自动创建索引[例13]在查询设计器下，为“客户信息”表的“电话”字段添加唯一键约束，并建立聚集索引。上一页下一页ALTERTABLE

客户信息

ADDUNIQUECLUSTERED

(电话)

GO返回目录6.6.2在图形界面下创建索引利用SSMS

直接创建索引方法：右击表—“修改”—工具栏“管理索引和建”按钮—弹出“索引/建”对话框—单击“添加”按钮[例14]在图形界面下，在“客户信息”表中，建立基于“姓名”字段的非唯一、非聚集索引，索引名为“IX_客户信息_姓名”。（具体操作过程，参见P109）上一页下一页返回目录6.6.3使用CREATEINDEX语句创建索引格式：CREATE

[UNIQUE][CLUSTERED|NONCLUSTERED]

INDEX<索引名>

ON{表名

|视图名

}(列名

[ASC|DESC][,...n])

[WITH

<索引选项>

][ON

<文件组>

]上一页下一页返回目录6.6.3使用CREATEINDEX语句创建索引[例15]在“订单信息”表上创建名为“IX_订单信息_客户货品”的非聚集、复合索引，该索引基于客户编号列和货品编码列创建。上一页下一页

USEmareketing

CREATE

NONCLUSTERED

INDEX

IX_订单信息_客户货品

ON

订单信息(客户编号,货品编码)GO返回目录6.6.3使用CREATEINDEX语句创建索引练习：为表深圳客户创建一个基于编号列的唯一、聚集索引，要求按编号降序排列。上一页下一页

CREATE

UNIQUECLUSTERED

INDEXIX_深圳客户_编号

ON深圳客户(编号DESC)

GO返回目录6.7管理和维护索引6.7.1查看和修改索引信息

查看和修改索引信息有两种方法：1、使用SQLServerManagementStudio2、使用sp_helpindex系统存储过程或有关表上的索引信息。上一页下一页返回目录6.7.2删除索引删除索引有两种方法：（1）使用SSMS删除索引。方法：右击表—“修改”—“管理索引和键”按钮

—“删除”[例16]使用SSMS删除[例15]中创建的索引。

上一页下一页返回目录6.7.2删除索引（2）使用语句DROPINDEX命令删除索引。格式：DROP

INDEX<表名.索引名>

[，<表名.索引名>……][例17]使用T-SQL语句删除例15创建的索引。

DROPINDEX

订单信息.IX_订单信息_客户货品

GO[例18]删除一主键索引。上一页下一页不能删除用约束创建的索引返回目录作业

1、在xk数据库的course表上创建基于couno的聚集、惟一索引IX_course_No。2、在stucou表上创建基于stuno和couno的复合型索引IX_stucou_FH，并且要求按stuno升序、couno降序建立。3、删除上题中建立的索引IX_stucou_FH。上一页下一页返回目录6.7.3索引的分析与维护1．索引的分析（1）显示查询计划

SQLServer2005提供了两种显示查询中的数据处理步骤以及如何访问数据的方式：

①以图形方式显示执行计划：“查询”菜单—“显示估计的执行计划”[例19]查询客户的订单信息，并显示执行计划。上一页下一页SELECT*FROM

订单信息

a,客户信息

bWHERE

a.客户编号=b.编号返回目录6.7.3索引的分析与维护②以表格方式显示执行计划

通过在查询语句中设置SHOWPLAN选项，我们可以选择是否让SQLServer2005显示查询计划。设置是否显示查询计划的命令为：

SETSHOWPLAN_ALLON|OFF

或：SETSHOWPLAN_TEXTON|OFF[例20]将例19

的执行计划以表格的形式显示。上一页下一页SETSHOWPLAN_TEXTONGOSELECT*FROM

订单信息

a,客户信息

bWHERE

a.客户编号=b.编号返回目录6.7.3索引的分析与维护（2）数据I/O统计数据检索语句所花费的磁盘活动量也是我们较关心的性能之一。通过设置STATISTICSIO选项，我们可以使SQLServer2005显示磁盘I/O信息。命令格式：SETSTATISTICSIOON|OFF上一页下一页必须是批处理中仅有的语句返回目录6.7.3索引的分析与维护（2）数据I/O统计

[例21]

查询客户“赵英”的订单信息，并分析执行该查询所花费的磁盘活动量的信息。上一页下一页SETSTATISTICSIOONGOSELECT*FROM

订单信息

a,客户信息

bWHERE

a.客户编号=b.编号

AND

姓名='赵英‘GOSETSTATISTICSIOOFFGO返回目录6.7.3索引的分析与维护2、索引的维护在创建索引后，为了得到最佳的性能，必须对索引进行维护。因为随着时间的推移，用户需要在数据库上进行插入、更新和删除等一系列操作，这将使数据变得支离破碎，从而造成索引性能的下降。（1）统计信息更新

①

SSMS：右击数据库—“属性”—“选项”[例22]

在SSMS中设置数据库，实现统计信息的自动更新。

上一页下一页返回目录6.7.3索引的分析与维护②

命令：

UPDATESTATISTICS

表名索引名[例23]使用UPDATESTATISTICS命令更新“客户信息”表主键索引的统计信息。

UPDATESTATISTICS

客户信息

PK_客户信息_XXXXX上一页下一页返回目录6.7.3索引的分析与维护（2）使用

DBCCSHOWCONTIG

语句扫描表

——获取索引碎片信息

格式：DBCCSHOWCONTIG(表名,索引名)[例24]

使用DBCCSHOWCONTIG

命令获取“客户信息”表主键索引的碎片信息。

DBCCSHOWCONTIG

(客户信息，PK_客户信息_XXX)