use chinacity;
provinceNo tinyint primary key,
provinceName varchar(20)
);
oneCityId smallint primary key,
oneCityNo char(2),
oneCityName varchar(40),
provinceNo tinyint,
foreign key (provinceNo) references t_province(provinceNo) on delete cascade on update cascade
);
twoCityNo char(2),
twoCityName varchar(40),
oneCityId smallint,
primary key(twoCityNo, oneCityId),
foreign key (oneCityId) references t_oneCity(oneCityId) on delete cascade on update cascade
#省
provinceNo tinyint primary key,
provinceName varchar(20)
);
#一级市
oneCityId smallint primary key,
oneCityNo char(2),
oneCityName varchar(40),
provinceNo tinyint,
foreign key (provinceNo) references t_province(provinceNo) on delete cascade on update cascade
);
#注:oneCityId是由省的编号 + 一级市编号组成的
#如:南昌的oneCityId为3601(其中36为江西省的编号,01为南昌市的编号)
#二级市
twoCityNo char(2),
twoCityName varchar(40),
oneCityId smallint,
primary key(twoCityNo, oneCityId),
foreign key (oneCityId) references t_oneCity(oneCityId) on delete cascade on update cascade
);
整理的用途:
有了这个与国家统一的编号之后,只要我们得到了用户的身份证号,就可以从身份证的前6位解析出他的省、一级市 、二级市(籍贯)
已整理成sql插入语句。
省:
一级市:
二级市:
资源下载地址:http://download.csdn.net/detail/zhang527836447/5480639