Skip to content

mysql深入

【重点】SQL约束

对表中的数据进行进一步的限制,保证数据的正确性有效性完整性

约束种类:

  • PRIMARY KEY

    主键约束

  • UNIQUE

    唯一约束

  • NOT NULL

    非空约束

  • AUTO_INCREMENT

    自增约束

  • DEFAULT

    默认值

  • FOREIGN KEY

    外键约束

主键约束

用来唯一标识一条记录。

sql
CREATE TABLE tablename (
  column_name datatype PRIMARY KEY
);
sql
ALTER TABLE tablename ADD PRIMARY KEY(column_name)
sql
ALTER TABLE tablename DROP PRIMARY KEY

唯一约束

字段的值不能重复

sql
CREATE TABLE tablename (column_name datatype UNIQUE)
sql
ALTER TABLE tablename ADD UNIQUE(column_name)
sql
ALTER TABLE tablename DROP INDEX index_name

非空约束

这个字段必须设置值,不能是NULL

sql
CREATE TABLE tablename(COLUMN datatype NOT NULL)
sql
ALTER TABLE tablename MODIFY COLUMN column_name datatype NOT NULL

自增约束

每次插入新记录时,数据库自动生成主键字段的值。

sql
CREATE TABLE tablename (column_name datatype PRIMARY KEY AUTO_INCREMENT)
sql
ALTER TABLE tablename MODIFY COLUMN column_name INT(11) NOT NULL [AUTO_INCREMENT]
sql
ALTER TABLE tablename AUTO_INCREMENT = value;

默认约束

往表中添加数据时,如果不指定这个字段的数据,就使用默认值

sql
CREATE TABLE tablename (column_name datatype DEFAULT literal)
sql
ALTER TABLE tablename ALTER COLUMN column_name SET DEFAULT literal
sql
ALTER TABLE tablename ALTER COLUMN column_name DROP DEFAULT

外键约束

sql
CREATE TABLE tablename (..., FOREIGN KEY(column_name) REFERENCES table_name(column_name))
sql
ALTER TABLE tablename ADD FOREIGN KEY(column_name) REFERENCES table_name(column_name)
sql
ALTER TABLE tablename DROP FOREIGN KEY fk_symbol

【重点】表关系设计

在真实的开发中,一个项目中的数据一般都会保存在同一个数据库里,但是不同的数据需要保存在同一个数据库中,不同的数据表中。

在设计保存数据的数据表时,就需要根据具体的数据进行具体的分析,把同一类数据保存在同一张表,不同的数据保存在不同的表。所以不同表中的数据,就必然有一定的关联。这就需要在设计表时,考虑不同表之间的具体关系。

在数据库中,表总共存在三种关系:

  • 一对一关系
  • 一对多关系
  • 多对多关系

一对一

一对多

多对多

E-R图

外键约束(掌握)

创建第三张关系表即中间表,来维护程序员表和项目表之间的关系。

使用中间表的目的是维护两表多对多的关系:

  1. 中间表插入的数据,必须在多对多的主表中存在。

  2. 如果主表的记录在中间表维护了关系,就不能随意删除。如果可以删除,中间表就找不到对应的数据了,这样就没有意义了。

上述是中间表存在的意义,可是我们这里所创建的中间表并没有起到上述的作用,而是存在缺点的:

缺点 1: 我们是可以向中间表插入不存在的项目编号和程序员编号的。

说明:在程序员和项目表中是不存在编号是 30 和 20 的,但是这里依然可以插入不存在的编号。这样做是不可以的,失去了中间表的意义。

缺点 2 : 如果中间表存在程序员的编号, 我们是可以删除程序员表对应的记录的。

在中间表中是存在编号为 1 的程序员的:

可是我们却可以删除程序员表 coder 中的编号为 1 的程序员:

编号为 1 的程序员张三已经被我们删除,但是在中间表 coder_project 中仍然还存在编号为 1 的程序员,这样对于中间表没有意义了。

