数据库应用技术课程辅导62

1、数据库应用技术课程辅导 6-2第 6 章 数据库其他对象管理之索引 存储过程 触发器6.2 索引6.2.1 索引基本概念在数据库中建立索引是为了加快数据的查询速度。 数据库中的索引是一个表中某个 （或 某些）列的值列表，其中注明了列值所对应的行数据所在的存储位置。SQL Server 中的索引一般采用 B 树结构。索引由索引项组成， 索引项由表中的一个或多个列 （称为索引关键字） 组成。 B 树按索引关键字排序。6.2.2 索引的分类从数据的组织方式看，索引分为聚集索引（ Clustered Index ，也称为聚簇索引）和非 聚集索引（ Non-clustered Index ，也称为非聚簇

2、索引） 。聚集索引对数据按索引关键字进行 物理排序，非聚集索引不对数据进行物理排序。在SQL Server 中，聚集索引和非聚集索引都采用 B 树结构来存储索引项， 都包含数据页和索引页 （索引也以数据页为存储分配单位） 其中索引页存放索引项和指向下一层的指针，数据页用来存放数据。建立聚集索引后， 数据将按聚集索引项的值进行物理排序。 一个表只能包含一个聚集索 引。下列情况可考虑创建聚集索引：包含大量非重复值的列。使用下列运算符返回一个范围值的查询： BETWEEN AN、D、=、 和 = 。 经常被用于进行连接操作的列。ORDER BY或 GROUP BY子句使用的列。下列情况不适于建立聚集

3、索引：频繁更改的列。因为这将导致索引项的整行移动。 字节长的列。因为聚集索引的索引项值将被所有非聚集索引作为查找关键字使用， 并被存储在每个非聚集索引的叶级索引项中。3非聚集索引非聚集索引与图书后边的术语表类似。 数据存储在一个地方， 术语表存储在另一个地方。 而且数据并不按术语表的顺序存放， 但术语表中的每个词在书中都有确切的位置。 非聚集索 引就类似于术语表，而数据就类似于一本书的内容。非聚集索引与聚集索引一样采用 B 树结构存储，但有两个重要差别： 非聚集索引的数据不按索引关键字值的顺序排序和存储。 非聚集索引的叶节点不是存放数据的数据页。由于非聚集索引并不改变数据的物理存储顺序， 因此

4、，可以在一个表上建立多个非聚集 索引。就象一本书可以有多个术语表一样。下述情况可考虑创建非聚集索引： 包含大量非重复值的列。如果某列只有很少的非重复值，比如只有1 和 0，则不对这样的列建立非聚集索引。不返回大型结果集的查询。经常作为查询条件使用的列。 经常作为连接和分组条件的列。4唯一索引唯一索引可以确保索引列不包含重复的值。聚集索引和非聚集索引都可以是唯一的。因此， 只要列中的数据是唯一的， 就可以在一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。6.2.3 创建和删除索引1创建索引创建索引使用的是 CREATE INDEX语句，其一般语法格式为：CREATE UNIQUE CLUS

5、TERED | NONCLUSTEREDINDEX 索引名 ON 表名 ( 列名 ASC | DESC ,.n )其中：UNIQUE表示要创建唯一索引。CLUSTERED表示要创建聚集索引。NONCLUSTERE表示要创建非聚集索引。 ASC | DESC ：指定索引列的升序或降序排序方式。默认值为ASC。如果没有指定索引类型，则默认是创建非聚集索引。例1在Student表的Sname列上创建一个非聚集索引。CREATE INDEX Sname_indON Student (Sname)例 2在 Student 表的 Sid 列上创建一个唯一聚集索引。CREATE UNIQUE CLUSTER

6、ED INDEX Sid_indON Student (Sid)例 3在 Employee 表的 FirstName 和 LastName 列上创建一个聚集索引。CREATE CLUSTERED INDEX EName_indON Employee (FirstName, LastName)2删除索引索引一经建立， 就由数据库管理系统自动使用和维护， 不需要用户干预。 建立索引是为 了加快数据的查询效率， 但如果需要频繁的对数据进行增、删、改操作， 则系统会花费很多 时间来维护索引，这会降低数据的修改效率； 另外，存储索引需要占用额外的空间，这增加 了数据库的空间开销。因此，当不需要某个索引时

