聚合函数

聚合函数介绍

聚合函数

  聚合函数也称之为多行函数，组函数或分组函数。聚合函数不象单行函数，聚合函数对行的分组进行操作，对每组给出一个结果。如果在查询中没有指定分组，那么聚合函数则将查询到的结果集视为一组。

聚合函数类型

聚合函数说明：

【MySQL 教程（七）】聚合函数、数据分组与子查询：AVG/SUM/COUNT、GROUP BY、HAVING、单行/多行子查询

聚合函数使用方式

使用聚合函数的原则

 DISTINCT 使得函数只考虑不重复的值；所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。

AVG 和 SUM 函数

AVG(arg)函数

• 对分组数据做平均值运算。
• arg:参数类型只能是数字类型。

SUM(arg)函数

• 对分组数据求和。
• arg:参数类型只能是数字类型。

示例：计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。

select avg(salary),sum(salary)
from employees
where job_id like '%REP%';

MIN 和 MAX 函数

MIN(arg)函数

• 求分组中最小数据。
• arg:参数类型可以是字符、数字、 日期。

MAX(arg)函数

• 求分组中最大数据。
• arg:参数类型可以是字符、数字、 日期。

示例：查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。

select min(hire_date),max(hire_date)
from employees;

COUNT 函数

返回分组中的总行数。

COUNT 函数有三种格式：

• COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列的行。
• COUNT(expr)：返回在列中的由 expr 指定的非空值的数。
• COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数。

使用 DISTINCT 关键字

• COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数
• 显示 EMPLOYEES 表中不同部门数的值

示例一：显示员工表中部门编号是80中有佣金的雇员人数。

select count(commission_pct) 
from employees 
where department_id = 80;

示例二：显示员工表中的部门数。

select count(distinct department_id) 
from employees;

组函数和Null值

在组函数中使用 IFNULL 函数

select avg(ifnull(commission_pct,0))
from employees;

数据分组(GROUP BY)

创建数据组

创建数据组

  在没有进行数据分组之前，所有聚合函数是将结果集作为一个大的信息组进行处理。但是，有时，则需要将表的信息划分为较小的组，可以用 GROUP BY 子句实现。

GROUP BY 子句语法

原则

• 使用 WHERE 子句，可以在划分行成组以前过滤行。
• 如果有WHERE子句，那么GROUP BY 子句必须在WHERE的子句后面。
• 在 GROUP BY 子句中必须包含列。

使用 GROUP BY 子句

下面是包含一个 GROUP BY 子句 SELECT 语句的求值过程：

SELECT 子句指定要返回的列：

• 在 EMPLOYEES 表中的部门号
  − GROUP BY 子句中指定分组的所有薪水的平均值
  − FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
• WHERE 子句指定被返回的行。因为无 WHERE 子句默认情况下所有行被返回。
• GROUP BY 子句指定行怎样被分组。行用部门号分组，所以 AVG 函数被应用于薪水列，以计算每个部门的平均薪水。

示例：计算每个部门的员工总数。

select e.department_id,count(*)
from employees e
group by e.department_id;

在多列上使用分组

在组中分组

  可以列出多个 GROUP BY 列返回组和子组的摘要结果。可以用 GROUP BY子句中的列的顺序确定结果的默认排序顺序。下面是图片中的 SELECT 语句中包含一个 GROUP BY 子句时的求值过程：

• SELECT 子句指定被返回的列：
  − 部门号在 EMPLOYEES 表中
  − Job ID 在 EMPLOYEES 表中
  − 在 GROUP BY 子句中指定的组中所有薪水的合计
• FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
• GROUP BY 子句指定你怎样分组行：
  − 首先，用部门号分组行。
  − 第二，在部门号的分组中再用 job ID 分组行。

如此 SUM 函数被用于每个部门号分组中的所有 job ID 的 salary列。

示例：计算每个部门的不同工作岗位的员工总数。

select e.department_id,e.job_id,count(*)
from employees e
group by e.department_id,e.job_id;

约束分组结果(HAVING)

HAVING 子句

  HAVING 子句是对查询出结果集分组后的结果进行过滤。

约束分组结果

  用 WHERE 子句约束选择的行，用 HAVING 子句约束组。为了找到每个部门中的最高薪水，而且只显示最高薪水大于 $10,000 的那些部门，可以象下面这样做：

• 用部门号分组，在每个部门中找最大薪水。
• 返回那些有最高薪水大于 $10,000 的雇员的部门

select department_id, max(salary) 
from employees group by department_id 
having max(salary)>10000 ;

HAVING子句语法

示例：显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位，并且用合计月薪排序列表。

select e.job_id,sum(e.salary)
from employees e
where e.job_id not like '%REP'
group by job_id having sum(salary) > 1300
order by sum(salary)

练习

1.显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Max、Min、Sum 和 Avg。四舍五入结果为最近的整数。