说明:

创建第三张表的语句:

sql
create table coder_project(
   coder_id int ,--这个外键来自于coder表中的主键
   project_id int--这个外键来自于project表中的主键
);

我们在创建第三张关系表时,表中的每一列,都是在使用其他两张表中的列,这时我们需要对第三张表中的列进行相应的约束。

当前第三张表中的列由于都是引用其他表中的列,我们把第三张表中的这些列称为引用其他表的外键约束。

给某个表中的某一列添加外键约束:

简化语法:

sql
foreign key( 当前表中的列名 )  references 被引用表名(被引用表的列名);

注意:一般在开发中,被引用表的列名都是被引用表中的主键。

举例:

sql
constraint [外键约束名称] foreign key(当前表中的列名) references  被引用表名(被引用表的列名)
举例:constraint coder_project_id foreign key(coder_id) references coder(id);

关键字解释: constraint: 添加约束,可以不写 foreign key(当前表中的列名): 将某个字段作为外键 references 被引用表名(被引用表的列名) : 外键引用主表的主键

给第三张表添加外键约束有两种方式:

第一种方式:给已经存在的表添加外键约束:

sql
-- 来自于程序员表
alter table coder_project add constraint c_id_fk foreign key(coder_id) references coder(id);
-- 来自于项目表
alter table coder_project add constraint p_id_fk foreign key(project_id) references project(id);

第二种方式:创建表时就添加外键约束:

sql
create table coder_project(
    coder_id int,
    project_id int,
    constraint c_id_fk foreign key(coder_id) references coder(id),
    constraint p_id_fk foreign key(project_id) references project(id)
);

了解完如何给第三张表添加外键约束以后,我们就开始给上述创建好的第三张表添加外键约束。

在添加外键约束之前,由于刚才已经修改了表中的数据,所以我们先清空三张表,然后在添加外键约束。

操作步骤:

1、清空上述三张表:

2、增加外键约束:

3、添加完外键约束以后,就会在可视化工具中的架构设计器上查看表之间的关系

先选中表

然后点击右下角:

最后的表关系如下:

3、向三张表中分别插入数据:

sql
-- 插入数据

insert into coder values(null,'张三',12000);
insert into coder values(null,'李四',15000);
insert into coder values(null,'王五',18000);

insert into project values(null,'QQ项目');
insert into project values(null,'微信项目');

insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,1);

4、测试外键约束是否起到作用:

A:执行以下语句:

B:执行以下语句:

再次执行上述语句的时候,发现报错了,而数据库表中的数据都没有改变,说明外键约束起到了作用。

外键的级联(掌握)

在修改和删除主表的主键时,同时更新或删除从表的外键值,称为级联操作 ON UPDATE CASCADE -- 级联更新,主键发生更新时,外键也会更新 ON DELETE CASCADE -- 级联删除,主键发生删除时,外键也会删除

具体操作:

  • 删除三张表 coder、coder_project、project 表
  • 重新创建三张表,添加级联更新和级联删除
sql
-- 创建程序员表
create table coder(
    id int primary key auto_increment,
    name varchar(50),
    salary double
);
-- 创建项目表
create table project(
    id int primary key auto_increment,
    name varchar(50)
);
create table coder_project(
    coder_id int,
    project_id int,
    -- 添加外键约束,并且添加级联更新和级联删除
    constraint c_id_fk foreign key(coder_id) references coder(id) ON UPDATE CASCADE ON DELETE CASCADE,
    constraint p_id_fk foreign key(project_id) references project(id) ON UPDATE CASCADE ON DELETE CASCADE
);

再次添加数据到三张表:

sql
-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);

insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');

insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);

需求1:修改主表coder表的id为3变为4.

需求2:删除主表coder表的id是4的行数据。

小结:

级联更新:ON UPDATE CASCADE 主键修改后,外键也会跟着修改

级联删除:ON DELETE CASCADE 主键删除后,外键对应的数据也会删除

