数据库操作

创建数据库

1
2
3
4
5
create database [if not exists] 数据库名称 
    [default character set 默认字符集名称]
    [default collate 默认排序规则名称]
    [character set 字符集名称]
    [collate 排序规则名称];
参数简介
[default character set 默认字符集名称]创建表时指定的默认字符集
如果未指定字符集，则使用数据库的默认字符集。
[default collate 默认排序规则名称]创建表时指定的默认排序规则
如果未指定排序规则，则使用默认排序规则。
[if not exists]只有当数据库不存在时才会创建新的数据库
如果已经存在该数据库，则不执行任何操作。
[character set 字符集名称]指定数据库的默认字符集
如果未指定字符集，则使用服务器的默认字符集。
[collate 排序规则名称]指定与数据库及其对象关联的默认排序规则(例如字符串排序)
如果未指定排序规则，则使用默认排序规则。

使用数据库

1
2
# 选择使用指定的数据库
use 数据库名称

查询数据库

1
2
3
4
5
6
7
8
9
# 查看当前数据库服务器上的所有可用数据库
show databases;

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

# 查看指定数据库中指定表的列信息
show columns form 表名 from 数据库名;
show columns from 数据库名.表名;

修改数据库

1
2
3
4
5
6
7
alter database 数据库名称 
    [default character set 默认字符集名称]
    [default collate 默认排序规则名称]
    [character set 字符集名称]
    [collate 排序规则名称]
    [旧数据库名称 to 新数据库名称]
    [upgrade data directory name 文件夹名称];
参数说明
[default character set 默认字符集名称]指定数据库新的默认字符集。
[default collate 默认排序规则名称]指定数据库新的默认排序规则。
[character set 字符集名称]指定数据库新的字符集。
[collate 排序规则名称]指定数据库新的排序规则。
[旧数据库名称 to 新数据库名称]重命名数据库为新的名称。
[upgrade data directory name 文件夹名称]在将数据库从旧版本升级到新版本时 指定 MySQL 数据目录中的数据文件夹名称

删除数据库

1
2
# 删除数据库，如果不存在则删除不成功
drop database [if exists] 数据库名称；

数据表操作

查询表

1
2
3
4
5
6
7
8
# 查询数据库中的表
show tables
# 查询某个表的建表语句
show create table 表名;
# 查看指定表的结构信息（方式一）
describe 表名 [列名]
# 查看指定表的结构信息（方式二）
desc 表名 [列名]

查询出的表结构信息的字段解释

