【MySQL】基础入门知识

盐湖城

已于 2024-02-28 11:07:59 修改

阅读量1k

点赞数 22

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-01-17 16:59:58 首次发布

本文链接：https://blog.csdn.net/m0_56963884/article/details/135460582

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、MySQL的数据模型

1.数据库

数据库是用来存储数据的仓库，一个MySQL服务器上可以有多个数据库，数据库中可以存在多个表

2.表

表是MySQL数据库中作为存储结构的二维表，表的结构包含表头和记录，每一行都是一条记录，如下：

id name age
1 张三 18
2 李四 19
3 王五 20
4 老六 21

id	name	age
1	张三	18
2	李四	19
3	王五	20
4	老六	21

3.记录

上面的表中每一行都称为一条记录，例如：

1 张三 18

MySQL服务器可以理解为一个小区，小区里每栋楼都可以理解为一个数据库，而每栋楼中的每一户就可以理解为一个表，每户中的每个居民就可以理解为一条记录，每个居民的名字、年龄、身高等属性就可以理解为是表的字段（表头）

二、SQL

SQL是用来操作数据库的编程语言，我们对数据库进行的增删改查等操作都需要通过SQL来进行

1.SQL基本语法

SQL语句以分号结尾，可以单行或者多行
不区分大小写字母
空格和缩进多个和一个都是一样的效果，可以用来美化SQL结构
注释
- 单行注释：使用--或者#进行单行注释(--注释后面需要跟一个空格)，例如：
```
-- 单行注释
# 单行注释
```
- 多行注释：使用/**/进行多行注释，例如
```
/*
这里是多行注释
第一行
第二行
第三行
*/
```

2.SQL分类

SQL语句分为四类，分别是DDL、DML、DQL、DCL

分类	全称	说明
DDL	Data Definiton Language	数据定义语言，定义数据库对象（数据库、表、字段）
DML	Data Manipulation Language	数据操作语言，操作表中数据进行增删改
DQL	Data Query Language	数据查询语言，查询数据表中数据
DCL	Data Control Language	数据控制语言，创建管理用户、权限等

3.DDL

注意：所有对数据表的操作都需要先使用use 数据库名;将当前数据库切换到对应的数据库

1）数据库操作

1-查询

查询所有数据库：show databases;
查询当前使用的数据库：select dtabase();

2-使用

使用某个数据库：use 数据库名;

3-创建

创建数据库：create database [if not exists] 数据库名称 [default charset 字符集] [collate 排序规则];
可选参数解释：
- if not exists:如果不存在才执行操作
- default charset 字符集:设定数据库的字符集
- collate 排序规则:设定数据库的排序规则

4-删除

删除数据库：drop database [if exists] 数据库名;
可选参数解释：
- if exists:如果存在才执行操作

2）数据表操作

1-查询

查询当前数据库的所有表：show tables;
查询创建表的语句：show create table 表名;
查询表结构：desc 表名;

2-创建

创建数据表：

create table 表名(
	字段1 字段类型 [约束条件] [comment 字段注释],
	字段2 字段类型 [约束条件] [comment 字段注释],
	字段3 字段类型 [约束条件] [comment 字段注释],
	字段4 字段类型 [约束条件] [comment 字段注释],
	...
	字段n 字段类型 [约束条件] [comment 字段注释]
) [comment 表的注释];

字段类型：MySQL中字段类型主要分为三类（数值类型、字符串类型、日期时间类型）
1 数值类型

类型	大小（字节）	范围	说明
tinyint	1	-128~127	有符号小整数
tinyint unsigned	1	0~255	无符号小整数
smallint	2	-32768~32767	有符号大整数
smallint unsigned	2	0~65535	无符号大整数
mediumint	3	-8388608~8388607	有符号大整数
mediumint unsigned	3	0~16777215	无符号大整数
int	4	-2147483648~2147483647	有符号大整数
int unsigned	4	0~4294967295	无符号大整数
bigint	8	-2⁶³~2⁶³-1	有符号极大整数
bigint unsigned	8	0~2⁶⁴-1	无符号极大整数
float	4	-	有符号单精度浮点数
float unsigned	4	-	无符号单精度浮点数
double	8	-	有符号双精度浮点数
double unsigned	8	-	无符号双精度浮点数
decimal	-	-	精确小数（一般用来存储金额）

2 字符串类型

类型	大小（字节）	说明
char	0~255	定长字符串
varchar	0~65535	变长字符串
tinyblob	0~255	二进制短文本数据
tinytext	0~255	短文本字符串
blob	0~65535	二进制长文本数据
text	0~65535	长文本字符串
mediumblob	0~16777215	二进制中等长文本数据
mediumtext	0~16777215	中等长文本字符串
longblob	0~4294967295	二进制超长文本数据
longtext	0~4294967295	超长文本字符串

3 日期时间类型

类型	大小（字节）	范围	格式	说明
date	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
time	3	-888:59:59 至 888: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	混合日期和时间值、时间戳

3-修改

对表结构修改的一般式：
alter table 表名 add|modify|change|rename to ...

add：用于新增字段
- alter table 表名 add 字段名字段类型(长度) [约束条件] [comment 注释];
modify：用于修改字段的类型和约束
- alter table 表名 modify 字段名字段类型(长度) [约束条件];
change：用于修改字段所有属性（包括字段名和注释，等于重新定义该字段）
- alter table 表名 change 旧字段名新字段名字段类型(长度) [约束条件] [comment 注释];
rename to：用于修改表名
- alter table 旧表名 rename to 新表名;

5-删除

删除表有两种形式，第一种是直接删除（DROP），第二种是删除后重新创建（TRUNCATE）

drop：删除整个表
- drop table [if exists] 表名;
truncate：删除表，并重新创建该表
- truncate table 表名;

3）案例

现在有如下需求：某个停车场需要对出入车辆进行登记，请根据下面的要求创建一个合适的数据表
车辆信息包含以下字段：

id：唯一标识
车主名称：字符串类型，长度不超过10位
车主手机号：字符串类型，长度为11位
车辆型号：字符串类型，长度不超过25位
车牌号：字符串类型，长度为7位
入场时间：日期时间类型
离开时间：日期时间类型
停车费用：数值类型

show databases;  # 查看所有库

create database if not exists test_temp default charset utf8mb4;  # 创建数据库test_temp
create database if not exists test_temp2 default charset utf8mb4;  # 创建数据库test_temp2
drop database if exists test_temp2;  # 删除数据库test_temp2

use test_temp;  # 使用数据库test_temp

show databases;

select database();  # 查看当前使用的数据库

-- 创建数据表park_log
create table park_log(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '唯一标识',
	owner_name VARCHAR(10) comment '车主名称',
	owner_phone CHAR(11) comment '车主手机号',
	car_type VARCHAR(25) comment '车辆品牌',
	car_number CHAR(7) comment '车牌号',
	einlass datetime comment '入场时间',
	departure datetime comment '离场时间',
	parking_fees decimal(6,2) comment '停车费用'
) comment '停车记录';

show create table park_log;  # 查看park_log的完整建表语句

show tables;  # 查看当前数据库所有表

desc park_log;  # 查看数据表park_log的表结构

alter table park_log add test int comment '测试添加字段';  # 给park_log表添加一个test字段

desc park_log;

alter table park_log MODIFY test VARCHAR(255);  # 修改park_log的test字段类型

alter table park_log MODIFY test VARCHAR(255) NOT NULL;  # 为park_log的test字段添加非空约束

desc park_log;

ALTER TABLE park_log CHANGE test temp VARCHAR(10);  # 对park_log的test字段进行重定义

desc park_log;

ALTER TABLE park_log DROP temp;  # 删除park_log的temp字段

desc park_log;

ALTER TABLE park_log RENAME TO parking_log;  # 修改表名为parking_log

show tables;

4.DML

DML是对数据表中的记录进行增删改的操作

1）插入数据

插入单条数据：
- insert into 表名(字段1,字段2,...,字段n) values (值1,值2,...,值n);
注意：字段和值一定要对应，比如字段1的值一定要是值1，顺序不能颠倒
- insert into 表名 values (值1,值2,...,值n);
注意：因为没有写字段，所以这样插入必须按顺序赋予所有字段值，不能缺漏，顺序也不能颠倒

