Mysql數(shù)據(jù)庫(kù)技術(shù)Mysql數(shù)據(jù)庫(kù)技術(shù)應(yīng)用（微課版）課件 項(xiàng)目5 數(shù)據(jù)查詢

項(xiàng)目5數(shù)據(jù)查詢教材編寫(xiě)組任務(wù)1單表查詢一、select語(yǔ)句的基本語(yǔ)法1、語(yǔ)法格式：Select

查詢內(nèi)容[From

表名列表][Where<條件表達(dá)式>][Groupby<字段名表>[having<分組條件>]][Orderby

字段名表[asc|desc]][Limit[offset,]N]注意：格式中[]內(nèi)的內(nèi)容均可省略；在所有子句中，select子句必選。必須嚴(yán)格按照以下順序書(shū)寫(xiě)select語(yǔ)句：

select→from→where→groupby→orderby→limit任務(wù)1單表查詢說(shuō)明：select子句：查詢內(nèi)容。可以是常量、變量、函數(shù)和表達(dá)式。單獨(dú)使用時(shí)僅作為輸出語(yǔ)句。from子句：數(shù)據(jù)源?？梢允且粋€(gè)或多個(gè)表(視圖)，如果是多個(gè)表通常用于多表連接查詢。where子句：查詢條件。通過(guò)查詢條件或多個(gè)表的連接條件可以實(shí)現(xiàn)對(duì)表的行篩選。groupby子句：對(duì)記錄進(jìn)行分組匯總，通常和聚合函數(shù)一起使用。having選項(xiàng)用于對(duì)分組進(jìn)行限制，放在groupby子句后。Orderby子句：對(duì)查詢結(jié)果按行進(jìn)行排序，asc表示升序(默認(rèn))，desc表示降序。Limit子句：限制查詢結(jié)果顯示的行數(shù)，是MySQL的特定用法。其中，offset表示位置偏移量（表中第1條記錄的位置偏移量默認(rèn)為0，省略時(shí)默認(rèn)從第1條記錄開(kāi)始顯示)，n表示顯示行數(shù)。2、示例例1：select100,'MySQL數(shù)據(jù)庫(kù)',"2018-8-8";例2：selectversion(),database();例3：selectyear(now()),mod(12,5);例4：select100+20,12%5,12>5,'12'>"5";任務(wù)1單表查詢二、基本查詢基本查詢只包括select子句和from子句，查詢結(jié)果包括表中的所有行。1、語(yǔ)法格式

select查詢內(nèi)容

From

源表說(shuō)明：⑴如果要查詢表中的所有列（即源表的所有字段），可以在select子句中使用*。⑵如果要查詢表中的指定列，可在select子句中列出字段名或表達(dá)式，用英文半角逗號(hào)分隔，還可以改變表中字段的顯示順序。⑶在select子句中用distinct關(guān)鍵字可以去掉查詢結(jié)果中的重復(fù)值。⑷可以用as短語(yǔ)改變字段的顯示名稱。格式：字段名[as]別名。⑸在select子句中可以使用函數(shù)、表達(dá)式進(jìn)行簡(jiǎn)單計(jì)算。任務(wù)1單表查詢?nèi)蝿?wù)1單表查詢2、示例查看student表的所有信息：select*fromstudent;查詢學(xué)生的學(xué)號(hào)、姓名和專業(yè)信息：selectsno,sname,subjectfromstudent;還可以改變字段的顯示順序：selectsubject,sno,snamefromstudent;查看學(xué)生的民族信息：selectdistinctnationfromstudent;查詢學(xué)校開(kāi)設(shè)的專業(yè)信息，將查詢結(jié)果的列標(biāo)題改為中文顯示

selectdistinctsubjectas專業(yè)fromstudent;查詢所有學(xué)生的年齡：selectsno,sname,year(now())-year(birthday)asagefromstudent;查詢給所有課程增加%5課時(shí)的課程名、原課時(shí)和現(xiàn)課時(shí)，使用中文列標(biāo)題。

