聚合函数与分组查询

• 一、聚合函数
• • 1、常见的聚合函数
  • 2、实例
  • 二、分组查询
  • • 1、group by子句
    • 2、准备工作
    • 3、实例
    • 4、having 条件

      一、聚合函数

      说明：聚合函数用来计算一组数据的集合并返回单个值，通常用这些函数完成：个数的统计，某列数据的求和，某列数据的最大值，最小值，或者是平均值。

      1、常见的聚合函数

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

      聚合函数一般在select语句中使用，此时select每处理一条记录时都会将对应的参数传递给这些聚合函数。

      需要注意的是：聚合函数忽略空值，即NULL值不会参与运算的。

      2、实例

      2.1 统计班级共有多少同学

      这里我们使用下面的学生表来进行演示，学生表中的内容如下：

      

      使用*做统计

      这里我们直接使用count(*)进行聚合统计，表示对所有的列数据进行统计：

      select count(*) from students;
      

      

      这里的统计原理也很简单，其实就是使用了select *将每一条记录都拿到，然后将每一条待处理记录时都传递给这个count聚合函数，然后我们就能够拿到数据的总个数了。

      2.2 统计班级收集的QQ号有多少个

      值得注意的是：我们没有对qq号码进行非空约束，我们继续使用聚会函数count进行统计。

      

      select count(qq) from students;
      

      

      结果为1，这证明聚合函数确实会忽略空值，即NULL值不会参与运算的。

      2.3 统计本次考试的数学成绩分数的个数有几个

      下面是我们的测试用例表：

      

      注意本题目要求的是数学成绩的个数，不是数学成绩的值，这意味着我们需要对相同的数学成绩的值进行先去重，然后再进行聚合统计，对于去重我们可以使用distinct进行去重，然后再使用count进行聚合统计。

      select count(distinct math) from exam_result;
      

      

      2.4 统计数学成绩总分

      对于统计数学成绩的总分其实就是对数据进行求和，我们可以使用sum函数来进行求和：

      select sum(math) from exam_result;
      

      

      2.5 统计不满100分的数学成绩总分

      在刚才的示例中我们已经求得数学成绩的总分了，对于不满100分的人我们可以使用where子句进行筛选得到。

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

      

      2.6 统计总分的平均分

      对于平均分我们可以使用avg函数进行求得：

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

      

      2.7 求英语最高分

      求最高分其实就是求最大值，我们可以使用max函数进行求最大值：

      select max(english) from exam_result;
      

      

      二、分组查询

      不知刚才你注意到没，前面我们进行聚合统计时都是在对整张表进行聚合统计，但是有时我们想要对不同的情况进行分别统计。

      例如在一个班级之中，有男生和女生，我们想要得到男生和女生中英语成绩的最高分分别是多少？我们发现我们再使用max函数是没有办法达到我们想要的目的的，但是如果我们先对班级中的男女生进行分组，然后又分别进行聚合统计，使用max函数就能够达到我们想要的目的了！

      所以分组是数据库最重要任务之一，要将行分组，我们可以使用GROUP BY子句。

      1、group by子句

      分组查询的SQL语法如下：

      SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];
      

      （小声bb：虽然语法看起来很难，但是在使用一两次以后你就会发现其实很简单）

      说明一下：

      • SQL中大写的表示关键字，[ ]中代表的是可选项。
      • 查询SQL中各语句的执行顺序为：where、group by、select、order by、limit。
      • group by后面的列名，表示按照指定列进行分组查询。

        ---

        2、准备工作

        分组查询测试表 —— 雇员信息表

        准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）

        • EMP员工表
        • DEPT部门表
        • SALGRADE工资等级表

          雇员信息表数据库文件

          ---

          拿到该数据库文件以后，我们可以先打开该文件进行查看其内容：

          vim scott_data.sql
          

          

          我们会发现其里面都是SQL记录，对于MySQL我们备份其数据库时，其实备份的全部都是一条条有效的SQL记录，通过重新执行这些SQL，我们便能够得到和原来一摸一样数据库。

          接下来我们就可以在mysql中将这个数据库给创建出来了：

          source 该文件的绝对路径;
          

          例如我这里是：

          source /root/MySQL/scott_data.sql;
          

          

          执行成功！

          然后我们查询我们的数据库，发现数据库中多了一个scott的数据库：

          show databases;
          

          

          我们使用这个数据库并显示数据库中的所有表

          use scott;
          show tables;
          

          

          这三张表就是我们所说的：

          • EMP员工表
          • DEPT部门表
          • SALGRADE工资等级表

            我们先分别查看一下表结构和表内容：

            • 查看员工表结构

              desc emp;
              

              

              • 查看员工表内容

                select * from emp;
                

                

                • 查看部门表结构

                  desc dept;
                  

                  

                  • 查看部门表内容

                    select * from dept;
                    

                    

                    • 查看工资等级表结构

                      desc salgrade;
                      

                      

                      • 查看工资等级表内容

                        select * from salgrade;
                        

                        

                        3、实例

                        3.1 显示每个部门的平均工资和最高工资

                        由于要显示每个部门平均工资和最高工资，所以我们一定要借助group by来将这个整表进行划分为多个组，然后我们再使用avg与max函数来对工资分别求平均和最高工资。

                        所以我们可以这样进行查询：

                        select deptno, avg(sal), max(sal) from emp group by deptno;
                        

                        

                        说明一下： 上述SQL会先将表中的数据按照部门号进行分组，然后各自在组内做聚合查询得到每个组的平均工资和最高工资。

                        3.2 显示每个部门的每种岗位的平均工资和最低工资

                        现在对于我们来说：“求平均工资和最低工资”，是很简单的事情，但是题目的要求：显示每个部门的每种岗位，显然要求我们进行两次分组，对于group by子句来说，我们可以使用,分割，来进行多个条件分组。

                        select deptno, job, avg(sal), min(sal) from emp group by deptno, job;
                        

                        

                        说明：group by子句中可以指明按照多个字段进行分组，各个字段之间使用逗号隔开，分组优先级与书写顺序相同。

                        4、having 条件

                        在讲解此条件之前我们先继续来解决下面的问题：

                        3.3 显示平均工资低于2000的部门和它的平均工资

                        在这里我们会发现，我们必须先要拿到平均工资的值，然后再根据平均工资进行筛选。

                        假设这里我们使用where子句，我们会发现在where子句中我们无法表示平均工资的（聚合函数不能够在where子句中使用的，因为where子句是对单个记录进行筛选，而聚合函数是对整个结果集进行计算的）

                        就算假设我们能够表示平均工资，我们知道where子句的执行优先级是很高的，于是就会先按平均工资进行筛选，然后再拿到平均工资的值。显然这个逻辑是有问题的。

                        ---

                        为了解决这个问题我们就要学习一下HAVING 条件了，having也是一个筛选条件。

                        含有having子句的SQL如下：

                        SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];
                        

                        说明一下：

                        • SQL中大写的表示关键字，[ ]中代表的是可选项。
                        • SQL中各语句的执行顺序为：where、group by、select、having、order by、limit。
                        • having子句中可以指明一个或多个筛选条件。

                          having子句和where子句的区别

                          • where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
                          • where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
                          • where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名。
                          • where子句的执行优先级很高，而having的执行优先级很低。

                            ---

                            于是上面的问题就被转化为了下面的问题了：

                            • 先统计每个部门的平均工资。
                            • 然后通过having子句筛选出平均工资低于2000的部门。

                              统计每个部门的平均工资

                              select deptno, avg(sal) from emp group by deptno;
                              

                              

                              通过having子句筛选出平均工资低于2000的部门

                              select deptno, avg(sal)  平均工资 from emp group by deptno having 平均工资<2000;
                              

                              

                              需要注意的是：聚合函数的执行优先级通常是在SQL查询中确定的，这句的SQL执行顺序如下：

                              

                              这句SQL的执行顺序如下：

                              1. FROM子句：首先，从"emp"表中检索数据。

                              2. GROUP BY子句：然后，根据"deptno"列将结果集分组。相同"deptno"值的行将被分为一组。

                                 （AVG函数计算：接下来，在每个分组中计算"sal"列的平均工资）。

                              3. HAVING子句：然后，在HAVING子句中筛选出平均工资小于2000的分组。

                              4. SELECT子句：最后，在SELECT子句中选择"deptno"和平均工资作为结果返回。

                              面试题：SQL查询中各个关键字的执行先后顺序:
                              

                              from > on> join > where > group by > with > having > select > distinct > order by > limit

                               
                               网站制作设计公司  网站建设案例教程试卷  重庆网站推广软件  网线制作步骤  网站建设包括哪些内容  网线制作教程