【扩展】范式

概念

好的数据库设计对数据的存储性能和后期的程序开发,都会产生重要的影响。建立科学的,规范的数据库就需要满足一些规则(Normal Form),来优化数据的设计。目前共定义了 6 种范式:

  • 第一范式(1NF)

  • 第二范式(2NF)

  • 第三范式(3NF)

  • 巴斯科德范式(BCNF)

  • 第四范式(4NF)

  • 第五范式(5NF)

在实际应用中,一般只需要达到 3NF。

函数依赖

如果通过 A 属性的值,可以唯一确定 B 属性的值,则称 B 函数依赖于 A。

  • 通过学号,能够确定姓名

  • 通过学号和课程名称,能够确定分数

完全函数依赖

如果 A 是一个属性组,想要唯一确定 B 属性的值,需要依赖 A 属性组中所有的属性值。

通过学号和课程名称,能够确定分数

部分函数依赖

如果 A 是一个属性组,想要唯一确定 B 属性的值,只需要依赖 A 属性组中某一些值即可。

(学号和课程名称组成的属性组中),只需学号,就能够确定姓名。

传递函数依赖

通过 A 属性的值,可以唯一确定 B 属性的值;通过 B 属性的值,可以唯一确定 C 属性的值。则称 C 传递依赖于 A

通过学号,能确定系名;通过系名,能确定系主任

候选码

一个属性或属性组,被其他所有属性所完全依赖,则称这个属性为该表的码。也称为键或关键字。

主属性

包含在任意一个候选码中的属性

非主属性

不包含在任何一个候选码中的属性

第一范式

  • 官方描述

    在关系模式R中,当且仅当所有属性只包含原子值,即每个分量都是不可再分的数据项,则称R满足1NF

  • 指标

    • 原子性
  • 通俗理解

    每一列不可拆分

姓名街道
张三上海市浦东新区航头镇
李四上海市浦东新区周浦镇

第二范式

  • 官方描述

    当且仅当关系模式R满足1NF,且每个非键属性(即不属性任何候选键的属性,也称为非主属性)完全依赖于候选键时,则称R满足2NF。

  • 指标

    • 唯一性
  • 通俗理解

    1. 必须有主键

    2. 每张表只描述一件事

  • 地址表
id街道
1上海市浦东新区航头镇
  • 用户表
id姓名
1张三
2李四

第三范式

  • 官方描述

    当且仅当关系模式R满足1NF,且R中没有非键属性传递依赖于候选键时,则称R满足3NF。

  • 指标

    • 关联性
  • 通俗理解

    从表的外键必须使用主表的主键

  • 用户表
姓名id地址
张三11
李四22

巴斯科德范式

  • 官方描述

    如果关系模式R满足1NF,且R中没有属性传递依赖于候选键时,则称R满足BCNF。

  • 指标

    • 完整性
    • 一致性
  • 通俗理解

    借助中间表,实现多对多关系

第四范式

  • 官方描述

    将关系模式中的多值依赖拆分成单值依赖,从而避免数据冗余

  • 指标

    • 准确性
    • 独立性
  • 通俗理解

    不要有冗余

第五范式

  • 官方描述

    在一个关系型数据库中,任何联接依赖都被消除。

  • 指标

    • 完整性
  • 通俗理解

    也被称为"投影-连接范式",或"完美范式"。

    第五范式的主要目标是确保数据库设计可以支持所有可能的查询。或者说,为了满足第五范式,数据库设计师需要确保数据库的结构可以支持所有的业务需求,而不是简单地添加更多的表和列来存储冗余数据。

    以有限的数据,实现无限的业务

【重点】高级查询

为什么需要有多表操作

准备数据

sql
-- 创建部门表
create table dept(
  id int primary key auto_increment,
  name varchar(20)
);

insert into dept (name) values ('开发部'),('市场部'),('财务部'); 

