第7章 视图和索引管理

1、制作：荆州职业技术学院 彭岚制作：荆州职业技术学院 彭岚本章学习目标1、熟练掌握视图的分类、创建方法及各种操作方法2、熟练掌握索引的创建、管理及维护的各种操作方法学习要点1、查询视图的创建、修改与删除；2、视图数据的查询、插入、修改与删除；3、创建、更改、删除索引对象；4、索引对象的管理及维护制作：荆州职业技术学院 彭岚制作：荆州职业技术学院 彭岚项目要求 能根据实际工作需求完成对视图的创建、管理及维护，以简化对电脑销售管理系统中的数据查询操作。创建视图创建视图管理视图管理视图操作视图数据操作视图数据实际工作任务制作：荆州职业技术学院 彭岚 在在CPMS数据库的数据库的WORKER表上创建一个

2、名为表上创建一个名为Worker_view1的视图，视图的数据包括职位为的视图，视图的数据包括职位为“业务业务员员”的所有职员信息。的所有职员信息。任务一：创建视图任务一：创建视图 创建视图的两种方法：创建视图的两种方法：方法一：利用管理工具方法一：利用管理工具方法二：利用方法二：利用T-SQLT-SQL语句语句制作：荆州职业技术学院 彭岚建立视图建立视图利用对象资源管理器利用对象资源管理器任务一：在任务一：在CPMS数据库的数据库的WORKER表上创建一个名表上创建一个名为为Worker_view1的视图，视图的数据包括职位为的视图，视图的数据包括职位为“业业务员务员”的所有职员信息。的所有

3、职员信息。关系图窗格关系图窗格条件窗格条件窗格SQL窗格窗格结果窗格结果窗格制作：荆州职业技术学院 彭岚建立视图建立视图利用利用SQL语句语句1、格式：、格式： Create View 视图名视图名 (字段名字段名 , n) With Encryption AS Select 语句语句 With check option 2、说明：、说明：1）With Encryption：对视图的定义加密：对视图的定义加密2） With check option：指出对视图进行的修改要符合：指出对视图进行的修改要符合Select语句所指定的限制条件。语句所指定的限制条件。3） （字段名）：在视图中包含的列名

4、，如果未指定则（字段名）：在视图中包含的列名，如果未指定则与与SELECT语句中的列相同的名称。语句中的列相同的名称。制作：荆州职业技术学院 彭岚在在users表中创建一个视图表中创建一个视图users_view1，视图中包含三个用户，视图中包含三个用户名和密码，要求不能查看视图定义语句。名和密码，要求不能查看视图定义语句。create view users_view1with encryptionasselect username,pwd from usersGO此时用此时用SP_HelpText users_view1系统提示：此对象备注已加密系统提示：此对象备注已加密制作：荆州职业技术学

5、院 彭岚3、创建时应考虑的因素：、创建时应考虑的因素：1）语句中不能包括）语句中不能包括COMPUTE、COMPUTE BY字句和字句和INTO关键字；关键字；2）创建时参考的基础的列数最多为）创建时参考的基础的列数最多为1024列列3）创建视图不能参考临时表）创建视图不能参考临时表4）CREATE VIEW和其他和其他T-SQL语句不能同时出语句不能同时出现在一个批处理句中现在一个批处理句中制作：荆州职业技术学院 彭岚概念：概念：视图是一个由视图是一个由SELECT语句指定，用以检索数据库表语句指定，用以检索数据库表中某些行或列数据的语句存储定义。中某些行或列数据的语句存储定义。说明：说明：

6、1）视图是一个基于选择查询的虚拟表。视图与真实的数据库）视图是一个基于选择查询的虚拟表。视图与真实的数据库表有很多类似的地方，视图也是由若干字段（列）和一些记表有很多类似的地方，视图也是由若干字段（列）和一些记录（行）组成的；录（行）组成的；2）视图本身并不存储数据，视图中的字段和记录都是来自数）视图本身并不存储数据，视图中的字段和记录都是来自数据库表（称为基表）据库表（称为基表） 3）视图也可像表那样作为）视图也可像表那样作为Select语句的数据来源使用；语句的数据来源使用；4）视图可以用来更新其中的数据，但更新的是视图所对应的）视图可以用来更新其中的数据，但更新的是视图所对应的基表中的数

