教案首頁(yè)課程名稱(chēng)MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā)本節(jié)課題任務(wù)3.2數(shù)據(jù)表的高級(jí)查詢(xún)授課方式理實(shí)一體化教學(xué)參考及教具姜云橋主編.MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā).臨科院：教務(wù)處,2023計(jì)算機(jī)MySQL數(shù)據(jù)庫(kù)軟件多媒體教學(xué)系統(tǒng)資料包教學(xué)目標(biāo)及基本要求按照要求完成各種分組的設(shè)置。按照要求完成分組的過(guò)濾。按照指定要求完成排序。返回指定數(shù)量、位置的數(shù)據(jù)。掌握groupby的使用。掌握having的使用，理解having與where的區(qū)別。掌握orderby的各種排序方式。掌握l(shuí)imit的使用方式教學(xué)重點(diǎn)教學(xué)難點(diǎn)字符匹配方面的查詢(xún)、多表的查詢(xún)多表的查詢(xún)教學(xué)小結(jié)作業(yè)及要求按照任務(wù)書(shū)做好預(yù)習(xí)任務(wù)對(duì)數(shù)據(jù)表進(jìn)行查詢(xún)操作教后反思注意：用微課視頻每個(gè)典型操作做示范

教案用紙教學(xué)內(nèi)容、方法和過(guò)程附記任務(wù)3.2數(shù)據(jù)表的高級(jí)查詢(xún)【任務(wù)背景】 查詢(xún)和統(tǒng)計(jì)數(shù)據(jù)是數(shù)據(jù)庫(kù)的基本功能。在數(shù)據(jù)庫(kù)實(shí)際操作中，經(jīng)常遇到類(lèi)似的查詢(xún)，例如，查詢(xún)成績(jī)?cè)?0～90之間的學(xué)生；查詢(xún)姓李的學(xué)生。查詢(xún)選了李明老師，成績(jī)?cè)?0分以上的學(xué)生姓名；統(tǒng)計(jì)各系、各專(zhuān)業(yè)人數(shù)；查詢(xún)成績(jī)前10名的學(xué)生等。這些查詢(xún)有些是簡(jiǎn)單的單表查詢(xún)，有些是字符匹配方面的查詢(xún)，有些是基于多表的查詢(xún)，有些是要使用函數(shù)進(jìn)行統(tǒng)計(jì)，對(duì)于多表查詢(xún)，可以使用連接查詢(xún)和嵌套子查詢(xún)的辦法來(lái)實(shí)現(xiàn)?！救蝿?wù)要求】基于學(xué)生表student和成績(jī)表score，完成數(shù)據(jù)的分組，包括按照單字段和多字段進(jìn)行分組；按照指定方式，完成分組的過(guò)濾；完成數(shù)據(jù)的排序，按照指定字段指定方式；完成限制返回指定位置、指定數(shù)量的記錄的查詢(xún)?！救蝿?wù)必備知識(shí)】FROM子句?FROM子句：指定要查詢(xún)的表，可以指定兩個(gè)以上的表，表與表之間用逗號(hào)隔開(kāi)。WHERE子句?GROUPBY?WHERE子句：指定要查詢(xún)的條件。 GROUPBY：子句用于對(duì)查詢(xún)結(jié)構(gòu)進(jìn)行分組。HAVING子句?HAVING子句：指定分組的條件，通常在GROUPBY子句之后。ORDERBY子句?LIMIT子句?ORDERBY子句：用于對(duì)查詢(xún)結(jié)果進(jìn)行排序。 LIMIT子句：限制查詢(xún)的輸出結(jié)果行。3.2.1認(rèn)識(shí)GROUPBY子句GROUPBY子句主要根據(jù)字段對(duì)行分組。例如，根據(jù)學(xué)生所學(xué)的專(zhuān)業(yè)對(duì)STUDENTS表中的所有行分組，結(jié)果是每個(gè)專(zhuān)業(yè)的學(xué)生成為一組。GROUPBY子句的語(yǔ)法格式如下。GROUPBY{字段名|表達(dá)式|正整數(shù)}【ASC|DESC】,...【W(wǎng)ITHROLLUP】說(shuō)明 （1）GROUPBY子句后通常包含列名或表達(dá)式。也可以用正整數(shù)表示列，如指定3，則表示按第3列分組。（2）ASC為升序，DESC降序，系統(tǒng)默認(rèn)為ASC，將按分組的第一列升序排序輸出結(jié)果。（3）可以指定多列分組。若指定多列分組，則先按指定的第一列分組再對(duì)指定的第二列分組，以此類(lèi)推。（4）使用帶ROLLUP操作符的GROUPBY子句：指定在結(jié)果集內(nèi)不僅包含由GROUPBY提供的正常行，還包含匯總行?！救蝿?wù)3.2.1】按系統(tǒng)計(jì)各系的學(xué)生人數(shù)，SQL語(yǔ)句如下。mysql>SELECTD_NO,COUNT(*)AS各系人數(shù) FROMSTUDENTS GROUPBYD_NO;【任務(wù)3.2.2】統(tǒng)計(jì)各系男女生人數(shù)。SQL語(yǔ)句如下。mysql>SELECTD_NOAS系別,SEXAS性別,COUNT(*)AS人數(shù) FROMSTUDENTS GROUPBYD_NO,SEX;【任務(wù)3.2.3】求選修的各門(mén)課程的平均成績(jī)和選修該課程的人數(shù)mysql>SELECTC_NO,AVG(REPORT)AS平均成績(jī),COUNT(C_NO)AS選修人數(shù) FROMSCORE GROUPBYC_NO;【任務(wù)3.2.4】查詢(xún)成績(jī)表score，求每個(gè)學(xué)生成績(jī)的最高分和最低分【任務(wù)3.2.5】查詢(xún)學(xué)生表，統(tǒng)計(jì)各系學(xué)生人數(shù),包含人數(shù)匯總。GROUPBY子句通常與聚合函數(shù)（COUNT()、SUM()、AVG()、MAX()和MIN()）一起使用，有關(guān)聚合函數(shù)的用法將在任務(wù)11中詳細(xì)介紹。3.2.2認(rèn)識(shí)HAVING子句使用HAVING子句的目的與WHERE子句類(lèi)似，不同的是WHERE子句是用來(lái)在FROM子句之后選擇行，而HAVING子句用來(lái)在GROUPBY子句后選擇行。語(yǔ)法格式如同WHERE?！救蝿?wù)3.2.6】選修了2門(mén)以上課的學(xué)生學(xué)號(hào)。mysql>SELECTS_NO FROMSCORE GROUPBYS_NO HAVINGCOUNT(S_NO)>2;【任務(wù)3.2.7】查找講授了2門(mén)課的老師。mysql>SELECTT_NOFROMTEACH GROUPBYT_NO HAVINGCOUNT(*)>=2;3.2.3認(rèn)識(shí)ORDERBY子句使用ORDERBY子句后，可以保證結(jié)果中的行按一定順序排列。 語(yǔ)法格式如下。 ORDERBY{列|表達(dá)式|正整數(shù)}【ASC|DESC】,...說(shuō)明 （1）ORDERBY子句后可以是一個(gè)列、一個(gè)表達(dá)式，也可以用正整數(shù)表示列，如指定3，則表示按第3列排序。（2）關(guān)鍵字ASC表示升序排列，DESC表示降序排列，系統(tǒng)默認(rèn)值為ASC。 （3）指定要排序的列可以多列。如果多列，系統(tǒng)先按照第一列排序，當(dāng)該列出現(xiàn)重復(fù)值時(shí)，按第二列排序，以此類(lèi)推?！救蝿?wù)3.2.8】按成績(jī)降序排序列出選修AOO1課程的學(xué)生學(xué)號(hào)和成績(jī)，SQL語(yǔ)句如下。 mysql>SELECTS_NO,REPORT FROMSCOREWHEREC_NO='A001' ORDERBYREPORTDESC;【任務(wù)3.2.9】按系部和出生日期排序，SQL語(yǔ)句如下。mysql>SELECT* FROMSTUDENTS ORDERBY5,4DESC;說(shuō)明 上面任務(wù)中ORDERBY5,4中的5表示第5列(d_NO),4表示第4列(birthday)。【任務(wù)3.2.10】按學(xué)生的平均成績(jī)從高到低排序，顯示學(xué)號(hào)和平均成績(jī)mysql>SELECTS_NO,AVG(REPORT) FROMSCOREGROUPBYS_NO ORDERBYAVG(REPORT);3.2.4認(rèn)識(shí)LIMIT子句Ｌimit子句主要用于限制被SELECT語(yǔ)句返回的行數(shù)。 語(yǔ)法格式： LIMIT{【偏移量,】行數(shù)|行數(shù)OFFSET偏移量}例如，“LIMIT5”表示返回SELECT語(yǔ)句的結(jié)果集中最前面5行，而“LIMIT3，5”則表示從第4行開(kāi)始返回5行。值得注意的是初始行的偏移量為0而不是1?！救蝿?wù)3.2.11】查詢(xún)課程號(hào)為‘A101’成績(jī)前五名mysql>SELECTS_NO,REPORT FROMSCORE WHEREC_NO=’A001’ORDERBYREPORTDESCLIMIT5;分析與討論如果要查詢(xún)成績(jī)倒數(shù)5名的學(xué)生，可以用下面的SQL語(yǔ)句。SELECTS_NO,REPORTFROMSCOREORDERBYREPORTLIMIT5;【任務(wù)3.2.12】查詢(xún)成績(jī)第5名至第10名的學(xué)生。mysql>SELECTS_NOFROMSCOREORDERBYREPORTLIMIT4,10;3.2.5認(rèn)識(shí)FROM子句SELECT的查詢(xún)對(duì)象由FROM子句指定。FROM子句指定進(jìn)行查詢(xún)的單個(gè)表或者多個(gè)表。FROM子句要查詢(xún)的數(shù)據(jù)源還可以來(lái)自視圖，視圖相當(dāng)于一個(gè)臨時(shí)表。其語(yǔ)法結(jié)構(gòu)如下。FROM{表名｜視圖}【，…n】 當(dāng)有多個(gè)表時(shí)，表與表之間用“，”分隔。【任務(wù)3.2.12】查詢(xún)男生基本情況的SQL語(yǔ)句如下。mysql>SELECT

