0


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;

结束语

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

标签: 数据库 sql

本文转载自: https://blog.csdn.net/m0_61933976/article/details/126288297
版权归原作者 @每天都要敲代码 所有, 如有侵权,请联系我们删除。

“MySQL基础篇【第四篇】| 连接查询、子查询(嵌套)”的评论:

还没有评论