MySQL 自连接讲解
作者:mmseoamin日期:2023-12-18

目录

什么是自连接?

自连接语法

案例

案例演示1

案例演示2

扩展需求


什么是自连接?

        自连接可以理解为自己连接自己,在一张表上面所进行的操作;将一张表分成两张结构和数据完全一样的表(简单理解:相当于克隆了一张跟自己长得一模一样的表);

MySQL 自连接讲解,第1张

 但是问题来了,既然是两张一模一样的表,数据库怎么区分出那张表是哪张表呢?这时候最重要的一个知识点就来了,那就是给两张表分别取个别名

自连接语法

自连接我所知道有以下几种语法,有遗漏的话也欢迎大家在评论区给我补充出来。

MySQL 自连接讲解,第2张

🐱1、内连接

1.1隐式内连接

select 字段列表 from 表 [as] 表别名1,表  [as]  表别名2 where 条件...;

1.2.显式内连接

select 字段列表 from 表  [as]  表别名1 [inner] join 表  [as]  表别名2 on 条件...;

🐷2、外连接

2.1.左外连接

select 字段列表 from 表  [as]  表别名1 left [outer] join 表  [as]  表别名2 on 条件...;

2.2.右外连接

select 字段列表 from 表  [as]  表别名1 right [outer] join 表  [as]  表别名2 on 条件...;

注:

1.语法和内外连接的语法一样,只不过换成了只在一张表上面操作。

2.[ ] 里面的单词 as 代表取别名,可写可不写;不写也可以取别名,用哪种都可以啦~☂♔

案例

可以自己插入表和数据跟着做一下。

MySQL 自连接讲解,第3张

案例演示1

商品表(tb_goods):

create table tb_goods(
    id int primary key auto_increment comment '主键ID',
    goods varchar(50) not null comment '商品',
    price decimal(7,2) default 0.00 comment '商品价格'
) comment '商品表';

给商品表插入数据:

insert into tb_goods(goods,price) values('儿童牙刷',20),
                                        ('电动牙刷',10000),
                                        ('拼多多牙刷',9.9);
insert into tb_goods(goods) values('妈妈给买的牙刷');

数据展示: 

MySQL 自连接讲解,第4张

需求:

查询比 “拼多多牙刷” 的价格贵的牙刷有哪些?

思路解析(用的是隐式内连接),语法:

select 字段列表 from 表 [as] 表别名1,表  [as]  表别名2 where 条件...;

第一步:把 tb_goods(商品表)分成两张一模一样的表,分别取 g1 和 g2 两个别名,把它们连接起来;

select * from tb_goods as g1,tb_goods as g2;

查询结果如下:它会列出每条数据的组合情况,如下,每一种牙刷都能组成四种组合。

MySQL 自连接讲解,第5张

第二步:找出 g1 表里面的 “拼多多牙刷”;

select * from tb_goods as g1,tb_goods as g2 where g1.goods = '拼多多牙刷';

查询结果如下:

MySQL 自连接讲解,第6张

根据上面的语句查询出来的结果可以看到, g2 表里面的 g2.price 也已经把每个牙刷的价格查询出来了;

MySQL 自连接讲解,第7张

第三步:此时,只需要查询出 g2 所有牙刷的价格(g2.price)大于 g1 “拼多多牙刷”的价格(g1.price)的数据就可以了;

select * from tb_goods as g1,tb_goods as g2 where g1.goods = '拼多多牙刷' and g2.price > g1.price;

查询结果如下:在 g2 表里面,已经查出了大于 g1 “拼多多牙刷”的价格数据。

MySQL 自连接讲解,第8张

第四步:然后就可以把需要的数据进行查询;

select g2.goods,g2.price from tb_goods as g1,tb_goods as g2 where g1.goods = '拼多多牙刷' and g2.price > g1.price;

查询结果如下:得到了最终结果,比 “拼多多牙刷” 的价格贵的牙刷,完成了需求。

MySQL 自连接讲解,第9张

最后,如果要查询的数据更清晰的话,可以给查询的字段取别名;

select g2.goods as '商品',g2.price as '商品价格' from tb_goods as g1,tb_goods as g2 where g1.goods = '拼多多牙刷' and g2.price > g1.price;

查询结果如下:

MySQL 自连接讲解,第10张

温馨提醒您:取别名不加 as 也可以,只不过是我自己的个人习惯,嘿嘿~

同样的,用显示内连接也可以完成该需求:

