四 . 分组函数
一.分组函数/聚合函数/多行处理函数
输入多行,处理一行,会自动忽略NULL值
count:计数
sum:求和
avg:求平均
max:最大值
min:最小值
注意:所有分组函数都是针对某一组数据进行操作的。
1.eg:分数求和。
mysql> select sum(score)
-> from t_student;
+------------+
| sum(score) |
+------------+
| 1325 |
+------------+
2.eg:求平均值
mysql> select avg(score) from t_student;
+------------+
| avg(score) |
+------------+
| 73.6111 |
+------------+
注意:分组函数自动忽略NULL,在所有数据库中,只要
有null参与运算结果都为空。
单行处理函数(输入单行,输出单行)
3.eg:null = 828 + null;
方法:使用ifnull函数,是单行处理函数(可能为空的数据,被当作的某个值)
mysql> select name,ifnull(score,0)
-> from t_student;
+------+-----------------+
| name | ifnull(score,0) |
+------+-----------------+
| 张三 | 70 |
| 张三 | 80 |
| 张三 | 66 |
| 李四 | 50 |
| 李四 | 75 |
| 李四 | 80 |
| 王五 | 77 |
| 王五 | 55 |
| 王五 | 88 |
| 王五 | 90 |
+------+-----------------+
二.分组查询
1.eg:找出成绩高于平均分的学生。
mysql> select name,score
-> from t_student
-> where score>avg(score);
ERROR 1111 (HY000): Invalid use of group function
报错原因:
SQL语句的语法规则:分组函数不可直接使用在where子句中。
count * 和 count 具体字段,之间的区别?
count *:统计总记录条数,与null无关。(和某个字段无关)
mysql> select count(*) from t_student;
+----------+
| count(*) |
+----------+
| 18 |
+----------+
count score:除null之外所有的数据总数量。
2.eg:分组函数组合使用
mysql> select count(*),sum(score),avg(score)
-> from t_student;
+----------+------------+------------+
| count(*) | sum(score) | avg(score) |
+----------+------------+------------+
| 18 | 1325 | 73.6111 |
+----------+------------+------------+
3.group by,having
group by:按照某个字段或某些字段进行分组。
having:对分组后的数据(不满意)进行再次过滤 。
注:分组函数和group by 联用时,任何一个分组函数
(count,sum,avg,max,min)都是在group by
语句执行后才执行。
分组函数不可直接使用在where子句中。因为group
by实在where执行之后才执行的。实现先分组,再使
用分组函数。
eg:求年龄大于平均年龄的学生姓名,学号。
思路:
step1:找出平均年龄
step2:找出大于平均年龄的学生信息
select name,id
from Student
where(
select avg(age)
from Student;
);
4.多字段分组查询
执行顺序:
select (5)
***
from (1)
***
where (2)
***
group by (3)
***
having (4)
***
order by (6)
***
eg1:找出每个部门不同岗位的最高薪资。
select 部门id,job,max(sal),
from emp
group by 部门id,job;
eg2:找出每个部门的最高薪资,且薪资高于2000。
方法一:
select max(sal),部门id
from emp
group by 部门id
having max(sal)>2000;
执行效率低,筛选数据不及时。
方法二:
select max(sal),部门id
from emp
where sal>2000
group by 部门id
eg3:找出每个部门的平均薪资,且薪资高于2000。
select avg(sal),部门id
from emp
group by 部门id
having avg(sal)>2000;