0103 MySQL06

1.事务

1.一个事务其实就是一个完整的业务逻辑

如：转账，从A账户向B账户转账10000，将A账户的钱减去10000（update），将B账户的钱加上10000（update），这就是一个完整的业务逻辑

以上操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分

只有DML语句才会有事务（insert，delete，update），因为这些语句是数据库表中数据进行增删改的，只要一旦涉及增删改，一定要考虑安全问题

---

2.对事务的理解

假设所有业务只要一条DML语句就能完成，还有必要存在事务机制吗？

不必要，没有存在的价值，正是做某件事需要多条DML语句联合才能完成，所以需要事务的存在

说到底，一个事务其实就是多条DML语句同时成功，或同时失败

---

3.事务如何实现批量DML语句同时成功或失败？

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件

事务开启：

insert

delete

update

update

....

事务结束

在事务执行过程中，每一条DML的操作都会记录到’事务性活动的日志文件‘中，在这个过程中，可以提交事务，也可以回滚事务

提交事务？

清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中，提交事务标志着事务的结束，且是一种全部成功的结束

回滚事务？

将之前所有的DML操作全部撤销，且清空事务性活动的日志文件，标志着事务的结束，且是一种全部失败的结束

---

4.怎么提交事务？怎么回滚事务？

提交事务：commit;

回滚事务：rollback;（只能回滚到上一次的提交点）

事务对应的英文：transaction

在MySQL默认的事务行为？

默认情况下支持自动提交事务，每执行一条DML语句，则提交一次

自动提交不符合开发习惯，因为一个业务通常需要多条DML语句共同执行，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一次

将MySQL自动提交机制关闭？

start transaction;开启事务

演示事务：

start transaction;

insert into dept_bak values(10,'abc','aa');

insert into dept_bak values(10,'abc','bb');

select * from dept_bak;//有数据

rollback;

select * from dept_bak;//Empty

---

5.事务的四个特性

原子性：说明事务是最小的工作单元，不可再分

一致性：在同一个事务中，所有操作必须同时成功或同时失败，保证数据一致性

隔离性：A事务和B事务之间具有一定隔离。

持久性：事务最终结束的一个保障，事务提交就相当于将没有保存到硬盘上的数据保存到硬盘上

---

6.隔离级别

A教室和B教室中有一道墙，可以很厚也可以很薄，这就是事务的隔离级别

4个隔离级别？

读未提交：read uncommitted（最低隔离级别）《没有提交就读到》

        事务A可以读取到事务B未提交的数据，存在脏读（Dirty read）现象，即读到了脏数据

这种隔离级别一般都是理论上的，大多数数据库隔离级别都是二档起步

读已提交：read committed《提交后读到》

        事务A只能读取到事务B提交之后的数据，解决了脏读现象，存在不可重复读取数据问题，

这种隔离级别是比较真实的数据，每一次读到的数据绝对真实，Oracle数据库默认的隔离级别

        不可重复读取数据？

        在事务开启后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取时，读到的数据是4条，称为不可重复读取

可重复读：repeatable read《提交后也读不到》

        事务A开启之后，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改且提交，事务A读取到的数据还是没发生改变，解决了不可重复读的问题，存在了幻影读问题，每一次读取的数据都是幻象，不够真实，永远读取的都是刚开启事务时的数据。MySQL默认的隔离级别

序列化/串行化：serializable（最高隔离级别）

最高隔离级别，效率最低，解决了所有问题

这种隔离级别表示事务排队，不能并发

---

7.演示各种隔离级别

查看隔离级别：select @@tx_isolation

1.演示read uncommitted

set global transaction isolation level read uncommitted;设置全局隔离级别为读未提交

事务A：

start transaction;

select * from t_user;//能查询到事务B未提交数据

事务B：

start transaction;

insert into t_user values('zhangsan');//未提交

---

2.演示read committed

set global transaction isolation level read committed;

事务A：

start transaction;

select * from t_user;//不能查询到

select * from t_user;//commit后能查询到

事务B：

start transaction;

insert into t_user values('zhangsan');//未提交

commit；//提交

---

3.演示repeatable read

set global transaction isolation level repeatable read；

事务A：​​​​​​​

start transaction;

select * from t_user;//不能查询到

select * from t_user;//commit后也不能查询到

事务B：

start transaction;

insert into t_user values('zhangsan');//未提交

