mysql 查询 虚拟 多行 mysql多行子查询

子查询

概念： 出现在其他语句内部的 select 语句， 称为子查询或者内查询
内部嵌套其他 select 语句的查询，称为 外查询或者 主查询

例子：

SELECT first_name FROM employees 
WHERE department_id
IN(
	SELECT department_id from departments 
	WHERE location_id = 1700
)

分类
按子查询 出现的位置：
select后面
支持 标量子查询
from 后面
支持表子查询
where 或者 having 标量子查询 （单行）
列子查询 (多行)
行子查询
exists 表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行字查询（结果集可以有多行多列）
表子查询（结果集一般为多行多列）

. WHERE 或者 HAVING后面

1. 标量子查询 （单行子查询）
   案例1： 谁的工资比 abel 高

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

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

SELECT name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employees_id = 141
) AND 
salary > (
	SELECT salary
	FROM employees
	WHERE employees_id = 143
);

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

SELECT last_name,job_id
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	和子查询返回的某一个值 比较
ALL	和子查询返回的所有值比较

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

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

案例2：返回其他部门中比 job_id为IT_PROG 部门任一工资低的员工的员工号，姓名，job_id 以及 salary

SELECT last_name , employee_id , 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 last_name , employee_id , 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 job_id = (
	SELECT MIN(job_id)
	FROM employees
) AND salary = (
	SELECT MAX(salary)
	FROM employees
)

特点
① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 如果使用标量子查询，一般搭配着单行操作符使用 > < >= <= <>
④ 列子查询，一般搭配这多行操作符使用 in any some all

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 后面表子查询

案例1： 查询每个部门的平均工资的工资等级
将查询结果充当一张表。必须起别名

SELECT  ag_dep.*,g.grade_level
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

exists 表子查询（相关子查询）

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

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary = 300000)