第8章 記錄的查詢

第8章記錄的查詢學(xué)習(xí)目標(biāo)：掌握單表查詢的基本操作方法，掌握多表查詢的使用方法，掌握子查詢的使用方法，掌握正則表達(dá)式的查詢方法，掌握合并結(jié)果集的使用方法。8.1單表查詢8.1.1單表查詢語句SELECT[ALL|DISTINCT]selection_expr1[,selection_expr2…]FROMtable_source;1．查詢所有的列在SELECT語句中要查詢所有列時(shí)，用“*”表示表中所有列。

【例8-1】在數(shù)據(jù)庫student_db中，查詢課程表course所有的列記錄。USEstudent_db;SELECT*FROMcourse;8.1單表查詢2．查詢指定的列如果不需要將表中所有列都顯示出來，只需在SELECT語句后面列出要在結(jié)果集中顯示的列名，各個(gè)列之間用逗號分隔。【例8-2】查詢course表中的列CourseName、CourseHour、Credit和Term。SELECTCourseName,CourseHour,Credit,TermFROMcourse;8.1單表查詢3．查詢表達(dá)式的值【例8-3】查詢student表中的全體學(xué)生，顯示列StudentName、Sex、Birthday，以及“年齡:”字符串和年齡。SELECTStudentName,Sex,Birthday,'年齡:',YEAR(NOW())-YEAR(Birthday)FROMstudent;8.1單表查詢【例8-4】計(jì)算表達(dá)式的值111*222+333/25,'aaa'+'bbb'='ccc'。SELECT111*222+333/25,'aaa'+'bbb'='ccc';

8.1單表查詢4．為列取別名selection_expr[AS]alias【例8-5】查詢class表中的所有班級，要求對應(yīng)的列名顯示為中文名稱。SELECTDepartmentNameAS'院系名',ClassNameAS'班名',Grade年級,ClassNumAS人數(shù)

FROMclass;8.1單表查詢5．不顯示重復(fù)的行【例8-6】查詢class表中的院系名。SELECTDepartmentNameFROMclass;

SELECTDISTINCTDepartmentNameFROMclass;8.1單表查詢8.1.2查詢指定的記錄SELECT[ALL|DISTINCT]selection_expr1[,selection_expr2…]FROMtable_source[WHEREsearch_condition];8.1單表查詢1．使用關(guān)系表達(dá)式的條件查詢WHEREexpression1關(guān)系運(yùn)算符expression2【例8-7】在score表中，查詢不及格的名單。SELECT*FROMscoreWHEREScore<60;8.1單表查詢2．使用邏輯表達(dá)式的條件查詢

WHEREexpression1邏輯運(yùn)算符expression2[邏輯運(yùn)算符expression3…]【例8-8】在student表中，查詢2003-09-01至2004-05-31之間出生的女生名單。SELECT*FROMstudentWHEREBirthday>='2003-09-01'ANDBirthday<='2004-05-31'ANDSex='女';8.1單表查詢3．使用IN關(guān)鍵字的集合查詢expression[NOT]IN(value1,value2,…)【例8-9】在student表中，查詢生源不是北京、上海、廣東的記錄。SELECT*FROMstudentWHERESourceNOTIN('北京','上海','廣東');8.1單表查詢4．使用BETWEEN…AND關(guān)鍵字的范圍查詢expression[NOT]BETWEENexpression1ANDexpression2例如，xBETWEEN60AND69相當(dāng)于表達(dá)式x>=60ANDx<=69。【例8-10】對例8-8改用BETWEEN…AND關(guān)鍵字實(shí)現(xiàn)查詢。SELECT*FROMstudentWHERE(BirthdayBETWEEN'2003-09-01'AND'2004-05-31')ANDSex='女';8.1單表查詢【例8-11】在course表中，查詢課程名中含有“數(shù)據(jù)”的課程。SELECT*FROMcourseWHERECourseNameLIKE'%數(shù)據(jù)%';

