Oracle课件-03连接与汇总.ppt

AUTHOR BAICHUNCAI DATE 2003 02 20 DALIANHAIHUISCI TECH Oracle数据库培训 三 高级查询表的连接数据汇总 连接的功能 从多个表取得数据合成一个结果集合 EMPNODEPTNOLOC 783910NEWYORK769830CHICAGO778210NEWYORK756620DALLAS765430CHICAGO749930CHICAGO 14rowsselected 连接 EMP DEPT EMPNOENAME DEPTNO 7839KING 107698BLAKE 30 7934MILLER 10 DEPTNODNAMELOC 10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON 连接 从多个表取得数据合成一个结果集合 在WHERE子句中写连接条件 需要表明列来自于哪个表 SELECTtable1 column table2 columnFROMtable1 table2WHEREtable1 column1 table2 column2 连接 等值连接 非等值连接 外连接 自连接 连接的种类 等值连接 EMP DEPT EMPNOENAMEDEPTNO 7839KING107698BLAKE307782CLARK107566JONES207654MARTIN307499ALLEN307844TURNER307900JAMES307521WARD307902FORD207369SMITH20 14rowsselected DEPTNODNAMELOC 10ACCOUNTINGNEWYORK30SALESCHICAGO10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO30SALESCHICAGO20RESEARCHDALLAS20RESEARCHDALLAS 14rowsselected 等值连接 SQL SELECTemp empno emp ename emp deptno 2dept deptno dept loc3FROMemp dept4WHEREemp deptno dept deptno EMPNOENAMEDEPTNODEPTNOLOC 7839KING1010NEWYORK7698BLAKE3030CHICAGO7782CLARK1010NEWYORK7566JONES2020DALLAS 14rowsselected 等值连接举例 使用表的别名 使用表的别名可以简化查询语句 SQL SELECTemp empno emp 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 非等值连接 EMP SALGRADE EMPNOENAMESAL 7839KING50007698BLAKE28507782CLARK24507566JONES29757654MARTIN12507499ALLEN16007844TURNER15007900JAMES950 14rowsselected GRADELOSALHISAL 17001200212011400314012000420013000530019999 非等值连接 ENAMESALGRADE JAMES9501SMITH8001ADAMS11001 14rowsselected SQL SELECTe ename e sal s grade2FROMempe salgrades3WHEREe sal4BETWEENs losalANDs hisal 外连接 EMP DEPT ENAMEDEPTNO KING10BLAKE30CLARK10JONES20 DEPTNODNAME 10ACCOUNTING30SALES10ACCOUNTING20RESEARCH 40OPERATIONS 外连接 使用外连接查看不符合连接条件的记录 外连接的操作符为加号 SELECTtable column table columnFROMtable1 table2WHEREtable1 column table2 column SELECTtable column table columnFROMtable1 table2WHEREtable1 column table2 column 外连接 SQL SELECTe ename d deptno d dname2FROMempe deptd3WHEREe deptno d deptno4ORDERBYe deptno ENAMEDEPTNODNAME KING10ACCOUNTINGCLARK10ACCOUNTING 40OPERATIONS15rowsselected 自连接 EMP WORKER EMP MANAGER EMPNOENAMEMGR 7839KING7698BLAKE78397782CLARK78397566JONES78397654MARTIN76987499ALLEN7698 EMPNOENAME 7839KING7839KING7839KING7698BLAKE7698BLAKE 自连接 WORKER ENAME WORKSFOR MANAG BLAKEworksforKINGCLARKworksforKINGJONESworksforKINGMARTINworksforBLAKE 13rowsselected SQL SELECTworker ename worksfor manager ename2FROMempworker empmanager3WHEREworker mgr manager empno 数据汇总 汇总 汇总函数操作结果集合给出运算结果 EMP 求EMP表中salary列的最大值 DEPTNOSAL 1024501050001013002080020110020300020300020297530160030285030125030950301500301250 MAX SAL 5000 汇总函数 AVG平均值COUNT行数MAX最大值MIN最小值STDDEV标准偏差SUM和 汇总函数 SELECTcolumn group function column FROMtable WHEREcondition ORDERBYcolumn 汇总函数 AVG SAL MAX SAL MIN SAL SUM SAL 1400160012505600 例1 SQL SELECTAVG sal MAX sal 2MIN sal SUM sal 3FROMemp4WHEREjobLIKE SALES 汇总函数 例2 SQL SELECTMIN hiredate MAX hiredate 2FROMemp MIN HIREDMAX HIRED 17 DEC 8012 JAN 83 汇总函数 COUNT 6 SQL SELECTCOUNT 2FROMemp3WHEREdeptno 30 COUNT 取得结果集合行数 汇总函数 COUNT 列 取得结果集合中当列非空的行数 SQL SELECTCOUNT comm 2FROMemp3WHEREdeptno 30 COUNT COMM 4 汇总函数 汇总函数忽略结果集合中的空值 SQL SELECTAVG comm 2FROMemp AVG COMM 550 汇总 EMP 取得每个部门的平均工资值 DEPTNOSAL 1024501050001013002080020110020300020300020297530160030285030125030950301500301250 DEPTNOAVG SAL 102916 6667202175301566 6667 汇总 SELECTcolumn group function column FROMtable WHEREcondition GROUPBYgroup by expression ORDERBYcolumn 使用GROUPBY子句 可以将表中的行分成多个较小的组 汇总 在SELECT的所选列中 如果不出现在汇总函数中 则必须出现在GROUPBY子句中 SQL SELECTdeptno AVG sal 2FROMemp3GROUPBYdeptno DEPTNOAVG SAL 102916 6667202175301566 6667 汇总 TheGROUPBY子句的列不必出现在SELECT的选择列表中 SQL SELECTAVG sal 2FROMemp3GROUPBYdeptno AVG SAL 2916 666721751566 6667 汇总 这是一个错误的查询 SQL SELECTdeptno COUNT ename 2FROMemp SELECTdeptno COUNT ename ERRORatline1 ORA 00937 notasingle groupgroupfunction Deptno列必须出现在Groupby子句中 汇总 EMP 按部门求工资的和 DEPTNOJOBSAL 10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250 JOBSUM SAL CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600 DEPTNO 101010202020303030 汇总 SQL SELECTdeptno job sum sal 2FROMemp3GROUPBYdeptno job DEPTNOJOBSUM SAL 10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK1900 9rowsselected 汇总 WHERE子句中不允许出现汇总函数 汇总后的结果必须使用HAVING子句 SQL SELECTdeptno AVG sal 2FROMemp3WHEREAVG sal 20004GROUPBYdeptno WHEREAVG sal 2000 ERRORatline3 ORA 00934 groupfunctionisnotallowedhere AVG sal 不允许出现在WHERE子句中 汇总 取出最大值超过2900的部门的最大值 EMP DEPTNOSAL 1024501050001013002080020110020300020300020297530160030285030125030950301500301250 DEPTNOMAX SAL 105000203000 汇总 SQL SELECTdeptno max sal 2FROMemp3GROUPBYdeptno4HAVINGmax sal 2900 DEPTNOMAX SAL 105000203000 使用HAVING子句 汇总 SQL SELECTjob SUM sal PAYROLL2FROMemp3WHEREjobNOTLIKE SALES 4GROUPBYjob5HAVINGSUM sal 50006ORDERBYSUM sal JOBPAYROLL ANALYST6000MANAGER8275 例 小结 SELECTcolumn group function column FROMtable WHEREcondition GROUPBYgroup by expression HAVINGgroup condition ORDERBYcolumn WHERE子句GROUPBY子句HAVING子句 总结 连接 等值连接 非等值连接 外连接 自连接汇总 GROUPBY子句HAVING子句 课后练习 连接 Thanks Thanks