全国城市区县二级联动数据库脚本（MySQL 版）

 

-- 判断数据库 city 是否存在，存在则删除

drop database if exists city;

-- 创建数据库 city

create database city;

-- 切换当前数据库为 city

use city;

create table tb_country(
countryid int primary key AUTO_INCREMENT,
countryname varchar(30) null

);

create table tb_city(
cityid int primary key AUTO_INCREMENT,
cityname varchar(30) null ,
countryid int

);

create table tb_quxian(
quxianid int primary key AUTO_INCREMENT ,
quxianname varchar(30) null,
cityid int
);

insert into tb_country values(1,"中国");

insert into tb_city values(1,"北京市",1);
insert into tb_city values(2,"天津市",1);
insert into tb_city values(3,"河北省",1);

insert into tb_city values(4,"山西省",1);
insert into tb_city values(5,"内蒙古",1);
insert into tb_city values(6,"辽宁省",1);
insert into tb_city values(7,"吉林省",1);
insert into tb_city values(8,"黑龙江省",1);
insert into tb_city values(9,"上海市",1);
insert into tb_city values(10,"江苏省",1);
insert into tb_city values(11,"浙江省",1);
insert into tb_city values(12,"安徽省",1);
insert into tb_city values(13,"福建省",1);
insert into tb_city values(14,"江西省",1);
insert into tb_city values(15,"山东省",1);
insert into tb_city values(16,"河南省",1);
insert into tb_city values(17,"湖北省",1);
insert into tb_city values(18,"湖南省",1);
insert into tb_city values(19,"广东省",1);
insert into tb_city values(20,"广西自治区",1);
insert into tb_city values(21,"海南省",1);
insert into tb_city values(22,"重庆市",1);
insert into tb_city values(23,"四川省",1);
insert into tb_city values(24,"贵州省",1);
insert into tb_city values(25,"云南省",1);
insert into tb_city values(26,"西藏自治区",1);
insert into tb_city values(27,"陕西省",1);
insert into tb_city values(28,"甘肃省",1);
insert into tb_city values(29,"青海省",1);
insert into tb_city values(30,"宁夏回族自治区",1);
insert into tb_city values(31,"新疆维吾尔自治区",1);
insert into tb_city values(32,"香港特别行政区",1);
insert into tb_city values(33,"澳门特别行政区",1);
insert into tb_city values(34,"台湾省",1);

insert into tb_quxian(quxianname,cityid) values("北京",1),("东城区",1),("西城区",1),("崇文区",1),("宣武区",1),("朝阳区",1),("丰台区",1),("石景山区",1),("海淀区（中关村）",1),("门头沟区",1),("房山区",1),("通州区",1),("顺义区",1),("昌平区",1),("大兴区",1),("怀柔区",1),("平谷区",1),("密云县",1),("延庆县",1),("其他",1);

insert into tb_quxian(quxianname,cityid) values("和平区",2),("河东区",2),("河西区",2),("南开区",2),("红桥区",2),("塘沽区",2),("汉沽区",2),("大港区",2),("西青区",2),("津南区",2),("武清区",2),("蓟县",2),("宁河县",2),("静海县",2),("其他",2);

insert into tb_quxian(quxianname,cityid) values("石家庄市",3),("张家口市",3),("承德市",3),("秦皇岛市",3),("唐山市",3),("廊坊市",3),("衡水市",3),("沧州市",3),("邢台市",3),("邯郸市",3),("保定市",3),("其他",3);

insert into tb_quxian(quxianname,cityid) values("太原市",4),("朔州市",4),("大同市",4),("长治市",4),("晋城市",4),("忻州市",4),("晋中市",4),("临汾市",4),("吕梁市",4),("运城市",4),("其他",4);

insert into tb_quxian(quxianname,cityid) values("呼和浩特市",5),("包头市",5),("赤峰市",5),("呼伦贝尔市",5),("鄂尔多斯市",5),("乌兰察布市",5),("巴彦淖尔市",5),("兴安盟",5),("阿拉善盟",5),("锡林郭勒盟",5),("其他",5);

insert into tb_quxian(quxianname,cityid) values("沈阳市",6),("朝阳市",6),("阜新市",6),("铁岭市",6),("抚顺市",6),("丹东市",6),("本溪市",6),("辽阳市",6),("鞍山市",6),("大连市",6),("营口市",6),("盘锦市",6),("锦州市",6),("葫芦岛市",6),("其他",6);

insert into tb_quxian(quxianname,cityid) values("长春市",7),("白城市",7),("吉林市",7),("四平市",7),("辽源市",7),("通化市",7),("白山市",7),("延边朝鲜族自治州",7),("其他",7);

