《數(shù)據(jù)庫原理及應(yīng)用教程-MySQL8.0》課件 第6章 數(shù)據(jù)庫查詢

數(shù)據(jù)庫原理與應(yīng)用教程-MySQL8.0第6章

數(shù)據(jù)庫查詢第6章

數(shù)據(jù)庫查詢所謂查詢，就是檢索數(shù)據(jù)庫內(nèi)數(shù)據(jù)的特定請求。數(shù)據(jù)庫接受用SQL語言編寫的查詢，使用查詢可以按照不同的方式查看和分析數(shù)據(jù)。查詢設(shè)計是數(shù)據(jù)庫應(yīng)用程序開發(fā)的重要組成部分，因為在設(shè)計好數(shù)據(jù)庫并用數(shù)據(jù)進(jìn)行填充后，最常見地就是通過查詢來使用數(shù)據(jù)。6.1SELECT查詢語法基本語法格式：SELECT{select_list}FROM{table_list|view_list}[WHERE{search_conditions}][GROUPBY{group_by_list}][HAVING{search_conditions}][ORDERBY{order_list[ASC|DESC]}][LIMIT{n[,m]}];6.2簡單查詢簡單查詢包括投影查詢、選擇查詢及采用聚合函數(shù)的簡單計算查詢。6.2.1投影查詢通過SELECT語句的select_list項組成結(jié)果表的列。投影查詢格式：SELECT[ALL|DISTINCT]{*|{{column_name|expression}[[AS]column_alias]}[,…n]}6.2.1投影查詢1.選擇一個表中指定的列使用SELECT語句選擇一個表中的某些列，各列名之間要以逗號分隔，每個語句的結(jié)尾加分號。【例6-1】查詢teaching數(shù)據(jù)庫中學(xué)生的姓名、性別和專業(yè)。USEteaching;SELECTsname,ssex,majorFROMstudent;6.2.1投影查詢【例6-2】查詢course表的所有記錄。USEteaching;SELECT*FROMcourse;用“*”表示表中所有的列，按用戶創(chuàng)建表時聲明列的順序來顯示所有的列?！纠?-3】查詢student表的專業(yè)名稱，濾掉重復(fù)行。USEteaching;SELECTDISTINCTmajorFROMstudent;

DISTINCT關(guān)鍵字可以過濾掉查詢結(jié)果中的重復(fù)行。6.2.1投影查詢2.改變查詢結(jié)果中的顯示標(biāo)題SELECT語句中可以根據(jù)實際需要對查詢數(shù)據(jù)的列標(biāo)題進(jìn)行改變，或者為沒有標(biāo)題的列加上臨時的標(biāo)題。常用的方式：（1）用AS關(guān)鍵字來連接列表達(dá)式和指定的列名。

（2）在列表達(dá)式后面給出列名。6.2.1投影查詢【例6-4】查詢student表中所有學(xué)生的學(xué)號、姓名，結(jié)果中各列的標(biāo)題分別指定為漢字學(xué)號和姓名。

USEteaching;SELECTsnoAS學(xué)號,snameAS姓名FROMstudent;或：USEteaching;SELECTsno學(xué)號,sname姓名FROMstudent;注意：列標(biāo)題別名只在定義的語句中有效，即只是顯示標(biāo)題，對原表中列標(biāo)題沒有任何影響。6.2.1投影查詢3.計算列值

在進(jìn)行數(shù)據(jù)查詢時，經(jīng)常需要對查詢到的數(shù)據(jù)進(jìn)行再次計算處理?！坝嬎懔小辈⒉淮嬖谟诒砀袼鎯Φ臄?shù)據(jù)中，所以沒有列名?！纠?-5】查詢sc表，按150分制計算成績。USEteaching;SELECTsno,cno,score*1.50AS成績150分制FROMsc;6.2.2選擇查詢投影查詢是從列的角度進(jìn)行的查詢，一般對行不進(jìn)行任何過濾（DISTINCT除外）。但是，一般的查詢都不是針對表中所有行的查詢，只是從表中選出滿足指定條件的內(nèi)容，這就要用到WHERE子句進(jìn)行選擇查詢。選擇查詢的基本語法：SELECTselect_listFROMtable_listWHEREsearch_conditions6.2.2選擇查詢查詢條件謂詞比較運算符=,>,<,>=,<=,!=,<>確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件AND，OR，NOT6.2.2選擇查詢1.使用關(guān)系表達(dá)式【例6-6】查詢sc表中成績大于等于60的學(xué)生的學(xué)號、課程號和成績。USEteaching;SELECT*FROMscWHEREscore>=60;6.2.2選擇查詢2.使用邏輯表達(dá)式

