《MySQL數(shù)據(jù)庫原理與應用案例教程》 課件 項目6 查詢數(shù)據(jù)

《MySQL數(shù)據(jù)庫項目化教程》項目6查詢數(shù)據(jù)查詢數(shù)據(jù)是數(shù)據(jù)庫操作中使用頻道最高、最重要的操作，通過數(shù)據(jù)查詢，用戶可以從數(shù)據(jù)庫中根據(jù)需要，使用不同的查詢方式來獲取所需要的數(shù)據(jù)。本項目通過典型任務學習簡單查詢、單條件查詢、多條件查詢、模糊查詢、使用聚合函數(shù)查詢、查詢排序、分組查詢、多表查詢、子查詢、嵌套查詢、聯(lián)合查詢等多種查詢方式。前言掌握如何在單表上進行數(shù)據(jù)查詢。掌握如何進行條件查詢。掌握模糊查詢和使用聚合函數(shù)查詢。掌握多表查詢。掌握將查詢結果進行排序和分組。學習目標知識重點條件查詢。模糊查詢和聚合函數(shù)查詢。查詢排序和分組查詢。知識難點多表查詢。子查詢/嵌套查詢。知識重點與難點簡單查詢任務1任務2單條件查詢學習任務使用函數(shù)查詢任務3多條件查詢任務4任務5查詢排序學習任務分組查詢任務6連接查詢任務7任務8子查詢學習任務合并查詢結果任務9簡單查詢任務1任務描述任務目標任務分析任務實施123知識鏈接45

通過對數(shù)據(jù)表tb_student的查詢，獲取所需要的字段數(shù)據(jù)，具體需求為：查詢數(shù)據(jù)表中所有字段數(shù)據(jù)、查詢數(shù)據(jù)表中指定字段數(shù)據(jù)、為字段指定別名、為數(shù)據(jù)表指定別名和消除查詢結果中的重復記錄。任務描述（1）會查詢數(shù)據(jù)表中所有字段數(shù)據(jù)。（2）會查詢數(shù)據(jù)表中指定字段數(shù)據(jù)。（3）會為字段指定別名。（4）會為數(shù)據(jù)表指定別名。（5）會消除查詢結果中的重復記錄。（6）給學生參與表現(xiàn)的充分機會，讓學生在自主嘗試中探究學習，培養(yǎng)學生的自我學習習慣。任務目標學習查詢語句的基本語句格式，根據(jù)具體的需求，在單表上進行數(shù)據(jù)查詢，逐一將要顯示的字段名列舉在select子句后面完成查詢數(shù)據(jù)表中所有字段數(shù)據(jù)，查詢數(shù)據(jù)表中指定字段數(shù)據(jù)，通過as關鍵字為字段指定別名，為數(shù)據(jù)表指定別名，通過distinct關鍵字消除查詢結果中的重復記錄。任務分析查詢數(shù)據(jù)任務實施步驟1查詢數(shù)據(jù)表中所有字段數(shù)據(jù)查詢數(shù)據(jù)表tb_student中所有字段數(shù)據(jù)mysql>usechjgl_db;mysql>select*fromtb_student;任務實施結果顯示，使用“*”通配符可以查詢數(shù)據(jù)表中所有字段的數(shù)據(jù)，查詢結果中字段的排列順序和字段在數(shù)據(jù)表中定義的順序一致。步驟2查詢數(shù)據(jù)表中指定字段數(shù)據(jù)查詢數(shù)據(jù)表tb_student中sno、sname和ssex字段數(shù)據(jù)。mysql>selectsno,sname,ssexfromtb_student;結果顯示，只查詢了sno、sname和ssex三個字段的數(shù)據(jù)。查詢結果中字段的排列順序和select語句中指定的順序一致。任務實施步驟3為字段指定別名查詢數(shù)據(jù)表tb_student中sno、sname、ssex字段數(shù)據(jù)，為了顯示結果更加直觀，為字段sno、sname、ssex指定別名分別為學號、姓名、性別。mysql>selectsnoas學號,snameas姓名,ssexas性別fromtb_student;結果顯示，字段名顯示為指定的別名，代替了默認的字段名。任務實施步驟4為數(shù)據(jù)表指定別名查詢數(shù)據(jù)表tb_student中sno、sname、ssex字段數(shù)據(jù)，為數(shù)據(jù)表tb_student指定別名。mysql>selectstu.sno,stu.sname,stu.ssexfromtb_studentasstu;結果顯示，為數(shù)據(jù)表指定別名，在SQL語句中，就可以用這個別名來代替數(shù)據(jù)表的名稱。任務實施步驟5消除查詢結果中的重復記錄（1）查詢數(shù)據(jù)表tb_student中ssex字段數(shù)據(jù)，為字段ssex指定別名為性別。mysql>selectssexas性別fromtb_student;結果顯示，ssex字段存在重復的值。任務實施步驟5消除查詢結果中的重復記錄（2）查詢數(shù)據(jù)表tb_student中ssex字段數(shù)據(jù)，為字段ssex指定別名為性別，消除查詢結果中的重復記錄。mysql>selectdistinctssexas性別fromtb_student;結果顯示，使用ditinct關鍵字后，ssex字段只有“男”和“女”兩個取值，消除了重復記錄。任務實施步驟6限制查詢結果的條數(shù)（1）查詢數(shù)據(jù)表tb_student中sno、sname、ssex字段數(shù)據(jù)，為字段sno、sname、ssex指定別名分別為學號、姓名、性別，顯示查詢結果的前兩條記錄。mysql>selectsnoas學號,snameas姓名,ssexas性別fromtb_studentlimit2;結果顯示，查詢結果為滿足條件的前兩條記錄。任務實施（2）查詢數(shù)據(jù)表tb_student中sno、sname、ssex字段數(shù)據(jù)，為字段sno、sname、ssex指定別名分別為學號、姓名、性別，顯示查詢結果的第3-5條記錄。mysql>selectsnoas學號,snameas姓名,ssexas性別fromtb_studentlimit2,3;任務實施小貼士：（1）limit初始位置,記錄數(shù)：“初始位置”表示從哪條記錄開始顯示，第一條記錄的位置是0，第二條記錄的位置是1，后面的記錄位置依此類推。（2）如果“記錄數(shù)”的值大于查詢結果的總記錄數(shù)，則會顯示出查詢出來的所有記錄。（3）查詢數(shù)據(jù)表tb_student中sno、sname、ssex字段數(shù)據(jù)，為字段sno、sname、ssex指定別名分別為學號、姓名、性別，使用limitoffset語句顯示查詢結果的從第3條記錄開始的4條記錄。mysql>selectsnoas學號,snameas姓名,ssexas性別fromtb_studentlimit4offset2;任務實施查詢數(shù)據(jù)是數(shù)據(jù)庫操作中使用頻道最高、最重要的操作，通過數(shù)據(jù)查詢，用戶可以從數(shù)據(jù)庫中根據(jù)需要，使用不同的查詢方式來獲取不同的數(shù)據(jù)。在MySQL中，可以使用SELECT語句來查詢數(shù)據(jù)，基本語法格式如下：知識鏈接——查詢數(shù)據(jù)語法分析：SELECT：查詢數(shù)據(jù)的關鍵字。DISTINCT：對查詢返回的結果集提供一個最基本的過濾。即結果集中只含非重復行。對關鍵詞DISTINCT來說，空值都是相等的，無論有多少null值，只選擇一個。select_list：可以包含一項或多項下列內容：①“*”，表示按照創(chuàng)建的數(shù)據(jù)表的順序排列的所有字段。②按照用戶所需順序排列的字段列表，各字段之間要用逗號隔開。③可以在字段名后面使用as子句，實現(xiàn)查詢結果展示時使用別名來取代字段名。④表達式（字段名、常量、函數(shù)，或者以運算符連接的字段名、常量和函數(shù)的任何組合）。知識鏈接——查詢數(shù)據(jù)⑤內部函數(shù)或者聚合函數(shù)。⑥上述各項的任何一種組合。FROMtbl_name[[AS]alias_name],...：指定要查詢數(shù)據(jù)的數(shù)據(jù)表的列表。如果有多個數(shù)據(jù)表，用逗號將之分開。在關鍵字from后面的表的順序不影響結果。表名可以用as子句給出相關別名，以便使表達清晰。WHEREwhere_condition：指定查詢數(shù)據(jù)的查詢條件。GROUPBYcol_name,...：可以根據(jù)一個或多個字段對查詢結果進行分組。HAVINGwhere_condition：對分組后的數(shù)據(jù)進行過濾，支持WHERE關鍵字中所有的查詢條件。知識鏈接——查詢數(shù)據(jù)ORDERBYcol_name[ASC|DESC],...：將查詢結果中的數(shù)據(jù)按照一定的順序進行排序。col_name表示需要排序的字段名稱，多個字段時用逗號隔開。ASC表示字段按升序排序，DESC表示字段按降序排序。其中ASC為默認值。LIMIT{[offset,]row_count|row_countOFFSEToffset}：用于指定查詢結果從哪條記錄開始顯示，一共顯示多少條記錄。offset為初始位置，表示從哪條記錄開始顯示，第一條記錄的位置是0，第二條記錄的位置是1，后面的記錄依此類推。row_count為記錄數(shù)，表示顯示記錄的條數(shù)。本任務中只用到最簡單的SELECT語句，關于WHERE、GROUPBY、HAVING等子句，在后面的任務中將詳細介紹并應用。知識鏈接——查詢數(shù)據(jù)單條件查詢任務2任務描述任務目標任務分析任務實施123知識鏈接45

