MySQL：复杂查询(一)——聚合函数&分组查询&联合查询01

聚合查询&分组查询

Sunrise_angel

2532人浏览 · 2024-08-10 03:11:55

Sunrise_angel · 2024-08-10 03:11:55 发布

1、聚合查询

1.1 聚合函数

1.1.1 COUNT()

1.1.2 SUM()

1.1.3 AVG()

1.1.4 MAX()，MIN()

1.2 分组查询

1.2.1 GROUP BY子句

1.2.1.1 round()

1.2.2 HAVING

1.2.3 示例

2、联合查询（表连接查询）

2.1 内连接

2.1.1 ①取相关表笛卡尔积

2.1.2 ②确定连接条件，过滤无效数据

2.1.3 ③精简查询结果

2.1.3.1 表的别名

3、综合练习

3.1 问题一

3.2 问题二

3.3 问题三（三表查询）

1、聚合查询

聚合查询是针对某一列数据的行与行之间进行的运算。

而我们之前所学的表达式查询是针对某一行数据的列与列之间进行的运算。

简单来说，聚合查询是竖着的，表达式查询是横着的。

1.1 聚合函数

聚合函数是MySQL内置的函数，我们可按需调用。

1.1.1 COUNT()

count()：返回表中记录的总行数。

函数参数可传 *，表示根据任意一列计算行数（推荐）
函数参数可传常量
函数参数可传指定的列，表示根据这一列计算行数。
注意：当所传参数为列时，若该列存在数值为NULL的行，则不将为NULL的行计算进行数中。

1.1.2 SUM()

sum()：把查询结果中所有行的指定列的数据相加。

注意：

列的数据类型必须为数值型，不能为字符型或日期型。
若指定的列中有数值为NULL，则NULL视为无效数据不计算进列的和。（之前我们学到，不管什么数值与NULL进行运算，结果都为NULL，但是这里不同，是因为函数内部进行了处理）
通过sum函数求得的结果的值，不受表中字段类型的约束。

1.1.3 AVG()

avg()：计算所有行的指定列的平均数。

我们既可以计算列的平均值，也可以计算表达式的平均值：

我们同样可以给列或表达式别名：

1.1.4 MAX()，MIN()

max()：返回所有行指定的列的最大值。

min()：返回所有行指定的列的最小值。

注意：多个聚合函数可以同时使用：

注意：

在使用关于运算的聚合函数时（比如sum、avg、max、min），只能用于计算数值类型的列。
在使用聚合函数时，都可以结合之前所学的where、limit等子句

1.2 分组查询

1.2.1 GROUP BY子句

在select中使用group by子句可以对指定列进行分组查询。

需要满足：使用group by进行分组查询时，select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中。

这里创建了一个公司员工表单：

我们根据职位role进行分组，则select查询到结果集中的列只能是role或者是包含于聚合函数中的其他列：

注意：在SQL语句中，一定有着这样的执行顺序：先选表，再分组，再select查询，所以我们不能通过别名来分组

1.2.1.1 round()

在上图中，我们可以看到，通过聚合函数计算所得的值小数点后有6位，我们可以通过round函数手动设置小数位数：

语法：round(聚合函数，n) n为手动设置的小数位数

同样，我们可以根据实际需要后跟order和limit子句：

注意：order子句是select查询后执行的，所以可以使用别名进行排序。

1.2.2 HAVING

group by子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 where子句，而需要用 having

注意：having跟在group by子句后

查询平均薪资大于1w且小于2w的职位：

注意：having子句是在分组完成后执行的，此时还没有执行select子句，故不能使用别名进行过滤，否则报错。执行顺序如下：

这里需要注意where过滤与having过滤的区别：

where跟在 from+表名的后面，在选好表后就要进行过滤，过滤的是分组前的数据
having子句跟在group by子句的后面，是对分组后的结果进行过滤
where过滤的是表中每一行真实的数据
having过滤的是分组后通过聚合函数计算出的结果，而并非表中真实存在的
根据实际需求，可结合where和having完成过滤操作
having仅对组进行操作，过滤不满足条件的分组，返回符合条件的分组（不对每个组中的记录进行细节过滤）。

1.2.3 示例

示例一：查询每个职位的最高工资、最低工资和平均工资：

-- 查询每个职位的最高工资、最低工资和平均工资：
select role,round(avg(salary),2) 平均薪资,round(max(salary),2) 最高薪资,round(min(salary),2) 最低薪资 from emp group by role;

示例二：显示平均工资低于1500的职位和它的平均工资

-- 显示平均工资低于1500的职位和它的平均工资
select role,round(avg(salary),2) 平均薪资 from emp group by role having avg(salary) < 1500;

2、联合查询（表连接查询）

在设计表时，为了消除表中字段的部分函数依赖、传递依赖，我们通常会对数据进行拆分，拆分为多个表后再经过建立主外键关系进行关系的关联。但是这样也产生了一些问题，就是我们在使用select查询数据时，查询出来的记录是不完整、不明确的，例如下图：

而我们可以通过联合查询，使数据更加完整、明确的展现出来：

联合查询又称表连接查询，是工作中最常用的查询。

如何通过联合查询查询结果呢？有以下几个步骤：

确定那几张表要参与查询，取这几张表的笛卡尔积（全排列）：select * from 表1，表2；
根据表与表之间的主外键关系，确定连接条件，过滤无效数据（可根据实际情况继续过滤）
通过指定列查询，精简结果集

2.1 内连接

内连接有两种语法形式：

大家可选择自己习惯的语法使用。

2.1.1 ①取相关表笛卡尔积

联合查询的第一步就是取要查询的相关表的笛卡尔积，笛卡尔积即数据的全排列，上文的学生表和班级的表的笛卡尔积为下图：

取笛卡尔积：select * from 表1，表2；

执行SQL取笛卡尔积：

2.1.2 ②确定连接条件，过滤无效数据

获取的笛卡尔积中存在着大量无效数据，我们需要过滤掉这些数据。

联合的表之间必然存在主外键关系，通过主外键值确定连接条件。判断子表外键值和主表主键值是否相等即可，保留相等的，过滤不相等的。

因为主外键字段存在于两个表中，所以我们应该使用表名.字段的形式，指定哪个表中的哪个字段：

2.1.3 ③精简查询结果

上图中，我们可以发现表中有好多个无用字段如：class_id、id，我们可以通过 select 表名.列名的形式，抽取出有用的字段来精简查询结果，

注意：因为存在多个表，所以一定是 select 表名.列名的形式，明确哪个表中的哪个字段！

2.1.3.1 表的别名

通过表名.列明的方式是必要的，但是使得书写的代码量增多，我们可以通过给表起别名的方式简化代码，我们可以在 from 表名后给表起一个更加简短的别名，这样我们就这样在select和where子句中缩短表名长度：

3、综合练习

这里分别建好了4个表，大家简单瞄一眼即可：

班级表：

课程表：

成绩表：

学生表：

3.1 问题一

查询许仙同学的成绩：

根据题目可以推断出需要联合学生表和成绩表两个相关表，根据上文所讲过程，即可查询成功：

3.2 问题二

查询所有同学的总成绩和信息：

因为要查询成绩和学生信息，所以我们要将成绩表和学生表进行联合，并过滤掉无效数据
根据题目信息，因为要查询每人的总成绩，一个同学为一组，所以我们要进行分组，而分组我们要根据编号student.student_id进行分组，因为学生姓名可能会出现重名的现象
通过select，查询学号、姓名等学生信息字段，利用聚合函数计算每名学生总成绩