第9章 索引和视图

1、,索引和视图,安徽新华电脑专修学院,学习目标,掌握索引的概念与创建 掌握视图的概念与创建 掌握使用视图操作表数据,学习完本课程，您应该能够：,知识重、难点分析,重点： 索引的概念与创建 视图的概念与创建 难点： 使用视图操作表数据,索引和视图,任务二 视图的管理,目 录,任务一 索引的管理,概念： 数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。如同书本的目录，便于查询。 类型： 1.聚集索引 2.非聚集索引 3.复合索引 4.唯一索引,任务一 索引的管理,1、索引的基本概念,1、索引的基本概念,索引的优点： 可以大大

2、加快数据检索速度。 通过创建唯一索引，可以保证数据记录的唯一性。 在使用ORDER BY和GROUP BY子句进行检索数据时，可以显著减少查询中分组和排序的时间。 使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。,1、索引的基本概念,索引的分类： 1、聚集索引和非聚集索引 2、复合索引 3、唯一索引,1、索引的基本概念,特点： 1、表中的数据页会依照该索引的顺序来存放。即：表中的数据的物理存储顺序和排列顺序相同，可按表中的某一字段或多个字段来排序记录 2、每个数据库中的每个表只能有一个聚簇索引。因为一个表中的

3、记录只能以一种物理顺序存放。 3、通常情况下是对 一个表按照主码（关键字）建立聚簇索引。也可以用其它字段建立聚簇索引。,聚簇索引,例子： 职员表（未建立聚簇索引之前） 注意： 按姓名建立一个聚簇索引，表中的记录会自动按姓名的拼音顺序进行存储，如下图：,基于“姓名”字段建立了聚簇索引以后：,注意：现在向表中添加一条记录“20006，牛东华，女，6000”，则该记录按姓名顺序排序自动存放于李秀华和杨秋生之间，若没有建立聚簇索引，则存放于表的最后一条。,特点： 1、表的物理存储顺序与索引顺序不同，即表的数据并不是按照索引排列； 2、非聚簇索引不会影响数据表中的记录的实际存储顺序，如表中基于姓名创建了

4、非聚簇索引，虽然索引中的姓名顺序按拼音排序的，但数据表中数据记录存储顺序不会因为索引的创建而发生变化； 3、一个表可以创建多个非聚簇索引，最多可以有249个非聚簇索引。,非聚簇索引,创建聚簇索引注意几点： 每张表只能有一个聚簇索引； 创建聚簇索引所需空间来自用户数据库； 主码（主关键字）是聚簇索引的首选关键字； 表中设有主关键字时，SQL自动以该关键字建立一个聚簇索引，名称为“PK-表名”； 用户若在企业管理器中建的表，没有设置主关键字，建立的索引为一个非聚簇索引；创建聚簇索引只能通过SQL语句来创建,（1）创建聚簇索引,在企业管理器中，展开指定的服务器和数据库，选择要创建索引的表，从弹出的快

5、捷菜单中依次选择“所有任务|管理索引”选项。,1、使用企业管理器创建索引,格式： create uniqueclustered | nonclustered index On 表名（字段 asc | desc ,） 说明： unioue:创建的索引是唯一索引。 clustered：创建聚簇索引。 nonclustered:创建非聚簇索引。,2、使用Transact-SQL语句创建索引,案例： 在学生表中以学号建立一个聚簇索引 create clustered index index_学号 on 学生表（学号）,1、利用索引向导创建索引 2、利用索引优化向导创建索引,2、使用向导创建索引,欢迎使

6、用创建索引向导对话框,2、使用向导创建索引,选择数据库和对象对话框,2、使用向导创建索引,显示当前索引信息对话框,2、使用向导创建索引,选择创建索引的列对话框,2、使用向导创建索引,指定索引选项对话框,2、使用向导创建索引,指定索引名称对话框,2、使用向导创建索引,(2)创建非聚簇索引 A、在企业管理器中建立聚簇索引,案例：在学生档案表中，以姓名建立非聚簇索引,B、使用SQL句建立非聚簇索引,案例：在学生档案表中，以姓名建立非聚簇索引,（3）、创建唯一索引,结构：可以是聚簇索引结构，也可以是非聚簇索引结构。 特征： 不允许两行具有相同的索引值；即索引字段值在表中必须唯一（最好不为空），不得重复