選擇合適的基本查詢條件對數(shù)據(jù)表tb_student進行查詢，獲取需要的數(shù)據(jù)，按照查詢需求本任務主要分為以下幾種情況的查詢：帶比較運算符的查詢、帶指定取值范圍的查詢、空值查詢、指定集合查詢和模糊查詢。任務描述（1）會帶比較運算符的查詢。（2）會帶指定取值范圍的查詢。（3）會空值查詢。（4）會指定集合查詢。（5）會模糊查詢。（6）適當引導學生對各類查詢進行變化演繹，培養(yǎng)學生舉一反三的能力。任務目標學習查詢語句的基本語句格式，根據(jù)具體的需求，選擇相應的條件，通過使用比較運算符，完成比較運算符的查詢；使用betweenand/notbetweenand關鍵字指定查詢范圍，完成指定取值范圍的查詢；使用isnull/isnotnull關鍵字用來判斷某字段的取值是否為空，完成空值查詢；使用in/notin關鍵字來判斷某個字段的值是否在指定的集合中，完成指定集合查詢；使用like/notlike關鍵字來匹配字符串是否相等，完成模糊查詢。任務分析betweenand/notbetweenandisnull/isnotnullin/notin任務實施比較運算符like/notlike步驟1帶比較運算符的查詢（1）查詢數(shù)據(jù)表tb_student中性別為“女”的學生的學號、姓名、性別。mysql>usechjgl_db;mysql>selectsnoas學號,snameas姓名,ssexas性別fromtb_studentwheressex='女';任務實施（2）查詢數(shù)據(jù)表tb_student中2001年之前出生的學生的學號、姓名、出生日期。mysql>selectsnoas學號,snameas姓名,sbirthdayas出生日期fromtb_studentwheresbirthday<'2001-01-01';任務實施任務實施步驟2帶指定取值范圍的查詢（1）查詢數(shù)據(jù)表tb_student中2000年10月份出生的學生的學號、姓名、出生日期。mysql>selectsnoas學號,snameas姓名,sbirthdayas出生日期fromtb_studentwheresbirthdaybetween'2000-10-01'and'2000-10-31';任務實施（2）查詢數(shù)據(jù)表tb_student中不是2000年10月份出生的學生的學號、姓名、出生日期。mysql>selectsnoas學號,snameas姓名,sbirthdayas出生日期fromtb_studentwheresbirthdaynotbetween'2000-10-01'and'2000-10-31';步驟3空值查詢（1）查詢數(shù)據(jù)表tb_student中專業(yè)為空值的學生的學號、姓名、專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresspecialtyisnull;任務實施（2）查詢數(shù)據(jù)表tb_student中專業(yè)為非空值的學生的學號、姓名、專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresspecialtyisnotnull;任務實施步驟4

