任务6.5 索引与事务的应用(课件）-《网站数据库应用技术-SQL Server》同步教学（电子工业版）

项目六 数据库的编程操作 1 任务6.1 建立视图 任务6.2 游标 的创建与应用 任务6.3 存储过程的创建与管理 任务6.4 触发器的创建与管理 任务6.5 索引 与事务的应用 1 2 3 4 5 目录页 CONTENTS PAGE 2 任务6.5 索引与事务的应用 6.5.1 索引的创建与使用 6.5.2 处理事务 过渡页 TRANSITION PAGE 3 索引与事务的应用 6.5.1 索引的创建与使用 索引（Index）是表的对象，它属于表，使用索引可以快速访问数据库的表中的特定信息，提高检索速度。索引是对表中一列或多列的值进行排序的一种结构。 在关系型数据库中，一个表的存储由数据页面和索引页面两部分构成，但索引页面很小，主要存储空间分配给了数据页面。索引是一种与表有关的数据结构，它可以使对应于表的SQL语句执行得更快。索引相当于图书的目录，可以根据目录中的页码快速找到所需的内容。 索引是一个单独的、物理的数据结构，它是某个表中一列或若干列的值的集合和相应指向表中物理标识这些值的数据页面的逻辑指针清单。 索引提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针进行排序。 任务6.5 4 索引与事务的应用 6.5.1 索引的创建与使用 01 02 03 04 好处2 加速表和表之间的连接。 好处1 加快数据的检索速度。 好处4 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。 好处3 创建唯一性索引，保证数据库表中每一行数据的唯一性。 任务6.5 5 缺点一 创建索引和维护索引要耗费时间。 缺点二 索引需要占用物理空间。 缺点三 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。 索引与事务的应用 6.5.1 索引的创建与使用 任务6.5 6 索引与事务的应用 6.5.1 索引的创建与使用 SQL SERVER2022中主要的索引类型如下表所示： 任务6.5 索引类型 描述 聚集 聚集索引根据聚集索引键按顺序对表或视图的数据行进行排序和存储。 非聚集 非聚集索引可以在具有聚集索引的表或视图上定义，也可以在堆上定义。非聚集索引中的每个索引行都包含非聚集键值和行定位器。该定位器指向聚集索引或堆中具有键值的数据行。索引中的行按索引键值的顺序存储，但除非在表上创建聚集索引，否则不能保证数据行按任何特定顺序存储。 唯一 唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。 唯一性可以是聚集索引和非聚集索引的属性。 7 索引与事务的应用 6.5.1 索引的创建与使用 1．使用SSMS管理器窗口创建索引 在创建索引时，首先需要确定表的索引列。如果表没有主键约束，则可以创建聚集索引；如果表有主键约束，则系统禁止创建聚集索引，只能创建非聚集索引，因为系统把主键索引当成聚集索引，而聚集索引只能有一个。 例6-34创建非聚集索引Index1_BookInfo_BookName，其功能是将bookInfo表中的记录按照图书名称进行升序排序。 第1步，确定表和索引列。 在SSMS管理器窗口中依次展开“数据库”→“libsys”→“表”→“dbo.bookInfo”节点，右击“索引”节点，在弹出的快捷菜单中选择“新建索引”→“非聚集索引”命令，在弹出的“新建索引”窗口的“索引名称”文本框中输入索引名称，单击“添加”按钮，在弹出的窗口中选择“BookName”列，然后单击“确定”按钮，返回“新建索引”窗口，最终结果如图所示： 任务6.5 8 索引与事务的应用 6.5.1 索引的创建与使用 第2步，查看索引。 右击索引名Index1_BookInfo_BookName，在弹出的快捷菜单中选择“属性”命令，在弹出的“索引属性”窗口中可以查看并修改索引的有关参数。 任务6.5 9 索引与事务的应用 6.5.1 索引的创建与使用 索引一旦创建，在执行查询语句时由数据库管理系统自动启用。当再次执行与索引列相关的查询语句时，速度将会提高很多。例如，第二次执行以下查询语句时的速度会比第一次执行时快很多： SELECT BookName FROM bookInfo WHERE Publisher = '清华大学出版社' 在索引的快捷菜单中，除了“重命名”和“删除”等命令以外，还有一个“禁用”命令，通过该命令，可以禁用索引，但并不会删除索引，用同样的方法可以启用已经禁用的索引。 任务6.5 10 索引与事务的应用 6.5.1 索引的创建与使用 2．使用SQL命令创建索引 创建索引的命令格式如下： CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] ) <object> ::= {database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name } 格式说明： （1）UNIQUE：唯一索引。唯一索引不允许两行具有相同的索引键值，视图的聚集索引必须唯一。 任务6.5 11 索引与事务的应用 6.5.1 索引的创建与使用 （2）CLUSTERED：聚集索引。一个表或视图只允许同时有一个聚集索引，具有唯一聚集索引的视图称为索引视图。在创建索引时，键值的逻辑顺序决定了表中对应行的物理顺序。在创建任何非聚集索引之前，先创建聚集索引。在创建聚集索引时，表的现有非聚集索引将重新生成。 （3）NONCLUSTERED：非聚集索引。创建一个指定表的逻辑排序的索引。对于非聚集索引，数据行的物理排序独立于索引排序。对于索引视图，只能为已定义唯一聚集索引的视图创建非聚集索引。如果未另行指定，则默认索引类型为非聚集。 （4）index_name：索引的名称。索引的名称在表或视图中必须唯一，但在数据库中不必唯一，索引的名称必须遵循标识符的规则。 （5）<object>::=：要建立索引的完全限定对象或非完全限定对象。 （6）database_name：数据库的名称。 （7）schema_name：表或视图所属架构的名称。 任务6.5 12 索引与事务的应用 6.5.1 索引的创建与使用 （8）table_or_view_name：要建立索引的表或视图的名称。 必须使用SCHEMABINDING定义视图，才能为视图创建索引。必须先为视图创建唯一聚集索引，然后才能为该视图创建非聚集索引。 （9）column：索引所基于的一列或多列。指定两个或多个列名，可以为指定列的组合值创建组合索引。在table_or_view_name后的括号中，按照排序优先级列出组合索引中要包括的列。一个组合索引键中最多可组合32列，组合索引键中的所有列必须在同一个表或视图中。不能将ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml或image等大型对象数据类型的列指定为索引的键列。另外，即使CREATE INDEX语句中并未引用ntext、text或image数据类型的列，视图定义中也不能包含这些列。 （10）[ ASC | DESC ]：确定特定索引列的升序或降序排序方向，默认为ASC。 任务6.5 13 索引与事务的应用 6.5.1 索引的创建与使用 例6-35为bookInfo表创建非聚集索引Index2_BookInfo_Publisher，其功能是按照出版单位对记录进行降序排序。 CREATE NONCLUSTERED INDEX Index2_BookInfo_Publisher ON bookInfo(Publisher) 如果要创建唯一性的非聚集索引，则命令应为以下形式： CREATE UNIQUE NONCLUSTERED INDEX Index3_BookInfo_Publisher ON bookInfo(Publisher) 但在执行上述命令时，系统会提示“因为发现对象名称'dbo.bookInfo'和索引名称'Index3_BookInfo_Publisher'有重复的键，所以CREATE UNIQUE INDEX语句终止。重复的键值为(电子工业出版社)，语句已终止。”。 说明当Publisher列中有重复的值时，不能建立唯一性索引。同理，以下命令也不能执行： 任务6.5 14 索引与事务的应用 6.5.1 索引的创建与使用 CREATE CLUSTERED INDEX Index4_BookInfo_BookID ON bookInfo(BookID) 系统会提示“无法对表'bookInfo'创建多个聚集索引。请在创建新聚集索引前删除现有的聚集索引'PK__bookInfo__3DE0C22732D6EAB1'。”。 PK__bookInfo__3DE0C22732D6EAB1是主键约束对应的索引，名称由系统给定。 任务6.5 15 索引与事务的应用 6.5.1 索引的创建与使用 例6-36 为bookInfo表创建非聚集索引Index5_BookInfo_Publisher_Price，其功能是先按照出版单位对记录进行升序排序，出版单位相同的记录再按照销售价格进行降序排序。 CREATE NONCLUSTERED INDEX Index5_BookInfo_Publisher_Price ON bookInfo(Publisher ASC,Price DESC) 任务6.5 16 索引与事务的应用 6.5.1 索引的创建与使用 3、查看表中存在哪些索引 要查看表中的索引，可以用系统存储过程sp_helpindex实现，格式如下： sp_helpindex 表名 sp_helpindex bookInfo 系统会显示bookInfo表中的所有索引，如图所示。 任务6.5 17 索引与事务的应用 6.5.1 索引的创建与使用 4、重命名索引 1）使用表设计窗口重命名索引 ①在SSMS左侧的“对象资源管理器”中展开“数据库”节点，然后展开包含要重命名索引的表的数据库。 ②展开“表”节点，右击包含要重命名索引的表的名称，在弹出的快捷菜单中选择“设计”命令，打开表设计窗口。 ③在“表设计器”菜单中选择“索引/键”命令，在弹出的“索引/键”对话框的“选定的主/唯一键或索引”列表框中，选择要重命名的索引。 ④单击“添加”按钮，将索引的名称修改为新名称。 ⑤单击“关闭”按钮，然后在“文件”菜单中选择“保存”命令。 任务6.5 18 索引与事务的应用 6.5.1 索引的创建与使用 2）使用“对象资源管理器”窗口重命名索引 ①在SSMS左侧的“对象资源管理器”中展开“数据库”节点，然后展开包含要重命名索引的表的数据库。 ②展开“表”节点，然后展开包含要重命名的索引的表。 ③展开“索引”节点，右击要重命名的索引，在弹出的快捷菜单中选择“重命名”命令。 ④将索引的名称修改为新名称，然后按Enter键。 任务6.5 19 索引与事务的应用 6.5.1 索引的创建与使用 3）使用SQL命令重命名索引 使用SQL命令重命名索引的格式如下： sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' 格式说明： ①[@objname =] 'object_name'：需要重命名的索引名，object_name必须采用table.index或schema.table.index格式。 ②[ @newname = ] 'new_name'：指定索引的新名称，并且该名称必须遵循标识符的规则。 任务6.5 20 索引与事务的应用 6.5.1 索引的创建与使用 例6-37 将索引Index2_BookInfo_Publisher的名称修改为Index2_BookInfo_Pub。 sp_rename 'bookInfo.Index2_BookInfo_Publisher','Index2_BookInfo_Pub' 系统会提示“注意：更改对象名的任一部分都可能破坏脚本和存储过程。”。 在修改索引名的命令中，必须在索引名的前面加上表名，并且引号不可省略。 任务6.5 21 索引与事务的应用 6.5.1 索引的创建与使用 5、删除索引 使用DROP命令删除索引，格式如下： DROP INDEX [ IF EXISTS ] 表名.索引名[,…] 例6-38 删除bookInfo表中的索引Index2_BookInfo_Pub。 DROP INDEX bookInfo.Index2_BookInfo_Pub 说明：可以用DROP命令同时删除多个索引，多个索引之间用逗号隔开。DROP INDEX语句不适用于通过PRIMARY KEY约束或UNIQUE约束创建的索引。 任务6.5 22 索引与事务的应用 6.5.2 处理事务 事务是一个单一的工作单元。如果事务成功，则事务期间所做的所有数据修改都将被提交，并成为数据库的永久组成部分。如果事务遇到错误，必须取消或回滚，则所有数据修改都将被擦除。也就是说，操作作为一个整体成功或失败。 SQL Server 2022以下列事务模式运行： （1）自动提交事务：每条单独的语句都是一个事务。 （2）显式事务：每个事务均以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。 （3）隐式事务：在前一个事务完成时新事务隐式开始，但每个事务仍以COMMIT或ROLLBACK语句显式结束。 （4）批处理级事务：只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动由SQL Server数据库进行回滚。 任务6.5 23 索引与事务的应用 6.5.2 处理事务 1、事务的隔离级别 设置事务的隔离级别的命令格式是： SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } 任务6.5 24 索引与事务的应用 6.5.2 处理事务 格式说明： （1）READ UNCOMMITTED 读未提交。指定语句可以读取已由其他事务修改但尚未提交的数据，这种读取称为脏读。 （2）READ COMMITTED 读已提交。指定语句不能读取已由其他事务修改但尚未提交的数据，这样可以避免脏读。该选项是SQL Server的默认设置。 （3）REPEAT ABLEREAD 可重复读。指定语句不能读取已由其他事务修改但尚未提交的数据，并且指定其他任何事务都不能在当前事务结束之前修改由当前事务读取的数据。 任务6.5 25 索引与事务的应用 6.5.2 处理事务 （4）SNAPSHOT 快照。指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务一致性版本。事务只能识别在其开始之前提交的数据修改。 （5）SERIALIZABLE 可序列化。指定语句不能读取已由其他事务修改但尚未提交的数据。任何其他事务都不能在当前事务结束之前修改由当前事务读取的数据。在当前事务结束之前，其他事务不能使用当前事务中任何语句读取的键值插入新数据。 一次只能设置一个隔离级别选项，并且设置的选项将一直对那个连接始终有效，直到显式修改该选项。 任务6.5 26 索引与事务的应用 6.5.2 处理事务 2、开启事务 在SQL Server数据库中，通过BEGIN TRANSACTION语句来标记一个显式本地事务的起始点。显式事务以BEGIN TRANSACTION语句开始，并以COMMIT或ROLLBACK语句结束。 开启事务的命令格式如下： BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] [ ; ] 任务6.5 27 索引与事务的应用 6.5.2 处理事务 格式说明： （1）transaction_name：事务名称。transaction_name必须遵循标识符的规则，但其长度不能超过32个字符。仅在最外面的BEGIN...COMMIT或BEGIN...ROLLBACK嵌套语句对中使用事务名。transaction_name始终区分大小写，即使SQL Server实例不区分大小写也是如此。 （2）@tran_name_variable：包含有效事务名称的、用户定义的变量名。必须使用char、varchar、nchar或nvarchar数据类型声明该变量。如果传递给该变量的字符多于32个，则仅使用前面的32个字符，其余的字符将被截断。 （3）WITH MARK ['description']：指定在日志中标记事务，description是描述该标记的字符串。在将多于128个字符的description存储到msdb.dbo.logmarkhistory表中之前，先将其截断为128个字符。如果使用了WITH MARK，则必须指定事务名，WITH MARK允许将事务日志还原到命名标记。 任务6.5 28 索引与事务的应用 6.5.2 处理事务 3、提交事务 在SQL Server数据库中，通过COMMIT TRANSACTION语句来标记一个成功的隐式事务或显式事务的结束。 提交事务的命令格式如下： COMMIT [{TRAN | TRANSACTION} [transaction_name | @tran_name_variable]] [WITH (DELAYED_ DURABILITY = {OFF | ON})] [;] 任务6.5 29 索引与事务的应用 6.5.2 处理事务 3、提交事务 格式说明： （1）transaction_name：SQL Server数据库引擎忽略该参数。transaction_name指事务名称，与BEGIN TRANSACTION语句中的事务名称相同。transaction_name必须遵循标识符的规则，但其长度不能超过32个字符。transaction_name向程序员指明与COMMIT TRANSACTION关联的嵌套BEGIN TRANSACTION。 （2）@tran_name_variable：包含有效事务名称的、用户定义的变量名。 （3）DELAYED_DURABILITY：事务应该延迟提交，并持久化到数据库中。 任务6.5 30 索引与事务的应用 6.5.2 处理事务 例6-39 使用事务在borrowInfo表中添加一条借书记录，同时将bookInfo表中该图书的库存数量减1，这两条SQL语句必须同时成功或同时失败。 BEGIN TRANSACTION; INSERT INTO borrowInfo VALUES('T03010182','9787442768891','2022-10-10','2023-04-09', '2023-01-04'); UPDATE bookInfo SET AbleCount=AbleCount-1 WHERE BookID='9787442768891'; COMMIT TRANSACTION; 执行结果显示两行受影响，借阅记录添加成功，同时图书编号为“9787442768891”的图书的库存数量减1。 任务6.5 31 索引与事务的应用 6.5.2 处理事务 4、回滚事务 在SQL Server数据库中，使用ROLLBACK TRANSACTION语句可以将显式事务或隐式事务回滚到事务的开头，或者回滚到事务内部的保存点。使用ROLLBACK TRANSACTION语句可以擦除从事务开始或从保存点开始所做的所有数据修改。该语句还释放了事务所持有的资源。 回滚事务的命令格式如下： ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] [ ; ] 任务6.5 32 索引与事务的应用 6.5.2 处理事务 格式说明： （1）transaction_name：事务名称，与BEGIN TRANSACTION语句中的事务名称相同。transaction_name必须遵循标识符的规则，但其长度不能超过32个字符。在嵌套事务时，transaction_name必须是最外面的BEGIN TRANSACTION语句中的名称。transaction_name始终区分大小写，即使SQL Server实例不区分大小写也是如此。 （2）@tran_name_variable：包含有效事务名称的、用户定义的变量名。 （3）savepoint_name：保存点的名称，与SAVE TRANSACTION语句中的保存点名称相同。savepoint_name必须遵守标识符的规则。当条件回滚只影响事务的一部分时，可以使用savepoint_name。 （4）@savepoint_variable：用户定义的、包含有效保存点名称的变量的名称。必须使用char、varchar、nchar或nvarchar数据类型声明该变量。 任务6.5 33 索引与事务的应用 6.5.2 处理事务 例6-40 在事务中，先向borrowInfo表中添加两条读者记录，然后使用事务回滚，最后查询borrowInfo表中的所有记录。 USE libsys; GO DECLARE @TransactionName VARCHAR(20) = 'Transaction1'; BEGIN TRAN @TransactionName INSERT INTO readerInfo VALUES('T01010060','李四','男',32,'软件学院','教师','2015-12-20','13973198765','lisi@163.com',NULL); INSERT INTO readerInfo VALUES('T01010061','王五','男',33,'软件学院','教师','2016-11-20','13873176543','wangwu@163.com',NULL); ROLLBACK TRAN @TransactionName; SELECT * FROM readerInfo; ---查询结果显示，borrowInfo表中未插入任何记录。 任务6.5 34 索引与事务的应用 6.5.2 处理事务 5、保存事务 在事务内设置保存点用于保存事务。 保存事务的命令格式如下： SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable } [ ; ] 格式说明： （1）savepoint_name：保存点的名称。保存点的名称必须遵循标识符的规则，但其长度不能超过32个字符。savepoint_name始终区分大小写，即使SQL Server实例不区分大小写也是如此。 （2）@savepoint_variable：包含有效保存点名称的、用户定义的变量名。必须使用char、varchar、nchar或nvarchar数据类型声明该变量。如果字符的个数超过32个，则也可以传递给该变量，但只使用前32个字符。 任务6.5 35 索引与事务的应用 6.5.2 处理事务 5、保存事务 用户可以在事务内设置保存点或标记。保存点可以定义在按照条件取消某个事务的一部分后，该事务可以返回的一个位置。如果将事务回滚到保存点，则根据需要必须完成其他剩余的Transact-SQL语句和COMMIT TRANSACTION语句，或者必须通过将事务回滚到起始点来完全取消事务。如果要取消整个事务，则可以使用“ROLLBACK TRANSACTION transaction_name”格式的命令，这将撤销事务的所有语句和过程。 任务6.5 36 菊次郎的夏天 菊次郎的夏天 久石譲 $$