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

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

子查询的书写风格：

可以使用子查询的位置：

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)

不可以使用子查询的位置

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’要执行两次，而多表查询只有一个。

子查询的排序问题

示例：查询员工表中员工工资最高的三位 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表起了个别名

单行子查询和多行子查

多行操作符

单行子查询示例-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);

子查询中的空值问题

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