MYSQL——事务&DCL

四 三范式【了解】

4.1 概述

* 范式(Normal Form)
    设计数据库表的规则，好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。
    建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储 。
        
* 分类
    目前关系数据库有六种范式：
            第一范式（1NF）、
            第二范式（2NF）、
            第三范式（3NF）、
            巴斯-科德范式（BCNF）、
            第四范式(4NF）、
            第五范式（5NF，又称完美范式）。
    满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。
        一般说来，数据库只需满足第三范式(3NF）就行了。 

4.2 三范式

看图

1. 第一范式（1NF）
    要求：
        每一列不能再拆分
2. 第二范式（2NF）
    要求：
        1.一张表只描述一件事情
        2.表中的每一个普通列都依赖于主键
3. 第三范式（3NF）
    要求：
        从表的外键必须使用主表的主键

五 事务安全 TCL【理解】

tcl：Transaction Control Language

5.1 概述

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

准备基础数据

-- 创建数据库
CREATE DATABASE web13_3;
USE web13_3;

-- 创建数据表
CREATE TABLE account (  -- 账户表
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(32),
    money DOUBLE 
);

-- 添加数据
INSERT INTO account (NAME, money) VALUES ('李圣', 1000), ('凤姐', 1000);

模拟转账

-- 李圣扣钱（五姑娘）
UPDATE account SET money = money-100 WHERE id = 1;


-- 机器故障


-- 凤姐收钱 （稍等服务...）
UPDATE account SET money = money+100 WHERE id = 2;

5.2 操作

5.2.1 手动提交事务【重点】

1. 开启事务
        begin
2. 提交事务
        commit
3. 回滚事务
        rollback

转账成功

-- 1.开启事务
begin;
-- 2.李圣扣钱
update account set money = money - 100 where id = 1;
-- 3.凤姐收钱
update account set money = money + 100 where id = 2;
-- 4.提交事务
commit;

转账失败

-- 1.开启事务
begin;
-- 2.李圣扣钱
update account set money = money - 100 where id = 1;
-- 3.机器故障

-- 4.回滚事务
rollback;

5.2.2 自动提交事务【了解】

* MySQL数据库每一条DML（增删改）语句，就是一个独立的事务

* 查看MySQL是否开启自动提交
        show variables like 'autocommit';
        
* 临时关闭自动提交
        set autocommit = OFF;

模拟转账

-- 1.李圣扣钱
update account set money = money - 100 where id = 1;

-- 2.改为手动提交或回滚
commit / rollback

5.3 事务原理

注意：

​ 在同一个事务中，如果出现bug，那么必须执行rollback命令，不然会影响当前事务中下次提交

5.4 保存点【了解】

​ 当事务开启后，一部分sql执行成功，添加一个保存点，后续操作报错了，回滚到保存点，保证之前的操作可以成功提交

1. 设置保存点
        savepoint 保存点;
        
2. 回滚到保存点
        rollback to  保存点;

场景：李圣同时约5个小姐姐服务，如果前二个没有失败，设置保存点，保证前二个成功上门服务...

-- 1. 开启事务
begin;
-- 2.李圣扣钱一次（凤姐）
update account set money = money - 100 where id = 1;
-- 3.李圣扣钱二次（石榴姐）
update account set money = money - 100 where id = 1;
-- 4.设置保存点
savepoint ol;
-- 5.李圣扣钱三次（如花）
update account set money = money - 100 where id = 1;
-- 6.机器故障

-- 7.回滚到保存点
rollback to ol;
-- 8.提交事务
commit;

5.5 事务特性 ACID【面试】

1. 原子性：A atomicity
        如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
        
2. 一致性：C consistency
        事务执行的前后，保证数据一致性
        
3. 隔离性:I isolation【重点】
        多个事务之间，相互独立，互不干扰.......

4. 持久性:D durability
        事务一旦成功提交，保存到磁盘文件中，不可逆....

5.6 事务隔离性

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

1. 脏读
        一个事务中，读取到另一个事务，未提交的数据。
        
2. 不可重复读
        一个事务中，二次读取的内容不一致，另一个事务做了update操作

3. 幻读
        一个事务中，二次读取的数量不一致，另一个事务做了delete、insert操作

MySQL数据库隔离级别

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

性能角度：1>2>3>4

安全角度：4>3>2>1

综合考虑：2 or 3

演示不同隔离级别产生的问题

1. 查看当前数据库隔离级别
        show variables like '%isolation%';
2. 临时修改隔离级别
        set session transaction isolation level 级别字符串;

准备二个事务：通过dos窗口 A事务 B事务 模拟

1. 演示脏读问题：
    设置数据库隔离级别为： read uncommitted;
        set session transaction isolation level read uncommitted;

2. 解决脏读问题：（引发不可重复读问题）
    设置数据库隔离级别为： read committed;
        set session transaction isolation level read committed;

3. 解决不可重复读：
    设置数据库隔离级别为： repeatable read;
        set session transaction isolation level repeatable read;

5. 演示串行化
    设置数据库隔离级别为： serializable;
        set session transaction isolation level serializable;

六 用户权限 DCL【了解】

DCL：Data Control Language

1. SQL分类总结
    * DDL:操作数据库、表
    * DML:操作数据的增删改
    * DQL:操作数据的查询【重中之重】
    * TCL:事务安全
    * DCL:用户权限
    
2. 创建用户
    语法：
        create user '用户名'@'主机名' identified by '密码';
    注意：
        主机名：限定客户端访问ip地址
        %：任意客户端ip都可以登录
        
3. 授权用户
    语法：
        grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';
    注意：
        权限：select、delete、insert、update、create、alter.....
        所有：all
        数据库名.* ：指定库下所有的表
        
3. 查看权限
    语法：
        show grants for '用户名'@'主机名'; 
        
4. 撤销授权
    语法：
        revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名'; 
    注意：
        权限：select、delete、insert、update、create、alter.....
        所有：all
        数据库名.* ：指定库下所有的表
        
5. 删除用户
    语法：
        drop user '用户名'@'主机名';
        
6. 密码管理
    1.超级管理员
        set password for '用户名'@'主机名'=password('新密码');
    2.普通用户
        set password=password('新密码');

-- 创建用户
CREATE USER 'lisheng'@'%' IDENTIFIED BY '123';

-- 授权用户
GRANT SELECT ON web13_3.* TO 'lisheng'@'%';

-- 查看指定用户所有权限
SHOW GRANTS FOR 'lisheng'@'%';

-- 撤销权限
REVOKE SELECT ON web13_3.* FROM 'lisheng'@'%';

-- 密码加密
SELECT PASSWORD('123');

-- 通过超级管理员修改 lisheng密码
SET PASSWORD FOR 'lisheng'@'%' = PASSWORD('777');

-- 修改自己密码  （dos窗口执行）
SET PASSWORD = PASSWORD('999');

-- 删除用户
DROP USER 'lisheng'@'%';