MySQL數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用（微課版） 課件 模塊3 數(shù)據(jù)庫(kù)查詢

BYYUSHEN數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用DATABASETECHNOLOGYANDAPPLICATION任務(wù)1單表數(shù)據(jù)查詢?nèi)蝿?wù)2多表連接查詢?nèi)蝿?wù)3限行與排序查詢項(xiàng)目模塊3數(shù)據(jù)庫(kù)

查詢篇任務(wù)4分組統(tǒng)計(jì)查詢與分析查詢?nèi)蝿?wù)5子查詢?nèi)蝿?wù)6聯(lián)合查詢與逐行查詢?nèi)蝿?wù)7視圖簡(jiǎn)化查詢?nèi)蝿?wù)8索引加速查詢CONTENTSBYYUSHENYOURLOGOAUNIVERSITY01任務(wù)1單表數(shù)據(jù)查詢點(diǎn)擊查看本任務(wù)知識(shí)技術(shù)點(diǎn)02任務(wù)2多表連接查詢點(diǎn)擊查看本任務(wù)知識(shí)技術(shù)點(diǎn)03任務(wù)3限行查詢與排序查詢點(diǎn)擊查看本任務(wù)知識(shí)技術(shù)點(diǎn)04任務(wù)4分組統(tǒng)計(jì)與分析查詢點(diǎn)擊查看本任務(wù)知識(shí)技術(shù)點(diǎn)05任務(wù)5子查詢點(diǎn)擊查看本任務(wù)知識(shí)技術(shù)點(diǎn)06任務(wù)6聯(lián)合查詢與逐行查詢點(diǎn)擊查看本任務(wù)知識(shí)技術(shù)點(diǎn)07任務(wù)7使用視圖簡(jiǎn)化查詢點(diǎn)擊查看本任務(wù)知識(shí)技術(shù)點(diǎn)08任務(wù)8使用索引加速查詢點(diǎn)擊查看本任務(wù)知識(shí)技術(shù)點(diǎn)BYYUSHEN崗位能力能使用SQL命令和Workbench圖形化工具實(shí)現(xiàn)對(duì)一張或多張數(shù)據(jù)表進(jìn)行簡(jiǎn)單查詢、

連接查詢、嵌套子查詢、聯(lián)合查詢、排序或限量查詢、分組統(tǒng)計(jì)查詢的操作方法；能理解視圖的概念、特點(diǎn)及使用原則；能理解索引的概念、作用、存儲(chǔ)及分類；能使用SQL命令和Workbench圖形化工具為數(shù)據(jù)庫(kù)建立管理適當(dāng)?shù)囊晥D與索引；技能證書(shū)標(biāo)準(zhǔn)解答客戶對(duì)表數(shù)據(jù)各類查詢的理論和操作問(wèn)題；根據(jù)客戶需求運(yùn)用SQL的DML語(yǔ)言編寫(xiě)數(shù)據(jù)查詢語(yǔ)句；推薦客戶使用合理的視圖與索引高效查詢方案；思政素養(yǎng)養(yǎng)成注重細(xì)節(jié)、精益求精的職業(yè)操守；對(duì)解決問(wèn)題積極探索、尋求高效、簡(jiǎn)明、優(yōu)化手段的工匠精神；保護(hù)數(shù)據(jù)的同時(shí)還要認(rèn)識(shí)到片面性問(wèn)題，用辯證的思維看待事物；數(shù)據(jù)庫(kù)查詢項(xiàng)目模塊3

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用BYYUSHEN高校教學(xué)質(zhì)量分析管理系統(tǒng)數(shù)據(jù)庫(kù)db_teachingG-EDU格諾博教育公司開(kāi)發(fā)“高校教學(xué)質(zhì)量分析管理系統(tǒng)”，應(yīng)能為學(xué)校的質(zhì)量督導(dǎo)部門、教師、學(xué)生提供對(duì)評(píng)學(xué)、評(píng)教以及教學(xué)相關(guān)基礎(chǔ)數(shù)據(jù)的查詢服務(wù)，獲得查詢統(tǒng)計(jì)分析報(bào)表。根據(jù)學(xué)校用戶提供的限定條件與要求，系統(tǒng)通過(guò)MySQL的SELECT命令，從后臺(tái)數(shù)據(jù)庫(kù)中相應(yīng)的一個(gè)表或多個(gè)表中，運(yùn)用連接、子查詢、排序、分組統(tǒng)計(jì)、聯(lián)合查詢等不同方式，提取有效數(shù)據(jù)，并設(shè)置合理的視圖與索引方案，來(lái)簡(jiǎn)化和優(yōu)化加速對(duì)數(shù)據(jù)的查詢統(tǒng)計(jì)分析。項(xiàng)目模塊情境數(shù)據(jù)庫(kù)查詢項(xiàng)目模塊3

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用BYYUSHEN01任務(wù)一單表數(shù)據(jù)查詢通過(guò)“高校教學(xué)質(zhì)量分析管理系統(tǒng)”，質(zhì)量管理處、教務(wù)處、各二級(jí)學(xué)院等教學(xué)相關(guān)部門以及教師本人，需通過(guò)查看某個(gè)學(xué)期（如2019-2020學(xué)年一）指定教師（如000010號(hào)教師）的同行及督導(dǎo)專家的教學(xué)評(píng)價(jià)評(píng)語(yǔ)、評(píng)分情況，作為對(duì)該教師教學(xué)質(zhì)量評(píng)判的指標(biāo)參考之一，所需查詢的數(shù)據(jù)只源自教師教學(xué)評(píng)價(jià)表這一個(gè)表中。任務(wù)描述BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.1簡(jiǎn)單基本SELECT查詢語(yǔ)句任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLESELECT[ALL|DISTINCT]*|列表達(dá)式

,...FROM表名[WHERE條件表達(dá)式

];查什么從哪查查啥樣

SELECT子句：投影列，即選擇查詢結(jié)果集中要輸出顯示的列字段或表達(dá)式，及輸出顯示的順序。在SELECT子句的查詢列表中，列字段的順序是可以改變的，無(wú)需按照數(shù)據(jù)表中定義的順序排列。FROM子句：指定表，即選擇查詢的數(shù)據(jù)源表。WHERE子句：選擇行，即選擇查詢的行記錄的條件。BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.2投影列任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLESELECT*FROM表名

[WHERE條件表達(dá)式

];1查詢表中所有列SELECT子句中，“*”代替數(shù)據(jù)表中的所有列，查詢結(jié)果集中輸出顯示順序與源表中列順序相同。查詢課程信息表tb_course中的所有課程信息mysql>

SELECT*FROMtb_course;BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.2投影列任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLESELECT列表達(dá)式1,列表達(dá)式2,…FROM表名

[WHERE條件表達(dá)式

];2查詢表中指定列SELECT子句中，指定要查詢的列，各列名之間用英文逗號(hào)隔開(kāi)，指定各列的順序即顯示結(jié)果集列呈現(xiàn)的順序。查詢課程信息表tb_course中所有課程的課程號(hào)、課程名稱和學(xué)分。mysql>

