（P120）mysql数据库(十二)：聚合函数，group by/having，group by与子查询

文章目录

• ​​1.聚合函数​​
• ​​2.group by/having​​
• ​​3.group by与子查询​​

1.聚合函数

聚合函数一般用于统计

• 常用如下:

count(field)  //记录数
avg(field)     //平均值
min(field)     //最小值
max(field)    //最大值
sum(field)    //总和

• count()使用

/* 统计所有非空字段 */
mysql> select count(comm) as 'record number' from EMP;
+---------------+
| record number |
+---------------+
|             4 |
+---------------+
1 row in set (0.00 sec)

/* 统计所有行，即所有字段的数量，若一行都死null，也算是一条记录 */
mysql> select count(*) 'record numbers' from SALGRADE;
+----------------+
| record numbers |
+----------------+
|              5 |
+----------------+
1 row in set (0.00 sec)


/* 统计所有行，但是扫描的是行首，行首是空的，也算是一行 */
mysql> select count(0) as 'record number' from EMP;
+---------------+
| record number |
+---------------+
|            15 |
+---------------+
1 row in set (0.00 sec)

/* 统计所有行，但是扫描的是行首，行首是空的，不算是一行 */
mysql> select count(empno) as 'record number' from EMP;
+---------------+
| record number |
+---------------+
|            15 |
+---------------+
1 row in set (0.00 sec)

• 测试：
• 其他聚合函数

select avg(sal) as 平均薪金 from emp;
select max(sal) as 最高薪金 from emp;
select min(sal) as 最低薪金 from emp;
select sum(sal) as 薪金总和 from emp;


select avg(sal) as 平均薪金,
max(sal) as 最高薪金,
min(sal) as 最低薪金,
sum(sal) as 薪金总和
from emp;

2.group by/having

• 分组之后再统计
• 分组查询通常用于统计，一般和聚合函数配合使用

分组查询格式
select 分组字段或聚合函数
from 表 
group by 分组字段 having 条件 
order by 字段

/* 统计各个部门的员工数 */
/* 按照deptno进行分组 */
mysql> select deptno ,count(8) from EMP group by deptno;
+--------+----------+
| deptno | count(8) |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
|     10 |        3 |
|     90 |        1 |
+--------+----------+
4 rows in set (0.00 sec)

/* 分组之后的条件用HAVING，不要用where */
mysql> select deptno ,count(*) cv from EMP group by deptno having cv >= 6;
+--------+----+
| deptno | cv |
+--------+----+
|     30 |  6 |
+--------+----+
1 row in set (0.00 sec)


/* 对cn进行降序 */
mysql> select deptno ,count(*) cv from EMP group by deptno having cv >= 5 order by cv desc;
+--------+----+
| deptno | cv |
+--------+----+
|     30 |  6 |
|     20 |  5 |
+--------+----+
2 rows in set (0.00 sec)

3.group by与子查询

• 要求：查询出薪金成本最高的部门的部门号和部门名称

/* 用连接的方式： 
将dept表和emp表连接在一起，但是只是取出了dept.deptno, dept.dname这俩字段
*/
/* select dept.deptno, dept.dname from dept首先找出部门号和部门名称
为什么sum(sal)取部门总工资，因为这里对它group by dept.deptno, dept.dname分组了，select的字段只能是分组group by的字段
select dept.deptno, dept.dname, sum(sal)
from dept, emp
where dept.deptno=emp.deptno
group by dept.deptno, dept.dname
 */
 /* select sum(sal) from emp group by deptno是每个部门的总薪资，是对部门进行分组，有多条记录的，子查询 
mysql> select sum(sal) from  EMP group by deptno;
+----------+
| sum(sal) |
+----------+
| 10875.00 |
|  9400.00 |
|  8750.00 |
|  1200.00 |
+----------+
4 rows in set (0.00 sec)
*/

mysql> select A.deptno,A.dname 
from DEPT A,EMP B  
where A.deptno=B.deptno 
group by A.deptno,A.dname 
having sum(sal) >=all (select sum(sal) from  EMP group by deptno);
+--------+----------+
| deptno | dname    |
+--------+----------+
|     20 | RESEARCH |
+--------+----------+
1 row in set (0.00 sec)


