5.MySQL事务、索引、数据恢复和备份.pptx

《5.MySQL事务、索引、数据恢复和备份.pptx》由会员分享，可在线阅读，更多相关《5.MySQL事务、索引、数据恢复和备份.pptx（32页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、MySQL事务、索引、数据恢复和备份线上线下平台预习2/32本课目标学完本次课程后，你能够：掌握MySQL的事务处理方法理解MySQL的ACID原则创建数据库索引了解MySQL数据库备份和恢复的几种方法掌握mysqldump命令导出数据掌握source命令导入数据3/32MySQL的事务处理事务就是将一组SQL语句放在同一批次内去执行如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行注意MySQL事务处理只支持InnoDB和BDB数据表类型4/32事务的特性事务必须具备以下四个属性，简称ACID 属性原子性（Atomicity）一致性（Consistency）隔离性（Isolatio

2、n）持久性（Durability）5/61MySQL的事务实现方法SET AUTOCOMMIT使用SET语句来改变自动提交模式MySQL中默认是自动提交使用事务时应先关闭自动提交 SET AUTOCOMMIT =0;#关闭自动提交模式关闭自动提交模式SET AUTOCOMMIT =1;#开启自动提交模式开启自动提交模式示例注意6/32MySQL的事务实现方法START TRANSACTION开始一个事务,标记事务的起始点COMMIT提交一个事务给数据库 ROLLBACK将事务回滚，数据回到本次事务的初始状态 SET AUTOCOMMIT=1;开启MySQL数据库的自动提交7/32MySQL事务

3、处理步骤8/32模拟网上支付2-1模拟网上支付顾客A在线购买一款商品，价格为500.00元，采用网上银行转账的方式支付假如顾客A银行卡的余额为2000.00元，且向卖家B支付购买商品费用500.00元，起始卖家B的账号金额10000.00元创建数据库shop和创建表account并插入2条数据+-+-+-+-+-+-+-+-+-+-+-+-+|Field|Type|Null|Key|Default|Extra|Field|Type|Null|Key|Default|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|id|int(11)|NO|PRI|NULL|auto_incre

4、ment|id|int(11)|NO|PRI|NULL|auto_increment|name|varchar(32)|NO|NULL|name|varchar(32)|NO|NULL|cash|decimal(9,2)|NO|NULL|cash|decimal(9,2)|NO|NULL|+-+-+-+-+-+-+-+-+-+-+-+-+INSERT INTO account (name,cash)VALUES(A,2000.00);INSERT INTO account (name,cash)VALUES(A,2000.00);INSERT INTO account (name,cash)V

5、ALUES(B,10000.00);INSERT INTO account (name,cash)VALUES(B,10000.00);9/32模拟网上支付2-2设置场景A账户成功减少500元，B账户应该增加500元，但一些错误导致未增加成功，这时则需返回A账户的500元，达到账户总额的平衡要求：使用事务模拟以上过程演示示例1：网上支付10/32数据库索引作用提高查询速度确保数据的唯一性可以加速表和表之间的连接，实现表与表之间的参照完整性使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间全文检索字段进行搜索优化分类主键索引（PRIMARY KEY）唯一索引（UNIQUE）常规索引（

