李玉婷MYSQL进阶07-子查询

子查询

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

分类：

按子查询出现的位置：
select 后面：仅仅支持标量子查询
from 后面：支持表子查询
where或having后面：⭐️

                                   标量子查询

                                   列子查询

                                  行子查询
exists后面（相关子查询）：表子查询
按结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一行多列）

行子查询（结果集有一行多列）

表子查询（结果集一般为多行多列）

 

一、where或having后面：⭐️

1.标量子查询（单行子查询）

2.列子查询（多行子查询）

3.行子查询（多行多列）

特点：

1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 标量子查询，一般搭配着单行操作符使用（>、 <、 >=、 <=、 =、 <>）；列子查询一般搭配着多行操作符使用（In ANY/SOME ALL)
4. 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询（单行子查询）

案例1:谁的工资比abel高？

1. 查询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号员工工资
2. 查询143号员工的salary
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. 查询公司的最低工资
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号部门的最低工资
2. 查询每个部门的最低工资
3. 在2的基础上筛选，满足min(salary)>1

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

非法使用标量子查询

选择的结果为多行多列

2.列子查询（多行子查询）

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

1. 查询location_id是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'工种任一工资
2. 查询员工号、姓名、job_id以及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';		

或：

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

3.行子列查询（结果集一行多列/多行多列）

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

1. 最小的员工编号
2. 查询最高工资
3. 查询员工信息

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
				SELECT MIN(employee_id),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 (
				SELECT department_name
				FROM departments d
				INNER JOIN employees e
				ON d.department_id=e.department_id
				WHERE e.employee_id=102
)部门名;				

三、from后面的子查询

 

案例：查询每个部门的平均工资的工资等级

1. 每个部门的平均工资
2. 连接1的结果集和job_grade表，筛选条件平均工资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后面的子查询（相关子查询）

是否存在，布尔类型

语法：

e xi st（完整的查询语句）

结果：0或1

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

#in

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

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

#exsits

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 bo.id=b.boyfriend_id
);

五、案例

1.查询和zlotkey相同部门的员工姓名和工资

1. 查询zlotkey的部门
2. 查询部门号=1的姓名和工资

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

1. 查询平均工资
2. 查询工资>1的员工号，姓名和工资

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

1. 查询各部门的平均工资
2. 连接1的结果集和employee表，进行筛选

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的员工的部门
2. 查询部门号=1中的任意一个的员工号和姓名

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

1. 查询location_id为1700的部门（去重）
2. 部门号等于1中的任意一个员工号

 

6.查询管理者是king的员工姓名和工资

1. 查询姓名为king的员工编号
2. 查询哪个员工的manager——id=1

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

1. 最高工资
2. 查询工资=1的姓，名
3.