五、从多个表中显示数据

从多个表中显示数据-Itpub技术培训Eygle2003-06目的使用相等和不等连结写SELET语句来访问多个表使用外连接查询数据表的自连接EMPNODEPTNOLOC--------------------783910NEWYORK769830CHICAGO778210NEWYORK756620DALLAS765430CHICAGO749930CHICAGO...14rowsselected.从多个表中获得数据EMPDEPTEMPNOENAME...DEPTNO-----------...------7839KING...107698BLAKE...30...7934MILLER...10DEPTNODNAMELOC------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON什么是连接使用连接从多个表中查询数据在WHERE子句中写连接条件在多个表中具有相同的列名SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column1=table2.column2;笛卡尔结果笛卡尔结果形成于:•连接条件被省略•连接条件无效•第一个表的所有记录连接到第二个表的所有记录为了避免笛卡尔结果我们总是在WHERET子句中使用有效连接生成笛卡尔结果ENAMEDNAME----------------KINGACCOUNTINGBLAKEACCOUNTING...KINGRESEARCHBLAKERESEARCH...56rowsselected.EMP(14rows)DEPT(4rows)EMPNOENAME...DEPTNO-----------...------7839KING...107698BLAKE...30...7934MILLER...10DEPTNODNAMELOC------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON笛卡尔结果:14*4=56条记录连接的类型EquijoinNon-equijoinOuterjoinSelfjoin什么是等值连接EMPDEPTEMPNOENAMEDEPTNO--------------------7839KING107698BLAKE307782CLARK107566JONES207654MARTIN307499ALLEN307844TURNER307900JAMES307521WARD307902FORD207369SMITH20...14rowsselected.DEPTNODNAMELOC-------------------------10ACCOUNTINGNEWYORK30SALESCHICAGO10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO20RESEARCHDALLAS20RESEARCHDALLAS...14rowsselected.主键外键使用等值连接所获得的记录SQLSELECTemp.empno,emp.ename,emp.deptno,2dept.deptno,dept.loc3FROMemp,dept4WHEREemp.deptno=dept.deptno;EMPNOENAMEDEPTNODEPTNOLOC--------------------------------7839KING1010NEWYORK7698BLAKE3030CHICAGO7782CLARK1010NEWYORK7566JONES2020DALLAS...14rowsselected.限定列名使用表名作为前缀在多个表中指定列名使用表前缀改进性能使用列的别名以区分不同表的同名列使用AND运算符附加查询条件EMPDEPTEMPNOENAMEDEPTNO--------------------7839KING107698BLAKE307782CLARK107566JONES207654MARTIN307499ALLEN307844TURNER307900JAMES307521WARD307902FORD207369SMITH20...14rowsselected.DEPTNODNAMELOC-----------------------10ACCOUNTINGNEWYORK30SALESCHICAGO10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO20RESEARCHDALLAS20RESEARCHDALLAS...14rowsselected.使用表的别名使用别名简化查询SQLSELECTemp.empno,emp.ename,emp.deptno,2dept.deptno,dept.loc3FROMemp,dept4WHEREemp.deptno=dept.deptno;SQLSELECTe.empno,e.ename,e.deptno,2d.deptno,d.loc3FROMempe,deptd4WHEREe.deptno=d.deptno;多表联接NAMECUSTID-----------------JOCKSPORTS100TKBSPORTSHOP101VOLLYRITE102JUSTTENNIS103K+TSPORTS105SHAPEUP106WOMENSSPORTS107......9rowsselected.CUSTOMERCUSTIDORDID--------------101610102611104612106601102602106604106605...21rowsselected.ORDORDIDITEMID-------------61036111612160116021...64rowsselected.ITEM非等值连接EMPSALGRADEEMP表中的Salary列值在SALGRADE表的低salary和高salary之间EMPNOENAMESAL-------------------7839KING50007698BLAKE28507782CLARK24507566JONES29757654MARTIN12507499ALLEN16007844TURNER15007900JAMES950...14rowsselected.GRADELOSALHISAL----------------17001200212011400314012000420013000530019999使用非等值连接获取记录ENAMESALGRADE----------------------------JAMES9501SMITH8001ADAMS11001...14rowsselected.SQLSELECTe.ename,e.sal,s.grade2FROMempe,salgrades3WHEREe.sal4BETWEENs.losalANDs.hisal;外连接EMPDEPT在OPERATIONS部门没有员工ENAMEDEPTNO-----------KING10BLAKE30CLARK10JONES20...DEPTNODNAME----------------10ACCOUNTING30SALES10ACCOUNTING20RESEARCH...40OPERATIONS外连接使用外连接看到不满足连接条件的记录外连接运算符是加号(+)SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column=table2.column(+);使用外连接SQLSELECTe.ename,d.deptno,d.dname2FROMempe,deptd3WHEREe.deptno(+)=d.deptno4ORDERBYe.deptno;ENAMEDEPTNODNAME--------------------------------KING10ACCOUNTINGCLARK10ACCOUNTING...40OPERATIONS15rowsselected.自连接EMP(WORKER)EMP(MANAGER)在WORKER表中的MGR等于MANAGER表中的EMPNOEMPNOENAMEMGR---------------7839KING7698BLAKE78397782CLARK78397566JONES78397654MARTIN76987499ALLEN7698EMPNOENAME-------------7839KING7839KING7839KING7698BLAKE7698BLAKE表的自连接WORKER.ENAME||'WORKSFOR'||MANAG-------------------------------BLAKEworksforKINGCLARKworksforKINGJONESworksforKINGMARTINworksforBLAKE...13rowsselected.SQLSELECTworker.ename||'worksfor'||manager.ename2FROMempworker,empmanager3WHEREworker.mgr=manager.empno;小结EquijoinNon-equijoinOuterjoinSelfjoinSELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column1=table2.column2;支持SQL1999包括以下新的TABLEJOIN的句法结构CROSSIN——它在两个表格中创建了一个卡迪尔列，就象是在Oracle8i中没写WHERE时那样。NATURALJOIN——这是一个很有用的Oracle9i的句法，它通过从WHERE子句中移动连接标准来改善SQL的稳定性USING子句——它可以通过名字来具体指定连接-ON子句——这个句法允许在两个表格中为连接具体指定栏目的名头LEFTOUTERJOIN——它返回表格中左边的行和右边的数值，如果没有搭配的行的话，则返回零RIGHTOUTERJOIN——它返回表格中右边的行和左边的数值，如果没有搭配的行的话，则返回零FULLOUTERJOIN——它返回的是两个表格中所有的行，用零填满每一个空格。这在Oracle8i中则没有相应的此种句法SQL1999大多数改进都是为了让非Oracle的请求可更快速的进入Oracle数据库，并且必须记住这些只是句法上的不同，ISO99标准并没有给Oracle9iSQL带来任何新的功能。对于异构数据库的迁移，这可能提供了更多的方便之处!CROSSJOINCROSSJOIN产生了一个迪卡尔积（Cartesianproduct），就象是在连接两个表格时忘记加入一个WHERE子句一样selectlast_name,dept_idfromemp,depts;在Oracle9i中，我们使用CROSSJOIN来达到相同的结果SQLselectename,dname2fromemp3crossjoindept;NATURALJOINNATURALJOIN能够通过在两个