资源描述：
《高等数学PPT邱茂路1 (4)》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、AUTHOR:BAICHUNCAIDATE:2003/02/20DALIANHAIHUISCI-TECHOracle数据库培训(三)高级查询表的连接数据汇总连接的功能:从多个表取得数据合成一个结果集合EMPNODEPTNOLOC--------------------783910NEWYORK769830CHICAGO778210NEWYORK756620DALLAS765430CHICAGO749930CHICAGO...14rowsselected.连接EMPDEPTEMPNOENAME...DEPTNO ----
2、-------...------ 7839KING...107698BLAKE...30...7934MILLER...10DEPTNODNAMELOC------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON连接从多个表取得数据合成一个结果集合.在WHERE子句中写连接条件.需要表明列来自于哪个表.SELECTtable1.column,table2.columnFROMtable1,table
3、2WHEREtable1.column1=table2.column2;连接等值连接非等值连接外连接自连接连接的种类：等值连接EMPDEPTEMPNOENAMEDEPTNO--------------------7839KING107698BLAKE307782CLARK107566JONES207654MARTIN307499ALLEN307844TURNER307900JAMES307521WARD307902FORD207369SMITH20...14rowsselected.DEPTNODNAMELOC----
4、---------------------10ACCOUNTINGNEWYORK30SALESCHICAGO10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO20RESEARCHDALLAS20RESEARCHDALLAS...14rowsselected.主键外键等值连接SQL>SELECTemp.empno,emp.ename,emp.deptno, 2de
5、pt.deptno,dept.loc3FROMemp,dept4WHEREemp.deptno=dept.deptno;EMPNOENAMEDEPTNODEPTNOLOC--------------------------------7839KING1010NEWYORK7698BLAKE3030CHICAGO7782CLARK1010NEWYORK7566JONES2020DALLAS...14rowsselected.等值连接举例使用表的别名使用表的别名可以简化查询语句.SQL>SELECTemp.empno,em
6、p.ename,emp.deptno,2dept.deptno,dept.loc3FROMemp,dept4WHEREemp.deptno=dept.deptno;SQL>SELECTe.empno,e.ename,e.deptno,2d.deptno,d.loc3FROMempe,deptd4WHEREe.deptno=d.deptno;非等值连接EMPSALGRADE“EMP表的SAL列的值是在SALGRADE表的LOSAL与HISAL之间”EMPNOENAMESAL-------------------7839K
7、ING50007698BLAKE28507782CLARK24507566JONES29757654MARTIN12507499ALLEN16007844TURNER15007900JAMES950...14rowsselected.GRADELOSALHISAL----------------17001200212011400314012000420013000530019999非等值连接ENAMESALGRADE----------------------------JAMES9501SMITH8001ADAMS1
8、1001...14rowsselected.SQL>SELECTe.ename,e.sal,s.grade2FROMempe,salgrades3WHEREe.sal4BETWEENs.losalANDs.hisal;外连接EMPDEPT在40OPERATIONS部门中没有职员ENAMEDEPTNO ----------- KIN
