MySQL数据库的聚合函数和分组查询
1. 聚合函数
聚合函数一般用于统计,常用如下:
count(field) //记录数
avg(field) //平均值
min(field) //最小值
max(field) //最大值
sum(field) //总和
1.1 coun()的使用
- count统计一张表的记录数
mysql> select count(*) as 记录数 from EMP;
//用*代替所有字段,可以得到一张表的行数,下列语句也可以,且速度快,因为只扫描第一个字段。
//select count(0) as 记录数 from EMP;
+-----------+
| 记录数 |
+-----------+
| 15 |
+-----------+
- count统计一个字段非空部分的记录数
mysql> select count(comm) as 记录数 from EMP;
//comm是字段名
+-----------+
| 记录数 |
+-----------+
| 4 |
+-----------+
1.2 其他聚合函数的使用
mysql> select avg(sal) as 平均工资,
max(sal) as 最高工资,
min(sal) as 最低工资,
sum(sal) as 工资总和
from EMP;
+--------------+--------------+--------------+--------------+
| 平均工资 | 最高工资 | 最低工资 | 工资总和 |
+--------------+--------------+--------------+--------------+
| 2015.000000 | 5000.00 | 800.00 | 30225.00 |
+--------------+--------------+--------------+--------------+
2. group by/having
分组查询通常用于统计,一把和聚合函数配合使用。
分组查询格式如下:
select 分组字段或聚合函数
from 表
group by 分组字段 having 条件
order by 字段
mysql> select deptno, count(*) 数量 from EMP group by deptno;
//根据部分号分组查询出每个部分的记录数。
+--------+--------+
| deptno | 数量 |
+--------+--------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
| 50 | 1 |
+--------+--------+
mysql> select deptno, count(*) 数量 from EMP group by deptno having 数量 > 3 order by 数量 desc;
//查询条件不能使用WHERE关键字,使用HAVING
+--------+--------+
| deptno | 数量 |
+--------+--------+
| 30 | 6 |
| 20 | 5 |
+--------+--------+
3. group by与子查询
group by配合子查询可以统计出以组为单位的信息。
3.1 列出各部门信息以及每个部门的人数
mysql> select *, (select count(*) from EMP group by deptno having deptno = DEPT.deptno ) total from DEPT;
+--------+------------+----------+-------+
| deptno | dname | loc | total |
+--------+------------+----------+-------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | NULL |
+--------+------------+----------+-------+
//如果要将NULL改为0,可以使用ifnull函数
mysql> select *, ifnull((select count(*) from EMP group by deptno having EMP.deptno = DEPT.deptno), 0) total from DEPT;
+--------+------------+----------+-------+
| deptno | dname | loc | total |
+--------+------------+----------+-------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+-------+
3.2查询出工资最高的部门号,部门名称和总工资
1. 先查找出每个人的部门号,部门名称和工资
mysql> select DEPT.deptno, DEPT.dname, sal from DEPT, EMP where DEPT.deptno = EMP.deptno ;
+--------+------------+---------+
| deptno | dname | sal |
+--------+------------+---------+
| 20 | RESEARCH | 800.00 |
| 30 | SALES | 1600.00 |
| 30 | SALES | 1250.00 |
| 20 | RESEARCH | 2975.00 |
| 30 | SALES | 1250.00 |
| 30 | SALES | 2850.00 |
| 10 | ACCOUNTING | 2450.00 |
| 20 | RESEARCH | 3000.00 |
| 10 | ACCOUNTING | 5000.00 |
| 30 | SALES | 1500.00 |
| 20 | RESEARCH | 1100.00 |
| 30 | SALES | 950.00 |
| 20 | RESEARCH | 3000.00 |
| 10 | ACCOUNTING | 1300.00 |
+--------+------------+---------+
2. 在以各部门分组查询出总工资,部门号,部门名称。
mysql> select DEPT.deptno, DEPT.dname, sum(sal) from DEPT, EMP where DEPT.deptno = EMP.deptno group by DEPT.deptno ;
+--------+------------+----------+
| deptno | dname | sum(sal) |
+--------+------------+----------+
| 10 | ACCOUNTING | 8750.00 |
| 20 | RESEARCH | 10875.00 |
| 30 | SALES | 9400.00 |
+--------+------------+----------+
3. 在从总工资数中找出工资最高的部门(使用HAVING关键字加条件,通过子查询完成)。
mysql> select DEPT.deptno, DEPT.dname, sum(sal) from DEPT, EMP where DEPT.deptno = EMP.deptno group by DEPT.deptno having sum(sal) >= all (select sum(sal) from EMP group by EMP.deptno) ;
// >= all() 等于取最大值
+--------+----------+----------+
| deptno | dname | sum(sal) |
+--------+----------+----------+
| 20 | RESEARCH | 10875.00 |
+--------+----------+----------+