Oracle培训笔记20060412

WHERE条件中的子查询
 IN的多列比较
WHERE（A1，A2，....An） IN （SELECT C1，C2，...Cn FROM...）
练习1：SALARY表中有职工在各个月份的工资
QX_INFO表中有在各个月中缺勤的情况的职工信息
如果职工有缺勤，则扣除当月工资的10%，请查询哪些职工需要扣钱
select empno,sal*0.1
from salary
where (empno,ym) in (select empno,ym from qx_info);
in比较多列时，最好放在一列上进行比较
select empno,sal*0.1
from salary
where empno in (select empno from qx_info)
and ym in (select ym from qx_info);
像上面这种查询将列出三行记录

相关子查询：在子查询中引用主查询的列值
例如：查询谁的工资是其所在部门的工资最高的
select * from emp A
where sal=(select max(sal) from emp B
       where B.deptno=A.deptno);

练习二：查询谁的工资高于其所在部门的平均工资
SQL> select *
  2  from emp A
  3  where sal>
  4  (select avg(sal)
  5  from emp B
  6  where A.deptno=B.deptno);

练习：查询各个时间段的产量：rongji表
RTIM       LJRL
---- ----------
0901        800
1002       1500
1059       2700
1200       4100
1400       6000

以前：select B.ljrl-A.ljrl
from rongji A,rongji B
where B.rtim=A.rtim+100;
现在：时间相邻：小的时间是比大的时间小的里面最大的
select B.ljrl-A.ljrl
from rongji A,rongji B
where A.rtime=(select max(rtime) from rongji C
 where c.rtime<B.rtime);

SELECT A.RTIME||'->'||B.RTIME PERIOD,B.LJRL-A.LJRL
    FROM RONGJI A, RONGJI B
   WHERE A.RTIME=(SELECT MAX(RTIME) FROM RONGJI C
                   WHERE C.RTIME<B.RTIME)
倒空储油罐（装满后拉走）
select A.RTIME||'->'||B.RTIME,
 case when A.rid=B.rid then B.ljrl-A.ljrl
 else B.ljrl
 end
from rongji A,rongji B
where A.rtime=(select max(rtime) from rongji C
 where c.rtime<B.rtime);

SELECT-LIST中的子查询：用括号写，返回单行单列的记录
select ename,(select Dname from dept wheredeptno=emp.deptno) from emp;