-- 创建员工表
create table emp (
  id int primary key auto_increment,
  name varchar(10),
  gender char(1),   -- 性别
  salary double,   -- 工资
  join_date date,  -- 入职日期
  dept_id int,
  foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键)
);  

insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);

查询某员工在哪个部门?

sql
-- 只查询一张表不能查询出员工名字和部门名字,需要使用多表操作
select * from emp, dept;

完成多表操作的两种方式:

  1. 表连接
  2. 子查询

笛卡尔积

概念

左表的每条数据和右表的每条数据组合成新的数据

如:查询员工表和部门表,查询的数据如下,结果就是笛卡尔积的数据

sql
select * from emp,dept;

查询某员工所在的部门

部门是左表,员工是右表。

我们发现不是所有的数据组合都是有用的,只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据

sql
-- 1. 查询孙悟空在哪个部门名字
select * from emp;
select * from dept;

-- 2. 查询所有的员工和所有的部门
-- 查询2张表结果是2张表记录的乘积,称为笛卡尔积
select * from emp,dept;

- 如何消除笛卡尔积:条件是从表.外键=主表.主键
select * from emp,dept where emp.dept_id = dept.id;
-- 这就是隐式内连接,使用where,没有用到join...on

-- 给表起别名
select * from emp e ,dept d where e.dept_id = d.id;

-- 查询孙悟空在哪个部门名字
select * from emp e ,dept d where e.dept_id = d.id and e.id=1;

-- 只查询孙悟空的员工名字和部门名字,并指定别名:员工名、部门名
select e.name 员工名,d.name 部门名 from emp e ,dept d where e.dept_id = d.id and e.id=1;

内连接

内连接分类

  1. 隐式内连接

  2. 显示内连接

语法

sql
-- 隐式内连接语法
select 列名 from 左表,右表 where 从表.外键=主表.主键

-- 显示内连接, on后面就是表连接的条件
select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键

应用

查询唐僧的信息,显示员工 id,姓名,性别,工资和所在的部门名称

  1. 确定查询哪些表
  2. 确定表连接条件,员工表.dept_id = 部门表.id 的数据才是有效的
  3. 确定查询条件,我们查询的是唐僧的信息,员工表.name='唐僧'
  4. 确定查询字段,查询唐僧的信息,显示员工 id,姓名,性别,工资和所在的部门名称
  5. 我们发现写表名有点长,可以给表取别名,显示的字段名也使用别名
sql
-- 查询唐僧的信息,显示员工id,姓名,性别,工资和所在的部门名称
-- 1. 确定查询哪些表
select * from emp e inner join dept d;

-- 2. 确定表连接的条件
select * from emp e inner join dept d on e.dept_id = d.id;

-- 3. 如果有其它的查询条件,添加where语句
select * from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';

-- 4. 确定查询哪些列
select e.id 编号, e.name 姓名, e.gender 性别, e.salary 工资, d.name 部门名
 from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';

外连接

外连接分类

  1. 左外连接

    左表中所有的记录都出现在结果中,如果右表没有匹配的记录,使用 NULL 填充

  2. 右外连接

    右表中所有的记录都出现在结果中,如果左表没有对应的记录,使用 NULL 填充

语法

sql
-- 左外连接,左表中所有的记录都出现在结果,如果右表没有匹配的记录,使用NULL填充
select 列名 from 左表 left join 右表 on 从表.外键=主表.主键

-- 右外连接,保证右表中所有的数据都出现,如果左表没有对应的记录,使用NULL填充
select 列名 from 左表 right join 右表 on 从表.外键=主表.主键

应用

左外连接

需求:查询所有的部门,以及该部门下面的员工

sql
-- 添加一个销售部,暂时还没有员工
insert into dept (name) values ('销售部');

-- 使用内连接查询,缺少销售部
select * from dept d inner join emp e on d.id = e.dept_id;

-- 使用左外连接查询
select * from dept d left join emp e on d.id = e.dept_id;

右外连接

需求:查询所有员工,以及员工所属的部门