/* 用子查询的方式 :
select sum(sal) from emp group by deptno 看出是一个子查询

步骤：
首先对各部门的总sal进行统计
mysql> select sum(sal) from EMP group by deptno;
+----------+
| sum(sal) |
+----------+
| 10875.00 |
|  9400.00 |
|  8750.00 |
|  1200.00 |
+----------+
4 rows in set (0.00 sec)

接着求出所有部门sal最大的薪资
mysql> select max(t.total) from (select sum(sal) total from EMP group by deptno) t;
+--------------+
| max(t.total) |
+--------------+
|     10875.00 |
+--------------+
1 row in set (0.00 sec)

最终得到：
select A.deptno, A.dname
from DEPT A,EMP B
where A.deptno=B.deptno
group by A.deptno, A.dname
having max(sal) >=
(select max(t.total) from (select sum(sal) total from EMP group by deptno) t);
*/

mysql> select A.deptno, A.dname 
from DEPT A,EMP B 
where A.deptno=B.deptno 
group by A.deptno, A.dname 
having sum(sal) >=
(select max(t.total) from (select sum(sal) total from EMP group by deptno) t);
+--------+----------+
| deptno | dname    |
+--------+----------+
|     20 | RESEARCH |
+--------+----------+
1 row in set (0.01 sec)

• 小结：

/* 统计所有非空字段 */
select count(comm) as 记录数 from emp;

/* 统计所有行，即所有字段的数量，若一行都死null，也算是一条记录 */
select count(*) as 记录数 from emp;

/* 统计所有行，但是扫描的是行首，行首是空的，也算是一行 */
select count(0) as 记录数 from emp;

/* 统计所有行，但是扫描的是行首，行首是空的，不算是一行 */
select count(empno) as 记录数 from emp;

select avg(sal) as 平均薪金,
max(sal) as 最高薪金,
min(sal) as 最低薪金,
sum(sal) as 薪金总和
from emp;

/* 统计各个部门的员工数 */
/* 按照deptno进行分组 */
select deptno, count(*), from emp group by deptno;
/* 分组之后的条件用HAVING，不要用where */
select deptno, count(*) cn from emp group by deptno HAVING cn > 3;
/* 对cn进行降序 */
select deptno, count(*) cn from emp group by deptno HAVING cn > 3 ORDER BY cn desc;

/*列出各部门信息以及部门人数。 
书写步骤：
第一步：
select * from DEPT;
第二步：
select *,(select count(*) from EMP where EMP.deptno=DEPT.deptno group by deptno) from DEPT;
*/
/* 对emp表进行分组才能求出部门人数，更好*/
select *, (select count(*) from emp group by deptno HAVING deptno = dept.deptno) total 
from dept;

/* ifnull判断查询的字段是否为空，为空则给他一个0 */
select *, ifnull((select count(*) from emp group by deptno HAVING deptno = dept.deptno), 0) total 
from dept;

/* 查询出薪金成本最高的部门的部门号和部门名称 */
/* 用连接的方式： 将dept表和emp表连接在一起，但是只是取出了dept.deptno, dept.dname这俩字段*/
/* select dept.deptno, dept.dname
from dept首先找出部门号和部门名称

为什么sum(sal)取部门总工资，因为这里对它group by dept.deptno, dept.dname分组了，select的字段只能是分组group by的字段
select dept.deptno, dept.dname, sum(sal)
from dept, emp
where dept.deptno=emp.deptno
group by dept.deptno, dept.dname
 */
 /* select sum(sal) from emp group by deptno是每个部门的总薪资，是对部门进行分组，有多条记录的，子查询 */
select dept.deptno, dept.dname
from dept, emp
where dept.deptno=emp.deptno
group by dept.deptno, dept.dname
HAVING sum(sal) >= all (select sum(sal) from emp group by deptno)

/* 用子查询的方式 :select sum(sal) from emp group by deptno 看出是一个子查询
*/
select dept.deptno, dept.dname
from dept, emp
where dept.deptno=emp.deptno
group by dept.deptno, dept.dname
HAVING sum(sal) >= (
select max(t.total)
from
(select sum(sal) total from emp group by deptno) t
)