【例6-7】查詢student表中計算機科學(xué)與技術(shù)專業(yè)的男生的信息。USEteaching;SELECT*FROMstudentWHEREmajor='計算機科學(xué)與技術(shù)'ANDssex='男';【例6-8】查詢計算機科學(xué)與技術(shù)專業(yè)或男生信息。USEteaching;SELECT*FROMstudentWHEREmajor='計算機科學(xué)與技術(shù)'ORssex='男';6.2.2選擇查詢3.使用BETWEEN關(guān)鍵字使用BETWEEN關(guān)鍵字可以更方便地限制查詢數(shù)據(jù)的范圍。使用BETWEEN表達(dá)式進(jìn)行查詢的效果完全可以用含有“>=”和“<=”的邏輯表達(dá)式來代替，使用NOTBETWEEN進(jìn)行查詢的效果完全可以用含有“>”和“<”的邏輯表達(dá)式來代替。6.2.2選擇查詢【例6-9】查詢sc表中成績在80到90之間的學(xué)生的學(xué)號、課程號和成績。USEteaching;SELECT*FROMscWHEREscoreBETWEEN80AND90;或：USEteaching;SELECT*FROMscWHEREscore>=80ANDscore<=90;

6.2.2選擇查詢4.使用IN（屬于）關(guān)鍵字

同BETWEEN關(guān)鍵字一樣，IN的引入也是為了更方便地限制檢索數(shù)據(jù)的范圍。語法格式為：表達(dá)式[NOT]IN(表達(dá)式1,表達(dá)式2[,…,表達(dá)式n])【例6-11】查詢student表中計算機科學(xué)與技術(shù)和通信工程專業(yè)的學(xué)生的姓名、學(xué)號和專業(yè)。USEteaching;SELECTsname,sno,majorFROMstudentWHEREmajorIN('計算機科學(xué)與技術(shù)','通信工程');6.2.2選擇查詢5.使用LIKE關(guān)鍵字使用LIKE關(guān)鍵字的查詢又叫模糊查詢，LIKE關(guān)鍵字搜索與指定模式匹配的字符串、日期或時間值。通配符“%”：為包含零個或多個字符的任意字符串；通配符“_”：為任意單個字符。【例6-12】通配符的示例

LIKE'AB%'返回以“AB”或“ab”開頭的任意字符串。LIKE'%abc'返回以“ABC”或“abc”結(jié)尾的任意字符串。LIKE'%abc%'返回包含“ABC”或“abc”的任意字符串。LIKE'_12'返回以“12”結(jié)尾的三個字符的字符串。6.2.2選擇查詢【例6-13】查詢student表中所有姓張的學(xué)生的信息。USEteaching;SELECT*FROMstudentWHEREsnamelike'張%';6.2.2選擇查詢6.IS[NOT]NULL(是[否]為空)查詢在WHERE子句中不能使用比較運算符對空值進(jìn)行判斷，只能使用空值表達(dá)式來判斷某個列值是否為空值。語法格式：表達(dá)式IS[NOT]NULL【例6-14】查詢sc表中所有成績?yōu)榭罩档膶W(xué)生的學(xué)號、課程號和成績。USEteaching;SELECT*FROMscWHEREscoreISNULL;6.2.2選擇查詢7.復(fù)合條件查詢在WHERE子句中可以使用邏輯運算符把若干個搜索條件合并起來，組成復(fù)雜的復(fù)合搜索條件。邏輯運算符優(yōu)先級從高到低依次是NOT、AND、OR?！纠?-15】從student表中查詢所有計算機科學(xué)與技術(shù)和通信工程專業(yè)的女生的信息。USEteaching;SELECT*FROMstudentWHERE(major='計算機科學(xué)與技術(shù)'ORmajor='通信工程')ANDssex='女';