SELECTCourse_no,Course,CreditFROMtb_course;BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.2投影列任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE3查詢表達(dá)式計(jì)算列SELECT子句中，指定要查詢顯示的計(jì)算表達(dá)式結(jié)果，各表達(dá)式之間用英文逗號(hào)隔開(kāi)。查詢課程信息表tb_course中所有課程的課程號(hào)、課程名稱、學(xué)分若增加0.5分的結(jié)果、以及按16教學(xué)周計(jì)算課程所需的周課時(shí)數(shù)。mysql>

SELECTCourse_no,Course,credit+0.5,Class_hour/16FROMtb_course;BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.2投影列任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE4查詢顯示列別名默認(rèn)情況下，查詢結(jié)果集中顯示的列名，就是SELECT子句指定的查詢列或表達(dá)式的名稱。如果希望查詢結(jié)果集中的列名稱顯示為自定義的列名稱時(shí)，可以在SELECT子句中自定義列或表達(dá)式的別名。查詢課程信息表tb_course中所有課程的課程號(hào)、課程名稱、學(xué)分若增加0.5分的結(jié)果、以及按16教學(xué)周計(jì)算課程所需的周課時(shí)數(shù)。表頭的學(xué)分和周課時(shí)數(shù)列名顯示為credit和weekclass_hour。mysql>

SELECTCourse_no,Course,Credit+0.5ASCredit,Class_hour/16AS‘Weekclass_hour’FROMtb_course;SELECT列表達(dá)式1AS列別名|列表達(dá)式2列別名,…FROM表名

[WHERE條件表達(dá)式

];BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.2投影列任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE4查詢顯示列別名SELECT子句中設(shè)置列別名，改變的只是查詢結(jié)果集所顯示的列標(biāo)題，并沒(méi)有改變數(shù)據(jù)表中的列名。當(dāng)自定義的列別名中含有空格時(shí)，必須用引號(hào)將列別名括起來(lái)。BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.2投影列任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE5查詢過(guò)濾重復(fù)值的列

SELECT子句中，可在指定的一個(gè)列名前使用DISTINCT關(guān)鍵詞，獲取該列去除了重復(fù)值的結(jié)果集。查詢課程表tb_course中，學(xué)校所開(kāi)設(shè)的課程類型。mysql>

SELECTDISTINCTCategoryFROMtb_course;SELECTDISTINCT

列名FROM表名

[WHERE條件表達(dá)式

];使用DISTINC關(guān)鍵字過(guò)濾重復(fù)值的列只能指定一個(gè)。如果列值有多個(gè)為NULL，DISTINCT關(guān)鍵字會(huì)把這些NULL視為相同重復(fù)。BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.3選擇行任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE1查詢表中所有行

省略WHERE條件子句的SELECT查詢語(yǔ)句，默認(rèn)查詢表中的所有數(shù)據(jù)行。查詢?nèi)藬?shù)在45～50人之間的班級(jí)信息。mysql>

SELECTClass_No,Class_Name,Per_QuantityFROMTB_Class

->

WHEREPer_QuantityBETWEEN45AND50;2查詢篩選滿足條件的行使用比較運(yùn)算符、IN運(yùn)算符、BETWEENAND運(yùn)算符、LIKE運(yùn)算符、REGEXP正則運(yùn)算符、NOT、AND、OR運(yùn)算符定義的條件表達(dá)式，在WHERE子句中表示篩選行記錄的條件。BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.3選擇行任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE查詢?cè)u(píng)學(xué)評(píng)教表tb_grade中，還未進(jìn)行評(píng)教的學(xué)生的學(xué)號(hào)、課程編號(hào)及任課教師編號(hào)、評(píng)教分?jǐn)?shù)信息。mysql>

SELECTStu_no,Course_no,Teacher_no,Teach_evalu_scoreFROMtb_grade->WHERETeach_evalu_scoreISNULL;3查詢含空值的行使用IS[NOT]NULL運(yùn)算符，在WHERE子句中可篩選指定列值為（不為）空的行記錄。查詢教研室表tb_staffroom中，已安排了教研室主任人選的教研室。mysql>

SELECT*FROMtb_staffroomWHEREDirectorISNOTNULL;BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.1.4MySQL常用系統(tǒng)函數(shù)任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE1常用數(shù)值處理函數(shù)BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE查詢課程表tb_course中，所有公共基礎(chǔ)課的整數(shù)學(xué)分（四舍五入）mysql>SELECTCourse_No,Course,Category,ROUND(Credit,0)FROMtb_course

->WHERECategory='公共基礎(chǔ)課';3.1.4MySQL常用系統(tǒng)函數(shù)BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE2常用字符串處理函數(shù)3.1.4MySQL常用系統(tǒng)函數(shù)01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE查詢督導(dǎo)專家表tb_expert中，所有姓“王”的督導(dǎo)專家信息。mysql>SELECT*FROMtb_expertWHEREExpert_NameLIKE'王%';或者：mysql>SELECT*FROMtb_expert->WHEREExpert_NameREGEXP'王*';或者：mysql>SELECT*FROMtb_expert->WHERELEFT(Expert_Name,1)='王';mysql>

SELECT*FROMtb_expert->WHERESUBSTRING(Expert_Name,1,1)='王;mysql>

SELECT*FROMtb_expert->WHEREINSTR(Expert_Name,'王')=1;mysql>SELECT*FROMtb_expert->WHERELOCATE('王',Expert_Name)=1;或者：或者：或者：3.1.4MySQL常用系統(tǒng)函數(shù)BYYUSHEN01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE3常用日期時(shí)間獲取函數(shù)3.1.4MySQL常用系統(tǒng)函數(shù)01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE4日期加減函數(shù)3.1.4MySQL常用系統(tǒng)函數(shù)01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE查詢教師表tb_teacher中，工齡未滿20年的教師的編號(hào)、姓名、工作年份。Mysql>SELECTTeacher_No,Teacher_Name,YEAR(Work_Date)FROMtb_teacher

->

WHEREYEAR(CURDATE())-YEAR(Work_Date)<20;或者：mysql>SELECTTeacher_No,Teacher_Name,YEAR(Work_Date)FROMtb_teacher

->WHEREEXTRACT(yearfromCURDATE())-EXTRACT(yearfromWork_Date)<20;mysql>SELECTTeacher_No,Teacher_Name,YEAR(Work_Date)FROMtb_teacher

->WHEREADDDATE(Work_Date,interval20year)>CURDATE();或者：或者：mysql>

SELECTTeacher_No,Teacher_Name,YEAR(Work_Date)FROMtb_teacher

->WHERESUBDATE(CURDATE(),interval20year)<Work_Date;3.1.4MySQL常用系統(tǒng)函數(shù)01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE5數(shù)據(jù)類型轉(zhuǎn)換函數(shù)數(shù)據(jù)類型轉(zhuǎn)換函數(shù)功能示例運(yùn)行結(jié)果CONVERT(x,轉(zhuǎn)換后的類型)將數(shù)據(jù)x轉(zhuǎn)換成指定類型CONVERT(410008,char(6))‘410008’CAST(xAS轉(zhuǎn)換后的類型)將數(shù)據(jù)x轉(zhuǎn)換成指定類型CAST(410008ASchar(6))‘410008’3.1.4MySQL常用系統(tǒng)函數(shù)查詢?cè)u(píng)學(xué)評(píng)教成績(jī)表tb_grade，得到大數(shù)據(jù)技術(shù)20級(jí)班級(jí)中（學(xué)號(hào)前8位‘20200403’），評(píng)學(xué)分?jǐn)?shù)達(dá)到85分以上的評(píng)分信息的字符串報(bào)表。即輸出為:大數(shù)據(jù)技術(shù)20級(jí)班級(jí)XXXXXX號(hào)學(xué)生，XXXX號(hào)課程評(píng)學(xué)分?jǐn)?shù)：XXmysql>SELECTCONCAT('大數(shù)據(jù)技術(shù)20級(jí)班級(jí)',Stu_No,'號(hào)學(xué)生,',Course_No,'號(hào)課程評(píng)學(xué)分?jǐn)?shù)：',CONVERT(Score,char(5)))->FROMtb_grade

