【page】mysql笔记（九） 子查询

子查询

定义：

当一个查询是另一个查询的条件时，称为子查询或内查询
外部的查询语句，称为主查询或外查询

子查询详解：

指在一条select语句中，嵌入了另外一条select语句，那么被嵌入的select语句称之为子查询语句

子查询分类：

1.按子查询出现的位置：
　　　select后面：仅仅支持标量子查询
　　　from后面：支持表子查询
　　　where或having后面：
　　　　　　　　　　　标量子查询
　　　　　　　　　　　列子查询
　　　　　　　　　　　　 行子查询
　　　exists后面：表子查询
2.按结果集的行列数不同
　　　标量子查询（结果集只有一行一列）
　　　列子查询（结果集只有一列多行）
　　　行子查询（结果集只有一行多列）
　　　表子查询（结果集一般为多行多列）

一、where或having后面

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

1.标量子查询（单行子查询）

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

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

案例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
);

案例3：返回公司工资最少的员工的last_name,job_id和salary

SELECT
	last_name,job_id,salary
FROM
	employees
WHERE
	salary=(
		SELECT MIN(salary)
		FROM employees
);

案例4：查询最低工资大于50号部门最低工资的部门ID和其最低工资

SELECT
	`department_id`,MIN(salary)
FROM
	employees
GROUP BY
	`department_id`
HAVING
	MIN(salary)>(
		SELECT MIN(salary)
		FROM employees
		WHERE `department_id`=50
);

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

①发布多行
②使用多行比较操作符
操作符 　　　　　　　　　 含义
※IN/NOT IN 　　　 　　　等于/不等于列表中的任意一个
ANY/SOME <=>MIN() 　　 和子查询返回的某一个值比较
ALL 　　<=>MAX() 　　　和子查询返回的所有值比较

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

SELECT
	last_name
FROM
	employees
WHERE
	department_id IN(
		SELECT DISTINCT department_id
		FROM departments
		WHERE location_id IN(1400,1700)
);

案例2：返回其他工种中比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';

案例3：返回其他工种中比job_id为’IT_PROG’工种所有工资低的员工的员工号、姓名、job_id以及salary

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

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

案例1：查询员工编号最小并且工资最高的员工信息

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

二、放在select后面（仅仅支持标量子查询）

案例1：查询每个部门的员工个数

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

案例2：查询员工号=102的部门名

SELECT
	department_name AS 部门名
FROM
	employees AS e
JOIN
	departments AS d
ON
	e.`department_id`=d.`department_id`
WHERE
	e.`employee_id`=102;

三、放在from后面

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

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

#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY `department_id`;
#②连接①的结果集和job_grades表
SELECT
	av_salary.*,j.grade_level
FROM (
	SELECT AVG(salary) AS AVG,department_id
	FROM employees
	GROUP BY `department_id`
) AS av_salary
JOIN
	job_grades AS j
ON	
	av_salary.AVG BETWEEN j.lowest_sal AND j.highest_sal;

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

语法：
　　exists(完整查询语句)
结果：
　　1或0

SELECT EXISTS(SELECT employee_id FROM employees);