6.2.3聚合函數(shù)查詢MySQL提供了一系列聚合函數(shù)，這些函數(shù)把數(shù)據(jù)描述為一個整體而不是一行行孤立的記錄，實現(xiàn)數(shù)據(jù)集合的匯總或是求平均值等各種運算。函數(shù)名功能sum(列名)返回一個數(shù)字列的總和avg(列名)對一個數(shù)字列計算平均值min(列名)返回一個數(shù)字、字符串或日期列的最小值max(列名)返回一個數(shù)字、字符串或日期列的最大值count(列名)返回一個列的數(shù)據(jù)項數(shù)count(*)返回找到的行數(shù)6.2.3聚合函數(shù)查詢【例6-16】查詢sc表中成績的平均值，平均值顯示列標(biāo)題為平均成績。USEteaching;SELECTavg(score)AS平均成績FROMsc;【例6-17】從student表中查詢專業(yè)的種類個數(shù)（相同的按一種計算）。

USEteaching;SELECTcount(DISTINCTmajor)AS專業(yè)種類數(shù)FROMstudent;6.2.3聚合函數(shù)查詢【例6-18】在sc表中查詢2021010001號學(xué)生的平均成績和最高成績。USEteaching;SELECTavg(score)AS平均成績,max(score)AS最高成績FROMscWHEREsno='2021010001';6.3分組查詢使用聚合函數(shù)返回的是所有行數(shù)據(jù)的統(tǒng)計結(jié)果。如果需要按某一列數(shù)據(jù)的值進(jìn)行分類，在分類的基礎(chǔ)上再進(jìn)行查詢，就要使用GROUPBY子句了。GROUPBY子句的語法：[GROUPBY[ALL]group_by_expression[,…n][HAVINGsearch_conditions]]6.3.1簡單分組如果在GROUPBY子句中沒有使用HAVING關(guān)鍵字，那么表示這種分組的技術(shù)是簡單分組技術(shù)。【例6-19】查詢student表中男生和女生的人數(shù)。USEteaching;SELECTssex,count(ssex)人數(shù)

FROMstudentGROUPBYssex;6.3.1簡單分組注意：使用GROUPBY子句時，選擇（SELECT）列表中任何非聚合表達(dá)式內(nèi)的所有列都應(yīng)該包含在GROUPBY列表中（不能使用列別名），或者說GROUPBY表達(dá)式應(yīng)該與選擇列表表達(dá)式完全匹配，否則查詢結(jié)果可能無法理解?！纠?-20】查詢sc表中每個學(xué)生的平均成績。USEteaching;SELECTsno,avg(score)平均成績FROMscGROUPBYsno;6.3.2HAVING的應(yīng)用可以使用HAVING關(guān)鍵字來對查詢和統(tǒng)計的結(jié)果進(jìn)行進(jìn)一步的篩選?！纠?-21】在sc表中查詢選修了3門及以上課程的學(xué)生學(xué)號和選課數(shù)。USEteaching;SELECTsno,COUNT(cno)選修課程數(shù)FROMscGROUPBYsnoHAVINGCOUNT(cno)>=3;

6.3.2HAVING的應(yīng)用HAVING與WHERE子句的區(qū)別：WHERE子句是對源表中數(shù)據(jù)篩選滿足條件的行；而HAVING子句是對GROUPBY分組查詢后產(chǎn)生的組再加條件，篩選出滿足條件的組。另外，HAVING中條件一般都直接使用聚合函數(shù)，WHERE中條件不能直接使用聚合函數(shù)。在MySQL系統(tǒng)中，一般情況下可以在3個地方使用聚合函數(shù)，即SELECT子句、HAVING子句和ORDERBY子句中。6.4連接查詢在實際應(yīng)用中，經(jīng)常需要同時從兩個表或兩個以上表中檢索數(shù)據(jù)。實現(xiàn)從兩個或兩個以上表中檢索數(shù)據(jù)且結(jié)果集中出現(xiàn)的列來自于兩個或兩個以上表中的檢索操作被稱為連接技術(shù)。在MySQL系統(tǒng)中，這種連接操作又可以細(xì)分為交叉連接、內(nèi)連接、外連接等。6.4.1內(nèi)連接

