MySQL聚合查询案例讲解

目录

• 1、前言
• 2、插入查询结果
• 3、聚合查询
• 3.1 聚合函数
• 3.1.1 count 
• 3.1.2 sum
• 3.1.3 avg 
• 3.1.4 max 和 min
• 4、GROUP BY 子句
• 5、HAVING 关键字

1、前言

前面的内容已经把基础的增删改查介绍的差不多了，也介绍了表的相关约束， 从本期开始往后的内容，就更加复杂了，更多的是一些复杂的查询 SQL.

本期虽然是讲述聚合查询相关知识，但是这里补充一个知识点，如何将查询结果插入到另一个表中呢？

2、插入查询结果

查询还是用的比较多的，对于查询到的数据，能不能也给保存下来呢？也就是把查询的结果插入到另一张表中。

案例：创建一张学生表，表中有 id，name，sex，Java，python 这些字段，现需要把 java 成绩超过 90 的学生复制进 java_result 表，复制的字段为 name，java。

进行上述操作之前，我们需要创建一个学生表并准备好相关的数据：

create table student (
    id int primary key,
    name varchar(20),
    sex varchar(1),
    java float(5, 2)
);
insert into student value 
    (1, '张三', '男', 92.1),
    (2, '小红', '女', 88.2),
    (3, '赵六', '男', 83.4),
    (4, '王五', '男', 93.3),
    (5, '小美', '女', 96.0);

有了学生表之后，我们要把 name，java 这两个字段的查询结果复制到 java_result 这个表中，这里我们注意，要求查询结果的临时表的列数和列的类型，要和 java_result 这里匹配，所以接下来我们就来创建 java_result 这张表：

create table java_result (
    name varchar(20),
    java float(5, 2)
);

创建好 java_result 这张表之后，就要查询 student 表中 name java 两个字段，并且 java > 90，将满足上述条件的查询结果，插入到 java_result 表中！：

insert into java_result select name, java from student where java > 90;
-- Query OK, 3 rows affected (0.00 sec)
-- Records: 3  Duplicates: 0  Warnings: 0

select * from java_result;
+--------+-------+
| name javascript  | java  |
+--------+-------+
| 张三   | 92.10 |
| 王五   | 93.30 |
| 小美   | 96.00 |
+--------+-------+
-- 3 rows in set (0.00 sec)

这样我们就发现，已经将 student 表中 name 和 java 字段满足 > 90 的数据已经全部插入成功了！

3、聚合查询

前面我们接触过的 带表达式查询 都是列和列之间进行运算的，看哪一列满足了这个条件。

而现在要介绍的聚合查询，就是针对 行和行 之间进行运算的！ 

3.1 聚合函数

进行聚合查询，需要搭开发者_Spark配聚合函数，下面介绍的函数都是 SQL 中内置的一组函数，我们先来简单的认识下：

函数	解释
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字无意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字无意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字无意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

下面我们就来演示一下上述的聚合函数的简单使用，在使用之前，我们需要有一张表，并且有相应的数据：

select * from student;
+----+--------+------+-------+
| id | name   | sex  | java  |
+----+--------+------+-------+
|  1 | 张三   | 男   | 92.10 |
|  2 | 小红   | 女   | 88.20 |
|  3 | 赵六   | 男   | 83.40 |
|  4 | 王五   | 男   | 93.30 |
|  5 | 小美   | 女   | 96.00 |
|  6 | 李四   | 男   |  NULL |
+----+--------+------+-------+
-- 6 rows in set (0.00 sec)

下面我们就针对上述这张表，来使用下上述的聚合函数。 

3.1.1 count 

● 求出 student 表中有多少同学

select count(*) from student;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
-- 1 row in set (0.00 sec)

这个操作就相当于先进行 select * ，然后针对返回的结果，在进行 count 运算，求结果集合的行数. 注意：此处如果有一列的数据全是 null，也会算进去！(因为是针对 *)

此处这里的 count() 括号中，不一定写 *，可以写成任意的列明/表达式，所以我们可以针对 name 来统计人数：

select count(name) from student;
+-------------+
| count(name) |
+-------------+
|           6 |
+-------------+
-- 1 row in set (0.00 sec)

 ● 统计有多少人有 java 考试成绩

select count(java) from student;
+-------------+
| count(java) |
+-------------+
|           5 |
+-------------+
-- 1 row in set (0.00 sec)

这里我们看到了，由于 count 是针对 java 字段进行统计，而 李四 那一条数据中，java 为 null，前面我们学习过，null 与任何值计算都是 null，所以统计的时候，就把 null 给去掉了。

● 统计 java 成绩大于90分的人数

select count(java) from student where java > 90;
+-------------+
| count(java) |
+-------------+
|           3 |
+-------------+
-- 1 row in set (0.00 sec)

这里我们要弄清楚，count() 这个括号中，是针对你要针对的那一列，针对不同列，不同的条件，就会有不同的结果，对于 count 的演示就到这里。

注意：count 和 () 之间不能有空格，必须紧挨着，在 Java 中函数名和() 之间是可以有空格的，但很少人会这样写。

3.1.2 sum

这个聚合函数，就是把指定列的所有行进行相加得到的结果，要求这个列得是数字，不能是字符串/日期。

● 求出学生表中 java 考试分数总和

select sum(java) from student;
+-----------+
| sum(java) |
+-----------+
|    453.00 |
+-----------+
-- 1 row in set (0.01 sec)

虽然我们表中有 java 字段这列中有 null 值，前面了解到 null 与任何值运算都是 null，但是这里的 sum 函数会避免这种情况发生。

