第4章 视图和索引课件.ppt

《第4章 视图和索引课件.ppt》由会员分享，可在线阅读，更多相关《第4章 视图和索引课件.ppt（26页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、第 4 章视图和索引4.1 【案例 13】视图相关知识1视图概述（1）视图概念在说明视图之前，先了解基本表的含义。基本表 Basic Table）是本身独立存在的表。在 SQL Server 2005中，如果创建了一个基本表，就可以往“表”里添加记录了，并且这些记录存放在物理媒介（比如硬盘）上。例如【案例 4】中创建的“学生基本档案”表、“课程信息”表、“成绩”表等都是基本表。视图（View）：是从一个或多个基本表导出的表。在 SQL Server 中，如果创建了一个视图，那么在数据库中仅仅存储了有关视图的定义，而不存放视图所对应的记录，这些记录存放在导出视图的基本表中，因此，视图是一个“虚表

2、”，但用户可以像对基本表一样对它进行各种操作。视图和表一样，也包括几个被定义的数据列和多个数据行，但就本质而言，这些数据列和数据行来源于视图所引用的表，所以视图不是真实存在的物理表，而是一张虚表。视图（索引视图除外）所对应的数据并不实际地以视图结构存储在数据库中，而是存储在试图所引用的表中。基本表中的数据发生变化，从视图中查询出的数据ll也就随之改变。从这个意义上讲，视图就像一个窗口，透过它可以看到数据中自己感兴趣的数据及其变化。视图定义以后，就能和基本表一样被查询、被删除，还可以在一个视图之上再定义新的视图，但对视图的更新（新增、删除、修改）操作则有一定的限制。视图定义后便存储在数据库中，与

3、其相对应的数据并没有在数据库中另外存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改（有一定的限制）和删除。当对视图中的数据进行修改时，相应的基本表的数据也要发生变化，同时，如果基本表的数据发生变化，则这种变化也可以自动地反映到视图中。（2）视图与基本表的区别视图是建立在基本表的基础上的，但与基本表相比，视图有以下优点。视点集中。视图的机制使用户把注意力集中在他们所需要的特定数据或所负责的特定业务，从而使用户眼中的数据结构简单而直截了当。如用户可以选择特定行或特定列，不需要的数据可以不出现在视图中，增强了数据的安全性；而且视图并不实际包含

4、数据，SQL Server 2005 只在数据库中存储视图的定义。隐藏数据的复杂性，简化操作。视图的建立大大简化了用户的数据查询操作。因为视图是把若干张表连接在一起，向用户隐藏了表与表之间的连接操作，隐藏了数据库设计的复杂性，如果开发者改变数据库设计，不会影响到用户与数据库交互。另外，用户可将经常使用的连接查询、嵌套查询或合并查询定义为视图，lll这样，用户每次对特定的数据执行进一步的操作时，无须指定所有条件和限定，因为用户只须查询视图，而无须提交复杂的基础查询。多角度，从导构源组织数据。方便不同的用户从多角度“看待”同一数据。当许多不同种类的用户使用同一个集成数据库时，这种灵活性显然很重要。

5、视图机制可以在连接两个或多个表的复杂查询的基础上创建视图，并以单个表的形式显示给用户，即分区视图，分区视图可基于来自导构源的数据，如远程服务器或来自不同数据库中的表。安全。针对不同用户可以定义不同的视图，使机密数据不再出现在不应该看到这些数据的用户视图上，显然这就提供了对机密数据的保护。借助于视图可以提供改善数据的安全性，视图的安全性可以让特定的用户查看特定的数据，也可以防止未授权用户查看特定的行或列。实现将视图的操作权限赋予特定的用户，要限制用户只能看到表中特定行的方法包括：在表中增加一个标志用户名的列、建立视图，保证用户只能看到标有自己用户名的行，把视图授权给其他用户。逻辑上的数据独立。视

6、图可避免数据库中表的结构变化对用户程序造成不良影响。例如，当一个大表“垂直”地分成多个表时，只要重新定义视图就可以保持用户原来的关系，使用的外模式不变，从而不必修改用户程序，原来的应用程序仍能通过视图重载数据。当然，视图只能在一定程度上提供数据的逻辑独立，修改数据的语句仍会因基本表的结构改变而受到影响。llllllll（3）视图与查询的区别视图虽然保存的是 SELECT 查询，但与普通查询在使用上有一定的区别。数据库服务器在视图保存后可以立即建立查询计划。但是对于查询，数据库服务器直到查询实际运行时才能建立查询计划，也就是说，普通查询在用户显式请求结果集时建立查询计划。可以加密视图，但不能加密

