sql中两行合并_第五关：从零学会SQL之多表查询

1、sql中两行合并_第五关：从零学会SQL之多表查询、表的加法：union语句1、union语句使用union语句进行course表跟coursei相加，两个查询语句联结在一起,文氏图：左边course右边coursei两个表相加重复的数据只会留一个：例如语文课程0001文氏图:加法(union)数学(0002)工宀计算机(0004)I/语文'(0001)英语(0003)数据库(000占unionSQL语句的使用-unic语句的使用/Select课程号，课程名称nirtnoFromcourseUnionSelect课程号，课程名称Fromcoursel1112 3union表的加法13

2、L-union句的悝用14 Sslect课程号课程电称_5Frcmcourss16 Uriion17 Select课程号课程电称二Frcmcourssl19信息茫果1删112、unionall语句unionall将所有的列进行相联结,0001语文00C2数字00C3英常0004计訓不会删除重复的查询列一:三圭1111-unionallSQL语句的使用Select课程号，课程名称FromcourseUnionallSelect课程号，课程名称Fromcoursel3 unicnallS風语冗的使用4 Select课程号保程名称5 Fromccurse6 Unionall7 Select课程号程名

3、称8 Fromcense1二、表的联结什么是联结？魁号0001语文0002数学0003英语000300i34ttmtfc联结：表和表是通过列产生对应关系的，联结是将通过表和表之间的关系，将两个表合并在一起的操作。1、各表之间的关系各个表通过一些相同的列进行联结的例如下图每个学号列相同的学号行进行联结学生表成绩表联结（jo诃3005：9虹-1I-2L3004王W1990-C5-20血1赧子1990-12-2L左学号潴出生E軽性别300；齡LWl-OL男3151刀0-00oO0123oDDooO0oO3C9C990QQ2_J6C0003|800W300013C00530002Null00J3C0Q

4、34张表的联结关系图学生表跟成绩表通过学号联结成绩表跟课程表通过课程号进行联结课程表跟教师表通过教师号进行联结4张表联结关系图学生表1）交叉联结：crossjoin学生姓名出生年月教师号教师姓名2、常用的联结联1990U女L9911221Rpee加ME畀0001也叫笛卡尔积英文名称crossjoin案例：表一三行数据跟表二两行数据进行交叉联结表一crossjoin表二的数据表为3行*2行=6行数据类似扑克牌13张牌=（A.2.3.4.5.6.7.8.9.10.J.Q.K）花色=（.“）13张牌X花色=13*4=（52张扑克牌）平常用的比较少2）内联结：innerjoin查找出同时存在于两张表中

