数据库-进阶5-分组查询

分组查询

#进阶5：分组查询
/*
语法：
	select 分组函数,列(要求出现在 group by 后面)
	from 表
	【分组条件】
	group by 分组的列表
	【order by】子句
注意：
	查询列表比较特殊，要求是分组函数和 group by 后面出现的字段
特点：
	1.分组查询中的筛选条件分为两类
				数据源			位置			关键字
		分组前筛选	原始表			group by 的子句前	where
		分组后筛选	分组后的结果集		group by 的子句后	having
		
		① 分组函数做条件肯定是放在 having 子句中
		② 能用分组前筛选的，就优先考虑分组前筛选
	2.group by子句字段支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求)，表达式或函数(用的较少)
	3.也可以添加排序(排序放在整个分组查询的最后)
	
*/

#引入：查询每个部门的平均工资
	SELECT AVG(salary) FROM employees;
	
#简单的分组查询	
#案例1 ：查询每个工种的最高工资
	SELECT MAX(salary),job_id
	FROM employees
	GROUP BY job_id;
#案例2：查询每个位置上的部门个数
	SELECT COUNT(*),location_id
	FROM departments
	GROUP BY location_id;
	
#添加分组前的筛选条件
#案例 1：查询邮箱中包含 a 字符的，每个部门的平均工资
	SELECT AVG(salary),department_id
	FROM employees
	WHERE email LIKE '%a%'
	ORDER BY department_id;
#案例 2：查询有奖金的每个领导手下员工的最高工资
	SELECT MAX(salary),manager_id
	FROM employees
	WHERE commission_pct IS NOT NULL
	ORDER BY manager_id;
#添加分组后的筛选条件
	#案例1：查询那个部门的员工个数>2
		# ① 查询每个部门的员工个数
			SELECT COUNT(*),department_id
			FROM employees
			ORDER BY department_id;
		# ② 根据 ① 的结果进行筛选，查询那个部门的员工个数>2
			SELECT COUNT(*),department_id
			FROM employees
			ORDER BY department_id
			HAVING COUNT(*) > 2;
	#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
		# ① 查询每个工种有奖金的员工的最高工资
			SELECT MAX(salary),job_id
			FROM employees
			WHERE commision_pct IS NOT NULL
			GROUP BY job_id;
		# ② 根据 ① 继续筛选，最高工资 > 12000
			SELECT MAX(salary),job_id
			FROM employees
			WHERE commision_pct IS NOT NULL
			GROUP BY job_id
			HAVING MAX(salary) > 12000;
	#案例3：查询领导编号>102的每个领导手下的员工的最低工资>5000的领导编号是哪个，以及其最低工资
		# ① 查询每个领导手下的员工固定最低工资
			SELECT MIN(salary),employees_id
			FROM employees
			GROUP BY manager_id;
		# ② 添加筛选条件：编号 > 102
			SELECT MIN(salary),employees_id
			FROM employees
			WHERE manager_id > 102
			GROUP BY manager_id;
		# ③ 添加筛选条件：最低工资 > 5000
			SELECT MIN(salary),employees_id
			FROM employees
			WHERE manager_id > 102
			GROUP BY manager_id
			HAVING MIN(salary) > 5000;
# 按表达式或函数分组
	# 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
		# ① 查询每个长度的员工个数
			SELECT COUNT(*),LENGTH(last_name) len_name
			FROM employees
			GROUP BY LENGTH(last_name);
		# ② 添加筛选条件	
			SELECT COUNT(*),LENGTH(last_name) len_name
			FROM employees
			GROUP BY LENGTH(last_name)
			HAVING COUNT(*) > 5;
			# 支持别名
			SELECT COUNT(*) c,LENGTH(last_name) len_name
			FROM employees
			GROUP BY len_name#mysql支持用别名(where不行)，但是不建议，因为其他数据库不支持
			HAVING c > 5;
# 按多个字段分组
	# 案例：查询每个部门每个工种的员工的平均工资
		SELECT AVG(salary),department_id,job_id
		FROM employees
		GROUP BY department_id,job_id;#将两个字段中相同的内容组合成一组，字段顺序可换，不影响结果
# 添加排序	
	# 案例：查询每个部门每个工种的员工的平均工资
		SELECT AVG(salary),department_id,job_id
		FROM employees
		WHERE department_id IS NOT NULL
		GROUP BY job_id,department_Id
		HAVING AVG(salary) > 10000
		
		ORDER BY AVG(salary) DESC;#除了limit外 order by 放在最后
		
#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序
	SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
	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 department_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 COUNT(*) 人数,job_id
	FROM employees
	GROUP BY job_id;