MySQL-自关联介绍-2021/09/16

过了10天才继续看MySQL希望看完别半途而废

自关联介绍

只要有上下级关系的都存在一个表中，有两个字段，一个是编号，一个是上级编号。这样就可以满足要求。

构造数据

create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
-- 添加省市数据
insert into areas
values ('130000', '河北省', NULL),
('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'),
('130600', '保定市', '130000'),
('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),
('410000', '河南省', NULL),
('410100', '郑州市', '410000'),
('410300', '洛阳市', '410000'),
('410500', '安阳市', '410000'),
('410700', '新乡市', '410000'),
('410800', '焦作市', '410000');

-- 添加区县数据
insert into areas values
('410101', '中原区', '410100'),
('410102', '二七区', '410100'),
('410103', '金水区', '410100');

练习题：

查询两个表，复制areas然后粘贴道表中

在这里插入代码片

1. 查询有多少个省？

SELECT COUNT(*) FROM areas WHERE pid is null;

例1：查询河南省的所有城市 单极跳跃

SELECT * FROM areas , areas_copy1

然后让areas.aid = areas_copy1.pid 找到与省具有从属关系的数据

SELECT * FROM areas , areas_copy1
where areas.aid = areas_copy1.pid 

然后再用and进行查询areas.aid = '河南省’进行筛选

SELECT * FROM areas , areas_copy1
where areas.aid = areas_copy1.pid
and areas.atitle = '河南省'

这样查询显然是费事的，要复制一个表。其实可以对一个表进行两次查询。但是要给表起一个别名。

select
*
from
areas as p
inner join areas as c on c.pid=p.aid
where
p.atitle='河北省';

利用内连接

select
*
from
areas as c
inner join areas as a on a.pid=c.aid
where
c.atitle='郑州市';

例2：查询郑州市的所有区县 单极跳跃

select 
*
from
areas c, areas q
where c.aid = q.pid
and c.atitle = '郑州市'

例3：查询河南省的所有区县 两级跳跃

再连接一个区的表

select 
*
from
areas sheng, areas shi, areas qu
where sheng.aid = shi.pid
and shi.aid = qu.pid
and sheng.atitle = '河南省'

利用左连接

select
*
from
areas as p
left join areas as c on c.pid=p.aid
left join areas as a on a.pid=c.aid
where
p.atitle='河南省'