MySQL-基础篇

文章目录

第一章 MYSQL 概述
- 数据库相关概念
- MySQL 数据库
- - 下载
  - 安装
  - 启动和停止 MySQL
  - 客户端连接
  - 解决：mysql 不是内部或外部命令，也不是可运行的程序或批处理文件。
  - 配置 Path 环境变量
  - 解决：net start mysql80 发生系统错误 5。拒绝访问。
  - MYSQL 的数据模型
第二章 SQL
- 2.1 SQL 通用语法
- 2.2 SQL 分类
- 2.3 DDL
- - 2.3.1 数据库操作
  - - 实践
  - 2.3.2 表操作
  - - 数据类型
    - - 数值类型
      - 字符串类型
      - 时间和日期类型
    - 实践
  - 2.3.3 MySQL 图形化界面
  - - 下载 DataGrip 2023.2.1
    - 安装
    - 使用
    - - 创建数据库
      - 创建表
      - 修改表结构
      - 自己编写 SQL 操作，不通过图形化界面工具
- 2.4 DML
- - 2.4.1 添加数据 insert
  - 2.4.2 修改数据 update
  - 2.4.3 删除数据 delete
  - 实践
- 2.5 DQL
- - 基础查询
  - 条件查询 where
  - 聚合查询（聚合函数）
  - 分组查询 group by
  - 排序查询 order by
  - 分页查询
  - DQL 执行顺序
  - 实践
- 2.6 DCL
- - 管理用户
  - 权限控制
  - 实践
第三章函数
- 字符串函数
- 数值函数
- 日期函数
- 流程函数
- 实践
第四章约束
- 常用约束
- 外键约束
- - 删除/更新行为
- 实践
- - SQL语句来进行指定
  - 演示约束
  - 图形界面添加约束
  - 演示外键约束
  - 图形界面演示外键约束
第五章多表查询
- 多表关系
- - 一对多
  - 多对多
  - 一对一
- 多表查询
- - 实践
- 内连接查询
- 外连接查询
- - 左外连接：left
  - 右外连接：right
- 自连接查询
- 联合查询 union, union all
- 子查询
- - 标量子查询
  - 列子查询
  - 行子查询
  - 表子查询
第六章事务
- 事务操作
- 事务操作
- - 方式一
  - 方式二
- 事务四大特性 ACID
- 并发事务问题
- 事务隔离级别
- - 演示脏读问题
  - 演示不可重复读问题
  - 演示幻读问题

第一章 MYSQL 概述

数据库相关概念

名称	全称	简称
数据库	存储数据的仓库，数据是有组织的进行存储	DataBase(DB)
数据库管理系统	操纵和管理数据库的大型软件	DataBase Management System (DBMS)
SQL	操作`关系型数据库`的编程语言，定义了一套操作关系型数据库统一`标准`	Structured Query Language (SQL)

MYSQL 数据库本质上就是一个数据库管理系统，只不过现在开发人员为了方便，经常把 MYSQL 数据库管理系统也称之为 MYSQL 数据库。

我们通过 SQL 来操作数据库。

MySQL 数据库

版本
- 社区版(MySQL Community Server)
  免费，MySQL 不提供任何技术支持
- 商业版(MySQL Enterprise Edition)
  收费，可以试用 30 天，官方提供技术支持

我们这篇文章就用社区版MySQL Community Server 8.0.34

下载

MYSQL 官网: https://www.mysql.com/downloads/

在这里插入图片描述
这个就是社区版本的下载页面

在这里插入图片描述

注意：这里 MSI Installer 有两个，第一个是通过联网在线安装，会在线下载安装包；第二个是离线安装。我选第二个（包含 32 位和 64 位安装包），下载到本地后进行安装。
在这里插入图片描述

安装

双击，等待，进入到安装的向导页面
在这里插入图片描述

点击 next

在这里插入图片描述

点击 Execute
在这里插入图片描述
安装好之后点击 Next

点击 Next

注意看，默认端口号是3306

不用做什么改动，点击 Next

在这里插入图片描述

Authentication Method 密码验证方式 这一步 很重要
第一个是强密码校验，mysql 推荐使用最新的数据库和相关客户端，MySQL8 换了加密插件，所以如果选第一种方式，很可能导致你的 navicat 等客户端连不上 mysql8；所以一定要选第二个，选完后点击 Next

在这里插入图片描述

设置 MySQL 默认的用户 root 的密码，需要自己记住，因为后面要用这个密码连接数据库，我设置的密码 123456

输完密码后，点击 next
在这里插入图片描述

在这里插入图片描述
我们可以看到它会把 MySQL 注册为 Windows 一个系统服务，服务的名称叫做 MySQL80，而且这个 MySQL 的服务会随系统开机自启Start the MySQL Server at System Startup

不用动，使用默认项即可，直接 Next

在这里插入图片描述

Server File Permissions 服务器文件权限，默认选择第一个，直接点击 Next。

在这里插入图片描述

点击 Execute，正在应用我们刚才配置的信息。

在这里插入图片描述

点击 Finish

在这里插入图片描述
点击 Next

在这里插入图片描述

点击 Finish

这样我们就已经安装完成啦！！！MySQL 安装完成之后，默认就已经启动了。

启动和停止 MySQL

win+R 输入 services.msc 回车进入 Windows 的系统服务，刚才安装的时候就提到了 MySQL 安装完成之后会自动的注册为系统服务 MySQL80。

在这里插入图片描述

cmd 命令输入指令

或者在“开始菜单”（右键单击）—Windows PowerShell（管理员）（A）。

启动
net start mysql80
停止
net stop mysql80

在这里插入图片描述

客户端连接

MySQL 提供的客户端命令行工具
在这里插入图片描述

输入密码，我的是123456