7、查询。可以为查询创建参数，但不能为视图创建参数。可以对任何查询结果排序，但是只有当视图包括 TOP 子句时才能排序视图。视图可以建立索引，提高查询速度。视图可以屏蔽真实的数据结构和复杂的业务逻辑，简化查询。视图存储为数据库设计的一部分，而查询则不是。对视图和查询的结果集更新限制是不同的。2使用 SSMS 管理视图在 SQL Server 2005 中可以使用 SQL Server Management Studio 实现对视图的创建、修改、查看和删除等操作。下面将通过以下案例学习视图的创建、修改、查看和删除等操作方法。（1）创建视图（2）修改视图修改视图实际上就是修改对应的 SELECT 语句

8、。视图名称前面带有“锁”标记，表示该视图被加密，不能被修改。（3）查看视图属性（4）查看视图依赖关系视图中的数据可以是一个表（或视图）中的特定数据，也可以是来自多个表（或视图）中的特定数据，因此视图是依赖于表（或视图）而存在的。同时，一个视图也可以成为其他视图所依赖的基础。了解视图的依赖关系有助于视图的维护和管理。（5）视图的重命名重命名视图必须遵循以下规则：重命名的视图必须位于当前数据库中；新名称必须遵守标识符规则；重命名视图并不更改它在视图定义文本中的名称；只能对具有更改权限的视图进行重命名；数据库所有者可以更改任何用户视图的名称；视图可以作为另一视图的数据来源，重命名视图有可能会影响到其

9、他的对象。（6）删除视图在创建视图后，如果不再需要该视图，或想清除视图定义以及与之相关联的权限，可以删除该视图。删除视图后，基础表和基础视图并不受到影响，但任何使用基于已删除视图的查询将会失败。如果删除的视图是另一个视图的基础视图，则当删除该视图时，系统会给出错误提示。在删除之前可以通过单击“显示依赖关系”按钮了解该视图与其他对象的关系。既可以了解依赖该视图的对象，也可以了解该视图所依赖的对象。因此，通常基于数据表定义视图，而不是基于其他视图来定义视图。3使用 T-SQL 语句管理视图在 SQL Server 2005 中可以使用 T-SQL 语句实现对视图的创建、修改、查看和删除等操作。我们

10、将通过以下案例学习视图的创建、修改、查看和删除等操作方法。（1）CREATE VIEW 语句格式使用 T-SQL 语句中的 CREATE VIEW 命令可以创建视图，基本语句格式如下所示。CREATE VIEW 视图名 (列名,n )WTTH AS查询语句WTTH CHECK OPTIN（2）简单视图的创建（3）使用聚合函数（4）使用 T-SQL 语句修改视图使用 T-SQL 语句中的命令 ALTER VIEW 可以修改视图，基本语句格式如下所示。ALTER VIEW 视图名 (列名,.n)WITHAS查询语句WITH CHECK OPTIONALTER VIEW 语句格式与 CREATE V

11、IEW 语句格式基本相同，修改视图的过程就是先删除原有视图，然后根据查询语句再创建一个同名的视图过程。但是它又不完全等同于删除一个视图，然后又重新创建该视图。因为新建视图需要重新指定视图的权限，而修改视图不会改变原有的权限。（5）使用 sp_rename 重命名视图使用系统存储过程 sp_rename 可以重命名视图，但不会删除视图，也不会删除在该视图上的权限。系统存储过程 sp_rename 的基本语句格式如下所示。sp_rename,（6）查看视图使用系统存储过程 sp_help 可以查看视图的定义，基本语句格式如下所示。Sp_help【案例 13-9】要查看视图“View_年龄最大”的属

