oracle学习笔记之四 多表查询

1.从多个表中获取数据 SELECT table1.column,table2.column FROM table,table2 WHERE table1.column1=table2.column2; -在WHERE子句中书写连接条件 -如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀 -N个表相连时，至少需要N-1个连接条件 2.笛卡尔积 笛卡尔积在下列情况产生： -连接条件被省略 -连接条件是无效的 -第一个表中的所有行和第二个表中的所有行都发生连接 3.连接类型 1)等值连接 -在用到多个表时用表名作前缀来限定列 -通过使用表前缀可以提高性能 -通过表名.列名的形似来区分来自不同表但名字相同的列 -通过使用表的别名来简化查询语句 例1： SELECT emp.empno,emp.ename,emp.deptno,dept.deptno,dept.loc FROM emp,dept WHERE emp.deptno=dept.deptno; 例2： SELECT e.empno,e.ename,e.deptno,d.deptno,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno; 2)非等值连接 例： SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; 3)外部连接 -外部连接条件不能使用IN运算符，也不能使用OR运算符与其他条件相连 例： SELECT e.ename,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno(+)=d.deptno ORDER BY e.deptno; 4)自身连接 例： SELECT worker.ename||' works for '||manager.ename FROM emp worker,emp manager WHERE worker.mgr=manager.empno; 4.SQL:1999语法的连接 SELECT table1.column,table2.column FROM table1 [CROSS JOIN table2]| [NATURAL JOIN table2]| [JOIN table2 USING(column_name)]| [JOIN table2 ON (table1.column_name=table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (talble1.column_name=table2.column_name)]; 1)创建交叉连接 CROSS JOIN子句会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的 例: SELECT emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc FROM emp CROSS JOIN dept; 2)自然连接 -自然连接子句是基于两个表存在相同名字的列 -返回两个表相匹配列中具有相同值的记录 -如果相同名称的列的数据类型不同，则会产生错误 例1： SELECT empno,ename,sal,deptno FROM emp NATURAL JOIN dept; 结果： EMPNO ENAME SAL DEPTNO LOC 7369 SMITH 800 20 DALLAS 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7566 JONES 2975 20 DALLAS 7654 MARTIN 1250 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7782 CLARK 2450 10 NEW YORK ... 例2： SELECT empno,ename,sal,deptno,loc FROM emp NATURAL JOIN dept WHERE deptno IN (10,20); 结果： EMPNO ENAME SAL DEPTNO LOC 7369 SMITH 800 20 DALLAS 7566 JONES 2975 20 DALLAS 7788 SCOTT 3000 20 DALLAS 7876 ADAMS 1100 20 DALLAS 7902 FORD 3000 20 DALLAS 7782 CLARK 2450 10 NEW YORK 7839 KING 5000 10 NEW YORK 7934 MILLER 1300 10 NEW YORK 3)使用USING子句创建连接 -USING子句可以用于指定产生连接的列 例: SELECT e.ename,e.sal,deptno,d.loc FROM emp e JOIN dept d USING (deptno) WHERE deptno=20; 结果： ENAME SAL DEPTNO LOC SMITH 800 20 DALLAS JONES 2975 20 DALLAS SCOTT 3000 20 DALLAS ADAMS 1100 20 DALLAS FORD 3000 20 DALLAS ... 注： -如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句替换，以指定产生等值连接的列。 -如果有多个列匹配的情况，使用USING子句只能指定其中的一列 -USING子句中用到的列不能使用表名和列名作为前缀 -NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用 4)使用ON子句创建连接 -自然连接条件基本上是具有相同列名的表之间的等值连接，要指定任意连接条件，或指定要连接的列，可以使用ON子句 -用ON将连接条件和其他检索条件分隔开 -ON子句可以提高代码的可读性 例1： SELECT e.empno,e.ename,e.deptno,d.deptno,d.loc FROM emp e JOIN dept d ON (e.deptno=d.deptno); 结果： EMPNO ENAME DEPTNO DEPTNO LOC 7369 SMITH 20 20 DALLAS 7499 ALLEN 30 30 CHICAGO 7521 WARD 30 30 CHICAGO 7566 JONES 20 20 DALLAS ... 例2： SELECT e.empno,e.ename,d.loc,m.ename FROM emp e JOIN dept d ON e.deptno=d.deptno JOIN emp m ON e.mgr=m.empno; 结果： EMPNO ENAME LOC ENAME 7369 SMITH DALLAS FORD 7499 ALLEN CHICAGO BLAKE 7521 WARD CHICAGO BLAKE 7566 JONES DALLAS KING 7654 MARTIN CHICAGO BLAKE 7698 BLAKE CHICAGO KING 7782 CLARK NEW YORK KING 7788 SCOTT DALLAS JONES 7844 TURNER CHICAGO BLAKE 7876 ADAMS DALLAS SCOTT 7900 JAMES CHICAGO BLAKE 7902 FORD DALLAS JONES 7934 MILLER NEW YORK CLARK 5)左外连接 例: SELECT e.ename,e.deptno,d.loc FROM emp e LEFT OUTER JOIN dept d ON (e.deptno=d.deptno); 结果： ENAME DEPTNO LOC MILLER 10 NEW YORK KING 10 NEW YORK CLARK 10 NEW YORK FORD 20 DALLAS ... WARD 6)右外连接 例： SELECT e.ename,e.deptno,d.loc FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno=d.deptno); 结果： ENAME DEPTNO LOC SMITH 20 DALLAS ALLEN 30 CHICAGO WARD 30 CHICAGO JONES 20 DALLAS ... BOSTON 7)全外连接 例： SELECT e.ename,e.deptno,d.loc FROM emp e FULL OUTER JOIN dept d ON (e.deptno=d.deptno); 结果： ENAME DEPTNO LOC MILLER 10 NEW YORK KING 10 NEW YORK CLARK 10 NEW YORK FORD 20 DALLAS ... WARD BOSTON 练习 1.查询emp和dept表，显示所有符合ename列的第一个字母为"S"的empno、ename、deptno、dname列的信息 SELECT empno,ename,d.deptno,dname FROM emp e,dept d WHERE e.deptno=d.deptno AND ename LIKE 'S%'; 或： SELECT emp.empno,emp.ename,emp.deptno,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno AND SUBSTR(emp.ename,1,1)='S'; 2.查询emp、salgrade和dept表，显示雇员名、部门名、薪水以及薪水的等级 SELECT e.ename,d.deptno,e.sal,s.grade FROM emp e,dept d,salgrade s WHERE e.deptno=d.deptno AND (e.sal BETWEEN s.losal AND s.hisal); 3.显示工作在NEW YORK的所有雇员信息 SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno AND d.loc='NEW YORK'; 4.查询emp表，显示在雇员'SCOTT'之前来公司工作的雇员名和雇佣的时间信息 SELECT a.ename,a.hiredate,b.ename,b.hiredate FROM emp a,emp b WHERE a.hiredate