分组函数和分组查询

分组函数

#英谷教育
#官网：http://www.121ugrow.com/
#讲师：刘峰吉

#分组函数
/*
函数，类似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;

#三、关于max和min
/*
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()的效果是一样的，但是效率不一样

#问题：查询员工表最大工资和员工号?
#答：不能！！！！

不允许和分组函数，一起执行任意字段，因为employee_id这个字段的值是多个，分组函数的值是一个，一和多无法形成规则的表格

SELECT MAX(salary),employee_id FROM employees;

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

#引入：查询各部门的最高工资
SELECT MAX(salary) FROM employees WHERE department_id IN(20,90,30);
SELECT MAX(salary) FROM employees GRO UP BY department_id;

看图
/*

语法：
select 分组函数 别名,分组后的字段
from 表
【where 分组前筛选】
group by 分组的字段
【having 分组后筛选】
【order by 排序的字段】
从上到下依次顺序，不能颠倒顺序

分组查询的特点：
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;

#三、分组查询+筛选having
/*
筛选条件分两类：
分组前筛选：筛选的条件针对的是原始表
分组后筛选：筛选的条件针对的是分组后的结果集
针对的表 位置 使用的关键字
分组前筛选 原始表 group by的前面 where
分组后筛选 分组后的结果集 group by的后面 having
where——group by——having
#四、having子句支持别名

#五、排序 order by 支持别名