oracle9i学习笔记之十六 高级子查询

 1.子查询回顾
 
  SELECT   select_list
  FROM     table
  WHERE    expr operator(SELECT  select_list
                         FROM    table);

例1：查询emp表，显示薪水大于平均薪水的雇员信息

     SELECT *
     FROM   emp
     WHERE  sal>(SELECT AVG(sal)
                 FROM   emp);

例2：显示雇员的细节信息，要求这些雇员的经理和部门与雇员号为7521或7698是相同的，但不包括empno为7521或7698的雇员信息
   
   SELECT *
   FROM   emp
   WHERE  (mgr,deptno) IN(SELECT mgr,deptno
                          FROM   emp
                          WHERE  empno IN(7521,7698))
   AND    empno NOT IN(7521,7698);

例3：显示与薪水大于2900元的雇员相同部门、相同工作类型的其他雇员信息
     
     SELECT *
     FROM   emp
     WHERE  (deptno,job) IN(SELECT deptno,job
                            FROM   emp
                            WHERE  sal>2900)
     AND    sal<=2900;

例4：显示与薪水大于2900元的雇员相同部门、相同工作类型的其他雇员信息(在FROM子句中使用子查询)

    SELECT *
    FROM   emp e,(SELECT deptno,job,sal
                  FROM   emp
                  WHERE  sal>2900) s
    WHERE e.deptno=s.deptno
    AND   e.job=s.job
    AND   e.sal!=s.sal;

2.相关子查询
1)相关子查询是一种读表中每一行并且依靠相关数据比较每行的值的方法，相关子查询被用于row-by-row处理。对外查询的每一行，每个子查询被执行一次。
2)嵌套子查询与相关子查询
  (1)嵌套子查询的执行
     -内查询首先执行并且查找值
     -外查询用内查询的值执行一次
  (2)相关子查询的执行
     -用外查询取得候选行
     -用候选行的值执行内查询
     -用来自内查询的值确认或取消候选行
     -重复直到无剩余的候选行  

   SELECT column1,column2,...
   FROM   table1 outer
   WHERE  column1 operator(SELECT column1,column2
                           FROM   table2
                           WHERE  expr1=outer.expr2)                    

例：找出所有的雇员，他们挣的薪水高于该部门的平均薪水

    SELECT ename,deptno,sal
    FROM   emp outer
    WHERE  sal>(SELECT AVG(sal)
                FROM   emp
                WHERE  deptno=outer.deptno);
结果：
      ENAME  DEPTNO  SAL 
      ALLEN    30    1600 
      JONES    20    2975 
      BLAKE    30    2850 
      SCOTT    20    3000 
      KING     10    5000 
      FORD     20    3000 
      小王     10    3000 

3.EXISTS操作
  EXISTS操作对在子查询的结果集中存在的行进行检验：
  1)如果一个子查询行值被找到：
    -在内查询中的搜索不再继续
    -条件被标记为TRUE
  2)如果一个子查询行值未找到
    -条件被标记为FALSE
    -在内查询中的搜索继续

例：查找至少有一个雇员的经理信息
   
    SELECT *
    FROM   emp outer
    WHERE  EXISTS(SELECT 'X'
                  FROM   emp
                  WHERE  mgr=outer.empno);
结果：
     EMPNO  ENAME    JOB     MGR  HIREDATE      SAL  COMM  DEPTNO 
     7566   JONES  MANAGER   7839  02-4月 -81  2975         20 
     7698   BLAKE  MANAGER   7839  01-5月 -81  2850         30 
     7782   CLARK  MANAGER   7839  09-6月 -81  2450         10  
     7788   SCOTT  ANALYST   7566  19-4月 -87  3000         20 
     7839   KING   PRESIDENT       17-11月-81  5000         10 
     7902   FORD   ANALYST   7566  03-12月-81  3000         20 

4.NOT EXISTS操作
例：查找部门编号不在雇员表中的部门信息

   SELECT *
   FROM   dept
   WHERE  NOT EXISTS(SELECT *
                     FROM   emp
                     WHERE  emp.deptno=dept.deptno);
  

5.相关UPDATE
  -用一个相关子查询来更新一个表中的行，该表中的行基于另一个表中的行

  UPDATE table1 alias1
  SET    column=(SELECT expression
                 FROM   table2 alias2
                 WHERE  alias1.column=alias2.column);

例:1.在雇员表中增加部门名称列
  
     ALTER TABLE emp
     ADD   (dname VARCHAR2(14));

   2.使用部门表中的部门名称更新雇员表中的部门名称

     UPDATE emp e
     SET    dname=(SELECT dname
                   FROM   dept d
                   WHERE  e.deptno=d.deptno);

6.相关delete
  -用一个相关子查询来删除一个表中的行，该表中的行基于另一个表中的行
 
  DELETE FROM table1 alias1
  WHERE  column operator(SELECT expression
                         FROM   table2 alias2
                         WHERE  alias1.column=alias2.column);

