目录
聚合函数
一、聚合函数定义:
二、聚合函数类型:
三、使用聚合函数的原则:
四、聚合函数说明
1.AVG 和 SUM 函数:
注意:AVG、SUM函数只对数字类型做处理,其他类型返回0;
1.1AVG(arg)函数:对分组数据做平均值运算。
1.2SUM(arg)函数:对分组数据求和。
2.MIN 和 MAX 函数:
2.2MAX(arg)函数:求分组中最大数据。
3.COUNT 函数:返回分组中的总行数。
4.使用 DISTINCT 关键字:不允许重复
数据分组 GROUP BY
一、创建数据组前言
二、GROUP BY 子句语法
1.使用GROUP BY原则:
在多列上使用分组:
三、约束分组结果:HAVING
聚合函数
一、聚合函数定义:
聚合函数也称之为多行函数,组函数或分组函数。聚合函数不像单行函数,聚合函数对行的分组进行操作,并给出一个结果。如果在查询中没有指定分组,那么聚合函数则将查询到的结果集视为一组(即将整列作为一个组)。
二、聚合函数类型:
三、使用聚合函数的原则:
1.可以使用DISTINCT 使得函数只考虑不重复的值;
2.所有聚合函数忽略空值。为了用一个值代替空值,用 IFNULL 或 COALESCE 函数。
四、聚合函数说明
1.AVG 和 SUM 函数:
注意:AVG、SUM函数只对数字类型做处理,其他类型返回0;
1.1AVG(arg)函数:对分组数据做平均值运算。
arg:参数类型只能是数字类型。
1.2SUM(arg)函数:对分组数据求和。
arg:参数类型只能是数字类型。
例:SELECTAVG(salary),SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
表示:计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。
2.MIN 和 MAX 函数:
2.1MIN(arg)函数:求分组中最小数据。
arg:参数类型可以是字符、数字、 日期。
2.2MAX(arg)函数:求分组中最大数据。
arg:参数类型可以是字符、数字、 日期。
例:SELECT MIN(hire_date), MAX(hire_date) FROM employees;
表示:查询员工表中入职时间最短与最长的员工,并显示他们的入职时间。
3.COUNT 函数:返回分组中的总行数。
COUNT 函数有三种格式:
COUNT(*):返回表中满足 SELECT 语句的所有列的行数,包括重复行,包括有空值列的行。
COUNT(expr):返回在列中的由 expr 指定的非空值的数。
COUNT(DISTINCT expr):返回在列中的由 expr 指定的唯一的非空值的数。
4.使用 DISTINCT 关键字:不允许重复
COUNT(DISTINCT expr)返回对于表达式 expr 非空并且值不相同的行数显示 EMPLOYEES 表中不同部门数的值。
例:SELECT COUNT(commission_pct) FROM employees
WHERE department_id = 80;
表示:显示员工表中部门编号是80中有佣金的雇员人数。
数据分组 GROUP BY
一、创建数据组前言
在没有进行数据分组之前,所有聚合函数是将结果集作为一个大的
信息组进行处理。但是,有时,则需要将表的信息划分为较小的
组,可以用 GROUP BY 子句实现。
二、GROUP BY 子句语法
1.使用GROUP BY原则:
(1)使用 WHERE 子句,可以在划分行成组以前过滤行(即GROUP BY在WHERE条件结果上进行分组)。
(2)如果有WHERE子句,那么GROUP BY 子句必须在WHERE的子句后面,在ORDER BY前面。
(3)在 GROUP BY 子句中必须包含列。
例:SELECT DEPARTMENT_ID, COUNT(*)
FROM employees
GROUP BY DEPARTMENT_ID;
表示:计算每个部门的员工总数。
在多列上使用分组:
注意:上图中group by department_id,job_id表示先对department_id分组,在department_id分组的基础上再对job_id 分组;
例:SELECT e.DEPARTMENT_ID, e.JOB_ID,COUNT(*)
FROM employees e
GROUP BY e.DEPARTMENT_ID,e.JOB_ID;
表示:计算每个部门的不同工作岗位的员工总数。
三、约束分组结果:HAVING
1.HAVING 子句
HAVING 子句是对查询出结果集分组后的结果进行过滤。
注意:HAVING只能用在分组结果中
2.HAVING子句语法:
例:SELECT job_id, SUM(salary) PAYROLL FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_idHAVING SUM(salary) > 13000
ORDER BY SUM(salary);
表示:显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位,并且用合计月薪排序列表。
例:SELECTMANAGER_ID,min(SALARY)
FROMemployees
whereMANAGER_ID is not null
GROUP BYMANAGER_IDhavingMIN(SALARY)>6000;
ORDER BYMIN(salary) DESC;
表示:显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人,排除最低薪水小于等于¥6,000的组,按薪水降序输出。
例:SELECT
d.DEPARTMENT_NAME,d.LOCATION_ID,COUNT(*),round(AVG( e.SALARY)
FROM employees e,departments d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME,d.LOCATION_ID;
表示:写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。