12、性，执行语句效果如图 4-1-24 所示。（7）查看视图的文本使用系统存储过程 sp_helptext 可以查看视图的文本，基本语句格式如下所示。Sp_helptext（8）删除视图使用 DROP VIEW 可以删除单个视图也可以删除多个视图，在 DROP VIEW 命令中，需要被删除的视图名之间以逗号隔开。DROP VIEW 的基本语句格式如下所示。DROP VIEW删除视图时，要注意以下几点：删除视图时，将从系统目录中删除视图的定义和有关视图的其他信息，还将删除视图的所有权限；使用 DROP TABLE 删除表上的任何视图都必须使用 DROP VIEW 显示删除；对索引视图执行DROP V

13、IEW 操作时，将自动删除视图上的所有索引。图 4-1-24查看“View_年龄最大”视图属性图 4-1-25查看“View_年龄最大”视图文本4视图的查询视图与表具有相似的结构，视图创建后，可以通过视图对源表中的数据进行查询操作。（1）简单查询（2）使用聚合函数5更新视图操作（1）更新视图操作的种类当向视图中插入或更新数据时，实际上是对视图所基于的表执行数据的插入和更新。但是通过视图插入、更新数据有一些限制。更新视图操作包括 3 类：插入（INSERT）操作、修改（UPDATE）操作、删除（DELETE）操作。（2）更新视图的说明由于视图实际上并不存储记录，因此对视图的更新最终要转换为对基本

14、表的更新，为防止用户通过视图对数据进行增加、删除、修改、或者有意无意地对不属于视图范围内的基本表记录进行操作，最好定义在视图时加上WITHCHECK OPTION 子句，这样在视图上修改数据时间时，SQL Server 会检查视图定义中的条件，若不满足条件，则拒绝执行该操作。更新视图时，需要注意以下两点：当要通过视图修改多个视图基表时，必须给出多个单独的修改基表的语句来一起完成；为了保证多个单独的修改语句都能被执行，可以通过显式事务的方式来实现。（3）插入操作（INSERT）（4）修改操作（UPDATE）（5）删除操作（DELETE）4.2 【案例 14】索引相关知识1索引的概念索引是一个单独

15、的、物理的数据结构，这个数据结构中包括表中的一列或若干列的值以及指向表中物理标识这些值的数据页的逻辑指针的集合。索引提供了数据库中编排表中数据的内部方法。与书中的目录一样，数据库中的索引使用户可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键以及映射到指定数据的存储位置的指针。通过创建设计良好的索引，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。索引是对数据库表中一个或多个列的值进行排序的结构。不同的索引对应不同的排序方法，就像查字典时如果使用拼音法，则按汉字的拼

16、音进行排序：如果使用部首法，则按汉字的部首进行排序。通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引将占用磁盘空间，并且降低添加、删除和更新行的速度。不过在多数情况下，索引所带来的数据检索速度的优势大大超过它的不足之处。然而，如果应用程序非常频繁地更新数据，或者磁盘空间有限，那么最好限制索引的数量。索引依赖于数据库的表，作为表的一个组成部分，一旦创建后，由数据库系统自身进行维护。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面，索引就存放在索引页面上。通常，索引页面相对于数据页面来说小得多。当进行数据检索时，系统先搜索索引页面，从中找到所需数

17、据的指针，再直接通过指针从数据页面中读取数据。从某种程度上可以把数据库看做一本书，把索引看做书的目录，通过目录查找书中的信息，会使查找更方便、快捷。2索引的类型按存储结构的不同，可将索引分为两大类：聚集索引和非聚集索引。而根据数据库的功能，可在数据库设计时创建三种类型的索引：聚集索引、唯一索引和主键索引。（1）聚集索引聚集索引（Clusered Index）像一本书的目录，目录的顺序与实际的页码顺序是一致的。采用聚集索引可以保证表中的数据的物理存储顺序和排列顺序相同，它使用表中的某一列或多列来排序记录。索引值是按照有序的次序排列在聚集索引的结构中，索引是由索引页面组成的，索引页面具有层次性，聚

18、集索引的底层成为叶子级，它包含了实际的数据，用来存放表中的数据；上层称为非叶级。在聚集索引中，记录的索引顺序与物理顺序完全相同，索引树的叶子页包含了实际的数据。聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的。由于聚集索引对表中的数据一一进行了排序，因此用聚集索引查找数据很快。但由于聚集索引将表的所有数据完全重新排列了，它所需要的空间也就特别大，大概相当于表中数据所占空间的 120%。表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个聚集索引。每个数据库中的每个表只能有一个聚集索引，因为一个表中的记录只能以一种物理顺序存放。通常情况