指定集合查詢（1）查詢數(shù)據(jù)表tb_student中姓名為李中華、朱麗麗的學生的學號、姓名、專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresnamein('李中華','朱麗麗');任務實施（2）查詢數(shù)據(jù)表tb_student中除了李中華、朱麗麗之外的其他學生的學號、姓名、專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresnamenotin('李中華','朱麗麗');任務實施步驟5模糊查詢（1）查詢數(shù)據(jù)表tb_student中姓“李”的學生的學號、姓名、專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresnamelike'李%';任務實施（2）查詢數(shù)據(jù)表tb_student中所有不姓“李”的學生的學號、姓名、專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresnamenotlike'李%';任務實施（3）查詢數(shù)據(jù)表tb_student中姓“朱”，并且姓名的第3個字是“麗”的學生的學號、姓名、專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresnamelike'朱_麗';任務實施（4）查詢數(shù)據(jù)表tb_student中2000年01月份出生的學生的學號、姓名、出生日期。mysql>selectsnoas學號,snameas姓名,sbirthdayas出生日期fromtb_studentwheresbirthdaylike'2000-01%';任務實施當使用select語句進行查詢時，where子句中，MySQL允許用戶對表達式的左邊操作數(shù)和右邊操作數(shù)進行比較，比較結果為真，則返回1，為假則返回0，比較結果不確定則返回NULL。MySQL支持的比較運算符如表6-1所示。知識鏈接——比較運算符betweenand用來判斷字段的數(shù)值是否在指定范圍內。betweenand需要兩個參數(shù)，即范圍的起始值和終止值。如果字段值在指定的范圍內，則這些記錄被返回。如果不在指定范圍內，則不會被返回。notbetweenand表示指定范圍之外的值。如果字段值不在指定范圍內的值，則這些記錄被返回。betweenand/notbetweenand常用于查詢指定范圍內的記錄時。語法格式為：知識鏈接——betweenand/notbetweenandvalue1：表示范圍的起始值。value2：表示范圍的終止值。isnull用來判斷字段的值是否為空值（null）?？罩挡坏韧?，也不等同于空字符串。如果字段的值是空值，則滿足查詢條件，該記錄將被查詢出來。如果字段的值不是空值，則不滿足查詢條件。isnotnull表示字段的值不是空值（null）時滿足條件。知識鏈接——isnull/isnotnullin運算符用來判斷表達式的值是否位于給出的集合中；如果是，返回值為1，否則返回值為0。notin的作用和in恰好相反，notin用來判斷表達式的值是否不存在于給出的集合中；如果不是，返回值為1，否則返回值為0。其基本語法格式如下：知識鏈接——in/notinvalue1,value2,value3...valueN表示集合中的值，各值之間用逗號隔開，字符型和日期型的需要加上單引號。like/notlike關鍵字用于匹配字符串是否相等。like在字段中的內容與指定的字符串匹配時滿足條件；notlike在字段中的內容與指定的字符串不匹配時滿足條件。知識鏈接——like/notlikestr表示指定用來匹配的字符串，該字符串必須加單引號或者雙引號。其可以是一個完整的字符串，也支持百分號“%”和下劃線“_”通配符。百分號“%”通配符可以代表任意長度的字符串，字符串的長度可以為0。下劃線“_”通配符只能表示單個字符，字符的長度不能為0。默認情況下，like/notlike關鍵字匹配字符的時候是不區(qū)分大小寫的。如果需要區(qū)分大小寫，需要加入binary關鍵字。使用函數(shù)查詢任務3任務描述任務目標任務分析任務實施123知識鏈接45

在對數(shù)據(jù)庫查詢時，常常需要對數(shù)據(jù)進行統(tǒng)計或計算。完成數(shù)值型函數(shù)、字符串函數(shù)、日期和時間函數(shù)、聚合函數(shù)的常用函數(shù)的函數(shù)值的輸出，完成統(tǒng)計數(shù)據(jù)表的記錄數(shù)量、獲取字段的最大值和最小值、計算字段的總和、計算平均值等。任務描述（1）會使用數(shù)值型函數(shù)。（2）會使用字符串函數(shù)。（3）會使用日期和時間函數(shù)。（4）會使用聚合函數(shù)。（5）引導學生學習更多的函數(shù)，培養(yǎng)學生主動擴展自己知識面，自主學習的習慣。任務目標學習MySQL常用函數(shù)的功能，根據(jù)具體的需求，選擇相應的函數(shù)，通過輸出函數(shù)值掌握數(shù)值型函數(shù)、字符串函數(shù)、日期和時間函數(shù)的使用方法。使用count、sum、avg、max和min等聚合函數(shù)完成數(shù)據(jù)的統(tǒng)計與計算。任務分析MySQL函數(shù)任務實施步驟1準備工作向數(shù)據(jù)表tb_grade中插入多條數(shù)據(jù)。任務實施mysql>usechjgl_db;mysql>insertintotb_grade(sno,cno,tno,usualgrade,termgrade)->values('202115010201','204001','202101',90,98),->('202115010201','204002','202101',92,95),->('202115010201','204003','202101',96,95),->('202115010202','204001','202101',75,70),->('202115010202','204002','202101',70,72),->('202115010202','204003','202101',72,69);步驟2

