自学内容网 自学内容网
自学内容网 > 正文

MySQL 基础

🕗 发布于 2024-11-08 20:49 mysql  数据库  

MySQL 基础

SQL 语法

市面上有很多关系型数据库，不管使用哪一个，都是使用 SQL 语言来进行统一操作，SQL 语言是操作关系型数据库的统一标准。SQL 全称 Structured Query Language，结构化查询语言。

SQL 语句有以下几个通用的语法规则：

  • SQL 语句可以单行或多行书写，必须以分号结尾
  • SQL 语句可以使用空格/缩进来增强语句的可读性
  • MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
  • 注释：
    • 单行注释：-- 注释内容 或 # 注释内容
    • 多行注释：/* 注释内容 */

SQL 语句根据其功能主要分为四类：DDL、DML、DQL、DCL

  • DDL（Data Definition Language）：数据定义语言，用来定义数据库对象(数据库，表，字段)
  • DML（Data Manipulate Lanuage）：数据操作语言，用来对数据库表中的数据进行增删改
  • DQL（Data Query Language）：数据查询语言，用来查询数据库表中的记录
  • DCL（Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象(数据库，表，字段)。

数据库操作
  1. 连接到 MySQL 数据库以后，一般会先查看当前 MySQL 中的数据库有哪些，是否有需要操作的数据库，操作指令如下
SHOW DATABASES;

在这里插入图片描述

  1. 我们可以创建数据库，指令如下：
CREATE DATABASE 数据库名;

在这里插入图片描述

如果新创建的数据库名已经存在，创建会失败，因此可以使用 IF NOT EXISTS 进行创建来避免失败的出现

CREATE DATABASE IF NOT EXISTS 数据库名;

在这里插入图片描述

  1. 数据库在创建时可以设置字符集，如果使用 utf8，则字符只能使用三个字节表示，而 MySQL 中有些字符需要四个字节，因此可以使用 utf8mb4（默认字符集），指令如下：
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
  1. 当我删除不存在的数据库会失败，因此在删除数据库一般使用如下的指令
DROP DATABASE [IF EXISTS] 数据库名;
  1. 切换到创建好的数据库，指令如下：
USE 数据库名;
  1. 如果我们忘记当前所使用的数据库是哪一个，可以使用如下指令来获取当前所使用的数据库名：
SELECT DATABASE();
表操作
  1. 切换到指定的数据库以后，可以查看此数据库中的所有数据表，命令如下：
SHOW TABLES;
  1. 创建一个表的命令如下：
CREATE TABLE 表名 (
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
......
字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];

比如创建下面结构的一张表，SQL 语句为

在这里插入图片描述

在这里插入图片描述

  1. 一个数据库是会存在很多的表，查询指定表结构使用如下命令：
DESC 表名;

在这里插入图片描述

  1. 不仅如此，还可以查找指定表的建表语句，通过这个语句查看的建表语句，有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如存储引擎，字符集等
SHOW CREATE TABLE 表名;

在这里插入图片描述

  1. 修改已创建表的表名，命令如下：
ALTER TABLE 表名 RENAME TO 新表名;
  1. 删除已创建的表与删除已创建的数据库类似，命令如下：
DROP TABLE [IF EXISTS] 表名;

上面删除表的方式会将表和数据一并删除，如果只想删除表中的数据而不删除表，则使用下面的命令：

TRUNCATE TABLE 表名;
数据类型