sql
-- 在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null
insert into emp values(null, '沙僧','男',6666,'2013-02-24',null);
select * from emp;

-- 使用内连接查询 
select * from dept d inner join emp e on d.id = e.dept_id;

-- 使用右外连接查询 
select * from dept d right join emp e on d.id = e.dept_id;

子查询

什么是子查询

  1. 将一个查询的结果做为另一个查询的条件
  2. 这是一种查询语句的嵌套,嵌套的 SQL 查询称为子查询。
  3. 如果使用子查询必须要使用括号

子查询分类

  1. 单行单列
  2. 单行多列
  3. 多行多列

单行单例

语法

sql
-- 如果子查询是单行单列,父查询使用比较运算符:> < =

应用

sql
-- 1. 查询最高工资是多少
select max(salary) from emp;

-- 2. 查询最高工资的员工信息
select * from emp where salary=(select max(salary) from emp);

-- 查询工资大于"蜘蛛精"的员工
-- 1. 查询蜘蛛精的工资是多少
select salary from emp where name='蜘蛛精';
-- 2. 查询大于这个工资的员工
select * from emp where salary > (select salary from emp where name='蜘蛛精');

多行单列

语法

多行单列认为是一个数组,父查询使用in、any 、all关键字

关键字说明
in查询包含在in条件中的所有数据
all可以与>、<结合起来使用,分别表示大于、小于其中的所有数据时条件为真
any可以与>、<结合起来使用,分别表示大于、小于其中的任何一个数据时条件为真

应用

sql
-- 查询工资大于5000的员工,来自于哪些部门,得到部门的名字
-- 1. 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;

-- 2. 再查询在这些部门id中部门的名字
select * from dept where id in (select dept_id from emp where salary > 5000);

-- 查询工资高于在1号部门工作的所有员工的员工信息
-- 1. 查询1号部门所有员工的工资,得到多行单列
select salary from emp where dept_id=1;

-- 2. 使用all关键字
select * from emp where salary > all (select salary from emp where dept_id=1);

-- 查询工资高于在1号部门工作的所有员工最低工资的员工信息
-- 1. 查询1号部门所有员工的工资,得到多行单列
select salary from emp where dept_id=1;

-- 2. 使用any关键字
select * from emp where salary > any (select salary from emp where dept_id=1);

多行多列

特点

子查询可以认为它是一张虚拟表,可以使用表连接再次进行多表查询

如果访问子查询表的字段,需要为子查询表取别名,否则无法访问表中的字段

应用

sql
-- 查询出2011年以后入职的员工信息,包括部门名称
-- 1. 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date > '2011-1-1';

-- 2. 查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门id等于的dept_id
select * from dept d inner join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;

-- 3. 使用右连接,否则没有部门的员工信息查询不到
select * from dept d right join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;

【重点】索引

概述

索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构,类似于字典中的目录

索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,会保存在额外的文件中

索引建立的原则

  1. 表的主键、外键必须有索引;外键是唯一的,而且经常会用来查询

  2. 数据量超过 300 的表应该有索引

  3. 经常与其他表进行连接的表,在连接字段上应该建立索引;经常连接查询,需要有索引

  4. 经常出现在 Where 子句中的字段,加快判断速度,特别是大表的字段,应该建立索引。

    注意: 建引,一般用在select ……where f1 and f2 ,我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用

  5. 经常用到排序的列上,因为索引已经排序

  6. 经常用在范围内搜索的列上创建索引,因为索引已经排序了,其指定的范围是连续的

常用索引分类

索引分类作用
普通索引仅加速查询 最基本的索引,没有任何限制,
大多数情况下使用到的索引
唯一索引加速查询 + 列值唯一(可以有null)
组合索引将几个列作为一条索引进行检索,
使用最左匹配原则,即一定要包含最左边的

语法

创建索引

sql
-- 建表时创建索引
create table 表名 (
  字段名1 字段类型1,
  字段名2 字段类型2,
  [unique] index (字段名1 [, 字段名2])
);