6、INDEX）全文索引（FULLTEXT）11/32主键索引某一个属性或属性的组合能唯一标识一条记录如：学生表（学号，姓名，班级，性别等），学号就是唯一标识的，可作为主键特点最常见的索引类型确保数据记录的唯一性确定特定数据记录在数据库中的位置 CREATE TABLE Grade(GradeID INT(11)AUTO_INCREMENT PRIMARY KEY,#省略代码省略代码#主键索引也可在字段字义之后，如主键索引也可在字段字义之后，如#PRIMARY KEY(GradeID)示例12/32唯一索引（UNIQUE）作用避免同一个表中某数据列中的值重复与主键索引的区别主键索引只能有一个唯一索

7、引可有多个 CREATE TABLE Grade(GradeID INT(11)AUTO_INCREMENT PRIMARY KEY,GradeName VARCHAR(32)NOT NULL UNIQUE#或 UNIQUE KEY GradeID (GradeID)示例13/32常规索引（INDEX）作用：快速定位特定数据注意index和key关键字都可设置常规索引应加在查找条件的字段不宜添加太多常规索引，影响数据的插入、删除和修改操作CREATE TABLE result(/省略一些代码省略一些代码 INDEX/KEY ind (studentNo,subjectNo)ALTER TABL

8、E result ADD INDEX ind (studentNo,subjectNo);创建表时添加创建表时添加创建后追加创建后追加示例14/32作用：快速定位特定数据注意只能用于MyISAM类型的数据表只能用于 CHAR、VARCHAR、TEXT数据列类型适合大型数据集 CREATE TABLE student(#省略一些省略一些SQL语句语句 FULLTEXT(StudentName)ENGINE=MYISAM;ALTER TABLE employee ADD FULLTEXT(first_name);全文索引（FULLTEXT）示例15/32创建表时添加创建表时添加创建后追加创建后追加

9、管理索引创建索引创建表时添加建表后追加删除索引查看索引ALERT TABLE 表名表名 ADD 索引类型（数据列名）索引类型（数据列名）DROP INDEX 索引名索引名 ON 表名表名ALTER TABLE 表名表名 DROP INDEX 索引名索引名ALTER TABLE 表名表名 DROP PRIMARY KEYSHOW INDEX(或或KEYS)FROM 表名表名语法语法语法16/32索引准则索引不是越多越好不要对经常变动的数据加索引小数据量的表建议不要加索引索引一般应加在查找条件的字段17/32给数据库表添加索引给数据库表student添加索引学号StudentNo，添加主键索引身份

10、证IdentityCard，添加唯一索引邮箱Email，添加常规索引姓名StudentName，添加全文索引(MyISAM类型数据表)演示示例2：添加索引18/32分析SQL语句的执行性能提示：使用 G 结尾能竖排显示 查询中不包含子查询连接类型,从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL包含MySQL解决查询的详细信息演示示例3：EXPLAIN语句使用EXPLAIN 表名 （DESC 表名）EXPLAIN SELECT语句语法19/32添加正确的索引在WHERE、ORDER BY 子句中经常使用的字段字段的值是多个（例如性别字段则不适合）字段

11、内容不是经常变化的经常变化的字段，添加索引反而降低性能不宜过多添加索引每添加一条索引都会占用磁盘空间20/32MySQL的备份数据库备份必要性保证重要数据不丢失数据转移MySQL数据库备份方法mysqldump备份工具数据库管理工具，如SQLyog直接拷贝数据库文件和相关配置文件21/32mysqldump数据库备份作用转储数据库搜集数据库进行备份将数据转移到另一个SQL服务器(不一定是MySQL服务器)#备份备份myschool数据库如：数据库如：mysqldump-u root-p myschool d:/myschool.sql EnterPassword:*mysqldump -h 主

12、机名主机名 u 用户名用户名 p options 数据库名数据库名 table1 table2 table3 path/filename.sql预存文件目录，须有该预存文件目录，须有该目录读写权限目录读写权限语法示例22/32mysqldump常用选项2-1符号名称描述-add-drop-table 导出sql脚本会加上 DROP TABLE IF EXISTS 语句默认是打开的，可以用-skip-add-drop-table 来取消-add-locks该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句好处：防止记录被再次导入时，其他用户对表进行的操作

13、，默认是打开的-t 或-no-create-info忽略不写重新创建每个转储表的CREATE TABLE语句23/32提示可通过mysqldump-help 查看该命名的选项mysqldump常用选项2-2符号名称描述-c或-complete-insert在每个INERT语句的列上加上字段名在数据库导入另一个数据库时非常有用-d 或-no-data不写表的任何行信息。对于只想转储表的结构很有用-where where-condition,-w where-condition只转储给定的WHERE条件选择的记录-opt该选项是速记；等同于指定-add-drop-tables-add-lockin

14、g-create-option-disable-keys-extended-insert -lock-tables-quick -set-charset 24/32mysqldump数据库备份备份MySchool数据库的subject课程表，保存为subject.sql脚本文件要求在每个INERT语句的列上加上字段名25/32MySQL数据库的恢复方法一用 SOURCE 语法 /path/是一个绝对路径，并且必须是mysql 运行用户有权限读取的文件SOURCE 在MySQL命令行里执行方法二用 mysql 客户端SOURCE /path/db_name.sql;mysql u root p

15、dbname /path/db_name.sql;都要先创建或选择数据库都要先创建或选择数据库26/32演示示例4：数据库的备份和恢复利用SQL语句导出、导入数据输出的文件不能先存在，否则报错SELECT *INTO OUTFILE file_name FROM tbl_nameLOAD DATA INFILE file_name INTO TABLE tbl_nameFIELDS语法演示示例5：数据的导出和导入27/32语法导出数据导出数据导入数据导入数据练习1：数据库备份和恢复需求说明使用mysqldump命令备份myschool数据库要求在每个INSERT语句上加上字段名导入备份的mys

16、chool数据库要求使用SOURCE命令和mysql命令两种方式恢复数据28/32练习2：查询综合练习查询密码长度不足6位的学员编号、姓名、密码、年级名查找郑环环所有考试的成绩（科目名称、考试成绩）使用表连接方式查询所有U1年级学员学号、姓名及年级名使用子查询方式查询所有U1年级学员学号、姓名、年级ID使用表连接方式查询“Java”课程至少一次考试刚好等于60分的学生姓名使用子查询方式查询“Java”课程至少一次考试刚好等于60分的学生使用子查询方式+表连接方式混合查询“Java”课程至少一次考试刚好等于60分的学生29/32总结MySQL数据库事务的ACID原则是什么？如何关闭和开启MySQL的自动提交？MySQL的事务处理实现流程是什么？MySQL数据库有哪些索引？什么时候使用索引？MySQL备份的几种方法？如何实现mysqldump的导出以及SOURCE导入？30/32问题及作业集中问题&课后作业31/32扫我有更多精彩课程呦32/32