Oracle入门之子查询

为什么有子查询

对于一个问题,1步不能求解,需要多步

1 先求scott的工资
select sal from emp where ename=’SCOTT’; ===>3000
2 求比3000大的工资
select * from emp
where sal > 3000;

通过子查询求解,子查询的本质是select语句的嵌套

select * from emp
where sal > (select sal from emp where ename='SCOTT'  )

基本语法

注意事项

合理的书写风格
子查询的() 不要丢掉

子查询和主查询可以不是同一张表,只要子查询返回的结果,主查询能用就行

查询部门名称是SALES 的员工信息

部门表-》部门编号-》利用编号在员工表中获取对应部门的员工信息：

sql> select * 
  @H_502_49@2  from emp
  @H_502_49@3  where deptno = 
  @H_502_49@4  (select deptno
  @H_502_49@5  from dept
  @H_502_49@6  where dname = 'SALES')
  @H_502_49@7  ;

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      @H_502_49@7499 ALLEN      SALESMAN        @H_502_49@7698 @H_502_49@20-FEB-@H_502_49@81       @H_502_49@1600        @H_502_49@300     @H_502_49@30
      @H_502_49@7521 WARD       SALESMAN        @H_502_49@7698 @H_502_49@22-FEB-@H_502_49@81       @H_502_49@1250        @H_502_49@500     @H_502_49@30
      @H_502_49@7654 MARTIN     SALESMAN        @H_502_49@7698 @H_502_49@28-SEP-@H_502_49@81       @H_502_49@1250       @H_502_49@1400     @H_502_49@30
      @H_502_49@7698 BLAKE      MANAGER         @H_502_49@7839 @H_502_49@01-MAY-@H_502_49@81       @H_502_49@2850            @H_502_49@30
      @H_502_49@7844 TURNER     SALESMAN        @H_502_49@7698 @H_502_49@08-SEP-@H_502_49@81       @H_502_49@1500      @H_502_49@0     @H_502_49@30
      @H_502_49@7900 JAMES      CLERK       @H_502_49@7698 @H_502_49@03-DEC-@H_502_49@81        @H_502_49@950            @H_502_49@30

@H_502_49@6 rows selected.

多表查询的方法：

select e.* from emp e,dept d where e.deptno = d.deptno and d.dname='SALES'

关于sql语句的优化第三点：子查询检索数据库2次,多表查询只检索一次，优先用多表查询！利用了空间换时间，因为多表查询的列数增多，时间减少！

可以在主查询的什么地方放一个子查询

select ...可以放置子查询 (必须要放单行子查询)
    from .... 可以放置子查询
    where ... 可以放置子查询
    group by .... 不
    having .... 可以放置子查询 ppt例子
    order by ... 不

子查询中一般不使用order by,但是Top-N问题,子查询必须要用order by
eg: 求工资的前三名分页.... M<=x<=N

子查询的分类

按照子查询返回的条目数,分为: 单行子查询和多行子查询
- 单行子查询只能用单行比较操作符 (= < >)
- 多行子查询只能用多行比较操作符 (in any all)
按照子查询和主查询的执行顺序来分
- 一般子子查询子查询把结果返回给主查询….一般
- 相关子查询主查询把select列中的参数传递给子查询

单行子查询

查询员工信息,属于141号,薪水比143号员工工资高的 col1,col2,co3信息

在子查询中使用组函数

查询工资最低的员工信息

sql> select ename,empno,sal
  @H_502_49@2  from emp
  @H_502_49@3  where sal = 
  @H_502_49@4  (select min(sal)
  @H_502_49@5  from emp)
  @H_502_49@6  ;

ENAME       EMPNO        SAL
---------- ---------- ----------
SMITH        @H_502_49@7369        @H_502_49@800

HAVING 子句使用子查询

求各个部门编号和部门的最低工资 (这个最低工资要比20号部门的最低工资要高)

sql> select deptno,min(sal)
  @H_502_49@2  from emp
  @H_502_49@3  group by deptno
  @H_502_49@4  having min(sal) >
  @H_502_49@5  (select min(sal) 
  @H_502_49@6  from emp
  @H_502_49@7  where deptno = @H_502_49@20)
  @H_502_49@8  ;

    DEPTNO   MIN(SAL)
---------- ----------
    @H_502_49@30    @H_502_49@950
    @H_502_49@10   @H_502_49@1300

sql>

select 放置子查询 (必须要放单行子查询)

错误示例===select子句中使用了多行子查询

