文章目录
分组查询
一、分组函数(聚合函数)
1.聚合函数通过作用一组值而只返回一个单一值,因此,在SELECT语句中出现的字段要么为一个聚合函数的输入值,如COUNT(course),要么为GROUP BY语句中指定的字段,要么是常数,否则会出错。
简而言之:使用GROUP BY子句时,SELECT子句中只能有聚合键、聚合函数、常数。 一个聚合字段对应多个非聚合字段,选择的时候,会随机选择非聚合字段中的任何一个
2.功能:用作统计使用,又称为聚合函数或统计函数或组函数
3.分类:
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
4.特点:
①sum、avg一般用于处理数值型
max、min、count可以处理任何类型
②以上分组函数都忽略null值
③可以和distinct搭配实现去重的运算
select sum(distinct(salary));
④count函数的单独介绍
一般使用count()用作统计行数
count(字段):统计该字段非空值的个数
count():统计结果集的行数
效率上:
MyISAM存储引擎,count()最高
InnoDB存储引擎,count()和count(1)效率>count(字段)
⑤和分组函数一同查询的字段要求是group by后的字段
5.with rollup函数:此函数是对聚合函数进行求和,注意 with rollup是对 group by 后的第一个字段,进行分组求和。
6.group_concat(【order by】,【separator】)
:将组中的字符串连接成为具有各种选项的单个字符串,separator指定在组中的值之间插入的文字值。如果不指定分隔符,则GROUP_CONCAT函数使用逗号(,)作为默认分隔符;GROUP_CONCAT函数忽略NULL值,如果找不到匹配的行,或者所有参数都为NULL值,则返回NULL。
二、分组查询
1.语法
select 分组函数,被分组的列名(要求出现在group by的后面)
from 表名
【where 筛选条件】
group by 分组的字段
【having 分组后筛选】
【order by 子句】;
**注意:**查询列表必须特殊,要求是分组函数和group by后出现的字段;where语句必须在from后 group by前。
2.特点
1)分组查询可分为两类:
使用关键字 | 筛选的表 | 位置 |
---|---|---|
分组前筛选 where | 原始表 | group by的前面 |
分组后筛选 having | 分组后的结果 | group by 的后面 |
①分组函数做条件肯定是放在having子句中
②能用分组前筛选的,就优先考虑使用分组前筛选
2)group by子句中支持单个字段分组,多个字段分组(多个字段之间用逗号隔开,没有顺序要求),表达式或函数用的较少;
3)可以添加排序:放在分组查询最后
4)order by之后可以用列名,还可以用列的序号来进行排序(order by 2)
3.简单的分组查询
#案例1:查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id;
4.添加筛选条件
添加筛选前分组
#案例1:查询邮箱中包含a字符的,每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;
添加分组后的筛选
having
添加复杂的筛选条件
#案例2:查询哪个部门的员工个数>2
select count(*),department_id
from employees
group by department_id
having count(*)>2;
#案例3:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id
from eployees
where commission_pct is not null
group by job_id
having max(salary)>