数据库学习（八）—SQL数据查询05（自关联）

目录

3.8 自关联

3.8.1 介绍

3.8.2 自关联语法

---

3.8 自关联

3.8.1 介绍

自关联：自己连接自己多次，假设制作地区显示，如下图： 

💦知识点：

         像这种带有层级关系，若给每一层，每一级都来一个表，级数越多，表就越多，不利于扩展，有可能后期规划变细，不利于扩展。

层级关系：如：商品分类，

 

• 设计省信息的表结构provinces

                🥕 id

                🥕ptitle

• 设计市信息的表结构citys

                 🥕id

                🥕ctitle

               🥕 proid

•  citys表的proid表示城市所属的省，对应着provinces表的id值
• 问题：能不能将两个表合成一张表呢?

                思考：观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的

                意义：存储的都是地区信息。而且每种信息的数据量有限。没必要增加一个新表，或者将来还要存储区、乡镇信息。都增加新表的开销太大

        答案：定义表areas，结构如下

               🥕 id

              🥕atitle

               🥕pid

        因为省没有所属的省份，所以可以填写为null

        城市所属的省份pid。填写省所对应的编号id

•        这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
•         在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

3.8.2 自关联语法

用法：

        自关联一般用于：数据之间有层次结构，有上下级结构的数据 

创建省市--2级 

drop table if exists areas;

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');

❗注意：aid是唯一的 

 🧐例1:查询一共多少个省

select count(*) from areas where pid is Null

 

 🧐例2:查询河南省的所有城市

 

💬通常想法：

 步骤一：先找到河南省找到其的aid

步骤二：根据编号aid找到其的城市

-- select * from areas where atitle = '河南省'
-- 410000
-- select * from areas where pid = '410000'

这样子不符合需求，需要先得到河南省aid在进行下一步。

 方法：

-- 数据源
select * from areas as sheng, areas as shi 
where shi.pid = sheng.aid  and sheng.atitle = '河南省'

把自己多查一次 ，如图：

 

添加区县数据，将层级变为三级

insert into areas values
('410101', '中原区', '410100'),
('410102', '二七区', '410100'),
('410103', '金水区', '410100');

  🧐例3:查询郑州市的所有区县

select * from areas as shi, areas as qu
where shi.aid = qu.pid and shi.atitle = '郑州市'

 

   🧐例4:查询河南省的所有区县

因此要在做一个关联，拿省市后加区，还是市区前加省

 

-- 方法一：
-- select * from areas as sheng, areas as shi, areas as qu
-- where sheng.aid = shi.pid and shi.aid = qu.pid and sheng.atitle = '河南省'

-- 方法二：
select * from areas as sheng
inner join areas as shi on sheng.aid = shi.pid
inner join areas as qu on shi.aid = qu.pid
where sheng.atitle = '河南省'

🧐例5:查询河南省所有市区

select * from areas as sheng
inner join areas as shi on sheng.aid = shi.pid
left join areas as qu on shi.aid = qu.pid
where sheng.atitle = '河南省'

🔶 得：

        2表连接，得到2级数据

        3表连接，得到3级数据

        多表连接，得到多级数据