MySQL基础——子查询、联合查询

进阶7：子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
内部嵌套其他select语句的查询，称为主查询或外查询
分类：
按子查询出现的位置：
	select 后面：
		标量子查询
	from 后面：
		表子查询
	where 或 having 后面：
		标量子查询
		列子查询
		行子查询
	exists 后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集只有一行多列）
	表子查询（结果集一般为多行多列）

一、where或having后面

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

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

列子查询，一般搭配着多行操作符使用
IN AND/SOME ALL 
④子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果

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 和工资

①查询141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id = 141;

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

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

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

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

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;

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

非法使用标量子查询

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

列子查询（多行子查询）一列多行

返回多行
使用多行比较操作符
in/not in	等于列表中的任意一个
any/some	和子查询返回的某一个值比较
all		和子查询返回的所有值比较

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

①查询location_id 是1400或1700 的部门编号

SELECT DISTINCT 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

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT MAX(salary)
	FROM employees
	WHERE 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'
);

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

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

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后面(都是一行一列)

仅仅支持标量子查询
显示结果都是一行一列

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

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 lowest_sal and highest_sal

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

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

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

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

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

SELECT * FROM boys;
SELECT * FROM beauty;

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

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

例题

SELECT last_name,salary
FROM employees 
WHERE department_id=(
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
);

SELECT employee_id,last_name,salary
FROM employees 
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

SELECT employee_id,last_name,salary,e.department_id
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN employees e
ON ag_dep.department_id=e.`department_id`
WHERE salary>(ag_dep.ag);

SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;

SELECT employee_id
FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);

SELECT employee_id 
FROM employees
WHERE last_name = 'K_ing';

SELECT last_name,salary
FROM employees
WHERE manager_id IN (
	SELECT employee_id 
	FROM employees
	WHERE last_name = 'K_ing'
);

进阶8：联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
	查询语句1
	union
	查询语句2
	union
	......
应用场景：
要查询的结果来自与多个表，且多个表没有直接的连接关系，单查询的信息一致时
特点：
1、要求多条查询语句的查询列数一致的
2、要求多条查询语句的查询的每一列的类型和顺序是一致的
3、union 关键字默认去重，如果使用union all 包含全部项

引入：查询部门编号>90或邮箱中包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
SELECT * FROM employees WHERE email LIKE '%a%'
UNION 
SELECT * FROM employees WHERE department_id >90;