->WHEREscore>85andleft(Stu_No,8)='20200403';01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE6常用條件控制函數(shù)3.1.4MySQL常用系統(tǒng)函數(shù)01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE查詢教師表tb_teacher中，副高以下職稱的教師的職稱與學(xué)歷，助教的職稱級(jí)別為初級(jí)，講師的職稱級(jí)別為中級(jí)。mysql>SELECTTeacher_NoAS教師號(hào),Teacher_NameAS教師名,Positional_TitleAS職稱,

->

IF(Positional_Title='助教','初級(jí)','中級(jí)')AS職稱級(jí)別,Edu_Background學(xué)歷

->FROMtb_teacher

->WHEREPositional_TitleNOTLIKE'%教授%';或者：mysql>SELECTTeacher_NoAS教師號(hào),Teacher_NameAS教師名,Positional_TitleAS職稱,

->

CASEPositional_Title

->

WHEN'助教'THEN'初級(jí)’

->WHEN'講師'THEN'中級(jí)’

->

END

AS職稱級(jí)別,

->Edu_Background學(xué)歷

->FROMtb_teacher

->WHEREPositional_TitleNOTLIKE'%教授%';3.1.4MySQL常用系統(tǒng)函數(shù)01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE查詢教師教學(xué)評(píng)價(jià)表tb_teach_evaluation中，督導(dǎo)專家對(duì)教師教學(xué)質(zhì)量的評(píng)價(jià)等級(jí)，評(píng)分90分以上的“優(yōu)秀”，80～89分“良好”，60～79分“合格”，60分以下“不合格”。mysql>SELECTTeacher_NoAS教師號(hào),Evalu_ScoreAS評(píng)分,

->

IF(Evalu_Score>=90,'優(yōu)秀',IF(Evalu_Score>=80,'良好’,IF(Evalu_Score>=60,'合格','不合格')))

->AS評(píng)價(jià)等級(jí)

->FROMtb_teach_evaluation

->WHEREAppraiser='督導(dǎo)專家';或者：mysql>mysql>SELECTTeacher_NoAS教師號(hào),Evalu_ScoreAS評(píng)分,

->

CASE

->WHENEvalu_Score>=90THEN'優(yōu)秀'

->WHENEvalu_Score>=80THEN'良好'

->WHENEvalu_Score>=60THEN'合格'

->ELSE'不合格'

->ENDAS評(píng)價(jià)等級(jí)

->FROMtb_teach_evaluation

->WHEREAppraiser='督導(dǎo)專家';3.1.4MySQL常用系統(tǒng)函數(shù)01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE7常用JSON操作函數(shù)3.1.4MySQL常用系統(tǒng)函數(shù)01

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)一單表數(shù)據(jù)查詢QUERYDATAINASINGLETABLE創(chuàng)建一個(gè)含JSON類型的數(shù)據(jù)表，并運(yùn)用函數(shù)進(jìn)行JSON字段操作。mysql>CREATETABLEtb_testjson(Idintprimarykeynotnull,D1varchar(50)null,D2jsonnull);mysql>INSERTINTOtb_testjson

->VALUES(1,'1','{"a":1,"a":"2"}');

mysql>INSERTINTOtb_testjson->VALUES(2,'2',JSON_OBJECT("a",1,"b","2"));

mysql>INSERTINTOtb_testjson->VALUES(3,'3',JSON_ARRAY("arr",1,2));

mysql>SELECT*FROMtb_testjson;生成JSON數(shù)組或JSON對(duì)象進(jìn)行表數(shù)據(jù)添加：3.1.4MySQL常用系統(tǒng)函數(shù)合并兩個(gè)JSON進(jìn)行表數(shù)據(jù)添加：mysql>INSERTINTOtb_testjson->VALUES(4,'4',JSON_MERGE_PRESERVE('{"a":4,

->"b":"4"}','{"c":4,"d":"4"}'));mysql>SELECT*FROMtb_testjson;mysql>SELECTJSON_EXTRACT(D2,'$.a')FROMtb_testjson;從D2字段中查詢提取a的值：BYYUSHEN任務(wù)二多表連接查詢學(xué)生、教師、質(zhì)量督導(dǎo)部門通過(guò)“高校教學(xué)質(zhì)量分析系統(tǒng)”，需查看指定學(xué)生在一學(xué)期中各科課程的評(píng)學(xué)考試成績(jī)、所獲績(jī)點(diǎn)，分析其學(xué)習(xí)情況，從而了解學(xué)習(xí)質(zhì)量和有助于學(xué)生制定提升進(jìn)步的學(xué)習(xí)策略計(jì)劃。所需查詢的指定學(xué)生信息、該生所考的課程信息、成績(jī)與績(jī)點(diǎn)信息的數(shù)據(jù)分別源自學(xué)生表、課程表、評(píng)學(xué)評(píng)教成績(jī)表的多個(gè)表中。任務(wù)描述02BYYUSHEN

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.2.1連接查詢語(yǔ)句任務(wù)二多表連接查詢JOINQUERIESFORMULTIPLETABLES02JOIN子句：連接關(guān)鍵字，指定查詢數(shù)據(jù)源表中所連接的表。JOIN為內(nèi)連接類型時(shí)可省略INNER，外連接類型有LEFTOUTER和RIGHTOUTER兩種。ON連接表達(dá)式：通過(guò)兩表間的相關(guān)字段值相等，指定表間連接的關(guān)聯(lián)關(guān)系。CROSS交叉連接時(shí)無(wú)指定連接條件、無(wú)該子句。WHERE子句：選擇行，即選擇查詢的數(shù)據(jù)源表。SELECT[ALL|DISTINCT]*|列表達(dá)式

,...FROM表名1[表別名1][INNER|[LEFT|RIGHT]OUTER|CROSS]JOIN表名2[表別名2]

[ON表名1.關(guān)聯(lián)列=表名2.關(guān)聯(lián)列]...[WHERE條件表達(dá)式];查什么從哪查如何連接哪個(gè)表連接條件

查啥樣

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.2.2內(nèi)連接查詢?nèi)蝿?wù)二多表連接查詢JOINQUERIESFORMULTIPLETABLES02

內(nèi)連接（INNERJOIN）是多表查詢最常用的連接方式，也是MySQL系統(tǒng)默認(rèn)的多表連接方式，

因此INNER關(guān)鍵字可以省略。內(nèi)連接通過(guò)ON或WHERE中指定的表間連接條件，比較兩個(gè)表中公共字段的值，

