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

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

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