批量插入数据：

注意事项同上

insert into 表名(字段1,字段2,...,字段n) 
values 
(值1,值2,...,值n),
(值1,值2,...,值n),
(值1,值2,...,值n),
...;

insert into 表名 
values 
(值1,值2,...,值n),
(值1,值2,...,值n),
(值1,值2,...,值n),
...;

现在我们给前面创建的表插入一些数据：

# 插入语句
INSERT INTO parking_log ( owner_name, owner_phone, car_type, car_number, einlass, departure, parking_fees )
VALUES
	( "张三", "15108269353", "保时捷", "川A66666", "2023-12-05 12:52:48", "2023-12-06 12:30:23", 24 );  # id自增，这里不用写id
	
INSERT INTO parking_log
VALUES
	( 2,"李四", "15108265575", "法拉利", "川A88888", "2023-12-07 10:15:28", "2023-12-07 12:55:44", 3 );  # 全字段插入必须手动给所有字段值
	
# 批量插入
INSERT INTO parking_log ( owner_name, owner_phone, car_type, car_number, einlass, departure, parking_fees )
VALUES
	( "王五", "15108269353", "奔驰", "川A77777", "2023-12-05 12:52:48", "2023-12-06 12:30:23", 24 ),
	( "赵六", "15608269354", "宝马", "川A55555", "2023-12-05 12:52:48", "2023-12-06 12:30:23", 24 ),
	( "周七", "15708269532", "奥迪", "川A44444", "2023-12-05 12:52:48", "2023-12-06 12:30:23", 24 ),
	( "孙八", "15808264553", "大众", "川A33333", "2023-12-05 12:52:48", "2023-12-06 12:30:23", 24 );  # id自增，这里不用写id
	
INSERT INTO parking_log
VALUES
( 7,"吴九", "15108265575", "奇瑞", "川A22222", "2023-12-07 10:15:28", "2023-12-07 12:55:44", 3 ),  # 全字段插入必须手动给所有字段值
( 8,"郑十", "15108265575", "吉利", "川A11111", "2023-12-07 10:15:28", "2023-12-07 12:55:44", 3 ),  # 全字段插入必须手动给所有字段值
( 9,"萧十一", "15108265575", "比亚迪", "川A99999", "2023-12-07 10:15:28", "2023-12-07 12:55:44", 3 ),  # 全字段插入必须手动给所有字段值
( 10,"金十二", "15108265575", "东风", "川A88888", "2023-12-07 10:15:28", "2023-12-07 12:55:44", 3 ),  # 全字段插入必须手动给所有字段值
(11, '金十二', '15108265575', '东风', '川A88888', '2023-12-07 15:15:15', '2023-12-07 20:15:22', 5);  # 全字段插入必须手动给所有字段值

2）修改数据

update 表名 set 字段1=值1,字段2=值2,...,字段n=值n [where 条件];
条件不写就是修改整个表，一定要谨慎！

# 修改id为8/9的数据的owner_phone值
UPDATE parking_log
SET owner_phone = '13688070011'
WHERE id = 8;

UPDATE parking_log
SET owner_phone = '13685548432'
WHERE id = 9;

# 修改id为10的数据的owner_phone值和car_number值
UPDATE parking_log
SET owner_phone = '13688010722',
    car_number  = '川A00000'
WHERE id = 10;

# 添加一个缴费状态字段pay_status
ALTER TABLE parking_log
    ADD pay_status TINYINT DEFAULT 0 comment '缴费状态';

# 将所有数据的缴费状态修改为1
UPDATE parking_log SET pay_status = 1;

3）删除数据

delete from 表名 [where 条件];
条件不写就是删除整个表的数据，一定要谨慎！

# 删除车主名称为金十二的数据
delete from parking_log where owner_name='金十二';

# 删除所有数据
delete from parking_log;

5.DQL

数据准备：公司人员信息库（10w假数据）

1）基本语法

查询关键字：select
别名关键字：as（别名可以用来给字段或表名起别名，可以忽略as直接写在需要别名的字段或表名后面以空格隔开）
去重关键字：distinct
查询句式：

select
	[distinct]
	字段列表
from
	表名列表
where
	查询条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数;

基本查询只需要两个参数，查询字段和表名，如：select 字段 from 表名;
注意：可以使用通配符*表示所有字段

例如：

# 查询staff_info表的所有字段数据
select id,staffid,name,age,gender,id_card,phone,email,work_city,entry_time,`status`,department_id,job_id from staff_info;
select * from staff_info;

2）条件查询

基本式：select 字段列表 from 表名 where 条件;

条件：

比较运算符	功能
>	大于
<	小于
=	等于
>=	大于等于
<=	小于等于
<>或!=	不等于
between … and …	在某个范围内（含最大值、最小值）
in( … )	在in之后的列表中（多选一）
like 占位符	字符串模糊匹配（_表示单个字符，%表示任意字符）
is null	判断是否为null
and 或 &&	逻辑与（表示多个条件同时成立）
or 或 \|\|	逻辑或（表示任意一个条件成立）
not 或 !	逻辑非（表示条件不成立）

例如：

# 查询id为20的人员信息
select id,staffid,name,age,gender,id_card,phone,email,work_city,entry_time,status,department_id,job_id from staff_info where id=20;
select * from staff_info where id=20;

# 查询年龄26岁以内的人员的工号、姓名、年龄、性别
select staffid,name,age,gender from staff_info where age<=26;

# 查询年龄在25-35之间的男性员工的姓名、工作城市
select name,work_city from staff_info where age between 25 and 35 and  gender='男';

# 查询姓尤的员工的姓名、身份证号、手机号、工作城市
select name,id_card,phone,work_city from staff_info where name like '尤%';

# 查询姓名为两个字且名为“鹏”的员工的姓名、身份证号、手机号、工作城市
select name,id_card,phone,work_city from staff_info where name like '_鹏';

3）聚合函数

MySQL中的聚合函数用于对一组数据进行聚合计算，返回一个单一的结果。下面是常用的聚合函数：

COUNT：统计选定列的行数，可以使用COUNT(*)来统计所有行的数目。
SUM：计算选定列的总和。
AVG：计算选定列的平均值。
MAX：找出选定列的最大值。
MIN：找出选定列的最小值。

这些聚合函数在SELECT语句中与GROUP BY子句联合使用，可以对数据进行分组计算，并返回每个组的聚合结果。
例如：

# 统计公司员工总数
select count(*) from staff_info;

# 统计公司员工年龄总和
select sum(age) from staff_info;

# 计算公司的员工平均年龄
select avg(age) from staff_info;

# 找出公司年龄最大是多少
select max(age) from staff_info;

# 找出公司年龄最小是多少
select min(age) from staff_info;

4）分组查询

基本句式：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组结果过滤条件];

where和having的区别

where是在分组之前进行过滤，不满足where条件的不参与分组
having是在分组之后进行过滤，只筛选满足having条件的分组结果

注意：
1.分组查询的执行顺序是：where>聚合函数>having
2.分组查询的字段只能是聚合函数和group by字句中的字段（sql_mode设置为ONLY_FULL_GROUP_BY时）

# 按性别统计员工数量分别是多少
select gender, count(*)
from staff_info
group by gender;

# 查询按性别字段分组的员工年龄最大和最小值是多少
select gender, max(age) as max_age, min(age) as min_age
from staff_info
group by gender;

# 统计每个城市有多少员工
select work_city, count(*) person_num
from staff_info
group by work_city;

# 统计每个城市员工年龄总和
select work_city, sum(age)
from staff_info
group by work_city;

# 统计每个城市员工年龄的平均值
select work_city, avg(age)
from staff_info
group by work_city;

# 统计成都的员工男女数量
select gender, count(*)
from staff_info
where work_city = '成都'
group by gender;

# 找出员工平均年龄小于40的城市
select work_city
from staff_info
group by work_city
having avg(age) < 40;

# 查询在职员工数量大于10000的城市
select work_city, count(*)
from staff_info
where `status` = '在职'
group by work_city
having count(*) > 10000;

5）排序查询

排序查询就是对查询的结果进行排序，排序的关键字是order by，排序规则：升序（asc）和降序（desc）
asc是默认值，可以省略