insert into tb_quxian(quxianname,cityid) values("哈尔滨市",8),("七台河市",8),("黑河市",8),("大庆市",8),("齐齐哈尔市",8),("伊春市",8),("佳木斯市",8),("双鸭山市",8),("鸡西市",8),("大兴安岭地区(加格达奇)",8),("牡丹江",8),("鹤岗市",8),("绥化市",8),("其他",8);

insert into tb_quxian(quxianname,cityid) values("黄浦区",9),("卢湾区",9),("徐汇区",9),("长宁区",9),("静安区",9),("普陀区",9),("闸北区",9),("虹口区",9),("杨浦区",9),("闵行区",9),("宝山区",9),("嘉定区",9),("浦东新区",9),("金山区",9),("松江",9),("青浦区",9),("南汇区",9),("奉贤区",9),("崇明县",9),("其他",9);

insert into tb_quxian(quxianname,cityid) values("南京市",10),("徐州市",10),("连云港市",10),("宿迁市",10),("淮安市",10),("盐城市",10),("扬州市",10),("泰州市",10),("南通市",10),("镇江市",10),("常州市",10),("无锡市",10),("苏州市",10),("其他",10);

insert into tb_quxian(quxianname,cityid) values("杭州市",11),("湖州市",11),("嘉兴市",11),("舟山市",11),("宁波市",11),("绍兴市",11),("衢州市",11),("金华市",11),("台州市",11),("温州市",11),("丽水市",11),("其他",11);

insert into tb_quxian(quxianname,cityid) values("合肥市",12),("宿州市",12),("淮北市",12),("亳州市",12),("阜阳市",12),("蚌埠市",12),("淮南市",12),("滁州市",12),("马鞍山市",12),("芜湖市",12),("铜陵市",12),("安庆市",12),("黄山市",12),("六安市",12),("巢湖市",12),("池州市",12),("宣城市",12),("其他",12);

insert into tb_quxian(quxianname,cityid) values("福州市",13),("南平市",13),("莆田市",13),("三明市",13),("泉州市",13),("厦门市",13),("漳州市",13),("龙岩市",13),("宁德市",13),("其他",13);

insert into tb_quxian(quxianname,cityid) values("南昌市",14),("九江市",14),("景德镇市",14),("鹰潭市",14),("新余市",14),("萍乡市",14),("赣州市",14),("上饶市",14),("抚州市",14),("宜春市",14),("吉安市",14),("其他",14);

insert into tb_quxian(quxianname,cityid) values("济南市",15),("聊城市",15),("德州市",15),("东营市",15),("淄博市",15),("潍坊市",15),("烟台市",15),("威海市",15),("青岛市",15),("日照市",15),("临沂市",15),("枣庄市",15),("济宁市",15),("泰安市",15),("莱芜市",15),("滨州市",15),("菏泽市",15),("其他",15);

insert into tb_quxian(quxianname,cityid) values("郑州市",16),("三门峡市",16),("洛阳市",16),("焦作市",16),("新乡市",16),("鹤壁市",16),("安阳市",16),("濮阳市",16),("开封市",16),("商丘市",16),("许昌市",16),("漯河市",16),("平顶山市",16),("南阳市",16),("信阳市",16),("周口市",16),("驻马店市",16),("其他",16);

insert into tb_quxian(quxianname,cityid) values("武汉市",17),("十堰市",17),("襄樊市",17),("荆门市",17),("孝感市",17),("黄冈市",17),("鄂州市",17),("黄石市",17),("咸宁市",17),("荆州市",17),("宜昌市",17),("随州市",17),("恩施土家族苗族自治州",17),("仙桃市",17),("天门",17),("潜江市",17),("神农架林区",17),("其他",17);

insert into tb_quxian(quxianname,cityid) values("长沙市",18),("张家界市",18),("常德市",18),("益阳市",18),("岳阳市",18),("株洲市",18),("湘潭市",18),("衡阳市",18),("郴州市",18),("永州市",18),("邵阳市",18),("怀化市",18),("娄底市",18),("湘西土家族苗族自治州",18),("其他",18);

insert into tb_quxian(quxianname,cityid) values("广州市",19),("清远市市",19),("韶关市",19),("河源市",19),("梅州市",19),("潮州市",19),("汕头市",19),("揭阳市",19),("汕尾市",19),("惠州市",19),("东莞市",19),("深圳市",19),("珠海市",19),("中山市",19),("江门市",19),("佛山市",19),("肇庆市",19),("云浮市",19),("阳江市",19),("茂名市",19),("湛江市",19),("其他",19);

insert into tb_quxian(quxianname,cityid) values("南宁市",20),("桂林市",20),("柳州市",20),("梧州市",20),("贵港市",20),("玉林市",20),("钦州市",20),("北海市",20),("防城港市",20),("崇左市",20),("百色市",20),("河池市",20),("来宾市",20),("贺州市",20),("其他",20);