通常比較主表的主鍵與從表的外鍵的字段值，只有比較值滿足連接條件的作為匹配行返回結(jié)果集。查詢軟件學(xué)院所開(kāi)設(shè)的專業(yè)情況，查詢輸出學(xué)院名稱、學(xué)院編號(hào)、專業(yè)名稱、專業(yè)編號(hào)。mysql>SELECTDepartment,tb_department.Dep_No,Profession,Profession_No

->

FROMtb_departmentJOINtb_profession

->ONtb_department.Dep_No=tb_profession.Dep_No

->WHEREDepartment='軟件學(xué)院';

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.2.2內(nèi)連接查詢

在連接查詢時(shí)，如果要用到的字段是主表和從表中都共有的同名字段，則必須在這樣的共有同名字段的名前加上表名進(jìn)行區(qū)分，用“表名.字段名”來(lái)表示。如果表名太長(zhǎng)，可以在FROM和JOIN關(guān)鍵字的表名后，為表名定義一個(gè)簡(jiǎn)短的別名，這樣在連接查詢語(yǔ)句中，公共字段前的表名標(biāo)識(shí)可用簡(jiǎn)短的別名代替。當(dāng)連接條件為比較運(yùn)算符“=”比較兩表的公共字段值時(shí)，為“等值連接”。等值連接所比較的兩表公共字段的名稱與類型都完全相同時(shí)，還可以使用“USING(字段名)”子句取代ON子句來(lái)連接。任務(wù)二多表連接查詢JOINQUERIESFORMULTIPLETABLES02mysql>SELECT

Department,d.Dep_No,Profession,Profession_No

->

FROMtb_departmentd

JOINtb_professionp

->

ONd.Dep_No=p.Dep_No

->WHEREDepartment='軟件學(xué)院';mysql>SELECTDepartment,d.Dep_No,Profession,Profession_No

->FROMtb_departmentdJOINtb_professionpUSING(Dep_No)

->WHEREDepartment='軟件學(xué)院';

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.2.2內(nèi)連接查詢?nèi)蝿?wù)二多表連接查詢JOINQUERIESFORMULTIPLETABLES02在多個(gè)表進(jìn)行連接查詢時(shí)，可以兩兩表間依次JOIN并ON指定連接條件。內(nèi)連接還有一種特殊情況：將一個(gè)表與其自身進(jìn)行連接，即連接的兩個(gè)表是同一個(gè)表，稱為“自連接”。它是指相互連接的表在物理上為同一個(gè)表，但邏輯上分為兩個(gè)表，所以使用自連接時(shí)，需要為該表指定兩個(gè)別名，并對(duì)所有字段列的引用均要前面標(biāo)識(shí)表別名。若要在一個(gè)表中查找具有相同字段列值的行數(shù)據(jù)，可以使用自連接。mysql>SELECTDepartment,d.Dep_No,Profession,Profession_No

->FROMtb_departmentd,tb_professionp

->WHEREDepartment='軟件學(xué)院'ANDd.Dep_No=p.Dep_No;

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.2.3外連接查詢?nèi)蝿?wù)二多表連接查詢JOINQUERIESFORMULTIPLETABLES02

外連接（OUTERJOIN）方式下，可以只限制一個(gè)表，對(duì)另一個(gè)表不加限制，使另一個(gè)表中的所有行都會(huì)顯示在結(jié)果集中。

外連接含左外連接（LEFTOUTERJOIN）、右外連接（RIGHTOUTERJOIN），左右是相對(duì)JOIN關(guān)鍵詞的左表

和右表而言，其中OUTER關(guān)鍵字可以省略。查詢大數(shù)據(jù)技術(shù)2020級(jí)及以后的各班學(xué)生的課程評(píng)學(xué)考試成績(jī)，包括剛?cè)雽W(xué)還沒(méi)考試過(guò)的學(xué)生。mysql>SELECTs.Stu_No,Stu_Name,g.Course_No,Score

->

FROMtb_studentsLEFTJOINtb_gradegONs.Stu_No=g.Stu_No

->WHEREs.Stu_NoREGEXP'202[0-9]0403';mysql>SELECTs.Stu_No,Stu_Name,g.Course_No,Score

->

FROMtb_gradegRIGHTJOINtb_students

ONs.Stu_No=g.Stu_No

->WHEREs.Stu_NoREGEXP'202[0-9]0403';

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.2.4交叉連接任務(wù)二多表連接查詢JOINQUERIESFORMULTIPLETABLES02

交叉連接（CROSSJOIN）是在沒(méi)有ON或WHERE指定兩表間連接條件的情況下，會(huì)把一個(gè)表的所有行與另一個(gè)表

的所有行一一連接組合，構(gòu)成兩個(gè)表中所有數(shù)據(jù)記錄行的笛卡爾積，即結(jié)果集的記錄行數(shù)會(huì)是這兩個(gè)表記錄行數(shù)的乘積M×N查詢每個(gè)二級(jí)學(xué)院可能開(kāi)設(shè)的公共基礎(chǔ)課程情況，即各二級(jí)學(xué)院名稱、公共基礎(chǔ)課程的課程號(hào)、課程名、課程類型。mysql>SELECTDepartment,Course_No,Course,Category

->FROMtb_departmentCROSSJOINtb_course

->WHERECategory='公共基礎(chǔ)課';mysql>SELECTDepartment,Course_No,Course,Category

->FROMtb_department,tb_course

->WHERECategory='公共基礎(chǔ)課';交叉連接產(chǎn)生笛卡爾積的結(jié)果集，在規(guī)范化

的數(shù)據(jù)庫(kù)中并無(wú)太多應(yīng)用價(jià)值。

因?yàn)闆](méi)有指定表間連接條件，所以結(jié)果集會(huì)出現(xiàn)兩表中并不匹配的無(wú)意義的連接行。BYYUSHEN任務(wù)三排序與限行“高校教學(xué)質(zhì)量分析系統(tǒng)”中，無(wú)論教師、學(xué)生、專業(yè)、課程等教學(xué)基礎(chǔ)數(shù)據(jù)，還是各學(xué)期的評(píng)學(xué)與評(píng)教等教學(xué)質(zhì)量數(shù)據(jù)，數(shù)據(jù)量都很大，經(jīng)常需要在查詢查看數(shù)據(jù)時(shí)進(jìn)行排序，使關(guān)心的數(shù)據(jù)顯示到最前面，方便進(jìn)一步分析，還需通過(guò)對(duì)操作的數(shù)據(jù)進(jìn)行限量的方式提高執(zhí)行效率。學(xué)校教學(xué)質(zhì)量督導(dǎo)部門和教務(wù)處在學(xué)期末，要對(duì)督導(dǎo)專家評(píng)分最高的前三位老師進(jìn)行評(píng)優(yōu)，對(duì)指定課程評(píng)學(xué)考試成績(jī)最高的前五位同學(xué)評(píng)定該門課程的學(xué)習(xí)標(biāo)兵，成績(jī)最低的的五位同學(xué)提出對(duì)該門課程學(xué)習(xí)的幫助，需對(duì)相關(guān)數(shù)據(jù)記錄實(shí)行排序和限行操作。任務(wù)描述03BYYUSHEN03

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.3.1ORDERBY排序子句任務(wù)三排序與限行查詢SORTANDLIMITEDQUERIESQUERYDATAINASINGLETABLEORDERBY列名1|表達(dá)式1|列編號(hào)[ASC|DESC][,...n]ORDERBY：可以按多個(gè)關(guān)鍵字進(jìn)行排序，多關(guān)鍵字之間英文逗號(hào)分隔。

