三、MySQL子查询学习笔记（标量子查询、列子查询、行子查询、表子查询 详解）

三、MySQL子查询学习笔记

7：子查询

含义：
一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询；在外面的查询语句，称为主查询或外查询
分类：
一、按子查询出现的位置：

1）select后面：
                       仅支持标量子查询

2）from后面：
                       支持表子查询

3）where或having后面：
                       标量子查询（1行1列）
                       列子查询（n行1列）
                       行子查询（1行n列）

4）exists后面（相关子查询）:
                       表子查询

二、按结果集的行列数不同：

1. 标量子查询（单行子查询）：结果集为1行1列
2. 列子查询（多行子查询）：结果集为n行1列
3. 行子查询：结果集为1行n列
4. 表子查询：结果集为n行n列

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：

① 单行子查询：结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空

② 多行子查询：结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替

一、where 或 having后面可以加一下三种子查询：

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

特点

1. 子查询放在小括号内
2. 子查询一遍放在条件的右侧
   3. 标量子查询，一般会搭配着单行操作符来使用：> < >= <= = <>；
   列子查询，一般搭配着多行操作符来使用：in、any/some、all
   
   注意：
   all 是“任意”，是与所有值比较
   例如：小于 any() 等价于 小于 min()
   any 是“任一个”， 是与其中的某个值比较满足即可。
   例如：小于 any() 等价于 小于 max()
3. 子查询的执行优先于主查询执行，因为 主查询的条件用到了子查询的结果

1.标量子查询

# 1.标量子查询（一行一列）

# 【案例1】查询谁的工资比Abel高？
# 1）查询Abel的工资
SELECT salary 
FROM employees
WHERE last_name = 'Abel';

# 2) 查询员工的信息，满足 salary > 1)的结果
SELECT * 
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

# 【案例2】返回job_id（工种号） 与 141号员工相同，salary 比 143 号 员工多的员工姓名，job_id，和工资
# 1）查询 员工号 为141 的员工的job_id
SELECT job_id 
FROM employees
WHERE employee_id = 141;

# 2）查询 员工号为143的员工的工资
SELECT salary
FROM employees
WHERE employee_id = 143;

# 3)查询员工的姓名，job_id，和工资，要求job_id = 1）并且salary > 2）
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
# 1) 查询公司的最低工资
SELECT MIN(salary)
FROM employees;

# 2) 查询 last_name , job_id和salary，要求salary = 1)
SELECT last_name, job_id,salary
FROM employees
WHERE salary = (
						SELECT MIN(salary)
						FROM employees
)

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

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

# 2) 查询每个部门的最低工资
SELECT department_id , min(salary)
FROM employees
GROUP BY department_id;

# 3) 在2）的基础上筛选，满足min(salary)>1)  查询department_id > 50 的部门号 和 最低工资
SELECT department_id , MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
						SELECT MIN(salary) 
						FROM employees
						WHERE department_id = 50
)

2.列子查询

# 2.列子查询（n行1列）
# 【案例1】 返回location_id是1400或1700 的部门中的所有员工姓名

# 1）查询location_id 是1400或1700的部门编号（多行1列）
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);

# 2）查询员工姓名，要求部门号是1）列表中的某一个
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

# 1) 查询job_id为“IT_PROG” 部门的所有员工的工资 
SELECT DISTINCT salary
FROM employees
WHERE job_id = "IT_PROG";

# 2) 查询员工的员工号、姓名、job_id和salary，要求salary< 1）中的任一（比其中某一个小就行）一个结果
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';