SELECT*FROMcourseWHERECourseNameLIKE'%技術(shù)@_%'ESCAPE'@';8.1單表查詢6．使用ISNULL關(guān)鍵字查詢空值expressionIS[NOT]NULL【例8-12】在class表中，查詢沒有填寫班級人數(shù)的記錄。SELECT*FROMclassWHEREClassNumISNULL;8.1單表查詢8.1.3對結(jié)果集的處理1．使用ORDERBY子句對結(jié)果集排序ORDERBYexpression1[ASC|DESC][,expression2[ASC|DESC],…]【例8-13】在class表中，按ClassNum升序顯示。SELECT*FROMclassORDERBYClassNumASC;8.1單表查詢【例8-14】在score表中，查詢成績大于或等于80分的學(xué)生的學(xué)號、課程號和成績，并先按成績的降序，再按學(xué)號的升序排列，列名顯示為中文。SELECTStudentIDAS學(xué)號,CourseID課程號,Score成績FROMscoreWHEREScore>=80ORDERBYScoreDESC,StudentIDASC;8.1單表查詢2．使用LIMIT子句限制結(jié)果集的行數(shù)LIMITrow_count[OFFSEToffset]【例8-15】在course表中，顯示課程編號中第2～5條的記錄。SELECT*FROMcourseLIMIT4OFFSET1;8.1單表查詢【例8-16】在student表中，查詢年齡較大的第3、4、5名學(xué)生的記錄。SELECT*FROMstudentORDERBYBirthdayASCLIMIT3OFFSET2;8.1單表查詢8.1.4聚合函數(shù)查詢SELECT語句的輸出列包含聚合函數(shù)，聚合函數(shù)是MySQL提供的一類內(nèi)置函數(shù)，常用于對一組值進(jìn)行計(jì)算，并返回單個(gè)值。8.1單表查詢1．COUNT()【例8-17】在class表中，查詢ClassNum列的行數(shù)，分別查詢包含NULL行和不包含NULL行的行數(shù)。SELECTCOUNT(*)AS'包含NULL值的行數(shù)',COUNT(ClassNum)AS'非空行數(shù)'FROMclass;8.1單表查詢【例8-18】在student表中，查詢男生、女生和總?cè)藬?shù)。SELECTCOUNT(Sex)FROMstudentWHERESex='男';SELECTCOUNT(*)FROMstudentWHERESex='女';SELECTCOUNT(*)FROMstudent;【例8-19】在score表中，查詢參加考試的學(xué)生總?cè)藬?shù)。SELECTCOUNT(DISTINCTStudentID)FROMscore;8.1單表查詢2．SUM()和AVG()【例8-20】在score表中，查詢指定學(xué)號2023510103學(xué)生的總分、總平均分。SELECTSUM(Score)總分,AVG(Score)平均分,SUM(Score)/COUNT(*)平均分?jǐn)?shù)

FROMscoreWHEREStudentID='2023510103';8.1單表查詢3．MAX()和MIN()【例8-21】在score表中，求課程編號CourseID為300105學(xué)生成績的最高分、最低分、最高分與最高分之差、平均分。SELECTMAX(Score)最高分,MIN(Score)最低分,MAX(Score)-MIN(Score)分?jǐn)?shù)差,AVG(Score)平均分FROMscoreWHERECourseID='300105';8.1單表查詢8.1.5分組查詢GROUPBYexpression1[,expression2,…][WITHROLLUP][HAVINGwhere_condition]1．GROUPBY子句（1）按單列分組【例8-22】在student表中，按照Sex單列分組，并查詢學(xué)生信息。SELECTSex性別,COUNT(*)人數(shù)FROMstudentGROUPBYSex;

SELECTSex性別,COUNT(*)人數(shù)FROMstudentGROUPBY性別;SELECTSex性別,COUNT(*)人數(shù)FROMstudentGROUPBY1;8.1單表查詢【例8-23】在score表中，統(tǒng)計(jì)每名學(xué)生的考試門數(shù)、最高分、最低分和平均分。SELECTStudentID,COUNT(*)考試門數(shù),MAX(Score)最高分,MIN(Score)最低分,AVG(Score)平均分