19、下是对一个表按照主关键字字段建立聚集索引，也可以对其他类型的字段建立聚集索引，如字符型、数值型和日期时间型字段等。在聚集索引中，需要注意的是，记录存放的物理顺序相同。因而，聚集索引最适合于范围搜索在硬盘上存放的相邻的记录将被存放在相同的页面上或相邻近的页面上。如果用户在 SSMS 中建立表，而不是使用 T-SQL 语句来建立表，则这个索引就成为非聚集索引。当表中有设置为 Primary key 的字段时，SQL Server 会在该 Primary key 字段上自动建立聚集索引，索引名称为“PK_表名称”。创建聚集索引时，需要注意以下几点：每张表只能有一个聚集索引，应该首先建立；创建索引所需

20、的空间来自用户的数据库，而不是 TEMPDB 数据库；主关键字是聚集索引的首选字段；默认设置是非聚集索引。（2）非聚集索引非聚集索引像一本书后的标准索引一样，通常情况下，索引表中的顺序与实际的页码顺序是不一致的。一本书也许有多个索引表。例如，它也许同时有主题索引和作者索引。l非聚集索引具有与表的数据完全分离的结构，表的物理顺序与索引顺序不同，即表的数据并不是按照索引列顺序。在非聚集索引结构中，叶子页指向表中的记录，也就是说，表中记录的物理存储顺序与逻辑顺序没有必然的联系。使用非聚集索引不用将物理数据页中的数据按列排序，而是存储索引行，每个索引行均包含非聚集索引键值和一个或多个指向包含该值的数据

21、行的行定位器。如果表有聚集索引，行定位器就是该行的聚集索引键值；如果表没有聚集索引，行定位器就是行的磁盘地址。在搜索数据时，先对非聚集索引进行搜索，然后通过相应的行定位器从表中找到对应的数据。单个分区中的非聚集索引结构如由非聚集索引使用索引页存储，因此它比聚集索引需要更多的存储空间，且检索效率较低。但一个表只能建一个聚集索引，当用户需要建立多个索引时，就需要使用非聚集索引了。默认情况下，在 SQL 中，数据库中的每个表可以有多个非聚集索引。从理论上讲，一个表最多可以建 249个非聚集索引。一般情况下，先创建聚集索引，后创建非聚集索引，因为创建聚集索引会改变表中行的顺序，从而会影响到非聚集索引。

22、（3）比较聚集索引和非聚集索引存取速度：从建立聚集索引的表中取出数据要比建立非聚集索引的表快。当需要取出一定范围的数据时，用聚集索引也比用非聚集索引好。ll索引的数量：对聚集索引的主要限制是每个表只能建立一个聚集索引。而每个表最多可以建立 249 个非聚集索引。可以对一个表同时建立聚集索引和非聚集索引。所需空间：非聚集索引需要大量的硬盘空间和内存，虽然非聚集索引可以提高表中取数据的速度，它也会降低向表中插入和更新数据的速度。每个用户改变了一个建立非聚集索引的表中的数据时，必须同时更新索引，因此对一个表建立非聚集索引时要慎重考虑。如果一个表需要频繁地更新数据，那么不要对它建立太多的非聚集索引。如

23、果硬盘和内存有限，也应该限制使用非聚集索引的数量。（4）唯一索引唯一索引允许两行具有相同的索引值。如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接收此数据。例如，在“学生基本档案”表中的“姓名”列上创建了唯一索引，则所有学生姓名不能相同。唯一索引既可以是聚集索引，也可以是非聚集索引。（5）主键索引数据库表通常有一列或列组合，其值用来唯一标识表中的每一行，该列称为表的主键。在数据库关系图中，为表定义一个主键将自动创建主键索引，主键索引为聚集索引，是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用

24、主键索引时，它还允许快速访问数据。3使用 SSMS 管理索引（1）创建索引在“课程信息”表创建基于“课程名称”的唯一索引。具体操作步骤如下所述。 启动 Microsoft SQL Server Management Sudio 程序，展开数据库“选课管理”“课程信息”“索引”结点，右击，在弹出的快捷菜单中选择“新建索引”命令。 在“新建索引”窗口中输入索引名称为“INDEX_课程名称”，选择索引类型为“非聚集”索引，勾选“唯一”复选框，如图 4-2-6 所示。 单击“添加”按钮，选择“课程名称”添加到索引键的列。 单击“确定”按钮，返回“常规”页面，单击“确定”按钮完成创建，效果如图 4-2-

