MySQL学习笔记之子查询，在关键字select、from、where、exists后的子查询_select where exists

一、子查询的含义

• 含义：出现在其他语句中的select语句，称为子查询或内查询；而外部的查询语句，称为主查询或外查询

• 分类：

按照子查询出现的位置：
select后面：仅仅支持标量子查询
from 后面：仅支持表子查询
where或having后面：支持标量子查询、列子查询、行子查询
exists后面：表子查询

按照结果集的行列数不同：
标量子查询：结果集只有一行一列 （又称为单行子查询）
列子查询： 结果集只有一列多行
行子查询： 结果集只有一行多列
表子查询： 结果集一般为多行多列

二、在where或having后面的子查询

1.标量子查询  2.列子查询   3.行子查询
1

1.特点：

① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 在标量子查询中，一般搭配单行操作符使用：< > <= >= <> =
在列子查询中，一般搭配着多行操作符使用：IN, ANY/SOME, ALL
④ 子查询的运行要优先于主查询，因为主查询要使用子查询的查询结果

案例1.谁的工资比Abel高？

SELECT *
FROM employees
WHERE salary > (
	SELECT salary FROM employees
	WHERE last_name = 'Abel'
)
1
2
3
4
5
6

案例2.返回job_id与141号员工相同，salary比143号员工多的员工的姓名、job_id、工资

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
	SELECT job_id FROM employees WHERE employee_id = 141
) AND salary > (
	SELECT salary FROM employees WHERE employee_id = 143
);
1
2
3
4
5
6
7

案例3.返回公司中工资最少的员工的last_name job_id salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary) FROM employees
);
1
2
3
4
5

2.HAVING + 标量子查询

案例4.查询最低工资大于 50号部门最低工资 的部门id及其最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary) FROM employees WHERE department_id = 50
);
1
2
3
4
5
6

3.列子查询（多行子查询）

案例5：返回location_id是1400或1700的部门的所有员工姓名

SELECT last_name FROM employees e 
INNER JOIN departments d 
ON e.`department_id` = d.`department_id`
WHERE d.`location_id` IN (1400,1700);
1
2
3
4

或

SELECT last_name FROM employees
WHERE department_id IN (
	SELECT department_id FROM departments
	WHERE location_id IN (1400,1700)
);
1
2
3
4
5

案例6 返回其他工种中比job_id为’IT_PROG’工种任一工资低的员工的员工号、姓名、job_id及salary

SELECT employee_id, last_name, job_id, salary
FROM employees 
WHERE salary < ANY(	
	SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
1
2
3
4
5

4.行子查询（结果集为一行多列或者多行多列）

案例7 查询员工编号最小、工资最高的员工信息

SELECT * FROM employees
WHERE salary = (
	SELECT MAX(salary) FROM employees
)AND employee_id = (
	SELECT MIN(employee_id) FROM employees
);

SELECT * FROM employees
--注意：此时的运算符应该是一样的如，employee_id = ? salary = ?
WHERE (employee_id,salary) =
	SELECT MIN(employee_id), MAX(salary) FROM employees #行子查询，输出就应得是行
);
1
2
3
4
5
6
7
8
9
10
11
12

三、放在SELECT后的子查询（仅仅支持标量子查询）

案例8 查询每个部门的员工个数(♥)

SELECT d.*,(
	SELECT COUNT(*) FROM employees e
	WHERE d.`department_id` = e.`department_id`
) AS 员工个数
FROM departments d;
1
2
3
4
5

案例9 查询员工号为102的部门名

SELECT department_name
FROM departments
WHERE department_id = (
	SELECT department_id FROM employees WHERE employee_id = 102
);
1
2
3
4
5

使用where后的标量子查询就可以实现（如上），但是如果必须放在SELECT之后，那么可以如下：

SELECT (
	SELECT department_name FROM departments d
	INNER JOIN employees e 
	ON d.department_id = e.department_id
	WHERE e.employee_id = 102
) AS 部门名称;
1
2
3
4
5
6

四、子查询放在From后面

• 本质就是将结果集充当成一张表，然后必须为这张表起名字，之后进行查询即可

案例10 查询每个部门的平均工资的工资等级

SELECT AVG(salary)avg_sal,department_id
FROM employees
GROUP BY department_id;

SELECT avg_tab.avg_sal, department_id, jg.grade_level
FROM (
	SELECT AVG(salary) avg_sal,department_id
	FROM employees e
	GROUP BY department_id
) AS avg_tab   ##一定要给新表格起别名！！！！
INNER JOIN job_grades jg
ON avg_tab.avg_sal BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;
1
2
3
4
5
6
7
8
9
10
11
12

五、exists后边的子查询

语法：
exists (完整的查询语句)
结果：
0或1
注意：
一般用exists的查询均可用其他的查询方式代替，所以EXISTS用的较少，如下例子

案例11 查询有员工的部门名

IN 方法

SELECT department_name FROM departments d
WHERE  d.`department_id` IN (
	SELECT department_id FROM employees
);


1
2
3
4
5
6

exists方法

SELECT department_name FROM departments d
WHERE EXISTS (
	SELECT *
	FROM employees e
	WHERE e.`department_id` = d.`department_id`
);
1
2
3
4
5
6

案例12 查询没有女朋友的男生信息

EXISTS方法

SELECT * FROM boys b
WHERE NOT EXISTS (
	SELECT *   FROM beauty g
	WHERE g.boyfriend_id = b.`id` 
);
1
2
3
4
5

NOT IN方法

SELECT bo.* FROM boys bo
WHERE bo.`id` NOT IN (
	SELECT boyfriend_id FROM beauty
);
1
2
3
4