數(shù)值型函數(shù)(1)輸出函數(shù)rand()，round(3.14)，round（3.1415,2），mod(10,3)的值。mysql>selectrand(),round(3.14),round(3.1415,2),mod(10,3);任務實施(2)輸出函數(shù)abs(-100)，sign(-100)，sign(100)，sign(0)的值。mysql>selectabs(-100),sign(-100),sign(100),sign(0);步驟3字符串函數(shù)(1)輸出函數(shù)length('welcome!')，concat('how','','are','','you!')的值。mysql>altertabletb_classaddconstraintfk_dep_id1foreignkey(dno)referencestb_department(d_no)ondeletecascadeonupdaterestrict;任務實施(2)輸出函數(shù)lower('WELCOMETOCHINA!')，upper('hello!')的值。mysql>selectlower('WELCOMETOCHINA!'),upper('hello!');任務實施(3)輸出函數(shù)insert('李小靜',2,1,'曉'),replace('abaab','a','c')的值。mysql>selectinsert('李小靜',2,1,'曉'),replace('abaab','a','c');(4)輸出函數(shù)substring('welcome',4,2)，substring('welcome',4)，substring('welcome',-4)，substring('welcome',-4,2)的值。任務實施mysql>selectsubstring('welcome',4,2),->substring('welcome',4),->substring('welcome',-4),->substring('welcome',-4,2);任務實施小貼士：（1）substring(s，n，len)表示返回從字符串s第n個位置開始，長度為len的子字符串。如果n為負值，則返回從字符串s倒數(shù)第n個位置開始，長度為len的子字符串。（2）substring(s，n)，表示返回從字符串s第n個位置開始到字符串結尾的子字符串。如果n為負值，則返回從字符串s倒數(shù)第n個位置到字符串結尾的子字符串。(4)輸出函數(shù)reverse('welcome')的值。任務實施mysql>selectreverse('welcome');步驟4日期和時間函數(shù)(1)輸出當前系統(tǒng)的日期值、時間值、日期和時間值。mysql>selectcurdate(),curtime(),now();任務實施(2)在數(shù)據(jù)表tb_student中，查詢姓名為“王苗苗”的出生年份和出生月份。mysql>usechjgl_db;mysql>selectyear(sbirthday)as出生年份,month(sbirthday)as出生月份fromtb_studentwheresname='王苗苗';任務實施(3)在數(shù)據(jù)表tb_student中，查詢姓名為“王苗苗”出生月份的英文名稱，以及出生日期對應的星期幾的英文名稱。mysql>selectmonthname(sbirthday),dayname(sbirthday)fromtb_studentwheresname='王苗苗';步驟5聚合函數(shù)(1)在數(shù)據(jù)表tb_grade中，按照綜合成績=平時成績*40%+期末成績*60%公式，修改數(shù)據(jù)表中綜合成績字段totalgrade的值。mysql>updatetb_gradesettotalgrade=(usualgrade*0.4+termgrade*0.6);任務實施(2)在數(shù)據(jù)表tb_grade中，查詢課程號cno=“204001”的綜合成績的最高分和最低分。mysql>selectmax(totalgrade),min(totalgrade)fromtb_gradewherecno='204001';(3)在數(shù)據(jù)表tb_grade中，查詢學號sno=“202115010202”的學生的各科綜合成績的總分，四舍五入保留1位小數(shù)。mysql>selectround(sum(totalgrade),1)fromtb_gradewheresno='202115010202';任務實施(4)在數(shù)據(jù)表tb_grade中，查詢學號sno=“202115010202”的學生的各科綜合成績的平均分，四舍五入保留2位小數(shù)。mysql>selectround(avg(totalgrade),2)fromtb_gradewheresno='202115010202';任務實施(5)在數(shù)據(jù)表tb_student中，查詢學生的總人數(shù)。mysql>selectcount(sno)fromtb_student;MySQL包含了大量并且豐富的函數(shù)，這些函數(shù)可以幫助用戶更加方便地處理數(shù)據(jù)表中的數(shù)據(jù)。每個用戶都可以調用已經(jīng)存在的函數(shù)來完成某些功能。MySQL常用函數(shù)包括數(shù)值型函數(shù)、字符串函數(shù)、日期時間函數(shù)、聚合函數(shù)等。MySQL支持的常用函數(shù)如表6-2至表6-5所示。知識鏈接——MySQL函數(shù)知識鏈接——MySQL函數(shù)知識鏈接——MySQL函數(shù)多條件查詢任務4任務描述任務目標任務分析任務實施123知識鏈接45