5、的数据：英文名innerjoin案例(score；学生表（student）跟成绩表（score）通过学号产生了匹配，在表中使用相同的颜色表示都有的学号。两个表里都有的学号是0001,学号o002只存在于学生表里，学号0005只存在于成绩表中，图片中用文氏图画出了内联结之间的关系，左边的圆圈显示学生表里的数据，右边的圆圈显示成绩表里的数据，两个圆圈重合的地方就是同时存在于两个表里的数据，也就是学号0001。(student)拿学生表跟成绩表进行内联结第一步：±1鯛醐3001轅子1969-01-01劉3002子199C-12-21女内联结innerjoin学号课程号卿300100018C

6、-300100029C-30056C-两个表通过学生的学号进行内联纟可以看出内联结在两张表上是如何运行的两个表通过学号进行内联结，会将两个表中都有的学号所在的行取出来，学生表里会把学号0001行的数据取出来，成绩表里学号0001里有两行，取出的是两行数据。将两个表里取出来的数据进行合并，这里就是进行交叉联结了，交叉联结是将表中的每一行跟另一个表中的每一行两两合在一起，这里将学生表中的学号0001行数据跟成绩表中的两行数据合并，最终产生了两行数据，合并以后的表呢就有了两张表中的全部信息。学号，姓名，出生日期，性别，课程号，成绩都放在了一张表中。学生表(student)成绩表(score)'

7、;Iftw出生日期0001猴子1989-01-01男0002报子1990-12-21女内联结innerjoin学号孵号J00010001;00010002!00050003取出符合条件的行|SQL查询语句0001出生日期性别猴子1989-01-01男学号pi%;”00010001800001000290交叉联结-内联结SQL查询语句Selecta.学号,a.姓名,b.课程号Fromstudentasainnerjoinscoreasb-重点1:scoreasOna学号=b.学号-重点on在学号的基础上进行联结学号姓名出生日期性别00010001猴子1989-01-01男猴子1989-OfOl男

8、00010001000180000290153 内厩结innezjoinSQL查询:吾句154 3e；ect沐学号疋.姓名课程号155 Fromstudentasainnerjoinscoreasb155加儿学号土氏学号严亠重点Non生学号腑基础上进行联结157153信息箔杲1狀态学号駆号COD1抿子0201匚g曝子0002CO01振子0003C0C2蹊子Q3O2C002提子OCXB注意事项：From中使用.了两张表.，.'/：)C0D3马云ODO?1、表的别名，列名是为了区分是哪张表中的数据，需要在列名前加上表的别名跟点2、两个表中间innerjoin就说明两张表通过innerjoi

9、n进行内联结3、From子句中的关键字on表示两张表是通过哪个列匹配产生关系的3)左联结:leftjoin会将左侧表中的数据取出来，文氏图中左联结就是学生表中的部分如何进行运行的第一步左联结就是左侧的表作为主表，主表中的数据全部读取出来，也就是将学生表中的数据全部取出来，右边的表中只选出和左边表中相同学号的行也就是学号0001所在的两行数据将两个表中取出的数据进行合并，如何合并：这里就要进行交叉联结了，因为学号0002在右边的表里没有对应的行，所以这一行对应的列的值就是空值对应的SQL语句-左联结leftjoinSQL语句Selecta.学号,a.姓名,b.课程号Fromstudentasal

10、eftjoinscoreasbOna.学号=b.学号气”，叶Er?-工吉prrinSi,1'口477-Iki»4丄J1L7比"ILJ159 q*学号q»姓名.b课程号|160 Froir；studentasaleftjoinscoreasb16LOna学号=b学号162163信息结果1ffiKR状态学导课程号_0001猴子00030002猴子00020002濮子00030003马云0001我们再来看'个问题00020003马云00030004(Null)左联结的基础上，图片中红色部分的地方如何用SQL表示呢(score)姓名出生日嚥丽猴子1988-

11、01-010002猴子199012-21女学5表(st-jdent)联结学号i襁号峨00310001B00031m2卯Q0D5OOQ360成绩表030100Q5表中的公共部分D0024)右联结：rightjoin这是在左联结中去掉了两个表中共同的地方,SQL语句-左联结中去掉公共部分Selecta.学号,a.姓名,b.课程号FromstudentasaleftjoinscoreasbOna.学号=b.学号Whereb.学号isnull;丄no159-左.联结中去掉公共邹分16CSelecta.学号”a.姓名b.课程号161 Fromstudentssaleftjoinscoreasb162 O

12、na.学号=b,学号163 hersb.学号isnu.ll;164 |165信息结杲1翻舷学号姓名鞘吕右联结会将右侧表中的数据全部取出来，图片中文氏图画出了右联结是红色图的地方第一步：两行表通过学号进行右联结，就是将成绩表里的数据全部取出来左边表只选出和右边表相同学号的行第二步:将两张表中取出的数据合并：如何合并：就是进行交叉联结了，因为学号0005在左边的表中没有相应的行，这一行对应的列是空值。SQL语句-右联结rightjoinSQL语句Selecta.学号,a.姓名,b.课程号FromstudentasarightjoinscoreasbOna.学号=b.学号11517E17ns112-

