| 【MySQL数据库入门】自关联 | 您所在的位置：网站首页 › 叮咛的意思和什么有关联 › 【MySQL数据库入门】自关联 |
【MySQL数据库入门】自关联
|
文章目录
1. 自关联定义2. 自关联举例2.1. 需求2.2. 设计2.3. 简化2.4. 结论
3. MySQL实现3.1. 创建数据表3.2. 批量插入数据3.3. 演练
1. 自关联定义
所谓自关联是指，一个数据表中的某个字段关联了该数据表中的另外一个字段。 2. 自关联举例下面我们通过设计一套数据表，实现一个具体需求，来通俗解释什么叫自关联。 2.1. 需求假设现在需要设计一套数据表，使这套数据表可以通过结构化的方式存储全国的省、市、区/县的名称和层级从属关系。如：安徽省–>合肥市–>包河区；江苏省–>南京市–>江宁区等。 2.2. 设计首先，第一反应是按照上述三个行政层级分别创建一个数据表，即省级信息数据表、市级信息数据表、区/县级数据表，对于省级信息数据表，如表1所示： 表1 省级信息数据表 id省份1安徽省2江苏省3江西省……对于市级信息表，因为要体现市和省的层级关系，在市级信息数据表2中加了parent_id字段，该字段存储了表1对应省的主键，如景德镇市隶属于江西省，则其parent_id取值为江西在表1中的id值3。 表2 市级信息数据表 id市parent_id1合肥市12安庆市13南京市24无锡市25南昌市36景德镇市3………对于区/县级信息数据表，类似于表2设计得到了表3，即表3中字段parent_id存储了表2中对应市的主键。 表3 区/县级信息数据表 id区/县parent_id1包河区12庐阳区13宿松县24望江县25江宁区36雨花台区37崇安区48北塘区4……… 2.3. 简化上述的设计最终将产生3张数据表，实际使用时可能较为复杂，而且如果后续希望将地区信息进一步细化，如：区/县下设乡镇，乡镇下设村等，则会进一步增加数据表数量。 鉴于上述考虑，我们思考是否可以仅通过一张数据表实现所有层级数据的存储。通过观察表1、2、3我们发现，其格式都可以统一为表4所示，只是对于表1，可以将parent_id全部视为null。 表4 行政层级统一数据表 id行政层级名称parent_id………基于表4的设计，我们可以将表1、2、3合并为表5，即： 表5 行政区域信息统一数据表 id行政区域名称parent_id1安徽省null2江苏省null3江西省null4合肥市15安庆市16南京市27无锡市28南昌市39景德镇市310包河区411庐阳区412宿松县513望江县514江宁区6……… 2.4. 结论如表5所示，第9条数据中，parent_id为3，即取值为江西省的id值。则表5中字段id和parent_id之间即构成了自关联。 3. MySQL实现 3.1. 创建数据表按照表5结构在数据库test（此处创建数据库过程省略，具体请见MySQL数据库入门（2）——数据库操作基础）中创建一个名为areas的数据表。 create table areas( id int primary key, title varchar(20), parent_id int ); 3.2. 批量插入数据 数据准备 所需批量数据请见中国省市插入sql语句，请将所有数据复制到txt文件后，将.txt后缀更改为.sql。插入准备 首先按照下图所示做好数据批量插入的准备。 批量插入 在mysql>后输入以下语句，从而实现数据批量插入。
source areas.sql
3.3. 演练
-- 查询数据表areas，确定所有parent_id字段为null的记录，即全国的省级行政区域。
select * from areas where parent_id is null;
下面考虑如何仅通过一条SQL语句实现相同功能，问题在于此处仅有一张数据表，如果省级行政区域和市级行政区域各有一张表，则可以通过MySQL数据库入门（3）——数据库查询操作中学习过连接查询实现，即使用类似以下的SQL语句实现。 -- 假设有两张数据表，分别为province和city，且其结构都如表5所示 select province inner join city on province.id=city.id having province.title="安徽省";基于上述考虑，即使现在只有一个统一的数据表情况下，也可以实现上述需求，因为可以将数据表areas同时使用两次，分别作为内连接查询语句的左表和右表，即： -- 将数据表areas分别取别名province和city select * from areas as province inner join areas as city on city.parent_id=province.id having province.title="安徽省";
|
上述案例通过两条SQL语句实现了需求，即：先通过一条SQL语句查询areas表获取了安徽省的字段id值，然后基于表5的特征，即市级行政区域的parent_id等于省级行政区域的id，从而得出结果。
【本文地址】