上面在创建一个表的时候，需要标明每个字段的类型，MySQL 数据库中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型：

  • 数值类型
    • TINYINT：小整数值，大小 1 字节，有符号范围是（-128，127），无符号范围是（0，255）
    • SMALLINT：大整数值，大小 2 字节，有符号范围是（-32768，32767），无符号范围是（0，65535）
    • MEDIUMINT：大整数值，大小 3 字节，有符号范围是（-8388608，8388607），无符号范围是（0，16777215）
    • INT/INTEGER：大整数值，大小 4 字节，有符号范围是（-2147483648，2147483647），无符号范围是（0，4294967295）
    • BIGINT：极大整数值，大小 8 字节，有符号范围是（-2^ 63，2^ 63 - 1），无符号范围是（0，2^64-1）
    • FLOAT：单精度浮点数值，大小 4 字节，有符号范围是（-3.402823466 E+38，3.402823466351 E+38），无符号范围是 0 和 (1.175494351 E-38，3.402823466 E+38)
    • DOUBLE：双精度浮点数值，大小 8 字节，有符号范围是（-1.7976931348623157E+308，1.7976931348623157E+308），无符号范围是 0 和 (2.2250738585072014E-308，1.7976931348623157E+308)
    • DECIMAL：小数值（精确定点数）有符号范围依赖于M(精度)和D(标度)的值，无符号范围依赖于M(精度)和D(标度)的值
  • 字符串类型
    • CHAR：定长字符串（需要指定长度），大小是 0~255 字节
    • VACHAR：变长字符串（需要指定长度），大小是 0~65535 字节
    • TINYBLOB：不超过 255 个字符的二进制数据，大小是 0~255 字节
    • TINYTEXT：短文本字符串，大小是 0~255 字节
    • BLOB：二进制形式的长文本数据，大小是 0~65535 字节
    • TEXT：长文本数据，大小是 0~65535 字节
    • MEDIUMBLOB：二进制形式的中等长度文本数据，大小是 0~16777215 字节
    • MEDIUMTEXT：中等长度文本数据，大小是 0~16777215 字节
    • LONGBLOB：二进制形式的极大文本数据，大小是 0~4294967295 字节
    • LONGTEXT：极大文本数据，大小是 0~4294967295 字节
  • 日期时间类型
    • DATE：日期值，格式为 YYYY-MM-DD，大小是 3 字节，范围为 1000-01-01 至 9999-12-31
    • TIME：时间值或持续时间，格式为 HH:MM:SS，大小是 3 字节，范围为 -838:59:59 至 838:59:59
    • YEAR：年份值，格式为 YYYY，大小是 1 字节，范围为 1901 至 2155
    • DATETIME：混合日期和时间值，格式为 YYYY-MM-DD HH:MM:SS，大小是 8 字节，范围为 1000-01-01 00:00:00 至 9999-12-31 23:59:59
    • TIMESTAMP：混合日期和时间值，时间戳，格式为 YYYY-MM-DD HH:MM:SS，大小是 4 字节，范围为 1970-01-01 00:00:01 至 2038-01-19 03:14:07

CHAR 与 VACHAR 都可以描述字符串，CHAR 是定长字符串，指定长度多长，就占用多少个字符，和
字段值的长度无关 。而 VACHAR 是变长字符串，指定的长度为最大占用长度 。相对来说，CHAR 的性
能会更高些。

了解了 MySQL 的基本类型，设计一张员工信息表，要求如下：

  1. 编号（纯数字）
  2. 员工工号 (字符串类型，长10位)
  3. 员工姓名（字符串类型，长度不超过10位）
  4. 性别（男/女，存储一个汉字）
  5. 年龄（正常人年龄，不可能存储负数）
  6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
  7. 入职时间（取值年月日即可）

在这里插入图片描述

表字段操作

如果已创建的表某个字段的类型有误，或者字段名有误，我们都可以进行修改，在表创建完以后还可以添加和删除字段（字段是一条记录）。

  1. 添加字段的命令如下：
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

如在上面一创建的 emp 表中添加字段 nickname 表示昵称，类型为 varchar(20)

在这里插入图片描述

  1. 删除字段的命令如下：
ALTER TABLE 表名 DROP 字段;
  1. 修改数据类型的命令如下：
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
  1. 修改字段名和字段类型命令如下：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型(长度) [COMMENT 注释] [约束];

DML

DML 全称 Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。主要有三个操作：添加数据、修改数据以及删除数据。

添加数据
  1. 给指定字段添加数据的语法如下：
INSERT INTO 表名 (字段1, 字段2, 字段3, ...) VALUES (值1, 值2, 值3, ...)

如果是给所有字段添加数据，可以使用如下的语法（每个值要与字段一一对应）：

INSERT INTO 表名 VALUES (值1, 值2, 值3, ...)

