聚合函数聚合函数介绍聚合函数聚合函数也称之为多行函数组函数或分组函数。聚合函数不象单行函数聚合函数对行的分组进行操作对每组给出一个结果。如果在查询中没有指定分组那么聚合函数则将查询到的结果集视为一组。聚合函数类型聚合函数说明【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);