-- 表创建后再创建索引
create [unique] index 索引名称 on 表名 (字段名1 [, 字段名2]);

删除索引

sql
drop index 索引名称 on 表名;
alter table 表名 drop index 索引名称;

查看表中的索引

sql
show index from 表名;

代码

sql
/*
* 新建订单表user_order
*     id 编号,自增主键
*    uid 用户id 整型
*    createtime 订单创建时间 日期
*    pid 产品id 整型
*    price 产品价格 decimal
*    pname 产品名称 可变字符串100
*    uid和pid是组合索引
*/

-- 导入数据

-- 创建组合索引(uid、pid)

-- 查询

【重点】事务

概述

在实际的业务开发中,有些业务操作要多次访问数据库。一个业务要发送多条 SQL 语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行,要么所有的 SQL 语句全部执行成功。如果其中有一条 SQL 语句失败,就进行事务的回滚,所有的 SQL 语句全部执行失败。

事务的应用场景

张三给李四转账,张三账号减钱,李四账号加钱。

假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元,数据就出现问题了。我们需要保证其中一条 SQL 语句出现问题,整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。

sql
-- 创建数据表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    balance DOUBLE
);

-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

-- 1. 张三账号-500
UPDATE account SET balance = balance - 500 WHERE id=1;

-- 2. 李四账号+500
UPDATE account SET balance = balance + 500 WHERE id=2;

事务的四大特性(ACID)

  • 原子性(Atomicity)

    事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

  • 一致性(Consistency)

    事务前后数据的完整性必须保持一致

  • 隔离性(Isolation)

    多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离,不能相互影响。

  • 持久性(Durability)

    事务一旦被提交,它对数据库中数据的改变就是永久性的,即使数据库发生故障也不应该对其有任何影响

事务控制

语法

  • START TRANSACTION

    开启事务

  • COMMIT

    提交事务

  • ROLLBACK

    回滚事务

事务控制流程

第 1 种情况:开启事务 -> 执行 SQL 语句 -> 成功 -> 提交事务 第 2 种情况:开启事务 -> 执行 SQL 语句 -> 失败 -> 回滚事务

演示【成功】

模拟张三给李四转500元钱

  1. 恢复数据

    sql
    UPDATE account SET balance = 1000;
  2. 开启事务

    sql
    START TRANSACTION;
    UPDATE account SET balance = balance - 500 WHERE id=1;
    UPDATE account SET balance = balance + 500 WHERE id=2;
  3. 查询数据库

    sql
    SELECT * FROM account;
    idNAMEbalance
    1张三1000
    2李四1000
  4. 提交事务

    sql
    COMMIT;
  5. 查询数据库

    sql
    SELECT * FROM account;
    idNAMEbalance
    1张三500
    2李四1500

演示【失败】

  1. 恢复数据

    sql
    UPDATE account SET balance = 1000;
  2. 开启事务

    sql
    START TRANSACTION;
    UPDATE account SET balance = balance - 500 WHERE id=1;
  3. 查询数据库

    sql
    SELECT * FROM account;
    idNAMEbalance
    1张三1000
    2李四1000
  4. 回滚事务

    sql
    ROLLBACK;
  5. 查询数据库

    sql
    SELECT * FROM account;
    idNAMEbalance
    1张三1000
    2李四1000

单条DML事务

MySQL 的每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,执行完毕自动提交事务,MySQL 默认开始自动提交事务。

自动提交事务

  1. 恢复数据

    sql
    UPDATE account SET balance = 1000;
  2. 更新张三帐号

    sql
    UPDATE account SET balance = balance - 500 WHERE id=1;
  3. 查看数据库

    sql
    SELECT * FROM account;
    idNAMEbalance
    1张三500
    2李四1000
  4. 查看autocommit全局变量

    sql
    SHOW VARIABLES LIKE '%commit%';
    -- 或
    SELECT @@autocommit;
    
    -- 0:OFF(关闭自动提交)
    -- 1:ON(开启自动提交)