13、右联结rightjoir.SQL语句Select乳学号a.姓名J,保程号FromstudetitasarightjoinscoreasbOna,学号二b.学号在右联结的基础上，我们再来看一个问题，图比中红色部分应该如何用SQL表示呢这是在右联结的基础上去掉了两个表中共同的地奈去掉了两张表中共同的地方SQL语句0031狠子0D010001抿子00020031猴子0D030032猴子M02OOJ2猴子OD03-leftjoin右联结去掉相同列的SQL语句Selecta.学号,a.姓名,b.课程号FromstudentasarightjoinscoreasbOna.学号=b.学号Wherea.学号i

14、snull;5）全联结:fulljoin全联结的查询结果会返回左表和右表中的所有行，当某行和另一个表中有匹配的时候两个行进行合并，如果某行和另一个表中没有匹配的行的时候，另一个表中没有的值用空值填充，在图片中看出了有两行空值，分别是左联结结果和右联结结果中的空值行，这样就会看到两个表中的数据都在联结结果中了，值得一提的是MySQL是不支持全联结的。6)SQL联结from表1asarightjoin妻2asbona名旳列名fromSIasainnerjoin表2asbon曰洌名=h列名;from表1asal&hjcin表2asbon日列列名;from表1asarig"1join

15、表2asbon白副名二b+列名,from表1asakftjoin表2asbon乩列名二b.列名whereb.列名isnt;什么时候用哪种联结呢佗&列名isnuI;当实际工作业务中想要生成固定行数的表单，或者特别说明了要哪一张表里的全部数据的时候，会使用左联结或右联结，其他情况都是用内联结获取两个表的公共部分。3、SQL运行顺序三、联结应用案例如何用SQL解决业务问题select萱询结果from从哪张表中酣数据联结）where査询条件（§算饪模麹登询:groupby仝组海4）tw帕对分组结杲蹙条件0旳加对查询结東毎序sql运行厢序：1冼运f抒餉Z每个查询语句里运柯瞬L）先运行蓝

16、框里的子句2）select?句3）最后运巧红框里的子句主要分为三个步骤1、翻译成大白话2、写出分析思路3、写出对应的SQL子句案例问题1：查询所有学生的学号、姓名、选课数、总成绩1、翻译成大白话1）学号、姓名（学生表student）2）选课数（每个学生的选课数目）：成绩表score按学号分组，对课程号计数count3）总成绩（每个学生的总成绩：成绩表score,4）按学号分组，对成绩求和sum2、写出分析思路查询所有学生的学号、姓名、每个学生的选课数目、每个学生的总成绩Select查询结果【学号，姓名，选课数，总成绩】From从哪张表中查找数据【学生表student，成绩表score两个表如何

17、联结？通过学号哪种联结？左联结】Where查询结果【没有】Groupby分组【每个学生的选课数目：按学号分组，对课程号计数count）每个学生的总成绩：按学号分组，对成绩求和sum）】Having对分组结果指定条件【没有】Orderby对查询结果排序【没有】Limit从查询结果中取出指定行【没有】3、写出对应的SQL语句-查询所有学生的学号、姓名、选课数、总成绩-查询所有学生的学号、姓名、选课数、总成绩Select学号，姓名,count（课程号）as选课数,sum（成绩）as总成绩FromstudentasaleftjoinscoreasbOna.学号=b.学号Groupbya.学号2、写出分

18、析思路Select查询结果【学号，姓名，平均成绩】From从哪张表中查找数据【学生表student,成绩表score两个表如何关联？通过学号用哪种联结?左联结】Where查询条件【没有】Groupby分组【每个学生的选课项目：按学号分组，对课程号计数count每个学生的总成绩：按学号分组，对成绩求和sum】Having对分组结果指定条件【没有】Orderby对查询结果排序【没有】Limit从查询结果中取出指定行【没有】；3、写出对应的SQL子句-查询所有学生的学号、姓名、平均成绩Selecta.学号,a.姓名,avg（b.成绩）as平均成绩Fromstudentasaleftjoinscore

