Oracle培训笔记20060410

内连接：INNER JOIN符合条件的被连接
外连接：OUTER JOIN
 特例：自连接，将同一表的不同行进行连接
eg.查询每个人的直接领导的名字：MGR列是每个人的领导的名字
Sselect A.ename,B.ename
from emp A,emp B
where A.mgr=B.empno;

练习：查询每个职工的名字和他所领导的职工的名字
 select A.ename,B.ename
 from emp A,emp B
 where B.mgr=A.empno
 order by A.ename;
假设RONGJI表中记录了某炼油厂某个油罐在各个时间点（RTIM）的所测量到的容量（LJRL），请查询每个时间段（相邻两个时间点）
SQL> desc rongji
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 RTIME                                              VARCHAR2(4)
 LJRL                                               NUMBER(4)
SQL> SELECT * FROM RONGJI;
RTIM       LJRL
---- ----------
0900        800
1000       1500
1100       2700
1200       4100

select B.ljrl-A.lrjl
from rongji A,rongji B
where B.rtim=A.rtim+100;

练习三：查询各个部门的编号，名称和人数，总工资
SQL> select A.deptno,B.dname,count(*),sum(sal)
  2  from emp A,dept B
  3  where A.deptno=B.deptno
  4  group by A.deptno,B.dname;
实际上有四个部门，但是只统计出三个部门，由于一个部门没有人，所以需要外连接来
解决

练习：查询每个职工的编号以及该职工直接下属的人数。
SQL> select A.empno,count(*)
  2  from emp A,emp B
  3  where B.mgr=A.empno
  4  group by A.empno;
     EMPNO   COUNT(*)
---------- ----------
      7566          2
      7698          5
      7782          1
      7788          1
      7839          3
      7902          1
有些职工没有下属，则列不出来，需要外连接来解决。

外连接：两个表的连接M*N行，有的部门没有列出来
专门用于没有任何行与其连接的
用一个空白行与其连接
外连接的实现：看哪一个表需要被外连接，DEPT表中有一行没有数据与起
连接，在WHERE条件中，除了被外连接的表之外，其他的列都要加外连接符号（+）
select B.deptno,B.dname,A.empno,A.ename
from emp A,dept B
where A.deptno(+)=B.deptno
order by B.deptno;
练习：查询每个职工的编号，姓名以及其直接下属的编号，姓名，如果他没有下属，则只显示该职工的编号，姓名就可以了
select A.empno,A.ename,B.empno,B.ename
from emp A,emp B
where A.empno=B.mgr(+)；
A表需要外连接，所以除A表的数据之外，其他的都需要加外连接符号

ALL_USERS有所有帐户的姓名（username）与创建时间（created）
DBA_TABLES有所有表的名字(table_name)和拥有者的名字(owner).
查询四天前创建的帐户中，如果帐户内有名字是STUDENTS的表，则显示帐户名和表明，如果帐户内没有STUDENTS表，则只显示帐户名
SQL> select A.username,B.table_name
  2  from all_users A,dba_tables B
  3  where A.username=B.owner(+) and B.table_name(+)='STUDENTS'   and trunc(A.created)=trunc(sysdate)-4;
练习：查询所有部门的编号，名称和人数，如果部门内没有人，则显示人数为0（//count()函数具有过滤空行的功能）
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;

    DEPTNO DNAME          COUNT(A.EMPNO)
---------- -------------- --------------
        10 ACCOUNTING                  3
        20 RESEARCH                    5
        30 SALES                       6
        40 OPERATIONS                  0

练习：查询每个职工的编号，姓名以及他们下属的人数。
SQL> select A.empno,A.ename,count(B.empno)
  2  from emp A,emp B
  3  where A.empno=B.mgr(+)
  4  group by A.empno,A.ename;

如果一个人没有部门，如果一个部门没有人
SELECT B.DEPTNO,B.DNAME,A.EMPNO,A.ENAME
FRPM EMP A,DEPT B
WHERE A.DEPTNO(+)=B.DEPTNO;

SELECT B.DEPTNO,B.DNAME,A.EMPNO,A.ENAME
FRPM EMP A,DEPT B
WHERE A.DEPTNO=B.DEPTNO(+);
SELECT B.DEPTNO,B.DNAME,A.EMPNO,A.ENAME
FRPM EMP A,DEPT B
WHERE A.DEPTNO(+)=B.DEPTNO(+);
//不能执行：一个谓词只能引用一个外连接
SQL-1999标准：ORACLE9.0后开始实施。

（1）CROSS JOIN：将A，B两个表的所有的连接组合都列出来
 SELECT ......
 FROM A......
 WHRE.........

（2）QUALIFIED JOIN：带限制的连接
 SELECT ....
 FROM A JOIN B USING（列名列表）
