《MySQL数据库应用与管理项目化教程》课件-项目9-1索引和事务

项目九索引和事务任务目标任务一创建和查看索引任务二处理事务教学目标能力目标◎能应用索引有效提高数据检索性能；◎能使用事务机制实现数据的一致性。知识目标◎了解索引作用，掌握建立和查看管理索引的语法应用；◎了解事务特性，掌握事务处理操作语句应用。学习重点◎索引的建立与查看；◎事务的提交与回滚，保证数据的一致性。学习难点◎并发控制的锁使用。任务一创建和查看索引创建和查看索引

主要内容:索引概念和类型；查看索引创建索引删除索引重点难点:索引的创建索引概念和类型索引概念索引是为了加速检索而创建的一种存储结构；索引是针对一个表而建立的，它是由除存放表的数据页面以外的索引页面组成，每个索引页面中的行都包含逻辑指针，以指针指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针，通过该指针可以直接检索到数据，从而加速物理数据的检索。索引优缺点优点：当经常查询索引列中的数据时，需要在表上创建索引，可以提高检索效率；缺点：索引将占用磁盘空间，并且降低添加、删除和更新行的速度，所以索引也不是建立越多越好。索引概念和类型（1）普通索引这是MySQL中最基本的索引类型，允许索引列由重复值，可以用在查询条件字段或排序字段上建立索引。（2）唯一性索引唯一索引要求组成该索引的字段或字段组合不能存在重复值，要求具有唯一性，允许空值。通常用在不允许重复的字段上建立唯一索引，以避免重复值的录入。（3）主键索引主键索引是在创建主键时自动建立的，是唯一索引的特殊类型。主键索引字段不允许空值。索引概念和类型（4）复合索引使用一个以上的字段组合建立索引，这种索引称为复合索引。（5）全文索引全文索引的索引类型为FULLTEXT,可以用在CHAR、VARCHAR或TEXT类型的列上创建。主要用于文章等大量文本文字中检索字符串信息。（6）空间索引空间索引时对空间数据类型的字段上建立索引，如POINT等，索引字段不允许空。空间索引只能在MyISAM存储引擎的表中创建。查看索引语法：SHOWINDEXFROMtbl_name说明：查看表全部记录信息例9-1：查看db_shop数据库中goods表中的索引文件mysql>USEdb_shop;mysql>SHOWINDEXFROMgoods\G创建索引语法：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtbl_name({col_name[(length)]|(expr)}[ASC|DESC])说明：·UNIQUE|FULLTEXT|SPATIAL：分别表示唯一索引、全文索引和空间索引，省略默认是普通索引；·index_name是定义的索引名；·tbl_name是建立索引的表；·col_name是要建立索引的字段。创建普通索引语法：CREATEINDEX索引名ONtbl_name(索引字段列表)例9-2在商品表的数量字段建立普通索引。mysql>USEdb_shop;mysql>

CREATEINDEXix_amountONgoods(amount);mysql>SHOWINDEXFROMgoods\G创建唯一索引语法：CREATEUNIQUEINDEX索引名ONtbl_name(索引字段列表)说明：创建唯一索引可以在建立表时通过添加UNIQUE约束时建立，也使用CRAETEUNIQUEINDEX语句建立。第一种方法前面创建表时已经做过。例9-3给staffer_rec_bak表的用户名建立一个唯一索引。mysql>USEdb_shop;mysql>CREATEUNIQUEINDEXstaffer_rec_ixONstaffer_rec_bak(username);创建复合索引复合索引：就是指定多个字段作为索引列的索引。例9-4建立员工表建立性别和出生日期（降序）的索引mysql>USEdb_shop;mysql>CREATEINDEXix_stafferONstaffer(sex,birthdayDESC);考考你按下列操作实施完成db_shopping数据库索引的建立和查看。1.复制商品表和记录，复制的表名为goods_rec_bak;执行语句如下：2.为goods_rec_bak表建立商品类型的普通索引ix_goods_type；执行语句如下：3.为goods_rec_baka表建立商品名称的唯一索引iq_goods_name；4.为goods_rec_baka表建立供应商编号（升序）和商品数量（降序）的索引ix_sup_id_amount；5.查看goods_rec_baka表建立的索引文件信息，观察iq_goods_name索引参数；任务二处理事务处理事务

