MySQL數據庫項目化教程V1項目五數據查詢項目任務知識目標學習目標任務1：簡單查詢任務2：連接查詢任務3：子查詢（1）了解簡單查詢和復合查詢（2）掌握模糊查詢的用法（3）掌握內連接、左外連接、右外連接和全連接查詢的用法（4）掌握連接查詢和子查詢的區別能力目標素質目標學習目標（1）能夠完成關于表的行和列的查詢（2）能夠使用模糊查詢（3）能夠實現自身連接查詢（4）能夠實現表間連接查詢（1）培養學生的編程能力和業務素質（2）培養學生自我學習的習慣、愛好和能力（3）培養學生的科學精神和態度課程思政（素養提示）學習目標（1）數據庫SQL查詢語句的正確性、查詢效率的優化都體現了嚴謹的工匠精神和求真務實的科學精神。（2）工匠精神是一種執著專注、精益求精、一絲不茍、追求卓越的精神。通過對數據查詢的學習培養學生的工匠精神，形成科學的思維方法。思維導圖學習目標任務分析任務完成任務描述Part

2Part

3Part

1任務一簡單查詢任務分析任務完成Part

1Part

2Part

3任務描述在數據庫應用中，最常用的操作就是查詢，它也是數據庫其他操作的基礎。數據查詢不應只是簡單返回數據庫中存儲的數據，還應該根據需要對數據進行篩選，以及確定數據以什么樣的格式顯示。簡單查詢通常是指查詢的范圍只涉及一個表的查詢。以下根據要求分別從學生技能競賽管理系統中查找相關數據信息。任務描述任務分析任務完成Part

1Part

2Part

3任務描述在學生技能競賽管理系統中，學生需要在數據表中查詢自己參加競賽的信息，教師需要查詢指導學生的信息等。在MySQL中，使用SELECT語句不僅能夠從數據表中查詢所需要的數據，也可以進行數據的統計匯總，將查詢的數據以用戶規定的格式整理返回給用戶端。任務分析任務分析任務完成Part

1Part

2Part