selectcnameas課程名,periodas原課時(shí),period*1.05現(xiàn)課時(shí)fromcourse;任務(wù)1單表查詢?nèi)?、使用where子句的條件查詢1、格式：where<條件>用來(lái)從數(shù)據(jù)表中篩選出滿足條件的行。根據(jù)條件表達(dá)式中使用的運(yùn)算符大體分為以下5種條件查詢。比較條件查詢?cè)诓樵儣l件中使用關(guān)系運(yùn)算符和邏輯運(yùn)算符，查詢滿足條件的記錄。使用[not]

between…and…范圍條件查詢用于搜索測(cè)試表達(dá)式介于某指定范圍內(nèi)的記錄，包含臨界值。使用[not]

in

列表?xiàng)l件查詢用于查詢字段值在列表內(nèi)的記錄。使用[not]like運(yùn)算符的模糊查詢用于查詢字段值與指定的值相匹配的記錄。必須和通配符配合使用。關(guān)于空值（is[not]null）的查詢用于查詢表達(dá)式的值是否為空值的記錄。任務(wù)1單表查詢注意：邏輯運(yùn)算符有優(yōu)先級(jí)，not最高，or最低?？梢允褂脠A括號(hào)改變優(yōu)先級(jí)。模糊查詢中使用通配符%和_，分別代表任意個(gè)字符和單個(gè)字符?？罩凳侵副碇心沉袥](méi)有值，或其值尚未確定的情況?？捎胕snull或isnotnull進(jìn)行判斷。所有的運(yùn)算符都可以和邏輯運(yùn)算符not配合使用，表示相反的條件。任務(wù)1單表查詢2、條件查詢示例⑴使用關(guān)系運(yùn)算符和邏輯運(yùn)算符的比較條件查詢。查詢男生的所有信息：select*fromstudentwheregender='男';查詢漢族男生的所有信息：select*fromstudentwherenation='漢'andgender='男';查詢少數(shù)民族學(xué)生或女生的信息：select*fromstudentwherenation<>'漢'orgender='女';⑵使用between…and…運(yùn)算符的范圍條件查詢查詢選課表中成績(jī)?cè)?0-90之間的學(xué)生成績(jī)信息：select*fromstudywherescorebetween80and90;查詢選成績(jī)不在80-90之間的學(xué)生成績(jī)信息：select*fromstudywherescorenotbetween80and90;查詢2002年出生的學(xué)生信息：select*fromstudentwherebirthdaybetween'2002-1-1'and'2002-12-31';⑶使用in運(yùn)算符的列表?xiàng)l件查詢查詢回族和侗族學(xué)生的信息：select*fromstudentwherenationin('回','侗');查詢不是回族和侗族學(xué)生的信息：select*fromstudentwherenationnotin('回','侗');⑷使用like運(yùn)算符的模糊查詢，要與通配符%和_一起使用查詢姓張學(xué)生的信息：select*fromstudentwheresnamelike'張%';查詢姓李且名字中只有兩個(gè)字的學(xué)生信息：select*fromstudentwheresnamelike'李_';查詢課程名中含有基礎(chǔ)兩個(gè)字的課程信息：select*fromcoursewherecnamelike'%基礎(chǔ)%';⑸關(guān)于空值的查詢查看開(kāi)設(shè)學(xué)期不確定的課程信息：select*fromcoursewheretermisnull;查詢課時(shí)已確定的課程號(hào)、課程名和課時(shí)數(shù)：selectcno,cname,periodfromcoursewhereperiodisnotnull;四、使用groupby子句的分組查詢1、聚合函數(shù)用于對(duì)查詢結(jié)果中的指定字段進(jìn)行統(tǒng)計(jì)，并返回單個(gè)計(jì)算結(jié)果。常見(jiàn)的聚合函數(shù)有5個(gè)：sum()、avg()、count()、max()、min()。注意：聚合函數(shù)必須有參數(shù)，且參數(shù)必須放在函數(shù)名后的一對(duì)圓括號(hào)中；sum/avg：求和/平均值，參數(shù)類型必須是數(shù)值；count：計(jì)數(shù)，僅統(tǒng)計(jì)參數(shù)值為非null值的行，當(dāng)參數(shù)為*時(shí)返回檢索行的總數(shù)；max/min：求最大值/最小值，參數(shù)類型可以是數(shù)值型、日期型和字符型。6任務(wù)1單表查詢2、groupby子句在select查詢中，使用groupby子句，配合聚合函數(shù)可按指定的表達(dá)式對(duì)查詢結(jié)果進(jìn)行分組匯總。注意：在select子句中，和聚合函數(shù)一起出現(xiàn)的字段名，一定要寫(xiě)在groupby子句中。如果select語(yǔ)句中沒(méi)有g(shù)roupby子句，則聚合函數(shù)對(duì)整個(gè)查詢結(jié)果進(jìn)行統(tǒng)計(jì)；如果select語(yǔ)句中包含groupby子句，則聚合函數(shù)按分組進(jìn)行統(tǒng)計(jì)。3、having<分組條件>選項(xiàng)對(duì)groupby分組的結(jié)果進(jìn)行篩選，使用時(shí)放在groupby子句后。注意：