insert into tb_quxian(quxianname,cityid) values("海口市",21),("三亚市",21),("其他",21);

insert into tb_quxian(quxianname,cityid) values("渝中区",22),("大渡口区",22),("江北区",22),("沙坪坝区",22),("九龙坡区",22),("南岸区",22),("北碚区",22),("万盛区",22),("双桥区",22),("渝北区",22),("巴南区",22),("万州区",22),("涪陵区",22),("黔江区",22),("长寿区",22),("合川市",22),("永川市",22),("江津市",22),("南川市",22),("綦江县",22),("潼南县",22),("铜梁县",22),("大足县",22),("璧山县",22),("垫江县",22),("武隆县",22),("丰都县",22),("城口县",22),("开县",22),("巫溪县",22),("巫山县",22),("奉节县",22),("云阳县",22),("忠县",22),("石柱土家族自治县",22),("彭水苗族土家族自治县",22),("酉阳土家族苗族自治县",22),("秀山土家族苗族自治县",22),("其他",22);

insert into tb_quxian(quxianname,cityid) values("成都市",23),("广元市",23),("绵阳市",23),("德阳市",23),("南充市",23),("广安市",23),("遂宁市",23),("内江市",23),("乐山市",23),("自贡市",23),("泸州市",23),("宜宾市",23),("攀枝花市",23),("巴中市",23),("资阳市",23),("眉山市",23),("雅安",23),("阿坝藏族羌族自治州",23),("甘孜藏族自治州",23),("凉山彝族自治州",23),("其他",23);

insert into tb_quxian(quxianname,cityid) values("贵阳市",24),("六盘水市",24),("遵义市",24),("安顺市",24),("毕节地区",24),("铜仁地区",24),("黔东南苗族侗族自治州",24),("黔南布依族苗族自治州",24),("黔西南布依族苗族自治州",24),("其他",24);

insert into tb_quxian(quxianname,cityid) values("昆明市",25),("曲靖市",25),("玉溪市",25),("保山市",25),("昭通市",25),("丽江市",25),("普洱市",25),("临沧市",25),("宁德市",25),("德宏傣族景颇族自治州",25),("怒江傈僳族自治州",25),("楚雄彝族自治州",25),("红河哈尼族彝族自治州",25),("文山壮族苗族自治州",25),("大理白族自治州",25),("迪庆藏族自治州",25),("西双版纳傣族自治州",25),("其他",25);

insert into tb_quxian(quxianname,cityid) values("拉萨市",26),("那曲地区",26),("昌都地区",26),("林芝地区",26),("山南地区",26),("日喀则地区",26),("阿里地区",26),("其他",26);

insert into tb_quxian(quxianname,cityid) values("西安市",27),("延安市",27),("铜川市",27),("渭南市",27),("咸阳市",27),("宝鸡市",27),("汉中市",27),("安康市",27),("商洛市",27),("其他",27);

insert into tb_quxian(quxianname,cityid) values("兰州市",28),("嘉峪关市",28),("金昌市",28),("白银市",28),("天水市",28),("武威市",28),("酒泉市",28),("张掖市",28),("庆阳市",28),("平凉市",28),("定西市",28),("陇南市",28),("临夏回族自治州",28),("甘南藏族自治州",28),("其他",28);

insert into tb_quxian(quxianname,cityid) values("西宁市",29),("海东地区",29),("海北藏族自治州",29),("黄南藏族自治州",29),("玉树藏族自治州",29),("海南藏族自治州",29),("果洛藏族自治州",29),("海西蒙古族藏族自治州",29),("其他",29);

insert into tb_quxian(quxianname,cityid) values("银川市",30),("石嘴山市",30),("吴忠市",30),("固原市",30),("中卫市",30),("其他",30);

insert into tb_quxian(quxianname,cityid) values("乌鲁木齐市",31),("克拉玛依市",31),("喀什地区",31),("阿克苏地区",31),("和田地区",31),("吐鲁番地区",31),("哈密地区",31),("塔城地区",31),("阿勒泰地区",31),("克孜勒苏柯尔克孜自治州",31),("博尔塔拉蒙古自治州",31),("昌吉回族自治州 伊犁哈萨克自治州",31),("巴音郭楞蒙古自治州",31),("河子市",31),("阿拉尔市",31),("五家渠市",31),("图木舒克市",31),("其他",31);

insert into tb_quxian(quxianname,cityid) values("香港",32),("其他",32);

insert into tb_quxian(quxianname,cityid) values("澳门",33),("其他",33);

insert into tb_quxian(quxianname,cityid) values("台湾",34),("其他",34);