第05章多表查詢與組函數(shù)

第5章

多表查詢與組函數(shù)玉不琢,不成器;人不學,不知道。2鑒賞3什么是oracle函數(shù)？回顧與作業(yè)點評函數(shù)分為哪幾類？請說出幾個常用的函數(shù)？5預習檢查什么是組函數(shù)？groupby的作用?連接查詢的分類?(提示一共分四類)6內容進度組函數(shù)使用groupby進行分組使用having過濾子查詢連接查詢8自關聯(lián)組函數(shù)什么是組函數(shù)也叫聚合函數(shù)，多行輸入一行輸出。普通函數(shù)是一行輸入一行輸出常見的五種組函數(shù)MAX()求最大值

求員工的最大工資selectmax(sal)fromempMIN()求最小值

AVG()求平均值

SUM()求和

COUNT()求總共多少條記錄,例如:求10號部門一共有多少個員工

selectcount(*)fromempwheredeptno=1014內容進度使用groupby進行分組組函數(shù)使用having過濾子查詢連接查詢8自關聯(lián)使用groupby進行分組使用groupby對查詢后的記錄進行分組需求1:求每個部門的平均薪水selectdeptno,avg(sal)asavg_salfromempgroupbydeptno

groupby分組規(guī)則如果分組了,select后面的字段要么是分組的條件，要么是五個組函數(shù)之一小技巧:一說每個就要分組了哦，而且每后面的就是分組的條件14使用groupby進行分組需求2：每個部門同一個職位的最大工資 selectdeptno,job,max(sal)fromempgroupbydeptno,job14內容進度使用having進行過濾組函數(shù)使用groupby進行分組子查詢連接查詢8自關聯(lián)使用having進行過濾使用having對分組以后的數(shù)據(jù)進行再過濾where語句是對單條記錄進行過濾的，不能過濾分組之后的記錄

執(zhí)行語句的時候首先執(zhí)行where語句然后把查到的記錄進行分組

要想過濾分組后的數(shù)據(jù)，用having需求1:按照部門編號進行分組，分組之后求每一個部門的平均薪水，要求顯示平均薪水大于2000的部門的部門編號和平均薪水 selectdeptno,avg(sal)fromemp groupbydeptno havingavg(sal)>2000;

14使用having進行過濾需求2:薪水大于1200的雇員，按照部門編號進行分組，分組之后平均薪水必須大于1500,求分組內的平均工資，平均工資按倒序排列selectavg(sal)fromempwheresal>1200groupbydeptnohavingavg(sal)>1500orderbyavg(sal)descSql語句總結(共五行)select*fromempwhere條件groupby分組條件having對分組進行過濾orderby排序條件

14內容進度子查詢組函數(shù)使用groupby進行分組使用having進行過濾連接查詢8自關聯(lián)子查詢查詢的分類簡單查詢:是查一次一個select復雜查詢分為子查詢和連接查詢子查詢(也叫嵌套查詢)查多次，多個select嵌套出現(xiàn),第一次的查詢結果可以作為第二次的查詢條件或表名需求:求最大工資那個人的姓名和薪水

第一步:求最大工資selectmax(sal)fromemp

第二步:求最大工資那個人叫什么 selectename,salfromempwheresal=5000

第三步合并selectename,salfromempwheresal=(selectmax(sal)fromemp);

14子查詢需求2:哪些人得工資位于所有人得平均工資之上1.求平均工資 selectavg(sal)fromemp2.工資大于平均工資的人的姓名、工資 selectename,salfromempwheresal>2073.214285714293.合并 selectename,salfromempwheresal>(selectavg(sal)fromemp)

14學生練習需求:查SMITH的上司叫什么1.查SMITH的上司的編號selectmgrfromempwhereename='SMITH';--79022.查上司叫什么selectenamefromempwhereempno=7902;--FORD3.合并selectenamefromempwhereempno=(selectmgrfromempwhereename='SMITH')

14內容進度連接查詢組函數(shù)使用groupby進行分組使用having進行過濾子查詢8自關聯(lián)連接查詢什么是連接查詢查詢多張表的數(shù)據(jù),把多張表連接起來查詢叫連接查詢?yōu)槭裁从没蚴裁磿r候用?當數(shù)據(jù)來自多張表的時候，考慮使用連接查詢連接查詢分類內連接外連接交叉連接自然連接14內連接內連接需求:查詢員工姓名、薪水、部門名稱 selecte.ename,e.sal,d.dnamefromdeptdinnerjoinempe on(d.deptno=e.deptno)14外連接外連接分類外連接分為:左外連接、右外連接、全外連接左外連接在內連接的基礎上,保證左表的數(shù)據(jù)都有(右表的字段用空補全)select*fromdeptleftouterjoinempon(dept.deptno=emp.deptno)14外連接右外連接在內連接的基礎上,保證右表的數(shù)據(jù)都有(左表的字段用空補全) select*fromdeptrightouterjoinemp on(dept.deptno=emp.deptno)14外連接全外連接在內連接的基礎上保證左右表的數(shù)據(jù)都有(左連接和右連接的并集) select*fromdeptfullouterjoinemp on(dept.deptno=emp.deptno)外連接語句中的outer關鍵字可以省略14交叉連接和自然連接交叉連接(笛卡爾積)左表的每條記錄和右表的每條記錄首尾相連 selectename,dnamefromemp,dept//1992sql selectename,dnamefromdeptcrossjoinemp//1999sql左表4條*右表14=查詢結果共56條自然連接select*fromdeptnaturaljoinemp

和內連接類似，只是有以下區(qū)別以左右兩表相同名稱的字段作為連接條件相當于加上

on(dept.deptno=emp.deptno)去掉重復的字段(即名稱相同、類型也相同的字段)14等值連接和非等值連接等值連接和非等值連接根據(jù)連接條件是否相等,可以把連接查詢分為等值連接和非等值連接等值連接selectename,dnamefromempjoindept on(emp.deptno=dept.deptno);//1999sql非等值連接(演示連接三張表)需求:查詢姓名、薪水、部門名稱、薪水等級selectemp.ename,emp.sal,dept.dname,salgrade.gradefromdeptinnerjoinemp on(dept.deptno=emp.deptno) innerjoinsalgrade on(emp.salbetweensalgrade.losalandsalgrade.hisal)14內容進度自關聯(lián)組函數(shù)使用groupby進行分組使用having進行過濾子查詢8連接查詢自關聯(lián)特殊的連接查詢:自關聯(lián)連接查詢時,左右兩張表是同一張表

為一張表取不同的別名，當成兩張表來用需求1:SMITH這個人的名字以及他的經(jīng)理人的名字 selectemployee.ename,manager.enamefromempmanagerinnerjoinempemployee on(manager.empno=employee.mgr)whereemployee.ename='SMITH'需求2:查詢所有員工的名字和上司的名字 selectemployee.ename,manager.enamefromempman