having選項(xiàng)只能配合groupby使用having選項(xiàng)和where子句可以同時(shí)使用。where子句是在分組前對(duì)記錄進(jìn)行篩選，having選項(xiàng)是對(duì)分組結(jié)果進(jìn)行限制。having選項(xiàng)中的條件可以使用聚合函數(shù)，而where子句中不能使用聚合函數(shù)。任務(wù)1單表查詢4、示例聚合函數(shù)在查詢中的應(yīng)用統(tǒng)計(jì)course表中的課程總數(shù)：selectcount(*)fromcourse;統(tǒng)計(jì)course表中已確定課時(shí)的課程數(shù)：selectcount(period)fromcourse;查看study表的最高分、最低分和平均分：selectmax(score)最高分,min(score)最低分,avg(score)平均分fromstudy;關(guān)于groupby子句和having選項(xiàng)的使用統(tǒng)計(jì)student表中男女生人數(shù)：selectgender,count(*)fromstudentgroupbygender;統(tǒng)計(jì)student表各民族學(xué)生人數(shù)：selectnation,count(*)fromstudentgroupbynation;查詢不同年份出生的學(xué)生人數(shù)：selectyear(birthday),count(*)fromstudentgroupbyyear(birthday);查詢每門(mén)課程的考試人數(shù)和課程平均分：selectcno,count(*),avg(score)fromstudygroupbycno;查詢課程平均分在80分以上的課程的考試人數(shù)和課程平均分：

selectcno課程號(hào),count(*)考試人數(shù),avg(score)課程平均分fromstudygroupbycnohavingavg(score)>=80;查詢每個(gè)學(xué)生的考試課程數(shù)和平均分：selectsno,count(cno),avg(score)fromstudygroupbysno;查詢參加2門(mén)以上課程考試的學(xué)生的考試課程數(shù)和平均分：selectsno,count(*),avg(score)fromstudygroupbysnohavingcount(*)>2;統(tǒng)計(jì)已確定開(kāi)設(shè)學(xué)期的課程的課程數(shù)和平均課時(shí)，且每學(xué)期平均課時(shí)要求大于60：

