數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用授課人：姓名項(xiàng)目7數(shù)據(jù)高級(jí)查詢目錄03數(shù)據(jù)排序01聚合函數(shù)及其應(yīng)用02分組查詢01聚合函數(shù)及其應(yīng)用一.任務(wù)分析從粵文創(chuàng)的數(shù)據(jù)庫(kù)中，獲取：1.統(tǒng)計(jì)地級(jí)市的個(gè)數(shù)2.找出面積最小的地級(jí)市area表地區(qū)編號(hào)中文名外文名別名地理位置面積（平方千米）人口數(shù)量（人）電話區(qū)號(hào)車牌代碼5810廣州Guangzhou穗廣東中南部7434.4018676600020粵A5820韶關(guān)Shaoguan韶州廣東北部18400.0028551310751粵F5840深圳Shenzhen鵬城珠江口東岸1997.47175600610755粵B5850珠海Zhuhai百島之市珠江三角洲西南部1736.4524396000756粵C二.知識(shí)儲(chǔ)備：如何求平均值SELECT

AVG(字段名)FROM

表名average的縮寫(xiě)2.1、示例7-1：求所有學(xué)生的平均成績(jī)編號(hào)姓名性別成績(jī)1趙曉明男982張宏男883陳強(qiáng)男79班級(jí)1表2.1、示例7-1：求所有學(xué)生的平均成績(jī)——命令編號(hào)姓名性別成績(jī)1趙曉明男982張宏男883陳強(qiáng)男79班級(jí)1表SELECT

AVG(成績(jī))FROM

表名三.如何統(tǒng)計(jì)個(gè)數(shù)——符合條件的記錄的數(shù)量SELECT

COUNT(字段名)FROM

表名3.1、

統(tǒng)計(jì)廣東省地級(jí)市的個(gè)數(shù)area表地區(qū)編號(hào)中文名外文名別名地理位置面積（平方千米）人口數(shù)量（人）電話區(qū)號(hào)車牌代碼5810廣州Guangzhou穗廣東中南部7434.4018676600020粵A5820韶關(guān)Shaoguan韶州廣東北部18400.0028551310751粵F5840深圳Shenzhen鵬城珠江口東岸1997.47175600610755粵B5850珠海Zhuhai百島之市珠江三角洲西南部1736.4524396000756粵CSELECT

COUNT(*)FROM

表名3.2、

最小的地級(jí)市，其面積為多少？area表地區(qū)編號(hào)中文名外文名別名地理位置面積（平方千米）人口數(shù)量（人）電話區(qū)號(hào)車牌代碼5810廣州Guangzhou穗廣東中南部7434.4018676600020粵A5820韶關(guān)Shaoguan韶州廣東北部18400.0028551310751粵F5840深圳Shenzhen鵬城珠江口東岸1997.47175600610755粵B5850珠海Zhuhai百島之市珠江三角洲西南部1736.4524396000756粵CSELECT

MIN(面積)FROM

表名四.復(fù)合語(yǔ)句類似于英語(yǔ)的從句。比如：SELECT字段名FROM表WHERE面積=(select語(yǔ)句)select語(yǔ)句的結(jié)果，是一個(gè)值4.1、

哪個(gè)地級(jí)市的面積最小area表地區(qū)編號(hào)中文名外文名別名地理位置面積（平方千米）人口數(shù)量（人）電話區(qū)號(hào)車牌代碼5810廣州Guangzhou穗廣東中南部7434.4018676600020粵A5820韶關(guān)Shaoguan韶州廣東北部18400.0028551310751粵F5840深圳Shenzhen鵬城珠江口東岸1997.47175600610755粵B5850珠海Zhuhai百島之市珠江三角洲西南部1736.4524396000756粵CSELECT中文名FROMareaWHERE面積=(SELECT

MIN(面積)FROMarea);城市的名字五.常用的聚合函數(shù)函數(shù)函數(shù)功能sum()總和avg()平均值count()個(gè)數(shù)max()最大值min()最小值02分組查詢一.什么是分組姓名性別成績(jī)黃奕男78劉偉男84羅伊女86班級(jí)1表比如：按性別分組，求每組人數(shù)，最終，得到男女生的數(shù)量。一.分組用到的關(guān)鍵詞是