1  select empno,ename,sal,(select ename from emp where deptno = @H_502_49@10) "十号部门员工" @H_502_49@2* from emp sql> / select empno,(select ename from emp where deptno = @H_502_49@10) "十号部门员工" * ERROR at line @H_502_49@1: ORA-@H_502_49@01427: single-row subquery returns more than one row

正确实例

1  select empno,(select ename from emp where deptno=@H_502_49@10 and ename='CLARK') "十号部" @H_502_49@2* from emp sql> / EMPNO ENAME SAL 十号部 ---------- ---------- ---------- ---------- @H_502_49@1 tom_abc @H_502_49@8000 CLARK @H_502_49@7369 SMITH @H_502_49@800 CLARK @H_502_49@7499 ALLEN @H_502_49@1600 CLARK @H_502_49@7521 WARD @H_502_49@1250 CLARK @H_502_49@7566 JONES @H_502_49@2975 CLARK @H_502_49@7654 MARTIN @H_502_49@1250 CLARK @H_502_49@7698 BLAKE @H_502_49@2850 CLARK @H_502_49@7782 CLARK @H_502_49@2450 CLARK @H_502_49@7788 SCOTT @H_502_49@3000 CLARK @H_502_49@7839 KING @H_502_49@5000 CLARK @H_502_49@7844 TURNER @H_502_49@1500 CLARK @H_502_49@7876 ADAMS @H_502_49@1100 CLARK @H_502_49@7900 JAMES @H_502_49@950 CLARK @H_502_49@7902 FORD @H_502_49@3000 CLARK @H_502_49@7934 MILLER @H_502_49@1300 CLARK @H_502_49@15 rows selected.

from后面放置子查询–多行子查询

select * from (select a,b,c,d from emp where d='aaa'); 这种情况在oracle用的比较多 !

求员工编号和员工姓名,只能显示这2列，开头必须是select *。

sql> select * 
  @H_502_49@2  from 
  @H_502_49@3  (select empno,ename
  @H_502_49@4  from emp);

     EMPNO ENAME
---------- ----------
     @H_502_49@1 tom_abc
      @H_502_49@7369 SMITH
      @H_502_49@7499 ALLEN
      @H_502_49@7521 WARD
      @H_502_49@7566 JONES
      @H_502_49@7654 MARTIN
      @H_502_49@7698 BLAKE
      @H_502_49@7782 CLARK
      @H_502_49@7788 SCOTT
      @H_502_49@7839 KING
      @H_502_49@7844 TURNER
      @H_502_49@7876 ADAMS
      @H_502_49@7900 JAMES
      @H_502_49@7902 FORD
      @H_502_49@7934 MILLER

@H_502_49@15 rows selected.

where 放置子查询

查询部门名称是SALES 的员工信息。

部门表-》部门编号-》利用编号在员工表中获取对应部门的员工信息：

sql> select * 
  @H_502_49@2  from emp
  @H_502_49@3  where deptno = 
  @H_502_49@4  (select deptno
  @H_502_49@5  from dept
  @H_502_49@6  where dname = 'SALES')
  @H_502_49@7  ;

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      @H_502_49@7499 ALLEN      SALESMAN        @H_502_49@7698 @H_502_49@20-FEB-@H_502_49@81       @H_502_49@1600        @H_502_49@300     @H_502_49@30
      @H_502_49@7521 WARD       SALESMAN        @H_502_49@7698 @H_502_49@22-FEB-@H_502_49@81       @H_502_49@1250        @H_502_49@500     @H_502_49@30
      @H_502_49@7654 MARTIN     SALESMAN        @H_502_49@7698 @H_502_49@28-SEP-@H_502_49@81       @H_502_49@1250       @H_502_49@1400     @H_502_49@30
      @H_502_49@7698 BLAKE      MANAGER         @H_502_49@7839 @H_502_49@01-MAY-@H_502_49@81       @H_502_49@2850            @H_502_49@30
      @H_502_49@7844 TURNER     SALESMAN        @H_502_49@7698 @H_502_49@08-SEP-@H_502_49@81       @H_502_49@1500      @H_502_49@0     @H_502_49@30
      @H_502_49@7900 JAMES      CLERK       @H_502_49@7698 @H_502_49@03-DEC-@H_502_49@81        @H_502_49@950            @H_502_49@30

@H_502_49@6 rows selected.

多行子查询

但是多行子查询中是可以使用>,<以及=操作符的，如果查询结果是多个，就要使用in，all或者any对结果进行处理，再和前面的符号进行比较操作。

在多行子查询中使用 IN 操作符