Selectterm,count(*),avg(period)fromcoursewheretermisnotnullgroupbytermhavingavg(period)>60;任務(wù)1單表查詢?nèi)蝿?wù)1單表查詢五、使用orderby子句和limit子句的查詢1、使用orderby子句對(duì)查詢結(jié)果進(jìn)行排序說(shuō)明：因?yàn)閡tf8mb4字符集不支持中文排序，若要對(duì)中文字段值排序，需要用convert()函數(shù)將字段編碼轉(zhuǎn)換為支持中文的編碼，如gbk編碼。如果進(jìn)行多字段排序，查詢結(jié)果先按照第1個(gè)字段的值排序，第1個(gè)字段值相同的數(shù)據(jù)行，再按照第2個(gè)字段的值排序，依次類推。2、使用limit子句限制返回的查詢行數(shù)用來(lái)限定查詢結(jié)果的起始行（默認(rèn)值為0，即第1行）和總行數(shù)。任務(wù)1單表查詢3、示例⑴使用orderby子句對(duì)查詢結(jié)果進(jìn)行排序查詢選課表中的成績(jī)信息，按成績(jī)降序排序。select*fromstudyorderbyscoredesc;查詢課程的所有信息，按開(kāi)設(shè)學(xué)期升序排序。select*fromcourseorderbyterm;查詢學(xué)生的所有信息，結(jié)果按性別升序排序。select*fromstudentorderbyconvert(genderusinggbk);查詢學(xué)生信息，先按性別升序排序，再按民族降序排序。select*fromstudentorderbyconvert(genderusinggbk),convert(nationusinggbk)desc;⑵使用limit子句限定查詢的返回行查詢student表前3條記錄的所有內(nèi)容。select*fromstudentlimit0,3;

或者

select*fromstudentlimit3;查詢student表中從第3條記錄開(kāi)始的4條記錄select*fromstudentlimit2,4;任務(wù)1單表查詢⑶orderby子句和limit子句配合使用查詢study表中成績(jī)較好的前3名的所有信息。select*fromstudyorderbyscoredesclimit3;查詢study表中成績(jī)較差后5名的所有信息。select*fromstudyorderbyscorelimit5;查詢student表中年齡最小的學(xué)生信息。select*fromstudentorderbybirthdaydesclimit1;查詢年齡最大學(xué)生的學(xué)號(hào)、姓名、出生日期及所學(xué)專業(yè)信息。selectsno,sname,birthday,subjectfromstudentorderbybirthdaylimit1;一、內(nèi)連接查詢內(nèi)連接查詢使用的連接方式是內(nèi)連接，有兩種語(yǔ)法格式：格式1：from表1[Inner]join表2on連接條件

（SQL標(biāo)準(zhǔn)語(yǔ)法）格式2：from表1,表2where連接條件

（MySQL語(yǔ)法）

說(shuō)明：兩個(gè)格式中的連接條件都是“主表.主鍵=從表.外鍵”兩個(gè)格式的執(zhí)行結(jié)果一樣，格式1適用于所有的DBMS。任務(wù)2多表連接查詢例1：查詢所有學(xué)生的班級(jí)信息，查詢結(jié)果包括學(xué)號(hào)、姓名、性別和所在班級(jí)的班級(jí)名稱。分析：⑴要查詢的學(xué)號(hào)、姓名和性別在student表中，而班級(jí)名稱在class表中，所以要使用多表查詢；⑵student表中的所在班級(jí)字段classid是class表的外鍵，兩表存在主從關(guān)系，所以，使用內(nèi)連接查詢，連接條件是class.classid=student.classid。使用SQL標(biāo)準(zhǔn)語(yǔ)法，命令如下：selectsno,sname,gender,classnamefromstudentjoinclassonclass.classid=student.classid;使用MySQL語(yǔ)法，命令如下:selectsno,sname,gender,classnamefromstudent,class

