甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

QQ :532337155
电话：028-86922220
 
成都网站建设设计

将想法与焦点和您一起共享

Oracle专题8之Oracle的子查询

1、Oracle的子查询的概述

  • 什么是子查询？
  • 子查询是指嵌入在其他SQL语句中的SELECT语句，也称之为嵌套查询。
  • 可以使用子查询的位置：where、select列表、having、from。

    a、在where子句中使用子查询

  • 示例：查询出和FORD是相同职位的员工：select * from emp where job = (select job from emp where ename='FORD'); 
    SQL> select * from emp where job = (select job from emp where ename='FORD');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7951 EASON      ANALYST         7566 01-12月-17           3000
                20
            7788 SCOTT      ANALYST         7566 19-4月 -87           3000
                20
            7902 FORD       ANALYST         7566 03-12月-81           3000
                20

    b、在select子句中使用子查询

  • 示例：查询出每个部门的编号、名称、位置和部门人数。示例：select deptno, dname, loc, (select count(empno) from emp where emp.deptno = dept.deptno) count from dept; 
    SQL> select deptno, dname, loc, (select count(empno) from emp where emp.deptno = dept.deptno) count from dept;
DEPTNO DNAME          LOC                COUNT
    10 ACCOUNTING     NEW YORK               3
    20 RESEARCH       DALLAS                 6
    30 SALES          CHICAGO                6
    40 OPERATIONS     BOSTON                 0

    c、在having子句中使用子查询

  • 举例：查询员工信息表，按照部门编号进行分组，要求显示员工的部门编号、平均工资，查询条件时平均工资大于30号部门的最高工资。 
    SQL> select deptno, avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno = 30);
        DEPTNO   AVG(SAL)
                10 2916.66667

    d、在from子句中使用子查询

  • 把子查询的结果看成一张新的表。示例：查询并显示高于部门平均工资的雇员信息。 
    SQL> select ename, job, sal from emp, (select deptno, avg(sal) avgsal from emp group by deptno) dept where emp.deptno = dept.deptno and sal > avgsal;
ENAME      JOB              SAL
EASON      ANALYST         3000
ALLEN      SALESMAN        1600
JONES      MANAGER         2975
BLAKE      MANAGER         2850
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
FORD       ANALYST         3000
已选择7行。

    2、Oracle的主查询和子查询

  • 什么是主查询和子查询？
    Oracle专题8之Oracle的子查询

    a、一个主查询可以有多个子查询

  • 举例：显示职位和7521的职位相同并工资大于7934这个员工工资的员工信息。 
    SQL> select * from emp where job = (select job from emp where empno = 7521) and sal > (select sal from emp where empno = 7934);
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
                30
            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
                30

    b、子查询的执行顺序

  • 一般先执行子查询，再执行主查询，但相关子查询例外。
  • 举例：查询员工表中小于平均工资的员工信息。 
    SQL> select empno, ename, sal from emp where sal < (select avg(sal) from emp);
         EMPNO ENAME             SAL
            7369 G_EASON           800
            7499 ALLEN            1600
            7521 WARD             1250
            7654 MARTIN           1250
            7844 TURNER           1500
            7876 ADAMS            1100
            7900 JAMES             950
            7934 MILLER           1300
已选择8行。

    c、相关子查询

  • 当子查询需要引用主查询的表列时，Oracle会执行相关查询。
  • 相关子查询是先执行主查询，在执行子查询。
  • 示例：查询工资高于部门平均工资的雇员名、工资和部门号： 
    SQL> select ename, sal, deptno from emp e where sal > (select avg(sal) from emp where deptno = e.deptno);
ENAME             SAL     DEPTNO
EASON            3000         20
ALLEN            1600         30
JONES            2975         20
BLAKE            2850         30
SCOTT            3000         20
KING             5000         10
FORD             3000         20
已选择7行。

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

  • 主查询和子查询可以查询的不是同一张表。
  • 示例：查询部门名称时ACCOUNTING的员工信息。 
    SQL> select * from emp where deptno = (select deptno from dept where dname='ACCOUNTING');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7782 CLARK      MANAGER         7839 09-6月 -81           2450
                10
            7839 KING       PRESIDENT            17-11月-81           5000
                10
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10

    3、Oracle的子查询：单行子查询

  • 子查询的类型：单行子查询和多行子查询。
  • 单行子查询：只返回一行数据的子查询语句。
  • 使用单行比较操作符：
操作符含义
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于