# 2) 或者   小于其中任何一个就行 等价于 小于最大值
SELECT last_name ,employee_id , job_id , salary
FROM employees
WHERE salary < (
							SELECT max(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';

# 或者  小于所有（任意）的  等价于  大于最小值
SELECT last_name ,employee_id , job_id , salary
FROM employees
WHERE salary < (
							SELECT MIN(salary)
							FROM employees
							WHERE job_id = "IT_PROG"
) AND job_id <> 'IT_PROG';

3.行子查询（结果集为1行多列 或 多行多列）

# 3.行子查询（结果集为1行多列 或 多行多列）

# 【案例1】 查询员工编号最小并且工资最高的员工信息

# 先用标量子查询来实现
# 1) 查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;

# 2) 查询最高的工资
SELECT MAX(salary)
FROM employees;

# 3) 查询员工的信息
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后面（仅仅支持标量子查询）

# 【案例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 e.department_id = d.department_id
					WHERE e.employee_id = 102	
) 部门名;

三、from后面
注意：将子查询结果充当一张表时，必须要起别名，否则会报错

# 三、from后面
# 注意：将子查询结果充当一张表时，必须要起别名，否则会报错
# 【案例1】 查询每个部门的平均工资的工资等级

# 1) 查询每个部门的平均工资
SELECT AVG(salary) , department_id
FROM employees
GROUP BY department_id

SELECT * FROM job_grades;

# 2) 连接1）的结果集 和 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后面（相关子查询）:只关心查询的东西是否存在，存在返回1，不存在返回0
语法：EXISTS(完整的查询语句)
结果：1或0

# 四、exists后面（相关子查询）:只关心查询的东西是否存在，存在返回1，不存在返回0
# 语法：
#    EXISTS(完整的查询语句)
# 结果：
#    1或0
SELECT EXISTS (SELECT employee_id FROM employees);

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

# 【案例1】 查询有员工的部门名
# 方法1 , exists
SELECT department_name
FROM departments d
WHERE EXISTS(
							SELECT *
							FROM employees e , departments d
							WHERE d.department_id = e.department_id
);

# 方法2 , in
SELECT department_name
FROM departments d
WHERE d.department_id IN(
					SELECT department_id 
					FROM employees
);

# 【案例2】 查询没有女朋友的男神信息
USE girls;
# 方法一，not in 
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (
				SELECT boyfriend_id
				FROM beauty
);

# 方法二 not EXISTS
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
					SELECT boyfriend_id
					FROM beauty b
					WHERE bo.id = b.boyfriend_id
);

子查询练习部分：

# 子查询练习部分：

# 1、查询和Zlotkey相同部门的员工姓名和工资
use myemployees;
# 1）查询Zlotkey的部门id
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';

# 2) 查询部门号等于 1）的查询结果的 员工姓名和工资
SELECT last_name , salary,department_id
FROM employees
WHERE department_id = (
					SELECT department_id
					FROM employees
					WHERE last_name = 'Zlotkey'
);

# 2、查询工资比公司平均工资高的员工的 员工号、姓名和工资

# 1) 查询平均工资
SELECT AVG(salary)
FROM employees;

# 2) 查询工资高于 1）的查询结果的 员工的 员工号、姓名和工资
SELECT employee_id , last_name , salary
FROM employees
WHERE salary > (
						SELECT AVG(salary)
						FROM employees
);

# 3、查询各部门中工资比本部门平均工资高的员工 的 员工号、姓名和工资

# 1) 查询每个部门的平均工资
SELECT department_id , AVG(salary)
FROM employees
GROUP BY department_id;

# 2) 把1)查询的结果 与 employees表连接起来，进行筛选
SELECT employee_id , last_name , salary,e.department_id
FROM employees e
INNER JOIN (
		SELECT AVG(salary) ag, department_id
		FROM employees 
		GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary > ag_dep.ag


# 4、查询和 姓名中包含字母 u 的员工 在相同部门的员工的员工号和姓名

# 1) 查询姓名中包含字母 u 的员工的部门
SELECT DISTINCT last_name,department_id
FROM employees e
WHERE last_name LIKE '%u%';

# 2)查询 部门号 = 1）中任意一个的 员工的员工号和姓名
SELECT employee_id ,last_name ,department_id 
FROM employees
WHERE department_id IN(
						SELECT DISTINCT department_id
						FROM employees
						WHERE last_name LIKE '%u%'
)


# 5、查询在部门的location_id 为 1700的部门工作的员工的员工号

# 1) 查询location_id为1700的部门号
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;

# 2) 查询部门号 = 1) 中任意一个部门号的 员工号
SELECT employee_id
FROM employees
WHERE department_id = ANY(
					SELECT DISTINCT department_id
					FROM departments
					WHERE location_id = 1700
);


# 6、查询管理者是K_ing 的员工编号

# 1) 查询姓名为King的员工编号
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing';

# 2) 查询哪个员工的manger_id = 1）的查询结果
SELECT last_name ,salary
FROM employees
WHERE manager_id IN (
					SELECT employee_id
					FROM employees
					WHERE last_name = 'K_ing'
);


# 7、查询工资最高的员工的姓名，要求first_name 和 last_name显示为一列，列名为 姓.名

# 1) 查询最高工资
SELECT MAX(salary)
FROM employees;

# 2) 查询工资 = 1)的姓.名
SELECT CONCAT(first_name , last_name) "姓.名"
FROM employees
WHERE salary = (
					SELECT MAX(salary)
					FROM employees

);