Oracle学习笔记7 -- 子查询

上次笔记学习了分组函数，本次笔记学习子查询。

子查询根据返回结果的不同，被分为单行子查询（返回的结果是一列的一个内容），多行子查询（多个列），多列子查询（多条记录）。

子查询的定义：在一个查询的内部还包括另外一个查询，则成为子查询。

语法格式：

SELECT *  |(列名 )

FROM table

WHERE  条件     (

     SELECT  列名         

      FROM  table

      where 条件

);

括号内的查询在主查询执行之前执行完成，查询的结果返回主查询使用。

例：查询工资比雇员号7900工资高的全部雇员信息。

分析：工资比7900雇员工资高的员工，那么是不是要首先知道7900的工资是多少？

SQL> select sal from  emp where empno=7900;
 
         SAL
   ------
   950.00

知道了7900的员工之后，我们可以查询出比7900工资高的所有员工信息了

SQL> select *  from emp  where sal > 950 ;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
12 rows selected

上面我们是分部写的，其实可以合并称为一条语句，即：使用子查询，查询中还有查询。

SQL> select * from emp
  2  where sal > (
  3          select sal from emp where empno = 7900 ) ;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
12 rows selected

我们发现上面两个结果其实还是一样的。注意：所有的子查询必须要写在括号内--( 子查询语句)，子查询放在比较条件的右侧 ;

 

子查询中可以使用组函数：

例：求工资最低的员工信息

SQL> select * from  emp where sal > (select min(sal)  from emp );
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 
13 rows selected

 

例：查询出：部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名

selectd.dname ,ed.c ,ed.a ,e.ename

from  dept   d ,(select  deptno , count(empno) c , avg(sal) a ,min(sal) min

from  emp    groupbydeptno ) ed ,empe

whered.deptno = ed.deptno  ande.sal = ed.min

 

DNAME               C            A        ENAME

---------      ----------  ---------     ----------

RESEARCH        5            2175          SMITH

SALES           6          1566.66666      JAMES

ACCOUNTING      3          2916.66666     MILLER

上面使用到了两张表，emp 和dept ，从emp表中查询出了部门号，部门员工数，平均工资，最低收入，以此作为条件，进行两个表的联立。

多行子查询

in：一个指定的范围

any：与返回的任何一个值作比较

all：与返回的所有值做比较

 例：查询每个部门最低工资员工的信息。

SQL> select * from emp
  2  where sal in(
  3  select min(sal) from emp group by deptno);
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

ANY：与IN操作符功能完全一样

select * from emp
where sal = any(
select min(sal) from emp group by deptno)

查询结果也与上面相同。

 >ANY :比里面最小的都要大 ；<ANY 比里面最大的都要小。

ALL： >ALL  ,比最大的值都要大，<ALL ,比最小的都要小。

select * from emp
where sal > all(
select min(sal) from emp group by deptno)

 EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 
8 rows selecte