oracle數(shù)據(jù)庫學(xué)習(xí)資料課件-資料下載頁

　　

【正文】 l MIN()：求出一組中的最小值l AVG()：求出一組中的平均值l SUM()：求和范例：COUNT()函數(shù)SELECT COUNT(empno) FROM emp。我們常用COUNT(*)，最好能夠用字段代替*范例：MAX()、MIN()函數(shù)，求最大最小值，一般是針對于數(shù)值的字段的，求出所有員工的的最高工資，和最底工資和平均工資。SELECT MAX(sal) 最高工資,MIN(sal) 最底工資,AVG(sal) 平均工資　FROM emp。范例：求出部門１０的所有員工工資的總合SELECT SUM(sal) 工資綜合 FROM emp WHERE deptno=10。如果如下查詢輸出部門編號和其部門所有員工的工資總和，會(huì)產(chǎn)生錯(cuò)誤。SELECT deptno ,SUM(sal) 工資綜合 FROM emp WHERE deptno=10。發(fā)生以上的錯(cuò)誤信息，是因?yàn)檫@樣的查詢需要進(jìn)行分組統(tǒng)計(jì)。分組統(tǒng)計(jì)有其固定的語法格式：SELECT {DISTINCT} *| 查詢列 列別名1，查詢列2 列別名2,……FROM 表名稱１　表別名１，表名稱２　表別名２,……{WHERE 條件(s)} {ORDER BY 排序的字段1,排序的字段2 ASC|DESC} {GROUP BY 分組字段}范例：求出每個(gè)部門的雇員的數(shù)量 分析：應(yīng)該按照部門編號deptno進(jìn)行分組SELECT deptno ,COUNT(empno)FROM empGROUP BY deptno。根據(jù)上面的理解，完成查詢每個(gè)部門的雇員的總工資SELECT deptno ,SUM(sal)FROM empGROUP BY deptno。進(jìn)一步查詢部門編號是１０的雇員的總工資，觀察下面的代碼：SELECT deptno ,SUM(sal)FROM empWHERE deptno=10GROUP BY deptno。代碼正確執(zhí)行，可是如果我們想找出總工資大于9000的部門有哪些？修改上面的的代碼：SELECT deptno ,SUM(sal)FROM empWHERE SUM(sal)9000GROUP BY deptno。可是如果執(zhí)行以上的命令，會(huì)出現(xiàn)以下的錯(cuò)誤：分組函數(shù)只能在分組中使用，不允許在WHERE語句之中出現(xiàn)，那么如果現(xiàn)在要指定的分組的條件，只能通過HAVING子句。此時(shí)的SQL語法格式如下：SELECT {DISTINCT} *| 查詢列 列別名1，查詢列2 列別名2,……FROM 表名稱１　表別名１，表名稱２　表別名２,……{WHERE 條件(s)} {ORDER BY 排序的字段1,排序的字段2 ASC|DESC} {GROUP BY 分組字段 {HAVING 分組條件}}以上的查詢通過以下的代碼：SELECT deptno ,SUM(sal)FROM empGROUP BY deptno HAVING SUM(sal)9000 。這樣可以得到正確的查詢結(jié)果。范例：顯示非銷售人員工作名稱以及從事同一工作雇員的月工資的總和，并且要滿足從事同一工作的雇員的月工資合計(jì)大于$5000，輸出結(jié)果按月工資的合計(jì)升序排列。 分析：顯示非銷售人員—條件是： job’SALESMAN’SELECT *FROM empWHERE job’SALESMAN’ 分析：從事同一工作—按照工作分組：GROUP BY job 工作名稱、月工資的總和—分組查詢出的是工作名稱和工資合計(jì)：job,SUM(sal)SELECT job, SUM(sal)FROM empWHERE job’SALESMAN’GROUP BY job。 分析：工資合計(jì)大于$5000—分組的條件：HAVING SUM(sal)5000SELECT job, SUM(sal)FROM empWHERE job’SALESMAN’GROUP BY job HAVING SUM(sal)5000。 分析：輸出結(jié)果按月工資的合計(jì)升序排列—排序的方式：ORDER BY SELECT job, SUM(sal)FROM empWHERE job’SALESMAN’GROUP BY job HAVING SUM(sal)5000ORDER BY SUM(sal) 。因?yàn)榕判虻臅r(shí)候使用SUM(sal)不方便，我們常用列別名解決這個(gè)問題。SELECT job, SUM(sal) suFROM empWHERE job’SALESMAN’GROUP BY job HAVING SUM(sal)5000ORDER BY su 。小結(jié)： 如果程序中使用了分組函數(shù)，則有兩種可以使用的情況l 程序中存在了GROUP BY，并指定了分組條件，這樣可以將分組條件一起查詢出來l 如果不使用分組的話，則只能單獨(dú)的使用分組函數(shù) 在使用分組函數(shù)的時(shí)候，不能出現(xiàn)分組函數(shù)和分組條件之外的字段，否則會(huì)出現(xiàn)錯(cuò)誤ORA00937: 不是單組分組函數(shù)分組的簡單原則：l 只要一列上存在重復(fù)的內(nèi)容才可能考慮到分組l 分組函數(shù)可以嵌套使用，但是在組函數(shù)使用的時(shí)候不能再出現(xiàn)分組條件的查詢語句范例：求出平均工資最高的部門的編號和部門的工資SELECT deptno,MAX(AVG(sal))FROM empGROUP BY deptno。結(jié)果出現(xiàn)錯(cuò)誤。修改代碼，去掉deptno字段的顯示可以正確查詢。SELECT MAX(AVG(sal))FROM empGROUP BY deptno。如果需要查詢出平均工資最高的部門編號，修改以上的代碼：SELECT FROM (SELECT deptno d,AVG(sal) avFROM empGROUP BY deptno) aWHERE =( SELECT MAX(AVG(sal))FROM empGROUP BY deptno)。以上問題的解決采用了子查詢。子查詢子查詢：在一個(gè)查詢的內(nèi)部還包括另外的一個(gè)查詢，則此查詢稱為子查詢。子查詢的格式：SELECT {DISTINCT} *| 查詢列 列別名1，查詢列2 列別名2,……FROM 表名稱１　表別名１，表名稱２　表別名２,…… (SELECT {DISTINCT} *| 查詢列 列別名1，查詢列2 列別名2,……FROM 表名稱１　表別名１，表名稱２　表別名２,……{WHERE 條件(s)} {ORDER BY 排序的字段1,排序的字段2 ASC|DESC} {GROUP BY 分組字段}){WHERE 條件(s)(SELECT {DISTINCT} *| 查詢列 列別名1，查詢列2 列別名2,……FROM 表名稱１　表別名１，表名稱２　表別名２,……{WHERE 條件(s)} {ORDER BY 排序的字段1,排序的字段2 ASC|DESC} {GROUP BY 分組字段})} {ORDER BY 排序的字段1,排序的字段2 ASC|DESC} {GROUP BY 分組字段}范例： 要求查詢出比7654工資要高的所有雇員的信息。 分析：先查詢出7654的工資SELECT sal FROM emp WHERE empno=7654。 分析。在此查詢的基礎(chǔ)上再查詢比7654工資高的雇員,只要sal7654工資即可SELECT *FROM empWHERE sal(SELECT sal FROM emp WHERE empno=7654)。所有的子查詢必須 在（）中編寫。子查詢在操作中有分為以下三類：l 單列子查詢：返回的結(jié)果是一列的一個(gè)內(nèi)容，此種查訓(xùn)出現(xiàn)的幾率最高l 單行子查詢：返回多個(gè)列，有可能是一條完整的記錄l 多行子查詢：返回多條記錄在查詢的面試題目中 ，子查詢出現(xiàn)的機(jī)會(huì)最高。范例：要求查詢出工資比7654高，同時(shí)與7788從事相同工作的全部雇員信息。根據(jù)剛才的查詢分析，條件有兩個(gè)，一個(gè)是sal7654的工資,同時(shí)job=7788的jobSELECT *FROM empWHERE sal(SELECT sal FROM emp WHERE empno=7654) ANDjob=(SELECT job FROM emp WHERE empno=7788)。范例：要求查詢出工資最低的雇員姓名、工作、工資。分析：工資最低，需要采用到分組函數(shù)MIN(sal),首先查出最低工資SELECT MIN(sal) FROM emp。分析：在這個(gè)基礎(chǔ)上完善查詢的條件SELECT ename,job,sal FROM empWHERE sal=(SELECT MIN(sal) FROM emp)。思考：要求查詢出，部門名稱、部門的員工數(shù)，部門的平均工資，部門的最低收入雇員的姓名和最高收入雇員的姓名。分析：查詢涉及到兩張表emp和dept 如果要想求出每個(gè)部門的員工數(shù)量及平均工資，則肯定需要使用分組統(tǒng)計(jì)，按deptno進(jìn)行分組。SELECT deptno,COUNT(empno),AVG(sal)FROM empGROUP BY deptno。 但是如果想查詢出部門的名稱，就需要與dept表進(jìn)行關(guān)聯(lián)。于是有人寫下以下代碼：SELECT ,COUNT(),AVG()FROM emp e,dept dWHERE =GROUP BY 。可是出現(xiàn)了這樣的錯(cuò)誤。在第一步查出部門編號、部門的人數(shù)、部門的平均工資的基礎(chǔ)上，應(yīng)該把這個(gè)作為一個(gè)臨時(shí)的表與dept表進(jìn)行關(guān)聯(lián)。這樣才能正確的顯示出部門的名稱。SELECT ,FROM (SELECT deptno,COUNT(empno) c,AVG(sal) aFROM empGROUP BY deptno) l,dept dWHERE =。 部門的最低收入和最高收入，應(yīng)該用到MAX()和MIN()函數(shù)，依然是根據(jù)部門進(jìn)行分組的，修改以上的代碼,同時(shí)顯示出部門中工資最低和工資最高的員工的姓名。SELECT ,FROM (SELECT deptno,COUNT(empno) c,AVG(sal) a ,MAX(sal) max ,MIN(sal) minFROM empGROUP BY deptno) l,dept d,emp eWHERE = AND (= OR =)。在子查詢中有以下三種查詢的操作符號：l INl ANYl ALLIN操作：指定一個(gè)查詢的范圍范例：求出每個(gè)部門的最低工資的雇員信息每個(gè)部門的最低工資，返回的值肯定是多個(gè)，此時(shí)可以使用IN指定一個(gè)操作的范圍。SELECT * FROM empWHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno)。ANY操作：有三種l =ANY：與IN的操作符功能完全一樣SELECT * FROM empWHERE sal=ANY(SELECT MIN(sal) FROM emp GROUP BY deptno)。l ANY：比里面最小的值大SELECT * FROM empWHERE salANY(SELECT MIN(sal) FROM emp GROUP BY deptno)。l ANY：比里面最大的值小SELECT * FROM empWHERE salANY(SELECT MIN(sal) FROM emp GROUP BY deptno)。ALL操作：有三種l ALL：比里面最大的值大SELECT * FROM empWHERE salALL(SELECT MIN(sal) FROM emp GROUP BY deptno)。l ALL：比里面最小的值小SELECT * FROM empWHERE salALL(SELECT MIN(sal) FROM emp GROUP BY deptno)。對于子查詢來講，還可以進(jìn)行多列子查詢，一個(gè)子查詢中同時(shí)返回多個(gè)查詢的列。多列子查詢在開發(fā)中不建議使用。32 / 32