數(shù)據(jù)庫課件章分組函數(shù)

課程結(jié)2內(nèi)課時第一章Oracle數(shù)據(jù)庫第二章編寫簡單課程結(jié)2內(nèi)課時第一章Oracle數(shù)據(jù)庫第二章編寫簡單的查詢語第三章限制數(shù)據(jù)和對數(shù)據(jù)排單行函多表查2分組函2子查2表和約4第六分組函第六分組函目標(biāo)本章旨在向?qū)W員介紹1)分組函數(shù)的基本概2）時間：2學(xué)教學(xué)方法：講授ppt＋上3本章本章要GROUPBY4第六分第六分組函56.1分6.1分組函66.1.1MIN函數(shù)和MAX函6.1.1MIN函數(shù)和MAX函例6-員工最低工資及最高工資的示例。7SELECTMIN(salary),MAX(salary)FROMemployees;6.1.1MIN函數(shù)和MAX函例6-員工6.1.1MIN函數(shù)和MAX函例6-員工姓最開始及最后的示例6-員工最低工資及最高工資的示。8 FROMemployees; FROMemployees;6.1.2SUM函數(shù)和AVG函6.1.2SUM函數(shù)和AVG函例6-公司員工總工資及平均工資的示9 6.1.3COUNT函COUNT({*|[DISTINCT|ALL]表達(dá)式–例6.1.3COUNT函COUNT({*|[DISTINCT|ALL]表達(dá)式–例6-公司IT_PROG職位的員工人數(shù)的示例6-公司有部門員工人數(shù)的示FROMemployees; FROM 6.1.4組函數(shù)中6.1.4組函數(shù)中例6-公司有員工部門數(shù)的示例 FROMemployees;6.1.5組函數(shù)中空值處6.1.5組函數(shù)中空值處例6-員工平均獎金的示例——忽略空例6-員工平均獎金的示例——空值轉(zhuǎn) 第六分第六分組函6.2.1GROUPBY子6.2.1GROUPBY子Orderby子句 表 [GROUPBY [ORDERBY 6.2.1GROUPBY子例6-每個6.2.1GROUPBY子例6-每個部門的總工例6-相同職位且經(jīng)理相同的員工平均工資 ORDERBYjob_id; GROUPBYdepartment_id6.2.2GROUPBY6.2.2GROUPBY例6- 查詢公司每個職位的平均工資，職位列不顯示，同時果按照平均工資排序 GROUPBYjob_idORDERBYAVG(salary);6.2.2GROUPBY子句注意問6.2.2GROUPBY子句注意問例6-分組匯總錯誤例6-分組匯總正確 FROMemployeesGROUPBY FROMemployeesGROUPBY6.2.3HAVING子例6-組函數(shù)篩選6.2.3HAVING子例6-組函數(shù)篩選示–WHEREGROUPORDER FROMemployeesWHEREGROUPBY6.2.3HAVING子6.2.3HAVING子 列名 表 [GROUPBY [ORDERBY 6.2.3HAVING子例6-6.2.3HAVING子例6-組函數(shù)篩選示 job_id, GROUPBY 6.2.3HAVING子6.2.3HAVING子6.2.3HAVING子例6-176.2.3HAVING子例6-17 FROMemployees department_idBETWEEN30AND90GROUPBYdepartment_id