mysql分组函数与查询学习04

分组函数

定义： 函数，类似java中的方法，分组函数，又称为统计函数或聚合函数

length(‘john’) 传入一个值，返回一个值，一对一，像这种函数，就不是分组函数，叫做单行函数，分组函数表示这个函数给出一组值,最后返回一个值
max(salary) 分组函数，给它一组值，比如说给它一列值

分组函数的分类
max():最大值
min():最小值
sum():和
avg():平均值
count():计算非空的个数
这些是通用的，sqlserver、oracle、mysql 都是一样的

特点：
1、sum、avg只支持数值型，count、max、min支持任何类型
2、这几个分组函数，都忽略null值
3、count()分组函数，支持以下写法
count(*) 建议使用
count(1)
表示 查询结果集的行数

#分组函数

USE myemployees;

#一、查询分组函数
SELECT MAX(salary) 最大值 FROM employees;
SELECT MIN(salary) 最小值 FROM employees;
SELECT SUM(salary) 和 FROM employees;
SELECT AVG(salary) 平均值 FROM employees;
SELECT COUNT(salary) 个数 FROM employees;
SELECT MAX(salary) 最大值,SUM(salary) 和 FROM employees;


#二、关于sum 和avg
/*
1.只支持数值型
2.sum和avg都忽略null值
*/
SELECT AVG(last_name) FROM employees;
SELECT SUM(last_name) FROM employees;

SELECT * FROM employees;
SELECT SUM(commission_pct)/107,AVG(commission_pct) FROM employees;
 /*
1.支持任何类型
2.max和min都忽略null值
*/
SELECT MAX(last_name),MIN(last_name) FROM employees;  //判断类型
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;  //判断是否忽略null

#四、关于count
SELECT COUNT(commission_pct) FROM employees;    //计算非空的值，忽略null、
//查询员工表占了几个部门
SELECT COUNT(DISTINCT department_id) FROM employees;

#查询结果集中的行数
/*
count(1)的性能没有count（*）高
因为count(*)，sql服务器对其进行了优化，所以效率高

*/
SELECT COUNT(*) FROM employees;  //查看有多少条数据 
//计算满足条件的行数
SELECT COUNT(*) FROM employees WHERE department_id>30;
SELECT COUNT(1) FROM employees;  //和count(*)的效果是一样的，但是效率不一样
SELECT MAX(salary),employee_id FROM employees;

分组查询

定义：
和分组函数一起搭配使用的查询，我们叫做分组查询，换句话说，分组查询一般都会用到分组函数

分组查询的特点：
1、按多个字段分组，字段间用逗号隔开，没有顺序要求
2、可以和分组函数一起查询的字段，只能是分组后的字段，不能是任意字段
3、分组筛选
针对的表 位置 使用的关键字
分组前筛选 原始表 group by的前面 where
分组后筛选 分组后的结果集 group by的后面 having
顺序：where——group by——having
4、having和group by支持别名
例如：

#一、普通的分组查询
#01 案例：查询各部门的最高工资和部门号
SELECT MAX(salary),department_id FROM employees GROUP BY department_id;

#二、按多个字段分组
#案例：查询每个工种、每个部门的平均工资
SELECT AVG(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id;


#案例1：查询有奖金的，每个部门的最高奖金率
SELECT MAX(commission_pct) 最高奖金率,department_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY department_id;


#案例2：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号
SELECT department_id,MIN(salary) 最低工资
FROM employees
WHERE last_name LIKE '%a%'  
GROUP BY department_id
HAVING MIN(salary)>3000;


#案例3：查询电话以“515”开头的，工种号包含字符'T'的,每个工种的平均工资>5000的工种号和平均工资
SELECT job_id,AVG(salary)
FROM employees
WHERE phone_number LIKE '515%' AND job_id LIKE '%T%'
GROUP BY job_id
HAVING AVG(salary)>5000;


#案例4：查询每个地区的部门个数，求个数大于2的部门个数和地区号
SELECT COUNT(*) 个数,location_id
FROM departments
GROUP BY location_id
HAVING COUNT(*)>2;


#四、having子句支持别名
#案例：查询每个地区的部门个数，求个数大于2的部门个数和地区号
SELECT COUNT(*) 个数,location_id 
FROM departments
GROUP BY location_id
HAVING 个数>2;