MySQL入门命令之函数-分组函数

功能：用于统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
datediff 求差、having 分组后筛选
特点：
1、sum、avg一班用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配使用
此为本人学习记录笔记所用，如有侵权请联系删改。

mysql> use myemployees
Database changed
mysql> select sum(salary) from employees;
+-------------+
| sum(salary) |
+-------------+
|   691400.00 |
+-------------+
1 row in set (0.01 sec)

mysql> select avg(salary) from employees;
+-------------+
| avg(salary) |
+-------------+
| 6461.682243 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(salary) from employees;
+-------------+
| min(salary) |
+-------------+
|     2100.00 |
+-------------+
1 row in set (0.00 sec)

mysql> select max(salary) from employees;
+-------------+
| max(salary) |
+-------------+
|    24000.00 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(salary) from employees;
+---------------+
| count(salary) |
+---------------+
|           107 |
+---------------+
1 row in set (0.00 sec)

mysql> select max(hiredate),min(hiredate),datediff(max(hiredate),min(hiredate)) as 差 from employees;
+---------------------+---------------------+------+
| max(hiredate)       | min(hiredate)       | 差   |
+---------------------+---------------------+------+
| 2016-03-03 00:00:00 | 1992-04-03 00:00:00 | 8735 |
+---------------------+---------------------+------+
1 row in set (0.00 sec)

实例

mysql> select sum(salary) as 和,avg(salary) as 平均,max(salary) as 最高,min(salary) as 最低,count(salary) as 个数
    -> from employees;
+-----------+-------------+----------+---------+--------+
| 和        | 平均        | 最高     | 最低    | 个数   |
+-----------+-------------+----------+---------+--------+
| 691400.00 | 6461.682243 | 24000.00 | 2100.00 |    107 |
+-----------+-------------+----------+---------+--------+
1 row in set (0.00 sec)

和distinct搭配使用实例

mysql> select sum(distinct salary),sum(salary) from employees;
+----------------------+-------------+
| sum(distinct salary) | sum(salary) |
+----------------------+-------------+
|            397900.00 |   691400.00 |
+----------------------+-------------+
1 row in set (0.00 sec)

查询表中的行数

mysql> select count(*) from employees;#推荐
+----------+
| count(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from employees;
+----------+
| count(1) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

实战
语法：
select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 字句】

案例一
查询每个工种的最高工资
mysql> select max(salary),job_id
    -> from employees
    -> group by job_id;
+-------------+------------+
| max(salary) | job_id     |
+-------------+------------+
|     8300.00 | AC_ACCOUNT |
|    12000.00 | AC_MGR     |
|     4400.00 | AD_ASST    |
|    24000.00 | AD_PRES    |
|    17000.00 | AD_VP      |
|     9000.00 | FI_ACCOUNT |
|    12000.00 | FI_MGR     |
|     6500.00 | HR_REP     |
|     9000.00 | IT_PROG    |
|    13000.00 | MK_MAN     |
|     6000.00 | MK_REP     |
|    10000.00 | PR_REP     |
|     3100.00 | PU_CLERK   |
|    11000.00 | PU_MAN     |
|    14000.00 | SA_MAN     |
|    11500.00 | SA_REP     |
|     4200.00 | SH_CLERK   |
|     3600.00 | ST_CLERK   |
|     8200.00 | ST_MAN     |
+-------------+------------+
19 rows in set (0.00 sec)
案例二
查询每个位置上的部门个数
mysql> select count(*),location_id
    -> from departments
    -> group by location_id;
+----------+-------------+
| count(*) | location_id |
+----------+-------------+
|        1 |        1400 |
|        1 |        1500 |
|       21 |        1700 |
|        1 |        1800 |
|        1 |        2400 |
|        1 |        2500 |
|        1 |        2700 |
+----------+-------------+
7 rows in set (0.00 sec)
案例三
查询邮箱中包含字母f的每个部门的平均工资
mysql> select avg(salary),department_id
    -> from employees
    -> where email like '%f%'
    -> group by department_id;
+-------------+---------------+
| avg(salary) | department_id |
+-------------+---------------+
| 6000.000000 |            20 |
| 5550.000000 |            50 |
| 9600.000000 |            80 |
| 9000.000000 |           100 |
+-------------+---------------+
4 rows in set (0.00 sec)
案例四
查询有奖金的每个领导手下员工的最高工资
mysql> select max(salary),manager_id
    -> from employees
    -> where commission_pct is not null
    -> group by manager_id;
+-------------+------------+
| max(salary) | manager_id |
+-------------+------------+
|    14000.00 |        100 |
|    10000.00 |        145 |
|    10000.00 |        146 |
|    10500.00 |        147 |
|    11500.00 |        148 |
|    11000.00 |        149 |
+-------------+------------+
6 rows in set (0.00 sec)
案例五
查询每个部门的员工个数
mysql> select count(*),department_id
    -> from employees
    -> group by department_id;
+----------+---------------+
| count(*) | department_id |
+----------+---------------+
|        1 |          NULL |
|        1 |            10 |
|        2 |            20 |
|        6 |            30 |
|        1 |            40 |
|       45 |            50 |
|        5 |            60 |
|        1 |            70 |
|       34 |            80 |
|        3 |            90 |
|        6 |           100 |
|        2 |           110 |
+----------+---------------+
12 rows in set (0.00 sec)
案例六
查询员工人数大于2的部门
mysql> select count(*) ,department_id
    -> from employees
    -> group by department_id
    -> having count(*)>2;
+----------+---------------+
| count(*) | department_id |
+----------+---------------+
|        6 |            30 |
|       45 |            50 |
|        5 |            60 |
|       34 |            80 |
|        3 |            90 |
|        6 |           100 |
+----------+---------------+
6 rows in set (0.00 sec)
案例七
查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
mysql> select job_id,max(salary)
    -> from employees
    -> where commission_pct is not null
    -> group by job_id
    -> having max(salary)>12000;
+--------+-------------+
| job_id | max(salary) |
+--------+-------------+
| SA_MAN |    14000.00 |
+--------+-------------+
1 row in set (0.00 sec)
案例八
mysql> select min(salary),manager_id
    -> from employees
    -> where manager_id>102
    -> group by manager_id
    -> having min(salary)>5000;
+-------------+------------+
| min(salary) | manager_id |
+-------------+------------+
|     6900.00 |        108 |
|     7000.00 |        145 |
|     7000.00 |        146 |
|     6200.00 |        147 |
|     6100.00 |        148 |
|     6200.00 |        149 |
|     6000.00 |        201 |
|     8300.00 |        205 |
+-------------+------------+
8 rows in set (0.00 sec)

更多MySQL命令