19、asbOna.学号=b.学号P701020304050607Groupbya.学号；查询所有学生旳学号、妊名、平览或绩Selecta.学号f3.姓名favg（b.成绩）己s平均成绩Fromszudsntaaaleftjoinscoreas二Dn乳学号龙，学号Sroupby己.学号；信息结杲1諏学号好平均瞬001抿子89.66670C02舉子70.0030查询平均成绩大于85的所有学生的学号、垄名和平均成绩°iNulli卜0204王巨聪-查询平均成绩大于85的所有学生的学号、姓名和平均成绩Selecta.学号,a.姓名,avg（b.成绩）as平均成绩Fromstudentasaleft

20、joinscoreasbOna.学号=b.学号Groupbya.学号Havingavg（b.成绩）>85;£ti加9-查询平均成绩.瓦于瞪茹祈有学生的学号、姓名彌平坟成绩21CSslecta,学号”己.姓名/avg（b.成绩）as平均成绩211 FrcmStuderasalEftjoinscoreasb212 Or.a.学号=1.学号213 Groupjzya.学号214 Having-avg（b.>35;2152162171）学号，姓名（学生表student）2）课程号，课程名称（课程表course）SQL语句查询学生的选课情况Selecta.学号,a.姓名,c.课程号

21、,c.课程名称Fromstudentainnerjoinscorebona.学号=b.学号Innerjoincourseconb.课程号=c.课程号；217218212220B上'ffl古irrI"jMi'*IS,5-lect乳学号用.姓名课程号课程名称rrornstudentainnerjcir.scorebon日.学号二b,学号Innerjoincourseconb课程号课穆号；0002振子0002数学0002四、Cas表达式一猴子0003英语马云0001语文什么是Case表达式？二二0002数孚结果1WJ?学号姓名渥程名称0001猴子0001语文00010002

22、数学T1Case表达式可以帮助我们解决复杂的查询问题，case表达的作用就相当于进行一个条件判断的函数用来判断每一行是不是满,条件，图片中给出了case表达式的语句，里面的wen子句的判断表达式，来判断某行数据，是否符合某个条件，如果符合条件后面的then子句，case表达式运行到此结束，就不会运行后面的when子句了，如果不符合条件就进入下一个when子句，如果后面的when子句都没有找到合适的数据，那么就会运行else中的表达式Casewhen判断表达式the门表达式when判断表达式the门表达式when判断表达式the门表达式Else表达式End案例：判断学生成绩是否及格判断学生成绩是

23、否及格Select学号，课程号,成绩，（casewhen成绩>=60then及格'When成绩<60then不及格Elsenullend）as是否及格fromscore;303304 判断学生成绩是否及格305 Select学号#课程号*成绩306 （casewhen成绩then'及格丫307 When成绩V60then不及格303Elsenull32end）s是否及格31Lfromscore;）311312313信息结果1概况状态0001000399及格0002000260及格00020003案例：查询出每门课程的及格人数和不及格人数1）查询出每门课程的人数i匸8

24、0及格000180及格0003-查询出每门课程的人数Select课程号,Count（学号）as.人数Fromscore""Groupby课程号；0002000380及格80及格学号课程号成绩是否及格0001000180及搭90及格000100022）查询出每门课程的及格人数和不及格人数-查询出每门课程的及格人数和不及格人数Select课程号，Sum(casewhen成绩=60then1else0end)as及格人数，sum(casewhen成绩60then1else0end)as不及格人数fromscoregroupby课程号；erniit-?m比L_：占r/r詁琵鞅JIJ