whereclass.classid=student.classid;任務(wù)2多表連接查詢例2：查詢所有男生所在的班級(jí)信息，查詢結(jié)果包括學(xué)號(hào)、姓名、性別和所在班級(jí)的名稱。分析：⑴要查詢的學(xué)號(hào)、姓名和性別在student表中，而班級(jí)名稱在class表中，所以要使用多表查詢；⑵兩表存在主從關(guān)系，使用內(nèi)連接查詢，連接條件是class.classid=student.classid；⑶男生是性別信息，是student表gender字段的值，要設(shè)置篩選條件為：gender='男'。使用SQL標(biāo)準(zhǔn)語(yǔ)法，語(yǔ)句如下：selectsno,sname,gender,classnamefromstudentjoinclassonclass.classid=student.classidwheregender='男';使用MySQL語(yǔ)法，語(yǔ)句如下：selectsno,sname,gender,classnamefromstudent,classwhereclass.classid=student.classidandgender='男';任務(wù)2多表連接查詢例3：查詢所有學(xué)生各門(mén)課程的考試成績(jī)，查詢結(jié)果包括學(xué)號(hào)、姓名、課程號(hào)和成績(jī)。分析：⑴要查詢的學(xué)號(hào)、姓名字段在student表中，而課程號(hào)、成績(jī)字段在study表中，學(xué)號(hào)字段兩個(gè)表中都有，所以要使用多表查詢；⑵兩表存在主從關(guān)系，使用內(nèi)連接查詢，連接條件是student.sno=study.sno；⑶在select子句中若出現(xiàn)兩表的共有字段，一定要用表名.字段名標(biāo)識(shí)。這里，學(xué)號(hào)sno要用student.sno或study.sno進(jìn)行標(biāo)識(shí)，否則，會(huì)出現(xiàn)如圖所示的錯(cuò)誤。使用SQL標(biāo)準(zhǔn)語(yǔ)法，語(yǔ)句如下：selectstudent.sno,sname,cno,scorefromstudentjoinstudyonstudent.sno=study.sno;使用MySQL語(yǔ)法，語(yǔ)句如下：selectstudent.sno,sname,cno,scorefromstudent,studywherestudent.sno=study.sno;任務(wù)2多表連接查詢一、子查詢子查詢是一個(gè)獨(dú)立完整的select語(yǔ)句，它的查詢結(jié)果可以作為另一個(gè)查詢的查詢條件的一部分，包含子查詢的查詢稱為父查詢。子查詢總是寫(xiě)在一對(duì)圓括號(hào)中。子查詢可以嵌套，執(zhí)行時(shí)由內(nèi)向外，首先執(zhí)行子查詢，其查詢結(jié)果并不顯示，而是傳遞給上一級(jí)父查詢，并作為父查詢的查詢條件來(lái)使用。子查詢通常與比較運(yùn)算符、in運(yùn)算符和exists謂詞結(jié)合使用，對(duì)應(yīng)的形式有比較子查詢、in子查詢和exists子查詢。任務(wù)3子查詢1、比較子查詢父查詢和子查詢之間用比較運(yùn)算符如=、<、>等連接，且子查詢的返回值最多只有一個(gè)。在比較子查詢中，子查詢和父查詢可以是同一個(gè)表，還可以是相關(guān)聯(lián)的兩個(gè)表。例1：查詢成績(jī)高于平均分的成績(jī)信息，結(jié)果包括學(xué)號(hào)、課程號(hào)和成績(jī)。分析：要查詢的學(xué)號(hào)、課程號(hào)和成績(jī)來(lái)自選課表，查詢平均分是子查詢，和成績(jī)有關(guān)，也來(lái)自選課表。子查詢和父查詢的數(shù)據(jù)源是同一個(gè)表。子查詢：selectavg(score)fromstudy#查詢結(jié)果是選課表中的平均分父查詢：selectsno,cno,scorefromstudywherescore>(selectavg(score)fromstudy);任務(wù)3子查詢例2：查詢年齡比“李麗”大的學(xué)生的學(xué)號(hào)、姓名、性別和出生日期。分析：查詢結(jié)果中的字段都來(lái)自student表，子查詢查詢李麗的年齡。年齡和student表中的birthday字段有關(guān)。對(duì)于日期型的數(shù)據(jù)，越早的日期越小，年齡越大。子查詢：selectbirthdayfromstudentwheresname='李麗'#查詢結(jié)果是李麗的出生日期父查詢：selectsno,sname,birthdayfromstudentwherebirthday<(selectbirthdayfromstudentwheresname='李麗');例3：查詢”經(jīng)濟(jì)數(shù)學(xué)”課程的成績(jī)情況，顯示課程號(hào)、學(xué)號(hào)和成績(jī)。分析：查詢結(jié)果中的課程號(hào)、學(xué)號(hào)和成績(jī)字段都來(lái)自study表，子查詢和course表相關(guān)。在子查詢中獲得經(jīng)濟(jì)數(shù)學(xué)課程的課程編號(hào)，根據(jù)課程編號(hào)，查詢study表中的內(nèi)容。子查詢：selectcnofromcoursewherecname='經(jīng)濟(jì)數(shù)學(xué)'#查詢結(jié)果是經(jīng)濟(jì)數(shù)學(xué)課程的課程編號(hào)父查詢：selectcno,sno,scorefromstudywherecno=(selectcnofromcoursewherecname='經(jīng)濟(jì)數(shù)學(xué)');任務(wù)3子查詢例4：查詢成績(jī)低于平均分的學(xué)生的學(xué)號(hào)、姓名、課程號(hào)和成績(jī)。分析：查詢結(jié)果中的姓名來(lái)自student，其他字段來(lái)自study，這兩個(gè)表通過(guò)學(xué)號(hào)sno相關(guān)，使用內(nèi)連接查詢；子查詢和選課表相關(guān)，查詢平均分。子查詢：selectavg(score)fromstudy#子查詢獲得study表的平均分父查詢：selectstudent.sno,sname,cno,scorefromstudentinnerjoinstudyonstudent.sno=study.snowherescore<(selectavg(score)fromstudy);或者selectstudent.sno,sname,cno,scorefromstudent,studywherescore<(selectavg(score)fromstudy)andstudent.sno=study.sno;任務(wù)3子查詢2、in子查詢當(dāng)子查詢的返回值有多個(gè)時(shí)，父查詢和子查詢之間使用in或notin運(yùn)算符連接。在in子查詢中，子查詢和父查詢必須是相關(guān)聯(lián)的兩個(gè)表。例1：查詢會(huì)計(jì)學(xué)院的學(xué)生信息，結(jié)果包括學(xué)號(hào)、姓名和班級(jí)號(hào)。分析：先用子查詢?cè)诎嗉?jí)表class中查詢出會(huì)計(jì)學(xué)院的班級(jí)號(hào)，查找到的班級(jí)號(hào)肯定有多個(gè)，是一個(gè)集合。