7、，否则不能建立唯一索引，如：学号，用户名，E-mail 能够实现实体的完整性； 在创建主码约束和唯一约束时自动创建。,在学生档案表中创建“学号”的唯一索引，即学号的每一条记录值是唯一的。,唯一索引案例：,（4）、创建复合索引,定义：由两个或更多的字段组成的索引称为“复合索引”。 特征：复合索引可以为唯一索引，也可不为唯一索引,若为唯一索引,字段组合取值不能重复。 对复合字段作为一个单元进行搜索； 创建复合索引中的列序不一定与表定义列序相同。 注意:最多可以有16个字段组合到一个复合索引中,复合索引中的所有字段必须在同一个表中。,复合索引的应用实例,为学生表中学号和姓名创建一个复合索引,1、使用

8、企业管理器查看、修改和删除索引 使用企业管理器查看和修改索引 使用企业管理器删除索引 2、使用系统存储过程查看索引信息 sp_helpindex 表名、视图名,查看、修改和删除索引,3、使用Transact-SQL语句删除索引 当不再需要某个索引时，可以使用Transact-SQL语句中的DROP INDEX命令删除索引。 DROP INDEX命令可以删除一个或多个当前数据库中的索引： Drop index 表名.索引名 删除多个索引则用逗号隔开。,查看、修改和删除索引,视图（view）： 视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果

9、。 视图被定义后便存储在数据库中，通过视图看到的数据只是存放在基表中的数据。当对视图中的数据进行修改时，相应的基表的数据也会发生变化，同时，若基表的数据发生变化，这种变化也会自动地反映到视图中。 视图可以是一个数据表的一部分，也可以是多个基表的联合。,任务二 视图的管理,1、视图的基本概念,特点： 它保存了数据库中的选择查询结果，相当于一个从一个或多个数据表中派生出来的虚拟表，是用户查看数据库数据的一种方式。 视图与真实的数据表相似，由一些记录和字段组成的二维表。 视图是在引用存储在数据库中的查询语句时动态创建的，数据库中只存储视图的定义，真正的数据依然存储在数据表中。,任务二 视图的管理,视

10、图的特点： （1）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。 （2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据表的更改也不影响用户对数据库的使用。 （3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。 （4）便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。 （5）可以重新组织数据以便输出到其他应用程序中。,任务二 视图的管理,视图的分类： 投影：最简单的视图，一个投影仅仅是一个

11、表的列的集合。 连接；通过比较指定列中的值连接二个或更多的表。 其它类型的视图：可用聚合函数、计算列和基于其它视图来创建。,创建视图时应该注意以下情况： 只能在当前数据库中创建视图。 如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。 如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。,创建视图,创建视图时应该注意以下情况： 不能在视图上创建索引，不能在规则、缺省、触发器的定义中引用视图。 当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。 视图的名称

12、必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表的名称相同。,创建视图,2、创建视图： （1）使用企业管理器创建 展开数据库，右键“视图” ，选择“新建视图”菜单项。,2、创建视图： 在 “添加表”对话框中选择与视图相关联的表、视图或函数，可以使用Ctrl或Shift键进行多选，选择完毕后，单击“添加”按钮。,2、创建视图： 在第二个子窗口中选择创建视图所需的字段，可以指定列的别名、排序方式和规则等。注意当视图中需要一个与原字段名不同的字段名，或视图的源表中有同名的字段，或视图中包含了计算列时，需要为视图中的这样的列重新指定名称。,2、创建视图： 在上一步完

13、成后，单击“保存”按钮，出现保存视图对话框。在其中输入视图名，并单击“确定”按钮，便完成了视图的创建。,通过视图向导创建视图,格式： create view 字段名1,字段名2, As with check option 说明： 子查询：任意的select 语句查询； with check option： 表示对视图进行更新、插入、删除操作时，要保证更新、插入、删除的记录符合定义中的子查询的条件。,（2）使用SQL语句创建视图,注意： 定义视图的用户须对所参照的表或视图有查询权限，即可执行SELECT语句。 不能使用COMPUTE或COMPUTE BY子句。 不能使用ORDER BY子句。 不