7、据。基表中的数据。什么是视图什么是视图制作：荆州职业技术学院 彭岚2、视图的用途、视图的用途使用视图可以集中、简化和定制用户的数据库显示。使用视图可以集中、简化和定制用户的数据库显示。视图通常应用两种场合：视图通常应用两种场合：简化数据操作简化数据操作。如果在查询中数据分散在两个或多如果在查询中数据分散在两个或多个表中，或者所用的搜索条件比较复杂时，需要编写个表中，或者所用的搜索条件比较复杂时，需要编写很长的很长的Select语句，并且要多次执行此相同的任务，语句，并且要多次执行此相同的任务，则可考虑建立视图。则可考虑建立视图。提高数据库的安全性。提高数据库的安全性。使用视图还可定制允许用户使

8、用视图还可定制允许用户查看哪些数据，让用户通过视图来访问表中的特定字查看哪些数据，让用户通过视图来访问表中的特定字段和记录，而不是授予他们直接访问基表的权限。段和记录，而不是授予他们直接访问基表的权限。制作：荆州职业技术学院 彭岚创建视图示例创建视图示例创建联合视图创建联合视图将多表查询中的记录汇总为一个虚拟表将多表查询中的记录汇总为一个虚拟表例例:在在cpms的的worker表和表和sell表上创建一个包括所有女性职员的职员编表上创建一个包括所有女性职员的职员编号、姓名、性别、职位、所销售货物编号及销售价格的视图号、姓名、性别、职位、所销售货物编号及销售价格的视图sex_VIEW3 。例三：

9、例三：create view sex_view3asselect a.work_id,work_name,sex,position,ware_id,sell_price from worker a,sell bwhere a.work_id=b.work_id and sex=1goselect * from sex_view3制作：荆州职业技术学院 彭岚创建包含集合函数的视图创建包含集合函数的视图例例:创建一个能显示职员编号及该职员的销售总价格的视图创建一个能显示职员编号及该职员的销售总价格的视图price_VIEW1。create view price_view1asselect work

10、_id,sum(sell_price) from sell group by work_id创建视图的视图创建视图的视图例例:在在sex_view3视图中创建一个能显示职员编号、姓名及性别的视视图中创建一个能显示职员编号、姓名及性别的视图图sex_view4。create view sex_view4asselect work_id,work_name,sex from sex_view3制作：荆州职业技术学院 彭岚两种方法修改：一是使用企业管理器；二是使用两种方法修改：一是使用企业管理器；二是使用ALTER VIEW语语句。句。1、使用、使用ALTER View修改视图修改视图 ALTER

11、View 视图名视图名 (字段名字段名 , n) With Encryption AS Select 语句语句 With check option 例：例：修改名为修改名为sex_view1的视图的视图,使其能显示所有男性职员信息使其能显示所有男性职员信息alter view sex_view1asselect * from worker where sex=0go任务二：管理视图任务二：管理视图-修改视图修改视图制作：荆州职业技术学院 彭岚2、查看视图信息、查看视图信息1）查看视图名称、拥有者及创建日期）查看视图名称、拥有者及创建日期SP_HELP 视图名视图名2）查看视图的定义脚本）查看视

12、图的定义脚本SP_HELPTEXT 视图名视图名3、更改视图名字、更改视图名字SP_RENAME 旧视图名，新视图名旧视图名，新视图名4、删除视图、删除视图DROP VIEW 视图名视图名制作：荆州职业技术学院 彭岚任务三：操作视图数据任务三：操作视图数据 视图数据的查询、插入、修改与删除与表中数据视图数据的查询、插入、修改与删除与表中数据的操作几乎一样。语句均为：的操作几乎一样。语句均为：1、查询：、查询：SELECT2、插入：、插入：INSERT3、更新：、更新：UPDATE4、删除：、删除：DELETE制作：荆州职业技术学院 彭岚现场操作在CPMS数据库的Worker表上创建一个名为Wo