7、，应将其删除。删除索引的SQL语句是DROP INDEX其一般语法格式为：DROP INDEX 索引名 ON 表名其中 表名 为包含要删除索引的表。例 4删除 Student 表中的 Sname_ind 索引。DROP INDEX Sname_ind ON Student6.3 存储过程6.3.1 存储过程概念存储过程是存储在数据库服务器端供客户端调用执行的SQL语句。客户端应用程序可以直接调用并执行存储过程，存储过程的执行结果可返回给客户端。数据库中的存储过程与一般程序设计语言中的过程或函数类似，存储过程可以： 接受输入参数并以输出参数的形式将多个值返回给调用者。 包含执行数据库操作的语句。

8、将查询语句执行结果返回到客户端内存中。使用存储在数据库服务器端的存储过程而不使用嵌入到客户端应用程序中SQL 语句的好处有（1）允许模块化程序设计（2）改善性能（3）减少网络流量（4）可作为安全机制使用存储过程实际是存储在数据库服务器上的、由 SQL 语句和流程控制语句组成的预编译 集合，它以一个名字存储并作为一个单元处理，可由应用程序调用执行，允许包含控制流、 逻辑以及对数据的查询等操作。 存储过程可以接受输入参数， 并可具有输出参数， 还可以返 回单个或多个结果集。6.3.2 创建和执行存储过程创建存储过程的 SQL语句为CREATE PROCEDUR其语法格式为：CREATE PROCE

9、DURE 存储过程名 参数名 数据类型 = default OUTPUT,n ASSQL语句n 其中：default ：表示参数的默认值。如果定义了默认值，则在调用存储过程时，可以省略 该参数的值。OUTPUT表明参数是输出参数。使用OUTPUTS数可将存储过程产生的信息返回给调用者。执行存储过程的SQL语句是EXECUTE其语法格式为： EXEC UTE 存储过程名 实参 , OUTPUT , n 本章的所有示例均在第 4、5章的Student、Course和SC表及数据上进行。例 1不带参数的存储过程。查询计算机系学生的考试情况，列出学生姓名、课程名和 考试成绩。CREATE PROCED

10、URE p_StudentGrade1ASSELECT Sname, Cname, GradeFROM Student s INNER JOIN SCON s.Sno = SC.Sno INNER JOIN Course cON c.Cno = sc.CnoWHERE Sdept = ' 计算机系 '执行此存储过程： EXEC p_StudentGrade1例 2 带输入参数的存储过程。查询某个指定系学生的考试情况，列出学生姓名、所在 系、选的课程名和考试成绩。CREATE PROCEDURE p_StudentGrade2 dept char(20)ASSELECT Snam

11、e, Sdept, Cname, GradeFROM Student s INNER JOIN SCON s.Sno = SC.Sno INNER JOIN Course cON c.Cno = SC.CnoWHERE Sdept = dept 如果存储过程有输入参数并且没有为输入参数指定默认值， 则在调用存储过程时， 必须 为输入参数指定一个常量值。执行例 2 定义的存储过程，查询信息管理系学生的修课情况：EXEC p_StudentGrade2 ' 信息管理系 '例 3 带多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，课 程的默认值为“ VB”。CREA

12、TE PROCEDURE p_StudentGrade3sname char(10), cname char(20) = 'VB'ASSELECT Sname, Cname, GradeFROM Student s INNER JOIN SCON s.Sno = SC.sno INNER JOIN Course cON c.Cno = SC.CnoWHERE sname = sname AND cname = cname执行带多个参数的存储过程时，参数的传递方式有两种：( 1)按参数位置传值 ( 2)按参数名传值6.3.3 查看和维护存储过程1查看已定义的存储过程SQL Ser

13、ver 2008 在 SSMS 工具的“对象资源管理器”中将列出已定义好的全部存储过 程。请参考有关教材内容学习可视化的查看方法。2修改存储过程可以对已创建好的存储过程进行修改。修改存储过程的SQL语句为ALTER PROCEDURE其语法格式为：ALTER PROC EDURE 存储过程名 参数名 数据类型 = default OUTPUT , . n ASSQL 语句 .n 可以看到，修改存储过程的语句与定义存储过程的语句基本是一样的，只是将 CREATE PROC EDURE 改成了 ALTER PROC EDURE。3删除存储过程当不再需要某个存储过程时，可将其删除。删除存储过程可通过

