⑤MySQL之约束二
2021-09-14 本文已影响0人
SimonLike
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之约束一
