创建 areas 地区表
-- 创建表
create database pyhton3;
use python3;
create table areas(
id int primary key,
title char(32) default '',
pid int
);
-- 导入数据
source ~/Desktop/areas.sql;
-- 查看总数
select count(*) from areas;
-- 添加外键
alter table areas add foreign key(pid) references areas(id);
自关联表
查询 各个省份 的市地州数量的示例
select prov.title, count(*) as count
from areas as city
inner join areas as prov on city.pid=prov.id
inner join areas as dist on dist.pid=city.id
where prov.pid is null
group by prov.title;
此处把一个表 当成了三个表:
第一个表 prov 表的pid字段是空, 说明 prov表的所有数据都是省份. (省份没有pid)
第二个表 city 表的pid和prov的id 对应, 所以 city 表里的数据都是 pid指向 prov省份 的市级数据.
第三个表 dist 表的pid和city的id 对应, 所以 dist 表里的数据都是 pid指向 city市级 的地级市数据.
以上, 按照 pid的对应关系, 把一个表的 数据分开, 成为 彼此不重复的三个表, 便于查询结果.
查看
-- 查看数据库结构
show create database python3;
-- 查看表结构
desc areas;
-- 查看创建表代码
show create table areas;
| areas | CREATE TABLE `areas` (
`id` int(11) NOT NULL,
`title` char(32) DEFAULT '',
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `areas_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `areas` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
constraint xxx: 表示创建的外键名, 可以在创建时指定, 也可以用自动生成的
注意
一个数据库的外键名不能一样, 要确保外键名的唯一!!
-- 创建指定外键名 con_self 的外键
create table areas1(
id int primary key,
title char(32) default '',
pid int,
constraint con_self foreign key(pid) references areas1(id)
);
添加外键
create table areas2(
id int primary key,
title char(32) default '',
pid int
);
-- 添加外键
alter table areas2 add constraint con_self_1 foreign key(pid) references areas2(id);
删除外键
alter table areas2 drop foreign key con_self_1;
技巧
在大量导入数据的时候, 特别是 自关联表, 由于彼此关联, 插入的先后顺序不同可能导致 外键冲突导入失败, 可以先删除外键, 待数据导入后在插入外键 约束.
转载 卢景浩
