MYSQL【持续更新中】
文章目录
跳转链接(学习路线)及前言(更新中)
java
↓
mysql
↓
jdbc
↓
javaweb
↓
html
css
javascript
javascriptDOM操作
vue
react
基础部分、供复习和平日查询使用,快速复习!!!
内容有误,可以评论区指出,随时改正
此处入门过sql的可以忽略不看
注释
-- 这是单行注释
# 这也是单行注释
/*
这是多行注释
注释*/
mysql不区分大小写
另外的关于数据库的作用和定义自己百度或者找视频
语法分类
DDL:
定义数据库、表、字段
DML:
增删改,数据操作
DQL:
查询数据
DCL:
创建用户,管理用户权限
定义数据库、表、字段DDL
数据库操作
查询数据库
show databases; #查询所有数据库
select database(); # 查询当前数据库
创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
#此处的 if not exists 加上则代表 “ 如果没有该数据库则创建,如果有该数据库则不操作 ”
#此处的 default charset 加上则代表设置数据库默认的字符集格式,可以设置为utf8mb4(该字符集支持四字节字符,默认的utf8只支持三字节字符)
删除数据库
drop database [if exists] 数据库名;
#此处的 if not exists 加上则代表 “ 如果没有该数据库则删除,如果有该数据库则不操作 ”
使用某个数据库
use 数据库名;
表操作
查询当前数据库所有表
show tables;
查询指定表结构
desc 表名;
查询指定表的建表语句
show create table 表名;
创建表
create table 表名(
字段名称 字段类型 [comment 注释],
字段名称1 字段1类型 [comment 字段1注释],
字段名称2 字段2类型 [comment 字段2注释],
...
字段名称n 字段n类型 [comment 注释n]
)[comment 表注释内容];
注意最后一个字段结束没有逗号
数据类型(了解)
- 数值类型
类型 | 大小 | 有符号范围 | 无符号范围(在类型关键字后加unsigned) | 描述 |
---|---|---|---|---|
tinyint | 1byte | (-128,127) | (0,255) | 小整数值 |
smallint | 2bytes | (-32768,32767) | (0,65535) | 大整数值 |
mediumint | 3bytes | (-8388608,8388607) | (0,16777215) | 大整数值 |
int或integer | 4bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
bigint | 8bytes | (-263,263-1) | (0,2^64-1) | 极大整数值 |
float | 4bytes | (-3.402823466 E+38, 3.402823466351 E+38) | 0 和(1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 |
double | 8bytes | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0 和(2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数值 |
decimal | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
2.字符串类型
类型 | 大小 | 描述 |
---|---|---|
char | 0-255bytes | 定长字符串 |
varchar | 0-65535bytes | 变长字符串 |
tinyblob | 0-255bytes | 不超过255个字符的二进制数据 |
tinytext | 0-255bytes | 短文本字符串 |
blob | 0-65535bytes | 二进制形式的长文本数据 |
text | 0-65535bytes | 长文本数据 |
mediumblob | 0-16777215bytes | 二进制形式的中等长度文本数据 |
mediumtext | 0-16777215bytes | 中等长度文本数据 |
longblob | 0-429967295bytes | 二进制形式的极大文本数据 |
longtext | 0-429967295bytes | 极大文本数据 |
char定长字符,如果定义十个字符,即使存储一个字符,也会占用十个字符的空间,未占用的空间使用空格补位
varchar变长字符,一个字符则占用一个字符的空间,无论定义多长,根据内容计算占用空间
所以char性能会优于varchar
3.日期时间类型
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
date | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
time | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
year | 1 | 1901至2155 | YYYY | 年份值 |
datetime | 8 | 1000-01-01 00:00:00 至 9999-12-31 23::59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
timestamp | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
修改表
添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
修改字段
# 修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
# 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
删除字段
alter table 表名 drop 字段名;
修改表名
alter table 表名 rename to 新表名;
删除表
# 删除表 注意该操作会删除表的数据
drop table [if exists] 表名;
# 删除指定表,并重新创建该表 注意该操作也会删除表的数据
truncate table 表名;
图形化工具(了解)
sqlyog
navicat
idea自带的datagrip
增删改数据操作DML
增
# 给指定字段添加数据
insert into 表名 (字段名1,字段名2,...) values (值1,值2,...);
# 给全部字段添加数据
insert into 表名 values(值1,值2,...);
# 批量添加
insert into 表名 (字段名1,字段名2,...) values (值1,值2,...),(值1,值2,...),(值1,值2,...)...;
insert into 表名 values (值1,值2,...),(值1,值2,...),(值1,值2,...)...;
注意:插入数据时,指定的字段顺序是对应的,字符串和日期类型数据应该包在引号中,插入的数据大小,也应该在字
段类型的范围内。
改
update 表名 set 字段名1 = 值1 ,字段名2 = 值2,...[where 条件];
# 此处的条件满足后修改对应的字段数据,如果没有该条件,则修改整条字段的数据
删
delete from 表名 [where 条件];
# 此处的条件满足后删除对应的字段数据,如果没有该条件,则删除整条字段的数据
# 不能删除某个字段的值,如果需要这么操作,可以使用update将该字段设置为null
查询数据DQL
总览
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
基础查询
# 查询指定字段
select 字段1,字段2,字段3,...from 表名;
# 查询所有字段
select * from 表名;
# 设置别名
select 字段1 [as 别名1],字段2 [as 别名2],字段3 [as 别名3] ... from 表名;
#去除重复记录
select distinct 字段列表 from 表名;
条件查询
# 条件可以有多个
select 字段列表 from 表名 where 条件列表 ;
具体的条件说明
符号 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between … and … | 在某个范围之内(含最小、最大值) |
in(…) | 在in之后的列表中的值,多旋翼 |
like 占位符 | 模糊匹配( “ _ ” 匹配单个字符, “ % ” 匹配任意个字符) |
is null | 为null |
and 或 && | 并且(多个条件同时成立) |
or 或 || | 或者(多个条件任意一个成立) |
not 或 ! | 非,不是 |
聚合函数
函数名 | 作用 |
---|---|
count | 统计数量(不计算null值) |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
select 聚合函数 (字段列表) from 表名;
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
where 与 having 的区别?
where是分组之前进行过滤,不满足where条件,不参与分组:而having是分组之后对结果进行过滤。
where不能对聚合函数进行判断,而having可以。
having是分组聚合之后进行过滤的
案例:
求男女数量
select gender,count(*) from emp group by gender;
求男女年龄平均值
select gender,avg(age) from emp group by gender;
进阶案例:查询年龄小于50,并根据工作地址分组,然后获取员工地址重复次数(住址相同)> 3 的员工
select address,count(*) from emp where age < 45 group by address having count(*) >= 3;
注意:分组之后,查询字段一般为聚合函数和分组字段,查询其他的字段没有意义
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
#排序方式 1、ASC升序(默认排序方式) 2、DESC降序
排序查询支持多字段排序,也就是说,如果这个字段碰到相同内容的值,那么可以使用第二个字段再进行排序
分页查询
select 字段列表 from 表名 limit 起始索引,每页查询的记录数;
# 如果查询第一页,可以如下省略
select 字段列表 from 表名 limit 10;
执行顺序
select
字段列表 # 5 决定返回的字段
from
表名列表 # 1 决定查询表
where
条件列表 # 2 查询条件
group by
分组字段列表 # 3分组
having
分组后条件列表 # 4 分组后条件
order by
排序字段列表 # 6 升序和排序
limit
分页参数 # 分页查询
用户权限管理DCL
用户管理
# 查询用户
use mysql;
# 该表存储用户的信息
select * from user;
# 创建用户
create user '用户名'@'主机名' identified by '密码';
# 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
# 删除用户
drop user '用户名'@'主机名';
删除创建用户时需要主机地址和用户名才可以定位具体的y用户
这里的主机名可以控制访问权限,内容为ip,设置为localhost则代表只能本机访问,不能远程访问
权限管理
权限 | 说明 |
---|---|
all,all privileges | 所有权限 |
select | 查询数据 |
insert | 插入数据 |
update | 修改数据 |
delete | 删除数据 |
alter | 修改表 |
drop | 删除数据库/表/视图 |
create | 创建数据库/ 表 |
# 查询权限
show grants for '用户名'@'主机名';
# 授予权限(支持通配符 “ * ” )
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
# 撤销权限(支持通配符 “ * ” )
revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';
函数
字符串函数
函数名 | 功能 |
---|---|
concat(Str1,Str2,…Strn) | 字符串拼接,将Str1,Str2,…Strn拼接成一个字符串 |
lower(Str) | 将字符串Str全部转换为小写 |
upper(Str) | 将字符串Str全部转为大写 |
lpad(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
rpad(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
trim(str) | 去掉字符串头部和尾部的空格 |
substring(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
案例:批量补位
update emp no = lpad(no,5,'0');
数值函数
函数名 | 功能 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x,y) | 返回x/y的模 |
rand() | 返回0~1内的随机数 |
round(x,y) | 求参数x的四舍五入的值,保留y位小数 |
案例
# 这样会出现五位数,那么咱们可以先将小数点保留6位
select round(rand()*1000000,0);
select substring(rand(),3,6);
日期函数
函数名 | 功能 |
---|---|
curdate() | 返回当前日期 |
curtime() | 返回当前时间 |
now() | 返回当前日期和时间 |
year(date) | 获取指定date的年份 |
month(date) | 获取指定date的月份 |
day(date) | 获取指定date的日期 |
date_add(date,interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
datediff(date1,date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
案例:
往后推100年的时间
select date_add(now(),interval 100 year);
算时间间隔天数
select datediff('2024-2-16','2024-2-23');
流程函数
函数名 | 功能 |
---|---|
if(value,t,f) | 如果value为true,则番薯t,否则返回f |
ifnull(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
case when [val1] then [res1] … else [default] end | 如果val1为true,返回res1,… 否则返回default默认值 |
case [expr] when [val1] then [res1] … else [default] end | 如果expr的值等于val1,返回res1,… 否则返回deault默认值 |
案例:
查询中奖信息,如果数字为666则代表中奖(一等奖),777则代表二等奖以此类推,其他为遗憾没中奖
select name,(case id when '666' then '一等奖' when '777' then '二等奖' ...) as '中奖信息' from emp;
约束
总览
- 非空约束:限制该字段的数据不能为null( NOT NULL )
- 唯一约束:保证该字段的所有数据都是唯一、不重复的( UNIOUE )
- 主键约束:主键是一行数据的唯一标识,要求非空且唯一( PRIMARY KEY)
- 默认约束:保存数据时,如果未指定该字段的值,则采用默认值( DEFAULT)
- 检查约束(8.0.16版本之后):保证字段值满足某一个条件( CHECK)
- 外键约束:用来让两张表的数据之间建立连接,保证数据的一致性和完整性( FOREIGN KEY)
create table user{
id int primary key auto_incrment comment '主键约束+自增长',
name varchar(10) not null unique comment '非空约束+唯一约束',
age int cheak(age > 0 && age <= 120) comment '检查约束',
status char(1) default "1" comment "默认约束",
gender char(1) comment '没约束'
} comment '约束概览';
# 外键约束需要多表查询
外键约束
外键的定义
id此时是部门表的主键(也称之为主表或父表)
deptid此时是员工表的外键(也称之为从表或子表)
员工表的deptid关联着部门表的主键
在没有加入外键约束时,两张表没有任何关联,也不能确保数据一致和完整性
接下来讲怎么实现关联
# 添加外键
create table 表名 {
字段名 数值类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
};
# 已有表中添加外键关系
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
# 删除外键关系
alter table 表名 drop foreign key 表名;
外键删除 / 更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与RESTRICT一致)。 |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与NOACTON 一致)。 |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null)。 |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(mysql 默认引擎不支持 Innodb)。 |
这里前四种行为里分为两类,前两个为默认行为,代表如果有主键约束的表内字段,不允许修改和删除。
第三个和第四个为,代表如果有主键约束的表内字段,允许更新和删除,但也删除/更新外键在子表中的记录。
alter table 表名 add constraint 外键名称 FOREIGNKEY (外键字段) references 主表名(主表字段名) on update cascade on delete CASCADE;
多表查询
多表关系
多表关系大致分为三类
1、一对多(多对一)
类似员工表,一个部门对应很多员工,一个员工对应一个部门
在多的一方建立外键,指向一的一方的主键
这种一对多的关系在外键约束内说过,这里不再赘述
2、多对多
类似学生和课程
建立中间表,中间表至少包括两个外键,分别关联两方主键
中间表字段设置主键id、学生id、课程id,学生id和课程id设置为外键
3、一对一
类似用户与用户详情的关系
一对一的多表关系类似一对多的设计方式,
将基础信息放到一个表中,详情信息放到另一个表中,以提升操作效率。
在经常查询的表中设置主键,另外一个详情不经常使用的表中设置外键,此时还不能确保这种一对一的关系,需要在外键对应字段设置唯一约束
多表查询概述
语法
# 多表查询的本质语句是
select * from 表1,表2;
# 但是这种查询出来的结果是两个表的组合集,需要消除不对应的
select * from 表1,表2 where 表1.id = 表2.category_id;
连接查询
内连接相当于查询A、B两个表的交集部分
外连接:
左外连接相当于查询A表所有数据,以及AB两表的交集部分
右外连接相当于查询A表所有数据,以及AB两表的交集部分
自连接表示当前表与自身连接查询,自连接必须使用表别名
具体内容下面开始啦!
内连接
上方我们多表查询的这种方式就是内连接,也就是查询AB两个表的交集部分
- 隐式内连接
select 字段列表 from 表1,表2 where 条件 ...;
# 别名的方式
select e.name,d.name from emp e ,dept d where e.dept_id = d.id;
多表查询中,经常会给表起别名,来简化写法。
起了别名,就不能再使用原来的表名来操作了,必须使用别名。
- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
# 举例
select e.name, d.name from emp e inner join dept d on e.dept id = d.id;
# 可以省略inner
select e.name, d.name from emp e join dept d on e.dept id = d.id;
外连接
# 左外
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
# 右外
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
# outer 可以省略
案例
如果有一些员工,是刚入职的,并没有绑定部门信息,那么使用内连接是查不到这些人信息的,所以要考虑左外或右外连接。
select e.*, d.name from emp e left outer join dept d on e.dept id = d.id;
自连接
就是将一个表分为两个别名来查询
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
# 自连接查询,可以是内连接查询,也可以是外连接查询。
自连接查询,可以是内连接查询,也可以是外连接查询。
案例:
员工表内,有领导的id,这个id代表该员工的领导,那么我们想查看员工对应的领导,应该怎么查看?
select a.name, b.name from emp a, emp b where a.managerid = b.id;
# 注意自连接必须起别名
再假设有些员工没有领导,比如老板,那么也想将其查询的办法?
# 这里需要在自连接中,使用外连接
# 需要将emp这张员工表分为两张表来看待
select a.name '员工', b.name '领导'from emp a left join emp b on a.managerid = b.id;
联合查询
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
案例
查询薪资低于4000的员工,和年龄大于50的员工。
select * from emp where salary < 4000
union all
select * from emp where age > 50;
#去掉all,可以将最后结果去重
select * from emp where salary < 4000
union
select * from emp where age > 50;
注意,两条语句要有相同的列数,而且字段类型保持一致
子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询
select * from t1 where column1 = (select column1 from t2);
子查询外部的语句可以是 insert / update / delete / select 的任何一个。
根据子查询位置,分为:where之后、from之后、select之后出现的子查询
- 标量子查询(结果为单个值,一行一列)
该子查询返回的结果是单个值(数字、字符串、日期等),是最简单的形式,这种子查询成为标量子查询。
常用操作符号:= 、 <>(不等于) 、 > 、 >= 、 < 、 <=
案例
查询 “ 研发部 ” 的所有员工信息
select id from dept where name = '研发部'; # 假设查询结果为2
select * from emp where deptid = 2;
# 子查询方式
select * from emp where deptid = (select id from dept where name = '研发部');
查询 “ 张三 ” 入职之后的员工 (查询比张三晚入职的员工)
select entydate from emp where name = '张三'; # 假设查询结果为2024-02-16
select * from emp where entydate > 2024-02-16;
# 子查询方式
select * from emp where entydate > (select entydate from emp where name = '张三');
- 列子查询(结果为一列)
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN、NOT IN、ANY、SOME、ALL
操作符 | 说明 |
---|---|
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定的集合范围之内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有值都必须满足 |
案例
查询 “ 销售部 ” 和 “ 市场部 ” 的所有员工信息
select id from dept where name = '销售部' or name = '市场部'; # 假设查询结果为2,4
select * from emp where deptid in (2,4);
# 列子查询方式
select * from emp where deptid in (select id from dept where name = '销售部' or name = '市场部');
# 查询后的值不再是单个值,而是一列多行,所以称之为列子查询方式
查询比财务部所有人工资都高的员工信息
select id from dept where name ='财务部';
select salary from emp where dept id = (select id from dept where name ='财务部');
select * from emp where salary > all ( select salary from emp where dept id =(select id from dept where name ='财务部') );
- 行子查询(结果为一行)
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:=、IN、NOT IN
案例
查询与 “ 张三 ” 的薪资及直属领导相同的员工信息
select salary,managerid from emp where name = '张三 ';
select * from emp where (salary,managerid) = (12500,1);
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张三 ');
事物
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
操作
实现
查询张三余额
select * from account where name = '张三';
张三余额减1000
update account set money = money - 1000 where name = '张三';
李四余额加1000
update account set money = money + 1000 where name = '李四';
操作
查看事务提交方式
select @@autocommit;
设置事务提交方式
set @@autocommit = 0; # 设置为手动提交
set @@autocommit = 1; # 设置为自动提交
提交事物
commit;
回滚事物
rollback;
开启事物
start transaction;
第一种方式:可以通过关闭事务自动提交,使用commit手动提交和rollback来回滚事物
第二种方式:通过指令start transaction;或者开启自动提交事物,如果执行过程出错,那么会自动回滚事物,如果执行过程没问题,那么会自动提交事物
四大特性ACID
原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事物问题和隔离级别
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 幻影”。 |
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted(读取未提交) | √ | √ | √ |
Read committed(读取已提交) | × | √ | √ |
Repeatable Read(默认,可重复读取) | × | × | √ |
Serializable(串行化) | × | × | × |
查看事务隔离级别
select @@transaction_isolation;
设置事务隔离级别
set [session|global] transaction isolation level { read uncommitted | read committed | repeatable read | serializable }
session:会话级别,只对当前客户端窗口有效
global:会话级别,针对所有客户端有效
演示
开启两个命令界面,咱们分别叫A界面和B界面,对应的事务为A事务和B事务,登录数据库
user表内容
首先测试Read uncommitted
将数据库提交事务设置为手动(这里非常关键,否则无法用两个窗口来模拟串行测试)
set autocommit = 0;
将数据库隔离级别设置为Read uncommitted
set session transaction isolation level read uncommitted;
// 翻译:设置会话事务隔离级别读取提交
脏读
开启AB两个界面的事务
start transaction;
查询A界面,user表内容
修改B界面,user表内容
update user set salary = salary - 1000 where account = "王五";
最后查询A界面user表的内容,我们会发现,在B界面没有提交事物的情况下,能看到A界面内的查询结果已经发生改变,这就是脏读
将事务提交,为后续测试做准备
commit;
解决脏读
Read committed解决了脏读问题
将数据库隔离级别设置为Read committed
set session transaction isolation level read committed;
// 翻译:设置会话事务隔离级别读取提交
开启AB两个界面的事务
start transaction;
A界面查询user表
select * from user;
B界面对王五加1000元的工资
update user set salary = salary + 1000 where account = "王五";
在B界面没有提交事物的情况下,A界面是查询不到数据的(这才不是脏读)
B界面提交事务后,就可以查询到更改后的数据了
将事务提交,为后续测试做准备
commit;
不可重复读
不可重复读实际上实在脏读的情况之上,当不发生脏读时,B界面未提交的事务,A界面查询时是查不到更新后的数据的。
并且在同一时刻,B界面又进行了提交,那么A界面的数据查询又发生了改变,这种就是不可重复读。(好好理解下,理解不了咱们上案例)
AB界面开启事务(注意当前数据库的隔离级别还是read committed)
start transaction
A界面查询user表信息
select * from user;
B界面更改李四数据,为salary增加2000
update user set salary = salary - 2000 where account = "李四";
此时B界面事务未提交,A界面查询,会查询不到更新后的数据,salary还是1000,对吧?
此时我们提交B界面事务,A界面再次查询数据,发现发生了改变,这种问题就称之为不可重复读,同一时刻,读取的结果不一样
Read committed解决了脏读问题,但是会出现不可重复读的问题
将事务提交,为后续测试做准备
commit;
解决不可重复读
将隔离级别再上升一个级别repeatable read
set session transaction isolation level repeatable read;
这里再照着上方的内容做测试即可,是可以解决问题的,我们继续讲幻读
幻读
演示幻读
1、AB界面开启事务
2、A界面查询id为3的数据,会显示没有
3、B界面插入一条id为3的数据
insert into user(id,account,password,salary) values (3,"杜甫","123456","5000");
这里我们直接将事务提交,因为不提交的话,后续A界面添加数据会卡住,直至超出响应时间。
commit;
4、A界面再次查询id为3的数据,会发现我们明明插入了,但是还是没有(因为B界面的事务没有提交)。
5、我们用A界面添加一个id为3的数据,会发现出现主键重复的报错
insert into user(id,account,password,salary) values(3,"李白","12312","5000");
A界面再查还是不会出现结果的,这种情况就是幻读
解决幻读
解决幻读的方法是,继续提升隔离级别至serializable
set session transaction isolation level serializable;
AB两个界面开启事务
start transaction;
A界面查询user表中id为4的数据,是没有的
select * from user where id = 4;
B界面插入一条id为4的数据,然后你会发现直接执行不了了。只有等待左侧事务提交完,这边才能执行。
该参数为serializable,中文翻译为串行化,代表这并发操作时,只能操作一个,所以性能也是最低的!
事务隔离级别越高,代表着数据越安全,那么也代表着性能越低
完结啦!后续我还会 再看几遍这篇文章,将一些错误和难以理解的知识点进行补充和修改。
道阻且长,行则将至!
进阶内容
进阶内容我会拿出新的一篇文章来写,最近这段时间我先把jdbc和servlet更新完,稍等几天哦!
2024年3月2日15:57:41