掌握一些统计函数的使用，能更方便我们查询数据。

统计函数

  1. COUNT()：求出全部的记录数
  2. SUM()：求出总和，操作的列是数字
  3. AVG()：平均值
  4. MAX()：最大值
  5. MIN()：最小值
  6. MEDIAN()：返回中间值
  7. VARIANCE()：返回方差
  8. STDDEV()：返回标准差

从SQL标准规定来讲，实际上只有前5个是标准函数。

范例：

  1. 查询出公司每个月支出的月工资总和

    SELECT SUM(sal) FROM emp;

  2. 查询出公司的最高工资、最低工资、平均工资

    SELECT MAX(sal), MIN(sal), AVG(sal), ROUND(AVG(sal),2) FROM emp;

    其中ROUND()表示的是取两位小数

  3. 统计出公司的雇员人数

    SELECT COUNT(*), COUNT(empno), COUNT(DISTINCT job) FROM emp;

    注：对于COUNT()函数而言，可以传递三类内容：*、字段、DISTICT 字段；而且在所有统计函数当中，只有COUNT()函数可以在表中没有任何数据的时候依然返回内容，即返回0，其他则返回null。

    面试题：

    题目：请问COUNT(*), COUNT(字段), COUNT(DISTINCT 字段)有什么区别？

    答案：在使用COUNT(字段)的时候，如果列上存在了null，那么null是不会进行统计的，如果存在重复，重复的记录也不统计。

GROUP BY分组

是否能进行分组：判断表中是否有一列或多列数据重复

子句的执行顺序：FROM、WHERE、GROUP BY、SELECT、ORDER BY

分组统计简单范例:

  1. 统计出每个部门的人数

    分析：此时一定需要按照部门的信息分组，在emp表中，每个雇员的部门都使用一个部门编号表示。那么就可以针对deptno字段来实现分组。

    1
    2
    3
    SELECT deptno, COUNT(*)
    FROM emp
    GROUP BY deptno;

分组的若干个限制：

  • 注意事项一：如果在一个查询之中不存在GROUP BY子句，那么SELECT子句只允许出现统计函数，其他的任何字段都不允许出现
  • 注意事项二：在统计查询之中（存在了GROUP BY子句），SELECT子句之中只允许出现分组字段（GROUP BY之后定义的字段）和统计函数，其他的任何字段都不允许出现

  • 注意事项三：所有统计函数允许嵌套使用，但是一旦使用了嵌套的统计函数之后，SELECT子句之中不允许再出现任何的字段，包括分组字段

    注意事项三的范例：求出每个部门平均工资的最高工资

    1
    2
    3
    SELECT MAX(AVG(sal))
    FROM emp
    GROUP BY deptno;

分组统计复杂范例：

  1. 查询每个部门的名称、部门人数、部门平均工资、平均服务年限

    • 步骤一：将dept表和emp表一起进行查询，暂时不分组 – 查询每个部门的名称、雇员编号、工资、雇佣日期

      1
      2
      3
      SELECT d.dname, e.empno, e.sal, e.hiredate
      FROM dept d, emp e
      WHERE e.deptno = d.deptno;

    • 步骤二：观察现在的dname字段（查询结果，理解为临时表），既然有重复的列就可以进行分组，针对于临时表数据进行分组

      1
      2
      3
      4
      5
      6
      SELECT d.dname, COUNT(e.empno), 
             ROUND(AVG(e.sal), 2),  
             ROUND(AVG(MONTHS_BETWEEN(SYSDATW,e.hiredate)/12),2)
      FROM dept d, emp e
      WHERE e.deptno = d.deptno
      GROUP BY d.dname;

    • 步骤三：针对部门信息是包含四个部门信息，可是上面结果只有三个部门的信息，因此需要加入外连接显示第四个部门的统计

      1
      2
      3
      4
      5
      6
      SELECT d.dname, COUNT(e.empno), 
             ROUND(AVG(e.sal), 2), 
             ROUND(AVG(MONTHS_BETWEEN(SYSDATW,e.hiredate)/12),2)
      FROM dept d, emp e
      WHERE e.deptno(+) = d.deptno
      GROUP BY d.dname;

多字段分组

范例：

  1. 查询出每个部门的详细信息

    分析：

    • 一定要包含的是：部门编号、名称、位置、平均工资、总工资、最高工资、最低工资、部门人数

    • 肯定不止一张表

    步骤：

    • 步骤一：将题目要求转换、将emp表和dept表关联，查询出部门编号、名称、位置、雇员编号、姓名

      1
      2
      3
      SELECT d.deptno, d.dname, d.loc, e.empno, e.ename
      FROM emp e, dept d
      WHERE e.deptno = d.deptno;

    • 步骤二：使用多字段分组(此处有三个地方重复)

      1
      2
      3
      4
      SELECT d.deptno, d.dname, d.loc, COUNT(e.empno), ROUND(AVG(e.sal),2), SUM(e.sal), MAX(e.sal), MIN(e.sal)
      FROM emp e, dept d
      WHERE e.deptno = d.deptno
      GROUP BY d.deptno, d.dname, d.loc;

    • 步骤三：使用外连接，因为此处部门信息不全

      1
      2
      3
      4
      SELECT d.deptno, d.dname, d.loc, COUNT(e.empno), ROUND(AVG(e.sal),2), SUM(e.sal), MAX(e.sal), MIN(e.sal)
      FROM emp e, dept d
      WHERE e.deptno(+) = d.deptno
      GROUP BY d.deptno, d.dname, d.loc;

    • 步骤四：NVL处理，是null结果为0

      1
      2
      3
      4
      SELECT d.deptno, d.dname, d.loc, COUNT(e.empno), NVL(ROUND(AVG(e.sal),2),0), NVL(SUM(e.sal),0), NVL(MAX(e.sal),0), NVL(MIN(e.sal),0)
      FROM emp e, dept d
      WHERE e.deptno(+) = d.deptno
      GROUP BY d.deptno, d.dname, d.loc;

HAVING子句

目的：对分组子句进行过滤。（使用WHERE是不行的，因为WHERE执行顺序在GROUP BY之前）

HAVING子句必须和GROUP BY子句一起使用

子句的执行顺序：FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY

范例：

  1. 查询出所以平均工资大于2000的职位信息、平均工资、雇员人数

    1
    2
    3
    4
    SELECT job, ROUND(AVG(sal),2), COUNT(empno)
    FROM emp
    GROUP BY job
    HAVING AVG(sal) > 2000;

  2. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资

    1
    2
    3
    4
    5
    SELECT d.deptno, d.name, ROUND(AVG(sal),2),MIN(e.sal), MAX(e.sal)
    FROM emp e, dept d
    WHERE e.deptno(+)=d.deptno
    GROUP BY d.deptno, d.dname
    HAVING COUNT(e.empno)>1;