MySQL高级之自关联

设计省信息的表结构provinces

  • id
  • ptitle

设计市信息的表结构citys

  • id
  • ctitle
  • proid

citys表的proid表示城市所属的省,对应着provinces表的id值

问题:能不能将两个表合成一张表呢?

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

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

答案:定义表areas,结构如下

  • id
  • atitle
  • pid

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

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

这就是自关联,表中的某一列,关联了这个表中的另外一列,但是它们的业务逻辑含义是不一样的,城市信息的pid引用的是省信息的id

在这个表中,结构不变,可以添加区县、乡镇街道、村社区等信息

创建areas表的语句如下:

create table areas(
    id int primary key,
    atitle varchar(20),
    pid int,
    foreign key(pid) references areas(id)
);

从sql文件中导入数据

source areas.sql;

查询一共有多少个省

查询省的名称为“山西省”的所有城市

select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.atitle='山西省';

 

查询市的名称为“广州市”的所有区县

select dis.*,dis2.* from areas as dis
inner join areas as city on city.id=dis.pid
left join areas as dis2 on dis.id=dis2.pid
where city.atitle='广州市';

 

上一篇:自关联


下一篇:省市区级联