*FROMSTUDENTS WHERESEX='男';【任務(wù)3.2.13】查詢(xún)學(xué)生的姓名、系部名稱(chēng)和聯(lián)系地址SQL語(yǔ)句如下 mysql>SELECTS_NAMEAS‘姓名’,D_NAMEAS‘系部’,addressAS‘地址’ FROMSTUDENTS,DEPARTMENTSWHEREDEPARTMENTS.D_NO=STUDENTS.D_NO;分析與討論任務(wù)3.1.6要查詢(xún)的信息來(lái)自于2張表，它們?cè)贔ROM后面指定，并用逗號(hào)隔開(kāi)，WHERE后面指明表與表之間的全連接（后面會(huì)詳細(xì)講述）。3.2.6認(rèn)識(shí)WHERE子句WHERE子句指定查詢(xún)的條件，限制返回的數(shù)據(jù)行。WHERE子句必須緊跟FROM子句之后，在WHERE子句中，使用一個(gè)條件從FROM子句的中間結(jié)果中選取行。其語(yǔ)法格式如下。 WHEREwhere_definition 其中，where_definition為查詢(xún)條件。WHERE子句用于指定條件，過(guò)濾不符合條件的數(shù)據(jù)記錄。可以使用的條件包括比較運(yùn)算、邏輯運(yùn)算、范圍、模糊匹配以及未知值等。說(shuō)明 IN關(guān)鍵字既可以指定范圍，也可以表示子查詢(xún)。【任務(wù)3.2.14】查詢(xún)選修了A001課程且成績(jī)?cè)?0分以上的學(xué)生SQL語(yǔ)句如下。 mysql>SELECT* FROMSCOREWHEREC_NO='A001'ANDREPORT>=80;【任務(wù)3.2.15】查詢(xún)出生日期在1992年5月出生的學(xué)生。mysql>SELECT* FROMSTUDENTS WHEREBIRTHDAYBETWEEN'1992-5-1'AND'1992-5-31';【任務(wù)3.2.16】查詢(xún)?cè)合稻幪?hào)為D001或D002的學(xué)生。mysql>SELECT* FROMSTUDENTS WHERED_NOIN('D001','D002'); 程序運(yùn)行結(jié)果如圖3.1.10所示。【任務(wù)3.2.17】查詢(xún)成績(jī)?cè)?0～70之間的學(xué)生和課程信息。mysql>SELECT* FROMscore WHEREreportBETWEEN60AND70;【任務(wù)3.2.18】查詢(xún)電話不為空的學(xué)生信息。mysql>SELECT* FROMSTUDENTS WHEREPHONEISNOTNULL; 【任務(wù)3.2.19】查詢(xún)姓李的學(xué)生信息。mysql>SELECT*FROMSTUDENTSWHERES_NAMELIKE’李%’;【項(xiàng)目實(shí)踐】在rsgl數(shù)據(jù)庫(kù)，執(zhí)行如下查詢(xún)。1.查詢(xún)各部門(mén)男、女人數(shù)，結(jié)果顯示如下：2.查詢(xún)男性大于等于2人的部門(mén)的編號(hào)、男性人數(shù)，按人數(shù)降序顯示，結(jié)果如下：+++|d_id|count|+++|B001|3||B003|3||A004|2||B005|2|+++3.查詢(xún)所有員工的平均基本工資、最低基本工資和最高基本工資，結(jié)果顯示如下：++++|平均|最低|最高|++++|2730.000000|1800.00|4500.00|++++4.查詢(xún)員工的基本工資和福利，按基本工資倒序，福利正序顯示，結(jié)果如下：++++|e_id|base|bonus|++++|100331|4500.00|5600.00||100330|3500.00|5000.00||100101|3000.00|5000.00||100105|2600.00|3500.00||100103|2600.00|4500.00||100102|2500.00|4500.00||100106|2400.00|4300.00||100104|2400.00|4600.00||100100|2000.00|4000.00||100332|1800.00|3600.00|++++5.查詢(xún)基本工資前三名的員工的編號(hào)和基本工資，結(jié)果顯示如下：+++|e_id|base|+++|100331|