查詢結(jié)果會(huì)首先按照第一個(gè)關(guān)鍵字的值排序，對(duì)第一個(gè)關(guān)鍵字值相同的數(shù)據(jù)行，再按第二個(gè)關(guān)鍵字

的值排序，依次類推。在SELECT子句的查詢列表中，列字段的順序是可以改變的，無(wú)需按照數(shù)據(jù)表

中定義的順序排列。ASC：升序排列（也是默認(rèn)排序順序）。含NULL空值的記錄行將最后顯示。DESC：降序排列。含NULL空值的記錄行將最先顯示。ORDERBY子句要寫(xiě)在WHERE子句后面。03

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)三排序與限行查詢SORTANDLIMITEDQUERIESQUERYDATAINASINGLETABLE查詢軟件學(xué)院（學(xué)院編號(hào)0004）所有班級(jí)人數(shù)，按人數(shù)升序排列輸出。3.3.1ORDERBY排序子句mysql>

SELECT*FROMtb_class

->WHERESUBSTRING(Class_No,5,2)=‘04’->ORDERBYPer_Quantity

；查詢指定課程Python程序設(shè)計(jì)（課程編號(hào)900011）的所有考試成績(jī)，按分?jǐn)?shù)從高到低輸出，若有同分的記錄，則按考試學(xué)期時(shí)間從早到晚排列。

mysql>SELECTg.Stu_No,Stu_Name,g.Course_No,Course,

->Score,GPA,g.Test_Term

->FROMtb_coursecJOINtb_gradeg

->ONc.Course_No=g.Course_No

->JOINtb_studentsONg.Stu_No=s.Stu_No

->WHERECourse='Python程序設(shè)計(jì)'

->ORDERBYScoreDESC,Test_Term;03

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.3.2LIMIT限行子句任務(wù)三排序與限行查詢SORTANDLIMITEDQUERIESQUERYDATAINASINGLETABLELIMIT[偏移量n,]

記錄數(shù);

SELECT語(yǔ)句中可以使用LIMIT子句來(lái)限定查詢輸出的記錄數(shù)量，指定查詢結(jié)果集是從哪一條記錄開(kāi)始、及查詢多少行記錄。偏移量：設(shè)置從查詢結(jié)果的哪條記錄開(kāi)始。默認(rèn)第1條記錄的偏移量為0，第2條記錄的偏移量為1，依此類推。記錄數(shù)：表示限定查詢返回的最大記錄數(shù)量。當(dāng)限定的記錄數(shù)大于數(shù)據(jù)表中符合要求的實(shí)際記錄數(shù)量時(shí)，以實(shí)際記錄數(shù)為準(zhǔn)。。LIMIT子句中只限定了記錄數(shù)、沒(méi)有指定偏移量時(shí)，默認(rèn)表示從查詢結(jié)果的第1條記錄開(kāi)始、返回限定數(shù)量的數(shù)據(jù)記錄。LIMIT子句也可與ORDERBY子句連用，要寫(xiě)在WHERE子句、ORDERBY子句后面。03

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)三排序與限行查詢SORTANDLIMITEDQUERIESQUERYDATAINASINGLETABLE查詢顯示班級(jí)開(kāi)課安排表tb_class_course的前3個(gè)班級(jí)排課情況。mysql>

SELECT*FROMtb_class_courseLIMIT33.3.2LIMIT限行子句查詢顯示第10-20行的學(xué)生記錄的學(xué)號(hào)、姓名、登錄名與登錄密碼。mysql>

SELECTStu_No,Stu_Name,Stu_Login_Name,stu_PasswordFROMtb_studentLIMIT9,11;03

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)三排序與限行查詢SORTANDLIMITEDQUERIESQUERYDATAINASINGLETABLE將指定課程（如900011Python程序設(shè)計(jì)）考分最低的5位同學(xué)的成績(jī)?cè)黾?分。mysql>

UPDATEtb_gradeSETScore=Score+3WHERECourse_No='900011’

->ORDERBYScoreLIMIT5;3.3.2LIMIT限行子句刪除最后錄入的5條評(píng)學(xué)成績(jī)記錄。。mysql>

DELETEFROMtb_gradeORDERBYidDESCLIMIT5;