基本句式：select 字段列表 from 表名 order by 字段1 排序规则,字段2 排序规则,...;

# 按年龄从小到大查询
select *
from staff_info
order by age;

# 按年龄从大到小排序
select *
from staff_info
order by age desc;

# 多级排序：先按入职时间升序，再按名字降序，再按年龄升序
select *
from staff_info
order by entry_time, name desc, age;

6）分页查询

分页查询的关键字是limit，作用是将查询结果分页返回

基本句式：select 字段列表 from 表名 limit 起始索引,每页条数;
参数解释：

起始索引：索引从0开始，索引=(页码-1)*每页条数，索引为0时可以省略该参数
每页条数：每页多少条

注意：第一个参数是索引，不是页码！

# 分页查询，每页10条，第1页的数据
select *
from staff_info
limit 0,10;

select *
from staff_info
limit 10;

# 分页查询，每页10条，第2页的数据
select *
from staff_info
limit 10,10;

# 分页查询，每页10条，第35页的数据(索引=(35-1)*10)
select *
from staff_info
limit 340,10;

7）DQL的执行顺序

DQL语句的编写顺序是：

DQL的执行顺序是：

8）综合案例

根据下面的要求编写SQL语句：
1.查询年龄为21、22、24、25、27的员工信息
2.查询年龄在36-42之间的女性且名字为两个字的员工信息
3.统计表中武汉市的在职男女员工数量
4.查询年龄在35岁以下的员工，将结果按入职时间升序排序，并分页，每页50条，查询第4页信息

# 1.查询年龄为21、22、24、25、27的员工信息
select *
from staff_info
where age in (21,22,24,25,27);

# 2.查询年龄在36-42之间的女性且名字为两个字的员工信息
select *
from staff_info
where age between 36 and 42 and gender='女' and name like '__';

# 3.统计表中武汉市的在职男女员工数量
select gender,count(*)
from staff_info
where work_city='武汉' and `status`='在职'
group by gender;

# 4.查询年龄在35岁以下的员工，将结果按入职时间升序排序，并分页，每页50条，查询第4页信息
select *
from staff_info
order by entry_time
limit 150,50;

6.DCL

对数据库用户以及数据库权限等进行控制

1）用户管理

查询用户：select * from mysql.user;
创建用户：create user '用户名'@'主机名' identified by '密码';
修改密码：alter user '用户名'@'主机名' identified with 认证方式 by '新密码';
- 认证方式：
  （1） mysql_native_password：这是MySQL的原生密码认证插件，使用基于哈希的密码存储和验证方法。这是最常用的认证方式。
  
  （2） caching_sha2_password：这是从MySQL 8.0版本开始引入的一种认证方式。它使用SHA-256算法进行密码加密，并支持更强的安全性。
  
  （3） sha256_password：这是MySQL 5.6版本引入的一种认证方式。它使用SHA-256算法进行密码加密，类似于caching_sha2_password，但在MySQL 8.0版本后已被弃用。
  
  （4） mysql_old_password：这是一个较旧的认证方式，使用基于哈希的密码存储和验证方法。它在MySQL 4.1之前的版本中使用，现在已经被弃用。
  
  （5）authentication_windows_client：这是一种基于Windows身份验证的认证方式，允许通过Windows用户登录MySQL服务器。

请注意，认证方式的可用性和默认方式可能会根据所使用的MySQL版本而有所不同。建议查阅所使用MySQL版本的官方文档以获取准确信息。

删除用户：drop user '用户名'@'主机名';

# 查询所有用户
use mysql;
select *
from user;

# 创建用户test，只允许本地访问，密码设置为test123
create user 'test'@'localhost' identified by 'test123';

# 修改用户test的密码为123456
alter user 'test'@'localhost' identified with mysql_native_password by '123456';

# 删除用户test
drop user 'test'@'localhost';

2）权限控制

下面是MySQL的一些常用权限，详细权限信息请查看官方文档：

权限	描述
SELECT	允许用户查询表中的数据。
INSERT	允许用户向表中插入新的数据。
UPDATE	允许用户更新表中已有的数据。
DELETE	允许用户删除表中的数据。
CREATE	允许用户创建新的数据库或表。
DROP	允许用户删除数据库或表。
ALTER	允许用户修改表的结构。
GRANT OPTION	允许用户将自己拥有的权限授予其他用户。
SHOW DATABASES	允许用户查看所有数据库的列表。
SHOW VIEW	允许用户查看视图的定义。
LOCK TABLES	允许用户锁定表，以便进行特定操作。
RELOAD	允许用户重新加载服务器配置或刷新缓存。
SHUTDOWN	允许用户关闭服务器。
PROCESS	允许用户查看当前正在运行的进程。
FILE	允许用户读取或写入服务器上的文件。
CREATE USER	允许用户创建新的MySQL账户。
REPLICATION SLAVE	允许用户设置MySQL复制从服务器的权限。

一般来说只会用到ALL、SELECT、INSERT、UPDATE、DELETE、ALTER、DROP、CREATE

1-查询权限

基本句式：show grants for '用户名'@'主机名';

2-修改权限

基本句式：grant 权限列表 on 数据库.表名 to '用户名'@'主机名';

3-删除权限

基本句式：revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';

数据库和表名都可以使用通配符*来表示所有库和所有表

# 查询用户root的权限
show grants for 'root'@'localhost';

# 授权company_info.staff_info给用户test的查询、修改、新增权限
grant select,update,insert on company_info.staff_info to 'test'@'localhost';

# 查询用户test的权限
show grants for 'test'@'localhost';

# 撤销test用户的新增和修改权限
revoke update,insert on company_info.staff_info from 'test'@'localhost';

# 查询用户test的权限
show grants for 'test'@'localhost';

# 撤销用户test的所有权限
revoke all on *.* from 'test'@'localhost';

三、函数

函数是一种用于执行特定任务或操作的预定义代码块。它接受一个或多个输入参数，并返回一个结果。常用的函数基本分为四种：字符串函数、数值函数、日期函数、流程控制函数还有之前的聚合函数。

1.字符串函数

MySQL中支持两种正则表达式：POSIX正则表达式和Perl兼容正则表达式（PCRE）。默认是POSIX模式，如果需要使用PCRE模式（我们常用的模式，空白字符用\s表示的，在POSIX中是另外的表示方法，可以自行百度），需要在正则表达式最前面加上一个大写的P，例如：MySQL中'P\\s+'等价于我们普通正则的'\s+'

1）常用的MySQL字符串函数：

函数	描述
CONCAT(str1, str2, …)	将多个字符串连接成一个字符串。
SUBSTRING(str, start, length)	从字符串中提取子字符串。
LENGTH(str)	返回字符串的长度。
LOWER(str)	将字符串转换为小写。
UPPER(str)	将字符串转换为大写。
TRIM(str)	去除字符串两端的空格。
LTRIM(str)	去除字符串左侧的空格。
RTRIM(str)	去除字符串右侧的空格。
REPLACE(str, from_str, to_str)	替换字符串中的子字符串。
REGEXP_REPLACE(str, regex_str, to_str)	正则替换字符串中的子字符串。
LEFT(str, length)	从字符串的左侧截取指定长度的字符。
RIGHT(str, length)	从字符串的右侧截取指定长度的字符。
INSTR(str, substr)	返回子字符串在字符串中第一次出现的位置。
LPAD(str,n,pad)	用字符串pad对str左边进行填充以达到长度n
RPAD(str,n,pad)	用字符串pad对str右边进行填充以达到长度n

2）参数解释：

