Day2续--------分组查询

1. 分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数。

* count：计算个数
    * 一般选择非空的列：主键
    * count(*)
* max：计算最大值
* min：计算最小值
* sum：计算和
* avg：计算平均值
* 特点：
    1、以上五个分组函数都忽略null值，除了 count(*)
    2、sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
    3、都可以搭配distinct使用，用于统计去重后的结果
    4、count的参数可以支持：
        * 字段、*、常量值，一般放1
* 注意：聚合函数的计算，排除null值。
    解决方案：
        * 选择不包含非空的列进行计算
        * IFNULL函数
 

#二、分组函数

/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数。

分类：
sum 求和 、avg 平均值 、max 最大值、 min 最小值 、count  计算个数


特点： 
1.sum、avg 一般用于处理数值型
	max、min、count 可以处理任何类型

2.以上分组函数都忽略null值

3.可以和distinct搭配

4.count函数的单独介绍
 
 一般使用count(*)作统计行数
 
 5.和分组函数一同查询的字段要求是group by后的字段
*/


#1. 简单的使用

SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;
SELECT MAX(salary) from employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;


SELECT SUM(salary),ROUND(AVG(salary),2),MAX(salary),MIN(salary),COUNT(salary) FROM employees;

#2.参数支持哪些类型

#有些问题不会报错但是是没有意义的
SELECT SUM(last_name),AVG(last_name) FROM employees;


#以下是支持多种类型的
SELECT MAX(last_name) ,MIN(last_name) ,COUNT(last_name) FROM employees;

SELECT MAX(hiredate),MIN(hiredate),COUNT(last_name) FROM employees;

SELECT COUNT(commission_pct) FROM employees;

#3.是否忽略null


SELECT SUM(commission_pct),AVG(commission_pct)  FROM employees;


SELECT NULL+'2'; #null加任何=null


#4.和distinct搭配实现去重的运算

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary) FROM employees;


#5. count 函数的详细介绍

#统计行数
SELECT COUNT( *) FROM employees;

SELECT COUNT(2) FROM employees;


#效率：
MyISAM 存储引擎下， COUNT(*)的效率高

INNODB 存储引擎下，count(*)和count（1）的效率差不多，比count（字段）高

#6.和分组函数一同查询的字段有限制

#逻辑错误
SELECT avg(salary) ,employee_id FROM employees;


SELECT AVG(salary) FROM employees GROUP BY employee_id;

相关案例

#1. 查询公司员工工资的最大值、最小值、平均值、总和

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees;

#2.查询员工表中最大入职时间和最小入职时间的相差天数（DIFFRENCE）

SELECT MAX(hiredate),MIN(hiredate),
DATEDIFF(MAX(hiredate),MIN(hiredate))  DIFFRENCE
FROM employees;


SELECT DATEDIFF('2021-9-3','2000-7-13');
#3.查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE department_id=90;

2.分组函数进阶

#进阶5：分组查询
/*GROUP BY 字句语法

SELECT 分组函数，列（要求出现在 GROUP BY的后面）
FROM 表
[WHERE 筛选条件]
GROUP BY 分组的列表
[ORDER BY 子句]

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：
        1.分组查询中的筛选条件分为两类
                                            数据源                        位置
                    分组前筛选    原始表                        GROUP By子句的前面
                    分组后筛选    分组后的结果集        GROUP BY子句的后面

                    ①分组函数做条件的话肯定是放在having子句中
                    ②能用分组前筛选的，就优先考虑分组前筛选
                    
        2.GROUP BY 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求）  表达式或函数（用的较少）
                              3.也可以添加排序（排序放在整个分组查询的最后）
*/
#引入：查询每个部门的平均工资

SELECT AVG(salary) FROM employees GROUP BY department_id;

#简单的分组查询
#案例一：查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
  
    
#案例二：查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;

#添加分组前的筛选条件
 # 案例一：查询邮箱中包含a字符的，每个部门的平均工资
 
 SELECT AVG(salary) ,department_id,email FROM employees WHERE email LIKE '%e%'    GROUP BY 
 department_id;
 
 #案例二、 查询有奖金的每个领导手下员工的最高工资
 
 SELECT MAX(salary) ,manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
 
 
 #添加分组后的筛选条件
 
 #案例一 查询哪个部门的员工个数>2
 
 SELECT COUNT(*) ,department_id FROM employees 
 GROUP BY department_id 
 HAVING COUNT(*) >2;
 
 #案例二 查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资
 
 SELECT MAX(salary),job_id FROM employees   WHERE commission_pct IS NOT NULL  GROUP BY job_id  HAVING MAX(salary)>12000;
 
 
 #案例三   查询领导编号>102 的每个领导 手下的最低工资>5000的领导编号是哪个
 
 
 SELECT manager_id,MIN(salary) FROM employees 
 WHERE manager_id >102 
 GROUP BY manager_id 
 HAVING MIN(salary)>5000;
 
 
 
 #按表达式或函数分组
 
 #案例： 按员工姓名的长度分组  ，查询每一组员工个数  筛选员工个数＞5的有哪些
 
 
 SELECT LENGTH(last_name),COUNT(*) FROM employees  GROUP BY LENGTH(last_name) HAVING 
 COUNT(1)>5;
 
 
 #按多个字段分组
 
 #案例： 查询每个部门每个工种的员工的平均工资
 
 SELECT AVG(salary) ,department_id,job_id FROM employees GROUP BY department_id,job_id;
 
 
 # 添加排序
  #案例： 查询部门编号>100的每个部门每个工种的员工的平均工资的并按平均工资降序
    
    SELECT AVG(salary),department_id,job_id FROM employees 
    WHERE department_id IS NOT NULL  GROUP BY department_id,job_id 
    HAVING department_id >100
    ORDER BY AVG(salary) DESC;

 
 

相关案例

-- 1.查询各job_id 的员工工资的最大值，最小值，平均值，总和并按job_id 升序
-- 

SELECT  MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees 

GROUP BY job_id;
ORDER BY job_id ;


#2.	查询员工最高工资和最低工资的差距  （DIFFERENCE）

SELECT MAX(salary)-MIN( salary) DIFFERENCE FROM employees;

#3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT MIN(salary) ,manager_id FROM employees WHERE manager_id is 	NOT NULL

GROUP BY manager_id HAVING MIN(salary)>=6000;
#4. 查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT department_id ,COUNT(*) ,AVG(salary) FROM employees

GROUP BY department_id
ORDER BY AVG(salary) DESC;

#5. 选择具有各个job_id 的员工人数

SELECT job_id ,COUNT(*) FROM employees GROUP BY job_id;