MySQL 自连接讲解

什么是自连接？

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

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

自连接语法

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

🐱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 代表取别名，可写可不写；不写也可以取别名，用哪种都可以啦~☂♔

案例

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

案例演示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('妈妈给买的牙刷');

**数据展示： **

需求：

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

思路解析（用的是隐式内连接），语法：

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

第一步：把 tb_goods（商品表）分成两张一模一样的表，分别取 g1 和 g2 两个别名，把它们连接起来；

select * from tb_goods as g1,tb_goods as g2;

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

第二步：找出 g1 表里面的 “拼多多牙刷”；
select * from tb_goods as g1,tb_goods as g2 where g1.goods = '拼多多牙刷';

查询结果如下：

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

第三步：此时，只需要查询出 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 “拼多多牙刷”的价格数据。

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

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

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

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

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

查询结果如下：

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

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

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

查询结果如下：

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

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

查询结果如下：

案例演示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);

**数据展示： **