commit；//提交

---

4.演示serializable

set global transaction isolation level serializable;

事务A：​​​​​​​

start transaction;

select * from t_user;//光标卡着不动，等待另一个事务提交

select * from t_user;//提交后可查询到

事务B：

start transaction;

insert into t_user values('zhangsan');//未提交

commit；//提交

---

2.索引

索引（index）是在数据库表的字段上添加，为了提高查询效率存在的一种机制，一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引，相当于一本书的目录，为了缩小扫描范围而存在的一种机制

在MySQL数据库当中索引也需排序，索引排序和TreeSet数据结构相同，底层是一个自平衡二叉树，在MySQL当中索引是一个B-Tree数据结构，遵循左小右大原则，采用中序遍历

索引是各种数据库进行优化的重要手段，优化时优先考虑的因素就是索引

---

1.索引的实现原理

假设有一张用户表t_user

id(pk)	name	硬盘上物理存储编号
100	zhangsan	0x1111
120	lisi	0x2222
99	wangwu	0x8888
130	zhaoliu	0x9999
55	jack	0x6666

idIndex(id字段的索引对象)

select * from t_user where id = 130;

MySQL发现id字段上有索引对象，会通过索引对象idIndex进行查找

通过索引对象定位到：130，得出物理编号：0x9999，此时SQL语句转换：

select * from t_user where 物理编号 = 0x9999;

注1：在任何数据库当中，主键上都会自动添加索引对象，即id字段上自动有索引，在MySQL中，一个字段上如果有unique约束，也会自动创建索引对象

注2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号

注3：在MySQL中，索引是一个单独的对象，不同的存储引擎以不同形式存在。在MyISAM存储引擎中，索引存储在一个.MYI文件中，在InnoDB存储引擎中，索引存储在tablespace中，在MEMORY存储引擎中，索引存储在内存里。

什么条件下会考虑给字段添加索引？

1.数据量庞大

2.该字段经常出现在where后面，以条件形式存在，即这个字段总是被扫描

3.该字段很少DML（增删改）操作（因为DML后索引需重新排序）

---

2.索引的创建和删除

创建索引：

create index emp_ename_index on emp(ename);

给emp表的ename字段添加索引，起名：emp_ename_index

删除索引：

drop index emp_ename_index on emp;

将emp表上的emp_ename_index索引对象删除

查看一个SQL语句是否使用索引进行检索？

explain select * from emp where ename = 'Jack';

//type=ALL,扫描了14条记录，说明没有使用索引

create index emp_ename_index on emp(ename);

explain select * from emp where ename = 'Jack';

//type=ref,扫描了1条记录，使用了索引

---

3.索引失效

失效1：

select * from emp where ename like '%T';

//即使添加了索引，也不会走索引，因为模糊匹配当中以%开头了，应尽量避免模糊查询%开头

失效2：

select * from emp where ename = ‘jack’ or job = 'manager';

//使用or，要求两边条件字段都要有索引，否则不走索引

失效3：

creat index emp_job_sal_index on emp(job,sal);

select * from emp where job = 'manager';//使用索引

select * from emp where sal = 3000;//失效

//使用复合索引，没有使用左侧的列查找，索引失效

失效4：

create index emp_sal_index on emp(sal);

select * from emp where sal+1 = 3000;

//在where当中索引列参加了运算，索引失效

失效5：

select * from emp where lower(ename) = 'jack';

//在where当中索引列使用了函数

失效6，7.....

---

4.索引的分类

单一索引：一个字段上添加索引

复合索引：两个字段或多个字段上添加索引

主键索引：主键上添加索引

唯一性索引：具有unique约束的字段添加索引

.....

注：唯一性比较弱的字段上添加索引用处不大

---

3.视图

view：站在不同的角度去看待同一份数据

1.创建视图，删除视图？

创建：

create view dept_view as select * from dept;

删除：

drop view dept_view;

注：只有DQL语句才能以view的形式创建

create view dept_view as DQL语句

---

2.用视图做什么？

可以面向视图对象进行增删改查，会导致原表被操作（即对视图操作，影响原表数据）

create view dept_view as select * from dept;

insert into dept_view(deptno,dname,loc) values(1,'sales','beijing');//原表插入数据

delete from dept_view;//原表被删除

在实际开发中的作用？

