MySQL学习记录（五）--分组查询

 #进阶5：分组查询
 /*
 语法： select 分组函数，列（要求出现在group by后）
	from 表
	【where 筛选条件】
	group by 分组的列表
	【order by 子句】
注意：
	查询列表和group by后要相同
 
特点:筛选条件可以分为两类：
		 数据源			位置			关键字
	分组前： 原始表			group by 子句前		where
	分组后 ：分组后的数据表		group by 子句后		having
	
	支持单字段分组和多字段分组（逗号隔开），表达式或函数（较少）
	也可添加排序
 */
 
 
 #引入：查询每个部分的平均工资
 
 SELECT AVG(salary) FROM `employees`
 
 #案例1：查询每个工种的最高工资
 SELECT MAX(`salary`),`job_id`
 FROM`employees`
 GROUP BY `job_id`;
 
 #案例二：每个位置的部门个数
 SELECT COUNT(*),`location_id`
 FROM`departments`
 GROUP BY `location_id`
 
 #案例3：查询邮箱中包含a字符的，每个部门的平均工资
 SELECT AVG(`salary`),`email`,`department_id`
 FROM `employees`
 WHERE `email`LIKE'%a%'
 GROUP BY department_id
 
 #复杂筛选条件
 #添加分组后的筛选
 #查询哪个部门的员工个数大于2
 SELECT COUNT(*),`department_id`FROM`employees`GROUP BY `department_id`HAVING COUNT(*)>2
 
 #案例
 SELECT MAX(`salary`), `job_id`AS 最高工资
 FROM `employees`
 WHERE  `commission_pct` IS NOT NULL
 GROUP BY `job_id`
 HAVING 最高工资>12000;
 
 #案例
 SELECT MIN(`salary`),MAX(`salary`),`manager_id`
 FROM`employees`
 WHERE `manager_id`>102 
 GROUP BY `manager_id`
 HAVING MIN(`salary`)>5000 AND MAX(`salary`) <9000

 #按函数或表达式分组
 #案例
 SELECT COUNT(*),LENGTH(`last_name`)
 FROM `employees`
 GROUP BY LENGTH(`last_name`)
 HAVING COUNT(*) >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 `department_id`,`job_id`
 HAVING AVG(`salary`)>10000
 ORDER BY AVG(`salary`)DESC
 
 #练习
 SELECT MIN(`salary`),`job_id`
 FROM `employees`
 GROUP BY `job_id`
 ORDER BY`job_id`
 
 SELECT MIN(`salary`),`manager_id`
 FROM `employees`
 WHERE `manager_id` IS NOT NULL
 GROUP BY `manager_id`
 HAVING MIN(`salary`)>6000