MySQL数据库学习之分组函数详解

吾爱主题阅读：153 2024-04-01 23:20:12 评论：0

1.分组函数

极值

找出最高工资：

1 2 3 4 5 6 7 mysql> select max (sal) from emp; + ----------+ | max (sal) | + ----------+ | 5000.00 | + ----------+ 1 row in set (0.00 sec)

找出最低工资：

1 2 3 4 5 6 7 mysql> select min (sal) from emp; + ----------+ | min (sal) | + ----------+ | 800.00 | + ----------+ 1 row in set (0.00 sec)

求和

将所有人的工资相加：

1 2 3 4 5 6 7 mysql> select sum (sal) from emp; + ----------+ | sum (sal) | + ----------+ | 29025.00 | + ----------+ 1 row in set (0.00 sec)

平均值

求所有人的平均工资：

1 2 3 4 5 6 7 mysql> select avg (sal) from emp; + -------------+ | avg (sal) | + -------------+ | 2073.214286 | + -------------+ 1 row in set (0.00 sec)

列数和

计算员工数量总和：

1 2 3 4 5 6 7 mysql> select count (ename) from emp; + --------------+ | count (ename) | + --------------+ | 14 | + --------------+ 1 row in set (0.00 sec)

count(具体字段) 表示该字段下不为null的行数

count(*) 表示整个范围的行数，因为数据库表中并不存在记录全为null的情况！

2.分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG等函数。

GROUP BY 语法

1 2 3 4 SELECT column_name, function (column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

请注意，在进行关键字组合的时候，他们的顺序是这样的：

select ...
from ...
where ...
group by ...
order by ...

这样的顺序是不可以被更改的，且他们在Mysql内部的执行顺序是：

from
where
group by
select
order by

注意：分组函数在进行使用的时候要先分组才能使用

那么现在出现了一个问题，如下语句看似违反了组合顺序，但是它为什么是正确的呢？

1	`select` `sum` `(sal)` `from` `emp;`

因为select在group by之后执行

现在，我们来看一个分组查询的示例，找出每个工作岗位的工资和：

1 2 3 4 5 6 7 8 9 10 11 12 mysql> select job, sum (sal) from emp -> group by job; + -----------+----------+ | job | sum (sal) | + -----------+----------+ | CLERK | 4150.00 | | SALESMAN | 5600.00 | | MANAGER | 8275.00 | | ANALYST | 6000.00 | | PRESIDENT | 5000.00 | + -----------+----------+ 5 rows in set (0.01 sec)

找出每个部门的最高薪资：

1 2 3 4 5 6 7 8 9 mysql> select deptno, max (sal) from emp group by deptno; + --------+----------+ | deptno | max (sal) | + --------+----------+ | 20 | 3000.00 | | 30 | 2850.00 | | 10 | 5000.00 | + --------+----------+ 3 rows in set (0.00 sec)

以上这些都是小儿科，现在我们来看看如何将两个字段进行联合分组：

查找每个部门不同岗位的最高薪资：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> select deptno,job, max (sal) -> from emp -> group by deptno,job; + --------+-----------+----------+ | deptno | job | max (sal) | + --------+-----------+----------+ | 20 | CLERK | 1100.00 | | 30 | SALESMAN | 1600.00 | | 20 | MANAGER | 2975.00 | | 30 | MANAGER | 2850.00 | | 10 | MANAGER | 2450.00 | | 20 | ANALYST | 3000.00 | | 10 | PRESIDENT | 5000.00 | | 30 | CLERK | 950.00 | | 10 | CLERK | 1300.00 | + --------+-----------+----------+ 9 rows in set (0.00 sec)

3.小练习

找出每个部门的最高薪资，要求显示最高薪资大于3000的：

请注意：如果我们想要对分完组之后的数据进行再次的过滤，需要使用having子句，having不能单独进行使用，必须和group by进行联合使用

1 2 3 4 5 6 7 8 9 10 mysql> select deptno, max (sal) -> from emp -> group by deptno -> having max (sal) > 3000; + --------+----------+ | deptno | max (sal) | + --------+----------+ | 10 | 5000.00 | + --------+----------+ 1 row in set (0.00 sec)

如上的sql语句效率很低，我们尝试进行一个小的优化：

1 2 3 4 5 6 7 8 9 10 mysql> select deptno, max (sal) -> from emp -> where sal > 3000 -> group by deptno; + --------+----------+ | deptno | max (sal) | + --------+----------+ | 10 | 5000.00 | + --------+----------+ 1 row in set (0.00 sec)

where 和 having 请优先选择where

找出每个部门平均薪资大于2500的：

我们发现无法使用where实现此需求，这时只能使用having子句：

1 2 3 4 5 6 7 8 9 10 mysql> select deptno, avg (sal) -> from emp -> group by deptno -> having avg (sal) > 2500; + --------+-------------+ | deptno | avg (sal) | + --------+-------------+ | 10 | 2916.666667 | + --------+-------------+ 1 row in set (0.00 sec)

4.大BOSS

找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除了MANAGER外，要求按照平均薪资降序排列：

1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> select job, avg (sal) -> from emp -> where job != 'MANAGER' -> group by job -> having avg (sal) > 1500 -> order by avg (sal) desc ; + -----------+-------------+ | job | avg (sal) | + -----------+-------------+ | PRESIDENT | 5000.000000 | | ANALYST | 3000.000000 | + -----------+-------------+ 2 rows in set (0.00 sec)