MySQL之子查询

一、含义

出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询

二、分类

按子查询出现的位置分类：
1.select后面：仅仅支持标量子查询
2.from后面：支持表子查询
3.where和having后面（使用最多）：支持标量子查询、列子查询、行子查询
4.exists后面(相关子查询)：支持表子查询

按结果集的行列数不同：
1.标量子查询（结果集只有一行一列）
2.列子查询（结果集只有一列多行）
3.行子查询（结果集有一行多列）
4.表子查询（结果集一般为多行多列）

三、案例讲解

一、where或having后面

1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（一行多列）
特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配单行操作符使用（> < >= <= = <>），列子查询一般搭配多行操作符使用（in any/some all）
4.子查询的执行优先于主查询执行，主查询条件用到了子查询的结果

1.标量子查询

#案例1：谁的工资比Abel高？
#第一步：查询Abel的工资
SELECT
	salary
FROM
	employees
WHERE
	name = ‘Abel’;

#第二步：查询员工信息筛选满足salary大于第一步结果的
SELECT
	*
FROM
	employees
WHERE
	salary>(
SELECT
	salary
FROM
	employees
WHERE
	name = ‘Abel’
);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
#第一步：查询141号员工的job_id
SELECT
	job_id
FROM
	employees
WHERE
	employees=141;

#第二步：查询143号员工的salary
SELECT
	salary
FROM
	employees
WHERE
	employee_id = 143;

#第三步：查询员工姓名，job_id和工资，要求job_id为第一步结果并且salary大于第二步结果
SELECT
	name,job_id,salary
FROM
	employees
WHERE
	job_id = (
SELECT
	job_id
FROM
	employees
WHERE
	employees=141
) AND salary>(
SELECT
	salary
FROM
	employees
WHERE
	employee_id = 143
);

#案例3：返回公司工资最少的员工的name,job_id,salary
#第一步：查询公司的最低工资
SELECT
	MIN(salary)
FROM
	employees;

#第二步：查询符合题意且工资等于第一步结果的
SELECT
	name,job_id,salary
FROM
	employees
WHERE
	salary=(
SELECT
	MIN(salary)
FROM
	employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#第一步：查询50号部门的最低工资
SELECT
	MIN(salary)
FROM
	employees
WHERE
	department_id = 50;

#第二步：查询每个部门的最低工资
SELECT
	MIN(salary),department_id
FROM
	employees
GROUP BY
	department_id;

#第三步：在第二步的结果中筛选满足最低工资大于第一步结果的部门id及最低工资
SELECT
	MIN(salary),department_id
FROM
	employees
GROUP BY
	department_id
HAVING
	MIN(salary)>(
SELECT
	MIN(salary)
FROM
	employees
WHERE
	department_id = 50
);

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

#案例：返回location_id是1400或1700的部门中的所有员工名字
#第一步：查询location_id是1400或1700的部门编号
SELECT
	DISTINCT department_id
FROM
	departments
WHERE
	location_id IN(1400,1700);

#第二步：查询员工姓名，要求部门号是第一步结果中的某一个
SELECT
	name
FROM
	employees
WHERE
	departmnet_id IN(
SELECT
	DISTINCT department_id
FROM
	departments
WHERE
	location_id IN(1400,1700)
);

#也可以换成any
SELECT
	name
FROM
	employees
WHERE
	departmnet_id = ANY(
SELECT
	DISTINCT department_id
FROM
	departments
WHERE
	location_id IN(1400,1700)
);
#any或some相当于是min函数，all相当于是max函数

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

#案例：查询员工编号最小且工资最高的员工
#1.之前的写法：
#第一步：查询编号最小员工
SELECT
	MIN(employee_id)
FROM
	employees;

#第二步：查询最高工资
SELECT
	MAX(salary)
FROM
	employees;

#第三步：查询员工信息
SELECT
	*
FROM
	employees
WHERE
	employee_id = (
SELECT
	MIN(employee_id)
FROM
	employees
) AND salary = (
SELECT
	MAX(salary)
FROM
	employees
);
#2.使用行子查询写法
SELECT
	*
FROM
	employees
WHERE
	(employee_id,salary) = (
	SELECT
			MIN(employee_id),MAX(salary)
	FROM
		employees
);

二、放在select后面

标量子查询

#案例1：查询每个部门的员工个数
SELECT
	d.*,(
SELECT
	COUNT(*)
FROM
	employees e
WHERE
	e.department_id = d.department_id
) 个数
FROM
	departments d;

#案例2：查询员工号为102的部门名
SELECT (
SELECT
	department_name
FROM
	departments d
INNER JOIN
	employees e
ON
	d.department_id = e.department_id
WHERE
	e.employee_id = 102
) 部门名;         #加不加外面这层都可以

三、放在from后面

注意：将子查询结果充当一张表，要求必须起别名
表子查询

#案例：查询每个部门的平均工资的工资等级
#第一步：查询每个部门的平均工资
SELECT
	AVG(salary),department_id
FROM
	employees
GROUP BY
	department_id;

#第二步：连接第一步的结果集和job_grades表，筛选条件平均工资 between lower_sal and highest_sal
SELECT
	ag_dep.*,g.grade_level
FROM
	(
SELECT
	AVG(salary),department_id
FROM
	employees
GROUP BY
	department_id
) ag_dep
INNER JOIN
	job_graades g
ON
	ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、放在exists后面（相关子查询）

语法：

exists(完整的结果) #结果为1或0，存在为1不存在为0

表子查询

#案例1：查询有员工的部门名
SELECT
	department_name
FROM
	departments d
WHERE EXISTS(
SELECT
	*
FROM
	employees e
WHERE
	d.department_id = e.department_id
);

#案例2：查询没有女朋友的男神信息
SELECT
	bo.*
FROM
	boys bo
WHERE
	NOT EXISTS(
SELECT
	boyfriend_id
FROM
	beauty b
WHERE
	bo.id = b.boyfriend_id
);

本篇博客就介绍到这里了，如果发现问题或有任何疑问请及时提出