CONCAT(str1, str2, …)：
- str1,str2,...：要连接的字符串。
SUBSTRING(str, start, length)：
- str：要提取子串的字符串。
- start：子串的起始位置。
- length（可选）：子串的长度，默认到结尾
LENGTH(str)：
- str：要计算长度的字符串。
LOWER(str)：
- str：要转换为小写的字符串。
UPPER(str)：
- str：要转换为大写的字符串。
TRIM(str)：
- str：要修剪的字符串的指定字符。
LTRIM(str)：
- str：要修剪的字符串的指定字符。
RTRIM(str)：
- str：要修剪的字符串的指定字符。
REPLACE(str, from_str, to_str)：
- str：要进行替换操作的字符串。
- from_str：要被替换的字符串。
- to_str：替换成的字符串。
REGEXP_REPLACE(str, regex_str, to_str)：
- str：要进行正则表达式替换的字符串。
- regex_str：正则表达式。
- to_str：替换成的字符串。
LEFT(str, length)：
- str：要截取左侧字符的字符串。
- length：要截取的字符数。
RIGHT(str, length)：
- str：要截取右侧字符的字符串。
- length：要截取的字符数。
LPAD(str,n,pad)：
- str：需要填充的字符串。
- n：整个字符长度。
- pad：填充的字符
RPAD(str,n,pad)：
- str：需要填充的字符串。
- n：整个字符长度。
- pad：填充的字符

3）示例：

# concat
select concat('hello',' ', 'world','!') as '字符串拼接函数';

# substring
select substring('hello world!',7) as '字符串截取函数';
select substring('hello world!',1,5) as '字符串截取函数';

# length
select length('hello world!') as '字符串长度获取函数';

# lower
select lower('Hello') as '字符串转换小写函数';

# upper
select upper('world') as '字符串转换大写函数';

# trim
select trim('   hello world         ') as '去除字符串两侧空格';

# ltrim
select ltrim('   hello world         ') as '去除字符串左侧空格';

# rtrim
select rtrim('   hello world    ') as '去除字符串右侧空格';

# replace
select replace('hello world!','!','?') as '字符串替换函数';

# regexp_replace
select regexp_replace('hello world! 123','P\\s|\\d','?') as '字符串正则替换函数';

# left
select left('hello world!', 5) as '字符串左截取函数';

# right
select right('hello world!', 6) as '字符串右截取函数';

# instr
select instr('hello world!', 'o') as '查找子字符串第一次出现的位置';

# lpad
select lpad('hello', 6,'+') as '左填充函数';

# rpad
select rpad('hello', 6,'+') as '右填充函数';

2.数值函数

1）MySQL中常用的数值函数有：

函数	描述
ABS(x)	求绝对值。
CEIL(x)	向上取整。
FLOOR(x)	向下取整。
ROUND(x, d)	将x四舍五入为d位小数。
TRUNCATE(x, d)	截取x的小数部分保留d位。
MOD(x, y)	取余。
POW(x, y)	返回x的y次方。
SQRT(x)	返回x的平方根。

2）参数解释：

ABS(x)：
- x：要计算绝对值的数字。
CEIL(x)：
- x：要向上取整的数字。
FLOOR(x)：
- x：要向下取整的数字。
ROUND(x, d)：
- x：要进行四舍五入的数字。
- d：保留的小数位数。
TRUNCATE(x, d)：
- x：要截取小数部分的数字。
- d：保留的小数位数。
MOD(x, y)：
- x：要计算余数的被除数。
- y：要计算余数的除数。
POW(x, y)：
- x：要进行幂运算的数字。
- y：幂运算的次数。
SQRT(x)：
- x：要计算平方根的数字。

3）示例：

# abs
select abs(-1314) as '绝对值';

# ceil
select ceil(12.4356) as '向上取整';

# floor
select floor(12.56) as '向下取整';

# round
select round(12.355) as '四舍五入';
select round(12.355, 2) as '四舍五入';

# truncate
select truncate(3.14159265936, 2) as '截取小数位';

# mod
select mod(5,3) as '取余';

# pow
select pow(2,4) as '求方';

# sqrt
select sqrt(4) as '求平方根';

3.日期时间函数

1）常用的MySQL日期时间函数有：

函数	描述
NOW()	返回当前日期和时间。
CURDATE()	返回当前日期。
CURTIME()	返回当前时间。
DATE_FORMAT(date, format)	将日期格式化为指定的格式。
DATE_ADD(date, INTERVAL value unit)	在日期上进行加法操作。
DATE_SUB(date, INTERVAL value unit)	在日期上进行减法操作。
DATEDIFF(date1, date2)	计算两个日期之间的天数差。
DAY(date)	返回日期中的日份。
MONTH(date)	返回日期中的月份。
YEAR(date)	返回日期中的年份。
HOUR(time)	返回时间中的小时。
MINUTE(time)	返回时间中的分钟。
SECOND(time)	返回时间中的秒钟。
DAYNAME(date)	返回日期的星期几名称。
MONTHNAME(date)	返回日期的月份名称。
DAYOFWEEK(date)	返回日期的星期几（1表示星期日，2表示星期一，以此类推）。
WEEK(date)	返回日期的周数。
WEEKDAY(date)	返回日期的工作日（0表示星期一，1表示星期二，以此类推）。

2）参数解释：

NOW()：
- 无参数。
- 返回当前日期和时间。
CURDATE()：
- 无参数。
- 返回当前日期。
CURTIME()：
- 无参数。
- 返回当前时间。
DATE_FORMAT(date, format)：
- date：要格式化的日期。
- format：日期格式的字符串。
DATE_ADD(date, INTERVAL value unit)：
- date：要进行加法操作的日期。
- value：要添加的值。
- unit：添加的单位，如年、月、日等。
DATE_SUB(date, INTERVAL value unit)：
- date：要进行减法操作的日期。
- value：要减去的值。
- unit：减法的单位，如年、月、日等。
DATEDIFF(date1, date2)：
- date1：第一个日期。
- date2：第二个日期。
- 返回两个日期之间的天数差。
DAY(date)：
- date：要提取日份的日期。
- 返回日期中的日份。
MONTH(date)：
- date：要提取月份的日期。
- 返回日期中的月份。
YEAR(date)：
- date：要提取年份的日期。
- 返回日期中的年份。
HOUR(time)：
- time：要提取小时的时间。
- 返回时间中的小时。
MINUTE(time)：
- time：要提取分钟的时间。
- 返回时间中的分钟。
SECOND(time)：
- time：要提取秒钟的时间。
- 返回时间中的秒钟。
DAYNAME(date)：
- date：要获取星期几名称的日期。
- 返回日期的星期几名称。
MONTHNAME(date)：
- date：要获取月份名称的日期。
- 返回日期的月份名称。
DAYOFWEEK(date)：
- date：要获取星期几的日期。
- 返回日期的星期几，其中1表示星期日，2表示星期一，以此类推。
WEEK(date)：
- date：要获取周数的日期。
- 返回日期的周数。
WEEKDAY(date)：
- date：要获取工作日的日期。
- 返回日期的工作日，其中0表示星期一，1表示星期二，以此类推。

3）示例：

# now
select now() as '当前日期时间';

# curdate
select curdate() as '当前日期';

# curtime
select curtime() as '当前时间';

# date_format
select date_format(now(), '%Y/%m/%d %H:%i:%s') as '日期格式化函数';

# date_add
select date_add(curdate(), INTERVAL 7 DAY) as '日期加法函数';

# date_sub
select date_sub(curdate(), INTERVAL 7 DAY) as '日期减法函数';

# datediff
select datediff('2024-01-17', '2023-01-01') as '日期差函数';

# day
select day('2024-01-17') as '提取日份函数';

# month
select month('2024-01-17') as '提取月份函数';

# year
select year('2024-01-17') as '提取年份函数';

# hour
select hour('12:34:56') as '提取小时函数';

# minute
select minute('12:34:56') as '提取分钟函数';

# second
select second('12:34:56') as '提取秒钟函数';

# dayname
select dayname('2024-01-17') as '星期几名称函数';

# monthname
select monthname('2024-01-17') as '月份名称函数';

# dayofweek
select dayofweek('2024-01-17') as '星期几函数';

# week
select week('2024-01-01') as '周数函数';

# weekday
select weekday('2024-01-16') as '工作日函数';

4.流程控制函数

1）MySQL中常用的流程控制函数有：

函数	描述
IF(condition, true_value, false_value)	如果条件成立，则返回true_value，否则返回false_value。
CASE expression WHEN value THEN result [WHEN …] [ELSE else_result] END	根据expression的值匹配WHEN列表中的value，返回对应的result；如果没有匹配，则返回else_result。
CASE WHEN value THEN result [WHEN …] [ELSE else_result] END	根据WHEN列表中的value是否为true，返回对应的result；如果不为true，则返回else_result。
COALESCE(value1, value2, …)	返回第一个非空值。
NULLIF(value1, value2)	如果value1等于value2，则返回NULL，否则返回value1。
IFNULL(value1,value2)	如果value1不为空返回value1，否则返回value2

