第7章视图和索引管理

第七章视图和索引管理本章学习目标1、熟练掌握视图的分类、创建方法及各种操作方法2、熟练掌握索引的创建、管理及维护的各种操作方法学习要点1、查询视图的创建、修改与删除；2、视图数据的查询、插入、修改与删除；3、创建、更改、删除索引对象；4、索引对象的管理及维护七-1视图项目要求能根据实际工作需求完成对视图的创建、管理及维护，以简化对电脑销售管理系统中的数据查询操作。创建视图管理视图操作视图数据实际工作任务在CPMS数据库的WORKER表上创建一个名为Worker_view1的视图，视图的数据包括职位为“业务员”的所有职员信息。任务一：创建视图创建视图的两种方法：方法一：利用管理工具方法二：利用T-SQL语句建立视图—利用对象资源管理器任务一：在CPMS数据库的WORKER表上创建一个名为Worker_view1的视图，视图的数据包括职位为“业务员”的所有职员信息。关系图窗格条件窗格SQL窗格结果窗格建立视图—利用SQL语句1、格式：

CreateView视图名[(字段名[,…n])][WithEncryption]ASSelect语句

[Withcheckoption]2、说明：1）WithEncryption：对视图的定义加密2）Withcheckoption：指出对视图进行的修改要符合Select语句所指定的限制条件。3）（字段名）：在视图中包含的列名，如果未指定则与SELECT语句中的列相同的名称。在users表中创建一个视图users_view1，视图中包含三个用户名和密码，要求不能查看视图定义语句。createviewusers_view1withencryptionasselectusername,pwdfromusersGO此时用SP_HelpTextusers_view1系统提示：此对象备注已加密3、创建时应考虑的因素：1）语句中不能包括COMPUTE、COMPUTEBY字句和INTO关键字；2）创建时参考的基础的列数最多为1024列3）创建视图不能参考临时表4）CREATEVIEW和其他T-SQL语句不能同时出现在一个批处理句中概念：视图是一个由SELECT语句指定，用以检索数据库表中某些行或列数据的语句存储定义。说明：1）视图是一个基于选择查询的虚拟表。视图与真实的数据库表有很多类似的地方，视图也是由若干字段（列）和一些记录（行）组成的；2）视图本身并不存储数据，视图中的字段和记录都是来自数据库表（称为基表）3）视图也可像表那样作为Select语句的数据来源使用；4）视图可以用来更新其中的数据，但更新的是视图所对应的基表中的数据。什么是视图2、视图的用途使用视图可以集中、简化和定制用户的数据库显示。视图通常应用两种场合：简化数据操作。如果在查询中数据分散在两个或多个表中，或者所用的搜索条件比较复杂时，需要编写很长的Select语句，并且要多次执行此相同的任务，则可考虑建立视图。提高数据库的安全性。使用视图还可定制允许用户查看哪些数据，让用户通过视图来访问表中的特定字段和记录，而不是授予他们直接访问基表的权限。创建视图示例创建联合视图—将多表查询中的记录汇总为一个虚拟表例:在cpms的worker表和sell表上创建一个包括所有女性职员的职员编号、姓名、性别、职位、所销售货物编号及销售价格的视图sex_VIEW3

。例三：createviewsex_view3asselecta.work_id,work_name,sex,position,ware_id,sell_pricefromworkera,sellbwherea.work_id=b.work_idandsex=1goselect*fromsex_view3创建包含集合函数的视图例:创建一个能显示职员编号及该职员的销售总价格的视图price_VIEW1。createviewprice_view1asselectwork_id,sum(sell_price)fromsellgroupbywork_id创建视图的视图例:在sex_view3视图中创建一个能显示职员编号、姓名及性别的视图sex_view4。createviewsex_view4asselectwork_id,work_name,sexfromsex_view3两种方法修改：一是使用企业管理器；二是使用ALTERVIEW语句。1、使用ALTERView修改视图

ALTERView视图名[(字段名[,…n])][WithEncryption]ASSelect语句

[Withcheckoption]例：修改名为sex_view1的视图,使其能显示所有男性职员信息alterviewsex_view1asselect*fromworkerwheresex=0go任务二：管理视图--修改视图2、查看视图信息1）查看视图名称、拥有者及创建日期SP_HELP视图名2）查看视图的定义脚本SP_HELPTEXT视图名3、更改视图名字SP_RENAME旧视图名，新视图名4、删除视图DROPVIEW视图名任务三：操作视图数据

