MySQL基础（三）聚合函数、子查询

目录

聚合函数

AVG/SUM/MAX/MIN

 COUNT函数

GROUP BY

HAVING

having和where的区别

 SELECT的执行过程

子查询

 单行子查询vs多行子查询

单行子查询 

 多行子查询

 关联子查询

EXISTS 与 NOT EXISTS关键字

---

聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值

AVG/SUM/MAX/MIN

分别是平均值、求和、最大最小值

SELECT AVG(salary),SUM(salary),MAX(salary),MIN(salary)
FROM employees

 COUNT函数

COUNT(*)返回表中记录总数，适用于任意数据类型。

SELECT COUNT(salary),COUNT(*),COUNT(1)
FROM employees

这三种方法都能得到记录的个数，但是用 COUNT(字段名) 时，如果字段中有NULL存在，则在函数中会自己省略

其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)

GROUP BY

分组，根据部门计算薪水平均值：

SELECT avg(salary),department_id
from employees
group by department_id

多列分组

department_id和job_id都相同的分为一组

SELECT avg(salary),department_id,job_id
from employees
group by department_id,job_id

注意！！

SELECT中出现的非组函数的字段必须声明在GROUP BY中

例如下面的写法就是错误的，分组之后，每一个department_id会有一个AVG(salary)值，但是无法确定对应哪个job_id值

SELECT avg(salary),department_id,job_id
from employees
group by department_id

with rollup ，加上这个之后会把所有数据当成一组进行计算

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id WITH ROLLUP

HAVING

过滤分组：HAVING子句
1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用，且声明在其后面

  例如，查询各个部门中最高工资比10000高的部门信息，很自然会想到

SELECT max(salary),department_id
from employees
where max(salary)>10000
group by department_id 

但是由于在执行 where的时候尚未分组，所以报错

应该：

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

having和where的区别

这里通过一个例子说明having和where的区别，查询10，20，30，40 四个部门中最高工资比10000高的部门信息，有两中方法：

SELECT MAX(salary),department_id
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id 
HAVING MAX(salary)>10000

SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id 
HAVING MAX(salary)>10000 AND department_id IN (10,20,30,40)

结论是第一种效率更高

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

 

 SELECT的执行过程

结构：

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...


#方式2：
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...


#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

关键字的顺序是不能颠倒的

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

执行顺序 

FROM -> ON ->(LEFT/RIGHT JOIN -> WHERE -> GROUP BY
 -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

子查询

通过一个例子引入子查询的使用场景：查询工资比Abel高的人的名字和工资

方式一，利用自连接：

SELECT e1.last_name,e1.salary
FROM employees e1,employees e2
WHERE e1.salary >e2.salary AND e2.last_name = 'Abel'  

方式二，利用子查询

SELECT last_name salary
FROM employees
WHERE salary >(
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
)

• 子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
• SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。
注意事项

• 子查询要包含在括号内
• 将子查询放在比较条件的右侧
• 单行操作符对应单行子查询，多行操作符对应多行子查

 单行子查询vs多行子查询

们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询 、多行子查询 

单行子查询 

单行子查询就是子查询结果是一行数据

单行子查询操作符

上面说的子查询的例子就是一个单行子查询，这里再补充一个例子：

查询与141号员工的manager_id 和 department_id 相同的其他员工，这里可以用（）将两个数值看出一个整体 

select employee_id,manager_id,department_id
from employees
where (manager_id,department_id) = (
		select manager_id,department_id
		from employees
		where employee_id=141
)
and employee_id <> 141

HAVING中的子查询

查询最低工资大于50号部门最低工资的部门id和其最低工资

select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
		select min(salary)
		from employees
		group by department_id
		having department_id = 50
)

 多行子查询

• 也称为集合比较子查询
• 内查询返回多行
• 使用多行比较操作符

多行比较操作符

 举例

1、查询每个部门中最低工资的那个人

select last_name,salary,department_id
from employees
where salary in (
		select min(salary)
		from employees
		group by department_id
)

2、返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY (
		SELECT salary
		FROM employees
		WHERE job_id ='IT_PROG'
		
)
AND job_id <> 'IT_PROG'

3、返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL (
		SELECT salary
		FROM employees
		WHERE job_id ='IT_PROG'
		
)
AND job_id <> 'IT_PROG'

4、查询平均工资最低的部门号（重要）

首先求平均工资：

			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id

求最低工资，很自然想到：

			SELECT MIN(AVG(salary)) avg_sal
			FROM employees
			GROUP BY department_id

但是报错了，不能嵌套函数，那么可以将平均工资表当成一个新的表选择：

		SELECT MIN(avg_sal)
		FROM(
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
		)

又报错：Every derived table must have its own alias  派生表必须有别名

所以：

SELECT department_id,AVG(salary) avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary = (
		SELECT MIN(avg_sal)
		FROM(
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
		) avg_salary_tb
)

当然也可以不求最小平均工资

SELECT department_id,AVG(salary) avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary <= ALL(
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
)

 关联子查询

• 非关联子查询是，先计算子查询，然后主查询使用子查询的结果
• 关联子查询是，先计算主查询，将主查询的一些参数传到子查询中。
• 如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件 关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

举例：

1、查询员工工资中大于本部门平均工资的员工信息

SELECT last_name,salary
FROM employees e1
WHERE salary >(
			SELECT AVG(salary) avg_sal
			FROM employees e2
			GROUP BY department_id
			HAVING e2.department_id = e1.department_id
)

这个例子也有另外一种方法，不使用关联子查询，先将平均工资表算出来当成新表，再比较

SELECT e1.last_name,e1.salary
FROM 	employees e1,	(SELECT department_id,AVG(salary) avg_sal
			FROM employees e2
			GROUP BY department_id
			)e2
WHERE e1.department_id = e2.department_id AND e1.salary>e2.avg_sal

2、若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同 id的员工的employee_id,last_name和其job_id （难）

job_history 表长这样：

 也就是筛选那些换职位两次及两次以上的人，具体来说就是101 、176和200

SELECT employee_id,last_name,job_id
FROM employees e1
WHERE 2<=(
	SELECT COUNT(j.employee_id)
	FROM job_history j
	WHERE j.employee_id = e1.employee_id		
)

这里查询相当于是一行一行执行的，查询到某一行时，将e1.employee_id导入到子查询，如果满足条件再输出

EXISTS 与 NOT EXISTS关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行： 条件返回 FALSE 继续在子查询中查找
• 如果在子查询中存在满足条件的行： 不在子查询中继续查找 条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

 案例

1、查询公司管理者的employee_id，last_name，job_id，department_id信息

 方式1：

SELECT e1.last_name
FROM employees e1
WHERE e1.employee_id IN (
			SELECT e2.manager_id
			FROM employees e2
) 

方式2：

SELECT e1.last_name
FROM employees e1
WHERE EXISTS(
	SELECT *
	FROM employees e2
	WHERE e1.employee_id = e2.manager_id
)

这个相当与两层循环，先再e1里面循环，获取每个employee_id，然后再e2循环，查看是否满足 e1.employee_id = e2.manager_id，如果有就break，没有就继续循环。复杂度是O2

2、查询departments表中，不存在于employees表中的部门的department_id和department_name

方式1：

SELECT d.department_id,d.department_name
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL

这里采用了右外连接 ，具体参见MySQL基础（二）排序与分页、多表查询、单行函数_独憩的博客-CSDN博客

方式2 

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
		SELECT *
		FROM employees e
		WHERE d.department_id = e.department_id
)

提供一些练习题