2）参数解释：

IF(condition, true_value, false_value)：
- condition：要测试的条件。
- true_value：当条件成立时返回的值。
- false_value：当条件不成立时返回的值。
CASE expression WHEN value THEN result [WHEN …] [ELSE else_result] END：
- expression：要匹配的表达式。
- value：匹配的值。
- result：匹配时返回的结果。
- else_result：如果没有匹配，则返回的默认结果。
CASE WHEN value THEN result [WHEN …] [ELSE else_result] END：
- value：表达式（布尔值）。
- result：表达式为true时返回的结果。
- else_result：表达式都不为true时返回的默认结果。
COALESCE(value1, value2, …)：
- value1：要比较的第一个值。
- value2：要比较的第二个值。
- ...：要比较的其他值。
- 返回第一个非空值。
NULLIF(value1, value2)：
- value1：要比较的第一个值。
- value2：要比较的第二个值。
- 如果value1等于value2，则返回NULL，否则返回value1。
IFNULL(value1, value2)：
- value1：要比较的第一个值。
- value2：要比较的第二个值。
- 如果value1是null，则返回value1，否则返回value2。

3）示例：

# if
select if(1 > 2, 'true', 'false') as 'if函数';

# case
select case when 1 + 1 > 2 then '正确' else '错误' end as 'case函数';
select case when 1 + 1 >= 2 then '正确' else '错误' end as 'case函数';
select case 2 + 2
    when 2 then '结果为2'
    when 3 then '结果为3'
    when 4 then '结果为4'
    else '结果未知' end as 'case函数';

# coalesce
select coalesce(null, 1, null, 2, 3) as 'coalesce函数';

# nullif
select nullif(1, 1) as 'nullif函数';
select nullif(2,1) as 'nullif函数';

# ifnull
select ifnull(1,2) as 'ifnull函数';
select ifnull(null,2) as 'ifnull函数';

5.综合案例

1）使用SQL生成一个6位数的随机验证码
2）使用前面查询用到的数据库，查询各个城市的员工入职时间为365天的员工数量，结果按降序排序

# 使用SQL生成一个6位数的随机验证码
select lpad(truncate(rand() * 1000000, 0), 6, 0);

# 使用前面查询用到的数据库，查询各个城市的员工入职时间为365天的员工数量，结果按降序排序
select work_city, count(*) as num
from staff_info
where datediff(curdate(), entry_time) = 365
group by work_city
order by num desc;

四、约束

MySQL的约束是一种用于限制表中数据值的规则或条件。约束可以确保表中的数据符合特定的规则，这有助于保持数据的完整性和一致性。（约束的对象是数据表的列）

约束名	关键字	作用
非空约束	NOT NULL	规定某个字段不允许为空值，如果试图插入一个空值，则会引发错误
唯一约束	UNIQUE	规定某个字段的所有值都必须是唯一的，如果试图插入一个已经存在的值，则会引发错误
主键约束	PRIMARY KEY	规定某个字段是表的主键，它是唯一的并且不允许为空值。每个表只能有一个主键。
外键约束	FOREIGN KEY	规定了某个字段的值必须是另一个表的某个字段的值
默认约束	DEFAULT	规定某个字段的默认值，当插入一条记录时，如果没有显式指定该字段的值，那么将会使用默认值
条件约束	CHECK	用于定义自定义的检查条件，确保插入或更新数据时满足特定的条件（mysql版本8.0.19以上）
自增约束	AUTO_INCREMENT	仅适用于整数类型的字段，它规定了该字段的值自动递增

外键约束（不建议使用，稍微了解即可）：
添加外键约束

建表时添加：
 create table 表名(
 字段名 数据类型
 ...
 [constraint] [外键名称] foreign key(外键字段名) references 父表表名(字段);
);
建表后添加：
 # 设置外键约束
 alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 父表表名(字段);
以下是常见的外键更新和删除行为以及它们的含义：

行为含义
CASCADE 当主表的记录被更新或删除时，从表中相关的记录也会被更新或删除。
SET NULL 当主表的记录被更新时，从表中相关的记录的外键字段将被设置为 NULL。当主表的记录被删除时，从表中相关的记录的外键字段也将被设置为 NULL。
SET DEFAULT 当主表的记录被更新时，从表中相关的记录的外键字段将被设置为默认值。当主表的记录被删除时，从表中相关的记录的外键字段也将被设置为默认值。
RESTRICT 如果尝试更新或删除主表的记录会导致从表中仍存在相关记录的情况，则会阻止更新或删除操作。
NO ACTION 同 RESTRICT，如果尝试更新或删除主表的记录会导致从表中仍存在相关记录的情况，则会阻止更新或删除操作。

这些是常见的外键更新和删除行为选项，用于定义在进行更新或删除操作时外键的行为。具体采用哪种行为取决于您在创建外>>键约束时指定的选项。
请注意，外键的行为可能会因数据库管理系统（如MySQL）的不同而有所差异。因此，在实际使用中，请查阅您所使用的数据>>库管理系统的文档以了解特定情况下的行为和限制。
添加行为的方式：
# 设置外键约束行为
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 父表表名(字段) on [update|delete] 约束行为;

行为	含义
CASCADE	当主表的记录被更新或删除时，从表中相关的记录也会被更新或删除。
SET NULL	当主表的记录被更新时，从表中相关的记录的外键字段将被设置为 NULL。当主表的记录被删除时，从表中相关的记录的外键字段也将被设置为 NULL。
SET DEFAULT	当主表的记录被更新时，从表中相关的记录的外键字段将被设置为默认值。当主表的记录被删除时，从表中相关的记录的外键字段也将被设置为默认值。
RESTRICT	如果尝试更新或删除主表的记录会导致从表中仍存在相关记录的情况，则会阻止更新或删除操作。
NO ACTION	同 RESTRICT，如果尝试更新或删除主表的记录会导致从表中仍存在相关记录的情况，则会阻止更新或删除操作。

下面通过一些例子来测试说明上面的约束：

# 数据准备
create database schools;

use schools;

create table student
(
    id       int primary key auto_increment comment '主键',    # 设置主键约束和自动增长
    name     varchar(20) not null unique comment '姓名',       # 设置非空约束和唯一约束
    age      int check ( age > 0 && age <= 18) comment '年龄', # 设置条件约束
    grade    char(3) default '三年级' comment '年级',             # 设置默认约束
    class_id int comment '班级id'                              # 无约束
) comment '学生信息表';

create table classes
(
    id         int primary key auto_increment comment '主键',
    class_name varchar(10) default '普通班'
) comment '班级信息';

# 初始化数据
insert into classes(class_name)
values ('尖子班'),
       ('火箭班'),
       ('平行班'),
       ('普通班');

insert into student(name, age, class_id)
values ('John1', 9, 1),
       ('John2', 8, 3),
       ('John3', 10, 3),
       ('John4', 9, 2),
       ('John5', 8, 4),
       ('John6', 10, 4),
       ('John7', 8, 1),
       ('John8', 9, 2),
       ('John9', 10, 1);

# 测试非空约束
insert into student(age, class_id) values (9, 1);

# 测试唯一约束
insert into student(name,age, class_id) values ('John1',9, 2);

# 测试条件约束
insert into student(name, age, class_id) values ('John10',20, 2);
insert into student(name, age, class_id) values ('John10',-1, 2);

# 添加外键约束
alter table student add constraint f1 foreign key (class_id) references classes(id);

# 测试外键，删除父表classes数据（默认行为不允许删除）
delete from classes where id=1;

# 删除外键约束
alter table student drop foreign key f1;

# 设置外键约束行为
alter table student add constraint f1 foreign key (class_id) references classes(id) on update cascade on delete set null;

# 测试外键约束行为，修改父表id
update classes set id=5 where id=1;
select * from student;

# 测试外键约束行为，删除父表数据
delete from classes where id=5;
select * from student;

drop database schools;

五、多表查询

数据库还是沿用前面的公司信息库

1.多表关系