再用父查詢從學(xué)生表student中找出班級(jí)號(hào)等于子查詢中某個(gè)班級(jí)號(hào)對(duì)應(yīng)的學(xué)生的學(xué)號(hào)、姓名和所在班級(jí)。

class表和student表之間通過(guò)班級(jí)號(hào)classid關(guān)聯(lián)。子查詢：selectclassidfromclasswheredepartment='會(huì)計(jì)學(xué)院'#查詢結(jié)果是會(huì)計(jì)學(xué)院的班級(jí)號(hào)父查詢：selectsno,sname,classidfromstudentwhereclassidin(selectclassidfromclasswheredepartment='會(huì)計(jì)學(xué)院');任務(wù)3子查詢例2：查詢有不及格成績(jī)的學(xué)生信息，結(jié)果包括學(xué)號(hào)和姓名。分析：先用子查詢?cè)谶x課表study中查詢出成績(jī)<60的學(xué)生的學(xué)號(hào)，查找到的學(xué)號(hào)可能有多個(gè)，是一個(gè)集合。

再用父查詢從學(xué)生表student中找出學(xué)號(hào)等于子查詢中的某個(gè)學(xué)號(hào)對(duì)應(yīng)的學(xué)生信息。

student表和study表之間通過(guò)學(xué)號(hào)sno關(guān)聯(lián)。子查詢：selectsnofromstudywherescore<60#查詢結(jié)果是成績(jī)不及格的學(xué)生的學(xué)號(hào)父查詢：selectsno,snamefromstudentwheresnoin(selectsnofromstudywherescore<60);例3：查詢成績(jī)都及格的學(xué)生的所有信息。分析：先用子查詢?cè)谶x課表study中查詢出成績(jī)<60