13、rker_view5的视图，该视图要求包括职员的所有信息。创建成功以后，从视图中查询出职位为“业务员”的职员信息。制作：荆州职业技术学院 彭岚现场操作向视图worker_view5中插入一行数据，信息为“9901”、”王方平”、”男”、”1980-3-2”、、”紫禁城45号”、”业务员”将视图worker_view5中“王方平”的家庭住址改为”紫阳路45号”将视图worker_view5中“王方平”的职员信息删除制作：荆州职业技术学院 彭岚七七-2 -2 索引索引制作：荆州职业技术学院 彭岚数据库的物理顺序数据库的物理顺序索引字段索引字段案例一：在案例一：在work

14、er表上创建一个名为表上创建一个名为work_index1的唯一索引，要求检索时能快速的唯一索引，要求检索时能快速的按照职员姓名字段定位。的按照职员姓名字段定位。Create unique Index work_index1 onworker(work_name asc)制作：荆州职业技术学院 彭岚索引的概念索引的概念 索引是一种特殊类型的数据库对象，它与表有着密切的关系。索引是一种特殊类型的数据库对象，它与表有着密切的关系。1、概念：、概念：索引是为了加速对表中数据行的检索而创建的一种分散索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。的存储结构。2、说明：索引通过记录表中的关键

15、值来指向表中的记录。、说明：索引通过记录表中的关键值来指向表中的记录。在一个在一个表中建立一个索引，执行查询时不必扫描整个表，就能快速地找表中建立一个索引，执行查询时不必扫描整个表，就能快速地找到所需的数据。一个索引就是一个列表，其中列出一些值和指向到所需的数据。一个索引就是一个列表，其中列出一些值和指向每个值在表中的实际存储位置的指针，这些索引信息放在索引页每个值在表中的实际存储位置的指针，这些索引信息放在索引页中，表中的数据则放在数据页中。中，表中的数据则放在数据页中。 制作：荆州职业技术学院 彭岚 基于某个字段（如基于某个字段（如“姓名姓名”）建立索引的，这样的字段通）建立索引的，这样的

16、字段通常称为常称为索引键索引键。索引键可以是表中的单个字段，也可以由多个。索引键可以是表中的单个字段，也可以由多个字段组合而成。一个索引就是一组索引键值的列表，这些值来字段组合而成。一个索引就是一组索引键值的列表，这些值来自于表中的各个记录。索引键值可以是惟一的，例如选择表中自于表中的各个记录。索引键值可以是惟一的，例如选择表中的主键，但索引键值也可以有重复值。的主键，但索引键值也可以有重复值。制作：荆州职业技术学院 彭岚案例二：在案例二：在worker表上创建一个名为表上创建一个名为work_index2的聚集索引，要求检索时能快速的聚集索引，要求检索时能快速的按照职工编号字段查询信息。的按

17、照职工编号字段查询信息。案例三：在案例三：在worker表上创建一个名为表上创建一个名为work_index3的唯一索引，要求检索时能快速的唯一索引，要求检索时能快速的按照姓名字段定位。的按照姓名字段定位。案例四：在案例四：在worker表上创建一个名为表上创建一个名为work_index4的非聚集索引，要求检索时能快的非聚集索引，要求检索时能快速的按照出生日期字段定位。速的按照出生日期字段定位。制作：荆州职业技术学院 彭岚2）说明：）说明：1、PAD_Index 指定每个页保持开放的空间，只有在指定指定每个页保持开放的空间，只有在指定FillFactor时才能使用。时才能使用。2、 Fill