在这里插入图片描述

  1. 一个一个添加数据太过于繁琐，可以一次批量添加数据，语法如下：
INSERT INTO 表名 (字段1, 字段2, 字段3, ...) VALUES (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), ... -- 批量添加指定字段的数据
INSERT INTO 表名 VALUES (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), ... -- 批量添加全部字段的数据

在这里插入图片描述

注意事项：

  • 插入数据时，指定的字段顺序需要与值的顺序时一一对应的
  • 字符串和日期型数据应该包含在引号中
  • 插入的数据大小，应该在字段的规定范围内
修改数据

修改数据的具体语法如下：

UPDATE 表名 SET 字段1=值1, 字段2=值2, ... [WHERE 条件];

在这里插入图片描述

注意事项：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据

删除数据的语法如下：

DELETE FROM 表名 [WHERE 条件];

在这里插入图片描述

注意事项：

  • DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
  • DELETE 语句不能删除某一个字段的值（可以使用 UPDATE，将该字段的值置为 NULL 即可）

DQL

DQL 全称 Data Query Language（数据查询语言），用来查询数据表中的记录。查询的关键字是 SELECT。

在一个正常的业务系统中，查询操作的频次是要远高于增删改的，当我们去访问企业官网、电商网站，在这些网站中我们所看到的数据，实际都是需要从数据库中查询并展示的。而且在查询的过程中，可能还会涉及到条件、排序、分页等操作。

查询的语法结构如下所示，根据语法结构逐步理解查询语句

在这里插入图片描述

在学习数据查询操作之前下做如下的准备工作：

drop table if exists emp;

create table emp(
  id int comment '编号',
  workno varchar(10) comment '工号',
  name varchar(10) comment '姓名',
  gender char(1) comment '性别',
  age tinyint unsigned comment '年龄',
  idcard char(18) comment '身份证号',
  workaddress varchar(50) comment '工作地址',
  entrydate date comment '入职时间'
) comment '员工表';

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');
基础查询

基础查询是指查询整个表指定字段的所有数据或所有字段的所有数据，语法如下：

SELECT 字段1, 字段2, ... FROM 表名;-- 查询指定字段的所有数据
SELECT * FROM 表名;-- 查询所有字段的所有数据

如果查询出来的字段名不好理解，可以为其设置别名，语法如下：

SELECT 字段1 AS 别名1, 字段2 AS 别名2, ... FROM 表名;-- 查询指定字段的所有数据
SELECT 字段1 别名1, 字段2 别名2, ... FROM 表名;-- 查询指定字段的所有数据

查询出来的数据可能会存在很多重复的内容，可以使用 DISTINCT‘ 关键字来修饰字段去除重复，语法如下：

SELECT DISTINCT 字段列表 FROM 表名;
条件查询

条件查询是指在基础查询的基础上进行一些条件过滤，把符合的数据记录查询出来，语法如下：

SELECT 字段列表 FROM 表名 WHERE 条件列表;

MySQL 中常用的比较运算符如下图所示：

在这里插入图片描述

常用的逻辑运算符如下图所示：

在这里插入图片描述

聚合函数

聚合函数是将字段作为计算对象，对该字段的所有数据进行计算，常见的聚合函数如下：

在这里插入图片描述

基本使用语法如下：

SELECT 聚合函数(字段列表) FROM 表名;

注意：NULL 是不参与所有聚合函数运算符的。

分组查询

前面查询获取的数据是指定条件下的数据记录，为了方便统计，可以对这些记录进行分组，使用 GROUP BY 关键字。

基本语法如下：

SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名1, 分组字段名2, ... [HAVING 分组后过滤条件];

WHERE 和 HAVING 的区别

  • 执行时机不同：WHERE 是在分组之前进行过滤，不满足 WHERE 条件是不会参与分组；而 HAVING 是分组之后对结果进行过滤
  • 判断条件不同：WHERE 不能对聚合函数进行判断，而 HAVING 可以
  • 执行顺序：WHERE > 聚合函数 > HAVING

在这里插入图片描述