a、使用单行子查询

  • 示例1：显示与JAMES同部门的所有其他的员工姓名、工资、部门号。 
    SQL> select ename, sal, deptno from emp where deptno = (select deptno from emp where ename='JAMES') AND ename <> 'JAMES';
ENAME             SAL     DEPTNO-
ALLEN            1600         30
WARD             1250         30
MARTIN           1250         30
BLAKE            2850         30
  • 示例2：查询大于等于公司平均工资的员工的姓名、职位、工资。 
    SQL> select ename, job, sal from emp where sal >= (select avg(sal) from emp);
ENAME      JOB              SAL
EASON      ANALYST         3000
JONES      MANAGER         2975
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
FORD       ANALYST         3000
已选择7行。
  • 示例3：查询部门名称不是‘SAVE’的员工。 
    SQL> select * from emp where deptno <> (select deptno from dept where dname= 'SALES');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7951 EASON      ANALYST         7566 01-12月-17           3000
                20
          ......
            7902 FORD       ANALYST         7566 03-12月-81           3000
                20
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
已选择9行。

    b、非法使用单行子查询

  • 示例：select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno); 
    SQL> select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno);
select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno)
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行
  • select max(sal) from emp group by deptno;sql语句返回多行数据。 
    SQL> select max(sal) from emp group by deptno;
MAX(SAL)
  2850
  3000
  5000

    4、Oracle的子查询：多行子查询

  • 多行子查询是指返回多行数据的子查询语句。
  • 使用多行比较操作符：（使用多行子查询时必须使用多行比较操作符。）
运算符含义
IN 等于列表中的任何一个
ALL 和子查询返回的所有值进行比较
ANY 和子查询返回的任一值进行比较

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

  • 示例：查询工作地点在NEW YORK和CHICAGO的部门所对应的员工信息。 
    SQL> select * from emp where deptno in (select deptno from dept where loc='NEW YORK' or loc = 'CHICAGO');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
            ......
            7698 BLAKE      MANAGER         7839 01-5月 -81           2850
                30
            7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
                30
已选择9行。

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

  • 示例：查询高于30号部门所有员工工资的员工名、工资和部门号。 
    SQL> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30);
ENAME             SAL     DEPTNO
JONES            2975         20
EASON            3000         20
FORD             3000         20
SCOTT            3000         20
KING             5000         10

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

  • 示例：查询高于10号部门任意一个员工工资的员工名、工资和部门号。 
    SQL> select ename, sal, deptno from emp where sal > any (select sal from emp where deptno = 10);
ENAME             SAL     DEPTNO
KING             5000         10
EASON            3000         20
......
ALLEN            1600         30
TURNER           1500         30
已选择9行。

    5、Oracle的子查询需要注意的问题

    a、不可以在group by子句中使用子查询。

  • 示例：select avg(sal) from emp group by (select deptno from emp); 
    SQL> select avg(sal) from emp group by (select deptno from emp);
select avg(sal) from emp group by (select deptno from emp)
第 1 行出现错误:
ORA-22818: 这里不允许出现子查询表达式

    b、在TOP-N分析问题中，需对子查询排序

  • 示例：显示员工信息表中工资最高的前五名员工。 
    SQL> select rownum, empno, ename, sal from (select * from emp order by sal desc) where rownum <= 5;
        ROWNUM      EMPNO ENAME             SAL
                 1       7839 KING             5000
                 2       7951 EASON            3000
                 3       7902 FORD             3000
                 4       7788 SCOTT            3000
                 5       7566 JONES            2975

    c、单行子查询的空值问题

  • 示例：select ename, job, from emp where job = (select job from emp wehre ename = 'Ruby'); 
    SQL> select ename, job from emp where job = (select job from emp where ename = 'Ruby');
未选定行
  • 如果子查询返回到的是一个空值，那么主查询将不会查询到任何结果。

    d、多行子查询的空值问题

  • 示例：select * from emp where empno not in (select mgr from emp); 
    SQL> select mgr from emp;
             MGR
            7566
            null
            ......
            7839
            7566
            7698
            已选择15行。
 SQL> select * from emp where empno not in (select mgr from emp);
未选定行
  • 多行子查询中如果出现空值，则主查询将不会查询到任何结果。 
    SQL> select * from emp where empno not in (select mgr from emp where mgr is not null);
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
                30
            7951 EASON      ANALYST         7566 01-12月-17           3000
                20
         ......
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
            7900 JAMES      CLERK           7698 03-12月-81            950
                30
已选择9行。

本文标题：Oracle专题8之Oracle的子查询
文章网址：http://chengdu.cdxwcx.cn/article/ghjjsd.html

其他资讯