在MySQL中，除了對(duì)SELECT查詢記錄時(shí)進(jìn)行排序和限量外，對(duì)數(shù)據(jù)表中記錄的UPDATE更新和DELETE刪除操作也可以進(jìn)行排序和限量。表示根據(jù)指定字段按順序進(jìn)行更新或刪除符合條件的限定數(shù)量條記錄。如果UPDATE和DELETE操作中沒(méi)有Where條件，Limit子句可用來(lái)直接限制更新和刪除的記錄數(shù)量BYYUSHEN任務(wù)四分組統(tǒng)計(jì)與分析查詢?cè)趯W(xué)期終，教學(xué)督導(dǎo)部門、教務(wù)處需通過(guò)高校教學(xué)質(zhì)量管理系統(tǒng)的數(shù)據(jù)統(tǒng)計(jì)，獲得學(xué)生、教師、班級(jí)、專業(yè)、院系等不同范圍的評(píng)學(xué)評(píng)教評(píng)分統(tǒng)計(jì)報(bào)表結(jié)果等。比如按學(xué)期對(duì)各學(xué)生的平均評(píng)學(xué)成績(jī)與績(jī)點(diǎn)匯總的報(bào)表查詢，對(duì)各專業(yè)或?qū)W院的評(píng)學(xué)成績(jī)均分統(tǒng)計(jì)報(bào)表查詢，等等，實(shí)現(xiàn)一學(xué)期中對(duì)學(xué)生學(xué)習(xí)質(zhì)量的多層級(jí)評(píng)估。任務(wù)描述04BYYUSHEN

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.4.1聚合函數(shù)任務(wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04聚合函數(shù)在統(tǒng)計(jì)參數(shù)字段值時(shí)，默認(rèn)忽略NULL值；COUNT（*）統(tǒng)計(jì)所有記錄行數(shù)（包含NULL值行）。DISTINCT：去除參數(shù)字段值中的重復(fù)值后，再聚合運(yùn)算。ALL：默認(rèn)為ALL，表示對(duì)所有非空值進(jìn)行聚合運(yùn)算。聚合函數(shù)對(duì)于用列的一組值進(jìn)行計(jì)算，然后返回單個(gè)值。

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.4.1聚合函數(shù)任務(wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04根據(jù)tb_grade評(píng)學(xué)評(píng)教成績(jī)表的數(shù)據(jù)，查詢檢索所有參加了考試評(píng)學(xué)的學(xué)生人數(shù)、考查課的成績(jī)數(shù)量、已考課程的門數(shù)、評(píng)學(xué)總分與平均分。mysql>SELECTCOUNT(*),COUNT(Mark),COUNT(DISTINCTCourse_No),SUM(Score),AVG(Score)

->FROMtb_grade;

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.4.2GROUPBY與HAVING子句任務(wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04SELECT

[

ALL

|

DISTINCT

]

分組列表達(dá)式,...，聚合函數(shù)統(tǒng)計(jì)表達(dá)式，...FROM表名1

[

表別名1

][,...][

WHERE條件表達(dá)式]GROUPBY分組依據(jù)列1[,...][WITHROLLUP][HAVING條件表達(dá)式];GROUPBY：按指定列字段的值進(jìn)行分組，列字段值相同的作為一組?？砂炊鄠€(gè)列字段進(jìn)行分組，多個(gè)分組關(guān)鍵字段間英文逗號(hào)(,)分隔。WITHROLLUP：使分組統(tǒng)計(jì)結(jié)果集中不僅包含GROUPBY分組的行，還包含匯總行。

HAVING：對(duì)分組統(tǒng)計(jì)后的數(shù)據(jù)記錄集，再進(jìn)行過(guò)濾篩選

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04查詢統(tǒng)計(jì)學(xué)生信息表tb_student中男生和女生的人數(shù)mysql>SELECTGender,COUNT(Gender)FROMtb_studentGROUPBYGender;GROUPBY子句帶上WITHROLLUP參數(shù)可以輸出分組的匯總值。如果查詢統(tǒng)計(jì)學(xué)生信息表tb_student中男生和女生的人數(shù)和總?cè)藬?shù)：mysql>SELECTGender,COUNT(Gender)FROMtb_studentGROUPBYGenderWITHROLLUP;3.4.2GROUPBY與HAVING子句

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04WHERE子句的篩選條件中不能包含聚合函數(shù)。WHERE只能對(duì)分組前的指定表中的記錄進(jìn)行篩選，所以WHERE子句跟在FROM子句之后、在GROUPBY子句之前。要篩選含聚合函數(shù)的條件，只能通過(guò)WHERE子查詢或GROUPBY的HAVING子句。HAVING子句是對(duì)分組后的結(jié)果記錄集進(jìn)行篩選，所以HAVING子句可以使用聚合函數(shù)篩選條件，并只能跟GROUPBY子句一起使用，寫(xiě)在GROUPBY子句之后。SELECT分組統(tǒng)計(jì)查詢各子句執(zhí)行順序是：

①執(zhí)行WHERE子句，從數(shù)據(jù)表中選取滿足條件的數(shù)據(jù)行；

②執(zhí)行GROUPBY子句，對(duì)選取的數(shù)據(jù)行、按指定列字段的值進(jìn)行分組；

③執(zhí)行SELECT子句中的聚合函數(shù)，對(duì)每組數(shù)據(jù)按指定聚合要求進(jìn)行統(tǒng)計(jì)；

④執(zhí)行HAVING子句，對(duì)分組統(tǒng)計(jì)后的數(shù)據(jù)行再按指定條件篩選出結(jié)果集數(shù)據(jù)。3.4.2GROUPBY與HAVING子句

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04對(duì)2019-2020學(xué)年第一學(xué)期，查詢軟件技術(shù)專業(yè)符合申報(bào)學(xué)校一等獎(jiǎng)學(xué)金的學(xué)生的學(xué)號(hào)、姓名、評(píng)學(xué)考試分?jǐn)?shù)及績(jī)點(diǎn)情況。（申報(bào)學(xué)校一等獎(jiǎng)學(xué)金應(yīng)符合評(píng)學(xué)考試平均績(jī)點(diǎn)達(dá)4.0、單科分?jǐn)?shù)不低于80分）。mysql>SELECTg.Stu_No,Stu_Name,MIN(Score),AVG(GPA)

->FROMtb_gradegJOINtb_studentsONg.Stu_No=s.Stu_No

->WHERETest_Term='2019-2020學(xué)年一'ANDSUBSTRING(g.Stu_No,5,2)='04'

->GROUPBYg.Stu_No,Stu_Name

->HAVINGAVG(GPA)>=4.0ANDMIN(Score)>=80;3.4.2GROUPBY與HAVING子句

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04查詢統(tǒng)計(jì)2019級(jí)-2021級(jí)各專業(yè)開(kāi)設(shè)的班級(jí)數(shù)量，其中總?cè)藬?shù)超過(guò)100的專業(yè)。mysql>SELECTProfession_No,GROUP_CONCAT(Class_Name),COUNT(Class_No),SUM(Per_Quantity)

->FROMtb_class

->WHERELEFT(Class_No,4)BETWEEN2019AND2021

->GROUPBYProfession_No

->HAVINGSUM(Per_Quantity)>100;GROUP_CONCAT([DISTINCT]非分組統(tǒng)計(jì)列[ORDERBY列名][SEPARATOR'分隔符’]);SELECT子句中如果有非分組和統(tǒng)計(jì)的字段，默認(rèn)是只輸出其在一組中的第一個(gè)值，若需要輸出該列在一組中包含的所有值，可通過(guò)

GROUP_CONCAT(

)

函數(shù)實(shí)現(xiàn)同一分組中指定列的各值、按指定分隔符連接全部輸出。GROUP_CONCAT()函數(shù)必須和GROUPBY子句一起使用。3.4.2GROUPBY與HAVING子句

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.4.3使用窗口函數(shù)查詢?nèi)蝿?wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY041窗口函數(shù)窗口函數(shù)OVER([PARTITIONBY分組字段][ORDERBY排序字段[ASC|DESC])[[AS]別名]MySQL從8.0版本開(kāi)始支持窗口函數(shù)，也稱OLAP函數(shù)（OnlineAnalyticalProcessing，聯(lián)機(jī)分析處理），可對(duì)數(shù)據(jù)進(jìn)行實(shí)時(shí)分析處理：OVER：指定函數(shù)執(zhí)行的窗口范圍。若省略O(shè)VER后面括號(hào)中的內(nèi)容，窗口會(huì)包含滿足WHERE條件的所有行，窗口函數(shù)會(huì)基于所有滿足WHERE條件的記錄進(jìn)行計(jì)算。PARTITIONBY子句：指定窗口函數(shù)按照哪些字段進(jìn)行分組,分組后,窗口函數(shù)可以在每個(gè)分組中分別執(zhí)行。ORDERBY子句：指定窗口函數(shù)按照哪些字段進(jìn)行排序,執(zhí)行排序操作使窗口函數(shù)按照排序后的數(shù)據(jù)記錄的順序進(jìn)行編號(hào)。

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.4.3使用窗口函數(shù)查詢?nèi)蝿?wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04122常用窗口函數(shù)分類

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3窗口函數(shù)使用查詢班級(jí)信息表，對(duì)軟件學(xué)院（專業(yè)編號(hào)前兩位04）班級(jí)，按專業(yè)分析各專業(yè)的累積人數(shù)，累積人數(shù)統(tǒng)計(jì)列列名Ac_Per。查看班級(jí)名稱、專業(yè)編號(hào)、人數(shù)及對(duì)應(yīng)專業(yè)班級(jí)累計(jì)人數(shù)。。mysql>

SELECTClass_Name,Profession_No,Per_Quantity,

->SUM(Per_Quantity)OVER(PARTITIONBYProfession_NoORDERBYClass_Name)ASAc_Per

->FROMtb_class

->WHERELEFT(Profession_No,2)='04';3.4.3使用窗口函數(shù)查詢?nèi)蝿?wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.4.3使用窗口函數(shù)查詢?nèi)蝿?wù)四分組統(tǒng)計(jì)與分析查詢GROUPSTATISTICSANDANALYSISQUERY04窗口函數(shù)和普通聚合函數(shù)的區(qū)別：

