mysql—基础 WHERE筛选-GROUP BY分组-HAVIN对分组后的数据查询

/*
查询姓名中包含C字符的，每个部分的最低工资
*/
SELECT 
CONCAT (`last_name`,' ',`first_name`)AS 姓名,
`department_id` AS 部门,
MIN(`salary`) AS 工资
FROM `employees`
WHERE `first_name` LIKE '%c%' || `last_name` LIKE '%c%'
GROUP BY `department_id`;
/*
查询有奖金的每个领导手下员工的平均工资和领导的年收入
*/
SELECT `manager_id` AS 领导编号,
`first_name` AS 员工,
AVG(`salary`) AS 平均工资,
(`salary`*12)+(`salary`*12*`commission_pct`)AS 领导年收入
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`;

 #查询那个部门的员工个数>3
SELECT `department_id`AS 部门,
COUNT(`department_id`) AS 员工数量
FROM `employees`
GROUP BY `department_id`
HAVING COUNT(`department_id`)>3;
  
  #每个工种有奖金的员工的最高工资>10000的工种编号和最高工资
  #职位ID求有奖金的员工编号的的职位的最高工资大于一万
SELECT 
  `job_id` AS 职位,
  MAX(`salary`) AS 最高工资,
  `employee_id`AS 员工编号
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL AND `salary` > 10000 
GROUP BY `job_id` ;

#领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT 
`manager_id` AS 领导编号,
MIN(`salary`) AS 最低工资
FROM `employees`
WHERE `manager_id`>102   ##筛选
GROUP BY `manager_id`##分组
HAVING MIN(`salary`)>5000;##分组后的数据查询 跟GROUP BY通常一起出现