多表关系指的是两个或多个表之间的关联关系。这种关系通常通过共享一个或多个相同列的值来建立。这些共享的列被称为“关联列”，它们用于将表之间的数据联系起来。

1）一对多（多对一）

一对多关系是指在数据库中，一个表的一条记录可以对应另一个表中的多条记录。
比如学校里有多个班级，一个班级里有多个学生，而每个学生都只属于一个班级
下面是班级表：

id	班级
1	一班
2	二班
3	三班

下面是学生表：

id	名称	班级id
1	张三	1
2	李四	1
3	王五	1
4	小明	2
5	小红	2
6	小刚	2
7	王刚	3
8	赵静	3
9	刘柳	3

上面的两张表中，我们可以看到，班级表中id为1的学生对应在学生表里是张三、李四、王五，这就是一对多的关系

2）多对多

多对多关系是指在数据库中，两个表之间存在互相关联的关系，每个表的记录可以对应另一个表中的多个记录。以下是一个示例来解释多对多关系：

假设我们有两个表，一个是学生表，一个是课程表，每个学生可以选择多门课程，而每门课程也可以被多个学生选择。

下面是学生表：

学生ID	学生姓名
1	张三
2	李四
3	王五

下面是课程表：

课程ID	课程名称
101	数学
102	英语
103	物理

为了建立多对多关系，我们需要创建一个中间表，通常称为关联表或连接表，用于存储学生和课程之间的关联信息。

下面是关联表，它记录了学生选择的课程：

学生ID	课程ID
1	101
1	103
2	102
3	101
3	102
3	103

通过这个关联表，我们可以看到学生ID为1的学生选择了数学和物理课程，学生ID为2的学生选择了英语课程，学生ID为3的学生选择了数学、英语和物理课程。

这样，学生和课程之间就建立了多对多的关系，一个学生可以选择多门课程，一门课程也可以被多个学生选择。通过关联表，我们可以方便地查询和管理这种多对多的关系。

3）一对一

一对一关系是指在数据库中，一个表的一条记录与另一个表的一条记录相对应。每个记录在两个表中都只有唯一的相关记录。以下是一个示例来解释一对一关系：

假设我们有两个表，一个是员工表，一个是身份证信息表，每个员工只对应一张身份证，而每张身份证也只能对应一个员工。

下面是员工表：

员工ID	姓名
1	张三
2	李四
3	王五

下面是身份证信息表：

身份证号	员工ID
111111	1
222222	2
333333	3

在这个例子中，每个员工在员工表中都有唯一的员工ID，而在身份证信息表中，每个身份证号也只有唯一的记录。通过员工ID和身份证号之间的关联，可以建立一对一的关系。

例如，员工ID为1的员工对应的身份证号是111111，员工ID为2的员工对应的身份证号是222222，员工ID为3的员工对应的身份证号是333333。

这样，员工和身份证信息之间就建立了一对一的关系，每个员工只有一张对应的身份证，每张身份证也只对应一个员工。这种一对一关系可以用于存储和管理相关的个人信息。

2.多表查询

多表查询是指在数据库中，同时查询两个或以上的表，根据不同表之间的关联关系来获取所需的数据结果。多表查询可以从多个表中提取和组合数据，使数据的查询结果更加丰富和有用。
例如我们要查询员工信息和部门信息可以这样写：
select *
from staff_info,
    department_info
where staff_info.department_id = department_info.id;

1）连接查询

1-内连接

内连接是取两张表都符合条件的交集的结果，如图所示，A、B表内连接的结果就是C

隐式内连接：select 表1.字段,表2.字段 from 表1,表2 where 连接条件...;

# 隐式内连接
select name, age, department_name
from staff_info,
     department_info
where staff_info.department_id = department_info.id;

显式内连接：select 表1.字段,表2.字段 from 表1 [inner] join 表2 on 连接条件...;

# 显式内连接
select name, age, department_name
from staff_info
         inner join department_info on staff_info.department_id = department_info.id;

2-外连接

1# 左外连接

左外连接是以左表为准，查询右表符合条件的数据，如下图所示，蓝色为结果区域：

基本句式：select 字段列表 from 左表 left [outer] join 右表 on 条件...;

2# 右外连接

右外连接是以右表为准，查询左表符合条件的数据，如下图所示，蓝色为结果区域：

基本句式：select 字段列表 from 左表 right [outer] join 右表 on 条件...;

3# 举例说明

# 新增一个部门 摸鱼部
insert into department_info (department_name)
VALUES ('摸鱼部');

# 左外连接查询部门职位信息
select d.department_name, j.job_name
from department_info d
         left join job_info j on d.id = j.department_id;
/*
可以看到我们的查询结果是以左表department_info为主的，
我们新增的摸鱼部没有在右表中有对应职位，所以结果为null
*/


# 右外连接查询部门职位信息
select d.department_name, j.job_name
from department_info d
         right join job_info j on d.id = j.department_id;
/*
可以看到我们的查询结果是以右表job_info为主的，
我们在右表中没有职位对应摸鱼部，所以结果不包含摸鱼部在内
*/

3-自连接

MySQL 的自连接是指在同一张表内进行连接操作，将表中的一列与另一列进行匹配。自连接常用于需要对表中的数据进行比较或关联的情况，例如在表中查找具有相似属性或关联关系的记录。
简单来说就是把一张表看做两张相同的表

基本句式：select 字段列表 from 表名 as 别名a [left|right] join 表名 as 别名b on 条件...;

以下是一个示例说明 MySQL 的自连接：

# 创建一个具有自关联属性的表
CREATE TABLE employees
(
    employee_id   INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id    INT,
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);
# 插入数据
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (1, 'John Doe', NULL),
       (2, 'Jane Smith', 1),
       (3, 'Mike Johnson', 2),
       (4, 'Lisa Lee', 2),
       (5, 'Michael Brown', 1),
       (6, 'Karen Johnson', 3),
       (7, 'David Williams', 4),
       (8, 'Jennifer Davis', 4);

# 查询数据表
select *
from employees;

# 使用自连接查询员工名字和领导的名字，不包括领导
select e1.employee_name '员工', e2.employee_name '领导'
from employees as e1
         join employees as e2 on e1.manager_id = e2.employee_id;

2）联合查询

在 MySQL 中，联合查询（Union Query）是一种将多个 SELECT 查询的结果合并成一个结果集的查询方式。联合查询使用 UNION 操作符来组合多个 SELECT 语句的结果，合并后的结果集中不包含重复的行（如需包含重复的行请使用UNION ALL）。
注意：联合查询的前提是每个结果集的字段数量相同，且对应位置的数据类型兼容。这意味着每个 SELECT 子句中选择的列数必须一致，而且每个列的数据类型应该兼容或相同。
如下图所示，类似于取并集：

联合查询的语法如下：

SELECT 字段1, 字段2, ...
FROM 表1
WHERE 条件1
UNION [ALL]
SELECT 字段1, 字段2, ...
FROM 表2
WHERE 条件2
[UNION [ALL]
SELECT 字段1, 字段2, ...
FROM 表3
WHERE 条件3
...];

其中，每个 SELECT 子句表示一个独立的查询，可以指定不同的表和条件。在每个 SELECT 子句中，我们可以选择要查询的列和筛选条件。使用 UNION 操作符将多个 SELECT 子句连接在一起，通过 UNION 关键字将它们分隔开。

UNION 操作符默认去除结果集中的重复行。如果想要包含重复行，可以使用 UNION ALL，它会保留所有的行，包括重复的行。

示例：查询公司信息表中年龄大于55岁和工资低于5000的员工

# 联合查询
select name,age,salary
from staff_info s
    left join job_info j on s.job_id=j.id
where s.age>55
union
select name,age,salary
from staff_info s
    left join job_info j on s.job_id=j.id
where j.salary < 5000;

3）子查询

子查询（Subquery）是指在一个 SQL 查询中嵌套另一个完整的 SELECT 查询
例如：

# 在where字句中使用子查询
SELECT column1, column2, ...
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

# 在from字句使用子查询
SELECT column1, column2, ...
FROM (SELECT column1, column2 FROM table1) AS subquery
WHERE condition;

# 在select字句中使用子查询
SELECT column1, column2, (SELECT MAX(column3) FROM table2) AS max_value
FROM table1;

