MySQL随记 - 子查询

# 进阶7：子查询
/*
含义：
出现在其他语句中的select语句，成为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
	select后面[标量子查询]
	from后面[表子查询]
	☆where或having后面[标量子查询/列子查询/行子查询]
	exists后面(相关子查询)[表子查询]
按结果集的行列数不同
	标量子查询(结果集只有一行一列)
	列子查询(结果集只有一列多行)
	行子查询(结果集有一行多列)
	表子查询(结果集一般为多行多列)
*/


# 一、where或having后面
# 特点：
# ①子查询放在小括号内
# ②子查询一般放在条件右侧
# ③标量子查询，一般搭配单行操作符使用 > < = <>
# ④列子查询，一般搭配 IN/ANY/SOME/ALL

# 案例1：查询谁工资比Abel高
SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel')

# 案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资
# ①先查141员工job_id
SELECT  job_id
FROM employees
WHERE employee_id = 141;
# ②查143号员工salary
SELECT salary
FROM employees
WHERE employee_id = 143;

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

/*
多行子查询：返回多行，使用多行比较操作符
(1)IN/NOT IN:等于列表中的任意一个
(2)ANY|SOME:和子查询返回的某一个值比较
(3)ALL:和子查询返回的所有值比较
*/
# 返回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 department_id
	FROM departments
	WHERE location_id IN (1400, 1700));
	

# 返回其他工种中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary
# ①查询job_id为'IT_PROG'部门任一工资
SELECT salary
FROM employees
WHERE job_id='IT_PROG';
# ②查询员工号、姓名、job_id以及salary，salary<(①)任一个
SELECT last_name, employee_id, job_id
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG') AND job_id<>'IT_PROG';
	
# 行子查询(结果集一行或多行多列)
# 案例：查询员工编号最小并且工资最高的员工信息
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 * 
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;