使用A，B两个表中的所有同名的列或者其中的一部分，作为等值连接的条件
T1（C1，C2，M）  T2（C1，C2，K）
SELECT .....
FROM T1 JOIN T2 USING（C1，C2）

SELECT .....
FROM T1 JOIN T2 USING（C1）

SELECT ....
FROM T1，T2
WHERE T1.C1=T2.C1 AND T1.C2=T2.C2

练习：使用新的连接方法查询每个职员和所在部门的名称
SQL> select A.ename,B.dname
  2  from emp A JOIN dept B using(deptno);
select A.ename,B.dname,B.deptno//出错，不能执行，不能在USING中参数前面加前缀
from emp A JOIN dept B using(deptno);

（3）ON字句
SELECT ....
FROM A JOIN B ON 连接条件
[WHERE .....]
练习：查询哪些职工在NEW YORK工作
SQL>  select A.ename,B.loc
  2   from emp A JOIN dept B on A.deptno=B.deptno
  3  where B.loc='NEW YORK';
练习：查询每个职工的编号，姓名，工资和工资级别
 select A.empno,A.ename,A.sal,B.grade
 from emp A JOIN salgrade B on A.sal between B.losal and B.hisal;
多表连接：
SELECT ...
FROM T1 JOIN T2 ON 连接条件
 JOIN T2 ON 连接条件
 ..............
练习：查询每个职工的编号，姓名，所在部门的名称和工资级别
select A.empno,A.ename,B.dname,C.grade
from emp A JOIN dept B on A.deptno=B.deptno
JOIN salgrade C on A.sal between C.losal and C.hisal;

select A.empno,A.ename,B.dname,C.grade
from emp A JOIN dept B using(deptno)
JOIN salgrade C on A.sal between C.losal and C.hisal;
外连接：
SELECT ...
FROM A [LEFT|RIGHT|FULL] OUTER JOIN B ON....

练习：查询每个职员的名字以及他下属的人数

select A.ename,count(B.empno)
from emp A left outer join emp B on A.empno=B.mgr
group by A.ename;
（5）自然连接：
SELECT .....
FROM A NATURAL [INNER|{LEFT|RIGHT|FULL]OUTER] JOIN B
[WHERE]......
自然连接：用两个表的所有同名列做等值连接条件
SELECT ....
FROM A NATURAL JOIN B
<===>
SELECT ....
FROM A JOIN B USING（所有同名列列表）
练习：使用自然连接查询每个人的名字和所在部门的名字
 select A.ename,B.dname
 from emp A natural join dept B;
练习：查询每个部门的编号，名称以及部门内的人数
select deptno,B.dname,count(A.empno)
from emp A natural right outer join dept B
group by deptno,B.dname;

子查询：（subquery）查询谁的工资比JONES工资高，前提是不知道JONES的工资。
SELECT * FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='JONES');

SELECT-LIST中的子查询
FROM子句中的子查询
WHERE条件中的子查询
HAVING条件中的子查询

WHERE中的子查询：要求：
 （1）必须用括号括起来
 （2）子查询中不能有ORDER BY子句
 （3）放在比较运算符的右边
 （4）单行比较运算符只能跟单行子查询比较
单行子查询：查询返回一行数据的子查询，从语法上不能看出来
单行比较运算符：= < > <> <= >= like,between..and..  is null in
select ...
from emp
where sal=(select sal from emp where deptno=10);
//上面的语句不能执行，=号是单行比较运算符，只能跟单行子查询比较
多行比较运算符：IN
select ...
from emp
where sal IN (select sal from emp where deptno=10);符合任何一个
IN等价于=ANY

ANY：任何一个（只要有一个满足条件就可以）
ALL：全部（全部符合）
上面的两个不能单独使用，必须使用一个单行运算符进行结合使用
如果子查询，没有行返回，遇到ANY总是为假，遇到ALL总是为真
select ...
from emp
where sal > ANY (select sal from emp where deptno=20);

select ...
from emp
where sal > ALL (select sal from emp where deptno=20);

练习1：查询谁的工资最高
 SELECT * FROM EMP
 WHERE SAL=(SELECT MAX(SAL) FROM EMP);
 
 SELECT * FROM EMP
 WHERE SAL>=ALL(SELECT SAL FROM EMP);

练习2：查询哪些职工的工资高于企业的平均工资
 select * from emp
 where sal>(select avg(sal) from emp);
练习3：查询谁在NEW YORK或者DALLAS工作。
 select * from emp
 where deptno in(select deptno from dept where loc in('NEY YORK','DALLAS'));
连接总是会比子查询快，但是优化器会试图将查询语句改为连接的方式。