groupby姓名性別成績(jī)黃奕男78劉偉男84羅伊女86班級(jí)1表比如語(yǔ)句中要用到：

GROUPBY

性別示例7-2：統(tǒng)計(jì)“志愿者活動(dòng)”表中，哪幾個(gè)班級(jí)的人參加了活動(dòng)姓名班級(jí)蔡陳星網(wǎng)絡(luò)2111曾浩網(wǎng)絡(luò)2111陳煌欽網(wǎng)絡(luò)2113陳木泉網(wǎng)絡(luò)2111陳一帆信安2115鄧棋彬網(wǎng)絡(luò)2112鄧子健網(wǎng)絡(luò)2112傅國(guó)權(quán)網(wǎng)絡(luò)2112黃炳盛網(wǎng)絡(luò)2114黃文杰網(wǎng)絡(luò)2112江俊宇網(wǎng)絡(luò)2112梁立志網(wǎng)絡(luò)2112林土稘網(wǎng)絡(luò)2112馬佳宜網(wǎng)絡(luò)2112?????通過(guò)觀察，發(fā)現(xiàn)有5個(gè)班的學(xué)生參加了活動(dòng)。如何通過(guò)

groupby

統(tǒng)計(jì)呢？示例7-2：統(tǒng)計(jì)“志愿者活動(dòng)”表中，哪幾個(gè)班級(jí)的人參加了活動(dòng)的方法姓名班級(jí)蔡陳星網(wǎng)絡(luò)2111曾浩網(wǎng)絡(luò)2111陳煌欽網(wǎng)絡(luò)2113陳木泉網(wǎng)絡(luò)2111陳一帆信安2115鄧棋彬網(wǎng)絡(luò)2112鄧子健網(wǎng)絡(luò)2112傅國(guó)權(quán)網(wǎng)絡(luò)2112黃炳盛網(wǎng)絡(luò)2114黃文杰網(wǎng)絡(luò)2112江俊宇網(wǎng)絡(luò)2112梁立志網(wǎng)絡(luò)2112林土稘網(wǎng)絡(luò)2112馬佳宜網(wǎng)絡(luò)2112SELECT

班級(jí)

FROM

表

GROUPBY

班級(jí)SELECT

DISTINCT

班級(jí)

FROM

表方法1方法2示例7-2：distinct（不同種類的）姓名班級(jí)蔡陳星網(wǎng)絡(luò)2111曾浩網(wǎng)絡(luò)2111陳煌欽網(wǎng)絡(luò)2113陳木泉網(wǎng)絡(luò)2111陳一帆信安2115鄧棋彬網(wǎng)絡(luò)2112鄧子健網(wǎng)絡(luò)2112傅國(guó)權(quán)網(wǎng)絡(luò)2112黃炳盛網(wǎng)絡(luò)2114黃文杰網(wǎng)絡(luò)2112江俊宇網(wǎng)絡(luò)2112梁立志網(wǎng)絡(luò)2112林土稘網(wǎng)絡(luò)2112馬佳宜網(wǎng)絡(luò)2112SELECT

DISTINCT

班級(jí)

FROM

表distinct班級(jí)含義是：每個(gè)班級(jí)只能出現(xiàn)1次，不能重復(fù)。其結(jié)果就是：得到每個(gè)班級(jí)的名稱，去掉重復(fù)任務(wù)7-2：統(tǒng)計(jì)每個(gè)城市名人的數(shù)量需要使用：1.COUNT()2.GROUPBY城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表任務(wù)7-2：統(tǒng)計(jì)每個(gè)城市名人的數(shù)量SELECT城市,COUNT(*)FROM表

GROUPBY城市城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表任務(wù)7-3：統(tǒng)計(jì)廣州、佛山、珠海三個(gè)城市的名人數(shù)量，以及三個(gè)市名人總數(shù)量需要使用：1.WHERE條件：包含這3座城市2.COUNT()3.GROUPBY4.WITHROLLUP城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表匯總，總和任務(wù)7-3：統(tǒng)計(jì)三個(gè)城市名人的數(shù)量和三市總數(shù)量——命令SELECT城市,COUNT(*)FROM表