普通聚合函數(shù)是將多條記錄聚合為一條；

窗口函數(shù)則是每條記錄都會(huì)執(zhí)行，有幾條記錄執(zhí)行完還是幾條。

窗口函數(shù)和GROUPBY在查詢中對(duì)數(shù)據(jù)進(jìn)行分組有類似之處，其區(qū)別在于：

窗口函數(shù)會(huì)對(duì)每個(gè)分組之后的數(shù)據(jù)進(jìn)行分別操作，也就是將分組的結(jié)果置于每一條數(shù)據(jù)記錄中；

GROUPBY只對(duì)分組之后數(shù)據(jù)使用聚集函數(shù)匯總，會(huì)把分組的結(jié)果聚合成一條記錄。窗口函數(shù)適用場(chǎng)景：對(duì)分組統(tǒng)計(jì)結(jié)果中的每一條記錄都進(jìn)行計(jì)算的場(chǎng)景下,使用窗口函數(shù)更好。

（因?yàn)镸ySQL的普通聚合函數(shù)的GROUPBY分組統(tǒng)計(jì)結(jié)果是每一組只有一條記錄。）

BYYUSHEN任務(wù)五子查詢?cè)趯W(xué)期末，教學(xué)督導(dǎo)部門、教務(wù)處需通過(guò)高校教學(xué)質(zhì)量管理系統(tǒng)的評(píng)學(xué)評(píng)教數(shù)據(jù)，獲得各位教師的學(xué)生評(píng)教、同行與督導(dǎo)專家評(píng)價(jià)及評(píng)分匯總的查詢報(bào)表，還可對(duì)學(xué)院分專業(yè)教研室查看各位教師的評(píng)教質(zhì)量數(shù)據(jù)，等等，實(shí)現(xiàn)一學(xué)期中對(duì)教師教學(xué)質(zhì)量的多層級(jí)評(píng)估。任務(wù)描述05BYYUSHEN05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.1比較子查詢?nèi)蝿?wù)五子查詢THESUBQUERY1單值返回比較子查詢查詢與秦奮老師在同一教研室的所有教師的編號(hào)、姓名、所在教研室編號(hào)和職稱信息。mysql>SELECTTeacher_No,Teacher_Name,Staff_No,Positional_TitleFROMtb_teacher->WHEREStaff_No=

(SELECTStaff_NoFROMtb_teacherWHERETeacher_Name='秦奮');當(dāng)子查詢的結(jié)果返回只有單個(gè)值時(shí)，可以用=、<>、>、>=、<、<=等大小相等比較運(yùn)算符，判斷外層數(shù)據(jù)操作的篩選條件所比較的某個(gè)列值是否與子查詢結(jié)果值滿足大小或相等關(guān)系。嵌套比較子查詢的執(zhí)行順序是“先內(nèi)后外”，即先執(zhí)行最內(nèi)層的子查詢，再將內(nèi)查詢的結(jié)果作為上一級(jí)外層查詢的篩選條件值來(lái)執(zhí)行外查詢。子查詢可嵌套更深一級(jí)的子查詢，最多可嵌套32層。05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.1比較子查詢?nèi)蝿?wù)五子查詢THESUBQUERY查詢何方同學(xué)的網(wǎng)頁(yè)設(shè)計(jì)課程的評(píng)學(xué)成績(jī)，查看該生學(xué)號(hào)、課程號(hào)、評(píng)學(xué)分?jǐn)?shù)或等級(jí)。mysql>SELECTStu_No,Course_No,Score,MarkFROMtb_grade

->WHEREStu_No=

(SELECTStu_NoFROMtb_studentWHEREStu_Name='何方')->ANDCourse_No=

(SELECTCourse_NoFROMtb_courseWHERECourse='網(wǎng)頁(yè)設(shè)計(jì)');對(duì)比：如果查詢何方同學(xué)的網(wǎng)頁(yè)設(shè)計(jì)課程的評(píng)學(xué)成績(jī)，需查看到該生的學(xué)號(hào)、姓名、課程號(hào)、課程名及評(píng)學(xué)分?jǐn)?shù)或等級(jí)。

比較總結(jié)數(shù)據(jù)查詢命令方式有何不同。

mysql>SELECTg.Stu_No,Stu_Name,g.Course_No,Course,Score,Mark->

FROMtb_studentsJOINtb_gradegONs.Stu_No=g.Stu_No->JOINtb_coursecONg.Course_No=c.Course_No->WHEREStu_Name='何方'ANDCourse='網(wǎng)頁(yè)設(shè)計(jì)';05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.1比較子查詢?nèi)蝿?wù)五子查詢THESUBQUERY子查詢與連接查詢?cè)诤芏嗲闆r下可以互換，兩者區(qū)別和互換原則：外查詢要輸出的結(jié)果集中，包含字段來(lái)自多個(gè)表，用連接查詢；外查詢要輸出的結(jié)果集中，包含字段只來(lái)自一個(gè)表，但其WHERE子句篩選條件

涉及另一個(gè)表時(shí)，常用子查詢。

幾乎所有連接查詢中使用JOIN運(yùn)算符的查詢部分都可以寫(xiě)成子查詢，

但連接查詢的效率高于子查詢。

05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.1比較子查詢?nèi)蝿?wù)五子查詢THESUBQUERY2多值返回比較子查詢WHERE表達(dá)式[NOT]IN(子查詢)當(dāng)子查詢的結(jié)果返回為批量多個(gè)值時(shí)，可以用IN、ANY、ALL、SOME等批量比較運(yùn)算符，判斷外層數(shù)據(jù)操作的篩選條件所比較的某個(gè)列值是否在子查詢的結(jié)果集中。WHERE表達(dá)式比較運(yùn)算符

[ANY|SOME|ALL](子查詢)IN：表達(dá)式只要與子查詢的結(jié)果集中有某個(gè)值相等時(shí)，即滿足條件。ANY和SOME是同義詞：表達(dá)式只要與子查詢的結(jié)果集中有某個(gè)值滿足比較關(guān)系時(shí)，即滿足條件。ALL：表達(dá)式要與子查詢結(jié)果集中每個(gè)值都滿足比較關(guān)系時(shí)，才滿足條件。

05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.1比較子查詢?nèi)蝿?wù)五子查詢THESUBQUERY查詢"軟件技術(shù)2031"班所開(kāi)設(shè)的課程信息,查看所開(kāi)課程的編號(hào),課程名,學(xué)時(shí)數(shù),學(xué)分。mysql>SELECTCourse_No,Course,Class_Hour,CreditFROMtb_course

->WHERECourse_NoIN(SELECTCourse_NoFROMtb_class_course

->WHEREClass_No=(SELECTClass_NoFROMtb_class

->

WHEREClass_Name='軟件技術(shù)2031'));或者:mysql>

SELECTCourse_No,Course,Class_Hour,CreditFROMtb_course

->WHERECourse_No=ANY(SELECTCourse_NoFROMtb_class_course

->WHEREClass_No=(SELECTClass_NoFROMtb_class

->WHEREClass_Name='軟件技術(shù)2031'))