14、能使用INTO子句。 不能在临时表或表变量上创建视图。,（2）使用SQL语句创建视图,视图基于一个表导出：行列子集视图。 1、创建学历为本科的视图。 Create view 本科学历表 As select 学号,姓名,性别,学历 from 学生表 where 学历=本科,实例,视图可基于多个表导出，建立在多表之上。 2、创建数据库成绩85分以上的学生档案视图 create view 新成绩表（学号,姓名,性别,数据库） as select A.学号,A.姓名,性别,数据库 from 学生表 A,成绩表 B where A.学号=B.学号 and 数据库=85,实例,3、创建“ceac1”班男生

15、档案视图 create view 男生档案（学号,姓名,性别,班级） as select 学号,姓名,性别,班级 from 学生表 where 班级=ceac1,实例,定义: 定义视图时增加一些虚拟字段(给原表字段加表达式),而基本表中无此字段,此视图为表达式视图。 案例: 创建各位同学平均分视图 Create view 平均分(学号,姓名,平均分) As Select 学号,姓名,(网络+网页+数据库)/3 From 成绩表,(3) 表达式视图,功能：可以确保在对视图进行更新、插入或删除的记录 时，满足视图定义中子查询的条件表达式。 案例：创建ceac1班的学生档案视图 create vie

16、w ceac1班档案 as select 学号,姓名,性别,年龄 from 学生表 where 班级=ceac1 with check option,（4）使用with check option子句,案例： 创建学生成绩表中平均分成绩视图，并按降序排列。 create view 平均分(学号,姓名,性别,平均分) as select 学号,姓名,性别, avg(数据库+网络+网页) from 学生成绩表,(5) 使用集合函数创建视图,3. 利用向导创建视图,选择工具菜单中的向导命令,欢迎使用创建视图向导对话框,选择数据库对话框,选择对象对话框,选择字段对话框,定义限制对话框,命名视图对话框,完

17、成创建视图向导对话框,1、使用企业管理器查看视图信息 2 、使用系统存储过程查看视图信息 sp_help 数据库视图名称 -显示视图特征 sp_helptext 视图（触发器、存储过程）-显示视图在系统表中的定义 sp_depends 数据库对象名称 -显示该视图所依赖的对象,(5)查看视图信息,1、修改视图 （1）使用企业管理器修改视图。 （2）使用Transact-SQL语句修改视图 Alter View view_name （column1, column2,.n） With Encryption AS select_statement With Check Option,(5)修改和重

18、命名视图,2、重命名视图 （1）使用企业管理器重命名视图 （2）使用系统存储过程重命名视图 sp_rename old_name,new_name,(5)修改和重命名视图,修改视图中的数据时每次修改都只能影响一个基表。 不能修改那些通过计算得到的字段。 如果在创建视图时指定了With Check Option选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。 执行Update、Delete命令时，所删除与更新的数据必须包含在视图的结果集中。 如果视图引用多个表时，无法用Delete命令删除数据，若使用Update命令则应与Insert操作一样，被更新的列必须属于同

19、一个表。,(5)修改使用视图,插入数据记录 1、使用企业管理器在视图中插入记录。 2、使用Transact-SQL语句通过视图插入记录。 更新数据记录 1、使用企业管理器在视图中更新记录。 2、使用Transact-SQL语句通过视图更新记录。,(5)使用视图,删除数据记录 1、使用企业管理器在视图中删除记录 2、使用Transact-SQL语句通过视图删除记录 使用视图删除记录时，可以直接利用DELETE语句删除视图中的记录。但应该注意，必须指定在视图中定义过的字段来删除记录。,(5)使用视图,1 、使用企业管理器删除视图 2 、使用Transact-SQL语句删除视图 Drop View

20、view_name ,n,3、删除视图,用法要点：用SQL语句查询视图，与查询表用法相同。 案例1 ：从视图ceac1班中，查询学历为大专的男生清 单。 select 学号,姓名,性别,学历 from ceac1班 where 学历=大专 and 性别=男 此例相当于： select 学号,姓名,性别,学历 from 学生表 where 班级=ceac1 and 学历=大专 and 性别=男,3、查询视图,案例2： 从”平均分”视图中，查询平均分85分以上的学生清单. Select * From 平均分 Where 平均分=85 案例3: 从视图“ceac1班”与”成绩表”中，查询网络成绩85分以上的清单。 sele