FROMscoreGROUPBYStudentID;8.1單表查詢【例8-24】在score表中，查詢每名學(xué)生的課程平均成績，并顯示平均成績最高的前3名學(xué)生的學(xué)號和平均成績。SELECTStudentID,AVG(Score)FROMscoreGROUPBYStudentIDORDERBYAVG(Score)DESCLIMIT3;8.1單表查詢（2）按多列分組【例8-25】在score表中，先按照CourseID分組，再按照StudentID分組，統(tǒng)計(jì)每組學(xué)生的人數(shù)和平均分。SELECTCourseID,StudentID,COUNT(CourseID),AVG(Score)FROMscoreGROUPBYCourseID,StudentID;8.1單表查詢2．在GROUPBY子句中使用WITHROLLUP關(guān)鍵字【例8-26】在student表中，按照Sex單列分組，查詢男生和女生的人數(shù)及學(xué)生的總?cè)藬?shù)。SELECTSex,COUNT(*)AS人數(shù)FROMstudentGROUPBYSexWITHROLLUP;8.1單表查詢【例8-27】在score表中，查詢每門課的平均分?jǐn)?shù)和所有課的平均分?jǐn)?shù)。SELECTCourseID課程號,AVG(Score)每門課程的平均分FROMscoreGROUPBYCourseIDWITHROLLUP;8.1單表查詢3．HAVING子句【例8-28】在score表中，查詢在第1學(xué)期選修兩門及以上課程的學(xué)號和選課門數(shù)。SQL語句和查詢結(jié)果如下：SELECTStudentID學(xué)號,COUNT(*)選課門數(shù)

FROMscoreWHERETerm=1GROUPBYStudentIDHAVINGCOUNT(*)>=2;8.1單表查詢【例8-29】在score表中，查詢所有課程在60分以上，平均分80以上的每名學(xué)生的選課門數(shù)、最高分、最低分和平均分。SELECTStudentID學(xué)號,COUNT(*)選課門數(shù),MAX(Score)最高分,MIN(Score)最低分,AVG(Score)平均分FROMscoreWHEREScore>=60GROUPBYStudentIDHAVINGAVG(Score)>=80;8.1單表查詢8.2多表查詢連接子句的語法格式：FROMtb_name1[別名1]連接類型tb_name2[別名2]…[ON連接條件]8.2.1交叉連接查詢SELECT*FROMtb_name1CROSSJOINtb_name2;或SELECT*FROMtb_name1,tb_name2;8.2多表查詢【例8-30】對student表和class表進(jìn)行交叉連接。SQL語句和查詢結(jié)果如下：SELECT*FROMstudentCROSSJOINclass;8.2多表查詢8.2.2內(nèi)連接查詢SELECT輸出列表

FROM表1[INNER]JOIN表2ON表名1.列名比較運(yùn)算符表名2.列名或SELECT輸出列表

FROM表1,表2WHERE表名1.列名比較運(yùn)算符表名2.列名8.2多表查詢1．等值連接【例8-31】在student表中，查詢生源是北京的學(xué)生名單，要求顯示班級名稱。SELECTStudentID,StudentName,Source,student.ClassID,ClassNameFROMstudentINNERJOINclassONstudent.ClassID=class.ClassIDWHERESource='北京';8.2多表查詢【例8-32】查詢學(xué)號為2023510103學(xué)生的考試情況，輸出學(xué)號、姓名、課程名和成績。SELECTst.StudentID,StudentName,co.CourseID,co.CourseName,sc.studentID,sc.CourseID,ScoreFROMstudentASst,scoreASsc,courseAScoWHEREst.StudentID=sc.StudentIDANDco.CourseID=sc.CourseIDANDst.StudentID='2023510103';8.2多表查詢【例8-33】查詢選修了課程名稱為“高級語言”的學(xué)號、姓名和課程號、課程名、成績的學(xué)生名單。SELECTstudent.StudentID,StudentName,course.CourseID,CourseName,ScoreFROMstudentINNERJOINcourseINNERJOINscoreONstudent.StudentID=score.StudentIDANDcourse.CourseID=score.CourseIDWHERECourseName='高級語言';8.2多表查詢2．自然連接SELECT輸出列表FROM表1NATURALJOIN表2【例8-34】對student表和class表做自然連接。1）不指定列名，SQL語句如下：SELECT*FROMstudentNATURALJOINclass;8.2多表查詢2）指定列名，按SELECT語句輸出列表中的列輸出。SELECTStudentID,StudentName,ClassID,ClassName,DepartmentNameFROMstudentNATURALJOINclass;8.2多表查詢3．不等值連接【例8-35】對student表和class表做不等值連接。SELECT*FROMstudentINNERJOINclassONstudent.ClassID!=class.ClassID;8.2多表查詢4．自連接【例8-36】在student表中，查詢與張三在同一個(gè)班的學(xué)生的學(xué)號和姓名。SELECTs2.StudentID,s2.StudentNameFROMstudents1,students2WHEREs1.ClassID=s2.ClassIDANDs1.StudentName='張三'ANDs2.StudentName!='張三';8.2多表查詢8.2.3外連接查詢SELECT輸出列表

