2019-02-13

喵星之旅-成长的雏鹰-MySQL数据库应用技术-4-事务、索引、数据恢复和备份

事务处理方法

事务就是将一组SQL语句放在同一批次内去执行
如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
MySQL事务处理只支持InnoDB和BDB数据表类型

MySQL的ACID原则

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

事务实现方法

使用SET语句来改变自动提交模式
MySQL中默认是自动提交

使用事务时应先关闭自动提交

1 2	SET AUTOCOMMIT = 0; # 关闭自动提交模式 SET AUTOCOMMIT = 1; # 开启自动提交模式

START TRANSACTION
    开始一个事务,标记事务的起始点
COMMIT
    提交一个事务给数据库
ROLLBACK
    将事务回滚，数据回到本次事务的初始状态
SET AUTOCOMMIT = 1;
    开启MySQL数据库的自动提交

事务处理步骤

Alt text

样例

/*创建 shop  数据库*/
CREATE DATABASE IF NOT EXISTS `shop`;

/* 创建 账户表 */

CREATE TABLE IF NOT EXISTS  `account` (
  `id` int(11) not null auto_increment,
  `name` varchar(32) not null,
  `cash` decimal(9,2) not null,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 

INSERT INTO  `account`   (`name`,`cash`) VALUES ('A',2000.00);
INSERT INTO  `account`   (`name`,`cash`) VALUES ('B',10000.00);



/* 事务处理*/

  select * from account;
  set autocommit= 0; 
  START TRANSACTION; 
  update account set cash = cash - 500  where name = 'A';
  select * from account; 
  ROLLBACK;  
  set autocommit = 1; 
  select * from account;

数据库索引

作用

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
全文检索字段进行搜索优化

分类

主键索引（PRIMARY KEY）
唯一索引（UNIQUE）
常规索引（INDEX）
全文索引（FULLTEXT）

主键索引

某一个属性或属性的组合能唯一标识一条记录。

特点：

最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

CREATE TABLE `Grade` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY, #省略代码……
#主键索引也可在字段字义之后，如
# PRIMARY KEY(`GradeID`)
)

唯一索引（UNIQUE）

避免同一个表中某数据列中的值重复。

与主键索引的区别：主键索引只能有一个、唯一索引可有多个。

CREATE TABLE `Grade` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY, `GradeName` VARCHAR(32) NOT NULL UNIQUE
#或 UNIQUE KEY `GradeID` (`GradeID`)
)

常规索引（INDEX）

作用：快速定位特定数据。

注意：
1.index和key关键字都可设置常规索引
2.应加在查找条件的字段
3.不宜添加太多常规索引，影响数据的插入、删除和修改操作

# 创建表时添加
CREATE TABLE `result` (
//省略一些代码
INDEX/KEY `ind` (`studentNo`, `subjectNo`)
)

# 创建后追加
ALTER TABLE `result` ADD INDEX `ind`
(`studentNo`, `subjectNo`);

全文索引（FULLTEXT）

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

CREATE TABLE `student` (
#省略一些SQL语句
FULLTEXT (`StudentName`)
)ENGINE=MYISAM;

ALTER TABLE employee ADD FULLTEXT (`first_name`);

管理索引

1.创建表时添加
2.建表后追加

1	ALERT TABLE 表名 ADD 索引类型（数据列名）

3.删除索引

1
2
3

DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY

4.查看索引

1	SHOW INDEX(或KEYS) FROM 表名

索引准则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段

MySQL数据库备份和恢复的几种方法

mysqldump备份工具
数据库管理工具，如SQLyog
直接拷贝数据库文件和相关配置文件

mysqldump命令导出数据

1 2	mysqldump -h 主机名 –u 用户名 –p [options] 数据库名 [ table1 table2 table3 ] > path/filename.sql

1 2	#备份myschool数据库的student及result表 mysqldump -uroot -pbdqn myschool student result>h:/myschool.sql

掌握source命令导入数据

1	SOURCE /path/db_name.sql;

都要先创建或选择数据库
/path/是一个绝对路径，并且必须是mysql 运行用户有权限读取的文件

1
2
3

mysql -uroot -pmxt;
USE test;
source h:/myschool.sql;