分组之后，查询的字段一般为分组字段和聚合函数，查询其他字段无任何意义，执行查询其他字段的语法可能还会报错。

排序查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。基本语法如下：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2, ...;

排序方式只有两种：升序（ASC 默认）和降序（DESC）

分页查询

分页查询是指在前面查询的结果中，选取指定范围的数据，起始索引从 0 开始，使用 LIMIT 关键字，语法如下：

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

在这里插入图片描述

执行顺序

上述标题的顺序是查询语句的编写顺序，而查询语句的执行顺序略有不同，如下图所示

在这里插入图片描述

验证如下：

查询年龄大于 15 的员工姓名、年龄，并根据年龄进行升序排序

select name, age from emp where age > 15 order by age;

在查询时，我们给 emp 表起一个别名 e，然后在 select 和 where 中使用该别名

select e.name, e.age from emp e where e.age > 15 order by age;

执行上述 SQL 语句后，我们看到依然可以正常的查询到结果，此时就说明： from 先执行，然后 where 和 select 执行。那 where 和 select 到底哪个先执行呢?

我们可以给 select 后面的字段起别名，然后在 where 中使用这个别名，然后看看是否可以执行成功。

select e.name ename, e.age eage from emp e where eage> 15 order by age;

执行上述 SQL 出错了，由此我们可以得出结论：from 先执行，然后执行 where，再执行 select 。

在这里插入图片描述
接下来看下面的语句

select e.name ename, e.age eage from emp e where eage> 15 order by eage;

执行成功，说明 order by 是在 select 语句之后执行的。

综上所述，我们可以看到 DQL 语句的执行顺序为： from … where … group by … having … select … order by … limit …

在这里插入图片描述

DCL

DCL 全称 Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限。一般数据库管理员用的多，开发人员只需了解即可。

管理用户
  1. 查询用户
select * from mysql.user;

在这里插入图片描述

其中 Host 代表当前用户访问的主机，如果为 localhost，仅代表只能够在当前本机访问，是不可以远程访问的，使用 % 表示所有网段可以访问。User代表的是访问该数据库的用户名。在 MySQ L中需要通过 Host 和 User 来唯一标识一个用户。

  1. 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
  1. 修改密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
  1. 删除用户
DROP USER '用户名'@'主机名';
权限控制

MySQL 定义了很多种权限，常用的权限如下图所示：

在这里插入图片描述

  1. 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
  1. 授予权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
  1. 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数

与编程语言中的函数是一样的作用，MySQL 中主要包含四类函数：字符串函数、数值函数、日期函数、流程函数

字符串函数

MySQL 内置了很多字符串函数，如下图所示：

在这里插入图片描述

字符串函数使用实例：将 workno 统一为 5 位数，不足五位数的在前面补 0，如 1 号就是 00001
在这里插入图片描述

数值函数

常见的数值函数如下所示

在这里插入图片描述

通过数据库的函数，生成一个六位数的随机验证码：首先获取随机数 rand() ——> 变为六位数的整数乘 1000000 ——> 没有小数 round(x, 0) ——> 可能生成的小于六位数，用 0 补齐 lpad(x, 6 , ‘0’)

在这里插入图片描述

日期函数

常见的日期函数如下所示：

在这里插入图片描述

使用日期函数查询所有员工的入职天数，并根据入职天数倒序排序

在这里插入图片描述

流程函数

流程函数也是很常用的一类函数，可以在 SQL 语句中实现条件筛选，从而提高语句的效率。常见的流程函数如下所示：

在这里插入图片描述

案例：

create table score(
  id int comment 'ID',
  name varchar(20) comment '姓名',
  math int comment '数学',
  english int comment '英语',
  chinese int comment '语文'
) comment '学员成绩表';

insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

根据分数判定成绩等级

在这里插入图片描述

约束

约束是作用与表中字段上的规则，用于限制存储在表中的数据。其目的是保证数据库中数据的正确、有效性和完整性。

在这里插入图片描述

注意：约束是作用在表中字段上的，可以创建/修改表的时候添加约束。

外键约束

外键约束是用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。我们来看一个例子：

