OldWang带你了解MySQL（八）

🔥聚合函数

聚合函数介绍

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

聚合函数类型

聚合函数说明：

聚合函数使用方式

使用聚合函数的原则

⭐ DISTINCT 使得函数只考虑不重复的值；

⭐ 所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。

🔥AVG 和 SUM 函数

AVG(arg)函数

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

SUM(arg)函数

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

🔥MIN 和 MAX 函数

MIN(arg)函数

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

MAX(arg)函数
● 求分组中最大数据。
● arg:参数类型可以是字符、数字、 日期。

🔥COUNT 函数

返回分组中的总行数。

COUNT 函数有三种格式：
● COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列的行。

● COUNT(expr)：返回在列中的由 expr 指定的非空值的数。

● COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数。

使用 DISTINCT 关键字

● COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数显示 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 子句

下面是包含一个 GROUP BY 子句 SELECT 语句的求值过程：
⭐ SELECT 子句指定要返回的列：
⭐ 在 EMPLOYEES 表中的部门号

− GROUP BY 子句中指定分组的所有薪水的平均值
− FROM 子句指定数据库必须访问的表：EMPLOYEES 表。

⭐ WHERE 子句指定被返回的行。因为无WHERE 子句默认情况下所有行被返回。
⭐ GROUP BY 子句指定行怎样被分组。行用部门号分组，所以 AVG 函数被应用于薪水列，以计算每个部门的平均薪水。

🔥在多列上使用分组

在组中分组

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

⭐ SELECT 子句指定被返回的列：

− 部门号在 EMPLOYEES 表中
− Job ID 在 EMPLOYEES 表中
− 在 GROUP BY 子句中指定的组中所有薪水的合计

⭐ FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
⭐ GROUP BY 子句指定你怎样分组行：

− 首先，用部门号分组行。
− 第二，在部门号的分组中再用 job ID 分组行。

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

🔥约束分组结果(HAVING)

HAVING 子句

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

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

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

SELECT department_id, MAX(salary) 
FROM employees GROUP BY department_id HAVING
MAX(salary)>10000 ;

HAVING子句语法

🔥子查询

子查询介绍

用子查询解决问题

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

子查询语法

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

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

⭐ WHERE 子句
⭐ HAVING 子句
⭐ FROM 子句

使用子查询

使用子查询的原则

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

子查询类型

🔥单行子查询

单行子查询

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

🔥多行子查询

多行子查询

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

使用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);