FROM表名1LEFT|RIGHT[OUTER]JOIN表名2ON表名1.列名1=表名2.列名28.2多表查詢1．左外連接【例8-37】利用左外連接，從course表中查詢參加這些課程（'高級語言','數(shù)據(jù)庫原理','計(jì)算機(jī)網(wǎng)絡(luò)'）考試的學(xué)生名單。SELECTStudentID,score.CourseID,CourseName,ScoreFROMcourseLEFTJOINscoreONcourse.CourseID=score.CourseIDWHERECourseNameIN('高級語言','數(shù)據(jù)庫原理','計(jì)算機(jī)網(wǎng)絡(luò)');8.2多表查詢2．右外連接【例8-38】利用右外連接，查詢男生及其考試的情況，包括沒有選修課程的學(xué)生，要求顯示學(xué)號、姓名、班號、選修的課程號和成績。INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Source,ClassID)VALUES('2023510127','胡十一','男','2003-11-22','河南','20235203');SELECTscore.StudentID,StudentName,ClassID,CourseID,ScoreFROMscoreRIGHTJOINstudentONscore.StudentID=student.StudentIDWHERESex='男';8.2多表查詢8.3子查詢8.3.1子查詢的執(zhí)行過程和類型1．子查詢的執(zhí)行過程2．子查詢的類型3．子查詢中的常用運(yùn)算4．使用子查詢時(shí)的注意事項(xiàng)8.3.2標(biāo)量子查詢1．使用子查詢與比較運(yùn)算符組成條件WHERE表達(dá)式比較運(yùn)算符(子查詢語句)【例8-39】查詢選修“高級語言”課程的學(xué)生學(xué)號和成績。SELECTCourseIDFROMcourseWHERECourseName='高級語言';

8.3子查詢SELECTStudentID,CourseID,ScoreFROMscoreWHERECourseID=(SELECTCourseIDFROMcourseWHERECourseName='高級語言');SELECTStudentID,score.CourseID,ScoreFROMscore,courseWHEREscore.CourseID=course.CourseIDANDcourse.CourseName='高級語言';8.3子查詢【例8-40】在score表中，查詢低于平均分的學(xué)生。SELECTStudentID,CourseID,ScoreFROMscoreWHEREScore<(SELECTAVG(Score)FROMscore);SELECTStudentID,CourseID,ScoreFROMscoreWHEREScore<75.00000;8.3子查詢2．表達(dá)式子查詢【例8-41】利用子查詢顯示學(xué)號為2023510103的學(xué)生的平均成績、考試次數(shù)，同時(shí)顯示其學(xué)號、姓名。SELECTStudentID學(xué)號,StudentName姓名,(SELECTAVG(Score)FROMscoreWHEREStudentID='2023510103')平均成績,(SELECTCOUNT(StudentID)FROMscoreWHEREStudentID='2023510103')考試次數(shù)