內(nèi)連接把兩個表中的數(shù)據(jù)連接生成第三個表，第三個表中僅包含那些滿足連接條件的數(shù)據(jù)行。內(nèi)連接的語法：SELECTselect_listFROMtable1INNERJOINtable2ONjoin_condition或SELECTselect_listFROMtable1,table2WHEREjoin_condition連接條件格式為：[<table1>.]<column1><compareoperator>[<table2>.]<column2>6.4.1內(nèi)連接

【例6-22】查詢每個學(xué)生的姓名、課程號和成績。

USEteaching;SELECTstudent.sname,o,sc.scoreFROMstudentINNERJOINscONstudent.sno=sc.sno;也可以利用下面的程序來實現(xiàn)：

SELECTstudent.sname,o,sc.scoreFROMstudent,scWHEREstudent.sno=sc.sno;

注意：當(dāng)從多個表中查詢的列名相同時，列名前必須加表名；列名不同時，列名前可以不加表名，但有時也會加上表名，以增強可讀性。6.4.1內(nèi)連接

【例6-23】查詢計算機科學(xué)與技術(shù)專業(yè)的學(xué)生所選的每門課的平均分。USEteaching;SELECTo,avg(b.score)as平均成績FROMstudentaINNERJOINscbONa.sno=b.snoanda.major='計算機科學(xué)與技術(shù)'GROUPBYo;為了簡化輸入，可以在SELECT查詢的FROM子句中為表定義一個臨時別名，在查詢中引用，以縮寫表名。6.4.1內(nèi)連接

【例6-24】查詢成績在75分以上的學(xué)生的學(xué)號、姓名，選修課的課程號、課程名、成績。USEteaching;SELECTo,ame,a.sno,a.sname,b.scoreFROMstudentASaJOINscASbONa.sno=b.snoANDb.score>75JOINcourseAScono=o;