3任務描述1.查詢表中指定字段用戶可以查詢表中的所有字段進行顯示，如果有些列在本次查詢中無關緊要，也可以只查詢部分字段。SELECT查詢語句最簡單的形式是從一個表中檢索所有記錄，實現的方法是使用星號（*）通配符指定查詢所有字段名稱。語法格式如下：SELECT*FROM表名;【例5-1】查詢student表中所有學生的詳細信息。SELECT*FROMstudent;【例5-2】查詢teacher表中所有教師的詳細信息。SELECT*FROMteacher;所有字段所有字段運行結果【例5-1】【例5-2】使用SELECT查詢語句，可以獲取多個字段下的數據，只需要在關鍵字SELECT后面指定要查找的字段的名稱即可，不同字段名稱之間用逗號（，）隔開，最后一個字段后面不需要加逗號，語法格式如下：SELECT字段名1,字段名2,…,字段名nFROM表名;【例5-3】查詢student表中學生的學號和姓名。SELECTst_no,st_nameFROMstudent;【例5-4】查詢teacher表中教師的姓名和信息。SELECTtc_name,tc_infoFROMteacher;1.查詢表中指定字段部分字段部分字段運行結果【例5-3】【例5-4】為字段取別名在有些情況下，顯示的列的名稱會很長或者名稱不夠直觀，MySQL可以指定列別名，替換字段或表達式。為字段取別名的基本語法形式為：列名[AS]列別名“列名”為表中字段定義的名稱，“列別名”為字段新的名稱，AS關鍵字為可選參數。【例5-5】查詢student表中所有學生的學號、姓名和班級編號，要求字段名為漢字形式。SELECTst_noAS學號,st_nameAS姓名,class_idAS班級編號FROMstudent;為字段取別名【例5-6】查詢teacher表中所有教師的工號、姓名和性別，要求字段名為漢字形式。SELECTtc_no教師工號,tc_name姓名,tc_sex性別FROMteacher;1.查詢表中指定字段運行結果【例5-5】【例5-6】簡單條件查詢2.選擇行選擇行查詢就是通過滿足某些條件進行查詢，從而查詢出相應的記錄，通常會把相應的查詢條件放到WHERE子句。語法形式為：SELECT字段名1,字段名2,…,字段名nFROM表名WHERE查詢條件;簡單條件查詢表時只有一個查詢條件。簡單條件查詢【例5-7】查詢student表中院系編號為4的所有學生信息。SELECT*FROMstudentWHEREdp_id=4;運行結果【例5-7】復合條件查詢2.選擇行復合條件查詢通常使用AND或者OR連接多個查詢條件。其中AND表示前后的兩個查詢條件要同時成立，OR表示前后的兩個查詢條件其中一個成立即可。OR可以和AND一起使用，但是在使用時要注意兩者的優先級，由于AND的優先級高于OR，因此先對AND兩邊的操作數進行操作，再與OR中的操作數結合。語法形式如下：SELECT字段1,字段2,…,字段nFROM表名WHERE查詢條件1AND查詢條件2AND…AND查詢條件n;【例5-8】查詢student表中院系編號為4的所有女學生的信息。SELECT*FROMstudentWHEREdp_id=4ANDst_sex='女';復合條件查詢【例5-9】查詢class表中院系編號為1和2的班級信息。SELECT*FROMclassWHEREdp_id=1ORdp_id=2;運行結果【例5-8】【例5-9】指定范圍查詢2.選擇行指定范圍查詢表示要查詢的記錄在指定的條件范圍內或者不在指定的條件范圍內，使用[not]BETWEENAND來查詢[不在]某個范圍內的值，該操作符需要兩個參數，即范圍的開始值和結束值，如果字段值滿足指定的范圍查詢條件，則這些記錄被返回。語法形式如下：SELECT字段1,字段2,…,字段nFROM表名WHERE查詢條件[not]BETWEEN開始值AND結束值;指定范圍查詢【例5-10】在project表中查詢培訓天數在20到35天的競賽項目名稱及培訓天數。SELECTpr_name,pr_daysFROMprojectWHEREpr_daysBETWEEN20AND35;運行結果【例5-10】模糊條件查詢2.選擇行在前面的查詢操作中，講述了如何查詢多個字段的記錄，如何進行比較查詢或者是查詢一個條件范圍內的記錄，如果要查找所有的包含字符“陳”的學生名字，該如何查找呢？簡單的比較操作在這里已經行不通了，在這里需要使用通配符進行匹配查找，通過創建查找模式對表中的數據進行比較。執行這個任務的關鍵字是LIKE。通配符是一種在SQL的WHERE條件子句中擁有特殊意思的字符，SQL語句中支持多重通配符。一般模糊條件查詢格式如下：SELECT字段FROM表WHERE某字段LIKE條件；模糊條件查詢%：匹配任意長度的字符，甚至包括零字符。【例5-11】查詢student表中姓“陳”的學生信息。SELECT*FROMstudentWHEREst_nameLIKE'陳%';運行結果【例5-11】模糊條件查詢2.選擇行_:表示任意單個字符。匹配單個任意字符，它常用來限制表達式的字符長度。如果要匹配多個字符，則需要使用相同個數的“_”。【例5-12】查詢student表中姓“李”的且名字為兩個字的學生信息。SELECT*FROMstudentWHEREst_nameLIKE'李_';[]:表示括號內所列字符中的一個（類似正則表達式）。指定一個字符、字符串或范圍，要求所匹配的對象為他們中的任一個。【例5-13】查詢student表中名字帶有單字“旭”的學生，姓可以為李、王、林、張。SELECT*FROMstudentWHEREst_nameREGEXP'[李王林張]旭';模糊條件查詢運行結果【例5-12】【例5-13】模糊條件查詢2.選擇行[^]：表示不在括號所列之內的單個字符。其取值和[]相同，但它所要求匹配對象為指定字符以外的任一個字符。【例5-14】查詢student表中名字帶有單字“燦”的學生，姓不可以為王、林、張。SELECT*FROMstudentWHEREst_nameREGEXP'[^王林張]燦';空值查詢2.選擇行數據表創建的時候，設計者可以指定某列中是否可以包含空值（NULL）。空值不同于0，也不同于空字符串。空值一般表示數據未知、不適用或將在以后添加數據。在SELECT語句中使用ISNULL子句，可以查詢某字段內容為空記錄。空值查詢【例5-15】查詢teacher表中2號院系沒有信息介紹的教師信息。SELECT*FROMteacherWHEREdp_id=2ANDtc_infoISNULL;消除重復行2.選擇行從例題5-15可以看到返回了6條記錄，其中性別只有男，有時，處于對數據分析的要求，需要消除重復的記錄值，如何使查詢結果沒有重復呢？在SELECT語句中，可以使用DISTINCT關鍵字指示MySQL消除重復的記錄。如果查詢結果有重復值并且所有重復值的次數并不重要的情況下，可以消除重復行。消除重復行【例5-16】查詢teacher表中院系編號為2的教師性別情況。SELECTDISTINCTtc_sexFROMteacherWHEREdp_id=2;顯示前N行2.選擇行SELECT返回所有匹配的行，有可能是表中所有的行，如僅僅需要返回第一行或者前幾行，使用LIMIT關鍵字，基本語法形式如下：LIMIT[位置偏移量,]行數;顯示前N行【例5-17】查詢前3名學生的信息。SELECT*FROMstudentLIMIT3;COUNT()函數3.使用聚合函數COUNT()函數統計數據表中包含的記錄行的總數，或者根據查詢結果返回列中包含的數據行數。其使用方法有兩種：計算表中總的行數，不管某列有數值或者為空值。COUNT(*)計算指定列下總的行數，計算時將忽略空值的行。COUNT(字段名)COUNT()函數【例5-18】統計student表中男學生的人數。SELECTCOUNT(*)AS男生人數FROMstudentWHEREst_sex='男';SUM()函數3.使用聚合函數SUM()是一個求總和的函數，返回指定列值的總和。AVG()函數【例5-20】統計project表中各種競賽項目的平均培訓天數。SELECTAVG(pr_days)AS平均培訓天數FROMproject;【例5-19】統計院系編號為6的競賽項目的總培訓天數。SELECTSUM(pr_days)AS總培訓天數FROMprojectWHEREdp_id=6;AVG()函數通過計算返回的行數和每一行數據的和，求得指定列數據的平均值。運行結果【例5-19】【例5-20】MAX()函數3.使用聚合函數MAX()返回查詢列中的最大值。MIN()函數【例5-22】在project表中找出所有競賽項目中的最小培訓天數。SELECTMIN(pr_days)AS最小培訓天數FROMproject;【例5-21】在project表中找出所有競賽項目中的最大培訓天數。SELECTMAX(pr_days)AS最大培訓天數FROMproject;MIN()返回查詢列中的最小值。分組查詢4.分組查詢分組查詢是對數據按照某個或多個字段進行分組，可以使用GROUPBY子句，用于將結果集中的行分成若干個組來輸出，在一個查詢語句中，如果用了GROUPBY子句，則SELECT子句中的列只能使用分組項字段和聚合函數。基本語法形式為：[GROUPBY字段][HAVING<條件表達式>]字段值為進行分組時所依據的列名稱；“HAVING<條件表達式>”指定滿足表達式限定條件的結果將被顯示。分組查詢【例5-23】查詢表project中平均培訓天數大于40天的院系編號和平均培訓時間。SELECTdp_id,AVG(pr_days)AS平均培訓天數FROMprojectGROUPBYdp_idHAVINGAVG(pr_days)>40;排序輸出5.排序輸出從前面的查詢結果，我們會發現有些字段的值是沒有任何順序的，MySQL可以通過在SELECT語句中使用ORDERBY子句，對查詢的結果進行排序。有升序排序和降序排序，默認為升序排序。排序輸出【例5-24】查詢表project中競賽項目的項目名稱和培訓時間，并按培訓天數降序排列。SELECTpr_name,pr_daysFROMprojectORDERBYpr_daysDESC;運行結果【例5-24】任務總結本任務主要介紹在MySQL軟件中關于單表數據的查詢操作，使用SELECT語句實現，從選擇列查詢、選擇行查詢、使用聚合函數查詢、分組查詢和排序輸出五個方面進行介紹。查詢是對數據庫里的數據進行檢索，并按用戶要求返回所需數據的過程。它是SQL語言的核心操作。任務分析任務完成任務描述Part

