MySQL（四）

事务

菜鸟教程

事务的基本介绍

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

概念：如果一个包含多个步骤的业务操作，被食物管理，那么这些操作要么同时成功，要么同时失败

视频讲解

操作：

  1. 开启事务：start transaction;
  2. 回滚：rollback;
  3. 提交：commit;

例子：我们先创建一张表并添加记录，相关sql：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance)
VALUES ('zhangsan', 1000),
	('lisi', 1000);

SELECT *
FROM account;

UPDATE account
SET balance = 1000;

创建成功后account表数据如下：

事务：张三给李四转账500元

1
2
3
4
5
6
7
8
-- 1.开启事务
start transaction;
-- 2.张三账户-500
update account set balance = balance - 500 where name = 'zhangsan';
-- 3.李四账户+500
update account set balance = balance + 500 where name = 'lisi';
-- 4.发现执行没有问题，提交事务
commit;

执行完毕后结果为：

事务执行成功

如果过程中出现了错误，那么应该回滚事务，还原已经执行了的SQL语句：

1
2
3
4
5
6
7
8
9
-- 1.开启事务
start transaction;
-- 2.张三账户-500
update account set balance = balance - 500 where name = 'zhangsan';
-- 3.李四账户+500
错误的sql代码
update account set balance = balance + 500 where name = 'lisi';
-- 4.出现错误，回滚事务
rollback;

回滚后发现数据没有变化（哪怕是执行成功了的sql语句也没有改变张三的数据）

回滚事务，数据没有变化

事务的默认自动提交和手动提交

事务提交的两种方式：

  • 自动提交：
    • MySQL就是自动提交的
    • 一条DML（增删改表中数据）语句会自动提交一次事务
  • 手动提交：
    • Oracle数据库默认是手动提交事务
    • 需要先开启事务，再提交

修改事务的默认提交方式：

1
2
3
4
-- 查看事务的默认提交方式
select @@autocommit;	-- 1表示自动提交，0表示手动提交
-- 修改默认提交方式
set @@autocommit = 0;

MySQL就是自动提交的,@@autocommit = 1

事务的四大特征

  • 原子性：事务是不可分割的最小操作单位。要么同时成功，要么同时失败
  • 持久性：当事务提交或回滚后，数据库会持久化地保存数据
  • 隔离性：多个事务之间相互独立
  • 一致性：事务操作前后，数据总量不变

事务的隔离级别（了解）

视频讲解

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题：

  • 脏读：一个事务，读取到另一个事务中没有提交的数据
  • 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
  • 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：

  • read uncommitted：读未提交
    • 产生的问题：脏读、不可重复读、幻读
  • read committed：读已提交 （Oracle）
    • 产生的问题：不可重复读、幻读
  • repeatable read：可重复读 （MySQL默认）
    • 产生的问题：幻读
  • serializable：串行化
    • 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：

1
select @@tx_isolation;

数据库设置隔离级别：

1
set global transaction isolation level  级别字符串;

事务隔离级别演示

DCL

在之前的笔记中，我们已经提到了SQL分类。这里简单提一下：

  • DDL：操作数据库和表
  • DML：增删改表中数据
  • DQL：查询表中数据
  • DCL：管理用户、授权

（DBA：数据库管理员，需要熟悉DCL，日常开发用的不多）

管理用户

增删查用户

添加用户。语法：

1
create user '用户名' @ '主机名' IDENTIFIED by '密码';	-- 主机名使用通配符%表示可以在任意主机使用用户登陆数据库

删除用户。语法：

1
drop user '用户名' @ '主机名';

查询用户。

1
2
3
4
-- 切换到mysql数据库
use mysql;
-- 查询user表
select * from user;

修改用户密码

语法：

1
2
3
4
-- 方法1
update user set password = password('新密码') where user = '用户名';
-- 方法2
set password for '用户名' @ '主机名' = password('新密码');

例子：

1
2
3
4
-- 方法1
update user set password = password('abc') where user = 'root';	-- 将root用户密码改成abc
-- 方法2
set password for 'lisi' @ 'localhost' = password('123');	-- 将lisi用户密码改成123

mysql中忘记了root用户密码的解决方法：

  1. 以管理员身份启动cmd，输入net stop mysql停止mysql服务

  2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables

  3. 打开新的cmd窗口，直接输入mysql命令，敲回车即可登陆成功

  4. 输入sql如下：

    1
    2
    use mysql;
    update user set password = password('新密码') where user = 'root';

  5. 关闭前几步打开的两个cmd窗口，打开任务管理器，手动结束msyql.exe的进程

  6. 启动mysql服务，使用新密码登录

权限管理

视频讲解

查询权限：

1
2
3
4
-- 语法：
show grants for '用户名' @ '主机名';
-- 例子：
show grants for 'lisi' @ '%';	-- 查询lisi用户在任意主机的权限

授予权限：

1
2
3
4
-- 语法：
grant 权限列表 on 数据库名.表名 to '用户名' @ '主机名';
-- 例子：
grant ALL on *.* to 'zhangsan' @ 'localhost';	-- zhangsan用户在localhost主机上，在所有数据库所有表（*.*），被授予了所有的权限（ALL）

撤销权限：

1
2
3
4
-- 语法：
revoke 权限列表 on 数据库名.表名 from '用户名' @ '主机名';
-- 例子：
revoke update on db3.account from 'lisi' @ '%';	-- 撤销lisi用户在任意主机上对db3数据库中account表的update权限