视图数据的查询、插入、修改与删除与表中数据的操作几乎一样。语句均为：1、查询：SELECT2、插入：INSERT3、更新：UPDATE4、删除：DELETE现场操作在CPMS数据库的Worker表上创建一个名为Worker_view5的视图，该视图要求包括职员的所有信息。创建成功以后，从视图中查询出职位为“业务员”的职员信息。现场操作向视图worker_view5中插入一行数据，信息为“9901”、”王方平”、”男”、”1980-3-2”、、”紫禁城45号”、”业务员”将视图worker_view5中“王方平”的家庭住址改为”紫阳路45号”将视图worker_view5中“王方平”的职员信息删除七-2索引数据库的物理顺序索引字段案例一：在worker表上创建一个名为work_index1的唯一索引，要求检索时能快速的按照职员姓名字段定位。CreateuniqueIndexwork_index1onworker(work_nameasc)索引的概念

索引是一种特殊类型的数据库对象，它与表有着密切的关系。1、概念：索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。2、说明：索引通过记录表中的关键值来指向表中的记录。在一个表中建立一个索引，执行查询时不必扫描整个表，就能快速地找到所需的数据。一个索引就是一个列表，其中列出一些值和指向每个值在表中的实际存储位置的指针，这些索引信息放在索引页中，表中的数据则放在数据页中。

基于某个字段（如“姓名”）建立索引的，这样的字段通常称为索引键。索引键可以是表中的单个字段，也可以由多个字段组合而成。一个索引就是一组索引键值的列表，这些值来自于表中的各个记录。索引键值可以是惟一的，例如选择表中的主键，但索引键值也可以有重复值。案例二：在worker表上创建一个名为work_index2的聚集索引，要求检索时能快速的按照职工编号字段查询信息。案例三：在worker表上创建一个名为work_index3的唯一索引，要求检索时能快速的按照姓名字段定位。案例四：在worker表上创建一个名为work_index4的非聚集索引，要求检索时能快速的按照出生日期字段定位。2、使用CreateIndex语句建立索引1）格式：

Create[Unique][Clustered|nonclustered]Index索引名ON表名（字段名ASC|DESC[，…n]）

[WITH

[PAD_Index][[,]FillFactor=填充因子][[,]Drop_Existing]2）说明：1、PAD_Index指定每个页保持开放的空间，只有在指定FillFactor时才能使用。2、FillFactor子句通过参数指定各索引页级的填满程度。3、Drop_Existing删除已存在的同名索引。只有在指定的索引名存在时才能使用案例二：在worker表上创建一个名为work_index2的聚集索引，要求检索时能快速的按照职工编号字段查询信息。知识点：什么是聚集索引（Clusteredindex）聚集索引表示表中的物理记录顺序和索引键值的顺序相同，在执行Insert语句时，新记录将按照该索引键值指定的顺序插入适当的位置上。注意：（1）在一个表中只能建立一个聚集索引。（2）必须要先创建聚集索引才能在创建非聚集索引。（3）主键创建后，自动创建聚集索引添加惟一索引时使用CreateTable语句在表中的某个字段上添加惟一性约束时，则系统会在该字段上自动建立惟一性索引。案例三：在worker表上创建一个名为work_index3的唯一索引，要求检索时能快速的按照姓名字段定位。什么是惟一性索引与复合索引惟一性索引（Uniqueindex）如果表中任意两行被索引的列不允许出现重复值,那么这种索引称为惟一索引。例如，在一个表中包含有身份证号码字段，则最适合于建立惟一性索引，因为不可能有两个人拥有相同的身份证号码。复合索引（Compositeindex）基于两列或多列组合而建立的索引称为复合索引。在一个复合索引中最多可以使用16个字段。在Select语句的Where子句中使用多个字段时，复合索引显得格外有用。案例四：在worker表上创建一个名为work_index4的非聚集索引，要求检索时能快速的按照出生日期字段定位。Createnonclusteredindexwork_index4Onworker(birthasc)知识点：什么是非聚集索引（NonClusteredindex）

非聚集索引用不会对表和视图进行物理排序,其用于指定数据的逻辑顺序，其索引页中包含着索引键值和指向对应数据页的指针，该指针用于确定记录在表中的实际位置。不过，表中的数据按照插入记录的顺序存放，并没有按照索引键值指定的顺序来存储。在一个表中最多可以建立249个非聚集索引。这样，就有可能在以不同方式访问表时使用不同的索引，这也是非聚集索引的一个优点。缺省创建非聚集索引

如在表中建立的索引太多，修改或删除记录时服务器用于维护索引所花费的时间就越长，反而会使数据库的性能下降。当不再需要某些索引时，就应当及时地从表中删除掉。删除索引1、使用DropIndex语句删除索引

DropIndex表名.索引名[,…n]例如，删除在worker表上创建一个名为work_index4的非聚集索引Dropindexworker.work_index4删除索引注意：

DropIndex语句不能用于删除在表中定义主键约束或惟一性约束时由SQLServer自动建立的那些索引。如确实需要删除此类索引，请使用带DropConstraint子句的ALTERTABLE语句来解除加在该字段上的主键约束或惟一性约束。例如：ALTERTABLETest01DropConstraintPK_Test01_693CA210本章以一个实