数据库-新手笔记(聚合函数,分组)-进阶

说明 

teacher表(name(姓名),title(职位),gender(性别),comm(奖金),salary(工资),subject_id(课程号))

student(name(姓名),job(职位),gender(性别),class_id(班级编号))

聚合函数

定义

聚合函数又称为多行函数，分组函数。对记录按照指定的字段进行统计并得出一行记录结果

聚合函数分类

MIN：统计指定字段的最小值

MAX：统计指定字段的最大值

SUM：统计指定字段的总和

AVG：统计指定字段的平均值

COUNT：不是对字段值的统计，而是对记录数进行的统计，统计共有多少条记录

模版(例子 查看工资平均值 最大值 最小值 工资总额)

#查询工资平均值
SELECT AVG(salary) FROM teacher;

#查询工资最大值
SELECT MAX(salary) FROM teacher;


#查询工资最小值
SELECT MAX(salary) FROM teacher;


#查询工资总数
SELECT SUM(salary) FROM teacher;


#查询老师总数
SELECT COUNT(name) FROM teacher;

注意事项

例 聚合函数忽略NULL值，AVG,COUNT最为明显

MIN,MAX,SUM,AVG是对值的统计，COUNT是对记录数的统计

这边奖金是有null值的,这边可以看出有3个老师为null

#查询comm是null并计算其个数
SELECT COUNT(teacher.comm) FROM teacher WHERE comm IS NULL;

解决方式 COUNT(*)统计记录数

//统计记录条数包括null的值
SELECT COUNT(*) FROM teacher;

 这边对奖金为null的老师取平均值

正常来说 应该是0,但是null不算值

//求奖金为null的平均值
SELECT AVG(comm) FROM teacher WHERE comm IS NULL;

而 AVG(IFNULL,0) 可以将null值转化成0,可以使其被记录可以求平均值

//求奖金为null的平均值
SELECT AVG(IFNULL(comm,0)) FROM teacher WHERE comm IS NULL;

 GROUP BY 分组

GROUP BY子句的作用是在DQL中将结果集按照指定的字段值相同的记录分组，并配合聚合函数进行分组统计使用

在SELECT子句中出现了聚合函数时，那么不在聚合函数中的字段都应当出现在GROUP BY子句中 GROUP BY子句通常是配合聚合函数的，因此如果SELECT子句中没有聚合函数，通常不会用 GROUP BY子句

#查看每种职位的老师平均工资是多少
SELECT DISTINCT title , AVG(salary) 
FROM teacher
GROUP BY title;

按照多字段分组

GOURP BY子句后面可以指定多个字段，只有结果集中这些字段值都相同的记录才会看作一组 

 #查看每个班的职位的数量
 SELECT COUNT(*),class_id,job
 FROM student
 GROUP BY class_id,job

注意可以使用count(*)统计每个组的数量 ,常用与多字段分组查询

排序

ORDER BY  字段名 

#每个班老师工资的平均值
 SELECT class_id,AVG(salary)
 FROM teacher
 GROUP BY class_id
 ORDER BY AVG(salary)

按照聚合函数排序的建议定定别名,按照字段别名对结果集排序

 

#每个班老师工资的平均值
 SELECT class_id,AVG(salary) avg_sal
 FROM teacher
 GROUP BY class_id
 ORDER BY avg_sal;

HAVING

HAVING子句是紧跟在GROUP BY子句之后的，用于添加过滤条件来筛选分组的

HAVING与WHER的区别

相同点在于:都可以添加过滤条件 HAVING子句可以使用聚合函数作为过滤条件

HAVING子句是用来过滤GOURP BY对结果集进行的分组

WHERE是先进行过滤，产生结果集后并根据GOURP BY分组后 HAVING才参与进来对分许进行过滤

#查看每个科目老师的平均工资?但是仅查看平局工资高于6000的那些
SELECT AVG(salary) subject_id
FROM teacher
GROUP BY subject_id
HAVING MAX(salary)>6000