«

MySQL聚合查询方法怎么使用

时间:2024-7-30 09:27     作者:韩俊     分类: Mysql


本篇内容介绍了“MySQL聚合查询方法怎么使用”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

    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   | java  |
    +--------+-------+
    | 张三   | 92.10 |
    | 王五   | 93.30 |
    | 小美   | 96.00 |
    +--------+-------+
    -- 3 rows in set (0.00 sec)

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

    3、聚合查询

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

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

    3.1 聚合函数

    进行聚合查询,需要搭配聚合函数,下面介绍的函数都是 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 student;
    +-----------+-----------+
    | 最高分    | 最低分    |
    +-----------+-----------+
    |     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(income)  |
    +-----------+--------------+
    | 老板      | 4000000.0000 |
    | 经理      |   22500.0000 |
    | 程序猿    |    4000.0000 |
    +-----------+--------------+
    -- 3 rows in set (0.00 sec)

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

    5、HAVING 关键字

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

      先筛选,再分组(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

    热门推荐