2Part

3Part

1任務二連接查詢任務分析任務完成Part

1Part

2Part

3任務描述一個數據庫中，通常存在多張數據表，用戶一般需要用多個表進行組合查詢來查詢出所需要的信息。如果一個查詢需要對多個表進行操作，那么這樣的操作就稱為連接查詢。多表連接查詢是關系數據庫中最重要也是最常用的查詢。多表連接查詢分為內連接、外連接和自連接等不同的連接方式，可以實現用戶各種各樣的查詢要求。任務描述任務分析任務完成Part

1Part

2Part

3任務描述教師的基本信息存儲在教師表（teacher）中，而項目編號存儲在教師指導項目表（tc_project）中，這就涉及兩個表的查詢了。而這兩個表中有一個公共屬性，即教師編號（tc_id），可以通過教師編號這個公共屬性將這兩個表連接起來，以得到符合要求的查詢結果。任務分析任務分析任務完成Part

1Part

2Part

3任務描述自身連接查詢1.第一種內連接查詢如果在一個連接查詢中，涉及到的兩個表都是同一個表，這種查詢稱為自連接查詢。自連接是一種特殊的內連接，它是指相互連接的表在物理上為同一張表，但可以在邏輯上分為兩張表。【例5-25】查詢所有教師工號比“張詠風”大的教師的姓名、工號和性別。SELECTA.tc_name,A.tc_no,A.tc_sexFROMteacherA,teacherBWHEREB.tc_name='張詠風'ANDA.tc_no>B.tc_no;表間連接查詢在多個不同表之間進行連接，這樣的連接稱為表間連接查詢。【例5-26】查詢“王曉東”學生參加的競賽項目名稱、培訓地點、培訓開始時間和培訓結束時間。SELECTpr_name,pr_trainaddress,pr_starttime,pr_endtimeFROMstudent,st_project,projectWHEREst_name='王曉東'ANDstudent.st_id=st_project.st_idANDst_project.pr_id=project.pr_id;運行結果【例5-25】【例5-26】內連接查詢2.第二種內連接查詢除了以上的內連接查詢以外，還可以使用JOINON實現內連接查詢。【例5-27】用JOINON查詢編號為27的學生的班級號、班級名和年級。SELECTclass.class_no,class.class_name,class.class_gradeFROMstudentJOINclassONstudent.st_id=27ANDstudent.class_id=class.class_id;左外連接3.外連接查詢左向外連接的結果集包括

