15 python与mysql 自关联

自关联

• 设计省的信息表 provinces
  • id
  • ptitle
• 设计市的信息表 cities
  • id
  • ctitle
  • proid 表示所在省，对应provinces的id

问题：两张表可以合成一张表吗？因为存储的都是地区信息，cities相比provinces就是多了一列proid而已。
答：所以，可以定义一个表areas：

• id
• atitle
• pid
  对于省份来说，没有所在省这个信息，pid这一字段可以留null。

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

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

创建areas表的命令：
create table areas(
id int primary key auto_increment not null, ! 顺序不限
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);

网上有相关的地区信息的sql文件，可以直接导入。
source xxx.sql; 导入信息

自关联查询

太多表会增加数据库的开销，所以尽量用更少的表。

例子

1. 查询一共有多少个省
   select count(*) from areas where pid is null;

2. 查询省包含的所有城市
   select sheng.id as sid, sheng.title as stitle,
   shi.id as shiid, shi.title as shititle
   from areas as sheng
   inner join areas as shi on sheng.id=shi.pid
   where sheng.pid is null
   limit 0,100;

   这里sheng和shi指的都是areas这个表。

   inner join用来连接sheng和shi，但是其实指的都是areas，也就是areas与自己连接。连接的条件是：
   sheng.id=shi.pid

   这里为了排除一些直辖市的影响，加了一个where
   因为省的pid是空的，而直辖市的pid不是空的。

   limit用来选出前100条记录。

3. 查询省的名称为"山西省"的所有城市
   select city.* from areas as city
   innner join areas as province on city.pid=province.id
   where province.atitle=‘山西省’;

4. 查询市的名称是广州市的所有区县
   select dis.* from areas as dis
   inner join areas as city on dis.pid=city.id
   where city.atitle=‘广州市’;