在對數(shù)據(jù)庫查詢時，往往需要多個查詢條件共同使用，才可以查詢到用戶需求的數(shù)據(jù)。完成記錄滿足所有查詢條件、記錄滿足任意一個查詢條件、記錄滿足其中一個條件，并且不滿足另一個條件時多條件的數(shù)據(jù)查詢操作。任務描述（1）會帶and（&&）的多條件查詢。（2）會帶or（||）的多條件查詢。（3）會帶xor的多條件查詢。（4）引導學生按照需求寫出恰當?shù)牟樵儣l件，培養(yǎng)學生自己思考、自己解決的行為方式。任務目標學習MySQL邏輯運算符的功能，根據(jù)具體的需求，依次寫出各個查詢條件，選擇合適的邏輯運算符連接多個查詢條件，精確的查詢到所需數(shù)據(jù)。任務分析運算符的優(yōu)先級任務實施多條件查詢步驟1帶and（&&）的多條件查詢(1)在數(shù)據(jù)表tb_grade中，查詢綜合成績在60分到75分之間的學生的成績單。mysql>usechjgl_db;mysql>select*fromtb_gradewheretotalgrade>=60andtotalgrade<=75;任務實施(2)在數(shù)據(jù)表tb_student中，查詢計算機應用專業(yè)姓“劉”的學生的學號、姓名和專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwhere(sspecialty='計算機應用')and(snamelike'劉%');任務實施任務實施(3)在數(shù)據(jù)表tb_student中，查詢在2021年年齡等于21歲的女生的學號、姓名和年齡。。mysql>selectsnoas學號,snameas姓名,ssexas性別,(2021-year(sbirthday))as年齡fromtb_studentwhere(2021-year(sbirthday))=21and(ssex='女');步驟2帶or（||）的多條件查詢(1)在數(shù)據(jù)表tb_grade中，查詢綜合成績在95分以上（包括95分），或者期末成績在98分以上（包含98分）的學生的成績單。mysql>select*fromtb_gradewheretotalgrade>=95ortermgrade>=98;任務實施(2)在數(shù)據(jù)表tb_student中，查詢姓“劉”和姓“王”的學生的學號、姓名和性別。mysql>selectsnoas學號,snameas姓名,ssexas性別fromtb_studentwheresnamelike'劉%'orsnamelike'王%';任務實施步驟3帶xor的多條件查詢(1)在數(shù)據(jù)表tb_student中，查詢姓“朱”的女生，和不姓“朱”的男生的學生的學號、姓名和性別。mysql>selectsnoas學號,snameas姓名,ssexas性別fromtb_studentwheresnamelike'朱%'xorssex='男';任務實施(2)在數(shù)據(jù)表tb_student中，給朱麗麗錄入固定電話，給朱華華錄入移動電話，查詢只有固定電話或者只有移動電話的學生的姓名、固定電話和移動電話。mysql>updatetb_studentsetsteleno=wheresname='朱麗麗';mysql>updatetb_studentsetsmobno=wheresname='朱華華';mysql>selectsnameas姓名,stelenoas固定電話,smobnoas移動電話fromtb_studentwherestelenoisnotnullxorsmobnoisnotnull;結果顯示，既有固定電話也有移動電話的學生信息、固定電話或者移動電話都沒有的學生信息均不在查詢結果里。任務實施任務實施在where關鍵詞后可以有多個查詢條件，這樣能夠使查詢結果更加精確。多個查詢條件時用邏輯運算符and（&&）、or（||）或xor隔開。and（&&）：邏輯與，記錄滿足所有查詢條件時，才會被查詢出來。or（||）：邏輯或，記錄滿足任意一個查詢條件時，才會被查詢出來。xor：邏輯異或，記錄滿足其中一個條件，并且不滿足另一個條件時，才會被查詢出來。知識鏈接——多條件查詢運算符的優(yōu)先級決定了不同的運算符在表達式中計算的先后順序，MySQL中的常用運算符及優(yōu)先級按照由高到底的排列順序如表6-6所示，一般情況下，級別高的運算符優(yōu)先進行計算，如果級別相同，MySQL按表達式的順序從左到右依次計算。知識鏈接——運算符的優(yōu)先級實際應用過程中，很少有人能將這些優(yōu)先級熟練記憶，而是使用圓括號“()”將需要優(yōu)先的操作括起來，這樣既可以改變優(yōu)先級，又可以使計算過程更加清晰。查詢排序任務5任務描述任務目標任務分析任務實施123知識鏈接45

在對數(shù)據(jù)庫查詢時，查詢出來的數(shù)據(jù)一般都是按照數(shù)據(jù)最初被添加到數(shù)據(jù)表中的順序來顯示，可能是無序的，或者其排列順序不是用戶所期望的。實際工作過程中，經(jīng)常需要對查詢結果按照某一個字段或者多個字段進行升序或者降序排列，從而滿足用戶的要求。任務描述（1）會將查詢結果按升序排列。（2）會將查詢結果按降序排列。（3）會將查詢結果按多個字段排序。（4）通過對查詢結果進行排序，讓學生養(yǎng)成認真做事的良好態(tài)度。任務目標學習orderby子句的功能，根據(jù)具體的需求，按照適當?shù)淖侄芜M行升序或降序排列，讓查詢結果顯示的更加有序清晰。任務分析任務實施orderby子句步驟1查詢結果按升序排列(1)在數(shù)據(jù)表tb_grade中，查詢綜合成績在90分以上（包括90分）的學生的成績單，并按照綜合成績升序排列。mysql>usechjgl_db;mysql>select*fromtb_gradewheretotalgrade>=90orderbytotalgrade;任務實施(2)在數(shù)據(jù)表tb_student中，查詢學生的學號、姓名和性別，并按照性別進行升序排列。mysql>selectsnoas學號,snameas姓名,ssexas性別fromtb_studentorderbyssexasc;任務實施任務實施步驟2查詢結果按降序排列(1)在數(shù)據(jù)表tb_grade中，查詢綜合成績在95分以上（包括95分）或者期末成績在98分以上（包括98分）的學生的成績單，并按照綜合成績降序排列。mysql>select*fromtb_gradewhere(totalgrade>=95)or(termgrade>=98)orderbytotalgradedesc;任務實施(2)在數(shù)據(jù)表tb_student中，查詢學生的學號、姓名和出生日期，并按照出生日期進行降序排列。小貼士：按照升序排列時，字段為空值（null）的記錄最先顯示，按照降序排列時，字段為空值（null）的記錄在最后顯示，即當排序的字段中存在空值（null）時，會將該空值（null）作為最小值來對待。mysql>selectsnoas學號,snameas姓名,sbirthdayas出生日期fromtb_studentorderbysbirthdaydesc;步驟3

查詢結果按多個字段排序（1）查詢數(shù)據(jù)表tb_student中專業(yè)為空值的學生的學號、姓名、專業(yè)。mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentwheresspecialtyisnull;任務實施(2)在數(shù)據(jù)表tb_student中，將學號開頭字符為“20211401”的學生的專業(yè)字段設置為“工程測量”，并查詢學生的學號、姓名和專業(yè)，并按照專業(yè)進行降序排列，同一專業(yè)的按照學號升序排列。任務實施mysql>updatetb_studentsetsspecialty='工程測量'wheresnolike'20211401%';mysql>selectsnoas學號,snameas姓名,sspecialtyas專業(yè)fromtb_studentorderbysspecialtydesc,snoasc;orderby子句主要用來將查詢結果中的數(shù)據(jù)按照一定的順序進行排序。其語法格式如下：知識鏈接——orderby子句語法分析：ORDERBY：關鍵字，用來將查詢結果中的數(shù)據(jù)按照一定的順序進行排序。col_name|expr：字段名或者表達式，表示按照該字段或者表達式進行排序。ASC|DESC：ASC表示按照升序進行排列，DESC表示按照降序進行排列，默認值是ASC。分組查詢任務6任務描述任務目標任務分析任務實施123知識鏈接45

