開發(fā)基礎(chǔ)課件章分組函數(shù)

OracleSQL開發(fā)基礎(chǔ)課程結(jié)構(gòu)內(nèi)容課時（H）第一章

Oracle數(shù)據(jù)庫基礎(chǔ)1.8第二章編寫簡單的查詢語句1.2第三章限制數(shù)據(jù)和對數(shù)據(jù)排序1.5第四章單行函數(shù)2.5第五章多表查詢2第六章分組函數(shù)2第七章子查詢2第八章數(shù)據(jù)操作與事務(wù)控制3.5第九章表和約束4第十章其他數(shù)據(jù)庫對象2.5第六章分組函數(shù)目標(biāo):本章旨在向?qū)W員介紹：1)分組函數(shù)的基本概念2）分組函數(shù)的使用方法時間：2學(xué)時教學(xué)方法：講授ppt＋上機(jī)練習(xí)本章要點分組函數(shù)的目的分組函數(shù)基本概念SUM、AVG、MIN、MAX、COUNT函數(shù)使用GROUPBY子句HAVING子句第六章分組函數(shù)分組函數(shù):6.1分組函數(shù)6.2GROUPBY…HAVING子句的使用6.1分組函數(shù)分組函數(shù)是對表中一組記錄進(jìn)行操作，每組只返回一個結(jié)果。即首先要對表記錄進(jìn)行分組，然后再進(jìn)行操作匯總，每組返回一個結(jié)果。分組時可能是整個表分為一組，也可能根據(jù)條件分成多組。分組函數(shù)常用到以下的五個函數(shù)：MINMAXSUMAVGCOUNT6.1.1MIN函數(shù)和MAX函數(shù)1/2MIN和MAX函數(shù)主要是返回每組的最小值和最大值。MIN([DISTINCT|ALL]表達(dá)式)MAX([DISTINCT|ALL]表達(dá)式)例6-1員工最低工資及最高工資的示例。

SELECTMIN(salary),MAX(salary)FROM employees;6.1.1MIN函數(shù)和MAX函數(shù)2/2例6-2員工姓最開始及最后的示例。例6-3員工最低工資及最高工資的示例。SELECT MIN(last_name),MAX(last_name)FROM employees;SELECT MIN(salary),MAX(salary)FROM employees;6.1.2SUM函數(shù)和AVG函數(shù)SUM和AVG函數(shù)分別返回總和及平均值。SUM([DISTINCT|ALL]n)AVG([DISTINCT|ALL]n)SUM和AVG函數(shù)都是只能夠?qū)?shù)字類型的列或表達(dá)式操作。例6-4公司員工總工資及平均工資的示例。SELECT SUM(salary),AVG(salary)FROM employees;6.1.3COUNT函數(shù)COUNT函數(shù)的主要功能是返回每組記錄的條數(shù)。COUNT({*|[DISTINCT|ALL]表達(dá)式})例6-5公司IT_PROG職位的員工人數(shù)的示例。例6-6公司有部門員工人數(shù)的示例。SELECT COUNT(*)FROM employeesWHERE job_id='IT_PROG';SELECTCOUNT(department_id)FROMemployees;6.1.4組函數(shù)中DISTINCTDISTINCT會消除重復(fù)記錄后再使用組函數(shù)例6-7公司有員工部門數(shù)的示例。