![在这里插入图片描述](https://img-blog.csdnimg.cn/01d4d83ac9cf4ed29a20417f09e5cae8.png)

系统自带的命令行工具执行指令
注意: 使用这种方式时，需要配置PATH环境变量。

mysql [-h 127.0.0.1][-P 3306] -u root -p
mysql -u root -p

在这里插入图片描述

解决：mysql 不是内部或外部命令，也不是可运行的程序或批处理文件。

在这里插入图片描述
命令中的 mysql 实际上是一个可执行程序，出现报错的原因是，系统找不到 mysql.exe 这个程序。

解决办法就是：给系统一个目录，让系统可以找到 mysql.exe 所在的目录，C:\Program Files\MySQL\MySQL Server 8.0\bin，添加到环境变量中即可。

配置 Path 环境变量

在这里插入图片描述

添加到环境变量

在这里插入图片描述

点击确定，确定，确定
重启 cmd, mysql.exe 就可以直接被识别

解决：net start mysql80 发生系统错误 5。拒绝访问。

问题在于在服务项中禁用了 MySQL 服务。
cmd—services.msc—右键—属性—自动。

在这里插入图片描述
然后关掉 cmd，同样使用管理员模式，就好啦

MYSQL 的数据模型

在这里插入图片描述

MySQL 客户端会给 MySQL 数据库服务器发送对应的 SQL 语句。

MySQL 数据库服务器有一个软件（DBMS 数据库管理系统），DBMS 会去维护、操作数据库，它也可以创建数据库。意味着在我们数据库服务器内部是可以维护多个数据库的，而一个数据库里面又可以维护多张表。

数据是存储在表结构当中的，这个表结构当中存储的就是一条一条的数据。

关系型数据库（RDBMS）
概念:建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
```
特点:
1.使用表存储数据，格式统一，便于维护
2.使用SQL语言操作，标准统一，使用方便
```

第二章 SQL

2.1 SQL 通用语法

1.SQL 语句可以单行或多行书写，以分号结尾。
2.SQL 语句可以使用空格 / 缩进来增强语句的可读性。
3.MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。 4.注释:
单行注释: - - 注释内容或 # 注释内容(MySQL 特有)
多行注释: /* 注释内容 */

2.2 SQL 分类

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

2.3 DDL

2.3.1 数据库操作

查询所有数据库：
SHOW DATABASES;
查询当前数据库：
SELECT DATABASE();
创建数据库：
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
- utf8 字符集长度为 3 字节，有些符号占 4 字节，所以推荐用utf8mb4字符集
删除数据库：
DROP DATABASE [ IF EXISTS ] 数据库名;
使用数据库：
USE 数据库名;

实践

在这里插入图片描述

COLLATE 排序规则推荐使用utf8mb4_unicode_ci

如果库级别没有设置 CHARSET 和 COLLATE，则库级别默认的 CHARSET 和 COLLATE 使用实例级别的设置。

在 mysql8.0 以下版本中，你如果什么都不修改，默认的 CHARSET 是 Latin1，默认的 COLLATE 是 latin1_swedish_ci。

从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci 大体上就是 unicode 的进一步细分，0900 指代 unicode 比较算法的编号（ Unicode Collation Algorithm version），ai 表示 accent insensitive（发音无关），例如 e, è, é, ê 和 ë 是一视同仁的。

在这里插入图片描述

2.3.2 表操作

查询当前数据库所有表：
SHOW TABLES;
查询表结构：
DESC 表名;
查询指定表的建表语句：
SHOW CREATE TABLE 表名;

创建表：

CREATE TABLE 表名(
         字段1 字段1类型 [COMMENT 字段1注释],
        字段2 字段2类型 [COMMENT 字段2注释],
         字段3 字段3类型 [COMMENT 字段3注释],
         ...
        字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];

注意：最后一个字段后面没有逗号

alter table 表名 add/modify/change/drop/rename to …;

添加字段：
ALTER TABLE 表名 ADD 字段名类型(长度) [COMMENT 注释] [约束];

修改数据类型：
ALTER TABLE 表名 MODIFY 字段名新数据类型(长度);
修改字段名和字段类型：
ALTER TABLE 表名 CHANGE 旧字段名新字段名类型(长度) [COMMENT 注释] [约束];

删除字段：
ALTER TABLE 表名 DROP 字段名;

修改表名：
ALTER TABLE 表名 RENAME TO 新表名

删除表：
DROP TABLE [IF EXISTS] 表名;
删除表，并重新创建该表：
TRUNCATE TABLE 表名;当我们在删除这张表的时候，这张表中所有数据都会被清楚掉，再重新创建这张表，这张表就是全新的一张表，里面数据没有了，只留下了这张空表，只有表结构没有数据。

注意:在删除表时，表中的全部数据也会被删除

数据类型

数值类型
字符串类型
日期

数值类型

分类	类型	大小	有符号（signed）范围	无符号（unsigned）范围	描述
数值类型	TINYINT	1 byte	(-128, 127)	(0, 255)	一个非常小的整数
	SMALLINT	2 bytes	(-32768, 32767)	(0, 65535)	大整数值
	MEDIUMINT	3 bytes	(-8388608, 8388607)	(0, 16777215)	大整数值
	INT or INTEGER	4 bytes	(-2147483648, 2147483647)	(0, 4294967295)	大整数值
	BIGINT	8 bytes	（-2^63, 2^63-1）	(0, 2^64-1)	极大整数值
	FLOAT	4 bytes	(-3.402823466 E+38，3.402823466351 E+38)	0和(1.175494351 E-38，3402823466 E+38)	单精度浮点数值
	DOUBLE	8 bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0和(22250738585072014 E-308，17976931348623157 E+308)	双精度浮点数值
	DECIMAL		依赖于M(精度)和D(标度)的值例如：123.45 精度是5 标度是2	依赖于M(精度)和D(标度)的值	小数值（精确定点数）

test_num TINYINT UNSIGNED
float(5.3) 5 宽度 3 精度[小数位] 剩余的位数留给整数部分宽度不算小数点

字符串类型

作用：用于存储用户的姓名、爱好、发布的文章等
CHAR 系列 CHAR 定长字符串、 VARCHAR 变长字符串 - char(10) 根据 10，占 10 个，未占用的字符会用空格进行补位，不管数据长度有没有 10 个字符，存储的时候都会占 10 个字符的空间。10个字符=10个汉字，在 unicode 编码体系中汉字要占 4 个字节，常用中文字符用 utf-8 编码占用 3 个字节。
列的长度固定为创建表时声明的长度： 0 ~ 255 bytes - varchar(10) 根据实际字符串长度占空间，最多 10 个，数据长度多少个字符，存储的时候就占多少个字符的空间
列中的值为可变长字符串，长度： 0 ~ 65535 bytes

> 字符类型的宽度是会限制输入的。 1.经常变化的字段用 varchar 2.知道固定长度的用 char 3.不知道长度尽量用 varchar
4.char 相比于 varchar，char 的性能要好，原因是 varchar 在使用的时候，需要根据内容去计算所占用的空间 5.超过 255 字符的只能用 varchar 或者 text 6.能用 varchar 的地方不用 text
1 个汉字 = 1 个字 = 1 个字符
1 个字符 = 1 个字节 = 8bit(ACSII 码下)
1 个字符 = 2 个字节 = 16bit(Unicode 码下)
oracle 中 varchar2(10) 既 10 个字节 3 个汉字，mysql 中 varchar(10) 既 10 个字符 10 个汉字。
TEXT 系列 TINYTEXT、 TEXT 、MEDIUMTEXT、 LONGTEXT
- TINYTEXT 0 ~ 255 bytes 短文本字符串
- TEXT 0 ~ 65535 bytes 长文本数据
- MEDIUMTEXT 0 ~ 16 777 215 bytes 中等长度文本数据
- LONGTEXT 0 ~ 4 294 967 295 bytes 极大文本数据
BLOB 系列 TINYBLOB、 BLOB、 MEDIUMBLOB、 LONGBLOB
- TINYBLOB 0 ~ 255 bytes 不超过 255 个字符的二进制数据
- BLOB 0 ~ 65535 bytes 二进制形式的长文本数据
- MEDIUMBLOB 0 ~ 16 777 215 bytes 二进制形式的中等长度文本数据
- LONGBLOB 0 ~ 4 294 967 295 bytes 二进制形式的极大文本数据
BINARY 系列 BINARY、 VARBINARY
枚举类型： ENUM 单选 enum
集合类型： SET 多选 set

时间和日期类型

分类	类型	大小	范围	格式	描述
日期类型	DATE	3 byte	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
	TIME	3 byte	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
	YEAR	1 byte	1901 至 2155	YYYY	年份值
	DATETIME	8 byte	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
	TIMESTAMP	4 byte	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

从 MySQL5.5.27 开始，2 位格式的 YEAR 已经不推荐使用。YEAR 默认格式就是“YYYY”，没必要写成 YEAR(4)，从 MySQL 8.0.19 开始，不推荐使用指定显示宽度的 YEAR(4)数据类型。

实践

在这里插入图片描述

mysql> use testdb2;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> create table student(
	student_id int comment 'id',
	student_name varchar(20) comment '姓名',
	student_number int unsigned comment '学号',
	student_sex tinyint(3) comment '性别',
	student_birthday date comment '出生日期'
) comment '用户表';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> desc student;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| student_id       | int          | YES  |     | NULL    |       |
| student_name     | varchar(20)  | YES  |     | NULL    |       |
| student_number   | int unsigned | YES  |     | NULL    |       |
| student_sex      | tinyint      | YES  |     | NULL    |       |
| student_birthday | date         | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table


                                     |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `student_id` int DEFAULT NULL COMMENT 'id',
  `student_name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `student_number` int unsigned DEFAULT NULL COMMENT '学号',
  `student_sex` tinyint DEFAULT NULL COMMENT '性别',
  `student_birthday` date DEFAULT NULL COMMENT '出生日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table student add student_password char(50) comment '密码';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| student_id       | int          | YES  |     | NULL    |       |
| student_name     | varchar(20)  | YES  |     | NULL    |       |
| student_number   | int unsigned | YES  |     | NULL    |       |
| student_sex      | tinyint      | YES  |     | NULL    |       |
| student_birthday | date         | YES  |     | NULL    |       |
| student_password | char(50)     | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table student modify student_password varchar(50);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| student_id       | int          | YES  |     | NULL    |       |
| student_name     | varchar(20)  | YES  |     | NULL    |       |
| student_number   | int unsigned | YES  |     | NULL    |       |
| student_sex      | tinyint      | YES  |     | NULL    |       |
| student_birthday | date         | YES  |     | NULL    |       |
| student_password | varchar(50)  | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table student change student_password passwd char(50) comment '密码';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| student_id       | int          | YES  |     | NULL    |       |
| student_name     | varchar(20)  | YES  |     | NULL    |       |
| student_number   | int unsigned | YES  |     | NULL    |       |
| student_sex      | tinyint      | YES  |     | NULL    |       |
| student_birthday | date         | YES  |     | NULL    |       |
| passwd           | char(50)     | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table student drop passwd;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| student_id       | int          | YES  |     | NULL    |       |
| student_name     | varchar(20)  | YES  |     | NULL    |       |
| student_number   | int unsigned | YES  |     | NULL    |       |
| student_sex      | tinyint      | YES  |     | NULL    |       |
| student_birthday | date         | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>  alter table student rename to stu;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_testdb2 |
+-------------------+
| stu               |
+-------------------+
1 row in set (0.00 sec)

mysql>  drop table stu;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> create table student(
    ->  student_id int comment 'id',
    ->  student_name varchar(20) comment '姓名',
    ->  student_number int unsigned comment '学号'
    -> ) comment '学生表';
Query OK, 0 rows affected (0.01 sec)

mysql> truncate table student;
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| student_id     | int          | YES  |     | NULL    |       |
| student_name   | varchar(20)  | YES  |     | NULL    |       |
| student_number | int unsigned | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_testdb2 |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

mysql>

2.3.3 MySQL 图形化界面

在这里插入图片描述
Workbench(免费): http://dev.mysql.com/downloads/workbench/
navicat(收费，试用版 30 天): https://www.navicat.com/en/download/navicat-for-mysql
Sequel Pro(开源免费，仅支持 Mac OS): http://www.sequelpro.com/
HeidiSQL(免费): http://www.heidisql.com/
phpMyAdmin(免费): https://www.phpmyadmin.net/
SQLyog: https://sqlyog.en.softonic.com/

DataGrip，官网：https://www.jetbrains.com/datagrip/

我们装这个 DataGrip 2023.2.1 吧

下载 DataGrip 2023.2.1

在这里插入图片描述

安装

在这里插入图片描述
点 Next

选择安装位置，最好不要装 c 盘

在这里插入图片描述
点击选中桌面快捷方式，然后点击 Next

点击 Install

勾选复选框，让他运行起来，finish

在这里插入图片描述
问我们是否需要去导入配置，我们选择 Do not import settings 不导入，点击 OK

在这里插入图片描述
到这里基本上已经安装完成了，现在他提示我们激活，我们选择免费使用就行了

在这里插入图片描述

激活那些就不在这里写了，可以去百度，遇到问题也可以联系我

使用

在这里插入图片描述

创建一个 Project，点击 OK

现在这个工程就创建好啦！！！

接下来我们要想在这里连接 MySQL，点击左上角 + 号，Data Source 数据源
在这里插入图片描述

在这里插入图片描述

配置 MySQL 的配置信息，并且去下载驱动，实际上就是去下载驱动的 jar 包

在这里插入图片描述
下载好之后，点击 Test Connection 测试连接

连接成功之后点击 Apply 点击 OK

在这里插入图片描述
这里是提示你，点击这里可以展示其他的数据库 schemas

在这里插入图片描述
这样我们的数据库就已经展示出来了

创建数据库

schema 实际上和数据库 DataBase 是一个概念

在这里插入图片描述
点击 OK

create schema test;
和
create database test;
效果是一样的

创建表

点击 test 数据库，鼠标右键，New Table
在这里插入图片描述

在这里插入图片描述
添加字段

在这里插入图片描述

点击 OK，，这张表就创建成功了

在这里插入图片描述

修改表结构

在这里插入图片描述
想增加就直接 new

自己编写 SQL 操作，不通过图形化界面工具

运行快捷键Ctrl + 回车
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
就演示到这里吧

2.4 DML

2.4.1 添加数据 insert

给指定字段添加数据：
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, ...);

注意事项

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

2.4.2 修改数据 update

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

UPDATE student SET student_name = 'Jack' WHERE student_id = 1;

2.4.3 删除数据 delete

DELETE FROM 表名 [ WHERE 条件 ];

注意:

delete 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
delete 语句不能删除某一个字段的值(可以使用 update)。

实践

create table student(
	student_id int comment 'id',
	student_name varchar(20) comment '姓名',
	student_number int unsigned comment '学号',
	student_sex tinyint(3) comment '性别',
	student_birthday date comment '出生日期'
) comment '用户表';

insert into student(student_id, student_name, student_number, student_sex, student_birthday) values (1,'微泫',202104999,1,'2021-01-01');

select * from student;

在这里插入图片描述

insert into student values (2,'清风',202004001,0,'2020-02-03');
insert into student values (3,'卡芙卡',202204002,0,'2022-01-03'),(3,'艾丝妲',202104003,0,'2021-01-02');

UPDATE student SET student_name = 'Jack' WHERE student_id = 1;
UPDATE student SET student_name = 'XiaoYi', student_sex=0 WHERE student_id = 1;
# 更新所有学生的出生日期为2002-01-02
update student set student_birthday = '2002-01-02';

在这里插入图片描述

# 删除一个学生
delete from student where student_id = 1;
# 删除所有学生
delete from student;

2.5 DQL

查询关键字: select

语法：

SELECT
    字段列表
FROM
    表名字段
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后的条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

* 代表查所有数据

基础查询

查询多个字段：
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;

设置别名：as
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;

去除重复记录：distinct
SELECT DISTINCT 字段列表 FROM 表名;

转义：
SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'
/ 之后的_不作为通配符
ESCAPE ‘/’: 这指定了在 LIKE 模式中使用的转义字符。在这种情况下，它允许你在模式中使用斜线 (“/”) 作为字面字符，而不是作为特殊字符。

条件查询 where

语法：
SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件：

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围内（含最小、最大值）
IN(…)	在 in 之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是 NULL

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或 \|\|	或者（多个条件任意一个成立）
NOT 或 !	非，不是

聚合查询（聚合函数）

将一列数据作为一个整体，进行纵向计算。

常见聚合函数：

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：
SELECT 聚合函数(字段列表) FROM 表名;
例：

select count(student_id) from student where student_sex=1;

注意: 我们使用聚合函数的时候,所有的 Null 值是不参与聚合函数运算的,所有的聚合函数在运算的时候是不计算Null值的

分组查询 group by

语法：
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];

where 和 having 的区别：

执行时机不同：where 是分组之前进行过滤，不满足 where 条件不参与分组；having 是分组后对结果进行过滤。
判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

注意事项

执行顺序：where > 聚合函数 > having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

排序查询 order by

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

排序方式：

ASC: 升序（默认）
DESC: 降序

注意事项: 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

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

注意事项:

起始索引从 0 开始，起始索引 = （查询页码 - 1） * 每页显示记录数
分页查询是数据库的方言，不同数据库有不同实现，MySQL 是 LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

方言: 不同数据库之间不同的地方。

DQL 执行顺序

SELECT
    字段列表
FROM
    表名字段
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后的条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

from -> where -> group by -> having -> select -> order by -> limit

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

实践

drop table student;
-- 数据准备
create table student
(
    student_id       int comment 'id',
    student_name     varchar(20) comment '姓名',
    student_number   int unsigned comment '学号',
    student_sex      tinyint(3) comment '性别',
    student_birthday date comment '出生日期'
) comment '用户表';

insert into student values  (1, '微泫', 202104999, 1, '2021-01-01'),
        (2, '卡芙卡', 202204002, 0, '2022-01-03'),
        (3, '艾丝妲', 202104003, 0, '2021-01-05'),
        (4, '意境', 202204001, 1, '2022-02-04'),
        (5, '甘雨', 202304008, 0, '2023-03-06'),
        (6, '清风', 202004001, 1, '2020-02-02'),
        (7, '陈阳', 202304001, 0, '2023-02-02'),
        (8, '张三', 202304002, 1, '2023-02-02'),
        (9, '李同学', 202304003, 0, '2023-02-03'),
        (10, '王五', 202304005, 0, '2023-02-01'),
        (11, '周芷若', 202304004, 0, '2023-02-02'),
        (12, '花无缺', 202304006, 1, '2023-02-03'),
        (13, '小鱼儿', 202304007, 1, '2023-02-01');

-- -------练习查询------------
select student_name,student_number,student_sex from student;
select * from student;
select student_name from student;
select student_name as '姓名' from student;
select student_name '姓名' from student;
select distinct student_sex '性别' from student;

select * from student where student_sex=1;
select * from student where student_number < 202204001;
select * from student where student_number > 202204001;
insert into student values (14, '清风微泫', Null, 1, '2021-01-01');
select * from student where student_number  is null;
select * from student where student_number  is not null;
select * from student where student_number != 202204001;
-- ----
select * from student where student_number >= 202204001 && student.student_number <= 202304003;
select * from student where student_number >= 202204001 and student.student_number <= 202304003;-- 等价的
select * from student where student_number between 202204001 and 202304003;-- 等价的
select * from student where student_number between 202304003 and 202204001;-- 查不到数据 between 最小值 and 最大值
-- ----
select * from student where student_number >= 202204001 and student.student_sex = 1;
select * from student where student_number = 202204001 or  student_number = 202004001 or  student_number = 202304002;
select * from student where student_number in(202204001, 202004001, 202304002);# 效果跟上一行是一样的
select * from student where student_name like '__';# 查询姓名两个字的学生, 一个_代表一个字符
select * from student where student_name like '___';# 查询姓名三个字的学生, 一个_代表一个字符
insert into student values (15, '陈欣雨', 202304008, 1, '2023-01-01');
select * from student where  student_name like '%雨';# 姓名最后一个字是雨的

# 统计学生数量
select count(*) from student;# 这张表的总数据量
select count(student_id) from student;# 不写*也可以写具体某个字段,统计这张表当中student_id字段的总数量
select count(student_number) from student;# 我们使用聚合函数的时候,所有的Null值是不参与聚合函数运算的,所有的聚合函数在运算的时候是不计算Null值的
select count(student_id) from student where student_sex=1;

# 练习avg平均值
select avg(student_sex) from student;# 0.5333

select max(student_number) from student;# 求最大学号

select  min(student_number) from student;# 求最小学号

# 练习sum	求和
select sum(student_id) from student where student_sex=1;

# 分组查询 group by
# 根据性别分组 ，统计男性学生 和 女性学生的数量
select count(*) from student group by student_sex;
select student_sex,count(*) from student group by student_sex;

# 根据性别分组 ，统计男性学生 和 女性学生的 平均id
select student_sex,avg(student_id) from student group by student_sex;

# 练习having
select student_sex,count(*) from student  group by student_sex having count(*)>7;
select student_sex,count(*) from student where student_id<8 group by student_sex;
select student_sex,count(*) as '人数' from student where student_id<8 group by student_sex;
select student_sex,count(*) 人数 from student where student_id<8 group by student_sex having count(*)>3;
select student_sex as '性别(0女1男)' ,count(*) as 人数 from student where student_id<8 group by student_sex having student_sex=1;

select * from student order by student_number asc;# asc: 升序（默认）
select * from student order by student_number;# asc: 升序（默认）
select * from student order by student_number desc; # desc: 降序
select * from student order by student_birthday desc;

# 根据年龄对学生进行排序，年龄相同，再按照学号进行降序排序
select * from student order by student_birthday asc , student_number desc ;

# 查询第1页学生数据，每页展示10条记录
select * from student limit 0,10;
select * from student limit 10;

# 查询第2页学生数据，每页展示10条记录, （查询页码 - 1） * 每页显示记录数  -->  (2-1)*10
select * from student limit 10,10;

2.6 DCL

DCL 英文全称是 Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

查询用户：

USE mysql;
SELECT * FROM user;

在这里插入图片描述
打开了之后，大家会看到，默认在 mysql 数据库中有四个用户，而其他用户我们没有用过，我们就只用过 root，这这张表当中，第一个字段 Host 指的是主机，在 mysql 中我们要去创建一个用户、删除一个用户需要通过用户名和 host 主机地质局同时定位

创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

默认 %
create user '用户名' identified by '密码'; 等价于 create user '用户名'@'%' identified by '密码';

修改用户密码：
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
identified with 指定我们要使用的是 mysql 的本地密码的处理方式（mysql_native_password 一个加密方式）

删除用户：
DROP USER '用户名'@'主机名';

注意事项:

主机名可以使用 % 通配, 代表的是任意主机都可以访问。
这类 SQL 开发人员操作的比较少，主要是 DBA(Database Administrator 数据库管理员) 使用。

权限控制

常用权限：

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

查询权限：

SHOW GRANTS FOR '用户名'@'主机名';

授予权限：
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项

多个权限用逗号分隔
授权时，数据库名和表名可以用 _ 进行通配，代表所有
grant all on _.\* to '用户名'@'主机名'; 代表的是我们要给这个用户分配所有数据库, 所有表的权限, 那么这就类似于是一个超级管理员了。

实践

USE mysql;
SELECT * FROM user;
# 创建用户 wind_teacher ， 只能够在当前主机Localhost访问，密码123456789;
create user 'wind_teacher'@'localhost' identified by '123456789';

在这里插入图片描述

创建用户 wind_teacher ，只能够在当前主机 localhost 访问，密码 123456789。
我们会发现表单中都是 N（No），我们可以通过命令行来访问一下

mysql -u wind_teacher -p123456789
show databases;

这时我们看到，只查询到两个数据库，但是 root 可以查询到很多数据库，原因是什么呢？原因是因为我们刚刚仅仅只是创建了 wind_teacher 这个用户，他可以访问 mysql, 但是他没有访问其他数据库的权限，目前只是创建了用户，还并未给这个用户分配权限，这是创建用户。
在这里插入图片描述
我们当前创建出来的用户 wind_teacher 他只能够在本机访问 mysql 数据库，那假如我们想在任意的机器当中都来访问数据库怎么办？那此时呢我们可以把 localhost 替换为一个符号就可以了（%）。

# 创建用户 meat ， 任意主机都可以访问，密码123456;
create user 'meat'@'%' identified by '123456';

在这里插入图片描述

create user 'meat2' identified by '123456';
# 修改用户meat2的密码为666666
alter user 'meat2'@'%' identified with mysql_native_password by '666666';

mysql -u meat2 -p666666

在这里插入图片描述

# 删除用户meat2
drop user 'meat2'@'%';

-- 查询权限
show grants for 'meat'@'%';

在这里插入图片描述
这是一个什么权限呢，USAGE 这个指的是我们没有其他权限，你仅仅只是能够连接登录上 mysql 而已，这是查询权限。

接下来演示授予权限

授予之前：

mysql -u meat -p123456
show databases;

在这里插入图片描述
授予权限之后：
先执行这段代码，查询 meat 这个用户的权限是 ALL PRIVILEGES 是所有权限，针对的是 testdb3 数据库的所有权限。

-- 授予权限
grant all on testdb3.* to 'meat'@'%';
show grants for 'meat'@'%';

通过 meat 用户再次访问 mysql，然后再来看一下权限

 show databases;
 use testdb3;
 show tables;

在这里插入图片描述

-- 撤销权限
revoke all on testdb3.* from 'meat'@'%';

第三章函数

函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

常用函数：

函数	功能
CONCAT(s1, s2, …, sn)	字符串拼接，将 s1, s2, …, sn 拼接成一个字符串
LOWER(str)	将字符串全部转为小写
UPPER(str)	将字符串全部转为大写
LPAD(str, n, pad)	左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符串长度
RPAD(str, n, pad)	右填充，用字符串 pad 对 str 的右边进行填充，达到 n 个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格, 中间的空格不去除
SUBSTRING(str, start, len)	返回从字符串 str 从 start 位置起的 len 个长度的字符串。注意:它的索引是从 1 开始的。
REPLACE(column, source, replace)	(要搜索的字符串表达式, 需要替换的字符串, 替换成的字符串) 将 column 中所有出现的 source 替换为 replace

select 函数(参数);

数值函数

常见函数：

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x, y)	返回 x/y 的模
RAND()	返回 0~1 内的随机数, [0-1)
ROUND(x, y)	求参数 x 的四舍五入值，保留 y 位小数

求 N-M 之间的随机数公式 floor( rand()*( (M+1) -N) +N)

日期函数

常用函数：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定 date 的年份
MONTH(date)	获取指定 date 的月份
DAY(date)	获取指定 date 的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔 expr 后的时间值，type 可以是 day、month、year
DATEDIFF(date1, date2)	返回起始时间 date1 和结束时间 date2 之间的天数, 第一个时间减去第二个时间

流程函数

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

常用函数：

函数	功能
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，… 否则返回 default 默认值

实践

-- -------练习函数---------------------
-- 练习字符串函数
select concat('Hello',' Mysql');
select lower('HELLo');
select upper('heLLo');
select lpad('01',6,'+');
select rpad('01',6,'+');
select trim('  hello +1  '); # 中间空格还在
select substr('hello +1',1,5);# 注意:它的索引是从1开始的。

update student set student_name=lpad(student_name,6,'*');

在这里插入图片描述

-- 练习数值函数
select ceil(1.5);
select ceil(1.1);
select floor(1.5);
select mod(5,8); # 5除以8取余
select mod(5,3);
select rand();
# 求N-M之间的随机数公式  floor( rand()*( (M+1) -N) +N)
# 求10-100之间的随机数[10,100]
select rand()*(101-10);# [0,1) ==>*91  => [0,91)
select rand()*(101-10)+10;# [10,101)
select floor(rand()*(101-10)+10);# [10,101) =>向下取整=> [10,100]
select round(2.34,2);
select round(2.34,1);

select lpad(round(rand()*1000000,0),6,'0');# 生成6位随机验证码

在这里插入图片描述

多执行几次

在这里插入图片描述

-- 练习日期函数
select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());

select date_add(now(),interval 70 day);# 往后推七十天
select date_add(now(),interval 70 month);# 往后推七十月
select date_add(now(),interval 70 year);# 往后推七十年

select datediff('2023-10-1','2023-6-1');# 相差122天
select datediff('2023-6-1','2023-10-1');

# 查询student表所有学生生日，并根据出生天数倒序排序
select student_name,datediff(curdate(),student_birthday) as 'birth_days' from student order by birth_days desc;

在这里插入图片描述

-- 练习流程函数
select if(true,'OK','Error');
select if(false,'OK','Error');

select ifnull('OK','Default');
select ifnull('','Default');
select ifnull(null,'Default');

select
    student_name,
    (case student_sex when 0 then '女' when 1 then '男' else '未知' end) as '性别'
from student;

在这里插入图片描述

统计班级各个学员的成绩，展示的规则如下：
>=85，展示优秀
>=60，展示及格
否则展示不及格

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,"清风",66,88,95),(2,"梦里不知身是客",23,67,90),(3,"佳一",56,98,75);

在这里插入图片描述

select
    id,
    name,
    (case when math >=85 then '优秀' when math>=60 then '及格' else '不及格' end) as '数学',
    (case when english >=85 then '优秀' when english>=60 then '及格' else '不及格' end) as '英语',
    (case when chinese >=85 then '优秀' when chinese>=60 then '及格' else '不及格' end) as '语文'
from score;

在这里插入图片描述

第四章约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据

目的: 保证数据库中数据的正确、有效性和完整性。

分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为 null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.1 版本后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张图的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

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

在很多的规范当中提到，我们设计一张表，这张表一定要有主键

常用约束

约束条件	关键字
主键	PRIMARY KEY 既是非空也是唯一的
自动增长	AUTO_INCREMENT （oracle 数据库里面是没有的）
不为空	NOT NULL
唯一	UNIQUE
逻辑条件	CHECK
默认值	DEFAULT

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
有外键的表称之为子表（从表），外键所关联的这张表称之为父表（主表）。

添加外键：
1.创建表的时候直接添加

CREATE TABLE 表名(
    字段名 字段类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

2.表结构创建好之后我们额外的增加这样的一个外键

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

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

删除外键：

alter table 表名 drop foreign key 外键名;

删除/更新行为

行为	说明
`no action` (外键约束的默认行为)	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与 restrict 一致）
restrict	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与 no action 一致）
`cascade`	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录级联修改：on update cascade 级联删除：on delete cascade
`set null`	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为 null（要求该外键允许为 null）当然这个前提是这个外键允许是null值
set default	父表有变更时，子表将外键设为一个默认值（在mysql当中的默认引擎 Innodb 当中是不支持的）

更改删除/更新行为：

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

实践

根据需求，完成表结构的创建

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID 唯一标识	int	主键，并且自动增长	primary key, auto_increment
name	姓名	varchar(10)	不为空，并且唯一	not null, unique
age	年龄	int	大于 0，并且小于等于 120	check
status	状态	char(1)	如果没有指定该值，默认为 1	default
gender	性别	char(1)	无

SQL语句来进行指定

create database my_db;
use my_db;
create table user(
    id int primary key auto_increment comment "id主键",
    name varchar(10) not null unique comment "姓名",
    age int check ( age>0 && age<=120 ) comment "年龄",
    status char(1) default '1' comment "状态",
    gender char(1) comment "性别"
)comment "用户表";

演示约束

insert into user(name, age, status, gender) VALUES ('清风',19,'1','男'),('佳一',25,'0','女');

id 会自增
在这里插入图片描述

insert into user(name, age, status, gender) VALUES(null,20,'1','男');# 会报错

name 有非空约束
在这里插入图片描述

insert into user(name, age, status, gender) VALUES('清风',19,'1','男');# 会报错: user表的name字段已经有了一个'清风'了,重复了

在这里插入图片描述

insert into user(name, age, status, gender) VALUES('梦里不知身是客',80,'1','男');

我们插入正确的数据再看看 id，这时候我们发现表里新插入的数据 id 怎么是 4 呢？原因是因为刚才我们虽然在插入数据没有成功，但是它已经向数据库申请到了主键 3 了，所以下一次再申请的时候他会继续从 3 再往后申请，申请到的就是 4。注意：check 检查约束如果不通过，就不会去申请主键。

在这里插入图片描述

演示 check 检查约束效果

insert into user(name, age, status, gender) VALUES('梦里不知身是客2',-1,'1','男');# 会报错 age存在一个检查约束,你的值是一个无效值
insert into user(name, age, status, gender) VALUES('梦里不知身是客2',121,'1','男');# 会报错 age存在一个检查约束,你的值是一个无效值

insert into user(name, age, gender) VALUES('梦里不知身是客2',100,'男');

status 不传，默认是 ‘1’
在这里插入图片描述

图形界面添加约束

通过图形界面来创建表怎么操作？

在这里插入图片描述

点击 OK

演示外键约束

在这里插入图片描述

-- 演示外键
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办');
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12580, '2005-12-05', 1, 1),
       (3, '杨道', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10580, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

执行这些代码。

通过目前的数据我们可以看出来，‘金庸’ 这个员工是属于5号部门，是属于总经办；2、3、4、5、6都是属于1号部门，是属于研发部的。但是目前两个表之间只是逻辑上存在一种关系，并没有设置物理外键，如果没有物理外键，我们无法保证数据的完整性，那么此时也就意味着如果我们直接将1号部门删了，点 “-” 然后需要确认一下这个操作提交确认，就删掉啦。

但是此时员工表中还有这么多数据还关联着一号部门，此时数据就出现了不完整，没有数据库外键关联的情况下是无法保证数据的完整性和一致性的。如果我们想要保证数据的一致性和完整性，我们需要建立外键关联。
在这里插入图片描述

由于我们刚刚把1号部门删掉了，我们先把这个数据给他补完整了。

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

执行完之后我们再次打开emp表，这里出现了一个蓝色的钥匙就说明他已经是一个外键了。
在这里插入图片描述
我们再次去删除1号部门看看能不能直接删除，看一下会发生什么现象？

报错了：告诉我们不能删除或者更新一个父表的记录，因为存在这么一个外键。你要删除id为1的这个父表的记录，此时子表还有这么多条记录关联着这个id为1的这个父表记录，你不能直接删除键。这样就保证了我们数据的一致性和完整性。

在这里插入图片描述

假如这个外键我们不要了，我们来演示删除这个外键。

-- 删除外键
alter table emp drop foreign key fk_emp_dept_id;

在这里插入图片描述

在演示外键的时候我们发现：一旦为emp表的dept_id建立了外键关联之后，我们再去删除父表的数据时，它会去判定当前父表的这条数据在子表中是否存在关联关系，如果存在则不允许删除，这样就保证了数据的完整性。这种行为实际上涉及到外界约束当中的删除和更新行为。

演示外键删除行为
演示cascade
cascade：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录

-- 外键的删除/更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;

添加完外键之后，我们在父表把它 id 改了，把1改为6，然后我们看看会发生什么现象。
我改完之后，此时研发部的id变成了6，emp所关联的这几个数据，他们的外键dept_id也会跟着发生了变化，这就是cascade 的作用。
在这里插入图片描述
如果我们把id为6的研发部现在直接删了会怎么样？
可以看到，如果我们删除父表当中的这条数据时，如果父表的这条数据在子表当中存在外键关联，子表当中的数据也会被删除。这就是cascade级联。

演示set null
把dept、emp表删了重新创建插入数据。

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;

我们删除dept表当中的id为1的数据，emp表所有的dept_id为1的数据全部置为null 了，这个就是set null的作用
在这里插入图片描述

图形界面演示外键约束

在这里插入图片描述

第五章多表查询

多表关系

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

一对多(多对一)
多对多
一对一

一对多

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

多对多

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

create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表';
insert into student values (null,'肉肉','2000100301'),(null,'小梦','2000100302'),(null,'清风','2000100303'),(null,'甘雨','2000100304');

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名'
) comment '课程表';
insert into course values (null,'数据库原理及应用'),(null,'Python语言基础'),(null,'WEB开发技术'),(null,'算法与数据结构');

建立中间表来维护他们之间的关系

create table student_course(
    id int auto_increment primary key comment '主键',
    student_id int not null comment '学生ID',
    course_id int not null comment '课程ID',
    constraint fk_student_id foreign key (student_id) references student(id),
    constraint fk_course_id foreign key (course_id) references course(id)
) comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,2,4);

在这里插入图片描述
此时我们就能看到学生表和课程之间的关系，他们之间是通过一张中间表取得关联，中间表中有两个外键，分别对应学生表主键和课程表主键

一对一

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

create table tb_stu(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    gender char(1) comment '1:男, 2:女',
    phone char(11) comment '电话'
)comment '学生基本信息表';

create table tb_stu_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(20) comment '专业',
    university varchar(50) comment '大学',
    stuid int unique comment '学生id',
    constraint fk_stuid foreign key (stuid) references tb_stu(id)
)comment '用户教育信息表';

insert into tb_stu(name, gender, phone) VALUES
        ('小易','2','12345678912'),
        ('清风','1','12345678666'),
        ('小梦','1','12345678888'),
        ('肉夹馍','1','12345678999');

insert into tb_stu_edu(degree, major, university, stuid) VALUES
        ('本科','应用数学','北京大学',1),
        ('硕士','人工智能科学与技术','清华大学',2),
        ('本科','英语','嘉应学院',3),
        ('本科','表演','北京电影学院',4);

多表查询

多表查询：指从多张表中查询数据

合并查询（笛卡尔积，会展示所有组合结果）：
select * from employee, dept;

笛卡尔积：两个集合 A 集合和 B 集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

消除无效笛卡尔积：
select * from employee, dept where employee.dept = dept.id;

实践

create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办');
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID',
    constraint fk_emp_dept_id foreign key (dept_id) references dept(id)
) comment '员工表';
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (null, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (null, '张无忌', 20, '项目经理', 12580, '2005-12-05', 1, 1),
       (null, '杨道', 33, '开发', 8400, '2000-11-03', 2, 1),
       (null, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (null, '常遇春', 43, '开发', 10580, '2004-09-07', 3, 1),
       (null, '小梦', 43, '开发', 10580, '2004-09-07', 3, 3),
       (null, '肉夹馍', 43, '开发', 10580, '2004-09-07', 3, 3),
       (null, '手抓饼', 43, '开发', 10580, '2004-09-07', 3, 3),
       (null, '张三', 43, '开发', 10580, '2004-09-07', 3, 3),
       (null, '李四', 43, '开发', 10580, '2004-09-07', 3, 2),
       (null, '王五', 43, '开发', 10580, '2004-09-07', 3, 2),
       (null, '清风', 43, '开发', 10580, '2004-09-07', 3, 4),
       (null, '小易', 43, '开发', 10580, '2004-09-07', 3, 4),
       (null, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

在这里插入图片描述

select * from emp,dept;

为什么是70行数据？因为dept表有5个部门，emp表有14个员工，这种现象叫笛卡尔积，5*14 = 70

在这里插入图片描述

select * from emp,dept where emp.dept_id=dept.id;# 消除无效的笛卡尔积

在这里插入图片描述

多表查询分类
- 连接查询
  内连接：相当于查询A、B交集部分数据
  外连接：
  左外连接: 查询左表所有数据，以及两张表交集部分数据
  右外连接: 查询右表所有数据，以及两张表交集部分数据
  自连接: 当前表与自身的连接查询，自连接必须使用表别名
- 子查询

内连接查询

内连接查询的是两张表交集的部分
在这里插入图片描述

隐式内连接：
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

显式内连接：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
select 字段列表 from 表1 join 表2 on 连接条件 ...;

显式性能比隐式高

create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办'),
       (6, '人事部');
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID',
    constraint fk_emp_dept_id foreign key (dept_id) references dept(id)
) comment '员工表';
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (null, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (null, '张无忌', 20, '项目经理', 12580, '2005-12-05', 1, 1),
       (null, '杨道', 33, '开发', 8400, '2000-11-03', 2, 1),
       (null, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (null, '常遇春', 43, '开发', 10580, '2004-09-07', 3, 1),
       (null, '小梦', 43, '开发', 10580, '2004-09-07', 3, 3),
       (null, '肉夹馍', 43, '开发', 10580, '2004-09-07', 3, 3),
       (null, '手抓饼', 43, '开发', 10580, '2004-09-07', 3, 3),
       (null, '张三', 43, '开发', 10580, '2004-09-07', 3, 3),
       (null, '李四', 43, '开发', 10580, '2004-09-07', 3, 2),
       (null, '王五', 43, '开发', 10580, '2004-09-07', 3, 2),
       (null, '清风', 43, '开发', 10580, '2004-09-07', 3, 4),
       (null, '小易', 43, '开发', 10580, '2004-09-07', 3, 4),
       (null, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, null);

在这里插入图片描述

-- 内连接演示
-- 1. 查询每一个员工的姓名,及关联的部门的名称 (隐式内连接实现)
-- 表结构:emp， dept
-- 连接条件: emp.dept_id = dept.id
# select * from emp,dept where emp.dept_id = dept.id;
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

select e.name,d.name from emp e,dept d where e.dept_id = d.id;# 为每一张表取一个别名

-- 2. 查询每一个员工的姓名,及关联的部门的名称(显式内连接实现)
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;
select e.name,d.name from emp e join dept d on e.dept_id = d.id;# inner可以省略

注意：起了别名之后，只能根据别名查询

因为有一个员工没有dept_id，所以查到的是13行数据，而不是14行，这时候这条数据就不属于我们两张表交集部分的内容。
在这里插入图片描述

外连接查询

左外连接：left

查询左表所有数据，以及两张表交集部分数据
在这里插入图片描述
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
select 字段列表 from 表1 left join 表2 on 条件 ...;
相当于查询表 1 的所有数据，包含表 1 和表 2 交集部分数据

右外连接：right

查询右表所有数据，以及两张表交集部分数据
在这里插入图片描述

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
select 字段列表 from 表1 right join 表2 on 条件 ...;
左连接可以查询到没有 dept 的 employee，右连接可以查询到没有 employee 的 dept

-- 外连接演示
-- 1. 查询emp表的所有数据,和对应的部门信息(左外连接)
-- 表结构:emp， dept
-- 连接条件: emp.dept_id = dept.id
select e.*,d.name from emp e left outer join dept d on d.id = e.dept_id;
select e.*,d.name from emp e left join dept d on d.id = e.dept_id;

-- 2. 查询dept表的所有数据,和对应的员工信息(右外连接)
select d.*,e.* from emp e right join dept d on d.id = e.dept_id;
select d.*,e.* from dept d left join emp e on d.id = e.dept_id;# 改左外，与上面效果一样

在这里插入图片描述

自连接查询

当前表与自身的连接查询，自连接必须使用表别名

语法：
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;内连接
select 字段列表 from 表A 别名A left/right join 表A 别名B on 条件 ...;外连接
自连接查询，可以是内连接查询，也可以是外连接查询, 可以是左外当然也可以是右外。

一定要取别名

在这里插入图片描述

-- 自连接演示
-- 1. 查询员工 及其 所属领导的名字
-- managerid
select a.name,b.name from emp a ,emp b where a.managerid=b.id;

-- 2.查询所有员工 emp 及其领导的名字 emp， 如果员工没有领导， 也需要查询出来
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid=b.id;

在这里插入图片描述

联合查询 union, union all

把多次查询的结果合并，形成一个新的查询集

语法：

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

注意事项

UNION ALL 将全部的数据直接合并在一起，会有重复结果；UNION 不会有重复数据，合并之后会数据去重
联合查询比使用 or 效率高，不会使索引失效

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

在这里插入图片描述

# union all ，union
#  将薪资低于 6000 的员工, 和 年龄大于45岁的员工全部查询出来
select * from emp where salary<6000
union all
select * from emp where age>45;


select * from emp where salary<6000
union
select * from emp where age>45;

在这里插入图片描述
注意这个员工重复了，因为他薪资低于 6000 并且年龄大于45岁。

需要对查询的结果去重，不要进行直接进行进行合并，合并后再去重，此时我们把all去掉。

在这里插入图片描述

在这里插入图片描述
报错： The used SELECT statements have a different number of columns
上面查询了八个字段，下面只查询了一个字段，不能合并。

子查询

SQL语句中嵌套select语句，内部的select语句称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为：
- 标量子查询（子查询结果为单个值）
- 列子查询（子查询结果为一列）
- 行子查询（子查询结果为一行）
- 表子查询（子查询结果为多行多列）
根据子查询位置可分为：
- WHERE 之后
- FROM 之后
- SELECT 之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。
常用操作符：= <> > >= < <=

例子：

-- 标量子查询
-- 1.查询"销售部"的所有员工信息
-- (1) 查询"销售部" 部门ID
select id from dept where name = '销售部';
-- (2) 根据销售部的部门ID  查询员工信息
select * from emp where dept_id=4;

select * from emp where dept_id=(select id from dept where name = '销售部');

-- 2. 查询在"常遇春"入职之后的员工信息
-- (1) 查询"常遇春" 的入职时间
select entrydate from emp where name='常遇春';
-- (2) 指定入职日期之后入职的员工信息
select * from emp where entrydate>'2004-09-07';

select * from emp where entrydate>(select entrydate from emp where name='常遇春');

在这里插入图片描述

列子查询

返回的结果是一列（可以是多行）。

常用操作符：

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

例子：
在这里插入图片描述

-- 列子查询
-- 1.查询"销售部"和"市场部"的所有员工信息
# select id from dept where name = '销售部' or name = '市场部';
# select * from emp where dept_id in (2,4);

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

-- 2. 查询比财务部所有人工资都高的员工信息
# select id from dept where name='财务部';
# select salary from emp where dept_id=3;
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='财务部'));

-- 3. 查询比研发部其中 任意一人 工资高的员工信息
# select salary from emp where dept_id=1;
# select salary from emp where dept_id=(select id from dept where name='研发部');

select * from emp where salary > any (
    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=10580 and managerid=3;
# select * from emp where (salary,managerid)=(10580,3);

select * from emp where (salary,managerid)=(select salary,managerid from emp where name='小易');

在这里插入图片描述

表子查询

返回的结果是多行多列
常用操作符：IN

表子查询经常出现在from之后，把表子查询返回的结果作为一张临时表，再和其他表进行联查操作。

例子：
员工表

-- 表子查询
-- 1.查询与“小易”，“宋远桥” 的职位和薪资相同的员工信息
# select job,salary from emp where name='小易' or name='小梦';
select * from emp where (job,salary) in (select job,salary from emp where name='小易' or name='小梦');

-- 2.查询入职日期是“2004-01-01” 之后的员工信息 ， 及其部门信息
# select * from emp where entrydate>'2004-01-01';

select e.*,d.* from (select * from emp where entrydate>'2004-01-01') e left join  dept d on e.dept_id=d.id;
# 在from之后用到了子查询,他会把这个子查询的结果作为一张表来和另一张表进行联查操作

在这里插入图片描述

第六章事务

事务操作

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

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

-- 数据准备
create table account(
    id int auto_increment primary key comment '主键ID',
    name varchar(20) comment '名字',
    money double comment '余额'
)comment '账户表';
insert into account(name, money) VALUES ('小梦',1500),('小易',1500);

-- 恢复数据
update account set money=1500 where name='小梦' or name='小易';

-- 转账(小梦给小易转账500)
-- 1. 查询小梦的余额5
select * from account where name='小梦';

-- 2. 将小梦账户的余额-500
update account set money=money-500 where name='小梦';

程序抛出异常...
-- 3. 小易账户的余额+500
update account set money=money+500 where name='小易';

在这里插入图片描述

这就出现问题了，我们需要把转账操作控制在一个事务范围内。

事务操作

方式一

查看事务提交方式
SELECT @@AUTOCOMMIT;

设置事务提交方式，1为自动提交，0为手动提交，该设置只对当前会话有效
SET @@AUTOCOMMIT = 0;
提交事务
COMMIT;
回滚事务
ROLLBACK;

在这里插入图片描述

select @@autocommit;
set @@autocommit=0;# 设置为手动提交

-- 转账(小梦给小易转账500)
-- 1. 查询小梦的余额5
select * from account where name='小梦';

-- 2. 将小梦账户的余额-500
update account set money=money-500 where name='小梦';

-- 3. 小易账户的余额+500
update account set money=money+500 where name='小易';

commit; # 提交事务

在这里插入图片描述

演示有异常，进行事务回滚。

-- 转账(小梦给小易转账500)
-- 1. 查询小梦的余额5
select * from account where name='小梦';

-- 2. 将小梦账户的余额-500
update account set money=money-500 where name='小梦';

程序抛出异常...
-- 3. 小易账户的余额+500
update account set money=money+500 where name='小易';

rollback; # 一旦出错我们需要回滚事务

在这里插入图片描述

设置为自动提交

set @@autocommit=1;

方式二

开启事务：
START TRANSACTION ; 或 BEGIN [TRANSACTION];
提交事务：
COMMIT;
回滚事务：
ROLLBACK;回滚之后代表当前事务已经结束了。

start transaction;
-- 转账(小梦给小易转账500)
-- 1. 查询小梦的余额5
select * from account where name='小梦';

-- 2. 将小梦账户的余额-500
update account set money=money-500 where name='小梦';

程序抛出异常...
-- 3. 小易账户的余额+500
update account set money=money+500 where name='小易';

-- 提交事务
commit;

-- 一旦抛出异常，回滚事务
rollback;

在这里插入图片描述

事务四大特性 ACID

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

并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

事务隔离级别

并发事务隔离级别：

隔离级别	脏读	不可重复读	幻读
Read uncommitted 读未提交	√	√	√
Read committed（oracle数据库默认）读已提交	×	√	√
Repeatable Read`(mysql默认)` 可重复读	×	×	√
Serializable 串行化	×	×	×

√表示在当前隔离级别下该问题会出现
Serializable 性能最低；Read uncommitted 性能最高，数据安全性最差

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

查看事务隔离级别：
select @@transaction_isolation;
设置事务隔离级别：
set [ session | global ] transcation isolation level {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
SESSION 是会话级别，表示只针对当前会话 (当前客户端窗口) 有效，GLOBAL 表示对所有会话（所有客户端窗口）有效

transaction事务
isolation隔离
level级别

在这里插入图片描述

select @@transaction_isolation;

set session transaction isolation level serializable;
set session transaction isolation level read uncommitted ;
set session transaction isolation level repeatable read;

开两个cmd模拟并发

mysql -u root -p
输入密码
use my_db;

演示脏读问题

-- 演示脏读
set session transaction isolation level read uncommitted;#1
select * from account;#1
start transaction;#1、2
update account set money=money-1000 where name='小梦';#2
select * from account;#1
commit;#1、2

这个就叫做脏读

脏读 : 一个事务读到另一个事务还没提交的数据

在这里插入图片描述
将事务的隔离级别变为read committed，演示解决脏读问题。

-- 演示解决脏读问题
set session  transaction isolation level read committed;#1
start transaction;#1、2
select * from account;#1
update account set money=money-1000 where name='小梦';#2
select * from account;#1
commit;#2
select * from account;#1
commit;#1

在这里插入图片描述

演示不可重复读问题

-- 演示不可重复读
set session  transaction isolation level read committed;#1
start transaction;#开启事务1、2
select * from account;#1
update account set money=money+1000 where name='小梦';#2
select * from account;#1
commit;#2 提交事务
select * from account;#1 查询到变更后的数据，我们发现与上面查询的第一次和第二次数据都不一样
# 同样的sql在一个事务当中查询出来的数据不一致，这个问题我们称之为 不可重复读。
commit;#1

在这里插入图片描述

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同

解决不可重复读

-- 解决不可重复读
set session  transaction isolation level repeatable read ;#1
start transaction;#开启事务1、2
select * from account;#1
update account set money=money+1000 where name='小梦';#2
select * from account;#1
commit;#2 提交事务
select * from account;#1
commit;#1
select * from account;#1

在这里插入图片描述

演示幻读问题

-- 演示幻读问题
set session  transaction isolation level repeatable read ;#1
start transaction;#开启事务1、2
select * from account where id=3;# 1
insert into account(id,name, money) VALUES (3,'肉老师',2000);# 2
commit;# 2
insert into account(id,name, money) VALUES (3,'皮蛋瘦肉粥',2000);# 1 报错：account表当中的主键3重复了
select * from account where id=3;# 1：发现没有
# 查的时候告诉我没有，插入的时候又告诉我有，这个时候出现了幻读
commit;# 1
select * from account where id=3;# 1 发现有

在这里插入图片描述

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

解决幻读问题

serializable 串行化，指的就是我在进行并发事务操作的时候，我只允许一次一个事物来操作，事务1在操作的时候，事物2等只有当事务1提交完成之后，事务2才能够操作。

-- 解决幻读问题
set session transaction isolation level serializable;# 1
start transaction;#开启事务1、2
select * from account where id=4;# 1 没有id=4
insert into account(id,name, money) VALUES (4,'肉老师',2000);# 2: 执行这个sql没有成功，因为光标一直在闪，代表它阻塞了，为什么？
# 因为此时事务1正在操作，事务2就得等，等到事务1执行完成之后把事务提交了，事务2才可以进行操作。
# 所以事务1查询出来之后，去插入没有问题，然后紧接着提交之后，事务2才可以执行，此时执行的时候就告诉他id为4的已经存在了。这样就规避了换补的问题了。
insert into account(id,name, money) VALUES (4,'皮蛋瘦肉粥',2000);# 1 不报错！
select * from account where id=4;# 1
commit;# 1
commit;# 2
select * from account where id=4;# 1