手动提交事务

  1. 恢复数据

    sql
    UPDATE account SET balance = 1000;
  2. 修改全局变量"autocommit",取消自动提交事务

    sql
    SET autocommit=0;
  3. 更新张三帐号

    sql
    UPDATE account SET balance = balance - 500 WHERE id=1;
  4. 查看数据库

    数据未改变

    sql
    SELECT * FROM account;
    idNAMEbalance
    1张三1000
    2李四1000
  5. 提交事务

    sql
    COMMIT;
  6. 查看数据库

    数据已改变

    sql
    SELECT * FROM account;
    idNAMEbalance
    1张三500
    2李四1000

事务原理

事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接)

小结

说出事务原理? 开启事务后,SQL 语句会放在临时的日志文件,如果提交事务,将日志文件中 SQL 的结果放在数据库中

如果回滚事务清空日志文件.

事务的操作MySQL操作事务的语句
开启事务start transaction
提交事务commit
回滚事务rollback
查询事务的自动提交情况show variables like '%commit%'; / select @@autocommit;
设置事务的自动提交方式set autocommit = 0 -- 关闭自动提交

【扩展】并发访问

并发访问的问题

理想状态下,在操作事务时,多个事务之间互不影响。

如果隔离级别设置不当就可能引发并发访问问题:

  1. 脏读

    一个事务读取到了另一个事务中尚未提交的数据

  2. 不可重复读

    一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是一致的,这是事务update时引发的问题

  3. 幻读

    一个事务内读取到了别的事务插入的数据,导致前后读取记录行数不同。这是insert或delete时引发的问题

事务隔离级别

MySQL 数据库有四种隔离级别:上面的级别最低,下面的级别最高。

级别名字隔离级别脏读不可重复读幻读数据库默认隔离级别
1读未提交read uncommitted
2读已提交read committedOracle和SQL Server
3可重复读repeatable readMySQL
4串行化serializable

脏读

脏读非常危险的,比如张三向李四购买商品,张三开启事务,向李四账号转入500块,然后打电话给李四说钱已经转了。李四一查询钱到账了,发货给张三。张三收到货后回滚事务,李四的再查看钱没了。

演示

  1. 查询全局事务隔离级别

    sql
    show variables like '%isolation%';
    
    select @@tx_isolation;
  2. 设置事务隔离级别,需要退出 MSQL 再进入 MYSQL 才能看到隔离级别的变化

    sql
    set global transaction isolation level read uncommitted;
    sql
    UPDATE account SET balance = 1000;
  3. 打开 A 窗口登录 MySQL,设置全局的隔离级别为最低

    sql
    mysql -u root -p
    set global transaction isolation level read uncommitted;
  4. 打开 B 窗口,AB 窗口都开启事务

    sql
    use day23;
    start transaction;
  5. 【A 窗口】更新 2 个人的账户数据,未提交

    sql
    update account set balance=balance-500 where id=1;
    update account set balance=balance+500 where id=2;
  6. 【B 窗口】查询账户

    sql
    select * from account;
    idNAMEbalance
    1张三500
    2李四1500
  7. A 窗口回滚

    sql
    rollback;
  8. B 窗口查询账户,钱没了

    sql
    SELECT * FROM account;
    idNAMEbalance
    1张三1000
    2李四1000

解决方案

提升全局隔离级别为read committed

  1. 恢复数据

    sql
    UPDATE account SET balance = 1000;
  2. 设置全局隔离级别为read committed

    sql
    set global transaction isolation level read committed;

不可重复读

两次查询输出的结果不同,到底哪次是对的?不知道以哪次为准。

很多人认为这种情况就对了,无须困惑,当然是后面的为准。我们可以考虑这样一种情况,比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户,结果在一个事务中针对不同的输出目的地进行的两次查询不一致,导致文件和屏幕中的结果不一致,银行工作人员就不知道以哪个为准了。

