⑤MySQL之约束二

1，事务（Transaction）

1.1，什么是事务？

一个事务是一个完整业务逻辑单元，不可再分。

比如：银行账户转账，从A账户向B账户转账10000，需要执行两条update语句
    update t_act set balance = balance - 10000 where actno = 'act-001';
    update t_act set balance = balance + 10000 where actno = 'act-002';
以上两条DML语句必须同时成功，或同时失败，不允许出现一条成功，一条失败

要想保住以上两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

1.2，和事务相关的语句只有DML语句。（insert delete update）

这三个语句都是和数据库表当中的“数据”相关的，事务的存在是为了保住数据的完整性，安全性。

1.3，假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要，但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。

1.4，事务包括四大特征：ACID

A：原子性：事务是最小的工作单元，不可再分。
C：一致性：事务必须保证多条DML语句同时成功或失败。
I：隔离性：事务A和B之间具有隔离。
D：持久性：说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

1.5，事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：
    第一级别：读未提交（read uncommitted）
        对方事务还未提交，我们当前事务可以读取到对方未提交的数据。
        读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
    第二级别：读已提交（read committed）
        对方事务提交之后的数据我方可以读取到。
        读已提交存在的问题：不可重复读。
    第三级别：可重复读（repeatable read）
        这种隔离级别解决了：不可重复读问题。
        可重复读存在问题：读取到的数据是幻象的
    第四级别：序列化读/串行化读(serializable)
        解决了所有问题，
        存在问题：效率低，事务需要排队。

    oracle数据库默认的隔离级别是：读已提交。
    mysql数据库默认的隔离级别是：可重复读。

1.6，演示事务

mysql事务是默认情况下自动提交的。（只要执行任意一条DML语句则提交一次。）
关闭自动提交：start transaction;

准备一个表
    drop table if exists t_user;
    create table t_user(
        id int primary key auto_increment,
        username varchar(255) unique
    );
---------------------------------------------------------------------------------------------
演示：(插入一条记录，rollback回滚，再次查询还是有一条记录，说明：要执行DML语句就会提交一次。)
    mysql>
        insert into t_user(username) values('e');
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        +----+----------+

    mysql>
        rollback;
        select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        +----+----------+
---------------------------------------------------------------------------------------------
演示：使用start transaction;关闭自动提交机制。
    mysql>
        start transaction;
        insert into t_user(username) values('es'),('ls');
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        |  2 | es       |
        |  3 | ls       |
        +----+----------+

    mysql>
        rollback;
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        +----+----------+
---------------------------------------------------------------------------------------------
演示提交：commit;
    mysql>
        start transaction;
        insert into t_user(username) values('es'),('ls'),('lss');
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        |  4 | es       |
        |  5 | ls       |
        |  6 | lss      |
        +----+----------+

    mysql>
        commit;
        rollback;
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        |  4 | es       |
        |  5 | ls       |
        |  6 | lss      |
        +----+----------+

1.7，使用事务的隔离级别

设置事务的全局隔离级别：
    set global transaction isolation level 级别;
    set global transaction isolation level read uncommitted;
    set global transaction isolation level read committed;
    set global transaction isolation level repeatable read;
    set global transaction isolation level serializable;
查看事务的全局隔离级别：
    MySQL8.0以前：select @@global.tx_isolation;
    MySQL8.0及以以上版本：select @@global.transaction_isolation

2，索引（是给字段添加索引）

2.1，什么是索引，有什么用？

索引相当于一本书的目录，通过目录可以快速的找到对应的资源
在数据库方面，查询一张表的时候有两种检索方式：
    第一种方式：全表扫描
    第二种方式：根据索引检索（效率高）

索引为什么可以提高检索效率
    根本原因是缩小了扫描范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引在数据库中的对象，也需要
不断的维护，是有维护成本的。比如：表中的数据经常被修改，这样就不适合添加索引，因为数据
一旦修改，索引需要重新排序，进行维护。