假设有一条非常复杂的SQL语句，而这条语句需要在不同位置上反复使用，每一次使用都需要重新编写，很麻烦，这时可把这条SQL语句以视图对象形式新建，在需要编写SQL语句时直接使用视图对象，大大简化开发，且利于后期维护，只需要修改视图对象所映射的SQL语句

视图不是在内存当中，也存储在硬盘上，不会消失

再次注意：视图对应的语句只能是DQL语句，但视图对象创建完成后，可对视图进行增删改查

补充：增删改查又叫CRUD

C：create(增)

R：retrieve（查，检索）

U：update（改）

D：delete（删）

---

4.DBA命令(了解)

1.新建用户

create user xxx identified by '123456';

2.授权

....

3.回收

..

4.数据的导入和导出（掌握）

数据导出：

mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p***

导出指定的表：

mysqldump bjpowernode emp>...

//在dos命令窗口中导出

数据导入

创建数据库：create database bjpowernode;

使用数据库：use bjpowernode;

初始化数据库：source D:\bjpowernode.sql

//先登录到MySQL数据库服务器上

---

5.数据库设计三范式

数据库设计范式？

数据库的设计依据，教你怎么进行数据库表的设计

第一范式：要求如何一张表必须有主键，每一个字段原子性不可再分

第二范式：建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖

第三范式：建立在第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖

设计数据库表时，按照以上范式，可避免表中数据冗余，空间浪费

---

1.第一范式

最核心，最重要的范式，所有表的设计都需满足

必须有主键，且每个字段都是原子性不可再分

编号	姓名	联系方式
1000	张三	[e-mail],13959999999
1001	李四	[e-mail],13956666666
1002	王五	[e-mail],13958888888

以上学生表不满足第一范式：

1.没有主键

2.联系方式可再分为邮箱和电话

编号（pk）	姓名	邮箱	电话
1000	张三	[e-mail]	13959999999
1001	李四	[e-mail]	13956666666
1002	王五	[e-mail]	13958888888

口诀？一对一若表很庞大，拆分为两张表

外键+unique约束

一对一，外键唯一

---

2.第二范式

建立在第一范式基础上，要求所有非主键字段必须完全依赖主键，不产生部分依赖

学生与老师关系表（1个学生可能有多个老师，1个老师可能有多个学生）

复合主键：学生编号+教师编号（pk）

学生编号	教师编号	学生姓名	教师姓名
1001	001	张三	王老师
1002	002	李四	赵老师
1003	001	王五	王老师
1001	002	张三	赵老师

不满足第二范式：

‘张三’依赖1001，‘王老师’依赖001，产生了部份依赖

部份依赖缺点：数据冗余，空间浪费，‘张三’‘王老师’重复

为了满足第二范式，使用三张表表示多对多关系

学生表

学生编号（pk）	学生名字
1001	张三
1002	李四
1003	王五

教师表

教师编号（pk）	教师名字
001	王老师
002	赵老师

学生教师关系表

id(pk)	学生编号（fk）	教师编号（fk）
1	1001	001
2	1002	002
3	1003	001
4	1001	002

口诀：多多对怎么设计？

多对多，三张表，关系表两个外键！

---

3.第三范式

建立在第二范式基础上，要求所有非主键字段必须直接依赖主键，不产生传递依赖

学生编号（pk）	学生姓名	班级编号	班级名称
1001	张三	01	高三一班
1002	李四	02	高三二班
1003	王五	03	高三三班
1004	赵六	03	高三三班

以上表格满足第一范式（有主键），满足第二范式（主键不是复合主键，没有产生部分依赖）

不满足第三范式：

高三一班依赖01，01依赖1001，产生了传递依赖，产生了数据冗余

设计一对多？

班级表：一

班级编号（pk）	班级名称
01	高三一班
02	高三二班
03	高三三班

学生表：多

学生编号（pk）	学生姓名	班级编号（fk）
1001	张三	01
1002	李四	02
1003	王五	03
1004	赵六	03

口诀？

一对多，两张表，多的表加外键

---

注：数据库设计三范式是理论上的，实践上有偏差，最终目的是为了满足客户需求，有时会拿冗余换执行速度，因为SQL中，表和表之间连接次数多，效率越低（笛卡尔积），有时可能存在冗余，但为了减少表的连接次数，这样做也合理，对于开发人员来说，SQL语句的编写难度也降低