LEFTOUTER子句中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯的結果集行中右表的所有選擇列表列均為空值。【例5-28】查詢3號院系所有的學生姓名、性別、參賽項目編號和對應的指導教師編號，如果該學生沒有參賽，也需要顯示參賽項目編號和對應的指導教師編號。SELECTstudent.st_name,student.st_sex,st_project.pr_id,st_project.tc_idFROMstudentLEFTOUTERJOINst_projectONstudent.st_id=st_project.st_idWHEREdp_id=3;運行結果【例5-28】右外連接3.外連接查詢右向外連接是左向外連接的反向連接。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。【例5-29】查詢所有的教師姓名、性別及指導的項目編號，如果該教師沒有指導競賽，也需要顯示項目編號。SELECTteacher.tc_name,teacher.tc_sex,st_project.pr_idFROMst_projectRIGHTOUTERJOINteacherONteacher.tc_id=st_project.tc_id;運行結果【例5-29】任務總結本任務主要是分兩部分來講解連接查詢。1.內連接（典型的連接運算，使用像=

或<>之類的比較運算符）。包括相等連接和自然連接。

內連接使用比較運算符根據每個表共有的列的值匹配兩個表中的行。例如，檢索student和st_project表中學生編號相同的所有行。

2.外連接。外連接可以是左向外連接、右向外連接或完整外部連接。

任務分析任務完成任務描述Part

2Part

3Part

1任務三子查詢任務分析任務完成Part

1Part

2Part

3任務描述當一個查詢是另一個查詢的條件時，稱之為子查詢。子查詢是一個SELECT語句，它嵌套在一個SELECT、SELECT...INTO語句、INSERT...INTO語句、DELETE語句或UPDATE語句或嵌套在另一子句查詢中。在學生技能競賽管理系統中，要查詢出所有參加競賽的學生的學號和姓名。任務描述任務分析任務完成Part

1Part

2Part

3任務描述根據任務二可知，使用連接查詢將學生表（student）與學生參賽表（st_project）按照學生編號相等連接，即可得到已經參加競賽的學生姓名和項目編號，因為凡是在學生參賽表中的學生都是已經參加競賽的。除使用連接查詢之外，還可以使用子查詢，子查詢又稱嵌套查詢。任務分析任務分析任務完成Part

1Part

2Part

3任務描述1.使用EXISTS的子查詢使用EXISTS關鍵字引入子查詢后，子查詢的作用就相當于進行存在測試。外部查詢的WHERE子句測試子查詢返回的行是否存在。子查詢實際上不產生任何數據，它只返回TRUE或FALSE值，其目標表達式通常都是*。【例5-30】查詢已有學生參賽的項目和培訓天數。SELECTpr_name,pr_timeFROMprojectWHEREEXISTS(SELECT*FROMst_projectWHEREst_project.pr_id=project.pr_id);EXISTS關鍵字運行結果【例5-30】2.使用IN或NOTIN的子查詢通過IN（或NOTIN）引入的子查詢結果是包含零個值或多個值的列表。子查詢返