Mysql学习总结

Day01_mysql基础

一.操作数据库

1.查询所有数据库

show databases//查询所有数据库
1

2.查询某个数据库的创建

show create database xx;
-- 查询mysql数据库的创建语句
SHOW CREATE DATABASE mysql;
1
2
3

3.创建数据库

create database xx;
-- 创建db1数据库
CREATE DATABASE db1;
1
2
3

4.创建数据库，判断、如果不存在则创建

CREATE DATABASE IF NOT EXISTS 数据库名称;
-- 创建数据库db2(判断，如果不存在则创建)
CREATE DATABASE IF NOT EXISTS db2;
1
2
3

5.创建数据库、并指定字符集

create database 数据库名称 character set 编码方式；
-- 创建数据库db1、并指定字符集utf8
create database db01 character set utf8;
1
2
3

6.练习

-- 练习：创建db4数据库、如果不存在则创建，指定字符集为gbk
CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;


-- 查看db4数据库的字符集
SHOW CREATE DATABASE db4;
1
2
3
4
5
6

7.修改数据库的字符集

alter database 数据库名称 character set 字符集;
-- 修改数据库db4的字符集为utf8
ALTER DATABASE db4 CHARACTER SET utf8;
show create database db04;
1
2
3
4

8.删除数据库

drop database  数据库名称;
-- 如果数据库存在则删除
drop database if exists db04;
1
2
3

9.使用数据库

use database db04;
1

10.查看当前使用的数据库名称

select database();
1

二.操作数据表

1.查询所有数据表

show tables;
1

2.查询数据表的结构

desc db01;
1

3.查询某个数据表的字符集

show table status from db01 like 'product';
1

4.创建数据表

-- 标准语法：
		CREATE TABLE 表名(
			列名 数据类型 约束,
			列名 数据类型 约束,
			...
			列名 数据类型 约束
		);
		