当然在后面也可也带上 where 条件，这里就不做过多演示了。

3.1.3 avg 

● 求班级中 java 的平均分

select avg(java) from student;
+-----------+
| avg(java) |
+-----------+
| 90.600000 |
+-----------+
-- 1 row in set (0.00 sec)

当前只是针对某一列进行平均运算，如果有两门课程，求每个学生总分的平均分呢？

select avg(java + python) from student;

这里每次查询结果都只有一列，能否把两个聚合函数一起使用呢？

select sum(java), avg(java) as '平均分' from student;
+-----------+-----------+
| sum(java) | 平均分    |
+-----------+-----------+
|    453.00 | 90.600000 |
+-----------+-----------+
-- 1 row in set (0.00 sec)

这里我们能发现一个细节，使用聚合函数查询，字段也是可以取别名的。

3.1.4 max 和 min

● 求出 java 考试分数的最高分和最低分

select max(java) as '最高分', min(java) as '最低分' from http://www.devze.comstudent;
+-----------+-----------+
| 最高分    | 最低分    |
+-----------+-----------+
|     96.00 |     83.40 |
+-----------+-----------+
-- 1 row in set (0.00 sec)

上述就是聚合函数最基础的用法了， 但是在实际中也可能会有更复杂的情况，比如需要按照某某进行分组查询，这就需要搭配 GROUP BY 字句了。

4、GROUP BY 子句

select 中使用 group by 自居可以对指定列进行分组查询，但是需要满足指定分组的字段必须是 "分组依据字段"，其他字段若想出现在 select 中，则必须包含在聚合函数中。

这里我们构造出一张薪水表 salary：

create table salary (
    id int primary key,
    name varchar(20),
    role varchar(20),
    income int 
);
insert into salary value 
    (1, '麻花疼', '老板', 5000000),
    (2, '篮球哥', '程序猿', 3000),
    (3, '歪嘴猴', '经理', 20000),
    (4, '多嘴鸟', '经理', 25000),
    (5, '雷小君', '老板', 3000000),
    (6, '阿紫姐', '程序猿', 5000);

像上述的情况，如果要查平均工资，那公平吗？？？

select avg(income) from salary;
+--------------+
| avg(income)  |
+--------------+
| 1342166.6667 |
+--------------+
-- 1 row in set (0.00 sec)

那篮球哥的月薪连平均下来的零头都不到，所以这样去求平均工资是毫无意义的，真正有意义的是啥呢？求老板这个职位的平均工资，以及经理这个职位的平均工资，及程序猿这个职位的平均工资，通俗来说，就是按照 role 这个字段进行分组。每一组求平均工资：

select role, avg(income) from salary group by role;
+-----------+--------------+
| role      | avg(income)  |
+-----------+--------------+
| 程序猿    |    4000.0000 |
| 经理      |   22500.0000 |
| 老板      | 4000000.0000 |
+-----------+--------------+
-- 3 rows in set (0.00 sec)

这就也就是把 role 这一列，值相同的行给分成了一组，然后计算平均值，也是针对每个分组，分别计算。

在 mysql 中，这里得到的查询结果临时表，如果没有 order by 指定列排序，这里的顺序是不可预期的，当然也可以手动指定排序，比如最终结果按照平均工资降序排序：

select role, avg(income) from salary group by role order by avg(income) desc;
+-----------+--------------+
| role      | avg(incjsome)  |
+-----------+--------------+
| 老板      | 4000000.0000 |
| 经理      |   22500.0000 |
| ipVIYKK程序猿    |    4000.0000 |
+-----------+--------------+
-- 3 rows in set (0.00 sec)

如果不带聚合函数的普通查询，能否可行呢？这里如果你没有修改任何配置文件，是不可行的，记住千万不能把前面的 order by 与 group by 弄混！

5、HAjsVING 关键字

分组查询也是可以指定条件的，具体三种情况：

• 先筛选，再分组(where)
• 先分组，再筛选(having)
• 分组前分组后都指定条件筛选(where 和 having 结合使用)

如何理解上述三条的含义呢？ 这里我们举几个例子就很好理解了：

● 篮球哥月薪 3000 实在是太低了，简直给程序猿岗位拖后腿，干脆求平均工资时去掉篮球哥的月薪数据。

select role, avg(income) from salary where name != '篮球哥' group by role;
+-----------+--------------+
| role      | avg(income)  |
+-----------+--------------+
| 程序猿    |    5000.0000 |
| 经理      |   22500.0000 |
| 老板      | 4000000.0000 |
+-----------+--------------+
-- 3 rows in set (0.00 sec)

这样求出来的平均值就不包含篮球哥的月薪数据了，这就是先筛选，再分组。

● 还是查询每个岗位的平均工资，但是除去平均月薪在 10w 以上的岗位，不能让篮球哥眼红！

select role, avg(income) from salary group by role having avg(income) < 100000;
+-----------+-------------+
| role      | avg(income) |
+-----------+-------------+
| 程序猿    |   4000.0000 |
| 经理      |  22500.0000 |
+-----------+-------------+
-- 2 rows in set (0.00 sec)

这样一来就只保留了平均月薪小于 10w 的岗位了，很明显这个平均值是在分组之后才算出来的，这也就是先分组，再筛选。

这里 having 也能加上逻辑运算符，具体感兴趣的小伙伴可以自行下来尝试一下，好比如你想要拿好 offer，就得技术过关，还能加班！

至于第三种分组前后都需要筛选，就是把上述俩例子结合起来，这里就不多赘述了！

到此这篇关于MySQL聚合查询的文章就介绍到这了,更多相关mysql聚合查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！