;05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.2EXISTS相關(guān)（存在）子查詢?nèi)蝿?wù)五子查詢THESUBQUERYWHERE

[NOT]

EXISTS(子查詢)EXISTS表示存在，帶EXISTS關(guān)鍵字的子查詢不返回任何實(shí)際數(shù)據(jù)，僅返回一個(gè)邏輯值，作為外層查詢WHERE篩選條件的結(jié)果。由于帶EXISTS關(guān)鍵字的子查詢不需要返回實(shí)際數(shù)據(jù)，所以這種子查詢的SELECT子句中結(jié)果列表達(dá)式用“*”，因?yàn)榻o出列名并沒(méi)有意義。帶EXISTS關(guān)鍵字的子查詢的WHERE條件，要依賴于外層查詢中的某個(gè)列值，因此也被稱為

“相關(guān)子查詢”。EXISTS相關(guān)存在子查詢的執(zhí)行過(guò)程是“反復(fù)逐行，先外后內(nèi)”。

05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.2EXISTS相關(guān)（存在）子查詢?nèi)蝿?wù)五子查詢THESUBQUERY查詢同行教師和督導(dǎo)專家對(duì)郭瑞老師的所有評(píng)教評(píng)語(yǔ)、評(píng)分以及進(jìn)行評(píng)價(jià)的學(xué)期。mysql>SELECTTeacher_No,Evalu_Score,Evalu_Comment,Evalu_TermFROMtb_teach_evaluation

->WHEREEXISTS(SELECT*FROMtb_teacherWHERETeacher_Name='郭瑞'

->ANDtb_teacher.Teacher_No=tb_teach_evaluation.Teacher_No);比較子查詢的SELECT子句列字段，與外查詢WHERE篩選條件的比較字段對(duì)應(yīng)，

EXISTS相關(guān)子查詢的SELECT子句列字段為*，外查詢的WHERE篩選條件為判斷子查詢結(jié)果集是否EXISTS。比較子查詢只執(zhí)行一次，EXISTS相關(guān)子查詢要反復(fù)執(zhí)行多次。比較子查詢先內(nèi)后外執(zhí)行，EXISTS相關(guān)子查詢先外后內(nèi)執(zhí)行。05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.3派生表子查詢?nèi)蝿?wù)五子查詢THESUBQUERY查詢年齡在45歲以下的青年教師的編號(hào)、姓名、年齡、職稱信息。mysql>SELECT*->FROM(SELECTTeacher_No,Teacher_Name,YEAR(curdate())-YEAR(Birthday)Age,

->Positional_TitleFROMtb_teacher)tt->WHEREAge<=45;當(dāng)FROM數(shù)據(jù)源是子查詢時(shí)，子查詢結(jié)果集視作派生表，是張?zhí)摂M表，并且必須為其設(shè)置一個(gè)別名，在SELECT查詢語(yǔ)句中使用別名來(lái)引用派生表?；蛘撸簃ysql>SELECTTeacher_No,Teacher_Name,YEAR(curdate())-YEAR(Birthday)Age,->Positional_Title->FROMtb_teacher->WHEREYEAR(curdate())-YEAR(Birthday)<=45;05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.3派生表子查詢?nèi)蝿?wù)五子查詢THESUBQUERY查詢?cè)诙綄?dǎo)專家和同行教師的不同評(píng)教人員，所給予評(píng)價(jià)最高分的教師的編號(hào)與評(píng)分。mysql>SELECTa.Appraiser,a.Teacher_No,a.Evalu_Score->FROMtb_teach_evaluationa,(SELECTAppraiser,MAX(Evalu_Score)MaxScore->FROMtb_teach_evaluationGROUPBYAppraiser)b->WHEREa.Appraiser=b.AppraiserANDa.Evalu_Score=b.MaxScore;05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用3.5.4

利用子查詢添加、修改、刪除表數(shù)據(jù)任務(wù)五子查詢THESUBQUERYINSERT[INTO]表名[（列名1,列名2,...）]SELECT列表達(dá)式1,列表達(dá)式2,...FROM表名1[表別名1][INNER|OUTER|CROSS][JOIN][,]表名2[表別名2]...[ON表名1.關(guān)聯(lián)列=表名2.關(guān)聯(lián)列]...[WHERE條件表達(dá)式];INSERT語(yǔ)句中的SELECT子查詢，可用于將子查詢結(jié)果集添加到指定表中，即同時(shí)插入多行數(shù)據(jù)、實(shí)現(xiàn)表復(fù)制，比使用多個(gè)單行的INSERT語(yǔ)句效率要高。子查詢SLELECT語(yǔ)句與單表查詢、多表查詢中SQL語(yǔ)法格式相同。INSERT目標(biāo)表中列順序、類型及數(shù)量，要與SELECT子查詢結(jié)果集中相應(yīng)列順序、類型及數(shù)量必須一致。1子查詢用于添加表數(shù)據(jù)05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)五子查詢THESUBQUERY創(chuàng)建退休教師信息表tb_retiree，將每年數(shù)據(jù)審核時(shí)達(dá)到退休年齡60歲的教師記錄添加到tb_retiree表中。##創(chuàng)建退休教師信息表tb_retiree，其表結(jié)構(gòu)與教師表tb_teacher相同mysql>CREATETABLEtb_retireeLIKEtb_teacher;QueryOK,0rowsaffected(0.05sec)##將達(dá)到退休年齡60歲的教師記錄添加入tb_retiree表mysql>INSERTINTOtb_retiree

->SELECT*FROMtb_teacherWHEREDATE_SUB(curdate(),INTERVAL60YEAR)>=Birthday;QueryOK,2rowsaffected(0.02sec)3.5.4利用子查詢添加、修改、刪除表數(shù)據(jù)05

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用任務(wù)五子查詢THESUBQUERY將所有評(píng)學(xué)分在90分以下的少數(shù)民族學(xué)生的評(píng)學(xué)分?jǐn)?shù)都增加5分。mysql>UPDATEtb_grade->SETScore=Score+5->WHEREStu_NoIN(SELECTStu_NoFROMtb_studentWHERENation<>'漢族')

ANDScore<90;3.5.4利用子查詢添加、修改、刪除表數(shù)據(jù)2子查詢用于修改表數(shù)據(jù)在教師表中為每位老師設(shè)置一列教學(xué)質(zhì)量均分Quality_Score,統(tǒng)計(jì)教師教學(xué)評(píng)價(jià)表tb_teach_evaluation中督導(dǎo)專家和同行對(duì)各位老師評(píng)價(jià)均分，更新教師表對(duì)應(yīng)老師的教學(xué)質(zhì)量均分。##在教師表表中設(shè)置一列quality_score，與教師教學(xué)評(píng)價(jià)表中Evalu_Score列字段類型相同mysql>ALTERTABLEtb_teacherADDCOLUMNQuality_ScoreDECIMAL(4,1);##統(tǒng)計(jì)督導(dǎo)專家和同行對(duì)各位老師的評(píng)價(jià)均分，更新教師表中對(duì)應(yīng)老師的教學(xué)質(zhì)量均分mysql>UPDATEtb_teacher

SETQuality_Score=(SELECTAVG(Evalu_Score)FROMtb_teach_evaluationWHEREtb_teacher.Teacher_No=tb_teach_evaluation.Teacher_NoGROUPBYTeacher_N