SELECT COUNT(DISTINCTdepartment_id)FROM employees;6.1.5組函數(shù)中空值處理所有組函數(shù)對空值都是忽略的。例6-8員工平均獎金的示例——忽略空值。例6-9員工平均獎金的示例——空值轉(zhuǎn)化。SELECT AVG(commission_pct)FROM employees;SELECT AVG(NVL(commission_pct,0))FROM employees;第六章分組函數(shù)分組函數(shù):6.1分組函數(shù)6.2GROUPBY…HAVING子句的使用6.2.1GROUPBY子句1/2組函數(shù)忽略空值,可以使用NVL,NVL2,COALESCE函數(shù)處理空值結(jié)果集隱式按升序排列,如果需要改變排序方式可以使用Orderby子句(9i)SELECT 列名,組函數(shù)(列名)FROM 表名[WHERE 條件][GROUPBY 分組列][ORDERBY 列名];6.2.1GROUPBY子句2/2例6-10每個部門的總工資。例6-11相同職位且經(jīng)理相同的員工平均工資。SELECTdepartment_id,SUM(salary)FROMemployeesGROUPBYdepartment_id;SELECT job_id,manager_id,AVG(salary)FROM employeesGROUPBYjob_id,manager_idORDERBYjob_id;6.2.2GROUPBY子句注意問題1/2

在GROUPBY子句使用中，有兩點需要注意：GROUPBY子句后的列可以不在SELECT語句中出現(xiàn)。SELECT子句中出現(xiàn)的非分組函數(shù)列必須在GROUPBY子句中出現(xiàn)。例6-12查詢公司每個職位的平均工資，職位列不顯示，同時結(jié)果按照平均工資排序。SELECT AVG(salary)FROM employeesGROUPBYjob_idORDERBYAVG(salary);6.2.2GROUPBY子句注意問題2/2

例6-13分組匯總錯誤示例例6-14分組匯總正確示例SELECT department_id,job_id,AVG(salary)FROM employeesGROUPBYdepartment_id;SELECT department_id,job_id,AVG(salary)FROM employeesGROUPBYdepartment_id,job_id;6.2.3HAVING子句1/5例6-15組函數(shù)篩選示例。原因是Oracle查詢語句的執(zhí)行順序是：FROMWHEREGROUPBYSELECTORDERBYSELECTjob_id,MAX(salary)FROM employeesWHEREMAX(salary)>=9000GROUPBYjob_id;6.2.3HAVING子句2/5語法結(jié)構(gòu)如下：SELECT 列名,組函數(shù)FROM 表名[WHERE 條件][GROUPBY 分組列][HAVING 組函數(shù)表達(dá)式][ORDERBY 列名];6.2.3HAVING子句3/5例6-16組函數(shù)篩選示例。SELECT job_id,MAX(salary)FROM employeesGROUPBYjob_idHAVING MAX(salary)>=9000;6.2.3HAVING子句4/5總結(jié)SELECT語句執(zhí)行過程：通過FROM子句中找到需要查詢的表；通過WHERE子句進(jìn)行非分組函數(shù)篩選判斷；通過GROUPBY子句完成分組操作；通過HAVING子句完成組函數(shù)篩選判斷；通過SELECT子句選擇顯示的列或表達(dá)式及組函數(shù)；通過ORDERBY子句進(jìn)行排序操作。6.2.3HAVING子句5/5例6-17組函數(shù)演示。SELECT department_id,MAX(salary)FROM employeesWHERE department_idBETWEEN30AND90GROUPBYdepartment_idHAVING MAX(salary)>=9000ORDERBYMAX(salary);6.2.4組函數(shù)的嵌套組函數(shù)可以實現(xiàn)嵌套操作，嵌套級數(shù)是2級(select)。例6-18組函數(shù)嵌套演示。SELECT MAX(COUNT(employee_id))FROM employeesGROUPBYdepartment_id;本章小結(jié)MIN函數(shù)和MAX函數(shù)SUM函數(shù)和AVG函數(shù)COUNT函數(shù)組函數(shù)中DISTINCT消除重復(fù)行組函數(shù)中空值處理通過GROUPBY子句進(jìn)行分組匯總GROUPBY子句使用需要注意的兩個問題HAVING子句的使用組函數(shù)的嵌套練習(xí)1.查詢部門平均工資在8000元以上的部門名稱及平均工資。2.查詢員工工作編號中不是以“SA_”開頭并且平均工資