查询部门名称为 SALES 和 ACCOUNTING 的员工信息

select * from emp
  @H_502_49@2  where deptno in
  @H_502_49@3  (select deptno
  @H_502_49@4  from dept
  @H_502_49@5* where dname='SALES' or dname='ACCOUNTING')
  @H_502_49@6  /

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     @H_502_49@1 tom_abc                     @H_502_49@8000            @H_502_49@10
      @H_502_49@7934 MILLER     CLERK       @H_502_49@7782 @H_502_49@23-JAN-@H_502_49@82       @H_502_49@1300            @H_502_49@10
      @H_502_49@7839 KING       PRESIDENT        @H_502_49@17-NOV-@H_502_49@81       @H_502_49@5000            @H_502_49@10
      @H_502_49@7782 CLARK      MANAGER         @H_502_49@7839 @H_502_49@09-JUN-@H_502_49@81       @H_502_49@2450            @H_502_49@10
      @H_502_49@7499 ALLEN      SALESMAN        @H_502_49@7698 @H_502_49@20-FEB-@H_502_49@81       @H_502_49@1600        @H_502_49@300     @H_502_49@30
      @H_502_49@7844 TURNER     SALESMAN        @H_502_49@7698 @H_502_49@08-SEP-@H_502_49@81       @H_502_49@1500      @H_502_49@0     @H_502_49@30
      @H_502_49@7900 JAMES      CLERK       @H_502_49@7698 @H_502_49@03-DEC-@H_502_49@81        @H_502_49@950            @H_502_49@30
      @H_502_49@7521 WARD       SALESMAN        @H_502_49@7698 @H_502_49@22-FEB-@H_502_49@81       @H_502_49@1250        @H_502_49@500     @H_502_49@30
      @H_502_49@7698 BLAKE      MANAGER         @H_502_49@7839 @H_502_49@01-MAY-@H_502_49@81       @H_502_49@2850            @H_502_49@30
      @H_502_49@7654 MARTIN     SALESMAN        @H_502_49@7698 @H_502_49@28-SEP-@H_502_49@81       @H_502_49@1250       @H_502_49@1400     @H_502_49@30

@H_502_49@10 rows selected.

多表查询的方式：

sql> select e.*
  @H_502_49@2  from emp e,dept d
  @H_502_49@3  where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     @H_502_49@1 tom_abc                     @H_502_49@8000            @H_502_49@10
      @H_502_49@7934 MILLER     CLERK       @H_502_49@7782 @H_502_49@23-JAN-@H_502_49@82       @H_502_49@1300            @H_502_49@10
      @H_502_49@7839 KING       PRESIDENT        @H_502_49@17-NOV-@H_502_49@81       @H_502_49@5000            @H_502_49@10
      @H_502_49@7782 CLARK      MANAGER         @H_502_49@7839 @H_502_49@09-JUN-@H_502_49@81       @H_502_49@2450            @H_502_49@10
      @H_502_49@7499 ALLEN      SALESMAN        @H_502_49@7698 @H_502_49@20-FEB-@H_502_49@81       @H_502_49@1600        @H_502_49@300     @H_502_49@30
      @H_502_49@7844 TURNER     SALESMAN        @H_502_49@7698 @H_502_49@08-SEP-@H_502_49@81       @H_502_49@1500      @H_502_49@0     @H_502_49@30
      @H_502_49@7900 JAMES      CLERK       @H_502_49@7698 @H_502_49@03-DEC-@H_502_49@81        @H_502_49@950            @H_502_49@30
      @H_502_49@7521 WARD       SALESMAN        @H_502_49@7698 @H_502_49@22-FEB-@H_502_49@81       @H_502_49@1250        @H_502_49@500     @H_502_49@30
      @H_502_49@7698 BLAKE      MANAGER         @H_502_49@7839 @H_502_49@01-MAY-@H_502_49@81       @H_502_49@2850            @H_502_49@30
      @H_502_49@7654 MARTIN     SALESMAN        @H_502_49@7698 @H_502_49@28-SEP-@H_502_49@81       @H_502_49@1250       @H_502_49@1400     @H_502_49@30

@H_502_49@10 rows selected.

在多行子查询中使用 ANY操作符

any 和其中的任意一个元素做比较
查询薪水比30号部门任意一个员工薪高的员工信息=====大于这个集合的最小值就可以.

@H_502_49@1  select * from emp
  @H_502_49@2  where sal >
  @H_502_49@3  any(select sal from emp
  @H_502_49@4* where deptno = @H_502_49@30)
