事务

事务的应用场景说明

• 什么是事务： 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。
  事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的SQL 语句都要回滚，整个业务执行失败。

手动提交事务

MySQL 中可以有两种方式进行事务的操作：

1. 手动提交事务
2. 自动提交事务

手动提交事务的 SQL 语句

功能	SQL 语句
开启事务	start transaction;
提交事务	commit;
回滚事务	rollback;

手动提交事务使用过程：

1. 执行成功的情况： 开启事务 → 执行多条SQL语句 → 成功提交事务
2. 执行失败的情况： 开启事务 → 执行多条SQL语句 → 事务的回滚

总结: 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL
语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务

取消自动提交

• 查看 MySQL 是否开启自动提交事务

@@表示全局变量，1 表示开启，0 表示关闭

• 取消自动提交事务

事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

原理图：

事务的步骤：

1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
2. 开启事务以后，所有的操作都会先写入到临时日志文件中
3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回
4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

回滚点

什么是回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

回滚点的操作语句

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

事务的隔离级别

事务的四大特性 ACID

事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务 update 时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是 insert 或 delete 时引发的问题

MySQL 数据库有四种隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle 和 SQL Server
3	读未提交	read uncommitted	否	否	是	MySQL
4	读未提交	read uncommitted	否	否	否

隔离级别越高，性能越差，安全性越高。

MySQL 事务隔离级别相关的命令

• 查询全局事务隔离级别

查询隔离级别 select @@tx_isolation;

• 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

设置隔离级别 set global transaction isolation level 级别字符串;

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
解决脏读的问题：将全局的隔离级别进行提升
解决不可重复读的问题： 将全局的隔离级别进行提升为：repeatable read
解决幻读的问题： 使用 serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读

DCL (Data Control Language)

• DDL： create / alter / drop
• DML：insert /update/delete
• DQL ：select /show
• DCL ：grant /revoke
  我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。

查询用户

-- 1. 切换到mysql数据库
USE myql;
-- 2. 查询user表
SELECT * FROM USER;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

关键字说明：

关键字	说明
‘用户名’	将创建的用户名
‘主机名’	指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
‘密码’	该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

具体操作：

创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123
create user 'user1'@'localhost' identified by '123';

创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123
create user 'user2'@'%' identified by '123';

注：创建的用户名都在 mysql 数据库中的 user 表中可以查看到，密码经过了加密。

给用户授权

GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';

关键字说明：

关键字	说明
GRANT…ON…TO	授权关键字
权限	授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的权限则使用 ALL
数据库名.表名	该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
‘用户名’@‘主机名’	给哪个用户授权，注：有 2 对单引号

具体操作：

给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';

注：用户名和主机名要与上面创建的相同，要加单引号。

给 user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';

撤销授权

REVOKE 权限 1, 权限 2... ON 数据库.表名 revoke all on test.* from 'user1'@'localhost'; '用户名'@'主机名';

关键字	说明
REVOKE…ON…FROM	撤销授权的关键字
权限	用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等，所有的权限则使用 ALL
数据库名.表名	对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用表示，如.*
‘用户名’@‘主机名’	给哪个用户撤销

具体操作：

撤销 user1 用户对 test 数据库所有表的操作的权限
revoke all on test.* from 'user1'@'localhost';

注：用户名和主机名要与创建时相同，各自要加上单引号

查看权限

SHOW GRANTS FOR '用户名'@'主机名';

具体操作：

查看 user1 用户的权限

注：usage 是指连接（登陆）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）。

删除用户

DROP USER '用户名'@'主机名';

具体操作：

删除 user2
drop user 'user2'@'%';

修改管理员密码

mysqladmin -uroot -p password 新密码

注意：需要在未登陆 MySQL 的情况下操作，新密码不需要加上引号。

修改普通用户密码

set password for '用户名'@'主机名' = password('新密码');	

注意：需要在登陆 MySQL 的情况下操作，新密码要加单引号。

管理员密码忘记并修改密码

1. cmd – > net stop mysql 停止mysql服务

需要管理员运行该cmd

2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
4. use mysql;
5. update user set password = password(‘你的新密码’) where user = ‘root’;
6. 关闭两个窗口
7. 打开任务管理器，手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录。