Oracle数据库 -- 子查询篇

课程目标:

• 子查询概述
• 子查询的使用
• 子查询的类型：单行子查询和多行子查询

子查询注意的10个问题

1. 子查询语法中的小括号
2. 子查询的书写风格
3. 可以使用子查询的位置:where，select，having，from
4. 不可以使用子查询的位置：group by
5. 强调 : from后面的子查询
6. 主查询和子查询可以不是同一张表
7. 一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序
8. 一般先执行子查询，再执行主查询；但相关子查询例外
9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
10. 注意：子查询中是null值问题

子查询语法中的小括号：

table th:first-of-type {
width: 100px;
}

SELECT select_list FROM table WHER exper operator (SELECT select_list FROM table);//这是子查询

注意：子查询语句必须有小括号

子查询的书写风格：

• 换行和缩进，不要都写在一行

可以使用子查询的位置：

• 可以使用子查询的位置:where，select ，having，from
• 在select语句后面的子查询必须使用单行子查询

where语句:
select *
from 　emp where sal 　>　 (select 　sal
　　　　　　 　　　 　　　　　from 　 emp
　　　　　　 　　　　　　　　 where　 ename = ‘SCOTT’ );

select语句：
select empno,ename,sal,(select job from emp where empno=7839)第四列 from emp;

having语句：
select deptno,avg(sal)
from emp
group by deptno
having avg(sal) > (select max(sal)
　　　　　　　　　from emmp
　　　　　　　　　where deptno = 30);

from语句：
select * from (select empno,ename,sal from emp)

不可以使用子查询的位置

• group by 不能使用子查询
• 会报错：ORA-22818：这里不允许出现子查询表达式

from后面的子查询

• from后面的子查询，很多问题是通过这样的方式解决的

• 示例：查询员工信息：员工号，姓名，月薪，年薪

  select * from (select tmpid, tname,gongzi,gongzi * 12 allgongzi from tmp);

主查询和子查询可以不是同一张表

select * from tmp where tmpid = (select userid from ttuser where name = ‘金培’ )

select t.* from tmp t inner join ttuser b on t.tmpid = b.userid where b.name = ‘金培’;

两条语句返回的结果是一样的，从理论上讲多表查询好于子查询，不考虑实际情况下。因为子查询有两个‘from’要执行两次，而多表查询只有一个。

子查询的排序问题

• 一般子查询中不使用排序，但在Top-N分析问题中，必须对子查询排序
• Top-N问题就是，我们按照某个规律排序以后取出最前面的几条记录
• rownum 行号需要注意的两个问题：永远是按照默认的顺序生成；只能使用<，<= 不能使用 >，>=

示例：查询员工表中员工工资最高的三位
select rownum,sal from (select * from tmp order by sal desc) where rownum <= 3

先执行子查询

• 一般先执行子查询，再执行主查询；但相关子查询例外
• 相关子查询可以把主查询的值作为参数传递给子查询

示例：找到员工表中薪水大于本部门平均薪水的员工
select empno,ename,sal,(select avg(sal) from emp where deptno = e.deptno) avgsal from emp e
where sal > (select avg(sal) from emp where deptno = e.deptno);
此sql语句中把emp表当做参数使用，给emp表起了个别名

单行子查询和多行子查

• 单行子查询只能使用单行操作符
• 多行子查询只能使用多行操作符
• 只返回单行记录的是单行子查询；返回多行记录的是多行子查询
  单行操作符：
• 在一个主查询中可以有多个子查询

操作符	含义
=	Equal to
>	Greater than
“>=”	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to

多行操作符

操作符	含义
IN	等于列表中的任何一个
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

单行子查询示例-1
查询员工信息，要求：
职位与3员工一致；薪水小于1员工的薪水

select tname, bumen, sal
from tmp
where bumen = (select bumen from tmp where tmpid = 2)
and sal < (select sal from tmp where tmpid = 1)

单行子查询示例-2
查询工资最低的员工信息
select * from tmp where sal = (select min(sal) from tmp)

单行子查询示例-3
查询最低工资大于20号部门最低工资的部门号和部门的最低工资
select tmpid, min(sal)
from tmp
group by tmpid
having min(sal) > (select min(sal) from tmp where tmpid = 1)

————–

多行子查询示例-1
查询部门名称是’研发部’和’科技部’的员工信息
select *
from tmp
where tmpid in (select tmpid
from tmp
where bumen = ‘研发部’
or bumen = ‘科技部’)

多行子查询示例-2
示例：查询工资比30号部门任意一个员工高的员工信息
select * from emp
where sal > any(select sal from emp where deptno = 30);

select * from emp
where sal > (select min(sal) from emp where deptno = 30);

多行子查询示例-3
示例：查询工资比30号部门所有员工高的员工信息
select * from emp
where sal > all(select sal from emp where deptno = 30);

select * from emp
where sal > all(select max(sal) from emp where deptno = 30);

子查询中的空值问题

• 单行子查询的空值问题
• 多行子查询中的null值问题
• 如果表中有空值的话，不能用not in

示例：查询不是老板的员工
select * from emp where rmpno not in (select mgr from emp where mgr is not null); //如果表中没有空值的话，就可以使用