-- 创建一个user表
create table user (
	name varchar(10),
    age int,
    score varchar(10)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6.查看数据表的结构

desc user;
1

7.修改表名

/*
	修改表名
	标准语法：
		ALTER TABLE 旧表名 RENAME TO 新表名;
*/
-- 修改user表的名称改为usr01；
alter table user rename to user01;
1
2
3
4
5
6
7

8.修改表的字符集

alter table user character set gbk;
1

9.查看某个表的字符集

show table status from db01 like 'user';
1

10.给表添加列

alter table user add birthday date;
1

11.更改某个列的数据类型

alter table user modify age int;
1

12.修改表中某个列的名称和数据类型

alter table user change gender sex int;
1

13.删除表中的某一列

alter table user drop sex;
1

14.删除表

drop table user ;
1

三.表数据的增删改

1.给表中添加数据

insert into product(id,name,price,stock,inset_time) values(1,'手机',100,1,'2020-1-1');
1

2.修改表数据

标准语法：
		UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... [where 条件];
*/
-- 修改手机的价格为3500
UPDATE product SET price=3500 WHERE NAME='手机';

-- 修改电脑的价格为1800、库存为36
UPDATE product SET price=1800,stock=36 WHERE NAME='电脑';

/*
1
2
3
4
5
6
7
8
9
10

3.删除表数据

/*
	删除表数据
	标准语法：
		DELETE FROM 表名 [WHERE 条件];
*/
-- 删除product表中的微波炉信息
DELETE FROM product WHERE NAME='微波炉';

-- 删除product表中库存为10的商品信息
DELETE FROM product WHERE stock=10;
1
2
3
4
5
6
7
8
9
10

四.表数据的查询

1.查询全部数据

/*
	查询全部数据
	标准语法：
		SELECT * FROM 表名;
*/
-- 查询product表所有数据
SELECT * FROM product;
1
2
3
4
5
6
7

2.查询某一列的数据

/*
	查询指定列
	标准语法：
		SELECT 列名1,列名2,... FROM 表名;
*/
-- 查询名称、价格、品牌
SELECT NAME,price,brand FROM product;


/*
1
2
3
4
5
6
7
8
9
10

3.去除重复查询

/*
	去除重复查询
	标准语法：
		SELECT DISTINCT 列名1,列名2,... FROM 表名;
*/
-- 查询品牌
SELECT brand FROM product;
-- 查询品牌，去除重复
SELECT DISTINCT brand FROM product;
1
2
3
4
5
6
7
8
9

4.计算列的值

/*
	计算列的值
	标准语法：
		SELECT 列名1 运算符(+ - * /) 列名2 FROM 表名;
		
	如果某一列为null，可以进行替换
	ifnull(表达式1,表达式2)
	表达式1：想替换的列
	表达式2：想替换的值
*/
-- 查询商品名称和库存，库存数量在原有基础上加10
SELECT NAME,stock+10 FROM product;

-- 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断
SELECT NAME,IFNULL(stock,0)+10 FROM product;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5.起别名

*
	起别名
	标准语法：
		SELECT 列名1,列名2,... AS 别名 FROM 表名;
*/
-- 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断。起别名为getSum
SELECT NAME,IFNULL(stock,0)+10 AS getSum FROM product;
SELECT NAME,IFNULL(stock,0)+10 getSum FROM product;
1
2
3
4
5
6
7
8

7.条件查询

/*
	条件查询
	标准语法：
		SELECT 列名列表 FROM 表名 WHERE 条件;
*/
-- 查询库存大于20的商品信息
SELECT * FROM product WHERE stock > 20;


-- 查询品牌为华为的商品信息
SELECT * FROM product WHERE brand='华为';

-- 查询金额在4000 ~ 6000之间的商品信息
SELECT * FROM product WHERE price >= 4000 and price <= 6000;
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;


-- 查询库存为14、30、23的商品信息
SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
SELECT * FROM product WHERE stock IN(14,30,23);

-- 查询库存为null的商品信息
SELECT * FROM product WHERE stock IS NULL;

-- 查询库存不为null的商品信息
SELECT * FROM product WHERE stock IS NOT NULL;


-- 查询名称以小米为开头的商品信息
SELECT * FROM product WHERE NAME LIKE '小米%';

-- 查询名称第二个字是为的商品信息
SELECT * FROM product WHERE NAME LIKE '_为%';

-- 查询名称为四个字符的商品信息
SELECT * FROM product WHERE NAME LIKE '____';

-- 查询名称中包含电脑的商品信息
SELECT * FROM product WHERE NAME LIKE '%电脑%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

8.聚合函数

-- 计算product表中总记录条数
select count(*) from product;
-- 获取最高价格
select max(price) from product;
-- 获取最低库存
select min(stock) from product;
-- 获取总库存数量
select sum(stock) from product;

-- 获取品牌为苹果的总库存数量
select sum(stock) 苹果总数量 from product where brand='苹果';
-- 获取品牌为小米的平均商品价格
select avg(price) 小米的平均价格 from product where brand='小米';
1
2
3
4
5
6
7
8
9
10
11
12
13

9.排序查询

/*
	排序查询
	标准语法：
		SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
*/
-- 按照库存升序排序
SELECT * FROM product ORDER BY stock ASC;

-- 查询名称中包含手机的商品信息。按照金额降序排序
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;

-- 按照金额升序排序，如果金额相同，按照库存降序排列
SELECT * FROM product ORDER BY price ASC,stock DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13

10.分组查询

/*
	分组查询
	标准语法：
		SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];
*/
-- 按照品牌分组，获取每组商品的总金额
SELECT brand,name,SUM(price) FROM product GROUP BY brand,name;

-- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额
SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;

-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
SELECT brand,SUM(price) getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;

-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
SELECT brand,SUM(price) getSum FROM product 
WHERE price > 4000 
GROUP BY brand 
HAVING getSum > 7000 
ORDER BY getSum DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

11.分页查询

/*
	分页查询
	标准语法：
		SELECT 列名 FROM 表名 
		[WHERE 条件] 
		[GROUP BY 分组列名]
		[HAVING 分组后条件过滤] 
		[ORDER BY 排序列名 排序方式] 
		LIMIT 当前页数,每页显示的条数;
	
	LIMIT 当前索引每页显示的条数;
	公式：当前索引 = (当前页数-1) * 每页显示的条数
*/
-- 每页显示3条数据

-- 第1页  当前页数=(1-1) * 3
SELECT * FROM product LIMIT 0,3;

-- 第2页  当前页数=(2-1) * 3
SELECT * FROM product LIMIT 3,3;

-- 第3页  当前页数=(3-1) * 3
SELECT * FROM product LIMIT 6,3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

12.select的执行顺序

一、mySql的执行顺序

1、FROM  table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1

2、JOIN table2  所以先是确定表，再确定关联条件

3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

4、WHERE  对中间表Temp2产生的结果进行过滤  产生中间表Temp3

5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4

6、HAVING  对分组后的记录进行聚合 产生中间表Temp5

7、SELECT  对中间表Temp5进行列筛选，产生中间表 Temp6

8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7

9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8

10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Day02Mysql进阶

一.约束

1.概念

约束就是用于对表中的数据进行某些限制，通过添加限制条件达成保证数据库中数据完整性的目的。

2.单表约束

主键

主键最多只能有一个，但是主键涉及的列可以有多个。如果一个主键涉及到多个列，我们称为复合主键（联合主键）。

主键约束限制数据必须是非空且唯一的。一般来说，我们使用主键可以唯一标识出一条记录。

主键一般不具有实际业务意义，否则可能会产生一些问题。

关键字：primary key

create table user(
	id int primary key auto_incriment unique not null,
    name varchar(20)
)；
--  删除主键约束
alter table user drop primary key;
1
2
3
4
5
6

数值型主键一般会配置自增使用。

关键字：auto_increment

id int primary key auto_increment,
1

自增的列，添加数据时可以手动指定值，也可以赋值为null。如果赋值为null，则代表自动增长赋值。

自增的操作不可逆，数会一直增加而不会回退。

唯一

限制数据不能重复。

关键字：unique

phone int unique,
1

唯一约束不限制null出现的次数。

非空

限制数据不能为null。

关键字：not null

3.多表约束

外键

在数据库设计时，表与表之间可能会存在数据的引用关系。

如果一张表A中数据要引用另一张表B中的数据，此时我们称表B是主表，表A是从表。

外键就是用于约束我们表A中引用的列的数据。保证我们A中引用的B的数据必须是存在的，用于确保数据库的完整性。

约束：从表中引用的数据在主表中必须存在。主表中的数据如果被引用了，不能删除修改。

关键字：

[constraint 外键名] foreign key (从表中外键列的列名) references 主表表名(主表的主键名称)

alter table 表名 add  [constraint 外键名] foreign key (从表中外键列的列名) references 主表表名(主表的主键名称)

create table orderlist(
	id int primary key auto_increment,
	number int ,
	uid int ,
	constraint fk1 foreign key(uid) references user(id)
);
1
2
3
4
5
6
7
8
9
10

外键名设置可以省略，但推荐都设置上，方便后期维护

二.表的关系及设计原则

1.一对一

建表原则：在任意一方建立外键指向另一方的主键，同时外键列要设置为唯一约束。也就是说要建立唯一外键。

通常情况下，一对一的关系我们可以直接把两张表合并为一张表。

2.一对多

建表原则：在多的一方添加外键指向一的一方的主键。

3.多对多

建表原则：要创建一张中间表，中间表必须至少包含两个列，分别作为原来两张表的外键指向原来两张表的主键。通常情况下，我们不会给中间表单独设置一个新的主键，而是把两个外键列，作为联合主键使用 。

create table stu_cou(
	sid int,
	cid int,
	constraint fk6 foreign key(sid) references student(id),
	constraint fk7 foreign key(cid) references course(id)
);
1
2
3
4
5
6

三.多表查询

1.连接查询

交叉连接

cross join

select * from user,orderlist;

select * from user cross join orderlist;
1
2
3

交叉连接查询的是所有已列出表的数据的笛卡尔积。

笛卡尔积：两个集合的笛卡尔积就是他们所有数据的全排列。最后结果的条数就是两个表中数量的乘积。

笛卡尔积在查询时如果出现，一般意味着我们查询的条件没有添加充足。

有时候我们会凭空创造一些数据的组合，需要使用到交叉连接。

内连接

把多张表连接到一起，通过指定条件，把交叉连接的结果进行进一步过滤，得到最终的数据。

显示内连接

标准语法：
		SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件  [INNER] JOIN 表名3  on   关联条件...
		
-- 查询用户信息和对应的订单信息，起别名
select 
u.id,u.age,o.number,uid
from 
user u
inner join 
orderlist o 
on 
u.id=o.`id`;
1
2
3
4
5
6
7
8
9
10
11
12

隐式内连接

标准语法：
		SELECT 列名 FROM 表名1,表名2,表3 WHERE 关联条件 and 其他条件;
-- 查询用户姓名，年龄。和订单编号
select name,age,number
from user,orderlist
where USER.`id`=orderlist.`id`;
1
2
3
4
5
6

显式内连接有明确的inner join 关键字的编写，通过on指定条件。隐式内连接隐藏了关键字。

实质上显式和隐式没有任何区别，只是编写方便程度不同而已。

只取满足条件的数据，相当于取多张表的交集部分。

外连接

关键字左边指定的表叫做“左表”，关键字右边指定的表叫做“右表”

关键字：left join或者right join

左外连接

查询时，要查出左表的所有数据和两张表的交集部分。

/*
	左外连接
	标准语法：
		SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有用户信息，以及用户对应的订单信息
select 
u.*,o.`number`
from 
user u
left outer join
orderlist o
on
u.`id`=o.`uid`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

右外连接

查询时，要查出右表的所有数据和两张表的交集部分。

/*
	右外连接
	标准语法：
		SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有订单信息，以及订单所属的用户信息
select 
o.*,u.`NAME`
from
user u
right outer join
orderlist o
on o.`uid`=u.`id`;
1
2
3
4
5
6
7
8
9
10
11
12
13

连接查询的套路：

​ 先找from子句后的表有哪些，从结果中看属于哪张表的哪一列

​ 然后写where子句或者on子句，把表的关联关系（主外键的条件）写入。

​ 如果有其他的限制条件，添加其他条件。

​ 最后指定select子句中的列的列表。

2.子查询

sql是非过程性语言，只能使用sql嵌套完成使用别的sql的结果的操作。

where子句子查询

​ 把子句的结果当成一个值或者一个集合来使用。配合where指定条件用。

*/
-- 查询年龄最高的用户姓名
select max(age) from user;
select name,age from user where age=(select max(age) from user);

/*
	结果是多行单列的
	标准语法：
*/
-- 查询张三和李四的订单信息
select id from user where name in('张三','李四');
select * from orderlist where uid in (select id from user where name in('张三','李四'));
1
2
3
4
5
6
7
8
9
10
11
12

select 子句子查询

​ 把子句嵌套在select中，使用外面的结果当成内部子句的条件使用。

例如：

select id,number,(select name from user where id = uid) name from orderlist;-- 查询所有订单信息，同时把订单中的uid转换为用户名称
1

from子句子查询

​ 把子句的结果当成一张表使用。配合from指定表。

/*
	结果是多行多列的
	标准语法：
		SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
*/
-- 查询订单表中id大于4的订单信息和所属用户信息
select 
*
from orderlist
where id>4;

select * 
from
user u,
(select * from orderlist where id>4 ) o
where u.`id`=o.uid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

where子句子查询和from子句子查询中的子句都可以单独执行，所以编写时先写子句。

select子句子查询的子句不能单独执行，要后写子句。

3.自关联

把一张表看成若干张表，自己跟自己有一些外键关系。进行内外连接查询。

使用自查询也能达到相同的效果：

例如：查询所有员工及其上级的名称

select id,name,(select name from employee e1 where e1.id = e2.mgr) 上级 from employee e2;

-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析
	员工信息 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表有交集部分数据，左外连接
*/
select 
e1.`NAME`,e2.`NAME`
from 
employee e1
left outer join
employee e2
on
e1.`mgr`=e2.`id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

二.视图

概念

把一条比较复杂的查询语句，缓存到数据库中，起个名字，以后可以直接把这个查询的结果当成一张表使用。这个虚拟的表我们称为视图。

视图本质就是一条sql查询的语句。

我们使用时，把视图当成一张表看待就可以了。

作用

1. 简化开发，把复杂语句缓存起来使用。
2. 保护一部分数据，可以把视图做成只查询一张表的某些列，给部分用户只开放查询视图的权限而不开放查询表的权限。

语法：

​ create view 视图名 as 查询语句。

一般在企业开发时，会禁止修改视图数据。

Day03-mysql高级

---

一.备份和恢复

命令行

在命令行中，输入：

mysqldump -u用户名 -p 数据库名 > 文件路径   # 把指定数据库的内容（包含结构和数据）输出到指定文件中
1

还原需要在mysql环境下，输入指令：

source 文件名  # 会把文件中的所有sql语句执行一遍
1

二.存储过程和存储函数

存储过程和存储函数就是一组sql语句的集合，我们可以把这组sql封装起来使用。提升代码的复用性，同时降低逻辑在Java层面执行，减轻对Java服务器的压力。

存储过程的参数

存储过程本身是没有返回值的。如果要让存储过程返回数据，只能通过输出型的参数变相实现。

输出型参数，可以让我们从外界传入一个参数，在存储过程内部进行赋值，然后再外面使用的过程。

@info 一个@符号后跟一个任意变量名，相当于在会话的范围内使用该变量。如果之前没有定义过这个变量，就会创建新的变量。

@@param 两个@@符号后跟一个任意变量名，是在全局范围内声明变量。

存储函数由于一定有一个返回值，所以存储函数本身可以当成一个值使用。可以应用在select、insert、update、delete语句中。

三.触发器

触发器能够关心数据库中的某些特定操作，当这些操作发生时，执行一段提前设定好的逻辑。

触发器关心的操作包括：insert、delete及update。

触发器可以设置为前置触发器或者后置触发器。前置代表在实际操作生效之前先执行触发器，可以修改真正生效的数据。后置代表在操作完成之后执行触发器，不能修改生效的数据。

now() 获取当前服务器时间

concat() 可以指定任意多个参数，表示把这些参数进行字符串的拼接。

new关键字代表本次触发器触发时新的一行记录。

old关键字代表本次触发器触发时旧的一行记录。

四.事务

把一组sql语句聚合到一起看成一个整体，这个整体在执行时要么同时成功，要么同时失败。失败会进行回滚，恢复到执行第一条语句之前的状态，从而保证数据的完整性的一致性。这种机制叫做事务管理。

sql语法

1.开启事务

start transaction;
1

事务的开启要放到所有sql语句执行之前进行。开启事务之后，当前就处于一个事务管理范围中，可以在这个事务中执行所有的sql语句。但是事务中执行的语句并不会真正影响数据库中的数据，都是处于一个临时状态。

2.提交事务

commit;
1

如果事务执行过程中没有任何问题，我们可以提交事务。此时在事务中执行的所有临时的效果会持久化到数据库文件中。操作就真正生效了。事务一旦commit之后，就宣告结束。

3.回滚事务

rollback;
1

如果事务执行过程中出现了问题，我们可以回滚事务。此时事务中操作的所有临时效果会被放弃掉。数据库会恢复到开启事务的时候的状态。事务一旦rollback之后，就宣告结束。

一个事务只能被提交或者回滚一次。

如果在执行事务的过程中程序异常中断了，会话结束，事务会自动回滚。

事务提交方式

1.自动提交

MySQL数据库默认是自动提交事务的：当我们执行一条sql时，如果没有开启事务，MySQL会自动开启一个事务。然后执行这条sql，执行完成后，MySQL会自动提交该事务。

即使MySQL是自动提交，但是如果我们手动开启了一个事务，那么该事务也得手动提交。

2.手动提交

Oracle数据库默认是手动提交事务的。

手动提交：事务开启之后，并不会自动提交，需要显式的编写commit语句进行提交。或者rollback回滚。

事务的四大特性

原子性：强调的是事务的不可分割。要么同时成功，要么同时失败。

一致性：数据库在事务操作前后，总是从一个一致性状态转变为另一个一致性状态。

隔离性：强调的是事务并发的时候，多个事务之间的互相影响。

持久性：当事务结束后，无论是提交还是回滚，对数据库数据影响就会持久化。

隔离性

隔离级别和不同隔离级别下产生的问题，都是在事务并发的时候产生的。

隔离级别

有四种隔离级别，分别是读未提交（Read uncommitted），读已提交（Read committed），可重复读（Repeatable read），可串行化（Serializable），用来解决数据库操作中产生的各种问题。

一、读未提交（Read uncommitted）
在这种隔离级别下，所有事务能够读取其他事务未提交的数据。读取其他事务未提交的数据，会造成脏读。因此在该种隔离级别下，不能解决脏读、不可重复读和幻读。

读未提交可能会产生脏读的现象，那么怎么解决脏读呢？那就是使用读已提交。

二、读已提交（Read committed）
在这种隔离级别下，所有事务只能读取其他事务已经提交的内容。能够彻底解决脏读的现象。但在这种隔离级别下，会出现一个事务的前后多次的查询中却返回了不同内容的数据的现象，也就是出现了不可重复读。

注意: 这是大多数数据库系统默认的隔离级别，例如Oracle和SQL Server，但mysql不是。

已提交可能会产生不可重复读的现象，我们可以使用可重复读。

三、可重复读（Repeatable read）
在这种隔离级别下，所有事务前后多次的读取到的数据内容是不变的。也就是某个事务在执行的过程中，不允许其他事务进行update操作，但允许其他事务进行add操作，造成某个事务前后多次读取到的数据总量不一致的现象，从而产生幻读。

注意: 这才是mysql的默认事务隔离级别

可重复读依然会产生幻读的现象，此时我们可以使用串行化来解决。

四、可串行化（Serializable）
在这种隔离级别下，所有的事务顺序执行，所以他们之间不存在冲突，从而能有效地解决脏读、不可重复读和幻读的现象。但是安全和效率不能兼得，这样事务隔离级别，会导致大量的操作超时和锁竞争，从而大大降低数据库的性能，一般不使用这样事务隔离级别。

下面用一张图来表示他们能够解决的问题
隔离级别

下面解释一下脏读幻读和不可重复读

【1】脏读（读取未提交数据）

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读。

这种情况常发生于转账与取款操作中

【2】不可重复读（前后多次读取，数据内容不一致,主要是修改数据）
事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

【3】幻读（前后多次读取，数据总量不一致,主要是添加时和删除时）
事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

Day04 Mysql_高级

一.数据库存储引擎

存储引擎主要用于管理数据如何存储和如何操作。

InnoDB：支持事务和外键和行级锁，效率略低，占用磁盘空间比较大。最通用的引擎，支持事务和外键，能保证并发时的数据安全和数据完整性。

MyISAM：不支持事务和外键，效率非常高。在对事务要求不高，几乎没有并发时候使用该引擎。

二.索引

1.分类

普通索引：最基本的索引，没有任何限制。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
主键索引：一种特殊的唯一索引，不允许有空值。在建表时有主键列同时创建主键索引。
联合索引：顾名思义，就是将单列索引进行组合。

2.数据结构

在MySQL中，默认的索引数据结构是B+Tree。

B-Tree：使用多叉树把节点进行拆分。让我们每次查询都能去除大部分的无效数据，从而更快定位目标记录。但是BTree查询时，除了看索引id之外，还会查询每个节点的数据值，略微加大我们查询的阻力。

B+Tree：使用多叉树把节点进行拆分。只不过在BTree的基础上进行了优化，除了叶子节点之外，其余的节点不存储数据，只存储索引id值，这样节省查询时间。而且，叶子节点会按照顺序进行连接，形成一种循环链表的形式。便于我们范围查询。

3.语法

-- 为student表中的name列创建一个普通索引
CREATE INDEX idx_name ON student(name); 

-- 为student表中的name和age列创建一个组合索引
CREATE INDEX idx_name ON student(name,age); 
1
2
3
4
5

4.索引的使用原则

1. 对于数据量比较大且查询多的表建立索引。
2. 数据区分度越高，建立索引的命中精确度越高。尽量能建立唯一索引。
3. 在常用的查询条件的列上建立索引。
4. 不要滥用索引，仅建立必要的索引即可。
5. 在where条件不要使用null和>以及or会使索引失效
6. 索引在精确查询和and查询时生效，like模糊查询和or查询无法命中索引。
7. 组合索引使用时，有最左匹配原则。

三.索引

分类

共享锁：同一个数据可以添加多个共享锁，共享锁锁住的数据禁止修改，但是可以并发查询。

​ 在InnoDB引擎中，共享锁如果加在带索引的列上，则是行级锁。如果不带索引，则锁表。

排他锁：同一个数据只能有一个事务进行操作，包括读取（如果是不加锁的普通查询，是允许操作的）和写入。

​ 在InnoDB引擎中，排它锁不允许其他事务和它同时加锁（包括共享锁和排它锁）。

表锁：一次性把整张表锁住，其他事务无法操作这个表中的任意数据。

行锁：一次性锁住某行数据，其他事务可以操作当前表中其他行数据。

MyISAM中读写锁

读锁：所有人（包括自己）只能查询，不能修改数据。

写锁：其他人不能查询和修改，只有锁的持有者才有权利修改数据和查询数据