例：用一个相关子查询删除emp表中的数据，被删除的数据是emp表和job_history表中有相同empno列值的数据
   
    DELETE FROM emp e
    WHERE  empno IN (SELECT empno
                     FROM   job_history
                     WHERE  empno=e.empno);

7.WITH子句
1)当一个查询块在一个复杂的查询中出现多次时，使用WITH子句，能够用在SELECT语句中使用相同查询块
2)WITH子句取回查询块的结果，并且将它存在用户的临时表空间中
3)WITH子句可以改善性能 

例：用WITH子句，写一个查询来显示部门名称和该部门的合计薪水，那些人的合计薪水高于各部门的平均薪水

   WITH
   dept_costs AS(
       SELECT   d.dname,SUM(e.sal) AS dept_total
       FROM     emp e,dept d
       WHERE    e.deptno=d.deptno
       GROUP BY d.dname),
   avg_costs AS(
       SELECT SUM(dept_total)/COUNT(*) AS dept_avg
       FROM   dept_costs)
   SELECT *
   FROM   dept_costs
   WHERE  dept_total>(SELECT dept_avg
                      FROM   avg_costs)
   ORDER BY dname;
结果：
      DNAME        DEPT_TOTAL 
      ACCOUNTING   11750 
      RESEARCH     14075 

练习
1.显示变换过至少两次工作的雇员的详细情况（emp和job_history）

 

2.写一个子查询显示任何一个其部门号和薪水都与任何挣佣金的雇员的部门号和薪水相匹配的雇员的名字、部门号和薪水

  SELECT ename,deptno,sal
  FROM   emp
  WHERE  (deptno,sal) IN(SELECT deptno,sal
                         FROM   emp
                         WHERE  comm IS NOT NULL);

3.显示任何其薪水和佣金与任何位于loc为CHICAGO的雇员薪水和佣金相匹配的雇员的名字、部门名和薪水
 
  SELECT ename, dname, sal
  FROM emp e, dept d
  WHERE e.deptno = d.deptno
  AND (sal, NVL(comm,0)) IN (SELECT sal, NVL(comm,0)
        FROM emp e, dept d
        WHERE e.deptno = d.deptno
        AND d.loc = 'CHICAGO');

4.创建一个查询来显示所有其薪水和佣金与SCOTT相同的雇员的名字、受雇日期和薪水（不显示SCOTT的信息）
 
  SELECT ename,hiredate,sal
  FROM   emp
  WHERE  (sal,NVL(comm,0)) IN (SELECT sal,NVL(comm,0)
                                 FROM emp
                                WHERE ename='SCOTT')
  AND   ename!='SCOTT';

5.创建一个查询来显示那些所挣薪水高于所有经理（job='MANAGER'）的雇员的名字、工作岗位和薪水。依据薪水从最高到最低排序结果集

  SELECT ename,job,sal
  FROM   emp
  WHERE  sal>ALL (SELECT sal
                  FROM   emp
                  WHERE  job='MANAGER')
  ORDER BY sal DESC;

6.显示那些住在城市名字以N开头的城市的雇员的ID、名字和部门号

  SELECT empno,ename,deptno
  FROM   emp
  WHERE  deptno IN(SELECT deptno
                   FROM   dept
                   WHERE  loc LIKE 'N%');

7.写一个查询来查找所有其薪水多于他所在部门的平均薪水的雇员，显示名字、部门号和部门的平均薪水，按平均薪水排序

  SELECT ename,e.deptno,sal,a.avg
  FROM   emp e,(SELECT   deptno,AVG(sal) avg
                  FROM    emp
                 GROUP BY deptno) a
  WHERE e.deptno=a.deptno
  AND   e.sal>a.avg
  ORDER BY a.avg;

8.写一个查询显示所挣薪水低于他们所在的部门平均薪水的雇员名

  SELECT ename
 FROM emp outer
 WHERE sal< (SELECT AVG(sal)
      FROM emp inner
             WHERE deptno = outer.deptno);
  或：
  SELECT ename
         FROM emp outer
  WHERE outer.sal< (SELECT AVG(inner.sal)
      FROM   emp inner
             WHERE  inner.deptno = outer.deptno);

9.查找所有不是管理人员的雇员（雇员编号不在经理编号列的雇员）

  SELECT *
  FROM   emp
  WHERE  empno NOT IN(SELECT mgr
                      FROM   emp
                      WHERE  mgr IS NOT NULL);
  或：
  SELECT *
  FROM   emp outer
  WHERE  NOT EXISTS(SELECT 'X'
                    FROM   emp
                    WHERE  mgr=outer.empno);
  或：
  SELECT *
  FROM   emp
  WHERE  empno NOT IN (SELECT NVL(mgr,0)
                       FROM   emp);