WHERE城市in('廣州','佛山','珠海')

GROUPBY城市

WITHROLLUP城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表二.拓展知識(shí)：

函數(shù)

group_concat()concat是

concatenate的縮寫(xiě)concatenate：連續(xù)，連鎖二.拓展知識(shí)：group_concat()產(chǎn)生的樣式城市名人珠海蘇兆征,楊匏安,唐國(guó)安潮州林大欽,黃仁勇汕頭秦牧,丘成桐,馬化騰城市名人珠海蘇兆征珠海楊匏安珠海唐國(guó)安潮州林大欽潮州黃仁勇汕頭秦牧汕頭丘成桐汕頭馬化騰原來(lái)只能這樣顯示現(xiàn)在可以在一行內(nèi)顯示借助group_concat()拓展7-2：通過(guò)“名人所在城市”表，列舉出每個(gè)城市的名人要求使用函數(shù)group_concat()城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表拓展7-2：通過(guò)“名人所在城市”表，列舉出每個(gè)城市的名人SELECT城市,GROUP_CONCAT(名人)FROM名人所在城市GROUPBY城市;城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表拓展7-3：通過(guò)“名人所在城市”表，列舉出名人少于11人的城市

及人數(shù)分析：1.按城市分組2.對(duì)每個(gè)組進(jìn)行求和3.篩選出“和”小于11的城市城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表拓展7-3：列舉出名人少于11人的城市

及人數(shù)。引入HAVING對(duì)每個(gè)小組進(jìn)行篩選，其條件引導(dǎo)詞，要使用

having不允許使用

where城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表拓展7-3：通過(guò)“名人所在城市”表，列舉出每個(gè)城市的名人SELECT城市,COUNT(名人)FROM名人所在城市GROUPBY城市

HAVINGCOUNT(*)>11;城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表拓展7-4：“社團(tuán)情況”表中，每個(gè)班的同學(xué)都參加了哪些社團(tuán)分析：先按照班級(jí)分組每個(gè)組中，再按照社團(tuán)分組“社團(tuán)情況”表姓名班級(jí)社團(tuán)蔡陳星網(wǎng)絡(luò)2112歷史曾浩網(wǎng)絡(luò)2113歷史陳煌欽網(wǎng)絡(luò)2113文學(xué)陳木泉網(wǎng)絡(luò)2113文學(xué)陳一帆網(wǎng)絡(luò)2112圍棋鄧棋彬網(wǎng)絡(luò)2114圍棋鄧子健網(wǎng)絡(luò)2114文學(xué)傅國(guó)權(quán)網(wǎng)絡(luò)2112歷史黃炳盛網(wǎng)絡(luò)2114圍棋黃文杰網(wǎng)絡(luò)2112歷史拓展7-4：“社團(tuán)情況”表中，每個(gè)班的同學(xué)都參加了哪些社團(tuán)SELECTDISTINCT班級(jí),社團(tuán)FROM社團(tuán)情況;“社團(tuán)情況”表姓名班級(jí)社團(tuán)蔡陳星網(wǎng)絡(luò)2112歷史曾浩網(wǎng)絡(luò)2113歷史陳煌欽網(wǎng)絡(luò)2113文學(xué)陳木泉網(wǎng)絡(luò)2113文學(xué)陳一帆網(wǎng)絡(luò)2112圍棋鄧棋彬網(wǎng)絡(luò)2114圍棋鄧子健網(wǎng)絡(luò)2114文學(xué)傅國(guó)權(quán)網(wǎng)絡(luò)2112歷史黃炳盛網(wǎng)絡(luò)2114圍棋黃文杰網(wǎng)絡(luò)2112歷史SELECT班級(jí),社團(tuán)FROM社團(tuán)情況GROUPBY班級(jí),社團(tuán);方法1方法203數(shù)據(jù)排序一.任務(wù)分析從粵文創(chuàng)的數(shù)據(jù)庫(kù)中，按車牌號(hào)碼排序area表地區(qū)編號(hào)中文名外文名別名地理位置面積（平方千米）人口數(shù)量（人）電話區(qū)號(hào)車牌代碼5810廣州Guangzhou穗廣東中南部7434.4018676600020粵A5820韶關(guān)Shaoguan韶州廣東北部18400.0028551310751粵F5840深圳Shenzhen鵬城珠江口東岸1997.47175600610755粵B5850珠海Zhuhai百島之市珠江三角洲西南部1736.4524396000756粵C1.1、排序的關(guān)鍵詞是ORDERBY升序：ORDERBY車牌號(hào)碼升序：ORDERBY車牌號(hào)碼