在對數(shù)據(jù)庫查詢時，經(jīng)常需要根據(jù)一個或多個字段對查詢結果進行分組或者分組統(tǒng)計。依據(jù)具體的需求，完成數(shù)據(jù)分組查詢、分組統(tǒng)計和對分組后的數(shù)據(jù)進行過濾。任務描述（1）會將查詢結果按一個字段進行分組。（2）會將查詢結果按多個字段進行分組。（3）會將groupby與group_concat()一起使用。（4）會將groupby與聚合函數(shù)一起使用。（5）會將groupby與withrollup一起使用。（6）會對分組后的數(shù)據(jù)進行過濾。（7）通過逐一的學習各個關鍵字，并加以綜合應用，提升學生分析問題、解決問題的能力。任務目標學習groupby子句的功能和基本語法格式，分析具體的需求，根據(jù)一個或多個字段對查詢結果進行分組或者分組統(tǒng)計，并選擇groupby和group_concat()、聚合函數(shù)、withrollup共同使用，完成數(shù)據(jù)的分組統(tǒng)計，以及通過having子句對查詢結果數(shù)據(jù)進行過濾。任務分析groupby關鍵字與聚合函數(shù)groupby子句與group_concat函數(shù)任務實施groupby子句任務實施步驟1groupby關鍵字單獨使用在數(shù)據(jù)表tb_student中，根據(jù)性別進行分組查詢。mysql>usechjgl_db;mysql>selectssexfromtb_studentgroupbyssex;結果只顯示了兩條記錄，這兩條記錄的ssex字段的值分別為“女”和“男”。任務實施步驟2groupby與group_concat()一起使用（1）在數(shù)據(jù)表tb_student中，根據(jù)性別進行分組查詢，并且將每個分組的學生姓名都顯示出來。mysql>selectssex,group_concat(sname)fromtb_studentgroupbyssex;結果顯示，查詢結果分為“女”和“男”兩組，并且每組的學生姓名都顯示出來了。任務實施（2）在數(shù)據(jù)表tb_grade中，根據(jù)學號進行分組查詢，并且將每個分組的學生選修的課程編號、綜合成績都顯示出來。mysql>selectsno,group_concat(cno),group_concat(totalgrade)fromtb_gradegroupbysno;步驟3groupby與聚合函數(shù)一起使用（1）在數(shù)據(jù)表tb_student中，統(tǒng)計查詢男女生的總數(shù)，并分組顯示出男女生的姓名。mysql>selectssexas性別,count(ssex)as人數(shù),group_concat(sname)as姓名fromtb_studentgroupbyssex;由結果可以看到，查詢結果分為“女”和“男”兩組，并且每組的記錄總數(shù)和學生姓名都顯示出來了。任務實施（2）在數(shù)據(jù)表tb_grade中，統(tǒng)計查詢每位學生所有科目綜合成績的總分和平均分，取兩位小數(shù)。mysql>selectsnoas學號,round(sum(totalgrade),2)as總分,round(avg(totalgrade),2)as平均分fromtb_gradegroupbysno;任務實施（3）在數(shù)據(jù)表tb_grade中，統(tǒng)計查詢各科綜合成績的最高分和最低分。mysql>selectcnoas課程號,max(totalgrade)as最高分,min(totalgrade)as最低分fromtb_gradegroupbycno;任務實施步驟4

按照多個字段進行分組（1）準備工作①在數(shù)據(jù)表tb_grade中，添加202102學期學生的各科平時成績和期末成績。mysql>insertintotb_grade(sno,cno,tno,usualgrade,termgrade)->values('202115010201','204004','202102',80,98),->('202115010201','204005','202102',75,95),->('202115010201','204006','202102',96,100),->('202115010202','204004','202102',60,82),->('202115010202','204005','202102',60,52),->('202115010202','204006','202102',75,89);任務實施②在數(shù)據(jù)表tb_grade中，計算出202102學期學生的綜合成績。mysql>updatetb_gradesettotalgrade=round((usualgrade*0.4+termgrade*0.6),2)wheretno='202102';任務實施（2）在數(shù)據(jù)表tb_grade中，統(tǒng)計查詢各學期每位學生綜合成績的最高分和最低分。mysql>selecttnoas學期,snoas學號,max(totalgrade)as最高分,min(totalgrade)as最低分fromtb_gradegroupbytno,sno;由結果可以看到，分組過程中，依次按照sno和tno進行分組。任務實施步驟5groupby與withrollup一起使用（1）在數(shù)據(jù)表tb_student中，統(tǒng)計查詢男女生的總數(shù)、分組顯示出男女生的姓名，并統(tǒng)計學生的總數(shù)和所有學生姓名。mysql>selectssexas性別,count(ssex)as人數(shù),group_concat(sname)as姓名fromtb_studentgroupbyssexwithrollup;由結果可以看到，查詢結果分為“女”和“男”兩組，并且每組的記錄總數(shù)和學生姓名都顯示出來了。任務實施（2）在數(shù)據(jù)表tb_grade中，統(tǒng)計查詢每位學生參加考試的所有課程號及綜合成績平均分。mysql>selectsnoas學號,group_concat(cno)as課程號,round(avg(totalgrade))as平均分fromtb_gradegroupbysnowithrollup;任務實施步驟6groupby與having一起使用在數(shù)據(jù)表tb_grade中，統(tǒng)計查詢各學期綜合成績的平均分高于90分的學生。mysql>selecttnoas學期,snoas學號,round(avg(totalgrade),2)as平均分fromtb_gradegroupbytno,snohavingavg(totalgrade)>90;任務實施groupby子句主要用來將查詢結果按照某個字段或者多個字段進行分組。其語法格式如下：知識鏈接——groupby子句GROUPBY：關鍵字，根據(jù)一個或多個字段對查詢結果進行分組。col_name|expr：字段名或者表達式，表示按照該字段或者表達式進行分組。WITHROLLUP：WITHPOLLUP關鍵字用來在所有記錄的最后加上一條記錄，這條記錄是上面所有記錄的總和。HAVINGwhere_condition：用于選擇滿足條件的分組數(shù)據(jù)。HAVING查詢條件中可以使用聚合函數(shù)，也可以使用字段別名。在數(shù)據(jù)統(tǒng)計時，groupby關鍵字經(jīng)常和聚合函數(shù)一起使用。常用的聚合函數(shù)包括COUNT()，SUM()，AVG()，MAX()和MIN()。其中，COUNT()用來統(tǒng)計記錄的條數(shù)；SUM()用來計算字段值的總和；AVG()用來計算字段值的平均值；MAX()用來查詢字段的最大值；MIN()用來查詢字段的最小值。知識鏈接——groupby關鍵字與聚合函數(shù)groupby關鍵字可以和group_concat函數(shù)一起使用。group_concat()函數(shù)會把每個分組中指定的字段值都顯示出來。知識鏈接——groupby子句與group_concat函數(shù)連接查詢任務7任務描述任務目標任務分析任務實施123知識鏈接45