FROMstudentWHEREStudentID='2023510103';8.3子查詢8.3.3表子查詢【例8-42】查詢課程號為300101，成績大于或等于60分的學(xué)生的學(xué)號、課程號和成績。SELECTT.StudentID,T.CourseID,T.ScoreFROM(SELECT*FROMscoreWHEREScore>=60)ASTWHERECourseID='300101';8.3子查詢8.3.4列子查詢1．IN子查詢WHERE表達(dá)式[NOT]IN(子查詢語句)【例8-43】查詢考試成績低于60分的學(xué)生的學(xué)號和姓名。SELECTStudentID,StudentNameFROMstudentWHEREStudentIDIN(SELECTStudentIDFROMscoreWHEREScore<60);8.3子查詢SELECTStudentIDFROMscoreWHEREScore<60;

SELECTstudent.StudentID,StudentNameFROMstudent,scoreWHEREstudent.StudentID=score.StudentIDANDScore<60;8.3子查詢2．ANY、SOME或ALL子查詢WHERE表達(dá)式比較運(yùn)算符{ANY|SOME|ALL}(子查詢語句)（1）ANY（或SOME）子查詢表達(dá)式{=|<>|!=|>|>=|<|<=|!>|!<}ANY(子查詢語句)8.3子查詢【例8-44】查詢考試成績不及格的學(xué)生信息。SELECT*FROMstudentWHEREStudentID=ANY(SELECTStudentIDFROMscoreWHEREScore<60);8.3子查詢【例8-45】查詢男生中比某個(gè)女生出生年份晚的學(xué)生姓名和出生年份。SELECTDISTINCTYEAR(Birthday)FROMstudentWHERESex='女';

SELECTStudentName,YEAR(Birthday)FROMstudentWHERESex='男'ANDYEAR(Birthday)>ANY(SELECTDISTINCTYEAR(Birthday)FROMstudentWHERESex='女');8.3子查詢（2）ALL子查詢【例8-46】查詢學(xué)生表中年齡最大的學(xué)生姓名和年齡。SELECTStudentName姓名,YEAR(NOW())-YEAR(Birthday)年齡FROMstudentWHEREBirthday<=ALL(SELECTBirthdayFROMstudent);

SELECTStudentName姓名,YEAR(NOW())-YEAR(Birthday)年齡FROMstudentWHEREBirthday=(SELECTMIN(Birthday)FROMstudent);8.3子查詢【例8-47】查詢男生中比所有女生出生年份晚的學(xué)生姓名和出生年份。INSERTINTOstudentVALUES('2023510155','丁一','男','2005-01-11','河北','20235101');SELECTStudentName,YEAR(Birthday)FROMstudentWHERESex='男'ANDYEAR(Birthday)>ALL(SELECTDISTINCTYEAR(Birthday)FROMstudentWHERESex='女');8.3子查詢（3）ANY或ALL與聚合函數(shù)的對應(yīng)關(guān)系【例8-48】查詢男生中比某個(gè)女生出生年份晚的學(xué)生姓名和出生年份。SELECTStudentName,YEAR(Birthday)FROMstudentWHERESex='男'ANDYEAR(Birthday)>(SELECTMIN(YEAR(Birthday))FROMstudentWHERESex='女');8.3子查詢3．EXISTS子查詢WHERE[NOT]EXISTS(子查詢語句)【例8-49】查詢選修了課程的學(xué)生姓名。SELECTStudentNameFROMstudentWHEREEXISTS(SELECT*FROMscoreWHEREstudent.StudentID=score.StudentID);8.3子查詢【例8-50】如果在student表中存在學(xué)號為2023510103的學(xué)生，則在score表中查詢該學(xué)生的所有考試成績。SELECT*FROMscoreWHEREEXISTS(SELECT*FROMstudentWHEREStudentID='2023510103')ANDStudentID='2023510103';8.3子查詢8.3.5利用子查詢插入、修改或刪除記錄1．利用子查詢插入記錄INSERTINTO表名[(列名列表1)](SELECT列名列表2FROM表名);【例8-51】把student表中男生記錄添加到student1表中。CREATETABLEstudent1LIKEstudent;INSERTINTOstudent1(SELECT*FROMstudentWHERESex='男');8.3