18、Factor子句通过参数指定各索引页级的填满程度。子句通过参数指定各索引页级的填满程度。3、Drop_Existing删除已存在的同名索引。只有在指定的索引名存删除已存在的同名索引。只有在指定的索引名存在时才能使用在时才能使用制作：荆州职业技术学院 彭岚案例二：在案例二：在worker表上创建一个名为表上创建一个名为work_index2的聚集的聚集索引，要求检索时能快速的按照职工编号字段查询信息。索引，要求检索时能快速的按照职工编号字段查询信息。 聚集索引表示表中的聚集索引表示表中的，在执行，在执行Insert语句时，新记录将按照该索语句时，新记录将按照该索引键值指定的顺序插入适当的位置上。

19、引键值指定的顺序插入适当的位置上。 注意注意：（1）在一个表中只能建立在一个表中只能建立一一个聚集索引个聚集索引。（2）必须要先创建聚集索引才能在创建非聚集索引。）必须要先创建聚集索引才能在创建非聚集索引。（3）主键创建后，自动创建聚集索引）主键创建后，自动创建聚集索引制作：荆州职业技术学院 彭岚添加惟一索引时添加惟一索引时使用使用Create Table语句在表中的某个字段上添加惟一语句在表中的某个字段上添加惟一性约束时，则系统会在该字段上自动建立惟一性索引。性约束时，则系统会在该字段上自动建立惟一性索引。案例三：在案例三：在worker表上创建一个名为表上创建一个名为work_index3

20、的唯的唯一索引，要求检索时能快速的按照姓名字段定位。一索引，要求检索时能快速的按照姓名字段定位。制作：荆州职业技术学院 彭岚什么是惟一性索引与复合索引什么是惟一性索引与复合索引惟一性索引（惟一性索引（Unique index） 如果表中任意两行被索引的列不允许出现重复值如果表中任意两行被索引的列不允许出现重复值,那那么这种索引称为惟一索引。么这种索引称为惟一索引。例如，在一个表中包含有身份证号码字段，则最适合于例如，在一个表中包含有身份证号码字段，则最适合于建立惟一性索引，因为不可能有两个人拥有相同的身份建立惟一性索引，因为不可能有两个人拥有相同的身份证号码。证号码。复合索引（复合索引（Com

21、posite index） 基于两列或多列组合而建立的索引称为复合索引。基于两列或多列组合而建立的索引称为复合索引。在一个复合索引中最多可以使用在一个复合索引中最多可以使用16个字段。在个字段。在Select语语句的句的Where子句中使用多个字段时，复合索引显得格外子句中使用多个字段时，复合索引显得格外有用。有用。制作：荆州职业技术学院 彭岚案例四：在案例四：在worker表上创建一个名为表上创建一个名为work_index4的非聚的非聚集索引，要求检索时能快速的按照出生日期字段定位。集索引，要求检索时能快速的按照出生日期字段定位。Create nonclustered index work

22、_index4On worker (birth asc)制作：荆州职业技术学院 彭岚知识点：什么是知识点：什么是非聚集索引（非聚集索引（NonClustered index） 非聚集索引用不会对表和视图进行物理排序非聚集索引用不会对表和视图进行物理排序,其用其用于于，其索引页中包含着索引键值，其索引页中包含着索引键值和指向对应数据页的指针，该指针用于确定记录在表和指向对应数据页的指针，该指针用于确定记录在表中的实际位置。不过，表中的数据按照插入记录的顺中的实际位置。不过，表中的数据按照插入记录的顺序存放，并没有按照索引键值指定的顺序来存储。序存放，并没有按照索引键值指定的顺序来存储。 在一个表中最多可以建立在一个表中最多可以建立249个非聚集索引个非聚集索引。这。这样，就有可能在以不同方式访问表时使用不同的索引，样，就有可能在以不同方式访问表时使用不同的索引，这也是非聚集索引的一个优点。这也是非聚集索引的一个优点。 缺省创建非聚集索引缺省创建非