MySQL基础篇【第四篇】| 连接查询、子查询（嵌套）

✅作者简介：大家好我是@每天都要敲代码，一位材料转码农的选手，希望一起努力，一起进步！
📃个人主页：@每天都要敲代码的个人主页
🔥系列专栏：MySQL专栏
💬推荐一款模拟面试、刷题神器，从基础到大厂面试题 👉点击跳转刷题网站进行注册学习

一：连接查询

连接查询：也可以叫跨表查询，需要关联多个表进行查询

（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
innerjoin
    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
innerjoin--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
leftouterjoin-- 左外链接，outer可以省略
    emp b
on
    a.mgr = b.empno;

​

** 使用右外连接：**

select 
    a.ename '员工', b.ename '领导'from
    emp b
rightouterjoin-- 右外链接，outer可以省略
    emp a
on
    a.mgr = b.empno;

​

（2）找出哪个部门没有员工？

第一步：找员工表EMP

​

第二步：找部门表 DEPT

​

第三步：从两张表中可以看出部门编号为40的没有员工，那么怎么显示出来呢？

select e.*,d.*from emp e
rightjoin dept d
on e.deptno = d.deptno;

​

我们发现14个员工，却有15条数据，因为我们是以dept表为主表查询的，没有员工的会自动匹配为NULL；所以我们只需要最后一条数据，利用where进行进一步的筛选

select d.*,e.ename 
 from emp e
 rightjoin dept d --采用右连接，dept是主表on e.deptno = d.deptno
 where e.ename isnull; -- 进一步筛选信息

​

（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
leftjoin 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后面不能直接使用分组函数。

第一步：找出平均薪资

selectavg(sal) from emp;

​

第二步：where过滤

select*from emp where sal >2073.214286;

​

第三步：联合嵌套使用

select ename,sal from emp where sal > (selectavg(sal) from emp);

​

（2）查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

第一步：首先取得管理者的编号，去除重复的；并且排查出NULL

selectdistinct mgr from emp where mgr isnotnull;
--提出重复的，并且让显示出其员工编号和员工姓名，肯定要排查出NULL

​

第二步： 查询员工编号包含管理者编号的

select empno,ename from emp where empno in (selectdistinct mgr from emp where mgr isnotnull);
-- 子查询中，先找到mgr的编号，再根据员工编号包含mgr的编号，是就代表是管理者

​

2. from后面嵌套子查询(重要)

（1）找出每个部门平均薪水的等级

第一步：找出每个部门平均薪水（按照部门分组，求sal的平均值）

select deptno,avg(sal) as avgsal from emp groupby 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 groupby deptno) t --上一个运行的结果看成一个表，起别名tjoin 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
groupby 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;

​

结束语

今天的分享就到这里啦！快快通过下方链接注册加入刷题大军吧！各种大厂面试真题在等你哦！
💬刷题神器，从基础到大厂面试题👉点击跳转刷题网站进行注册学习

​