字段介绍
Field表示字段名称
Type表示字段类型
Null表示该列是否可以存储NULL值
Key表示 该列是否为索引（PRI 表示主键，UNI 表示唯一索引，MUL 表示普通索引）
Default表示该列是否有默认值
Extra表示额外信息，例如 AUTO_INCREMENT 自增等

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table [if not exists]表名称 (
	字段1, 数据类型 [字段约束条件] [default 字段默认值] [comment "字段注释"] [collate 排序规则名称],
	字段2, 数据类型 [字段约束条件] [default 字段默认值] [comment "字段注释"] [collate 排序规则名称],
	字段3, 数据类型 [字段约束条件] [default 字段默认值] [comment "字段注释"] [collate 排序规则名称],
	……
	[[constraint 约束名称] primary key(主键约束的字段名称),]
	[[constraint 约束名称] foreign key(外键约束的字段名称) references 主表名称(主表中的字段名称)[on 级联方式],]
	[[constraint 约束名称] not null(非空约束的字段名称),]
	[[constraint 约束名称] unique(唯一约束的字段名称),]
	[[constraint 约束名称] default 默认约束的默认值,]
	[[constraint 约束名称] auto_increment(自增约束的字段名称),]
	[[constraint 约束名称] check(检查约束的检查条件),]
	
	[primary key 索引名称(主键索引的列名称[(length)] [asc/desc]),]
	[unique 索引名称(唯一索引的列名称[(length)] [asc/desc]),]
	[index 索引名称(普通索引的列名称[(length)) [asc/desc],]
	[fulltext 索引名称(全文索引的列名称[(length)] [asc/desc]),]
	[spatial 索引名称(空间索引的列名称[(length)] [asc/desc])]
)[comment "表注释"] [default character set = '字符集'] [engine = 存储引擎] [collate 排序规则名称];

修改表

完整语法

1
2
3
4
5
6
7
8
alter table 表名称
[
    add [column] 列名称 数据类型 [列属性],
    modify [column] 列名称 数据类型 [列属性],
    change [column] 原列名称 新列名称 数据类型 [列属性],
    drop [column] 列名称,
    rename [to] 新表名称
] [ ENGINE = 存储引擎名称 ]

常用语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 添加主键约束
alter table 表名 add primary key(列名1,列名2)

# 添加索引
alter table 表名 add index 索引名称 (列名)

# 添加一列并指定默认值
alter table 表名 add 列名 数据类型 default 默认值

# 修改表的存储引擎
alter table 表名 engine = 存储引擎名称

# 修改表中某一列的数据类型
alter table 表名 modify 列名 新数据类型

# 修改表中某一列的默认值
alter table 表名 alter 列名 set default 默认值

# 修改表中某一列的名称和数据类型
alter table 表名 change 列名 新列名 新数据类型

# 删除列
alter table 表名 drop 列名

# 删除列的默认值
alter table 表名 alter 列名 drop default

# 删除主键约束
alter table 表名 drop primary key

# 删除指定的索引
alter table 表名 drop index 索引名称

# 重命名表
alter table 表名 rename to 新的表名

# 修改表的存储引擎
alter table 表名 ENGINE = 存储引擎名称

删除表

1
drop table [if exists] 表名称

复制表

如果只需要复制表的结构，则使用 LIKE 子句创建新表可能是最好的选择；

如果需要完全复制表及其数据，则使用 SELECT 子句创建新表可能更合适

方式一：like方式

1
2
# 复制表结构到新表（备注、索引、主键外键、存储引擎等）
create table [if not exists] 新表名 like 源表名

方式二：select方式

1
2
# 复制表结构及数据到新表
create table [if not exists] 新表名 as (select 字段1,字段2… from 源表名)

方式三：insert方式

1
2
# 从源表中选择数据，并将其插入到目标表中
insert into 目标表 select 字段1,字段2… from 源表名;

三种方式对比

方法简介适用场景
like方式复制源表结构，不复制数据，无法复制主键、外键限制和约束等非字段信息只需复制表结构，不需复制数据
select方式复制源表结构和数据，可能会占用大量系统资源，在复制大表时可能出现内存不足的问题需要完全复制表及其数据
insert方式需手动创建目标表，然后插入源表中的数据，无法复制索引、主键或其他表级别的限制和约束需要复制表及其中的数据

数据的增删改

添加数据

插入单条数据

1
2
3
4
# 方式一
insert into 表名 (列名1, 列名2, ......) values (值1, 值2, ......);
# 方式二
insert into 表名 values (值1,值2,......);

插入多条数据

1
2
3
4
# 方式一
insert into 表名(列名1, 列名2, ......) values (值1,值2,......), (值1,值2,......), (值1,值2,......) ......;
# 方式二
insert into 表名 values (值1,值2,......),(值1,值2,......),(值1,值2,......)......;

修改数据

1
2
# 修改表数据，如果不加条件，会将所有数据都修改
update 表名 set 列名1=值1,列名2=值2,.....[where 条件];

删除数据

1
2
# 删除表数据，如果不加条件，则将所有数据都删除
delete from 表名 [where 条件];

数据的查询

完整查询语法结构

SQL执行顺序：from→on→join→where→group by→agg_func→with→having→select→distinct→order by→limit

  1. FROM：指定查询的数据源，即表名或视图名。
  2. ON：指定连接条件，用于连接多个表。
  3. JOIN：根据连接条件，将多个表的数据进行关联。内连接（INNER）、左外连接（LEFT）、右外连接（RIGHT）
  4. WHERE：指定查询的条件，只有符合条件的行才会被返回。
  5. GROUP BY：按照指定的列或表达式对数据进行分组。
  6. AGG_FUNC：对每个组进行聚合操作。
  7. WITH：可选WITH CUBE或WITH ROLLUP，在 GROUP BY 子句中使用，生成更多的汇总行。
  8. HAVING：类似于 WHERE，但是作用于已经分组的结果集，可以使用聚合函数进行筛选。
  9. SELECT：选择要返回的列或计算的表达式。
  10. DISTINCT：去除重复的行。不指定默认为all，表示返回所有行
  11. ORDER BY：按照指定的列对结果集进行排序。升序排序（ASC）或者降序排序（DESC）。
  12. LIMIT：限制返回记录的数量。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select [all / distinct] 
	聚合函数/字段列表
from
	表名称1
	 [left / right / inner] join 表名称2 on 多表的连接条件
	……
where
	条件表达式，建议不包含聚合函数的过滤条件
group by 
	分组字段 [with cube / with rollup]
having
	分组后的条件表达式，建议包含聚合函数的过滤条件
order by
	排序字段[asc / desc]
limit
	[查询的起始行,]每页显示的条数

基础查询（select）

SELECT 语法

1
2
3
4
select [distinct / all] 
	聚合函数/字段列表
from 
	表名称

SELECT 基本使用

1
2
3
4
5
6
7
8
# 查询所有数据（列名和列表可以用*代替，但是不方便看查询的数据，尽量不要使用）
select * from 表名;

# 查询多个字段，默认情况下，查询会返回全部行，包括重复行
select 字段列表 from 表名;

# 去重查询，去除重复记录查询（DISTINCT 需要放到所有列名的前面）
select distinct 字段列表 from 表名;

SELECT 结合聚合函数使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 统计班级一共有多少个学生（count统计的列名不能为空）
select count(id) from stu
select count(name) from stu
select count(*) from stu

# 查询数学成绩的最高分
select max(math) from stu

# 查询数学成绩的最低分（如果数学成绩中有null值，不会参与运算）
select min(math) from stu

# 查询数学成绩的总分
select sum(math) from stu

# 查询数学成绩的平均分
select avg(math) from stu

条件查询（where）

WHERE 子句语法

1
2
3
4
5
6
select [distinct / all] 
	聚合函数/字段列表
from
	表名称
where
	条件表达式，建议不包含聚合函数的过滤条件
常见的条件表达式简介
比较运算符如=、<、>、<=、>=、!=等，用于比较两个值是否相等或大小关系。
逻辑运算符如AND、OR、NOT等，用于组合多个比较条件。
IN运算符用于匹配某个值是否在指定的一组值之中
LIKE运算符用于匹配字符串是否符合指定的模式，其中%表示任意长度的字符串，_表示单个字符
BETWEEN运算符用于匹配某个值是否在指定的范围之内
IS NULL和IS NOT NULL运算符用于匹配空值或非空值。
EXISTS和NOT EXISTS运算符用于检查与子查询中是否存在数据行
ANY和ALL运算符用于将子查询的结果集与主查询进行比较
CASE表达式类似于switch-case语句，用于根据条件返回不同的结果

WHERE 子句使用比较运算符

在 WHERE 子句中，可以包含多个条件表达式，可以使用常用的条件运算符（如=、>、<、<>、>=、<=等）来组合不同的条件，以满足查询的要求

1
2
3
4
5
6
7
8
9
10
11
12
# 查询年龄 大于 18岁的学员信息
select * from stu where age > 18;

# 查询年龄 大于等于 18岁的学员信息
select * from stu where age >= 18;

# 查询年龄等于18岁的学员信息
select * from stu where age = 18;

# 查询年龄不等于18岁的学员信息
select * from stu where age != 18;
select * from stu where age <> 18;

WHERE 子句使用逻辑运算符

在 WHERE 子句中，可以包含多个条件表达式，可以使用逻辑运算符（如 AND、&&、OR、NOT 等）将这些条件连接起来，以实现更为复杂的查询需求

1
2
3
4
5
6
7
8
9
10
11
# 查询年龄 大于等于20岁 并且 年龄 小于等于 30岁的学员信息
select * from stu where age >= 20 and age <=30;

# 查询年龄 大于等于20岁 并且 年龄 小于等于 30岁的学员信息
select * from stu where age >= 20 && age <=30;

# 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
select * from stu where age = 18 or age = 20 or age = 22;

# 查询年龄不大于 30 岁的学员信息
select * from stu where not age > 30;

WHERE 子句使用 IN 操作符

1
2
# 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
select * from stu where age in (18,20,22);

WHERE 子句使用 LIKE 操作符

在 WHERE 子句中，可以使用 LIKE 运算符对某一列进行模糊查询，LIKE 运算符通常使用如下通配符

通配符含义
%代表多个不确定字符，匹配0个或多个字符
_代表单个不确定字符。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询表中包含A的字符
select * from 表名 like '%A%'

# 查询以A开头的字符
select * from 表名 like 'A%' 

# 查询第二个字符是A的字符
select * from 表名 like '_A% '

# 查询第二个字符是_第三个字符是A的字符（\代表转义字符）
select * from 表名 like '_\_A% '

# 查询第二个字符是&第三个字符是A的字符（escape表示转义字符）
select * from 表名 like '_$_A% ' escape '$'

WHERE 子句使用 BETWEEN 操作符

1
2
3
4
5
# 查询年龄 大于等于20岁 并且 年龄 小于等于 30岁的学员信息
select * from stu where age between 20 and 30;

# 查询入学日期在 2020-01-01 到 20021-01-01 之间的学员信息
select * from stu where date between '2020-01-01' and '20021-01-01';

WHERE 子句使用IS NULL 和 IS NOT NULL 操作符

1
2
3
4
5
6
7
8
9
# 查询数学成绩为 null 的学员信息
# 注意：null值的比较不能使用 = 和 != 需要使用 is 或 is not
select * from stu where math = null;（错误写法）
select * from stu where math is null;（正确写法）

# 查询数学成绩不为 null 的学员信息
# 注意：null值的比较不能使用 = 和 != 需要使用is 或  is not
select * from stu where math != null;（错误写法）
select * from stu where math is not null;（正确写法）

WHERE 子句使用 EXISTS 操作符

EXISTS 操作符用于判断是否存在满足特定条件的记录。可用于检查子查询是否有记录，并将结果作为布尔值返回

1
2
3
4
5
6
7
8
# 判断 scores 表中是否存在 id 为 1 的成绩记录
select * 
from stu 
where exists(
	select 1 
	from scores 
	where scores.id = 1 
);

分组查询（group by）

分组查询语法

1
2
3
4
5
6
7
8
9
select [distinct / all] 
	聚合函数/字段列表
from
	表名称
where
	条件表达式，建议不包含聚合函数的过滤条件
[where 分组前条件限定]
group by 分组字段名 [with cube / with rollup]
[having 分组后的过滤条件，建议包含聚合函数的过滤条件];

分组查询例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 根据性别分组，查询男同学和女同学各自的数学平均分
select sex,avg(math) 
from stu 
group by sex; 

# 根据性别分组，查询男同学和女同学各自的数学平均分，以及各自人数
select sex,avg(math),count(*) 
from stu 
group by sex;

# 根据性别分组，查询男同学和女同学各自的数学平均分，以及各自人数
# 要求：分数低于70分的不参与分组
select sex,avg(math),count(*) 
from stu 
where math > 70 
group by sex;

# 根据性别分组，查询男同学和女同学各自的数学平均分，以及各自人数
# 要求：分数低于70分的不参与分组，分组之后人数大于2个的
select sex,avg(math),count(*) 
from stu 
where math > 70 
group by sex 
having count(*) > 2;

with rollup语句

使用WITH ROLLUP语句时，MySQL会在所有分组行之后生成一个辅助行（汇总行），用于汇总所有分组的统计数据

注意：当使用 with rollup时，不能同时使用order by子句进行结果排序，即 with rollup 和 order by是互相排斥的

1
2
3
4
# 根据性别分组，查询男同学和女同学各自的数学平均分，同时生成分类汇总行
select sex,avg(math) 
from stu 
group by sex with rollup;

where 和 having 对比

区别简介
执行时机where是分组之前进行限定，不满足where条件，则不参与分组 having是分组之后对结果进行过滤
判断条件where不能对聚合函数进行判断 having可以对聚合函数进行判断
执行顺序where > 聚合函数 > having
执行效率如果过滤条件中没有聚合函数，where的执行效率要高于having

排序查询（order by）

排序查询语法

1
2
select 字段列表 from 表名 
order by 排序字段名1 [排序方式1],排序字段名2 [排序方式2]

排序方式

排序方式介绍
asc升序排列（默认值），从小到大
对于数值型数据而言，升序排列是把数据从小到大进行排列
对于字母型数据而言，升序排列是按照字母顺序从A到Z进行排列
对于时间型数据而言，升序排列是从最远的时间开始排列
desc降序排列，从大到小

多字段排序顺序

多个排序字段的排序顺序是从前到后的，先来先优先

排序查询案例

1
2
3
4
5
6
7
8
# 查询学生信息，按照年龄升序排列
select * from stu order by age;

# 查询学生信息，按照数学成绩降序排列
select * from stu order by  math desc;

# 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu order by math desc,english asc;

分页查询（limit）

分页查询语法

1
2
3
select 字段列表 
from 表名 
limit [查询的起始索引],每页显示的条数
参数简介
limitLIMIT 子句必须放在整个SELECT语句的最后！
查询的起始行起始行默认从0开始，若为0可省略不写
起始行计算公式：起始索引 =（当前页码-1）* 每页显示的条数
每页显示的条数查询的条数

分页查询案例

1
2
3
4
5
6
7
8
9
10
11
# 从第0条数据开始查询，查询3条数据
select * from stu limit 3;

# 假如每页显示3条数据，查询第1页的数据
select * from stu limit 0,3;

# 假如每页显示3条数据，查询第2页的数据
select * from stu limit 3,3;

# 假如每页显示3条数据，查询第3页的数据
select * from stu limit 6,3;

不同数据库分页查询的区别

数据库简介
MySQL使用limit进行分页查询
Oracle使用rownumber进行分页查询
SQL Server 和 Access使用top进行分页查询
DB2使用 FETCH FIRST 5 ROWS ONLY进行分页查询

连接查询（join）

自连接（自关联）

自连接也被称为自关联查询，指的是将一个表与它自己进行连接查询的操作

1
2
3
4
5
6
SELECT 
	T1.列名1, T2.列名2
FROM 
	表名 AS T1
JOIN 
	表名 AS T2 ON T1.列名 = T2.列名

笛卡尔积（交叉连接）

笛卡尔积概念

笛卡尔积（Cartesian product）是指在一个关系型数据库中，如果没有使用 WHERE 子句或者连接条件，就会对两个表进行取得所有可能的组合，从而得到一个新表，实现数据的拼接

1
SELECT 字段列表 FROM 表A, 表B;

笛卡尔积缺点

笛卡尔积存在很多缺点，实际业务中应该尽量避免使用笛卡尔积，而是使用其他连接方式，如INNER JOIN（内连接）、LEFT JOIN（左外连接）、RIGHT JOIN（右外连接）等

缺点描述
结果集大笛卡尔积会返回两个表所有行的组合，如果两个表中行的数量较大，将会导致返回的结果集也非常大，这不仅会占用数据库服务的计算资源和存储空间，还会导致查询速度缓慢，甚至使计算机崩溃。
冗余数据多由于笛卡尔积会返回两个表所有行的组合，因此结果集中往往包含大量的冗余数据，这些数据对于分析和处理来说是无用的，并且会浪费存储空间。
处理结果困难笛卡尔积返回的结果集比较复杂，可能包含大量的行和列，这会给数据的处理和分析带来诸多困难和挑战，需要消耗大量的时间和精力来解决。
性能差由于笛卡尔积返回的结果集比较庞大，因此它在数据库查询和分析中的性能相对较差，容易导致资源竞争和数据库响应时间变长。

笛卡尔积案例

表 A 中的数据

idname
1Tom
2John

表 B 中的数据

idage
120
225
330

使用 SQL 查询表 A 和表 B

1
SELECT * FROM A, B;

得到 A 和 B 两个表的笛卡尔积

A.idA.nameB.idB.age
1Tom120
1Tom225
1Tom330
2John120
2John225
2John330

UNION关键字（合并查询结果）

UNION语法（A+A∩B+B）

UNION关键字用于合并两个或多个结果集，并去除其中的重复行，语法如下：

1
2
3
select 字段列表 from 表1
union
select 字段列表 from 表2

UNION ALL语法（A+A∩B+A∩B+B）

UNION ALL关键字也用于合并两个或多个结果集，但不去除其中的重复行，语法如下：

1
2
3
select 字段列表 from 表1
union all
select 字段列表 from 表2

UNION与UNION ALL区别

  • 去重功能：Union操作符在合并结果集时会自动去除重复的行，确保最终结果集中只包含唯一的行。而Union All操作符不进行去重，保留所有行，包括可能存在的重复行。
  • 排序功能：Union操作符会对合并后的结果集进行排序，以保证结果集的顺序是唯一的。而Union All操作符不进行任何排序操作，结果集的顺序将按照原始查询的顺序保持。
  • NULL值处理：Union操作符会将具有相同位置的NULL值视为相同的值，如果一个位置上有一个NULL值，则只保留一个。而Union All操作符会将所有的NULL值都保留，不做任何处理。
  • 性能对比：由于Union操作符需要进行去重和排序操作，因此它的执行性能通常比Union All操作符要低。如果确保结果集已经没有重复行，并且不需要排序，使用Union All操作符可以获得更高的性能。
  • 列数与类型：Union和Union All操作符要求合并的结果集具有相同的列数和相似的数据类型。这是为了确保结果集能够正确形成并保持数据的一致性。

内连接（A∩B）

表A和表B的交集

隐式内连接

隐式内连接与显示内连接查询结果相同

1
2
3
4
# 隐式内连接
select 字段列表
from 表1, 表2... 
where 关联条件

显示内连接

隐式内连接语法更简单，显示内连接效率更高

1
2
3
# 显示内连接
select 字段列表 
from 表1 [inner] join 表2 on 关联条件;

外连接（join）

左外连接（A+A∩B）

表A所有的数据行+表A和表B的交集

1
2
select 字段列表 
from 表1 left [outer] join 表2 on 条件;

右外连接（A∩B+B）

表B所有的数据行+表A和表B的交集

1
2
select 字段列表 
from 表1 right [outer] join 表2 on 条件;

满外连接（A+A∩B+B）

表A所有的数据行 + 表B所有的数据行 + 表A和表B的交集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 方式一：左外连接 UNION 右外连接
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件
union
select 字段列表 
from 表1 right [outer] join 表2 on 关联条件

# 方式二：左外连接 UNION ALL 右反连接
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件
union all
select 字段列表 
from 表1 right [outer] join 表2 on 关联条件 
where 从表关联字段 is null

# 方式三：左反连接 UNION ALL 右外连接
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件 
where 从表关联字段 is null
union all
select 字段列表 
from 表1 right [outer] join 表2 on 关联条件

7种JOINS的实现

中图，内连接（A∩B）

表A和表B的交集

1
2
3
4
5
6
7
# 隐式内连接
select 字段列表
from 表1, 表2... 
where 关联条件
# 显示内连接
select 字段列表 
from 表1 [inner] join 表2 on 关联条件;

左上图，左外连接（A+A∩B）

表A所有的数据行 + 表A和表B的交集

1
2
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件;

右上图，右外连接（A∩B+B）

表B所有的数据行 + 表A和表B的交集

1
2
select 字段列表 
from 表1 right [outer] join 表2 on 关联条件;

左中图，左反连接（A - A∩B）

表A独有的数据行

1
2
3
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件 
where 从表关联字段 is null;

右中图，右反连接（B - A∩B）

表B独有的数据行

1
2
3
select 字段列表 
from 表1 right [outer] join 表2 on 关联条件 
where 从表关联字段 is null;

左下图，满外连接（A+A∩B+B）

表A所有的数据行 + 表B所有的数据行 + 表A和表B的交集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 方式一：左外连接 UNION 右外连接
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件
union
select 字段列表 
from 表1 right [outer] join 表2 on 关联条件

# 方式二：左外连接 UNION ALL 右反连接
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件
union all
select 字段列表 
from 表1 right [outer] join 表2 on 关联条件 
where 从表关联字段 is null

# 方式三：左反连接 UNION ALL 右外连接
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件 
where 从表关联字段 is null
union all
select 字段列表 
from 表1 right [outer] join 表2 on 关联条件

右下图，全反连接（A - A∩B）+（B - A∩B）

表A独有的数据 + 表B独有的数据行

1
2
3
4
5
6
7
8
# 左反连接  UNION ALL 右反连接
select 字段列表 
from 表1 left [outer] join 表2 on 关联条件 
where 从表关联字段 is null
union all
select 字段列表 
from 表2 right [outer] join 表2 on 关联条件 
where 从表关联字段 is null

子查询（嵌套查询）

子查询概念

子查询也被称为内部查询或嵌套查询，指在一个查询语句中使用另一个查询语句

子查询分类

按查询结果分类

查询结果简介
标量子查询返回单个值的子查询
列子查询返回多个值的子查询
行子查询返回一个或多个行的子查询
表子查询将子查询结果作为临时表，可在 SELECT、FROM 或 JOIN 中使用

按查询位置分类

子查询位置说明
SELECT 子查询将子查询放在 SELECT 子句中，通常作为列或表达式使用。
FROM 子查询将子查询放在 FROM 子句中，生成一个虚拟表，然后在外部查询中引用此表。
WHERE 子查询将子查询放在 WHERE 子句中，用于筛选符合条件的记录。
HAVING 子查询将子查询放在 HAVING 子句中，用于 GROUP BY 分组后筛选符合条件的分组。

标量子查询

标量子查询只能返回一个值，通常用于比较或者作为表达式中的一个值。通常使用单行比较运算符（如>、<、>=、<=、=等）将子查询结果与主查询进行比较，标量子查询可以嵌套在 SELECT 语句、WHERE 子句、HAVING 子句等多个 SQL 语句中

在 SELECT 语句中使用标量子查询

将标量子查询放在 SELECT 子句中作为列或表达式使用

1
2
3
4
5
6
SELECT 字段列表, (
    SELECT 单个字段 
    FROM 表名称 
    WHERE 条件表达式
) AS 别名
FROM 表名称;

在 FROM 子句中使用标量子查询

将标量子查询放在 FROM 子句中，生成一个虚拟表，然后在外部查询中引用此表。

1
2
3
4
5
6
SELECT 单个字段
FROM (
    SELECT 单个字段
    FROM 表名称
    WHERE 条件表达式
) AS 子查询别名;

在 WHERE 子句中使用标量子查询

将标量子查询放在 WHERE 子句中，用于筛选符合条件的记录。

1
2
3
4
5
6
7
8
SELECT 字段列表 
FROM 表名称 
WHERE 字段名 单行比较运算符 
(
    SELECT 单个字段 
	FROM 表名称 
	WHERE 条件表达式 
);

在 HAVING 子句中使用标量子查询

将标量子查询放在 HAVING 子句中，用于 GROUP BY 分组后筛选符合条件的分组。

1
2
3
4
5
6
7
8
SELECT 字段列表
FROM 表名称
GROUP BY 分组字段
HAVING 聚合函数 单行比较运算符 (
    SELECT 单个字段
    FROM 表名称
    WHERE 条件表达式
);

列子查询

列子查询可以返回多列的结果集，通常用于在主查询中连接、过滤或限制结果集，通常使用多行比较操作符（IN、ANY、ALL、SOME等）将子查询结果与主查询进行比较，列子查询可以嵌套在 SELECT 语句、WHERE 子句、HAVING 子句等多个 SQL 语句中

多行比较操作符含义
IN用于判断一个值是否在子查询的结果集中。
ANY/SOME用于将主查询中的某个值与子查询返回的多个值进行比较，只要有一个满足条件就返回 true。
ALL用于将主查询中的某个值与子查询返回的多个值进行比较，要求所有值都满足条件才返回 true。

在 SELECT 语句中使用列子查询

将列子查询放在 SELECT 子句中作为列或表达式使用

1
2
3
4
5
6
SELECT 字段列表, (
    SELECT 字段列表
    FROM 表名称 
    WHERE 条件表达式
) AS 别名
FROM 表名称;

在 FROM 子句中使用列子查询

将列子查询放在 FROM 子句中，生成一个虚拟表，然后在外部查询中引用此表。

1
2
3
4
5
6
SELECT 字段列表
FROM (
    SELECT 字段列表
    FROM 表名称
    WHERE 条件表达式
) AS 子查询别名;

在 WHERE 子句中使用列子查询

将列子查询放在 WHERE 子句中，用于筛选符合条件的记录。

1
2
3
4
5
6
7
8
SELECT 字段列表 
FROM 表名称 
WHERE 字段名 多行比较操作符 
(
    SELECT 字段列表 
	FROM 表名称 
	WHERE 条件表达式 
);

在 HAVING 子句中使用列子查询

将列子查询放在 HAVING 子句中，用于 GROUP BY 分组后筛选符合条件的分组。

1
2
3
4
5
6
7
8
SELECT 字段列表
FROM 表名称
GROUP BY 分组字段
HAVING 聚合函数 多行比较操作符 (
    SELECT 字段列表
    FROM 表名称
    WHERE 条件表达式
);

共用表表达式（CTE）

共用表表达式简介

公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）是一个命名的临时结果集，可以理解成一个可以复用的子查询，有时我们需要在一个SQL中重复执行同一个子查询，而每次子查询都会重新计算结果，带来性能的浪费。采用CTE可以在查询的一开始就定义好子查询的结果集，MySQL只会计算一次结果，然后在查询中使用CTE的名称可以反复引用。

普通公用表表达式

普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。

1
2
3
4
with CTE名称 [列名] as (
    子查询
) 
select语句 | delete语句 | update语句;

案例：查询用户的详细信息（子查询实现）

1
2
3
4
5
# 子查询实现
select * from user 
where user_id in (
    select distinct user_id from user_info
);

案例：查询用户的详细信息（使用公用表表达式实现）

1
2
3
4
5
with cte_test as (
    select distinct user_id from user_info
)
select * from user 
join cte_test on user.user_id = cte_test.user_id;

递归公用表表达式

递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以调用自己。

1
2
3
4
5
6
with recursive CTE名称 as ( 
	初始语句（非递归部分） 
	union all
	递归部分语句
)
[ select语句 | delete语句 | update语句 ]

使用了递归公共表达式 (CTE) 来生成一个包含数字 1 到 5 的表

1
2
3
4
5
6
7
with recursive cte as
(
  select 1 as n
  union all
  select n + 1 from cte where n < 5
)
select * from cte;

视图（逻辑表）

视图（View）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据

创建视图

1
create view 视图名称[字段列表] as 查询语句;

查看视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询视图中数据
select * from 视图名称;

# 查看视图的详细定义信息
show create view 视图名称; 

# 查看视图的结构（视图的字段信息）
desc / describe 视图名称;

# 查看视图的属性信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
show table status like '视图名称';

# 查看数据库的表对象、视图对象
show tables;

修改视图

1
alter view 视图名称 as 查询语句

更新视图数据

1
update 视图名称 set 内容 where 条件;

删除视图

1
drop view [if exists] 视图名称;