如果需要查詢的數(shù)據(jù)來自多個數(shù)據(jù)表，經(jīng)常用到多表連接查詢。有時需要獲取兩表共有的記錄，有時需要獲取左表（右表）的所有記錄，右表（左表）對應的數(shù)據(jù)進行拼接，根據(jù)實際需求選擇合適的多表連接方式，完成多表數(shù)據(jù)的查詢，并總結三種連接查詢的區(qū)別。設置合適的條件，完成對多表連接查詢結果的數(shù)據(jù)過濾。任務描述（1）會使用內連接查詢。（2）會使用左連接查詢。（3）會使用右連接查詢。（4）會復合條件連接查詢。（5）實踐操作過程中，培養(yǎng)學生細心，耐心的素質，成為有心人。任務目標學習連接查詢的基本語法格式，通過內連接、左連接、右連接完成多表連接查詢，觀察多表查詢返回的數(shù)據(jù)特點，總結三種連接查詢的區(qū)別，使用where或者having子句對多表連接查詢結果數(shù)據(jù)進行過濾。任務分析三種連接查詢的區(qū)別任務實施連接查詢步驟1內連接在數(shù)據(jù)表tb_student和tb_grade中，使用內連接查詢學生的學號，姓名，課程編號和綜合成績。mysql>usechjgl_db;mysql>selects.sno,s.sname,o,g.totalgradefromtb_studentsinnerjointb_gradegons.sno=g.sno;任務實施任務實施步驟2左連接在數(shù)據(jù)表tb_student和tb_grade中，使用左連接查詢學生的學號，姓名，課程編號和綜合成績。mysql>selects.sno,s.sname,o,g.totalgradefromtb_studentsleftjointb_gradegons.sno=g.sno;步驟3右連接在數(shù)據(jù)表tb_student和tb_grade中，使用右連接查詢學生的學號，姓名，課程編號和綜合成績。mysql>selects.sno,s.sname,o,g.totalgradefromtb_studentsrightjointb_gradegons.sno=g.sno;任務實施步驟4

復合條件連接查詢（1）在數(shù)據(jù)表tb_student和tb_grade中，查詢綜合成績大于90分的學生的學號，姓名，課程編號和綜合成績。mysql>selects.sno,s.sname,o,g.totalgradefromtb_studentsjointb_gradegons.sno=g.snowhereg.totalgrade>90;任務實施（2）在數(shù)據(jù)表tb_student和tb_grade中，查詢綜合成績平均分大于90分的學生的學號，姓名和平均分。mysql>selects.snoas學號,s.snameas姓名,round(avg(g.totalgrade))as平均分fromtb_studentsjointb_gradegons.sno=g.snogroupbys.snohavingavg(g.totalgrade)>90;任務實施（3）在數(shù)據(jù)表tb_student和tb_grade中，查詢各學期綜合成績平均分大于90分的學生，并顯示出學期，學號，姓名和平均分。mysql>selectg.tnoas學期,s.snoas學號,s.snameas姓名,round(avg(g.totalgrade))as平均分fromtb_studentsjointb_gradegons.sno=g.snogroupbyg.tno,s.snohavingavg(g.totalgrade)>90;任務實施在關系型數(shù)據(jù)庫中，表與表之間是有聯(lián)系的，所以在實際應用中，經(jīng)常使用連接查詢。連接查詢就是將兩個或兩個以上的表按照某個條件連接起來，從多個表中查詢需要的數(shù)據(jù)。在MySQL中，連接查詢常用的有內連接、左連接和右連接，其語法格式如下：知識鏈接——連接查詢SELECT：查詢數(shù)據(jù)的關鍵字。select_list：查詢數(shù)據(jù)列表。FROMtbl_name1[INNER|LEFT|RIGHT]JOINtbl_name2：指定要查詢數(shù)據(jù)的多個數(shù)據(jù)表，其中，tbl_name為數(shù)據(jù)表名，[INNER]JOIN為內連接，內連接中可以省略INNER關鍵字，只用關鍵字JOIN，LEFTJOIN為左連接，RIGHTJOIN為右連接。多個表連接時，連續(xù)使用[INNER|LEFT|RIGHT]JOIN即可。知識鏈接——連接查詢ONtbl_name1.col_name=tbl_name2.col_name：用來設置連接條件，數(shù)據(jù)表名和字段名之間用符號“.”來表示字段屬于哪個表。內連接查詢：是最常用的連接查詢。內連接的查詢結果都是符合連接條件的記錄。左連接查詢：左連接查詢時，可以查詢出左表中的所有記錄和右表中匹配連接條件的記錄，如果左表的某行在右表中沒有匹配記錄，則會在返回結果中，右表的字段值均為空值（null）。右連接查詢：右連接查詢時，可以查詢出右表中的所有記錄和左表中匹配連接條件的記錄，如果右表的某行在左表中沒有匹配記錄，則會在返回結果中，左表的字段值均為空值（null）。知識鏈接——三種連接查詢的區(qū)別子查詢任務8任務描述任務目標任務分析任務實施123知識鏈接45

