数据库教程:MySQL必备基础之分组函数 聚合函数 分组查询详解

目录

• 二、搭配distinct去重
• 三、count()详细介绍

  select sum(salary) as 和,avg(salary) as 平均,max(salary) as 最大,min(salary) as 最小,count(salary) as 数量  from employees;  

  select sum(distinct salary) as 和,avg(distinct salary) as 平均,count( distinct salary) as 去重数量,count(salary) as 不去重数量  from employees;  

  #相当于统计行数方式一  select count(*)   from employees;  

  #相当于统计行数方式二，其中1可以用其他常量或字段替换  select count(1)   from employees;  

  #其中[]内为可选  select 分组函数,列表（要求出现在 group by 的后面）  from 表  [where 筛选条件]  group by 分组列表  [order by 子句]  

  #查询每个工种的最高工资  select max(salary) as 最高工资,job_id  from employees  group by job_id;  

  #查询每个部门中，邮箱包含a的员工的平均工资（分组前的筛选）  select avg(salary) as 平均工资,department_id  from employees  where email like '%a%'  group by department_id;  

  #查询部门员工数量大于2的部门的员工数量（分组后的筛选）  #使用having  select count(*) as 员工数量,department_id  from employees  group by department_id  having count(*)>2;  

  #按照多字段  select count(*) as 员工数量,job_id,department_id  from employees  group by job_id,department_id;  

  #完整结构  select avg(salary) as 平均工资,department_id  from employees  where department_id is not null  group by department_id  having avg(salary)>9000  order by avg(salary) desc;