25、7 所示。图 4-2-6 “新建索引”窗口图 4-2-7输入索引名称为“INDEX_学生姓名”（2）查看索引 启动 Microsoft SQL Server Management Sudio 程序，展开数据库“选课管理”“课程信息”“索引”结点“INDEX_课程名称”结点，右击，在弹出的快捷菜单中选择“属性”命令。 在属性查看窗口可以查看到指定索引的属性，同时也可以进行相关的修改，效果如图 4-2-8 所示。图 4-2-8索引属性窗口 这里创建的索引为唯一、非聚集索引。索引一旦创建后，执行查询时由数据库管理系统自动启用。 在“INDEX_课程名称”结点上，右击，在弹出的快捷菜单中选择“禁用”命

26、令，可以禁用指定的索引，如图 4-2-9 所示。=图 4-2-9 选择“禁用”命令（3）索引的重命名 启动 Microsoft SQL Server Management Sudio 程序，展开数据库“选课管理”“课程信息”“索引”结点“INDEX_课程名称”结点，右击，在弹出的快捷菜单中选择“重命名”命令。 或者在选定的索引名上单击，进入编辑状态。 输入新的索引名称，完成重命名。（4）索引的删除 启动 Microsoft SQL Server Management Sudio 程序，展开数据库“选课管理”“课程信息”“索引”结点“INDEX_课程名称”结点，右击，在弹出的快捷菜单中选择“删除

27、”命令，如图 4-2-10 所示。 打开“删除对象”窗口，单击“确定”按钮，效果如图 4-2-11 所示。图 4-2-10 选择“删除”命令图 4-2-11 “删除对象”窗口llllll4使用 T-SQL 语句管理索引（1）CREATE INDEX 语句格式使用 CREATE INDEX 语句可以为数据库表中的列创建索引。CREATE INDEX 语句的基本语法格式为：CREATE UNIQUECLUSTERED NONCLUSTEREDINDEX index_nameON table_name view_namecolumn_name 1n ASC | DESC语句参数说明：UNIQUE：指

28、定创建的索引是唯一的索引。如果不是使用这个关键字，创建的索引就不是唯一的索引。CLUSTERED | NONCLUSTERED：指定被创建索引的类型。使用 CLUSTERED 来创建聚集索引，使用 NONCLUSTERED来创建非聚集索引。这两个关键字只能选用一个。index_name：表示要创建的索引的名称。table_name view_name：指明要创建索引的表或视图名。column_name：表示在表或视图的哪些列上创建索引。如果一个索引中只包含一列，则该索引称为简单索引，如果包含一个以上的列，则称为复合索引。（2）创建聚集索引（3）创建非聚集索引（4）创建唯一索引按照实现的功能，有

29、一类索引被称为唯一索引（UNIQUE）。它既可以采用聚集索引的结构，又可以采用非聚集索引的结构。唯一索引不允许两行具有相同的索引值；能够实现实体完整性；在创建主键约束和唯一时自动创建。在创建唯一索引时，如果在该字段上存在重复值，那么系统将返回错误信息。5全文索引的概念全文索引包含在全文目录中。每个数据库可以包含一个或多个全文目录。一个目录不能属于多个数据库，而每个目录可以包含一个或多个表的全文索引。一个表只能有一个全文索引，因此每个有全文索引的表只属于一个全文目录。全文目录和索引不存储在它们所属的数据库中。目录和索引由 Microsoft 搜索服务分开管理。SQL Server 2005 全文索引为在字符串数据中进行复杂的词搜索提供有效支持。全文索引 存储关于重要词和这些词在特定列中的位置的信息。全文查询利用这些信息，可以快速搜索包含具体某个词或一组词的行。全文索引由键值填充。每个键的项提供与该键相关联的重要词、它们所在的列和它们在列中的位置等有关信息。全文索引必须在基表上定义，不能在视图、系统表或临时表上定义。全文索引的定义包括以下两点：能唯一标识表中各行的列（主键或候选键），而且不允许空值；索引所覆盖的一个或多个字符串列。全文索引与普通的 SQL 索引的比较如表 4-2-1 所示。