演示

  1. 恢复数据

    sql
    UPDATE account SET balance = 1000;
  2. 设置全局隔离级别

    sql
    set global transaction isolation level read committed;
  3. 【B 窗口】开启事务,查询数据

    sql
    start transaction;
    select * from account;
    idNAMEbalance
    1张三1000
    2李四1000
  4. 【A 窗口】开启事务,更新数据

    sql
    start transaction;
    update account set balance=balance+500 where id=1;
    commit;
  5. 【B 窗口】查询

    sql
    select * from account;
    idNAMEbalance
    1张三1000
    2李四1000

解决方案

提升全局隔离级别为repeatable read

  1. 恢复数据

    sql
    UPDATE account SET balance = 1000;
  2. 设置全局隔离级别为repeatable read

    sql
    set global transaction isolation level repeatable read;

幻读

是指在一个事务内读取到了别的事务插入的数据,导致前后读取记录行数不同。这是insert或delete时引发的问题

演示

  1. 【A 窗口】开启事务,查询 id>1 的数据

    sql
    START TRANSACTION;
    SELECT * FROM account WHERE id > 1;
    idNAMEbalance
    2李四1000
  2. 【B 窗口】开启事务,添加一条数据,提交事务

    sql
    START TRANSACTION;
    INSERT INTO account(NAME, balance) VALUES('tom', 22000);
    COMMIT;
  3. 【A 窗口】修改 id>1balance 为 0,重新查询 id>1 的数据

    sql
    UPDATE account SET balance=0 WHERE id > 1;
    SELECT * FROM account WHERE id > 1;
    idNAMEbalance
    1李四0
    2tom0

解决方案

提升全局隔离级别为serializable

  1. 恢复数据

    sql
    UPDATE account SET balance = 1000;
  2. 设置全局隔离级别为serializable

    sql
    set global transaction isolation level `serializable`;

小结

  1. 并发访问的三个问题

    • 赃读

    一个事务读取另一个事务还没有提交的数据

    • 不可重复读

    一个事务读取多次数据内容不一样

    • 幻读

    一个事务读取多次数量不一样

  2. 四种隔离级别

    • 读未提交(read uncommitted)
    • 读已提交(read committed)
    • 可重复读(repeatable read)
    • 串行化(serializable)

【扩展】事务的传播特性

  1. PROPAGATION_REQUIRED

    如果存在活动事务,⽀持当前事务。 如果没有活动事务,则开启

  2. PROPAGATION_SUPPORTS(propagation_supports)

    如果存在活动事务,支持当前事务。 如果没有活动事务,则非事务执⾏

  3. PROPAGATION_MANDATORY(propagation_mandatory)

    如果存在活动事务,支持当前事务。 如果没有活动事务,则抛出异常

  4. PROPAGATION_REQUIRES_NEW(propagation_requires_new)

    总是开启一个新的事务。 如果存在活动事务,则将这个存在的事务挂起

  5. PROPAGATION_NOT_SUPPORTED(propagation_not_supported)

    总是非事务执⾏,并挂起任何存在的事务。

  6. PROPAGATION_NEVER(propagation_never)

    总是非事务执⾏ 如果存在活动事务,则抛出异常

  7. PROPAGATION_NESTED(propagation_nested)

    如果存在活动事务,则运⾏在一个嵌套的事务中; 如果没有活动事务,则按PROPAGATION_REQUIRED属性执⾏

学习目标总结

  • 能够使用 SQL 语句添加主键、外键、唯一、非空约束
  • 能够说出多表之间的关系及其建表原则
  • 能够理解三大范式
  • 能够使用内连接进行多表查询
  • 能够使用外连接进行多表查询
  • 能够使用子查询进行多表查询
  • 能够理解多表查询的规律
  • 能够理解事务的概念
  • 能够说出事务的原理
  • 能够在 MySQL 中使用事务
  • 能够理解脏读,不可重复读,幻读的概念及解决办法