25、4-LJ1J+LTfZ%LJk1icy去hJ.,.-J-'?未t丄dbyJJ弋¥JT壬口HiX.'PzX.X.TM314 Select课程号，315 s(:asewhen成绩K0then1316 else031"end)as及賂人数#318 sun(casewhen成绩心匸hen1319 else0320 end)as不及格人数321 frorr.score322 groupby课程号；323324课程号case表达式注意事I项|能及胳人数结果1状态CaseU1when判断表达式then表达式：when判断表达式the门表达式when判断表达式the门表达式

26、Else表达式else可以省略，默认为空值End.end不能省略不写的Case表达式能写在SQL语句中任意语句中case表达式有什么用，什么时候比较好用呢当有多种条件判断的时候使用case表达式了案例：使用分段100-85,85-70,70-6060来统计各科成绩，分别统计:各分数段人数(成绩表score),课程号和课程名称(课程表course)/*使用分段100-85,85-70,70-60<60来统计各科成绩，分别统计:各分数段人数(成绩表score)，课程号和课程名称(课程表course)*/Select&课程号,b.课程名称，Sum(casewhen成绩between85

27、and100Then1else0end)as'100-85',Sum(casewhen成绩>=70and成绩<85Then1else0end)as'85-70',Sum(casewhen成绩>=60and成绩<70Then1else0end)as'70-60',Sum(casewhen成绩<60Then1else0end)as'<60'FromscoreasarightjoincourseasbOna.课程号=b.课程号326E.'2I32832933033133233333433533

28、6337338339340341Groupbya.课程号,b.课程名称成绩between85ani10Jend)as1100-85成绩>=_ana成绩也三end)as'35-70成and成绩VendJas'口卜&仍成绩丈切灯51elsa0end)as'<601曰righjoincourseasbSelecta.课程号.课程名琢，Th&n1elseCSui(cise>.:herrh?n1elseCSmufccsehenThsn1ele0Sim(c：£5e：her.FromscoreasOna.误程号动.课程号Grouphy日”课

29、程号川”课程宅称;当用多个列来分组时这凡个列的值全部相同才算一组-:'-_+口H*0001语丈D20五、练习题SQLZODDjOin/*1、第一個例子列出球員姓氏為'Bender'的入球數據。*表示列出表格的全部欄位，簡化了寫matchid,teamid,player,gtime語句。修改此SQL以列出賽事編號matchid和球員名player,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查:teamid='GER'*/SELECTmatchid,playerFROMgoalWHEREteamid='GER'matchid

30、p怡疽SCCEMmrinGdmeiieicMdriaCjorr&z1C1Cari&Gorrez1C12跡Pocolski1C12_aisBinder1CM-3ilippLahm1C2f1圣翌总朋;.CornetEinsw&rtgnid,下仙”，包緻血Q删出亞輛北吵处刊豳名盟村鬲鹅畑酯廡11伽认剛*墜跚阳卑關，慕显：2d=如巫邀T帕toitLish闻吧皿】raniMwii='or/*2、由以上查詢，你可見LarsBender's於賽事1012入球。現在我們想知道此賽事的對賽隊伍是哪一隊。留意在goal表格中的欄位matchid，是對應表格game的欄位id

31、。我們可以在表格game中找出賽事1012的資料。只顯示賽事1012的id,stadium,team1,team2*/SELECTid,stadium,team1,team2FROMgamewhereid=1012Mfladkri耐n2AjffUIiIm芳用JinsniT2.师Elb.“Nd朗耿尿一划tnmhfl肛曲删沪睡*叙MLDI泗瓦用畸豪*1,孝eI.rtdm,imin±3LET弼jL佯ekL"2cup1*1142/*3、我們可以利用JOIN來同時進行以上兩個步驟。SELECT*FROMgameJOINgoalON(id=matchid)語句FROM表示合拼兩個表格g

32、ame和goal的數據。語句ON表示如何找出game中每一列應該配對goal中的哪一列-goal的id必須配對game的matchid。簡單來ON(game.id=goal.matchid)以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。*/SELECTy.player,y.teamid,x.stadium,x.mdateFROMgameasxinnerJOINgoalasyON(x.id=y.matchid)whereteamid='GER'ffcjeXi保M4ik>右旣ctn户Hfid