子查询根据查询结果的不同可以分为四类：

标量子查询：只有一行一列
列子查询：只有一列
行子查询：只有一行
表子查询：多行多列

1-标量子查询

子查询返回的结果是单个值的子查询
常用操作符：>、=、<、>=、<=、!=

示例：

# 标量子查询

# 查询入职时间在伍晨之后的所有员工
-- 1.查询伍晨的入职时间
select entry_time
from staff_info
where name = '伍晨';
-- 2.查询时间在伍晨入职时间之后的所有员工
select *
from staff_info
where entry_time > (select entry_time from staff_info where name = '伍晨');

# 查询职位是初级销售的员工信息
select *
from staff_info
where job_id = (select id from job_info where job_name = '初级销售');

2-列子查询

子查询返回结果是单列的子查询
常用的操作符：in、not in、any、some、all

操作符	说明
IN	在查询结果的范围内
NOT IN	不在查询结果的范围内
ANY	在子查询结果中，任意一个满足
SOME	与ANY等同
ALL	子查询返回的结果列表每一个都必须满足

示例：

# 列子查询

# 查询年龄在25-35之间的部长有哪些
-- 1.查询所有部门的部长名称
select leader
from department_info;
-- 2.查询年龄在25-35之间的所有部长（假数据存在重复数据，不用在意）
select name, age
from staff_info
where age between 25 and 35
  and name in (select leader from department_info);

# 查询部门下没有职位的部门
select department_name
from department_info
where id not in (select distinct department_id from job_info);

# 查询职位工资比网络部其中任意一个职位工资高的职位
select job_name
from job_info
where salary > any (select salary
                    from job_info
                    where department_id = (select id from department_info where department_name = '网络部'));

select job_name
from job_info
where salary > some (select salary
                    from job_info
                    where department_id = (select id from department_info where department_name = '网络部'));

# 查询职位工资比后勤部所有职位工资都高的职位
select job_name
from job_info
where salary > all (select salary
                    from job_info
                    where department_id = (select id from department_info where department_name = '后勤部'));

3-行子查询

子查询返回结果是单行的子查询
常用的操作符：in、not in、=、!=

示例：

# 行子查询

# 查询与焦军在同一个城市和部门的员工信息
select *
from staff_info
where (work_city, department_id) = (select work_city, department_id from staff_info where name = '焦军');

# 查询与伍晨的工资与部门相同的员工信息（emmm....可以不看这个例子）
select *
from staff_info
where (department_id, (select salary from job_info where id = job_id)) =
      (select department_id, (select salary from job_info where id = t1.job_id)
       from (select department_id, job_id from staff_info where name = '伍晨') as t1);

4-表子查询

子查询返回结果是多行多列的子查询
常用的操作符：in

表子查询可以看做一个临时表，当做临时表用时必须要取别名，如果只作为多行多列的数据集则不需要别名，需要对其进行select就需要别名

# 表子查询

# 查询与销售部或者研发部的职位等级与工资相同的职位
-- 1.查询销售部或研发部的职位等级和工资
select distinct level, salary
from job_info
where department_id in (select id from department_info where department_name in ('销售部', '研发部'));

-- 2.查询职位
select job_name
from job_info
where (level, salary) in (select distinct level, salary
                          from job_info
                          where department_id in
                                (select id from department_info where department_name in ('销售部', '研发部')));


# 查询25-35岁之间的员工，选出部门id为6的员工
select *
from (select * from staff_info where age between 25 and 35) as t
where department_id = 6;

六、事务

事务的演示请使用两个窗口执行，这样才能看出不同的事务查询结果的区别

1. 事务的概念

事务是指一组被视为单个操作单元的数据库操作。事务可以由一个或多个数据库操作组成，这些操作要么全部执行成功，要么全部回滚。

例如：我们有一个银行表，里面存储了存款人的名字和存款，现在张三要借给李四200块钱，这个过程是

这两步操作合起来就是一个事务

# 使用公司信息库
use company_info;

# 创建一个银行表
create table bank
(
    id     int primary key auto_increment,
    cardId char(6) default '000000',
    name   varchar(10)    not null,
    money  decimal(12, 2) not null
) comment '银行';

# 添加几条存款信息
insert into bank (cardId, name, money)
values (lpad('1', 6, 0), '张三', 10000),
       (lpad('2', 6, 0), '李四', 10000),
       (lpad('3', 6, 0), '王五', 20000);

# 张三借给李四200
-- 1.张三-200
update bank
set money=money - 200
where name = '张三';
-- 2.李四+200
update bank
set money=money + 200
where name = '李四';
-- 3.查看结果
select *
from bank;

2. 事务的操作

在数据库中，事务的操作包括以下几个关键步骤：

开始事务（BEGIN或START TRANSACTION）：标识事务的开始。
执行数据库操作：包括插入、更新、删除等操作语句。
判断操作结果：根据操作的执行结果判断是否继续或回滚事务。
提交事务（COMMIT）：如果所有操作都成功执行，则提交事务，将更改持久保存到数据库中。
回滚事务（ROLLBACK）：如果任何操作失败或发生错误，回滚事务，撤销已执行的操作。

1）通过关闭mysql自动提交来开启事务

mysql中每个操作都可以看做一个事务，mysql客户端默认开启了事务自动提交，我们把事务自动提交关闭可以看到，在语句执行的过程中，没提交之前的结果不会影响数据表本身：

# 查看事务自动提交模式是否开启
select @@autocommit;
# 关闭mysql的事务自动提交
set @@autocommit = 0;

# 恢复丢失部分
update bank
set money=money + 200
where name = '李四';

-- 没提交事务之前（新窗口执行操作）
select * from bank;

-- 提交事务
commit;

-- 提交事务之后（新窗口执行操作）
select * from bank;

对于没有提交的事务，在事务执行过程中如果出错，我们可以使用rollback;进行事务回滚操作，保证数据的完整性：

# 李四还给张三200，中途发生异常
-- 1.李四-200
update bank
set money=money - 200
where name = '李四';
-- 2.新增用户老六出错
insert into bank (cardId, name)
values (lpad('3', 6, 0), '老六');
-- 3.张三+300
update bank
set money=money + 200
where name = '张三';

-- 查看结果，两百块丢失(当前窗口执行)
select *
from bank;

-- 查看结果，未改变，因为事务没有提交生效(新窗口执行)
select *
from bank;

-- 回滚事务
rollback;
	
-- 查看结果，两百块回来了(当前窗口执行)
select *
from bank;

# 恢复自动提交
set @@autocommit=1;

2）手动创建事务

通过关键字start transaction;或begin;可以手动开启一个事务，直到执行了commit或者rollback之后才会结束事务。

# 手动开启事务
start transaction;

# 李四还给张三200，中途发生异常
-- 1.李四-200
update bank
set money=money - 200
where name = '李四';
-- 2.新增用户老六出错
insert into bank (cardId, name)
values (lpad('3', 6, 0), '老六');
-- 3.张三+300
update bank
set money=money + 200
where name = '张三';

-- 查看结果，两百块丢失（当前窗口）
select *
from bank;

-- 查看结果，无变化，事务未提交（新窗口）
select *
from bank;

# 提交事务（为了保证事务的完整性，是不能在报错的时候提交事务的）
# commit;

# 回滚事务（事务执行出错，为保证数据完整性和一致性，执行回滚操作）
rollback;

-- 查看结果，金额恢复，事务执行过程中出现问题，回滚修改操作到未修改之前（当前窗口）
select *
from bank;

-- 查看结果，无变化，事务执行未执行成功，所有操作都被回退（新窗口）
select *
from bank;

3. 事务的四大特性

事务具有以下四个重要特性，通常简称为ACID（Atomicity、Consistency、Isolation、Durability）：

原子性（Atomicity）：一个事务被视为不可分割的最小执行单位，要么全部成功执行，要么全部回滚。不存在中间状态。
一致性（Consistency）：事务将数据库从一个一致状态转换为另一个一致状态，确保数据完整性和约束条件的有效性。
隔离性（Isolation）：一个事务的执行不会受其他事务的干扰，每个事务都感觉自己在独立地操作数据库。
持久性（Durability）：一旦事务提交，其所做的修改将永久保存在数据库中，即使系统发生故障也不会丢失。

