含义:出现在其它语句中的select语句,称为子查询或内查询
外部的语句可以是 insert、update、delete、select
select在外部的查询语句,称为主查询或外查询分类:
按子查询出现的位置:
SELECT后:仅仅支持标量子查询FROM后:支持表子查询
WHERE或HAVING后:
常用于 标量子查询√
列子查询√少用于 行子查询
EXISTS后(可以叫相关子查询):所有子查询都行
按结果集的行列数不同:
标量 子查询/单行子查询 结果集 一行一列或者称为 列 子查询/多行子查询 结果集 一列多行 行 子查询 结果集 多行多列 表 子查询 一般为多行多列
#WHERE或HAVING后
1 标量子查询 单行子查询(一行一列)
2 列子查询 多行子查询 (一行多列)
3 行子查询 (多列多行)
单行和多行的特点:
1 子查询放在小括号内
2 子查询一般放在条件的右侧
3 标量子查询,一般搭配着单行操作符使用
> < >= <= <>列子查询的特点,一般搭配着多行操作符使用
in、any/some、all子查询的执行顺序优先于主查询,因为查询的条件用到子查询的结果
#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'
);#把第1步称为单行子查询,或标量子查询
#案例:返回job_id与141号员工相同,salary比143号员工多的员工 员工姓名 job_id和工资
#第1步查询141员工的job id
SELECT job_id
FROM employees
WHERE employee_id=141;
#第2步查询143员工的salary
SELECT salary
FROM employees
WHERE employee_id=143;
#第3步查询员工的姓名、job id、工资,要求jobid=1并且salary>2
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)
AND(
SELECT salary
FROM employees
WHERE employee_id=143
)
#在子查询中使用分组函数
#案例:返回公司工资最少的员工的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
);
#子查询中的having子句
#案例:查询最低工资大于50部门的最低工资,的部门id和最低工资
#第1步查询50部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50;
#第2步查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
#第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
);
#非法使用标量子查询
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id=50
);
/*
单行操作符(操作单个值的)只能搭配,单行子查询(标量子查询)
*/
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id=250
);
/*
子查询的结果不是一行一列
*
不管是什么样的子查询都放在小括号中
#2多行子查询(列子查询)(一列多行)
特点:返回多行
使用:需要搭配多行操作符来使用
把模糊查询小括号里放的常量,替换成了查询语句,查询语句的结果代替了模糊查询用的常量(查询语句的结果是一个列表)
常见的:
操作符 含义
IN/NOT IN 等于类表中的任意一个
ANY|SOME 和子查询返回的 某一个值比较
ALL 和子查询返回的 所有值比较IN意义:等于类表中的任意一个
NOT IN意义:这里面的都不是ANY|SOME意义:满足一个即可。例如a<ANY(10,20,30) a=15,这样就可以被查询出来。
使用的较少因为可以被代替:a>MIN()就行了,省去了麻烦事ALL意义:满足所有的。例如a<ANY(10,20,30) a=15,这样就不能被查询出来。
也可以被代替,a>MAX()
#案例:返回 location_id是1400或1700的部门中的 所有员工姓名
#1.查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id=1400 OR location_id=1700
//WHERE location_id=1400 || location_id=1700
//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)
);
#还可以
SELECT last_name
FROM employees
WHERE department_id=ANY(#等于里面任意一个
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);
SELECT last_name
FROM employees
WHERE department_id NOT IN(
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);
SELECT last_name
FROM employees
WHERE department_id !=ALL(#相当于并且
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);
SELECT last_name
FROM employees
WHERE department_id !=ANY(#相当于或者
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);
#案例:返回其他部门中比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';
#或,只要不超过最大的就可以满足
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT DISTINCT MAX(salary)
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id != 'IT_PROG';
#案例:返回其他部门中比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 DISTINCT MIN(salary)
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id != 'IT_PROG';
#3.行子查询(结果集一行多列,多行多列)
#案例:查询出员工编号最小 并且 工资最高的员工信息
#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 d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
) AS 个数
FROM departments d;
#案例:查询员工工号=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后面:
放在from后面一般的就是表,相当于把子查询的结果集充当一个表格来使用,充当数据源
要求:必须要起别名,否则找不到
表子查询
#案例:查询每个部门的平均工资的工资等级
#1 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2 连接1的结果集和工资等级表 筛选条件between
SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) AS ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、EXISTS后(相关子查询)
1代表true
2代表false语法
EXISTS(完整的查询语句)
结果:
1或0
SELECT EXISTS(
SELECT employee_id
FROM employees
WHERE salary =300000
);
#案例:查询有员工名的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department_id
);
/*
和其他子查询不一样,前面的子查询先执行,因为主查询用到子查询的结果
而这个先去执行主查询,根据主查询的结果,再去过滤。这就是相关子查询
子查询涉及到了主查询的字段
*/
#使用in的方式代替:能用 EXISTS就一定能用in代替
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees
);
#案例2:查询没有女朋友的男神信息
UPDATE beauty SET boyfriend_id = NULL WHERE id=10;
#IN 的方式
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);
#EXISTS
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);