【20-27】分组函数
2022-02-10 21:32:00 # MySQL

概述

  1. count 计数

  2. sum 求和

  3. avg 平均

  4. max 最大值

  5. min 最小值

  6. 分组函数：多行处理函数，输入多行输出一行

求总和/最高/最低/平均工资

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

求总人数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+
1 row in set (0.00 sec)

多行处理函数自动忽略NULL

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

数学表达式中有NULL出现结果为NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select ename, (sal+comm)*12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

ifnull()空处理函数

将 null 做为 0 处理 

1
ifnull(null, 0)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select ename, (sal+ifnull(comm,0))*12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

count(*)和count(某个字段)的区别

  1. count(*) 计算总记录条数
  2. count(某个字段) 计算该字段不为NULL的个数

group by 和 having

  1. group by: 按照某些字段进行分组
  2. having: 对分组后的数据进行再次过滤
  3. 当sql语句无group by那么整张表自成一组
  4. group by在where之后执行

格式

1
2
3
4
5
6
7
8
9
10
11
12
select      5
    xxx
from        1
    xxx
where       2
    xxx
group by    3
    xxx
having      4
    xxx
order by    6
    xxx

找出每个工作岗位最高薪资

1
2
3
4
5
6
7
8
9
10
11
mysql> select job, max(sal) from emp group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| CLERK     |  1300.00 |
| SALESMAN  |  1600.00 |
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)

找出高于平均工资的员工

运用子查询 

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select ename, sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

找出每个部门不同工作岗位的最高薪资

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

找出每个部门的最高薪资，要求显示薪资大于2900的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select deptno, max(sal) from emp group by deptno having max(sal) > 2900;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     10 |  5000.00 |
+--------+----------+
2 rows in set (0.00 sec)

// 建议使用where，因为参加分组的数据变少，提高效率
mysql> select deptno, max(sal) from emp where sal > 2900 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     10 |  5000.00 |
+--------+----------+
2 rows in set (0.00 sec)

找出每个部门的平均薪资，要求显示薪资大于2000的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select deptno, avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     10 | 2916.666667 |
+--------+-------------+
2 rows in set (0.00 sec)

总结DQL执行顺序

1
2
3
4
5
6
7
select xxx      5
from xxx        1
where xxx       2
group by xxx    3
having xxx      4
order by xxx    6
;

关于查询结果去重

distinct 关键字 

1
2
3
4
5
6
7
8
9
10
11
mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

distinct 关键字必须在所有字段最前面

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select distinct job,deptno from emp order by deptno asc;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     10 |
| MANAGER   |     10 |
| PRESIDENT |     10 |
| ANALYST   |     20 |
| CLERK     |     20 |
| MANAGER   |     20 |
| CLERK     |     30 |
| MANAGER   |     30 |
| SALESMAN  |     30 |
+-----------+--------+
9 rows in set (0.00 sec)

统计岗位数量

1
2
3
4
5
6
7
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)
Prev
2022-02-10 21:32:00 # MySQL
Next