4. 事务并发问题和隔离级别

1）事务并发的问题

事务并发执行可能会引发以下三种问题：脏读（Dirty Read）、不可重复读（Non-repeatable Read）和幻读（Phantom Read）。

问题	描述	示例
脏读（Dirty Read）	一个事务读取了另一个事务未提交的数据，如果这个事务在后续操作中回滚了，则读取的数据将变得无效并且不一致。	事务A读取了事务B未提交的数据，并使用该数据执行操作，如果事务B在后续操作中回滚，则事务A所执行的操作将是基于无效数据的，导致数据不一致。
不可重复读（Non-repeatable Read）	在同一事务中，相同的查询语句多次执行返回的结果不同。通俗地讲，就是在一个事务内，读取到了其他事务已提交的数据更新操作，导致多次读取同一数据时结果不一致。	事务A读取了某行数据，并在后续操作中对该数据进行了修改，在此期间事务B也读取了该行数据并进行了修改并提交，此时事务A再次读取该行数据时获得的数据与之前的不同。
幻读（Phantom Read）	在一个事务中，相同的查询语句多次执行返回的结果不同，并且结果集中出现了新的数据行。通俗地讲，就是在同一事务内，读取到了其他事务已提交的插入操作，导致同一查询语句多次执行时结果集不一致。	事务A读取了一组数据，并在后续操作中对其中的数据进行了修改，在此期间事务B也向该表中插入了一行数据，此时事务A再次读取该组数据时获得的结果集比之前的更多。

举例说明：

脏读：
- 脏读（Dirty Read）是数据库中的一种并发控制问题。它指的是一个事务读取了另一个未提交事务所写入的数据，而后者最终可能会回滚，导致前者读取到了不正确、不一致的数据。
  
  让我们通过一个例子来说明脏读问题。假设有两个并发的事务，分别是事务A和事务B。事务A执行如下操作：
```
BEGIN;
UPDATE users SET Age = 30 WHERE ID = 1;
```
  在事务A执行更新操作的同时，事务B读取该记录：
```
BEGIN;
SELECT * FROM users WHERE ID = 1;
```
  此时，事务B读取到的结果是被事务A修改前的数据，即年龄为20岁的记录（Alice）。然而，事务A在此之后决定回滚事务并撤销更新操作：
```
ROLLBACK;
```
  现在，事务B再次读取相同的记录：
```
SELECT * FROM users WHERE ID = 1;
```
  此时，事务B读取到的结果变成了回滚前的数据，即年龄为20岁的记录，而实际上事务A已经撤销了对该记录的修改。这就是脏读问题，因为事务B读取到了未提交的、不正确的数据。
不可重复读：
- 假设有一个数据表格，其中包含以下记录：
  
  ID Name Age
  1 Alice 20
  2 Bob 25
  现在有两个并发的事务，分别是事务A和事务B。事务A执行如下SQL语句：
```
BEGIN;
SELECT * FROM users WHERE ID = 1;
```
  此时，事务A读取到的结果是ID为1的记录（Alice）。
  
  接着，事务B修改了ID为1的记录的年龄：
```
BEGIN;
UPDATE users SET Age = 22 WHERE ID = 1;
COMMIT;
```
  然后，事务A再次执行相同的SELECT语句：
```
SELECT * FROM users WHERE ID = 1;
```
  此时，事务A读取到的结果变成了修改后的记录，即年龄变为22岁的Alice。这导致了不可重复读的问题，因为在同一个事务内，事务A多次读取同一条记录时得到的结果不一致。
幻读：
- 假设有一张数据表格，其中包含了以下记录：
  
  ID Name Age
  1 Alice 20
  2 Bob 25
  3 Carol 30
  现在有两个并发的事务，分别是事务A和事务B。事务A执行如下SQL语句：
```
BEGIN;
SELECT COUNT(*) FROM users WHERE age > 25;
```
  此时，事务A读取到的结果是1，即年龄大于25岁的用户只有一位（Carol）。
  
  接着，事务B在这之后插入了一条新的记录：
```
BEGIN;
INSERT INTO users (Name, Age) VALUES ('Dave', 27);
COMMIT;
```
  然后，事务A再次执行上述SELECT语句：
```
SELECT COUNT(*) FROM users WHERE age > 25;
```
  此时，事务A读取到的结果变成了2，即年龄大于25岁的用户有两位（Carol和Dave），发生了幻读问题。

ID	Name	Age
1	Alice	20
2	Bob	25

ID	Name	Age
1	Alice	20
2	Bob	25
3	Carol	30

2）隔离级别

为了解决并发问题，数据库定义了多个隔离级别，用于控制事务之间的相互影响程度。常见的隔离级别包括读未提交、读已提交、可重复读和串行化。选择合适的隔离级别需要根据具体应用场景和对数据一致性要求的权衡。

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	可能发生	可能发生	可能发生
读已提交（Read Committed）	不会发生	可能发生	可能发生
可重复读（Repeatable Read）	不会发生	不会发生	可能发生
串行化（Serializable）	不会发生	不会发生	不会发生

读未提交（Read Uncommitted）：
- 功能：事务可以读取其他事务未提交的数据，即可以读取到其他事务中尚未持久化到数据库的数据。
- 特点：最低的隔离级别，事务之间相互干扰最大，可能会发生脏读、不可重复读和幻读问题。
- 示例：事务A读取了事务B尚未提交的数据并使用该数据进行操作，但如果事务B在后续操作中回滚，则事务A所执行的操作将基于无效数据，导致数据不一致。
读已提交（Read Committed）：
- 功能：事务只能读取已经提交的数据，即只能读取到其他事务已经持久化到数据库的数据。
- 特点：避免了脏读问题，但仍可能发生不可重复读和幻读问题。
- 示例：事务A读取了某行数据，并在后续操作中对该数据进行了修改。在此期间，事务B也读取了同一行数据并进行了修改并提交。此时，如果事务A再次读取该行数据，会发现结果与之前不一致，出现了不可重复读的问题。
可重复读（Repeatable Read）（默认隔离级别）：
- 功能：保证同一事务中多次读取同一数据得到的结果是一致的，即事务中的查询结果不受其他事务的影响。
- 特点：避免了脏读和不可重复读问题，但仍可能发生幻读问题。
- 示例：事务A读取了一组数据，并在后续操作中对其中的数据进行了修改。在此期间，事务B向该表中插入了一行数据。此时，如果事务A再次读取该组数据，会发现结果集比之前的更多，出现了幻读问题。
串行化（Serializable）：
- 功能：最高的隔离级别，所有事务串行执行，确保每个事务独立执行，避免了所有并发问题。
- 特点：可以解决脏读、不可重复读和幻读问题，但可能导致性能下降，因为只允许一个事务在同一时间对数据进行读写。
- 示例：
  
  假设有两个事务同时对同一个银行账户进行操作：事务1想要查询账户余额并转账，事务2想要查询账户余额并修改账户信息。
  
  使用串行化隔离级别可以确保这两个事务不会产生并发冲突，并且保证数据的一致性。具体步骤如下：
  1. 开启事务1：START TRANSACTION;
  2. 开启事务2：START TRANSACTION;
  3. 事务1查询账户余额：SELECT balance FROM accounts WHERE account_id = '123' FOR UPDATE;
  4. 事务2查询账户余额：SELECT balance FROM accounts WHERE account_id = '123' FOR UPDATE;
  5. 事务1根据查询结果进行转账操作，并更新账户余额：UPDATE accounts SET balance = balance - 100 WHERE account_id = '123';
  6. 事务2根据查询结果修改账户信息，例如修改账户名称：UPDATE accounts SET account_name = 'New Name' WHERE account_id = '123';
  7. 事务1提交事务：COMMIT;
  8. 事务2提交事务：COMMIT;
  在串行化隔离级别下，事务1和事务2会依次执行，因此不会出现并发冲突的情况。即使事务2在事务1之后开始，由于使用了串行化隔离级别和FOR UPDATE语句，事务2会等待事务1提交后才执行，并且在查询账户余额时会对相应的行进行加锁，从而避免了并发冲突。这样可以确保账户余额和账户信息的一致性。