select 
round(max(e.salary)) max,round(min(e.salary)) min,
round(sum(e.salary)) sum,round(avg(e.salary)) avg
from employees e;

2.写一个查询显示每一工作岗位的人数。

select e.job_id,count(*) 
from employees e
group by e.job_id;

3.确定经理人数，不需要列出他们，列标签是 Number of Managers。提示：用MANAGER_ID列决定经理号。

select count(distinct e.manager_id)
from employees e;

4.写一个查询显示最高和最低薪水之间的差。

select max(e.salary) - min(e.salary)
from employees e;

5.显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。

select e.manager_id,min(e.salary)
from employees e
where e.manager_id is not null
group by e.manager_id
having min(e.salary) > 6000
order by min(e.salary) desc;

6.写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。

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

子查询

子查询介绍

用子查询解决问题

  假如要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题，需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。可以用组合两个查询的方法解决这个问题。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。

子查询语法

子查询

  子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。使用子查询可以用简单的语句构建功能强大的语句。

可以将子查询放在许多的 SQL 子句中，包括：

• WHERE 子句
• HAVING 子句
• FROM 子句

使用子查询

使用子查询的原则

• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

子查询类型

示例：查询与Fox同一部门的同事，并显示他们的名字与部门ID。

select e.last_name,e.department_id
from employees e
where e.department_id =
(select e.department_id
from employees e
where e.last_name = 'Fox');

单行子查询

单行子查询

单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。

示例：查询 Fox的同事，但是不包含他自己。

select e.last_name,e.department_id
from employees e
where e.department_id =(select e1.department_id
from employees e1
where e1.last_name = 'Fox') 
and e.last_name <> 'Fox';

多行子查询

多行子查询

  子查询返回多行被称为多行子查询。对多行子查询要使用多行运算符而不是单行运算符。

使用ANY运算符

ANY 运算符

ANY 运算符比较一个值与一个子查询返回的任意一个值。

• (<) ANY 意思是小于最大值。
• (>) ANY 意思是大于最小值。
• (=) ANY 等同于 IN。

使用ALL运算符

ALL 运算符比较一个值与子查询返回的全部值。

• (<) ALL 意思是小于最小值。
• (>)ALL 意思是大于最大值，

NOT 运算符可以与 IN运算符一起使用。

子查询中的空值

  内查询返回的值含有空值，并因此整个查询无返回行，原因是用大于、小于或不等于比较Null值，都返回null。所以，只要空值可能是子查询结果集的一部分，就不能用 NOT IN 运算符。NOT IN 运算符相当于 <> ALL。

注意，空值作为一个子查询结果集的一部分，如果使用 IN 操作符的话，不是一个问题。

IN 操作符相当于 =ANY。

select emp.last_name 
from employees emp 
where emp.employee_id 
in (select mgr.manager_id 
from employees mgr);

示例:查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。

select em.last_name,em.salary,em.department_id 
from employees em  where em.salary 
in(select min(e.salary) from employees e group 
by e.department_id) group by em.department_id;

练习

1.写一个查询显示与 Zlotkey 在同一部门的雇员的 last name 和 hire date，结果中不包括 Zlotkey。

select e1.LAST_NAME,e1.HIRE_DATE
FROM employees e1
where e1.DEPARTMENT_ID = (select e.DEPARTMENT_ID FROM employees e where 
e.LAST_NAME ='Zlotkey')
and e1.LAST_NAME <> 'Zlotkey';

2.创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序。

select e1.EMPLOYEE_ID,e1.LAST_NAME
from employees e1
where e1.SALARY >
(select AVG(e.SALARY) from employees e)
order by e1.SALARY;

3.写一个查询显示所有工作在有任一雇员的名字中包含一个 u 的部门的雇员的雇员号和名字。

select e1.EMPLOYEE_ID,e1.LAST_NAME 
from employees e1
where e1.DEPARTMENT_ID in
(select e.DEPARTMENT_ID from employees e where e.LAST_NAME like '%u%')

4.显示所有部门地点号 (department location ID ) 是 1700 的雇员的 last name、department number 和 job ID。

select e.LAST_NAME,e.DEPARTMENT_ID,e.JOB_ID
from employees e
where e.DEPARTMENT_ID in
(select d.DEPARTMENT_ID
from departments d
where d.LOCATION_ID = 1700)

5.显示每个向 King 报告的雇员的名字和薪水。

select e1.LAST_NAME,e1.SALARY
from employees e1
where e1.MANAGER_ID in(select e.EMPLOYEE_ID from employees e where 
e.LAST_NAME ='King')

6.显示在 Executive 部门的每个雇员的 department number、last name 和 job ID。

select e.DEPARTMENT_ID,e.LAST_NAME,e.JOB_ID
from employees e
where e.DEPARTMENT_ID =
(select d.DEPARTMENT_ID from departments d 
where d.DEPARTMENT_NAME = 'Executive');