ASC降序：ORDERBY車牌號(hào)碼

DESC

二者等價(jià)示例7.3、按車牌號(hào)碼升序排序area表地區(qū)編號(hào)中文名外文名別名地理位置面積（平方千米）人口數(shù)量（人）電話區(qū)號(hào)車牌代碼5810廣州Guangzhou穗廣東中南部7434.4018676600020粵A5820韶關(guān)Shaoguan韶州廣東北部18400.0028551310751粵F5840深圳Shenzhen鵬城珠江口東岸1997.47175600610755粵B5850珠海Zhuhai百島之市珠江三角洲西南部1736.4524396000756粵CSELECT城市,車牌號(hào)碼FROM表ORDERBY車牌號(hào)碼;任務(wù)7.4、按車牌號(hào)碼降序排序area表地區(qū)編號(hào)中文名外文名別名地理位置面積（平方千米）人口數(shù)量（人）電話區(qū)號(hào)車牌代碼5810廣州Guangzhou穗廣東中南部7434.4018676600020粵A5820韶關(guān)Shaoguan韶州廣東北部18400.0028551310751粵F5840深圳Shenzhen鵬城珠江口東岸1997.47175600610755粵B5850珠海Zhuhai百島之市珠江三角洲西南部1736.4524396000756粵CSELECT城市,車牌號(hào)碼FROM表ORDERBY車牌號(hào)碼DESC;任務(wù)拓展7.5、按照名人數(shù)量排列城市，并顯示名人的名字提示：排序是依照每個(gè)城市分組后，用COUNT(*)算出的結(jié)果進(jìn)行排序城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表城市名人珠海蘇兆征,楊匏安,唐國(guó)安,唐紹儀潮州林大欽,黃仁勇,劉允,王大寶,李嘉誠(chéng),饒宗頤,陳偉南,孫大文深圳凌道揚(yáng),曾生,劉黑仔,賴恩爵,麥克·楊進(jìn)華,汪公式,鄭毓秀,藍(lán)造,陳郁,黃耀庭汕頭鄭信,陳北科,翁萬(wàn)達(dá),秦牧,鄭正秋,馬大猷,丘成桐,李宏平,陳弼臣,陳興勤,馬化騰顯示結(jié)果（省略了17行）任務(wù)拓展7.5、按照名人數(shù)量排列城市，并顯示名人的名字——命令城市名人云浮惠能云浮陳集原云浮陳璘云浮李熙載云浮蔡廷鍇云浮鄧發(fā)廣州楊孚廣州崔與之廣州李昴英廣州陳大震“名人所在城市”表SELECT城市,GROUP_CONCAT(名人)FROM名人所在城市

GROUPBY城市

ORDERBYCOUNT(*);任務(wù)拓展7.6、“成績(jī)單”表中有多個(gè)班級(jí)。每個(gè)班級(jí)內(nèi)部，成績(jī)降序排序Snameclassscore蔡俊芬21120577陳楚權(quán)21120466陳廣燊21120385陳俊楠21120473陳煒鑫21120397戴劍豪21120381甘怡21120570Snameclassscore陳煒鑫21120397陳廣燊21120385戴劍豪21120381陳俊楠21120473陳楚權(quán)21120466蔡俊芬21120577甘怡21120570排序前排序后提示：這是一個(gè)2列排序任務(wù)拓展7.6、有多個(gè)班級(jí)。每個(gè)班級(jí)內(nèi)部，成績(jī)降序排序——命令Snameclassscore蔡俊芬21120577陳楚權(quán)21120466陳廣燊21120385陳俊楠21120473陳煒鑫21120397戴劍豪21120381甘怡21120570“成績(jī)單”表SELECT*FROM成績(jī)單ORDERBYclass,scoreDESC