MySQL DQL语言——子查询

子查询

beauty表
boys表

employees 表
departments表
job_grades表
locationas表

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

分类：按查询出现的位置

select 后面
	仅仅支持标量子查询
from 后面
	支持表子查询	
where 或having后面 ★
	标量子查询（单行）√
	列子查询（多行）√
	
	行子查询
exists后面（相关子查询）
	表子查询

按结果集的行列数不同：

标量子查询（结果只有一行一列）
列子查询（结果集只有一行多列）
行子查询（结果集有多行多列）
表子查询（结果集一般为多行多列）	

一、where 或having后面

1.标量子查询（单行子查询）
2.列子查询（多行子查询）		
3.行子查询（多行多列）

特点：
①子查询放在小括号内
②子查询一般放在条件的右括
③标量子查询，一般搭配这单行操作符使用
< >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询
案例1：谁的工资比Abel高？
查询Abel的工资

SELECT	salary
FROM employees 
WHERE last_name = 'Abel'

②查询员工的信息，满足salary>①结果

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

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

①job_id与141号员工相同

SELECT job_id
FROM employees
WHERE employee_id = 141;

②salary比143号员工多的员工

SELECT	salary 
FROM employees 
WHERE employee_id >143;

③查询员工姓名，job_id和工资，要求job_id=①并且salary=②

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 MIN(salary)
FROM employees;

②查询last_name,job_id和salary，要求salary=①

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

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

SELECT MIN(salary),department_id
FROM employees 
GROUP BY department_id HAVING MIN(salary)>(

	SELECT MIN(salary)
	FROM employees e WHERE e.`department_id`=50
);

2.列子查询（多行子查询）
案例1：返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门编号

SELECT department_id 
FROM departments 
WHERE location_id 
IN(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
①查询部门中job_id为‘IT_PROG’部门任意工资

SELECT DISTINCT salary 
FROM  employees WHERE 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';

或

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

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

①查询job_id为‘IT_PROG’部门所有工资

SELECT salary FROM employees WHERE 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';

或

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

3.行子查询（结果集一行或者多行多列）
案例：查询员工编号最小并且工资最高的员工信息

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

①查询编号最小的

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

二、select 后面
案例：查询每个部门的员工个数

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;

②根据①的结果查询工资等级

SELECT dep.*,j.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id	 
	FROM employees 
	GROUP BY department_id
) dep
INNER JOIN job_grades  j
ON dep.ag BETWEEN lowest_sal AND highest_sal;

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

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

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=30000 );

案例：查询有员工的部门名
in

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

EXISTS

SELECT department_name 
FROM departments  d
WHERE EXISTS( 
	SELECT * FROM 
	employees e 
	WHERE d.`department_id`=e.`department_id`
 );
 

案例：查询没有女朋友的男神信息
in

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

EXISTS

SELECT bo.* 
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty  b 
	WHERE bo.`id`=b.`boyfriend_id`
);