14、 SQL语句实现，也可以 通过SSMS勺“对象资源管理器”实现。删除存储过程的 SQL语句为DROP PROCEDUR具体语法格式为：DROP PROC | PROCEDURE 存储过程名,n 例 1删除 p_StudentGrade1 存储过程。DROP PROC p_StudentGrade1使用SSMST具删除存储过程的方法为：在 SSMS的“对象资源管理器”中，展开包含要 删除存储过程的数据库 可编程性 存储过程， 在要删除的存储过程上右击鼠标， 然后在弹 出的菜单中选“删除”命令即可。6.4 触发器触发器是一段自动执行的代码，引发触发器代码执行的是对数据的更改操作，包括： UPDAT

15、E INSERT和DELETE触发器通常用于保证业务规则和数据完整性，其主要优点是用 户可以用编程的方法来实现复杂的处理逻辑和业务规则以及复杂的数据完整性约束。在完整性约束方面，触发器可以实现比CHECK勺束更复杂的约束规则。6.4.1 创建触发器创建触发器时，要指定触发器的名称、触发器所作用的表、引发触发器的操作以及在触 发器中要完成的功能。创建触发器的SQL语句为CREATE TRIGGER其语法格式为：CREATE TRIGGER 触发器名ON 表名 FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE ASSQL 语句其中：触发器名在数据库

16、中必须是惟一的。ON子句用于指定在其上执行触发器的表。AFTER指定只有在引发触发器的 SQL语句已成功执行，并且所有的约束检查也成功 完成后，才执行此触发器。FOR作用同AFTERINSTEAD OF指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代引发语句的操作。INSERT DELETE和UPDATE是引发触发器执行的操作，若同时指定多个操作，则各 操作之间用逗号分隔。创建触发器时，需要注意如下几点：（1）在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由一个或多个数据更改语句引发。对于AFTER型的触发器，可以在同一种操作上建立多个触发器；对于INSTEAD O

17、F型的触发器，在同一种操作上只能建立一个触发器。（2）大部分SQL语句都可用在触发器中，但也有一些限制。例如，所有的创建和更改数据库以及数据库对象的语句、所有的DRO齬句都不允许在触发器中使用。（3） 在触发器中可以使用两个特殊的临时工作表：INSERTED表和DELETE滾，这两个表 的结构同建立触发器的表的结构完全相同。INSERTED表保存了 INSERT操作中新插入的数据和UPDATE操作中更新后的数据；DELETED保存了 DELETE操作中删除的数据和 UPDATE操作中更新前的数据。在触发器中对这两个临时工作表的使用方法同一般基本表一样，可以通过对这两个临时工作表所保存的数据进行

18、分析，来判断所执行的操作是否符合约束要求。如果某个表上即定义了完整性约束，又定义了触发器，则是先执行完整性约束检查，符合约束后才执行数据操作语句，然后才引发触发器执行。因此完整性约束的检查总是先于触 发器的执行。642后触发型触发器使用FOR或AFTER选项定义的触发器为后触发型的触发器，即只有在引发触发器执行的语句中指定的操作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。后触发型触发器的执行过程如图6-1所示。图6-1后触发型触发器执行过程从图6-1可以看到，当后触发型触发器执行时，引发触发器 执行的数据操作语句已经执行完成，因此，在编写后触发型触发器时，需要在触发器中判断所实现的操作是否违反了完整性约 束，如果是则必须撤销该操作（即回滚操作）。触发器与引发触发器执行的操作共同构成了一个事务，这个事务是系统隐含建立的。事务的开始是引发触发器执行的操作，事务的结束是触发器的结束。由于AFTER型触发器在执行时，引 发触发器执行的操作已经执行完了，因此，在触发器中应使用 ROLLBACK句撤消不正确的操作，这里的ROLLBACK际是回滚到引发触发器执行的操作之 前的状态，也就是撤消了违反完整性约束的操作。小资料：事务事务是数据