6.4.2自連接連接操作不僅可以在不同的表上進(jìn)行，而且在同一張表內(nèi)可以進(jìn)行自身連接，即將同一個表的不同行連接起來。自連接可以看作一張表的兩個副本之間的連接。必須為表指定兩個別名，使之在邏輯上成為兩張表?！纠?-25】查詢同名學(xué)生的信息。USEteaching;SELECT*FROMstudentaINNERJOINstudentbONa.sname=b.snameANDa.sno<>b.sno;6.4.3外連接外連接一般只限制其中一個表的數(shù)據(jù)行，而不限制另外一個表中的數(shù)據(jù)。這種連接形式在許多情況下是非常有用的，例如在連鎖超市統(tǒng)計報表時，不僅要統(tǒng)計那些有銷售量的超市和商品，而且還要統(tǒng)計那些沒有銷售量的超市和商品。在MySQL系統(tǒng)中，可以使用的兩種外連接關(guān)鍵字，即LEFTOUTERJOIN和RIGHTOUTERJOIN。①左外連接是對連接條件中左邊的表不加限制。②右外連接是對連接條件中右邊的表不加限制。6.4.3外連接【例6-26】查詢每個學(xué)生及其選修課程的成績情況（含未選課的學(xué)生信息）。USEteaching;SELECTstudent.*,o,sc.scoreFROMstudentLEFTJOINscONstudent.sno=sc.sno;【例6-27】查詢學(xué)生選修課程的情況(含未被選修的課程信息)。USEteaching;SELECTsc.sno,sc.score,course.*FROMscRIGHTJOINcourseONo=o;如果想查詢成績最高的學(xué)生的學(xué)號和姓名。SELECTstudent.sno,sname,scoreFROMstudent,scWHEREscore=MAX(score)Andstudent.sno=sc.sno注意：where條件當(dāng)中不能直接使用聚合函數(shù)。所以，以上語句是錯誤的，而這樣的查詢需要用子查詢來完成。SELECT語句可以嵌套在其他許多語句中，這些語句包括SELECT、INSERT、UPDATE及DELETE等，這些嵌套的SELECT語句就被稱為子查詢。6.5子查詢6.5子查詢當(dāng)一個查詢依賴于另外一個查詢結(jié)果時，那么可以使用子查詢（一般為查詢條件不已知）。在某些查詢中，查詢語句比較復(fù)雜不容易理解，因此為了把這些復(fù)雜的查詢語句分解成多個比較簡單的查詢語句形式時也常使用子查詢方式。使用子查詢方式完成查詢操作的技術(shù)是子查詢技術(shù)。子查詢可以分為無關(guān)子查詢（嵌套子查詢）和相關(guān)子查詢。6.5.1無關(guān)子查詢無關(guān)子查詢的執(zhí)行不依賴于外部查詢。無關(guān)子查詢在外部查詢之前執(zhí)行，然后返回數(shù)據(jù)供外部查詢使用，無關(guān)子查詢中不包含對于外部查詢的任何引用。1.比較子查詢使用子查詢進(jìn)行比較測試時，通過等于（=）、不等于（<>）、小于（<）、大于（>）、小于或等于（<=）以及大于或等于（>=）等比較運算符，將一個表達(dá)式的值與子查詢返回的單值進(jìn)行比較。6.5.1無關(guān)子查詢【例6-29】查詢與2021010001號學(xué)生在同一個專業(yè)學(xué)習(xí)的學(xué)生的學(xué)號、姓名和專業(yè)。USEteaching;SELECTsno,sname,majorFROMstudentWHEREmajor=(SELECTmajorFROMstudentWHEREsno='2021010001');6.5.1無關(guān)子查詢【例6-30】查詢C001號課考試成績比劉玲玲高的學(xué)生的學(xué)號和姓名。USEteaching;SELECTstudent.sno,snameFROMstudent,scWHEREstudent.sno=sc.snoandcno='C001'andscore>(SELECTscoreFROMscWHEREcno='C001'andsno=(SELECTsnoFROMstudent

WHEREsname='劉玲玲'));6.5.1無關(guān)子查詢2.SOME、ANY、ALL和IN子查詢ALL和ANY操作符的常見用法是結(jié)合一個比較運算符對一個數(shù)據(jù)列子查詢的結(jié)果進(jìn)行測試，測試比較值是否與子查詢所返回的全部或一部分值匹配?！纠?-31】查詢計算機科學(xué)與技術(shù)專業(yè)年齡最大的學(xué)生的學(xué)號和姓名。SELECTsno,snameFROMstudentWHEREsbirthday<=ALL(SELECTsbirthdayFROMstudentWHEREmajor='計算機科學(xué)與技術(shù)')ANDmajor='計算機科學(xué)與技術(shù)';6.5.1無關(guān)子查詢【例6-32】查詢與任何一個通信工程專業(yè)學(xué)生同齡的學(xué)生的信息。USEteaching;SELECT*FROMstudentWHEREyear(birthday)=ANY(SELECTyear(birthday)FROMstudentWHEREspecialty='通信工程');6.5.1無關(guān)子查詢【例6-33】查詢選修了C001號課程的學(xué)生姓名和所在專業(yè)。SELECTsname,majorFROMstudentWHEREsnoIN(SELECTsnoFROMscWHEREcno='C001');6.5.1無關(guān)子查詢3.子查詢結(jié)果作為主查詢的查詢對象【例6-34】查詢有2個以上學(xué)生平均成績超過80分的班級（用年級和專業(yè)表示）。USEteaching;SELECTgrade,majorFROMstudents,(SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(score)>=80)ssWHEREs.sno=ss.snoGROUPBYgrade,majorHAVINGCOUNT(*)>=2;6.5.1無關(guān)子查詢USEteaching;SELECTgrade,majorFROMstudentWHEREsnoIN(SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(score)>=80)GROUPBYgrade,majorHAVINGCOUNT(*)>=2;6.5.2相關(guān)子查詢在相關(guān)子查詢中，子查詢的執(zhí)行依賴于外部查詢，多數(shù)情況下是子查詢的WHERE子句中引用了外部查詢的表。無關(guān)子查詢中子查詢只執(zhí)行一次，而相關(guān)子查詢中的子查詢需要重復(fù)地執(zhí)行。

