主講教師：姜云橋任務(wù)3.2數(shù)據(jù)表的高級(jí)查詢(xún)子項(xiàng)目3數(shù)據(jù)庫(kù)的查詢(xún)開(kāi)發(fā)任務(wù)背景查詢(xún)和統(tǒng)計(jì)數(shù)據(jù)是數(shù)據(jù)庫(kù)的基本功能。在數(shù)據(jù)庫(kù)實(shí)際操作中，經(jīng)常遇到類(lèi)似的查詢(xún)，例如，按照要求完成各種分組的設(shè)置、過(guò)濾；返回指定數(shù)量、位置的數(shù)據(jù)。任務(wù)目標(biāo)1.能按照要求完成各種分組的設(shè)置。能按照要求完成分組的過(guò)濾。能按照指定要求完成排序。能返回指定數(shù)量、位置的數(shù)據(jù)。

知識(shí)目標(biāo)1.掌握groupby的使用。掌握having的使用，理解having與where的區(qū)別。掌握orderby的各種排序方式。掌握l(shuí)imit的使用方式。能力目標(biāo)任務(wù)要求本任務(wù)基于學(xué)生表student和成績(jī)表score，完成數(shù)據(jù)的分組，包括按照單字段和多字段進(jìn)行分組；按照指定方式，完成分組的過(guò)濾；完成數(shù)據(jù)的排序，按照指定字段指定方式；完成限制返回指定位置、指定數(shù)量的記錄的查詢(xún)。任務(wù)必備知識(shí)想一想GROUPBY子句的功能？任務(wù)必備知識(shí)3.2.1認(rèn)識(shí)groupby子句groupby子句主要根據(jù)字段對(duì)行分組，在分組之后會(huì)把每個(gè)分組聚合成一行數(shù)據(jù)，因此需要調(diào)用聚合函數(shù)完成該操作，常用的聚合函數(shù)如表所示。groupby子句的語(yǔ)法格式如下。selectcol1,col2,... fromtable_name wherewhere_condition groupbycol1,col2 havingwhere_condition任務(wù)必備知識(shí)3.2.1認(rèn)識(shí)groupby子句（1）groupby子句后通常包含列名或表達(dá)式。也可以用正整數(shù)表示列，如指定3，則表示按第3列分組，另外需要注意的一點(diǎn)，mysql8.0對(duì)于groupby字段不再支持隱式排序，如需要排序，必須顯式加上orderby子句。（2）asc為升序，desc降序，系統(tǒng)默認(rèn)為asc，將按分組的第一列升序排序輸出結(jié)果。（3）可以指定多列分組。若指定多列分組，則先按指定的第一列分組再對(duì)指定的第二列分組，以此類(lèi)推。（4）使用帶rollup操作符的groupby子句：指定在結(jié)果集內(nèi)不僅包含由groupby提供的正常行，還包含匯總行。任務(wù)必備知識(shí)3.2.1認(rèn)識(shí)groupby子句任務(wù)必備知識(shí)【任務(wù)3.2.1】按系統(tǒng)計(jì)各系的學(xué)生人數(shù)，SQL語(yǔ)句如下mysql>selectd_no,count(*)as人數(shù)

->fromstudent ->groupbyd_no;3.2.1認(rèn)識(shí)groupby子句任務(wù)必備知識(shí)count()參數(shù)沒(méi)有指定字段時(shí)，如用星號(hào)（*），表示統(tǒng)計(jì)的時(shí)記錄數(shù)，即使某個(gè)字段為null也會(huì)被統(tǒng)計(jì)在內(nèi)；而指定字段的情況下，如count(field),如果field為null,此時(shí)則不會(huì)被統(tǒng)計(jì)在內(nèi)。3.2.1認(rèn)識(shí)groupby子句任務(wù)必備知識(shí)【任務(wù)3.2.2】統(tǒng)計(jì)各系男女生人數(shù)mysql>selectd_noas系別,s_sexas性別,count(*)as人數(shù)

->fromstudent ->groupbyd_no,s_sex;3.2.1認(rèn)識(shí)groupby子句任務(wù)必備知識(shí)當(dāng)按多個(gè)字段分組時(shí)，字段之間需要逗號(hào)（,）分隔，此時(shí)，先按照第一個(gè)字段進(jìn)行分組，只有當(dāng)?shù)谝粋€(gè)字段內(nèi)容相同時(shí)，才會(huì)進(jìn)一步按照第二個(gè)字段分組，多個(gè)字段執(zhí)行方式相同。3.2.1認(rèn)識(shí)groupby子句任務(wù)必備知識(shí)【任務(wù)3.2.3】查詢(xún)成績(jī)表，求選修的各門(mén)課程的平均成績(jī)和選修該課程的人數(shù)mysql>selectc_no,avg(mark)as'平均成績(jī)',count(*)as'選修人數(shù)' ->fromscore ->groupbyc_no;3.2.1認(rèn)識(shí)groupby子句任務(wù)必備知識(shí)【任務(wù)3.2.4】查詢(xún)成績(jī)表score，求每個(gè)學(xué)生成績(jī)的最高分和最低分和總分mysql>selects_no,max(mark)as最高分,min(mark)as最低分,sum(mark)as總分

->fromscore ->groupbys_no;3.2.1認(rèn)識(shí)groupby子句任務(wù)必備知識(shí)【任務(wù)3.2.5】查詢(xún)學(xué)生表，統(tǒng)計(jì)各系學(xué)生人數(shù),包含人數(shù)匯總mysql>selectd_no,count(*)as人數(shù)

