文章目录外连接自连接子查询嵌套查询[not] exists 关键字合并查询外连接在执行内连接时我们会在两张表中各取一条记录进行匹配一旦匹配不成功那这两天记录都会被过滤掉。而外连接会保留一张或两张表full join的全部记录再去匹配另一张表里满足连接条件的数据也就是会固定直接先在一张表如左表里取一条数据依旧会去右表里进行匹配但是即使匹配不到也会把左表的该条记录保留下来。匹配不到的时候另一张表的字段填充为 null 。有以下两张表学生表班级表可以看到没有哪个学生的班级是java110班也就是java110班虽然存在但是暂时还没有招生。但是我们在查询学生的班级信息时想要把没有学生的班级也显示出来毕竟那也是真实存在的班级那使用内连接肯定就不行java110班没有匹配的学生直接就被过滤掉了所以我们使用外连接右连接select字段列表from表1rightjoin表2on表1.关联字段表2.关联字段;右连接就是会把join右边的表的所有记录都保留下来也就是表2作为基准表和表1匹配时如果匹配不上那表1中的字段就用null填充左连接select字段列表from表1leftjoin表2on表1.关联字段表2.关联字段;和右连接一样此时表1就会作为基准表去和表2进行匹配使用右连接应用场景查询哪位同学缺考了该同学在学生表中有记录在分数表中没有该同学对应的记录我们就可以使用外连接把学生表的记录全部保留下来去和分数表匹配这样哪个学生没有分数他的分数就会显示null继续在后面加where条件限制只保留缺考同学的记录要注意外连接执行时主表基准表数据是强制保留从表数据才会在连接条件不匹配的时候被过滤掉所以写在on and后面的条件只是用来过滤从表的所以自己额外加的条件限制不要写在on后面直接用where子句写。它和内连接是不一样的内连接的on是对两表加的限制所以内连接的额外限制条件可以直接写在on后面但是建议就都老老实实用where子句加额外限制条件更保险自连接自连接看着玄乎但它根本不是一种新的连接方式只是join的一种特殊用法。先来看懂场景什么时候非得用自连接当一张表里同时存了具有上下级/从属关系的两类数据员工领导、分类子分类、省市、评论回复等等需要把上下级数据拆成两列并排显示时就用自连接。而你想把它们的信息拼在同一行看就得用自连接。比如你想从一个表中查询一些信息但是根据你的查询需求分析发现我们必须把该表中的不同的两行数据拼接到一块得到一条新的记录才能满足你的查询需求。而“把两条记录拼接为一条记录”这种操作我们之前是见过的就是联合查询但是联合查询要的是两张表此时我们是一张表该怎么办这就是自连接要做的为同一张表起两个不同别名直接把它们当做两个不同的表来用这样就可以实现同一张表中不同条记录的拼接而最常见的会进行“同张表中记录的拼接操作”的场景就是上面提到的“一张表里同时存了具有上下级/从属关系的两类数据”比如员工表员工和领导都是员工都存在同一张表里分类表父分类和子分类都在同一张分类表里​地区表省和市都在同一张地区表里就拿最简单的员工表 emp 举例idnameleader_id1张三32李四33王总null可以看到王总之上已经没有更高的领导层级了所以他的lender_id就为null需求查出每个员工的名字以及他直属领导的名字。我们的卡点领导的名字也存在 name 字段里表中没有单独的“领导名字”列。思路把这张表“拆成两张表”用一张当做“员工表”一张当做“领导表”用 leader_id 去匹配领导的 id 就能拼出名字。给同一张表起两个不同的别名数据库就会把它当成两张独立的虚拟表来处理 员工角色别名 e 领导角色别名 m然后正常写join关联条件员工的领导id 领导的员工id selecte.nameas员工名,m.nameas领导名fromemp ejoinemp mone.leader_idm.id;说白了还是跟之前一样的拼接两个一样的表被当做不同的表把表中的记录进行拼接然后按照自己的查询需求进行过滤本次的需求就是e.leader_id m.id执行结果员工名领导名张三王总李四王总可以看到王总自己的 leader_id 是null匹配不上内连接就会被过滤掉不会出现在结果里。自连接只描述“参与连接的表是自己”它既可以是内连接也可以是左外连接连接类型由具体的查询需求去选也就应了上面说的“自连接根本不是一种新的连接方式只是join的一种特殊用法也就是自己 join 自己”。内自连接只保留两边匹配成功的行。上面的例子就是没有领导的人最高领导会被过滤掉。左自连接left join 保留左表全体员工的所有行哪怕没有领导领导名字字段显示 null 。右自连接 right 几乎不用。所以自连接的本质就是把一张物理表“复制一份”起个别名当成两张独立的表来做join用层级字段parent_id或者是leader_id这种 做关联条件。子查询嵌套查询一个select语句里面括号里面再套一个select语法要求必须把内层查询括起来括号里的就叫子查询内层查询外面的叫主查询外层查询执行顺序先跑内层子查询把查询结果/结果集返回交给外层主查询去使用。标量子查询子查询返回单个值select*fromstudentwhereclass_id(selectclass_idfromstudentwherename小红);内层执行得到id1外层变成 where class_id 1 再查询学生。列子查询子查询返回一列多行数据select*fromstudentwhereclass_idin(selectidfromclass);子查询取出班级id{1、2}这样的多条结果外层变为where class_id in (1,2)补充两个关键字 any(子查询)大于里面任意一个就行 all(子查询)必须大于里面全部表子查询子查询返回多行多列一张虚拟表这也是之前考虑过的一个问题select查询出来的结果集虽然是张临时表但能不能保存下来供我们继续查询使用那么用嵌套查询就可以实现临时表的再利用子查询当作一张临时表放在from后面注意必须给这个虚拟表起别名需求先筛选年龄≥19岁学生再从筛选后的结果里面查询名字idselectid,namefrom(select*fromstudentwhereage19)astemp;括号里面查出来的是一张临时虚拟表名字叫temp没有别名mysql直接报错。in(option…)结合子查询的拓展用法普通in单字段wherestudent_idin(10,20,30);拿一个字段跟集合里面每一个值对比。元组in多字段一起比较where(a,b,c)in(子查询);把 (a,b,c) 整体当成一个打包好的数据单元元组拿这一整套去和子查询返回的临时表中的每一行去比对三个字段必须全部对应相等才算in匹配成功。[not] exists 关键字语法select*from表1whereexists(select*from表2);exists后面括号中的子查询如果该子查询的结果不为空则外层的查询才会被执行如果该子查询的执行结果显示Empty set则不会执行外层的查询。就像是Java的if语句判断如果为空则返回false如果不为空则返回true只不过SQL中变为了if(...)→where...exists 加上 not 也就是个取反的效果如果字查询的结果集是下面这样的会怎么样答案是其实这不是空结果集只是该结果集的列名为null值也为null而已真正的空结果集就是Empty set合并查询合并查询把多条select查询的结果集上下堆叠合并成一个结果集。注意不是表与表左右拼接join是横向拼列union是纵向上下叠加行。注意前后两个select列的数量必须一样。对应位置列的数据类型要保持一致最终返回的列名只看第一条select的字段名第二条的列名直接忽略。MySQL对合并查询并不会做校验即使两表的列并不匹配也不会报错所以需要人为校验否则可能会出现错误的结果集union去重合并selectid,namefromstudentwhereclass_id1unionselectid,namefromstudentwhereclass_id2;把一班学生、二班学生上下叠放如果存在完全一样的行只保留一行idnameclass_id1小明12小红13小刚24小丽2union all不去重原样全部合并selectid,namefromstudentwhereclass_id1unionallselectid,namefromstudentwhereclass_id2;扩展根据一个表的表结构创建一个结构内部字段完全相同的表createtablestudent2likestudent1;