在完成較復雜的數(shù)據(jù)查詢過程中，經(jīng)常會出現(xiàn)一個查詢語句的條件來自于另一個查詢語句的查詢結果，這時候需要通過子查詢來實現(xiàn)，根據(jù)實際需求，選擇恰當?shù)谋容^運算符和in、notin、exists、notexists、any、all等關鍵字完成多個數(shù)據(jù)表的嵌套查詢。任務描述（1）會帶in關鍵字的子查詢。（2）會帶exists關鍵字的子查詢。（3）會帶any關鍵字的子查詢。（4）會帶all關鍵字的子查詢。（5）會帶比較運算符的子查詢。（6）通過子查詢語句的學習，告訴學生要腳踏實地，一步一步來。任務目標學習子查詢的各個關鍵字的意義，對于復雜的查詢，可以先寫出子查詢，再寫父查詢。任務分析帶in關鍵字的子查詢帶exists關鍵字的子查詢帶any關鍵字的子查詢任務實施子查詢帶all關鍵字的子查詢帶比較運算符的子查詢步驟1帶in關鍵字的子查詢（1）在數(shù)據(jù)表tb_student和tb_grade中，查詢參加課程編號為204001課程考試的學生姓名。mysql>usechjgl_db;mysql>selectsnamefromtb_student->wheresnoin(selectsnofromtb_gradewherecno='204001');任務實施（2）在數(shù)據(jù)表tb_student和tb_grade中，查詢沒有考試成績的學生姓名。mysql>selectsnamefromtb_student->wheresnonotin(selectsnofromtb_grade);任務實施任務實施步驟2帶exists關鍵字的子查詢（1）在數(shù)據(jù)表tb_student和tb_grade中，如果存在期末成績不及格的學生，就顯示出所有學生的姓名、性別。mysql>selectsnameas姓名,ssexas性別fromtb_student->whereexists(selectsnofromtb_gradewheretermgrade<60);任務實施（2）在數(shù)據(jù)表tb_student和tb_grade中，如果不存在期末成績不及格的學生，就顯示出所有學生的姓名、性別。mysql>selectsnameas姓名,ssexas性別fromtb_student->wherenotexists(selectsnofromtb_gradewheretermgrade<60);步驟3帶any關鍵字的子查詢（1）在數(shù)據(jù)表tb_student中，查詢計算機應用專業(yè)的學生中是否存在比工程測量專業(yè)所有學生的年齡都大的學生，如果存在，顯示她們的學號，姓名和出生日期。mysql>selectsnoas學號,snameas姓名,sbirthdayas出生日期fromtb_student->wheresspecialty='計算機應用'andsbirthday<any(selectsbirthdayfromtb_studentwheresspecialty='工程測量');任務實施（2）在數(shù)據(jù)表tb_student中，查詢計算機應用專業(yè)和工程測量專業(yè)的學生中是否存在密碼相同的學生，如果存在，顯示她們的學號，姓名和密碼。mysql>selectsnoas學號,snameas姓名,spasswordas密碼fromtb_student->wheresspecialty='計算機應用'andspassword=any(selectspasswordfromtb_studentwheresspecialty='工程測量');任務實施步驟4

帶all關鍵字的子查詢在數(shù)據(jù)表tb_student中，查詢年齡比計算機應用專業(yè)都小的學生的學號，姓名和出生日期。mysql>selectsnoas學號,snameas姓名,sbirthdayas出生日期fromtb_student->wheresbirthday>all(selectsbirthdayfromtb_studentwheresspecialty='計算機應用');任務實施步驟5帶比較運算符的子查詢在數(shù)據(jù)表tb_grade中，查詢綜合成績低于平均成績的學生的學號、課程編號和綜合成績。mysql>selectsnoas學號,cnoas課程編號,totalgradeas綜合成績fromtb_grade->wheretotalgrade<(selectavg(totalgrade)fromtb_grade);任務實施子查詢，也被稱為嵌套查詢，是MySQL中比較常用的查詢方法，通過子查詢可以實現(xiàn)多表查詢。子查詢指將一個查詢語句嵌套在另一個查詢語句中。子查詢可以在select、update和delete語句中使用，而且可以進行多層嵌套。在實際開發(fā)時，子查詢經(jīng)常出現(xiàn)在where子句中。子查詢中常包括in、notin、exists、notexists、any、all等關鍵字，還可能包含=、>、<、<>等比較運算符。一般來說，表連接都可以用子查詢替換，但有的子查詢不能用表連接來替換。相對而言，子查詢比較靈活、方便、形式多樣，適合作為查詢的篩選條件，而表連接更適合于查看連接表的數(shù)據(jù)。知識鏈接——子查詢當表達式與子查詢返回的結果集中的某個值相等時，返回true，否則返回false；若使用關鍵字notin，則返回值正好相反。知識鏈接——帶in關鍵字的子查詢用于判斷子查詢的結果集是否為空，若子查詢的結果集不為空，返回true，否則返回false；若使用關鍵字notexists，則返回的值正好相反。知識鏈接——帶exists關鍵字的子查詢只要滿足子查詢語句返回結果集中的任何一個，就可以通過該條件來執(zhí)行父查詢語句。知識鏈接——帶any關鍵字的子查詢只有滿足子查詢語句返回的所有結果，才可以執(zhí)行父查詢語句。知識鏈接——帶all關鍵字的子查詢比較運算符在子查詢時使用非常廣泛，常用的比較運算符包括“=、<>（!=）、>=、<=、>、<”等。知識鏈接——帶比較運算符的子查詢合并查詢結果任務9任務描述任務目標任務分析任務實施123知識鏈接45

在完成較復雜的數(shù)據(jù)查詢過程中，經(jīng)常會需要一次性查詢多條SQL語句，并將每一條select查詢的結果合并成一個結果集返回。這時候就需要用到union操作符，將多個select語句組合起來。根據(jù)實際需求，完成合并查詢結果的操作。任務描述（1）會使用union查詢。（2）會使用unionall查詢。（3）會在合并查詢結果中區(qū)分多表。（4）會在union子句或整句中使用orderby。（5）通過學習如何完成合并查詢結果，培養(yǎng)學生勇于探究與實踐的科學精神。任務目標學習union查詢的使用方法以及注意事項，完成合并查詢結果的操作。任務分析union