在这里插入图片描述
左侧的 emp 表是员工表，里面存储员工的基本信息，包含员工的 ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门 ID，在员工的信息中存储的是部门的 ID dept_id，而这个部门的 ID 是关联的部门表 dept 的主键 id，那 emp 表的 dept_id 就是外键，关联的是另一张表的主键。

注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

如果此时删除 dept 表中的 id 字段，删除成功之后，部门表不存在 id 为 1 的部门，而在 emp 表中还有很多的员工，关联的为 id 为 1 的部门，此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。

创建外键约束的语法：

CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段)
);-- 在建表的时候创建外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN  KEY (外键字段名) REFERENCES 主表(主表字段)

如上面的两个表建立外键如下所示：

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

添加了外键约束之后，我们再到 dept 表(父表)删除 id 为 1 的记录， 此时将会报错，不能删除或更新父表记录，因为存在外键约束。

删除外键的语法如下：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种：

在这里插入图片描述

具体语法如下：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE [行为] ON DELETE [行为];

多表查询

DQL 语句，也就是数据查询语句，再此之前的查询都是单表查询，而下面我们要学习的则是多表查询操作。

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结
构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多（多对一）
  • 多对多
  • 一对一
一对多
  • 案例：部门与员工的关系
  • 关系：一个部门对应多个员工，一个员工对应一个部门
  • 实现：在多的一方建立外键，指向一的一方的主键

在这里插入图片描述

多对多
  • 案例：学生与课程的关系
  • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
  • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

在这里插入图片描述

一对一
  • 案例：用户 与 用户详情的关系
  • 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
  • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

在这里插入图片描述

多表查询概述

多表查询就是指从多张表中查询数据。

事务

事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

就比如：张三给李四转账 1000 块钱，张三银行账户的钱减少 1000，而李四银行账户的钱要增加 1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

在这里插入图片描述

正常情况：转账这个操作，需要分为以下这么三步来完成 ，三步完成之后，张三减少 1000，而李四增加 1000，转账成功：

在这里插入图片描述

异常情况：转账这个操作，也是分为以下这么三步来完成 ，在执行第三步是报错了，这样就导致张三减少 1000 块钱，而李四的金额没变，这样就造成了数据的不一致，就出现问题了。

在这里插入图片描述

为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行
完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。

在这里插入图片描述

MySQL 的事务默认是自动提交的，也就是说，当执行完一条 DML 语句时，MySQL 会立即隐式的提交事务。

事务操作

数据准备：

drop table if exists account;
create table account(
  id int primary key AUTO_INCREMENT comment 'ID',
  name varchar(10) comment '姓名',
  money double(10,2) comment '余额'
) comment '账户表';

insert into account(name, money) values('张三',2000), ('李四',2000);
未控制事务
  1. 测试正常情况
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。

在这里插入图片描述

  1. 测试异常情况
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了....
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

我们把数据都恢复到 2000，然后再次一次性执行上述的 SQL 语句(出错了… 这句话不符合SQL语法，执行就会报错)，检查最终的数据情况，发现数据在操作前后不一致了。

在这里插入图片描述

控制事务 1

查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit = 0;

提交事务

COMMIT;

回滚事务

ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的 DML 语句都不会提交, 需要手动的执行 commit 进行提交。

控制事务 2

开启事务

START TRANSACTION 或 BEGIN;

提交事务

COMMIT;

回滚事务

ROLLBACK;

使用案例：

-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;

事务四大特性

事务有四大特性，简称 ACID：

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变是永久的。

在这里插入图片描述

并发事务问题

当数据库进行并发操作时，会出现三种事务问题：

  1. 脏读：一个事务读到另一个事务还没有提交的数据，如事务 B 读取到事务 A 未提交的数据

在这里插入图片描述

  1. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称为不可重复读，如事务 A 两次读取同一条记录，但是读取的数据却是不一样的

在这里插入图片描述

  1. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据
    已经存在，好像出现了 “幻影”。

在这里插入图片描述

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

在这里插入图片描述

查看事务隔离级别语法：

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别语法：

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低。


原文地址：https://blog.csdn.net/weixin_64501326/article/details/143502576

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024