33、Lw、此ne摘上丽町祜心沁；!Siadin13.U1&23I2tenSWkii；TW1.划Podddd/fundtw17La丹BandsFHHLWV17.ire23t21：何诵Lain豎AGEX1缶佃臥伽iKi>dwaWjEArw心佝臥应aune23li2必rt此：.n乳呻'3®曲口Rd*:阳即R0曾硏書制何刃希m如中0约K3IHHOCtt蘇餌划1gm阿舟一亍旦當帥対中的一弘呻凶1的衬筈SG?紳mi的nW4W.艘珮.就是OKigtu.中号锻1-：血£)SlET-yplfainr.k>UdwLii3：4TEXEmhkimffJOEFp-Altiya

34、kM-JiSd)/*4、使用上題相同的JOIN語句，列出球員名字叫Mario(playerLIKE'Mario%')有入球的隊伍1team1,隊伍2team2和球員名player*/selectx.team1,x.team2,y.playerFROMgameasxinnerJOINgoalasyON(x.id=y.matchid)whereplayerlike'Mario%'/*別it淞i害泗血1(3IJljymIJKK甫Af逐删I也耐,除縊閻厲上M駆&回弹rteamlteim2MarbMnsNBCMawGXinKKBCIRLMwKuhkIRL惦祜MdM

35、kubitMarfoMJinliukicITAIMIRLMaroEIdhfFBIThMrrhtwEil/Til'-Jl.ibB-iii'r5、表格eteam貯存了每一國家隊的資料，包括教練。你可以使用語句goalJOINeteamonteamid=id來合拼JOIN表格goal到表格eteam。列出每場球賽中首10分鐘gtime<=10有入球的球員player，隊伍teamid,教練coach,入球時間gtime*/SELECTx.player,x.teamid,y.coach,x.gtimeFROMgoalasxinnerjoineteamasyonx.teamid=y

36、.idWHEREgtime<=10/*求13电孫就砸廐用WEjar«哼ffSftFJ08K到弄軽缎土毎目自|e苛i耐gX”pT".馆i匸祖氣帕am,川如祇对tcsfndmehgrtinwf#rJidcccCZE5匕如hi.¥E制曲融5CM1：fmaKkTarw¥i：enlBde<BjleCoi'rectajisvr6、要合拼JOIN表格game和表格eteam，你可以使用gameJOINeteamON(team1=eteam.id)或gameJOINeteamON(team2=eteam.id)注意欄位id同時是表格game和表格et

37、eam的欄位，你要清楚指出eteam.id而不是只用id列出'FernandoSantos'作為隊伍1teaml的教練的賽事日期，和隊伍名。*/selectx.mdate,y.teamnamefromgameasxinnerjoineteamasyonx.team1=y.idwherecoach='FernandoSantos'/*7、*/selectb.playerfromgameasainnerjoingoalasbona.id=b.matchidwherestadium='NationalStadium,Warsaw'修改它，只列出全部賽事

38、，射入德國龍門的球員名字。*/SELECTdistinct(b.player)FROMgameasainnerJOINgoalasbONb.matchid=&idWHERE(a.team1='GERora.team2='GER')andb.teamido'GER'/*9、COUNTandGROUPBY*/SELECTa.teamname,count(b.player)FROMeteamasainnerJOINgoalasbONa.id=b.teamidgroupBY&teamnameselect&stadium,count(b.p

39、layer)fromgameasainnerjoingoalasbona.id=b.matchidgroupbya.stadium/*1O.HtUKfiW-TiUiHrK,时祁J>113石ic-arlpivr!tntInw魁r.血istm住kkhnicidfkFbrIUE-Jr!£lBSjbffilSQLConiecrtiLSweY吓*询¥77¥Qkr炉阳颐晌1；rE疔1-'川謠詛1LSubnitMLCorrectansvrermatchidnt-dateCiDuntitupaye.-1师BJuneSJI：2I«t412Jum20122刚iJtiiwl21j'rrl:1711、每一場波蘭'POL'有參與的賽事中，列出賽事編號matchid,日期date和入球數字。*/SELECTb.matchidamdate,count(b.player)FRO