Day5: MySQL的事务
一、事务(Transaction)
- 事务:将一组SQL放在一个批次中去执行。
- 事务的原则:
ACID原则:原子性(atomicity),一致性(consistency),隔离性(isolation),持久性(durability)
参考连接:ACID理解的博客 - 事务的隔离级别:
(1)脏读: 指一个事务读取了另外一个事务未提交的数据。
(2) 不可重复读: 在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)。
(3) 虚读(幻读): 是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致(一般是行影响,多了一行)。
事务隔离的级别和应用场景的参考博客:事务隔离级别的博客
(MySQL是默认开启事务自动提交的,需要手动关闭)
1.1事务开启和关闭流程
- step1:手动关闭自动提交;
-- MySQL是默认开启事务自动提交的,需要手动关闭
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认的)
- step2:开启事务,之后的语句都在一个事务内;
-- 事务开启
SET TRANSACTION -- 标记一个事务的开始,从这个之后的sql 都在同一个事务内
- step3:提交事务或回滚事务;
-- 提交: 持久化(成功)
COMMIT
-- 回滚: 回到原来的样子(失败)
ROLLBACK
- step4:结束,手动开启自动提交;
-- 事务结束
SET autocommit = 1 -- 开启自动提交
1.2 用事务模拟银行转账的操作
-- 转账数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `account` (`name`,`money`) VALUES ('A',2000.00),('B',10000.00);
-- 模拟转帐
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务
UPDATE ACCOUNT SET money=money-500 WHERE `name`='A'; -- A转账500
UPDATE ACCOUNT SET money=money+500 WHERE `name`='B'; -- B收到转账500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 开启自动提交,标志事务的结束
1.3 保存点的设置(了解)
可以参考游戏中的存档点进行理解:设置,加载和撤销。
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 撤销保存点
二、索引
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构,提取句子主干,就可以得到索引的本质: 索引就是数据结构
2.1 索引的分类
- 主键索引(PRIMARY KEY)
– 唯一的标识,主键不可重复,只能有一个列作为主键。 - 唯一索引(UNIQUE KEY)
– 避免重复的字段出现,可以有多个唯一索引,多个字段都可以标识为唯一索引。 - 常规索引(KEY / INDEX)
– 默认的,KEY / INDEX 来设置。 - 全文索引(FullText)
– 在特定的数据库引擎下才有,快速定义数据。
2.2 索引的创建
- 可以在创建表的时候直接指定:
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`), -- 指定主键索引:PRIMARY KEY (列名)
UNIQUE KEY `identitycard`(`identitycard`), -- 指定唯一索引:UNIQUE KEY 索引名(列名)
KEY `email` (`email`) -- 指定普通索引:KEY 索引名(列名)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-
可以通过create语句来进行创建索引:
CREATE INDEX indexName ON table_name (column_name)
-
可以通过ALTER命令来添加和删除索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。ALTER TABLE tbl_name ADD INDEX index_name (column_list)
添加普通索引,索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
该语句指定了索引为 FULLTEXT ,用于全文索引。
添加索引的实例,也可以在ALTER语句中使用DROP子句来删除索引:
-- 增加一个全文索引 索引名 (列名)
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`);
-- 删除索引
ALTER TABLE `student` DROP INDEX `studentname`;
2.3 索引的测试
创建一百万条数据来测试索引的:通过查询某一条数据可以看出,加上索引后的效果要更加快速。
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别(0:男;1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
SET GLOBAL log_bin_trust_function_creators=1; -- 开启创建函数功能
-- 插入100万条数据
DELIMITER $$ -- 写函数之前必写的
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- 插入语句
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES (CONCAT('用户',i),'2473743@qq.com','15811372982',FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END; $$
SELECT mock_data()$$ -- 执行此函数 生成一百万条数据
-- 直接查询
SELECT * FROM `app_user` WHERE `name`='用户99999';
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户99999';
-- 创建索引
CREATE INDEX id_app_user_name ON `app_user`(`name`);
-- 通过索引查询
SELECT * FROM `app_user` WHERE `name`='用户99999';
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户99999';
2.4 索引的原则
- 索引不是越多越好,小数据量的表不需要索引;
- 不要对经常变动的数据加索引;
- 索引一般加在常用来查询的字段上。
三、权限管理和备份
3.1 SQL命令操作
用户表:mysql库下的user表,本质就是对这张表进行增删改查。
常见的操作如下:
-- 创建用户: CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER hurunqiao IDENTIFIED BY '123456'
-- 用户重命名: RENAME USER 原名 TO 新名字;
RENAME USER hurunqiao TO hurunqiao2;
-- 用户授权:授予所有库.表全部权限
GRANT ALL PRIVILEGES ON *.* TO hurunqiao2;
-- 查看权限
SHOW GRANTS FOR hurunqiao2;
SHOW GRANTS FOR root@localhost; -- 查看管理员权限
-- 撤销权限: 撤销全部权限
REVOKE ALL PRIVILEGES ON *.* FROM hurunqiao2;
-- 删除用户
DROP USER hurunqiao2;
3.2 数据库备份
- 数据库备份的作用:
- 保证重要的数据不丢失
- 数据转移时需要
- MySQL数据库备份的方式:
- 直接拷贝物理文件
- 在SQLyog这种可视化工具中导出
- 使用命令行导出
mysqldump
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表明 >位置
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
四、数据库设计规范
4.1 数据库三大范式
参考博客:三大范式通俗理解
1. 第一范式(1NF)
- 原子性:保证每一列都不可再分。
2. 第二范式(2NF)
前提:满足第一范式。
- 非码属性必须完全依赖于候选码(消除非主属性对主码的部分函数依赖)。
(非主键列完全依赖于主键,而不能是主键的一部分)
3. 第三范式(3NF)
前提:满足第一范式和第二范式。
- 属性不依赖于其他非主属性(消除依赖传递)