MySQL基础篇【第四篇】| 连接查询、子查询（嵌套）
✅作者简介：大家好我是@每天都要敲代码，一位材料转码农的选手，希望一起努力，一起进步！
📃个人主页：@每天都要敲代码的个人主页
🔥系列专栏：MySQL专栏
💬推荐一款模拟面试、刷题神器，从基础到大厂面试题 👉点击跳转刷题网站进行注册学习
目录
一：连接查询
1. 连接查询原理以及笛卡尔积现象
2. 内连接
2.1 等值连接
2.2 非等值连接
2.3 自连接
3. 外连接（左外和右外）
二：子查询
1. where后面嵌套子查询
2. from后面嵌套子查询(重要)
3. select后面嵌套子查询
结束语
一：连接查询
连接查询：也可以叫跨表查询，需要关联多个表进行查询
（1）根据语法出现的年代来划分的话，包括：
SQL92（一些DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）
SQL99（比较新的语法）
（2）根据表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（很少用）
1. 连接查询原理以及笛卡尔积现象
笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积
例1：显示每个员工信息，并显示所属的部门名称
第一步：先找出员工名，EMP表
select ename,deptno from emp;
第二步：找出部门名，DEPT表
select * from dept;
第三步： 结合使用，实际上会有：14 * 4 = 56条数据；每一个EMP中的数据都会与DEPT中的数据结合一次
select ename,dname from emp,dept; --56条数据，笛卡尔现象第四步：进行过滤，只有EMP的deptno 和 DEPT的deptno相等时，才进行拼接结合
怎么避免笛卡尔积现象？
加条件进行过滤；但是不会减少记录的匹配次数，次数还是56次；只不过显示的是有效记录
select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;
select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno; --使用别名
--表的别名有什么好处？
--第一：执行效率高。
--第二：可读性好。
-- 以上是SQL92语法，老语法
2. 内连接
2.1 等值连接
最大特点是：条件是等量关系
例1：还是那个例子，显示每个员工信息，并显示所属的部门名称；使用SQL92和SQL99两种语法写
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno; ---等值连接
-- SQL92语法的where不是真正用到过滤条件，只能在后面用and加入过滤条件
select
e.ename,d.dname
from
emp e
join -- inner join 这里省略了inner
dept d
on
e.deptno = d.deptno; ---等值连接
--join B on 连接条件，后面还可以继续跟where；where后面才是真正的过滤条件
--SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了
--inner可以省略的，带着inner目的是可读性好一些，表示内连接
2.2 非等值连接
最大的特点是：连接条件中的关系是非等量关系
例1：找出每个员工的工资等级，要求显示员工名、工资、工资等级
第一步：先找出每个员工的员工名和工资
select ename,sal from emp;
第二步：再找出员工工资等级
select * from salgrade;
第三步：根据emp sal的取值范围，在salgrade losal 和 hisal 范围之间进行关系建立
select
e.ename,e.sal,s.grade
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal;
2.3 自连接
最大的特点是：一张表看做两张表；自己连接自己
例1：找出每个员工的上级领导，要求显示员工名和对应的领导名
解析：员工和上级领导在同一张表中，因为领导也是员工!
查看表中所有信息
select * from emp;
这张表既可以看成员工表，又可以看成领导表 ；我们只需要让：
员工的领导编号 = 领导的员工编号
select
a.ename as '员工名',b.ename as '领导名'
from
emp a
inner join --inner可省略
emp b
on
a.mgr = b.empno; --员工的领导编号 = 领导的员工编号
只有13条数据，因为king虽然也是员工，但是他没有上级领导！
3. 外连接（左外和右外）
（1）什么是外连接，和内连接有什么区别？
内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。
外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
（2）外连接的分类
左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。左连接有右连接的写法，右连接也会有对应的左连接的写法。
（3）外连接最重要的特点是：主表的数据无条件的全部查询出来
（1）找出每个员工的上级领导？（所有员工必须全部查询出来包括king）
注：我们在使用内连接---等值查询时，没有显示king的信息，因为他的上级是NULL，匹配不上所以没有显示，只显示了13条信息；那么我们如果使用外连接，显示14条信息呢？
使用左外连接：
select
a.ename '员工', b.ename '领导'
from
emp a
left outer join -- 左外链接，outer可以省略
emp b
on
a.mgr = b.empno;
使用右外连接：
select
a.ename '员工', b.ename '领导'
from
emp b
right outer join -- 右外链接，outer可以省略
emp a
on
a.mgr = b.empno;
（2）找出哪个部门没有员工？
第一步：找员工表EMP
第二步：找部门表 DEPT
第三步：从两张表中可以看出部门编号为40的没有员工，那么怎么显示出来呢？
select e.*,d.*
from emp e
right join dept d
on e.deptno = d.deptno;
我们发现14个员工，却有15条数据，因为我们是以dept表为主表查询的，没有员工的会自动匹配为NULL；所以我们只需要最后一条数据，利用where进行进一步的筛选
select d.*,e.ename
from emp e
right join dept d --采用右连接，dept是主表
on e.deptno = d.deptno
where e.ename is null; -- 进一步筛选信息
（3）找出每一个员工的部门名称以及工资等级（三张表连接查询）
第一步：拿到员工信息
select empno,ename,deptno,sal from emp;
第二步：拿到部门信息
select * from dept;
第三步：拿到等级信息
select * from salgrade;
第四步：先通过deptno让员工表EMP和部门表DEPT连接（两张表）
select e.ename,d.dname
from emp e
join dept d
on e.deptno = d.deptno;
第五步：在通过薪资sal与等级表建立联系（三张表）
select e.ename,d.dname,s.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on sal between s.losal and s.hisal;
（4）找出每一个员工的部门名称、工资等级、以及上级领导
注：这就需要外连接了，因为king虽然没有上级领导，但是有部门名称、工资等级，必须显示出来！
select e.ename '员工',d.dname,s.grade,e1.ename '领导'
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
left join emp e1 --表示e1与e是左连接（这里实际上是增加了一个自连接）
on e.mgr = e1.empno; --员工的领导编号 = 领导的员工编号
-- 不使用外连接left，实际上带有null的信息就会不显示，只有13条数据
二：子查询
什么是子查询？子查询都可以出现在哪里？
select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？select ..(select) --出现在selelct后面 from ..(select) --出现在from后面 where ..(select) --出现在where后面子查询就是嵌套的select语句，可以理解为子查询是一张表
1. where后面嵌套子查询
（1）找出高于平均薪资的员工信息
注：select * from emp where sal > avg(sal); 错误的写法，where后面不能直接使用分组函数。
第一步：找出平均薪资
select avg(sal) from emp;
第二步：where过滤
select * from emp where sal > 2073.214286;
第三步：联合嵌套使用
select ename,sal from emp where sal > (select avg(sal) from emp);
（2）查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
第一步：首先取得管理者的编号，去除重复的；并且排查出NULL
select distinct mgr from emp where mgr is not null;
--提出重复的，并且让显示出其员工编号和员工姓名，肯定要排查出NULL
第二步： 查询员工编号包含管理者编号的
select empno,ename from emp where empno in (select distinct mgr from emp where mgr is not null);
-- 子查询中，先找到mgr的编号，再根据员工编号包含mgr的编号，是就代表是管理者
2. from后面嵌套子查询(重要)
（1）找出每个部门平均薪水的等级
第一步：找出每个部门平均薪水（按照部门分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
select t.*,s.grade
from (select deptno,avg(sal) as avgsal from emp group by deptno) t --上一个运行的结果看成一个表，起别名t
join salgrade s
on t.avgsal between s.losal and s.hisal;
（2）找出每个部门平均的薪水等级
注：这道题与上面有所不同，我们第一步查询的数据都是原数据，并没有新数据，我们不需要临时表，直接拿来用就行！
第一步：找出每个部分的薪水等级
select e.ename,e.sal,e.deptno,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;
第二步：基于以上结果，继续按照deptno分组，求grade字段平均值
select e.deptno,avg(s.grade)
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by e.deptno;
3. select后面嵌套子查询
（1）找出每个员工所在的部门名称，要求显示员工名和部门名
第一种方法：使用连接
select e.ename,d.dname
from emp e
join dept d
on e.deptno = d.deptno;
第二种方法：使用select嵌套
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from emp e;
结束语
今天的分享就到这里啦！快快通过下方链接注册加入刷题大军吧！各种大厂面试真题在等你哦！
💬刷题神器，从基础到大厂面试题👉点击跳转刷题网站进行注册学习
