「MySQL」- 复杂的SQL查询语句

任意门

前言

聚合查询

1.聚合函数

count函数

sum函数

avg函数

max函数

min函数

2.分组查询

having子句

多表查询

1.笛卡尔积

2.内连接

3.外连接

左外连接

右外连接

自链接

子查询

合并查询

​ 关键字执行顺序

前言

MySQL 除了基础的 CRUD 操作之外 , 还涉及了更多更复杂的查询操作 , 不仅支持一张表进行查询 , 也支持多张表进行查询 ,本章是针对 MySQL 中的 select 查询操作更复杂的一些用法 , 在本章你将会学习进阶的 select 操作 , 随着作者一起往下看看吧(本章书写风格会在单行与多行之间切换) ! ! !

聚合查询

聚合查询是指对一个数据表中某个字段的数据进行部分或者全部统计查询的一种方式，比如所有全部书的平均价格或者是书籍的总数量等等 ，在这些时候就会使用到聚合查询这种方法。

在开始学习聚合查询之前 , 我们需要先在选中数据库中准备一张测试表 , 创建一张成绩表并添加测试记录

-- 创建成绩表
drop table if exists exam_result;
create table exam_result (
id int,
name varchar(20),
chinese decimal(3,1),
math decimal(3,1),
english decimal(3,1)
);
-- 插入测试数据
insert into exam_result (id,name, chinese, math, english) values
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30),
(null,null,null,null,null);

接下来我们就可以开始学习聚合查询了 ! ! !

1.聚合函数

对于一张表中 , 万一有需求需要我们去计算整张表有多少条记录 , 或者指定一张表计算平均值/求和等操作 , 我们总不可能拿着个计算器在那边看表便计算吧 , 虽然可以但是个笨方法, 所以这时候就可以使用聚合函数来实现统计总数、计算平局值等操作 , 常见的聚合函数如下

函数	说明
count	返回查询到的数据的数量
sum	返回查询到的数据的总和，不是数字没有意义
avg	返回查询到的数据的平均值，不是数字没有意义
max	返回查询到的数据的最大值，不是数字没有意义
min	返回查询到的数据的最小值，不是数字没有意义

• 每个聚合函数参数都可以为表达式或者列名

接下来 , 我们来看一下每一个聚合函数的用法

count函数

count 函数可以计算一张表中一共有多少条记录 , 并返回查询到的记录总数 , count 参数是什么类型都行

语法

select count(<表达式 / 列名>) from <数据表名>;

说明

• count ( ) 中的参数可以是 * , 为 * 代表数据表中所有的记录

• count ( ) 中的参数如果不是 * 的话 , 如果查询中没有格外的条件 , 那么记录值为 null 的将不计入查询结果

代码示例1

统计成绩表中一共有几条记录 , 参数为 * 的话 , 值为 null 的记录也会被计入到结果中

select count(*) from exam_result;

-- 查询结果
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

代码示例2

由于使用 * 参数会将值为 null 的记录也会被计入到结果 , 所以为了更精确的知道某一列中有多少条记录 , 我们就可以对 count 传入指定列来进行聚合查询 , 例如查询 name 列有几条记录 , 在使用指定列进行聚合查询的时候 , 会将该列值为 null 的值给忽略 , 不计入最终查询结果中

select count(name) from exam_result;

-- 查询结果
+-------------+
| count(name) |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

sum函数

sum 函数可以对表中的指定列或者表达式进行一个求和 , 前提要求传入的参数列 / 表达式是数值类型

语法

select sum(表达式/列名) from <数据表名> [where];

说明

• 要求传入的参数列 / 表达式是数值类型 , 否则没有意义

• [ ]表示可选项 , 在使用 sum 函数时可以使用 where 子句

代码示例1

统计成绩表中的数学成绩总分

select sum(math) from exam_result;

-- 查询结果
+-----------+
| sum(math) |
+-----------+
|     581.5 |
+-----------+
1 row in set (0.00 sec)

代码示例2

统计成绩表中的数学成绩总分 , 要求对数学成绩大于 70 的分数进行求和 , 如果没有就返回 NULL , 这个时候我们就可以使用 where 子句进行条件筛选 , 增加条件 math > 70 , 这时候计算的就是大于 70 分以上的总分

select sum(math) from exam_result where math > 70;

-- 查询结果
+-----------+
| sum(math) |
+-----------+
|     516.5 |
+-----------+
1 row in set (0.00 sec)

如果对于筛选条件没有对应的结果的话 , 就会返回 NULL , 例如要求对数学成绩小于 60 的分数进行求和 , 从上面的总表就能看出 , 在数学成绩那一列是没有小于 60 分的 , 记录为 NULL 的也不会计算到结果当中 , 所以本次查询返回值就是 NULL

select sum(math) from exam_result where math < 60;

-- 查询结果
+-----------+
| sum(math) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

代码示例3

参数不仅可以是指定列 , 还能指定表达式 , 前提是表达式的列都是数值类型 , 例如对 语文、数学、英语 全部成绩进行求和计算总分

select sum(chinese + math + english) from exam_result;

-- 查询结果
+-------------------------------+
| sum(chinese + math + english) |
+-------------------------------+
|                        1550.0 |
+-------------------------------+
1 row in set (0.00 sec)

avg函数

avg 函数可以对表中的指定列或表达式进行一个求平均值的操作 , 前提要求参数是数值类型

语法

select avg(表达式/列名) from <数据表名> [where];

说明

• 要求传入的参数列 / 表达式是数值类型 , 否则没有意义

• [ ]表示可选项 , 在使用 avg 函数时可以使用 where 子句

代码示例1

对成绩表中的数学成绩计算平均分

select avg(math) from exam_result;

-- 查询结果
+-----------+
| avg(math) |
+-----------+
|  83.07143 |
+-----------+
1 row in set (0.00 sec)

代码示例2

使用 where 子句对数学成绩大于 80 分的记录进行求平均分

select avg(math) from exam_result where math > 80;

-- 查询结果
+-----------+
| avg(math) |
+-----------+
|  91.37500 |
+-----------+
1 row in set (0.00 sec)

代码示例3

传入参数是一个表达式 , 对三门课程总分统计平均分

select avg(chinese + math + english) from exam_result;

-- 查询结果
+-------------------------------+
| avg(chinese + math + english) |
+-------------------------------+
|                     221.42857 |
+-------------------------------+
1 row in set (0.00 sec)