MYSQL数据库之事务与权限

事务

概念

1.用户定义的一组完成特定操作的数据的集合。
2.是不可再分的工作单元，通常一个事务对应一个完整的业务。
3.数据只和增删改有关。

特性ACID

1.原子性A：一个事务要么全成功，要么全失败。
2.一致性C：事务从一个一致的状态，到另一个一致的状态。
3.隔离性I：不同事物并发进行，互不干扰。
4.持久性D：数据一旦改变，其变化是永久的保存在数据库中。

MYSQL事务提交模式

1.默认是自动提交
2.手动提交

事务的开启和结束

1.start Transaction任何DML语句都标志着事务的开启
2.end Transcation提交事务或者回滚事务

事务与数据库底层数据的交互

事务的并发

1.读取脏数据：事务1读取事务2更新的数据，2做了回滚。
2.不可重复读：事务1需要多次读取同一数据，事务2在1读取数据的时候更改看数据，导致1读取的同一数据前后不一致。
3.幻读：管理员1已经将数据从数值改为相应等级，管理员2又插入数值数据，就像出现幻觉。

事务的四种隔离级别

1.未提交读(脏读)：事务1与2都能读到数据A，当事务1修改了数据A还未提交的时候，事务2也能读到修改的数据。
2.提交读：不会脏读，事务1读数据并修改，事务2读同一数据，事务1提交之前，事务2读的是原始数据。提交之后，看到的是信数据。即两次读的数据不一致。
3.可重复读：两者互不影响。
4.可串行化：事务2的执行必须在事务1执行结束之后。

USE employeemanager
-- 创建触发器 tr_employee_insert当新员工入职的时候，数值更新
CREATE TRIGGER tr_employee_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
	-- 修改实际人数和实际名额
	UPDATE dept SET ActualNum=ActualNum+1 WHERE did=new.did;
	UPDATE dept SET RemainderNum=RemainderNum-1 WHERE did=new.did;
END
-- 测试
SELECT * FROM employee;
USE studentmanager;
-- 事务
-- 创建账户表
CREATE TABLE IF NOT EXISTS account(
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(50) NOT NULL,
	money DECIMAL(9,2)
)ENGINE=INNODB
-- 插入数据
INSERT INTO account(username,money) VALUES('tom',1000);
INSERT INTO account(username,money) VALUES('jack',200);
-- 模拟事务的回滚和提交操纵
-- 关闭自动提交模式
SET autocommit=0;
-- SET autocommit=1;
-- 开始事务
START TRANSACTION;
UPDATE account SET money=money-500 WHERE username='tom';
UPDATE account SET money=money+500 WHERE username='jack';
-- 假设转账过程出现异常，执行回滚操作
-- ROLLBACK;
-- 假设转账过程没有异常，执行提交
COMMIT;
SELECT * FROM account;
-- 查看MYSQL的事务隔离级别:可重复读
SELECT @@tx_isolation;
-- 设置MYSQL的隔离级别,读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 客户端1
-- 开始事务
START TRANSACTION;
SELECT * FROM account;
-- 客户端2
START TRANSACTION;
UPDATE account SET money=money-100 WHERE id=1;

权限控制

USE mysql;
-- 创建用户
SELECT * FROM user;
-- 创建用户 CREATE USER '用户名'@'主机名'IDENTIFIED BY '密码'
CREATE USER user1@'localhost' IDENTIFIED BY '1234';
-- 执行flush命令使得用户生效，刷新权限
FLUSH PRIVILEGES;
-- 创建用户，允许任何ip都可以登录MYSQL数据库
CREATE USER user2@'%' IDENTIFIED BY '123456';
-- 使用drop user 删除用户
DROP USER user2@'%';
-- 使用delete删除用户
DELETE FROM user WHERE HOST='localhost' AND USER='user1';
-- 修改密码
UPDATE user SET password=PASSWORD('123456') 
WHERE user='user1' AND HOST='localhost';
-- 查看用户权限 SHOW GRANTS FOR 用户名
SHOW GRANTS FOR user1@localhost;
-- 给用户授权 GRANT 权限1....权限n ON 数据库 表 TO 用户名；
GRANT SELECT,UPDATE ON bigdata.* TO user1@'localhost';
FLUSH PRIVILEGES;

GRANT SELECT,UPDATE ON bigdata.test TO user1@'localhost';
FLUSH PRIVILEGES;

GRANT SELECT,UPDATE ON *.* TO user1@'localhost';
FLUSH PRIVILEGES;
--  拥有最高权限
GRANT ALL ON bigdata.* TO user2@localhost;
FLUSH PRIVILEGES;
-- 收回权限 REVOKW 权限1....权限n ON 数据库 表 FROM 用户名；