select g2.* from tb_goods as g1 inner join tb_goods as g2 on g1.goods = '拼多多牙刷' where g2.price > g1.price;

查询结果如下:

MySQL 自连接讲解,第11张

用左外连接也可以完成该需求:

select g2.* from tb_goods as g1 left outer join tb_goods as g2 on g1.goods = '拼多多牙刷' where g2.price > g1.price;

查询结果如下:

MySQL 自连接讲解,第11张


案例演示2

学生表(tb_student):

create table tb_student(
    id int primary key auto_increment comment '主键ID',
    student_id char(2) not null unique comment '学号',
    name varchar(50) not null comment '姓名',
    age tinyint unsigned not null comment '年龄',
    parent_id char(2) comment '监护人ID'
)comment '学生表';

给学生表插入数据:

insert into tb_student(student_id, name, age,parent_id) VALUES('01','大头儿子',6,'03'),
                                                              ('03','小头爸爸',31,null),
                                                              ('02','小灰灰',5,'04'),
                                                              ('04','灰太狼',36,null);

数据展示:  

MySQL 自连接讲解,第13张

需求:

查询学生姓名及学生监护人姓名。

思路解析(用的是隐式内连接),语法:

select 字段列表 from 表 [as] 表别名1,表  [as]  表别名2 where 条件...;

第一步:把 tb_student(学生表)分成两张一模一样的表,分别取 s1 和 s2 两个别名,把它们连接起来;

select * from tb_student as s1,tb_student as s2;

MySQL 自连接讲解,第14张

第二步:看到这个需求,你可能会觉得奇怪,为什么都没有 学生监护人姓名 这个字段,而只有 学生监护人ID ;

MySQL 自连接讲解,第15张

 那是因为我们可以通过 学生监护人ID(parent_id) 关联 学生学号(student_id),找到该学生的学生监护人(student_id)。

MySQL 自连接讲解,第16张

比如:大头儿子的 学生监护人ID(parent_id)是 03,此时 03 学生学号(student_id)的家长为小头爸爸;

MySQL 自连接讲解,第17张

SQL 语句编写:

第一步:把 tb_student(学生表)分成两张一模一样的表,分别取 s1 和 s2 两个别名,把它们连接起来;

select * from tb_student as s1,tb_student as s2;

第二步:找到 s1 表的 学生监护人ID(parent_id)和 s2 表的 学生学号(student_id),把它们用 = 关联起来,意思就是:通过 学生监护人ID(parent_id)找到 学生学号(student_id);

select * from tb_student as s1,tb_student as s2 where s1.parent_id = s2.student_id;

查询结果如下:从查询出来的结果,就可以看到对应的 学生姓名(s1.name) 及 学生监护人姓名(s2.name) 都已经被找到了。

MySQL 自连接讲解,第18张

第三步:这时候就可以对学生姓名(s1.name)及学生监护人(s2.name)这两个数据进行查询;

select s1.name,s2.name from tb_student as s1,tb_student as s2 where s1.parent_id = s2.student_id;

查询结果如下:已经实现该需求。

MySQL 自连接讲解,第19张

最后,如果要查询的数据更清晰的话,可以给查询的字段取别名;

select s1.name as '学生姓名',s2.name as '学生监护人姓名' from tb_student as s1,tb_student as s2 where s1.parent_id = s2.student_id;

查询结果如下:

MySQL 自连接讲解,第20张

同样的,用显示内连接也可以完成该需求:

select s1.name '学生姓名',s2.name as '学生监护人姓名' from tb_student as s1 inner join tb_student s2 on s1.parent_id = s2.student_id;

查询结果如下:

MySQL 自连接讲解,第20张

扩展需求

MySQL 自连接讲解,第22张

在这个学生表(tb_student)里面,家长是没有归属人的,也就是 parent_id 为 的情况,比如小头爸爸和灰太狼; 

MySQL 自连接讲解,第23张

此时,我又有一个需求:我希望查询 学生姓名 及 学生的监护人 姓名,如果学生没有学生的监护人, 也要查询出来。

此时就不能使用内连接了,内连接只能查出它们相互交集的数据;要改成左外连接;

select s1.name '学生姓名',s2.name '学生监护人姓名' from tb_student as s1 left join tb_student as s2 on s1.parent_id = s2.student_id;

查询结果如下:

MySQL 自连接讲解,第24张

MySQL 自连接讲解,第25张

完。。。