主要内容:基本概念；事务特性和类型事务处理操作 重点难点:事务处理操作基本概念事务（transaction）是一个数据处理操作序列作为一个整体来执行的一种机制。这些操作是一个不可分割的逻辑工作单元，事务更新操作要么都执行，要么都不执行。通过事务的整体性可以保证数据的一致性。事务特性事务是作为并发控制的最小控制单元，具备以下ACID四个特性：原子性（Atomicity）：事务是一个完整的操作，具有原子特性，不可分割。事务的所有操作作为一个整体提交或回滚，要么全部完成，要么全部不完成。不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。一致性（Consistency）：事务完成后，数据库数据总是从一个一致的状态到另一个一致的状态，不允许中间状态的存在。事务特性隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改，但所有并发事务是彼此隔离的。一个事务看到的数据要么是其他事务修改前的状态，要么是其他事务修改完成的状态，这个事务不能看到其他事务正在修改的数据。持久性（Durability）：事务处理结束后，对数据的修改就是永久的，一旦事务被提交，事务就永久地保持在数据库中，不能被回滚。即便系统发生故障也不会丢失。事务类型MySQL事务类型：一种是系统提供的自动提交事务；另一种是用户定义的显式事务。自动提交事务在MySQL命令行的默认设置下，事务都是自动提交的，一条语句就构成了一个事务，即执行SQL语句完成后要么被提交，要么回滚。例如CREATETABLE、ALTERTABLE、SELECT、INSERT、UPDATE、DELETE、DROP、TRUNCATETABLE、GRANT、REVOKE等语句的执行都是自动提交事务。事务类型显式事务显式事务是用户显式定义事务启动和结束。MySQL默认是自动提交事务，每句SQL语句就是一个事务。显示事务可以使用2种方法开启：方法一：使用BEGIN或STARTTRANSACTION开启一个事务，同时用事务提交或事务回滚来结束事务。方法二：执行命令：SETAUTOCOMMIT=0，来禁止当前会话的自动提交，用：SETAUTOCOMMIT=1，来恢复当前会话自动提交；事务类型查看事务提交状态值。SHOWVARIABLESLIKE'autocommit';例如：查看事务提交模式mysql>SHOWVARIABLESLIKE'autocommit';#开启事务显式模式mysql>SETAUTOCOMMIT=0;事务处理操作在MySQL中定义事务处理的语句主要有下面3个处理语句。1）语法：BEGIN;或STARTTRANSACTION;说明：开启事务。需要注意的是MySQL中是不允许事务嵌套的，开启一个新的事务后，前面的事务会自动提交。2）语法：COMMIT说明：提交事务，使得事务中对数据库进行的所有修改成为永久性的，同时结束当前会话事务，释放连接时占用的资源。事务处理操作3）语法：ROLLBACK说明：回滚事务。撤销正在进行的所有未提交的操作，数据状态回滚到事务开始前，同时结束当前会话事务，并释放事务占用的资源。事务处理操作例9-6向db_shop数据库的部门信息表Department中插入3行记录，一条用STARTTRANSACTION;开启事务，添加后用COMMIT提交结束事务；第二条为自动提交事务；第三条用SETAUTOCOMMIT=0;关闭自动提交事务，启动显式事务提交模式，添加后使用ROLLBACK回滚结束事务。执行语句如下,观察数据变化：事务处理操作mysql>USEdb_shop;mysql>STARTTRANSACTION;#开启事务mysql>INSERTINTOdb_shop.departmentvalues(100,'技术部2','38490123','技术业务');mysql>COMMIT;#结束事务mysql>INSERTINTOdb_shop.departmentvalues(101,'技术部3','38490123','技术业务');#在默认的自动提交事务模式下mysql>SETAUTOCOMMIT=0;#启动显式事务，之后为事务的开始mysql>INSERTINTOdb_shop.departmentvalues(101,'促销部','38490125','促销业务');mysql>ROLLBACK;#结束事务mysql>SELECT*FROMdb_shop.department;mysql>SETAUTOCOMMIT=1;#恢复事务自动提交模式说明：可以看到COMMIT的记录永久保存，不在显式事务模式的记录会自动提交，不能回滚；在显示提交模式的事务可以回滚，回滚后的记录没有添加入数据表。事务处理操作保存点建立事务保存点（SAVEPOINT），事务可以回滚到事务保存点而不影响事务保存点创建前的操作,不需要放弃整个事务。保存点有创建、回滚、删除三个相关操作。事务中创建一个保存点。语法格式：SAVEPOINTsavepoint_name说明：savepoint_name为事务保存点名称。一个事务中可以有多个事务保存点；事务处理操作事务回滚到某个事务保存点。语法格式：ROLLBACKTOsavepoint_name说明：回滚到保存点并不结束事务，仍然需要COMMIT;或者ROLLBACK;语句来结束事务。删除一个事务的保存点语法格式：RELEASESAVEPOINTsavepoint_name事务处理操作例9-7创建一个事务，向do_shop数据库的部门信息表Department中插入一行记录，设置保存点，然后在事务中修改该记录，回滚事务到保存点（记录回到修改前的状态），然后COMMIT。执行以下语句，观察数据变化：事务处理操作mysql>USEdb_shop;mysql>STARTTRANSACTION;mysql>INSERTINTOdepartmentvalues(101,'技术部3','S0000002','技术总监');mysql>SAVEPOINTpoint1;mysql>UPDATEdepartmentSETdept_phone='S0000001'WHEREid=101;mysql>SELECT*FROMdb_shop.department;mysql>ROLLBACKTOSAVEPOINTpoint1;mysql>SELECT*FROMdb_shop.department;mysql>COMMIT;mysql>SELECT*FROMdb_shop.department;考考你1.为避免前面创建的触发器影响，复制一份商品表名为goods_rec_bak。2.为避免前面创建的触发器影响，复制一份详细订单表名为item_rec_bak。3.开启事务，在item_rec_bak表添加一条记录，并在goods_rec_bak中修改相应商品数量。4.提交事务，并查看数据是否变化。5.开启事务，在item_rec_bak中插入2条记录，并回滚，查看数据是否变化。任务实施按下列操作实施完成db_shopping数据库事务应用。1.为避免前面创建的触发器影响，复制一份商品表名为goods_rec_bak，执行语句如下：mysql>CREATETABLEIFNOTEXISTSgoods_rec_bakASSELECT*FROMgoods;2.为避免前面创建的触发器影响，复制一份详细订单表名为item_rec_bak，执行语句如下：mysql>CREATETABLEIFNOTEXISTSitem_rec_bakASSELECT*FROMitem;3.开启事务，在item_rec_bak表添加一条记录，并在goods_rec_bak中修改相应商品数量，执行语句