数据库基础第6章__视图与索引

null第6章 视图与索引 第6章 视图与索引 本章学习目标 本章学习目标 l  理解视图与索引的涵义及作用 l  熟练创建和管理视图及索引 l 灵活运用视图及索引以提高系统开发效率null6.1 创建视图 6.1.1 认识视图 1．视图的概念： 视图是从一个或多个基本表（或视图）派生出的虚拟表，它可以是连接多张数据表的虚表，也可以是使用WHERE子句限制条件的查询结果。与真实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行的物理存储位置仍然在它所引用的基本表中。 2．使用视图的优点和作用 视图一经定义后，就可以像表一样被查询、修改、删除和更新，但与直接使用表相比，使用视图又具有许多优点： （1）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。 null（2） 屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据表的更改也不影响用户对数据库的使用。 （3） 简化用户权限的管理。使用视图时，只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，增加了安全性。 （4） 便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。 （5） 可以重新组织数据以便输出到其他应用程序中。 null6.1.2 创建视图 视图在数据库中是作为一个对象来存储的。创建视图前，要保证创建视图的用户已被数据库所有者授权使用CREATE VIEW语句，并且有权操作视图所涉及的表或其他视图。在SQL Server 2000中，创建视图可以在企业管理器或视图向导中进行，也可以使用T-SQL的CREATE VIEW语句。 创建视图时，需要注意以下事项： （1）只能在当前数据库中创建视图。 （2）如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。 （3）如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。 （4）不能在视图上创建索引，不能在规则、缺省、触发器的定义中引用视图。 null（5）当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。 （6）视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表的名称相同。 1．使用企业管理器创建视图 【例6-1】使用企业管理器在XSCJ数据库中创建V_05GZYY的视图，只显示【学生基本信息表】中班级编码为20051001的班级的学生信息 ①打开【企业管理器】，在控制台树中展开【数据库】,右击XSCJ，在弹出的快捷菜单中选择【新建】菜单的【视图】命令。或者展开XSCJ数据库，右击【视图】，在弹出的快捷菜单中选择【新建视图】命令。 ②在所出现窗口的第一个子窗口中单击鼠标右键，将弹出一个【添加表】快捷菜单，如图6-1所示。 null图6-1 【添加表】快捷菜单 ③在快捷菜单中选择【添加表】菜单项，将出现如图6-2所示的【添加表】对话框。 图6-2 【添加表】对话框 null④在如图6-2所示的【添加表】对话框中选择与视图相关联的表、视图或函数，可以使用Ctrl或Shift键进行多选，此处选择“学生基本信息表”，选择完毕后，单击【添加】按钮，如图6-3所示。 图6-3 选择表、视图或函数null⑤在如图6-1所示的窗口的第二个子窗口中选择创建视图所需的字段，可以指定列的别名、排序方式和规则等，此处选择表中所有的字段，在“班级编号”字段的准则处设置准则为“=’20051001’”，如图6-4所示。注意当视图中需要一个与原字段名不同的字段名，或视图的源表中有同名的字段，或视图中包含了计算列时，需要为视图中的这样的列重新指定名称。 图6-4 选择列 null说明：这一步所选择的字段、规则等的情况相对应的SELECT语句将会自动显示在第三个小窗口中。也可以直接在该小窗口输入SELECT语句。 ⑥上一步完成后，单击【保存】按钮，出现如图6-5所示的保存视图对话框。在其中输入视图名，此处输入“V_05GZYY”并单击【确定】按钮，便完成了视图的创建。 视图一经创建成功，其中便包含了所选择的列数据。此例中，创建了视图V_05GZYY，则可查看其结构及内容，方法是：在视图V_05GZYY上单击鼠标右键，选择【设计视图】功能项即可以查看并修改视图结构；选择【打开视图】→【返回所有行】将可查看视图的数据内容。 2．通过视图向导创建视图 【例6-2】使用视图向导在XSCJ数据库中创建“V_少数民族信息”的视图，只显示【学生基本信息表】中少数民族学生的学号、姓名、性别、族别、家庭住址信息。 null①打开【企业管理器】，选择【工具】菜单中的【向导】菜单项，出现如图6-6所示的【选择向导】对话框。 图6-5 保存视图对话框 图6-6 【选择向导】对话框②在【选择向导】对话框中展开【数据库】，选择【创建视图向导】菜单项，单击【确定】按钮，将出现欢迎使用向导界面。 null③在欢迎使用向导界面中单击【下一步】，出现如图6-7所示的选择数据库对话框。选择数据库名称，此处选择XSCJ，单击【下一步】，出现选择对象对话框。图6-7 选择数据库④在如图6-8所示的选择对象对话框中选择与视图相关联的表，此处选择“学生基本信息表”，单击【下一步】，出现如图6-9所示的选择列对话框。 null图6-8 选择对象对话框 图6-9 选择列对话框 null⑤在选择列对话框中选择视图所需要的列，此处选择“学号”、“姓名”、“性别”、“族别”、“家庭住址”列，单击【下一步】，出现如图6-10所示的视图限制对话框。 图6-10 输入视图限制对话框 ⑥在视图限制对话框中输入对视图的限制条件，本例为：WHERE 族别<>'汉族'，单击【下一步】，出现如图6-11所示的输入视图名对话框。 null图6-11 输入视图名对话框⑦在输入视图名对话框中输入视图名，单击【下一步】，出现如图6-12所示的视图创建完成对话框，单击【完成】，则视图创建完毕。 null图6-12 视图创建完成对话框 3．使用CREATE VIEW语句创建视图 语法格式为： CREATE VIEW 视图名[(列名1,列名2[,…n])] [WITH ENCRYPTION]] AS 查询语句 [WITH CHECK OPTION]null其中： l   列名 视图中包含的列，可以有多个列名，最多可引用1024个列。若使用与源表或视图中相同的列名时，则不必给出列名。 l    ENCRYPTION 说明在系统表syscomments中存储CREATE VIEW语句时进行加密。 l   查询语句 用来创建视图的SELECT语句。可在SELECT语句中查询多个表或视图，以表明新创建的视图所参照的表或视图，但对SELECT语句有以下的限制： ①定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。 ②不能使用COMPUTE或COMPUTE BY子句。 ③不能使用ORDER BY子句。 ④不能使用INTO子句。 ⑤不能在临时表或表变量上创建视图。 l WITH CHECK OPTION 指出在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。例如对于V_05GZYY视图，只能修改除“班级编号”字段以外的字段值，而不能把“班级编号”字段的值改为“=’20051001’”以外的值，以保证仍可通过V_05GZYY视图查询到修改后的数据。 null【例6-3】创建一个名为“V_计算机系课程信息”的视图，要求显示系部编号为01的课程信息。 在查询分析器中运行如下命令： USE XSCJ GO CREATE VIEW V_计算机系课程信息 AS SELECT * FROM 课程信息表 WHERE 系部编号='01' GO 运行结果如图6-13所示。 图6-13 创建名为“V_计算机系课程信息”的视图 null【例6-4】使用SELECT语句查看“V_计算机系课程信息”视图的返回结果。 在查询分析器中运行如下命令： USE XSCJ GO SELECT * FROM GO 运行结果如图6-14所示。 图6-14 使用SELECT语句查看视图返回结果 null【例6-5】使用企业管理器查看“V_少数民族信息”视图的返回结果。 ①在控制台树中展开XSCJ数据库。 ②单击【视图】，在视图详细列表中右击【V_少数民族信息】视图，在弹出菜单中选择【切换到视图】下的【返回所有行】命令。返回结果如图6-15所示。 图6-15 使用企业管理器查看“V_少数民族信息”视图的返回结果 null6.2 管理视图 6.2.1 查看视图信息 每当创建了一个新的视图后，则在系统说明的系统表中就定义了该视图的存储，因此，可以使用系统存储过程sp_help显示视图特征，使用sp_helptext显示视图在系统表中的定义，使用sp_depends显示该视图所依赖的对象。它们的语法形式分别如下： （1）sp_help 数据库对象名称 （2）sp_helptext 视图（触发器、存储过程）名 （3） sp_depends 数据库对象名称 null【例6-6】使用系统存储过程sp_helptext查看“V_计算机系课程信息”视图的定义信息。 在查询分析器中运行如下命令： USE XSCJ GO EXEC sp_helptext V_计算机系课程信息 GO 运行结果如图6-16所示。                     图6-16 查看“V_计算机系课程信息”视图的定义   null【例6-7】使用系统存储过程sp_help显示视图“V_05GZYY”特征。 在查询分析器中运行如下命令： USE XSCJ GO sp_help V_05GZYY GO 运行结果如图6-17所示。                         图6-17使用系统存储过程sp_help显示视图“V_05GZYY”特征 null【例6-8】使用系统存储过程sp_depends显示视图“V_05GZYY”所依赖的对象。 在查询分析器中运行如下命令： USE XSCJ GO sp_depends V_05GZYY GO 运行结果如图6-18所示。                           图6-18 使用系统存储过程sp_depends显示视图“V_05GZYY”所依赖的对象   null6.2.2 修改视图 1．使用企业管理器修改视图。 【例6-9】使用企业管理器将视图“V_05GZYY”修改为只显示女生信息。 ①在控制台树中展开XSCJ数据库。 ②单击【视图】，在视图列表中双击【V_05GZYY】视图，可以看到该视图的属性对话框，如图6-19所示。可以在该对话框中直接对视图的定义进行修改，此处将条件改为“WHERE班级编号 = '20051001' AND 性别='女'”，修改完毕后，单击【确定】按钮返回。                           图6-19 使用企业管理器修改视图   null2．使用ALTER VIEW语句修改视图 语法格式如下： ALTER VIEW 视图名 [(列名1,列名2[,...n])] [WITH ENCRYPTION] AS 查询语句 [ WITH CHECK OPTION ] 【例6-10】使用ALTER VIEW语句修改视图“V_计算机系课程信息”，使其只显示“课程编号”、“课程名称”、“考核类型”、“任课教师”信息。 在查询分析器中运行如下命令： USE XSCJ GO ALTER VIEW V_计算机系课程信息 AS SELECT 课程编号,课程名称,考核类型,任课教师 FROM 课程信息表 WHERE 系部编号='01' GO 运行结果如图6-20所示。          图6-20 使用ALTER VIEW语句修改视图 null6.2.3 视图更名 1．使用企业管理器重命名视图 【例6-11】使用企业管理器将视图“V_05GZYY”重命名为“V_05高职应用”。 ①在控制台树中展开XSCJ数据库。 ②单击【视图】，在视图详细列表中右击“V_05GZYY”，在弹出的菜单中单击【重命名】命令。 ③输入视图的新名称“V_05高职应用”。 ④在重命名对话框中单击【是】按钮，确认新名称。 ⑤系统显示成功命名提示后，单击【确定】按钮。 2．使用系统存储过程sp_rename修改视图名称 该过程的语法形式如下： sp_rename 旧名称,新名称 null 【例6-12】使用系统存储过程sp_rename将视图“V_05高职应用”改回为“V_05GZYY”。 在企业管理器中运行如下命令： USE XSCJ GO sp_rename V_05高职应用,V_05GZYY GO 运行结果如图6-21所示。                   图6-21 使用系统存储过程sp_rename更改视图名称   null6.2.4 删除视图 1．使用企业管理器删除视图 【例6-13】使用企业管理器删除视图“V_05GZYY”。 ①在控制台树中展开XSCJ数据库。 ②单击【视图】，在视图详细列表中右击“V_05GZYY”，在弹出的快捷菜单中选择【删除】命令。 ③然后再单击【全部除去】按钮即可。 2．使用DROP VIEW语句删除视图 语法格式为： DROP VIEW 视图名[,…n] 可以使用该命令同时删除多个视图，只需在要删除的视图名称之间用逗号隔开即可。 null  【例6-14】使用DROP VIEW语句删除视图“V_计算机系课程信息”、“V_少数民族信息”。 在企业管理器中运行如下命令： USE XSCJ GO DROP VIEW V_计算机系课程信息,V_少数民族信息 GO 运行结果如图6-22所示。                   图6-22 使用DROP VIEW语句删除视图 null6.3 索引 6.3.1 索引基础知识 1．索引的概念 数据库中的索引与书籍中的索引类似。在一本书中，使用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是指某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 2．索引的作用（1）通过创建唯一索引，可以保证数据记录的唯一性。（2）可以大大加快数据检索速度。（3）可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。（4）在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。 （5）使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。 null3．索引的分类索引有聚集索引与非聚集索引之分。（1）聚集索引聚集索引对表和视图中的数据进行物理排序，然后再重新存储到磁盘上，这种索引对查询非常有效。表和视图中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQL Server会用主键列作为聚集索引键。 用户可以在表的任何列或列的组合上建立索引，但一般是为定义成主键约束的列建立聚集索引。 （2）非聚集索引 非聚集索引不用将表和视图中的数据进行物理排序。如果表中不存在聚集索引，则表是未排序的。在表或视图中，最多可以建立250个非聚集索引或者249个非聚集索引和1个聚集索引。 一般在那些经常被用来查询的表的列上建立索引以获得最佳查询性能。但索引也需要占用空间，表越大，建立的包含该表的索引也越大。当一个含有索引的表被改动时，改动数据的速度会减慢，所以不要在表中建立太多且很少用到的索引。 null6.3.2 创建索引 可以使用企业管理器和CREATE INDEX语句的方法创建索引。 1．使用企业管理器创建索引 【例6-15】在XSCJ数据库的“学生基本信息表”中创建基于“姓名”列的名为“IX_学生姓名”的非聚集索引、非惟一索引。 ①在企业管理器中，展开指定的服务器和数据库XSCJ，单击【表】。②在详细列表中右击“学生基本信息表”，在弹出的快捷菜单中选择【所有任务】项的【管理索引】选项，即会出现【管理索引】对话框，如图6-23所示。在该对话框中，可以选择要处理的数据库和表。此处选择XSCJ和“学生基本信息表”③单击【新建】按钮，则出现【新建索引】对话框，如图6-24所示。 ④选择“姓名”列，并输入索引姓名“IX_学生姓名”，完成后单击【确定】按钮，即可生成新的索引。  null图6-23 【管理索引】对话框 null图6-24 【新建索引】对话框   null2．使用CREATE INDEX语句创建索引 其语法格式如下： CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]INDEX 索引名 ON { 表名 |视图名 } ( 列名[ ASC | DESC ] [ ,...n ] ) [WITH [PAD_INDEX] [[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY] [[,]DROP_EXISTING]    [[,]STATISTICS_NORECOMPUTE] [[,]SORT_IN_TEMPDB] ] [ ON filegroup ]   null l        表名 用于指定创建索引的表的名称。 l        视图名 用于指定创建索引的视图的名称。 l        ASC|DESC 用于指定具体某个索引列的升序或降序排序方向。 l        CLUSTERED 用于指定创建的索引为聚集索引。 l        NONCLUSTERED 用于指定创建的索引为非聚集索引。 l        索引名 用于指定所创建的索引的名称。 l        列名 用于指定被索引的列。 PAD_INDEX 用于指定索引中间级中每个页（节点）上保持开放的空间。 l          l        FILLFACTOR = fillfactor 用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。 l        IGNORE_DUP_KEY 用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。 l        DROP_EXISTING 用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。 l        STATISTICS_NORECOMPUTE 用于指定过期的索引统计不会自动重新计算。 l        SORT_IN_TEMPDB 用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 ON filegroup 用于指定存放索引的文件组。 l                 UNIQUE 用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。 其中：null【例6-16】使用CREATE INDEX语句在XSCJ数据库的“成绩表”上创建名为“IX_学号课号”的聚集、惟一、复合索引，该索引基于“学号”和“课程编号”列创建。 在查询分析器中运行如下命令： USE XSCJ GO CREATE UNIQUE CLUSTERED INDEX IX_学号课号 ON 成绩表(学号,课程编号) GO null6.4 管理索引 6.4.1 查看、修改索引使用企业管理器查看、修改索引 要查看和修改索引的详细信息，可以在企业管理器中，展开指定的服务器和数据库项，用右键单击要查看的表，从弹出的快捷菜单中选择【所有任务】子菜单中的【管理索引】选项，则会出现【管理索引】对话框。选择要查看或者修改的索引，单击【编辑】按钮，就会出现【修改索引】对话框。在该对话框中，可以修改索引的大部分设置，还可以直接修改其SQL脚本，只需按下【编辑SQL…】按钮，即可出现【编辑SQL脚本】对话框，其中可以编辑、测试和运行索引的SQL脚本。 2. 使用系统存储过程查看、更名索引 系统存储过程sp_helpindex可以返回表的所有索引信息，其语法形式如下： sp_helpindex ‘表名称’ 另外，系统存储过程sp_rename可以用来更改索引的名称，其语法形式如下： sp_rename '表名.旧索引名','新索引名' null【例6-17】使用系统存储过程sp_helpindex查看XSCJ数据库中“成绩表”的索引信息。 在查询分析器中运行如下命令： USE XSCJ GO sp_helpindex '成绩表' GO 运行结果如图6-25所示。                 图6-25 使用系统存储过程sp_helpindex查看XSCJ数据库中“成绩表”的索引信息   null【例6-18】使用系统存储过程将索引“PK_成绩表”更名为“IX_成绩表”。 在查询分析器中运行如下命令： USE XSCJ GO EXEC sp_rename '成绩表.PK_成绩表','IX_成绩表' GO 运行结果如图6-26所示。 图6-26 使用系统存储过程将索引“PK_成绩表”更名为“IX_成绩表”                   null6.4.2 删除索引 1.使用企业管理器删除索引 要删除索引，可以在企业管理器中，从【管理索引】对话框中或者【表的属性】对话框中，选择要删除的索引，单击【删除】按钮，即可删除索引。 2.使用DROP INDEX命令删除索引 当不再需要某个索引时，可以将其删除，DROP INDEX命令可以删除一个或者多个当前数据库中的索引，其语法格式如下： DROP INDEX table.index | view.index [ ,...n ] 其中：table | view用于指定索引列所在的表或索引视图；index用于指定要删除的索引名称。注意，DROP INDEX命令不能删除由CREATE TABLE或者ALTER TABLE命令创建的主键或者唯一性约束索引，也不能删除系统表中的索引。 null【例6-19】删除“学生基本信息表”中名为“IX_学生姓名”的索引。 在查询分析器中运行如下命令： USE XSCJ GO DROP INDEX 学生基本信息表.IX_学生姓名 GO 运行结果如图6-27所示。                 图6-27 删除“学生基本信息表”中名为“IX_学生姓名”的索引   null本章小结 本章介绍了视图与索引的基础知识，重点介绍了视图与索引的创建与管理技术，用户应该掌握使用企业管理器和SQL语句的方法创建、管理索引和视图的技术，灵活灵活运用视图及索引以提高系统开发效率。 null练习与上机 一．选择题 1、 SQL SERVER 2000提供了系统过程（ ），可以用来修改视图的名称。 A、sp_help B、sp_bindefault C、sp_rename D、其他 2、下面语句中，哪种语句用来创建视图（ ）？ A、CREATE TABLE B、ALTER VIEW C、DROP VIEW D、CREATE VIEW 3、下面语句中，哪种语句用来修改视图（ ）。 A、CREATE TABLE B、ALTER VIEW C、DROP VIEW D、CREATE VIEW 4、下面语句中，哪种语句用来删除视图（ ）。 A、CREATE TABLE B、ALTER VIEW C、DROP VIEW D、CREATE VIEW 5、下列哪种情况的列适合使用聚簇索引（ ）？ A、 包含大量非重复值的列，即该列或更多的组合在数据表的记录中重复值极少。 B、 精确匹配查询的搜索条件WHERE子句中经常使用的列。 C、 包含数量有限的惟一值的列。 D、 以上都不对。 6、下列哪种情况的列适合使用非聚簇索引（ ）？ A、包含数量有限的惟一值的列。 B、用户经常使用BETWEEN，>，>=，<和<=运算符限制某列来查询满足条件的数据时。 C、包含大量非重复值的列。 D、以上都不对。 7、每个数据表可以创建 （ ）个聚簇索引。 A、1 B、2 C、10 D、无数个 8、每个数据表可以创建（ ） 个非聚簇索引。 A、1 B、100 C、249 D、无数个 null二．填空题 1、在SQL SERVER 2000中，创建视图有三种方法：___________、________和_______________。 2、索引可以分为_________索引和_________索引。 3、视图名及创建日期等信息存放在系统表________________中。 4、视图是从一个或多个基本表（或视图）派生出的_________表。 5、可以使用系统存储过程_________显示视图特征，使用_________显示视图在系统表中的定义，使用_________显示该视图所依赖的对象。 6、系统存储过程_________可以返回表的所有索引信息，系统存储过程_________可以用来更改索引的名称。   三．简答题 1．简述视图的概念和优点： 2．创建视图时，需要注意哪些事项？ 3．简述索引的概念和作用？ 4．简述聚集索引和非聚集索引的涵义与要求？ null项目实训 1、 在XSCJ数据库中创建视图： （1）创建名为v_men的视图，该视图仅查看学生基本信息表中男生的基本信息。 （2）创建名为v_all的视图，该视图查看学生基本信息表中所有学生的学号、姓名、性别、出生日期、政治面貌、家庭住址、班级信息。 2、查看视图； 3、修改视图的属性； 4、视图的修改与删除； 5、视图的重命名及显示视图信息 6、创建各表索引： （1）基于学生基本信息表的学号列创建名为IX_学号的聚集、唯一索引； （2）基于成绩表的学号、课程ID列创建名为IX_成绩的聚集、唯一索引、复合索引； （3）基于成绩表的成绩列创建名为IX_成绩的非聚集索引，并删除； 7、显示索引信息。 8、对索引进行重命名。