功能:用于统计使用,又称为聚合函数或统计函数或组函数
分类:
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)