sql> /

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     @H_502_49@1 tom_abc                     @H_502_49@8000            @H_502_49@10
      @H_502_49@7839 KING       PRESIDENT        @H_502_49@17-NOV-@H_502_49@81       @H_502_49@5000            @H_502_49@10
      @H_502_49@7902 FORD       ANALYST         @H_502_49@7566 @H_502_49@03-DEC-@H_502_49@81       @H_502_49@3000            @H_502_49@20
      @H_502_49@7788 SCOTT      ANALYST         @H_502_49@7566 @H_502_49@19-APR-@H_502_49@87       @H_502_49@3000            @H_502_49@20
      @H_502_49@7566 JONES      MANAGER         @H_502_49@7839 @H_502_49@02-APR-@H_502_49@81       @H_502_49@2975            @H_502_49@20
      @H_502_49@7698 BLAKE      MANAGER         @H_502_49@7839 @H_502_49@01-MAY-@H_502_49@81       @H_502_49@2850            @H_502_49@30
      @H_502_49@7782 CLARK      MANAGER         @H_502_49@7839 @H_502_49@09-JUN-@H_502_49@81       @H_502_49@2450            @H_502_49@10
      @H_502_49@7499 ALLEN      SALESMAN        @H_502_49@7698 @H_502_49@20-FEB-@H_502_49@81       @H_502_49@1600        @H_502_49@300     @H_502_49@30
      @H_502_49@7844 TURNER     SALESMAN        @H_502_49@7698 @H_502_49@08-SEP-@H_502_49@81       @H_502_49@1500      @H_502_49@0     @H_502_49@30
      @H_502_49@7934 MILLER     CLERK       @H_502_49@7782 @H_502_49@23-JAN-@H_502_49@82       @H_502_49@1300            @H_502_49@10
      @H_502_49@7521 WARD       SALESMAN        @H_502_49@7698 @H_502_49@22-FEB-@H_502_49@81       @H_502_49@1250        @H_502_49@500     @H_502_49@30
      @H_502_49@7654 MARTIN     SALESMAN        @H_502_49@7698 @H_502_49@28-SEP-@H_502_49@81       @H_502_49@1250       @H_502_49@1400     @H_502_49@30
      @H_502_49@7876 ADAMS      CLERK       @H_502_49@7788 @H_502_49@23-MAY-@H_502_49@87       @H_502_49@1100            @H_502_49@20

@H_502_49@13 rows selected.

在多行子查询中使用 ALL操作符

all 和集合中的所有元素做比较
查询薪水比30号部门所有员工高的员工信息=====大于这个集合的最大值.

select * @H_502_49@2 from emp @H_502_49@3 where sal > @H_502_49@4* all(select sal from emp where deptno=@H_502_49@30) sql> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- @H_502_49@7566 JONES MANAGER @H_502_49@7839 @H_502_49@02-APR-@H_502_49@81 @H_502_49@2975 @H_502_49@20 @H_502_49@7902 FORD ANALYST @H_502_49@7566 @H_502_49@03-DEC-@H_502_49@81 @H_502_49@3000 @H_502_49@20 @H_502_49@7788 SCOTT ANALYST @H_502_49@7566 @H_502_49@19-APR-@H_502_49@87 @H_502_49@3000 @H_502_49@20 @H_502_49@7839 KING PRESIDENT @H_502_49@17-NOV-@H_502_49@81 @H_502_49@5000 @H_502_49@10 @H_502_49@1 tom_abc @H_502_49@8000 @H_502_49@10

子查询中的空值问题

查询不是经理的员工信息.

*思路先按照: 查询是经理的员工信息–把所有的经理id给查找出来,形成一个集合供in操作。

检索所有的经理信息

sql> ed
Wrote file afiedt.buf

  @H_502_49@1  select *
  @H_502_49@2  from emp
  @H_502_49@3* where empno in (select mgr from emp)
sql> /

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      @H_502_49@7566 JONES      MANAGER         @H_502_49@7839 @H_502_49@02-APR-@H_502_49@81       @H_502_49@2975            @H_502_49@20
      @H_502_49@7698 BLAKE      MANAGER         @H_502_49@7839 @H_502_49@01-MAY-@H_502_49@81       @H_502_49@2850            @H_502_49@30
      @H_502_49@7782 CLARK      MANAGER         @H_502_49@7839 @H_502_49@09-JUN-@H_502_49@81       @H_502_49@2450            @H_502_49@10
      @H_502_49@7788 SCOTT      ANALYST         @H_502_49@7566 @H_502_49@19-APR-@H_502_49@87       @H_502_49@3000            @H_502_49@20
      @H_502_49@7839 KING       PRESIDENT        @H_502_49@17-NOV-@H_502_49@81       @H_502_49@5000            @H_502_49@10
      @H_502_49@7902 FORD       ANALYST         @H_502_49@7566 @H_502_49@03-DEC-@H_502_49@81       @H_502_49@3000            @H_502_49@20