应用上面的子查询，查询每个部门的编号，名称和人数。
SQL> select deptno,(select dname from dept where deptno=emp.deptno)
  2  ,count(deptno)
  3  from emp
  4  group by deptno;

    DEPTNO (SELECTDNAMEFR COUNT(DEPTNO)
---------- -------------- -------------
        10 ACCOUNTING                 3
        20 RESEARCH                   5
        30 SALES                      6

SQL> select deptno,(select dname from dept where deptno=emp.deptno(+)),count(deptno)
  2  from emp
  3  group by deptno;
ERROR 位于第 1 行:
ORA-01705: 无法在关联列中指定外部连接

SQL> select deptno,dname,(select count(*) from emp      2 where emp.deptno=dept.deptno) RS
  3  from dept;
    DEPTNO DNAME                  RS
---------- -------------- ----------
        10 ACCOUNTING              3
        20 RESEARCH                5
        30 SALES                   6
        40 OPERATIONS              0
练习：查询比其所在部门平均工资高的职工的编号，姓名，工资以及高出多少钱？
SELECT EMPNO,ENAME,SAL,
         SAL-(SELECT AVG(SAL) FROM EMP B
               WHERE A.DEPTNO=B.DEPTNO)
   FROM EMP A
   WHERE SAL >(SELECT AVG(SAL) FROM EMP C
                 WHERE A.DEPTNO=C.DEPTNO);
练习：查询比其所在部门平均工资高的职工的编号，姓名，工资，所在部门平均工资，以及高出多少钱？
SQL> SELECT EMPNO,ENAME,SAL,(select avg(sal) from emp B where A.deptno=B.deptno)
 avg,SAL-(SELECT AVG(SAL) FROM EMP B WHERE A.DEPTNO=B.DEPTNO) differ
  2  FROM EMP A
  3     WHERE SAL >(SELECT AVG(SAL) FROM EMP C
  4                   WHERE A.DEPTNO=C.DEPTNO);
虽然(SELECT AVG(SAL) FROM EMP C
        WHERE A.DEPTNO=C.DEPTNO)
是同样的相关子查询，但是要各自执行一次，一但数据庞大，则效率会降低很大。
FROM子句中的子查询：
要求：1与WHERE条件子查询的要求相同
      2如果子查询中的表达式或者函数被主查询引用，则一定要先起个别名
select * from (select ename,sal from emp);
from后面的部分相当于一个虚拟表，不存在列

select income*1.1
from (select ename,sal+nvl(comm,0) income from emp);

select sal+nvl(comm,0)*1.1
from (select ename,sal+nvl(comm,0) income from emp);
上面两个查询中，第一个可以正确执行而第二个不可以，必须要起别名；

select empno,ename,sal,sal-Asal
 from emp A,(select deptno,avg(sal) Asal
 from emp
 group by deptno) B
where A.deptno=B.deptno
 and A.sal>b.Asal;
练习3:查询各个部门工资最高的职员的
       编号,姓名,工资,
       工资与所在部门平均工资的差
       工资与所在部门最低工资的差
select empno,ename,sal,sal-Asal,sal-Msal
 from emp A,(select
   deptno,avg(sal) Asal,
   min(sal) Msal,
   max(sal) MMsal
   from emp
 group by deptno) B
where A.deptno=B.deptno
 and A.sal=B.MMsal;
     EMPNO ENAME             SAL   SAL-ASAL   SAL-MSAL
---------- ---------- ---------- ---------- ----------
      7839 KING             5000 2083.33333       3700
      7788 SCOTT            3000        825       2200
      7902 FORD             3000        825       2200
      7698 BLAKE            2850 1283.33333       1900 

HAVING中的子查询：
 要求：与WHERE条件中的子查询的要求一致。
       子查询如果引用主查询的列，那么这个列必须是group by中的一个分组项
练习：查询哪个部门人数最多？
SQL> select deptno,count(*)
  2     from emp
  3     group by deptno
  4     having count(*)=(select max(count(*)) from emp group by deptno);

    DEPTNO   COUNT(*)
---------- ----------
        30          6
或者：
SQL> select deptno,count(*)
  2     from emp
  3     group by deptno
  4     having count(*)>=all(select count(*) from emp group by deptno);
练习：查询哪个部门人数最少？
SQL> select A.deptno,count(A.empno)
  2     from emp A,dept B
  3     where A.deptno(+)=B.deptno
  4     group by A.deptno
  5     having count(A.empno)=(select min(count(A.empno))
  6     from emp A,dept B
  7     where A.deptno(+)=B.deptno
  8             group by B.deptno);

    DEPTNO COUNT(A.EMPNO)
---------- --------------
                        0

SQL> select B.deptno,B.dname,count(A.empno)
  2     from emp A,dept B
  3     where A.deptno(+)=B.deptno
  4     group by B.deptno,B.dname
  5     having count(A.empno)=(select min(count(A.empno))
  6     from emp A,dept B
  7     where A.deptno(+)=B.deptno
  8             group by B.deptno);

    DEPTNO DNAME          COUNT(A.EMPNO)
---------- -------------- --------------
        40 OPERATIONS                  0
 
  练习2, 查询哪个部门平均工资最高?

   SELECT DEPTNO,AVG(SAL)
     FROM EMP
    GROUP BY DEPTNO
   HAVING AVG(SAL) >= ALL(SELECT AVG(nvl(SAL,0))
                            FROM EMP
                           GROUP BY DEPTNO)

   SELECT DEPTNO,AVG(SAL)
     FROM EMP
    GROUP BY DEPTNO
   HAVING AVG(SAL) = (SELECT MAX(AVG(SAL)) FROM EMP
                       GROUP BY DEPTNO);
练习3，假设RONGJI表中记录了
        某炼油厂的某个油罐
    在各个时间点(rtime)所测量到的
    容量(LJRL)
 
 请查询哪个时间段（相邻两个时间点之间）
  的产量最大?

SELECT *
  FROM (SELECT A.RTIME||'->'||B.RTIME PERIOD,
          CASE WHEN A.RID=B.RID THEN B.LJRL-A.LJRL
               ELSE B.LJRL
          END CL
   FROM RONGJI A, RONGJI B
   WHERE A.RTIME=(SELECT MAX(RTIME) FROM RONGJI C
        WHERE C.RTIME<B.RTIME))
WHERE CL=(SELECT MAX(CL)
            FROM (SELECT A.RTIME||'->'||B.RTIME PERIOD,
          CASE WHEN A.RID=B.RID THEN B.LJRL-A.LJRL
               ELSE B.LJRL
          END CL
      FROM RONGJI A, RONGJI B
        WHERE A.RTIME=(SELECT MAX(RTIME) FROM RONGJI C
                        WHERE C.RTIME<B.RTIME)));

********************************************************
 WITH语句：用来给from子句中出现的子查询起别名
 WITH A AS (SELECT ....)
 SELECT * FROM A;
  相当于
 SELECT * FROM (SELECT ...)

练习3,查询比其所在部门平均工资高的职工的
       编号,姓名,工资,所在部门平均工资
       以及高出所在部门的平均工资多少钱?           

  WITH B AS(SELECT DEPTNO,AVG(SAL) ASAL
                   FROM EMP
                 GROUP BY DEPTNO)
   SELECT EMPNO,ENAME,SAL,SAL-ASAL
     FROM EMP A,B
    WHERE A.DEPTNO=B.DEPTNO
      AND A.SAL>B.ASAL 

改写练习:假设RONGJI表中记录了
    某炼油厂的某个油罐
    在各个时间点(rtime)所测量到的
    容量(LJRL)
    请查询哪个时间段（相邻两个时间点之间）
    的产量最大?
with c as (SELECT A.RTIME||'->'||B.RTIME PERIOD,
          CASE WHEN A.RID=B.RID THEN B.LJRL-A.LJRL
               ELSE B.LJRL
          END CL
   FROM RONGJI A, RONGJI B
   WHERE A.RTIME=(SELECT MAX(RTIME) FROM RONGJI C
        WHERE C.RTIME<B.RTIME))
select * from c
where cl=(select max(cl) from c); 

(select (B.UL-A.UL)/(B.UL-A.UL+B.DL-A.DL)
from bizstat A,bizstat B
where A.GTIME=(select max(gtime) from bizstat C
where C.gtime<B.gtime))
   ************************子查询到此结束了***********************
集合操作：
UNION：并集
UNION ALL：集（不去掉重复的列）
INTERSECT：
MINUS
要求做集合操作的两个查询
必须返回相同数目的列
并且列的数据类型必须完全相同
UNION ALL要比UNION查询速度快
UNION要去掉重复的列
UNION ALL不去掉重复的列
练习1,查询各个部门的名称人数
       以及各种职务的名称和人数

  SELECT DNAME,(SELECT COUNT(*) FROM EMP WHERE DEPTNO=DEPT.DEPTNO)
    FROM DEPT
  UNION  ALL
  SELECT JOB,COUNT(*)
    FROM EMP
   GROUP BY JOB;

练习2,查询各个部门的编号,人数
       以及各种职务的名称,人数

  SELECT TO_CHAR(DEPTNO),COUNT(*)
    FROM EMP
   GROUP BY DEPTNO
  UNION ALL
  SELECT JOB ,COUNT(*)
    FROM EMP
   GROUP BY JOB
练习3,查询各个部门的编号,人数,总工资
        以及各种职务的名称,人数,总工资
        以及全体的人数和总工资      

  SELECT TO_CHAR(DEPTNO),COUNT(*),SUM(SAL)
    FROM EMP
   GROUP BY DEPTNO
  UNION ALL
  SELECT JOB ,COUNT(*),SUM(SAL)
    FROM EMP
   GROUP BY JOB
  UNION ALL
  SELECT '总计:',COUNT(*),SUM(SAL)
    FROM EMP;
   ************************DML数据操作语言**************************

    INSERT

    UPDATE

    DELETE

INSERT

    INSERT INTO 表名 VALUES(值的列表)
    INSERT INTO 表名 (列名列表) VALUES(值的列表)
INSERT INTO EMP
   VALUES(1122,'JA',NULL,NULL,NULL,NULL,NULL,NULL)

INSERT INTO EMP (EMPNO,ENAME) VALUES(1122,'JA')

   INSERT INTO 表名 [(列名列表)] (子查询)
 
   练习1,创建一个表EMP_BK,
         列定义和表中数据与EMP表完全相同