->fromstudent ->groupbyd_nowithrollup;3.2.1認(rèn)識(shí)groupby子句提示：當(dāng)沒(méi)有通過(guò)GROUPBY明確指定分組時(shí)，默認(rèn)所有的數(shù)據(jù)屬于同一個(gè)組，因此，SELECT語(yǔ)句中依然可以使用聚合函數(shù)。任務(wù)必備知識(shí)想一想Having子句功能？任務(wù)必備知識(shí)3.2.2認(rèn)識(shí)having子句使用having子句的功能與where子句類(lèi)似，不同的是where子句作用在整個(gè)表，且在分組之前執(zhí)行，而having子句作用于各個(gè)分組，即必須配合groupby才能使用，因此在where之后執(zhí)行。任務(wù)必備知識(shí)【任務(wù)3.2.6】查詢(xún)成績(jī)表score中選修了2門(mén)以上課程的學(xué)生的學(xué)號(hào)mysql>selects_no,count(*)as選修課數(shù)

->fromscore ->groupbys_no ->havingcount(*)>2;3.2.2認(rèn)識(shí)having子句任務(wù)必備知識(shí)【任務(wù)3.2.7】查詢(xún)各系部男生人數(shù)超過(guò)2人的信息mysql>selectd_no,count(*)as男生人數(shù)

->fromstudent ->wheres_sex='男' ->groupbyd_no ->havingcount(*)>2;3.2.2認(rèn)識(shí)having子句任務(wù)必備知識(shí)或者如下實(shí)現(xiàn)：mysql>selectd_no,count(*)as男生人數(shù)

->fromstudent ->groupbyd_no,s_sex ->havings_sex='男'andcount(*)>2;3.2.2認(rèn)識(shí)having子句提示：需要注意，s_sex出現(xiàn)在having中的前提是s_sex作為分組字段出現(xiàn)在groupby中，否則便會(huì)出現(xiàn)錯(cuò)誤。任務(wù)必備知識(shí)想一想orderby子句功能？任務(wù)必備知識(shí)3.2.3認(rèn)識(shí)orderby子句使用orderby子句后，可以保證結(jié)果中的行按一定順序排列。語(yǔ)法格式如下。orderby{列|表達(dá)式|正整數(shù)}[asc|desc],...任務(wù)必備知識(shí)【任務(wù)3.2.8】按成績(jī)降序排序列出選修AOO1課程的學(xué)生學(xué)號(hào)和成績(jī)，SQL語(yǔ)句如下mysql>selects_no,mark ->fromscore ->wherec_no='A001' ->orderbymarkdesc;3.2.3認(rèn)識(shí)orderby子句任務(wù)必備知識(shí)【任務(wù)3.2.9】按系部（升序）和出生日期（降序）排序mysql>selects_no,s_name,s_sex,s_birth,d_no,s_address,s_phone ->fromstudent ->orderbyd_no,s_birthdesc;3.2.3認(rèn)識(shí)orderby子句任務(wù)必備知識(shí)3.2.3認(rèn)識(shí)orderby子句或者如下實(shí)現(xiàn)：mysql>selects_no,s_name,s_sex,s_birth,d_no,s_address,s_phone ->fromstudent ->orderby5,4desc;任務(wù)必備知識(shí)【任務(wù)3.2.10】按學(xué)生的平均成績(jī)從高到低排序，顯示學(xué)號(hào)和平均成績(jī)mysql>selects_noas‘學(xué)號(hào)’,avg(mark)as‘平均成績(jī)’

->fromscore ->groupbys_no ->orderbyavg(mark)desc;3.2.3認(rèn)識(shí)orderby子句任務(wù)必備知識(shí)想一想Limit子句功能？任務(wù)必備知識(shí)3.2.4認(rèn)識(shí)limit子句limit子句主要用于限制被select語(yǔ)句返回的行數(shù)。語(yǔ)法格式：

limit{[偏移量,]行數(shù)|行數(shù)offset偏移量}任務(wù)必備知識(shí)【任務(wù)3.2.11】查詢(xún)課程號(hào)為‘A001’成績(jī)前五名mysql>selects_no,mark ->fromscore ->wherec_no='A001' ->orderbymarkdesc ->limit5;3.2.4認(rèn)識(shí)limit子句任務(wù)必備知識(shí)如果要查詢(xún)成績(jī)倒數(shù)5名的學(xué)生怎么使用SQL語(yǔ)句？分析與討論任務(wù)必備知識(shí)【任務(wù)3.2.12】查詢(xún)課程號(hào)為‘A001’，成績(jī)第5名至第10名的學(xué)生mysql>selects_no,mark ->fromscore ->wherec_no='A001’ ->orderbymarkdesc ->limit6offset4;3.2.4認(rèn)識(shí)limit子句任務(wù)實(shí)施在rsgl數(shù)據(jù)庫(kù)，執(zhí)行如下查詢(xún):1.查詢(xún)各部門(mén)男、女人數(shù)，結(jié)果顯示如下：:任務(wù)實(shí)施2.查詢(xún)男性大于等于2人的部門(mén)的編號(hào)、男性人數(shù)，按人數(shù)降序顯示，結(jié)果如下:任務(wù)實(shí)施3.查詢(xún)所有員工的平均基本工資、最低基本工資和最高基本工資，結(jié)果顯示如下:任務(wù)實(shí)施4.查詢(xún)員工的基本工資和福利，按基本工資倒序，福利正序顯示，結(jié)果如下:任務(wù)實(shí)施5.查詢(xún)基本工資前三名的員工的編號(hào)和基本工資，結(jié)果顯示如下:任務(wù)實(shí)施6.查詢(xún)基本工資排在第4位到第7位的員工的編號(hào)和基本工資，結(jié)果顯示如下：任務(wù)實(shí)施要求撰寫(xiě)實(shí)訓(xùn)總結(jié)，把