子查询
混乱的开始!套娃的开始!
子查询的基本使用
- 子查询(内查询)在主查询之前一次执行完成。
- 子查询的结果被主查询(外查询)使用 。
- 注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询,多行操作符对应多行子查询
子查询的分类
-
分类方式1:
- 我们按内查询的结果返回一条还是多条记录,将子查询分为
单行子查询
、多行子查询
。- 单行 子查询查询结果只有一行
- 多行 子查询查询结果行数 > 1
- 我们按内查询的结果返回一条还是多条记录,将子查询分为
-
分类方式2:
- 我们按内查询是否被执行多次,将子查询划分为
相关(或关联)子查询
和不相关(或非关联)子查询
。
- 我们按内查询是否被执行多次,将子查询划分为
单行子查询
子查询返回单行数据
行比较操作符
操作符 | 含义 |
---|---|
= | equal to |
> | greater than |
>= | greater than or equal to |
< | less than |
<= | less than or equal to |
<> | not equal to |
例子:
emp 员工表{
id, '员工id'
name,'姓名'
salary,'工资'
depId,'部门Id'
...
}
dep 员工表{
id, '部门Id'
name,'部门姓名'
loc, '部门地址'
...
}
-----------------------------------
//单行子查询
'查询工资 > id = 123 员工工资 的员工信息'
1.子查询的方式
select * from emp e1
where salary > (
select salary from emp e2
where e2.id = 'id'
)
2.自连接的方式
select * from emp e1
Join emp e2
on e2.name = 'id'
and e1.salary > e2.salary.
-----------------------------------
'查询公司 工资最少的员工信息'
1.子查询的方式
select * from emp e1
where salary = (
select MIN(salary) from e1
)
-----------------------------------
'查询 在 与id=123的员工部门相同 的员工中 的最高工资'
1.子查询的方式
select MAX(e1.salary) '最高工资' from emp e1
where e1.deptid = (
select deptid from emp e2 WHERE e2.id = 123
)
-----------------------------------
'查询最低工资大于50号部门最低工资的部门id和其最低工资'
select e1.dept_id '部门id', MIN(e1.salary) '最低工资' from emp e1
GROUP BY e1.dept_id
HAVING 最低工资 > (
select MIN(e2.salary) from emp e2
where e2.dept_id = 20
)
-----------------------------------
'显式员工上id, name, loc 其中若员工的 id 与 id=0的员工的 depId相同 则loc为北京 否则上海'
#case when else 写法
select e1.id '部门id',
e1.ename '员工姓名',
(case e1.id
when
(select e2.dept_id from emp e2 where e1.id = 0) then '北京'
else '上海'
end
) 'loc'
from emp e1;
#if 写法
select e1.id '部门id',
e1.ename '员工姓名',
(IF(e1.id = (select e2.dept_id from emp e2 where e1.id = 0), '北京', '上海')
) '工作地点'
from emp e1;
...
多行子查询
子查询返回多行数据
多行比较操作符
操作符 | 含义 |
---|---|
IN | 等于列表中的任意一个 |
ANY | 需要和单行比较操作符一起使用,和子查询返回的任意值比较 |
ALL | 需要和单行比较操作符一起使用,和子查询返回的所有值比较 |
SOME | 实际上是ANY的别名,作用相同,一般常使用ANY |
例子:
emp 员工表{
id, '员工id'
name,'姓名'
salary,'工资'
depId,'部门Id'
...
}
dep 员工表{
id, '部门Id'
name,'部门姓名'
loc, '部门地址'
...
}
-----------------------------------
//多行子查询
'查询部门WXG的员工工资 > 部门IEG员工的工资 的员工信息'
select * from emp e1
Join dep d1
on d1.name = 'WXG' and e1.depId = d1.id
and e1.salary > ANY (
elect e2salary from emp e2 join dep d2
on d1.name = 'IEG' and e1.depId = d1.id
)
-----------------------------------
'查询平均工资最低的部门Id'
' 有点绕 先查询到每个部门的平均工资 然后从中取最小 再算一遍各部门的平均工资 使其 等于 算出的最小平均工资'
1.方法一
select depId '最小的平均工资' from emp e
roup by e.depId
having AVG(e.salary) = (
select MIN(avg_Salary) '平均工资'
from (
select AVG(salary) avg_Salary
from emp e1
group by e1.depId
) avg_Table //把查询结果设置别名 当作一张表
)
2.方法二
select depId '最小的平均工资' from emp e
group by e.depId
having
AVG(e.salary) <= ALL (
select AVG(salary)
from emp e1
group by e1.depId
)
...
MySQL中聚合函数是不能嵌套使用的
相关子查询
如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为关联子查询
。
例子:
emp 员工表{
id, '员工id'
name,'姓名'
salary,'工资'
depId,'部门Id'
...
}
dep 员工表{
id, '部门Id'
name,'部门姓名'
loc, '部门地址'
...
}
-----------------------------------
// 相关子查询
'查找哪些 员工工资 低于 所在部门的平均工资'
1.子查询在where中 相关是'内查询利用了外查询的表'
select e1.id, e1.name, e1.salary
from emp e1
where e1.salary < (
select AVG(e2.salary) AVG_salary
from emp e2
where e2.depId = e1.depId
)
2.子查询在from中 相关是'外查询利用了内查询的表'
select e1.id, e1.name, e1.salary
from emp e1 join (
select e2.depId, AVG(e2.salary) AVG_salary
from emp e2
group by e2.depId
) AVG_e2
on e1.salary < AVG_e2.AVG_salary
and e1.depId = AVG_e2.depId;
-----------------------------------
'查询员工的id, salary 按照 部门名字排序'
select e1.id, e1.salary
from emp e1
order by (
select d1.name
from dep d1
where d1.id = e1.depId
)
-----------------------------------
...
EXISTS 与 NOT EXISTS 关键字
-
关联子查询通常也会和 EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
-
如果在子查询中不存在满足条件的行:
- 条件返回 FALSE
- 继续在子查询中查找
-
如果在子查询中存在满足条件的行:
- 不在子查询中继续查找
- 条件返回 TRUE
-
NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
题目:查询公司管理者的employee_id,last_name,job_id,department_id信息
方式一:
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e2.manager_id = e1.employee_id
);
方式二:自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
方式三:
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
题目:查询departments表中,不存在于employees表中的部门的department_id和department_name
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE d.`department_id` = e.`department_id`
);
题目中可以使用子查询,也可以使用自连接。一般情况建议你使用自连接,因为在许多 DBMS 的处理过程中,对于自连接的处理速度要比子查询快得多。