数据库MySQL-子查询

数据库MySQL-子查询

#进阶7：子查询

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

分类：
按子查询出现的位置：
	select后边
		仅仅支持标量子查询
	from后面
		表子查询
	where或having后面（*）
		标量子查询
		列子查询
		行子查询
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集中只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）
*/

一、where或having后面
*1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、标量子查询，一般搭配着单行操作符使用：> < >= <= = <>. 
   列子查询，一般搭配着多行操作符使用：in、any/some、all.
4、子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果。
*/
#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和工资。
#a、查141号的job_id
SELECT job_id
FROM employees
WHERE `employee_id` = 141;

#b、查143号员工的salary
SELECT salary
FROM employees
WHERE `employee_id` = 143;

#c、根据a、b的结果查询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。
#a、查出公司最少的工资
SELECT MIN(salary)
FROM employees;

#b、查询last_name,job_id和salary,要求salary = a。
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary` = (
	SELECT MIN(salary)
	FROM employees
);
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#a、查询50号部门最低工资
SELECT MIN(salary)
FROM `employees`
WHERE `department_id` = 50;

#查询最低工资大于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、返回location_id是1400或1700的部门中的所有员工姓名
#a、查询location_id是1400或1700的部门编号
SELECT department_id
FROM `departments`
WHERE location_id IN(1400,1700);

#b、根据 a 的结果查出所有员工姓名
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
	SELECT department_id
	FROM `departments`
	WHERE location_id IN(1400,1700)
);

#案例2、返回其他工种中比job_id为“IT_PROG”任一工资低的员工的工号、姓名、job_id 以及salary。
#a、查出job_id为“IT_PROG”的工资
SELECT salary
FROM `employees`
WHERE `job_id` = 'IT_PROG';

#b、返回其他job中比 a 低的员工的工号、姓名、job_id 以及salary
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < ANY(
	SELECT 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 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
);

#a、查询最小的员工编号
SELECT MIN(employee_id)
FROM employees

#b、查询最高工资
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后面
/*
仅仅支持标量子查询
*/
#案例1：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.`department_id` = d.`department_id`
) 个数
FROM `departments` d;

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

SELECT `department_name` 
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` = 102;
#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
#查询每个部门的平均工资
SELECT AVG(salary),`department_id`
FROM `employees`
GROUP BY `department_id`

SELECT ag_dep.*,g.job_grade
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、查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
	SELECT *
	FROM `employees` e
	WHERE e.`department_id` = d.`department_id`
)

SELECT DISTINCT `department_name`
FROM `departments` d
LEFT JOIN `employees` e 
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` IS NOT NULL;

#案例2、查询没有女朋友的男神信息
#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.`id`
);