相關(guān)子查詢的執(zhí)行過程：(1)子查詢?yōu)橥獠坎樵兊拿恳恍袌?zhí)行一次，外部查詢將子查詢引用的列的值傳給子查詢。(2)如果子查詢的任何行與其匹配，外部查詢就返回結(jié)果行。(3)再回到第一步，直到處理完外部表的每一行。6.5.2相關(guān)子查詢1.比較子查詢【例6-35】查詢成績比該課的平均成績低的學(xué)生的學(xué)號、課程號、成績。USEteaching;SELECTsno,cno,scoreFROMscaWHEREscore<(SELECTavg(score)FROMscbWHEREo=o);6.5.2相關(guān)子查詢2.帶有EXISTS的子查詢（存在性測試）使用子查詢進(jìn)行存在性測試時，通過邏輯運算符EXISTS或NOTEXISTS，檢查子查詢所返回的結(jié)果集是否有行存在。使用邏輯運算符EXISTS時，如果在子查詢的結(jié)果集內(nèi)包含有一行或多行，則存在性測試返回TRUE；否則返回FALSE。在EXISTS前加上NOT時，將對存在性測試結(jié)果取反。6.5.2相關(guān)子查詢【例6-37】查詢所有選修了C004號課程的學(xué)生的學(xué)號和姓名。USEteaching;SELECTsno,snameFROMstudentWHEREEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno='C004');

6.5.2相關(guān)子查詢由EXISTS引出的子查詢，其目標(biāo)屬性列表達(dá)式一般用*表示，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義。使用子查詢時要注意以下幾點：(1)子查詢需要用括號()括起來。(2)子查詢可以嵌套。(3)子查詢返回的結(jié)果的數(shù)據(jù)類型必須匹配外圍查詢WHERE語句的數(shù)據(jù)類型。(4)子查詢中不能使用ORDERBY子句。6.6其它查詢6.6.1限制查詢的行數(shù)使用LIMIT子句可以選擇只顯示一個表中連續(xù)的某些行數(shù)據(jù)。語法格式：select_statementLIMITn[,m];【例6-38】查詢student表，只顯示前5行數(shù)據(jù)。USEteaching;SELECT*FROMstudentLIMIT5;6.6.1限制查詢的行數(shù)【例6-39】查詢student表，只顯示第6到8行數(shù)據(jù)。USEteaching;SELECT*FROMstudentLIMIT5,3;注意：初始記錄行的偏移量是0而不是1。6.6.2聯(lián)合查詢聯(lián)合查詢即集合的并，是指將兩個或兩個以上的SELECT語句通過UNION運算符連接起來的查詢，聯(lián)合查詢可以將兩個或更多查詢的結(jié)果組合為單個結(jié)果集，該結(jié)果集包含聯(lián)合查詢中所有查詢的全部行。使用UNION組合兩個查詢的結(jié)果集的兩個基本規(guī)則：

所有查詢中的列數(shù)和列的順序必須相同；數(shù)據(jù)類型必須兼容。6.6.2聯(lián)合查詢【例6-40】查詢選修了課程C001和課程C004的學(xué)生的課程號和姓名。USEteaching;SELECTcno,snameFROMsc,studentWHEREcno='C001'andsc.sno=student.snoUNIONSELECTcno,snameFROMsc,studentWHEREcno='C004'andsc.sno=student.sno;6.6.3對查詢結(jié)果排序在使用SELECT語句時，排序是一種常見的操作。排序是指按照指定的列或其他表達(dá)式對結(jié)果集進(jìn)行排列順序的方式。SELECT語句中的ORDERBY子句負(fù)責(zé)完成排序操作。【例6-41】查詢teaching庫中女生的學(xué)號、姓名和專業(yè)，并按學(xué)號升序排列。USEteaching;SELECTsno,sname,majorFROMstudentWHEREssex='女'ORDERBYsnoASC;

6.6.3對查詢結(jié)