[大数据]MYSQL 寒假自学 2022 十一（九）

自关联查询

mysql有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时必须给表起别名。

准备工作

use mydb3;

create table t_sanguo
(
	eid int primary key,
	ename varchar(20),
	manager_id int,
	foreign key (manager_id) references t_sanguo(eid) #添加自关联查询
);

insert into t_sanguo values(1,'刘协',null);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);

打开逆向表到模型

part1

-- 进行关联查询
-- 查询每个三国人物及他的上级信息，如：关羽 刘备
select a.ename,b.ename from t_sanguo a,t_sanguo b where a.manager_id = b.eid;
select a.ename,b.ename from t_sanguo a join t_sanguo b on a.manager_id = b.eid;

?运行结果如下

?part2

-- 查询所有任务及上级，包括刘协
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;

?part3

-- 查询所有人物，上级，上上级 比如： 张飞，刘备，刘协
select a.ename,b.ename,c.ename
from t_sanguo a 
	left join t_sanguo b on a.manager_id = b.eid
	left join t_sanguo c on b.manager_id = c.eid;

?