@H_502_49@6 rows selected.

检索所有的不是经理信息

sql> ed
Wrote file afiedt.buf

  @H_502_49@1  select *
  @H_502_49@2  from emp
  @H_502_49@3* where empno not in (select mgr from emp where mgr is not null)
sql> /

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     @H_502_49@1 tom_abc                     @H_502_49@8000            @H_502_49@10
      @H_502_49@7369 SMITH      CLERK       @H_502_49@7902 @H_502_49@17-DEC-@H_502_49@80        @H_502_49@800            @H_502_49@20
      @H_502_49@7499 ALLEN      SALESMAN        @H_502_49@7698 @H_502_49@20-FEB-@H_502_49@81       @H_502_49@1600        @H_502_49@300     @H_502_49@30
      @H_502_49@7521 WARD       SALESMAN        @H_502_49@7698 @H_502_49@22-FEB-@H_502_49@81       @H_502_49@1250        @H_502_49@500     @H_502_49@30
      @H_502_49@7654 MARTIN     SALESMAN        @H_502_49@7698 @H_502_49@28-SEP-@H_502_49@81       @H_502_49@1250       @H_502_49@1400     @H_502_49@30
      @H_502_49@7844 TURNER     SALESMAN        @H_502_49@7698 @H_502_49@08-SEP-@H_502_49@81       @H_502_49@1500      @H_502_49@0     @H_502_49@30
      @H_502_49@7876 ADAMS      CLERK       @H_502_49@7788 @H_502_49@23-MAY-@H_502_49@87       @H_502_49@1100            @H_502_49@20
      @H_502_49@7900 JAMES      CLERK       @H_502_49@7698 @H_502_49@03-DEC-@H_502_49@81        @H_502_49@950            @H_502_49@30
      @H_502_49@7934 MILLER     CLERK       @H_502_49@7782 @H_502_49@23-JAN-@H_502_49@82       @H_502_49@1300            @H_502_49@10

@H_502_49@9 rows selected.

非法使用子查询

补充：

select是一个视图的概念，他只负责将数据库内容显示出来，和数据库的物理存储没有必然联系，可以抓取多张表的内容汇总显示！所以可以在其后面添加任意符合sql语句的表达式，使用逗号分离，比如加上时间等信息。

@H_502_49@1  select ename,sysdate
  @H_502_49@2  from
  @H_502_49@3  (select empno,ename
  @H_502_49@4* from emp)
sql> /

ENAME      SYSDATE
---------- ---------
tom_abc    @H_502_49@06-JAN-@H_502_49@17
SMITH      @H_502_49@06-JAN-@H_502_49@17
ALLEN      @H_502_49@06-JAN-@H_502_49@17
WARD       @H_502_49@06-JAN-@H_502_49@17
JONES      @H_502_49@06-JAN-@H_502_49@17
MARTIN     @H_502_49@06-JAN-@H_502_49@17
BLAKE      @H_502_49@06-JAN-@H_502_49@17
CLARK      @H_502_49@06-JAN-@H_502_49@17
SCOTT      @H_502_49@06-JAN-@H_502_49@17
KING       @H_502_49@06-JAN-@H_502_49@17
TURNER     @H_502_49@06-JAN-@H_502_49@17
ADAMS      @H_502_49@06-JAN-@H_502_49@17
JAMES      @H_502_49@06-JAN-@H_502_49@17
FORD       @H_502_49@06-JAN-@H_502_49@17
MILLER     @H_502_49@06-JAN-@H_502_49@17

@H_502_49@15 rows selected.

in作用于集合的时候，有空值不会受到影响，但是not in则会受到影响，无法执行正确的数据库操作！

The reason is that all conditions that compare a null value result in a null.


Deptno In(@H_502_49@10,@H_502_49@20);
解释为: Deptno =@H_502_49@10 || deptno=@H_502_49@20 ||deptno=null
Deptno not In(@H_502_49@10,@H_502_49@20,null);
解释为:Deptno!=@H_502_49@10 && Deptno!=@H_502_49@20 && deptno!=null