2.2，怎么创建索引对象，怎么删除索引对象？

创建索引对象：create index 索引名称 on 表名(字段名);
删除索引对象：drop index 索引名称 on 表名;

2.3，什么时候给字段添加索引？（满足什么条件）

数据量庞大
该字段很少的DML操作
该字段经常出现在where字句中。

2.4，主键和具有unique约束的字段会自动添加索引。

2.5，查看sql语句的执行计划（执行详情）：explain select语句;

mysql> explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

给薪资sal字段添加索引
    create index emp_sal_index on emp(sal);

mysql> explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

删除sal字段的索引
    drop index emp_sal_index on emp;

2.6，索引底层采用的数据结构：B + Tree

2.7，索引的实现原理

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到
数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

2.8，索引的分类：

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加一个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段会自动添加索引。
...

2.9，索引什么时候失效？

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

3，视图（view）

3.1，什么是视图

站在不同的角度去看数据。

3.2，怎么创建视图,怎么删除视图

复制一张表
create table emp_bak as select * from emp;

创建视图
create view emp_bak_view as select empno,ename,sal from emp_bak;
删除视图
drop view emp_bak_view;

注意：只要DQL语句才能以视图对象的方式创建出来。

3.3，对视图进行增删改查，会影响原表数据。（通过视图影响原表数据，不是直接操作的原表）

可以对视图进行CRUD操作

3.4，面向视图操作

mysql> select * from emp_bak_view;
    +-------+--------+---------+
    | empno | ename  | sal     |
    +-------+--------+---------+
    |  7369 | SMITH  |  800.00 |
    |  7499 | ALLEN  | 1600.00 |
    |  7521 | WARD   | 1250.00 |
    |  7566 | JONES  | 2975.00 |
    |  7654 | MARTIN | 1250.00 |
    |  7698 | BLAKE  | 2850.00 |
    |  7782 | CLARK  | 2450.00 |
    |  7788 | SCOTT  | 3000.00 |
    |  7839 | KING   | 5000.00 |
    |  7844 | TURNER | 1500.00 |
    |  7876 | ADAMS  | 1100.00 |
    |  7900 | JAMES  |  950.00 |
    |  7902 | FORD   | 3000.00 |
    |  7934 | MILLER | 1300.00 |
    +-------+--------+---------+

通过视图修改原表数据
mysql>
    update emp_bak_view set ename = 'hehe',sal = 1 where empno = 7369;
    select * from emp_bak;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7369 | hehe   | CLERK     | 7902 | 1980-12-17 |    1.00 |    NULL |     20 |
    |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
    |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
    |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    +-------+--------+-----------+------+------------+---------+---------+--------+

通过视图删除原表数据
mysql>
    delete from emp_bak_view where empno = 7369;
    select * from emp_bak;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
    |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
    |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    +-------+--------+-----------+------+------------+---------+---------+--------+

3.5，视图的作用

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员
只对视图对象进行CRUD。

4，DBA命令

4.1，数据导出

导出整个数据库：mysqldump 数据库名称>路径数据库名.sql -uroot -p
导出某一张表：mysqldump 数据库名称 表名>路径数据库名.sql -uroot -p

4.2，数据导入

create database 数据库名称;
use 数据库名称;
source 路径数据库名.sql

5，数据库设计三范式

5.1，什么是设计范式

设计表的依据，按照这三个范式设计的表不会出现数据冗余。

5.2，三范式

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有的非主键字段完全依赖主键，不能产生部分依赖。
（多对多，三张表，关系表两个外键）
第三范式：建立在第二范式的基础之上，所有的非主键字段直接依赖主键，不能产生传递依赖。
（一对多，两张表，多的表加外键）

6，悲观锁乐观锁

悲观锁：事务必须排队执行，数据锁住了，不允许并发。（行级锁：select语句后面加：for update）
乐观锁：支持并发，事务不需要排队，只不过需要一个版本号。

上篇：④MySQL之约束一