2023学年完整公开课版联合查询

主讲教师：魏巍巍北京信息职业技术学院数据库技术与应用联合查询创建采集任务01联合查询01目录CONTENTS联合查询01Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.UNION用于把来自许多SELECT语句的结果组合到一个结果集合中，也叫联合查询。语法在多个SELECT语句中，第一个SELECT语句中被使用的字段名称将被用于结果的字段名称。当使用UNION时，MySQL会把结果集中重复的记录删掉，而使用UNIONALL，MySQL会把所有的记录返回，且效率高于UNION。SELECT...UNION[ALL|DISTINCT]SELECT...[UNION[ALL|DISTINCT]SELECT...]联合查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.数据准备，student表DROPTABLEIFEXISTS`student`;CREATETABLE`student`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(10)DEFAULTNULL,`age`tinyint(4)DEFAULTNULL,`classId`int(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8;INSERTINTO`student`VALUES('1','s1','20','1');INSERTINTO`student`VALUES('2','s2','22','1');INSERTINTO`student`VALUES('3','s3','22','2');INSERTINTO`student`VALUES('4','s4','25','2');联合查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.数据准备，teacher表DROPTABLEIFEXISTS`teacher`;CREATETABLE`teacher`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(10)DEFAULTNULL,`age`tinyint(4)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8;INSERTINTO`teacher`VALUES('1','t1','36');INSERTINTO`teacher`VALUES('2','t2','33');INSERTINTO`teacher`VALUES('3','s3','22');联合查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.查询数据如下SELECT*FROMstudent;SELECT*FROMteacher;联合查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.使用UNION的结果SELECTid,NAME,ageFROMstudentUNIONSELECTid,NAME,ageFROMteacher;联合查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.使用UNIONALL的结果SELECTid,name,ageFROMstudentUNIONALLSELECTid,name,ageFROMteacher;联合查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.其实联合查询跟字段的类型无关，只要求每个SELECT查询的字段数一样，能对应即可，如这里可以看出第一个SELECT语句中的字段名称被用作最后结果的字段名SELECTid,name,ageFROMstudentUNIONSELECTage,name,idFROMteacher;联合查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.在联合查询中，当使用ORDERBY的时候，需要对SELECT语句添加括号，并且与LIMIT结合使用才生效，如此时classId为1的学生并没有按照年龄进行降序(SELECTclassId,id,name,ageFROMstudentWHEREclassId=1ORDERBYageDESC)UNION(SELECTclassId,id,name,ageFROMstudentWHEREclassId=2ORDERBYage);联合查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.结合LIMIT后(SELECTclassId,id,name,ageFROMstudentWH