mysql学习day7

5.count函数的详细介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;#count 可以用来统计个数
SELECT COUNT(1) FROM employees;#推荐用法，等于在表中加了一列，值填充为1

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

SELECT avg(salary),employee_id FROM employees;#不能正确显示id
#和分组函数一同查询的字段要求是ground by后的字段
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) diffrence from employees;#日期差距

进阶5.分组查询

/*
语法：
SELECT 分组函数，列（要求出现在group by的后面）
from表
（where 筛选条件）
group by 分组的列表
（ORDER BY 字句）
*/
#引入：查询各部门的平均工资
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;
#添加筛选条件
#案例：查询邮箱中包含a字符，每个部门的平均工资
SELECT avg(salary),department_id FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例：查询有奖金的每个领导手下员工的最高工资
SELECT max(salary),manager_id
FROM employees
WHERE commission_pct is not null
GROUP BY manager_id;
#添加付下的筛选条件
#案例1：查询那个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;#在前面的基础上再追加分组条件
#案例2：查询每个工种有奖金的员工最高工资>12000的工种编号和最高工资
#1查询每个工种有奖金的员工最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct is not null
GROUP BY job_id
HAVING max(salary)>12000;
#查询领导编号>102的领到手下的最低工资>5000的领导编号是哪个，以及最低工资
SELECT min(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING min(salary)>5000;
#分组前筛选 where 分组后筛选 HAVING
#按表达式或函数分组也可按别的
#案例：按员工姓名长度分组，查询每一组员工个数，筛选员工个数>5有哪些
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 LENGTH(last_name)
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
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) desc;