中国各省市名称数据库

--创建DBPromary数据库
create database DBPromary
--创建promary表
create table promary
(
proID int primary key,
proName varchar(50) not null
)
----------------------------------------------------------------------------------------------------------------------------------------
--中国34个省级行政单位23个省5个自治区4个直辖市2特别行政区
insert into promary values('01','北京市');
insert into promary values('02','天津市');
insert into promary values('03','上海市');
insert into promary values('04','重庆市');
insert into promary values('05','河北省');
insert into promary values('06','山西省');
insert into promary values('07','台湾省');
insert into promary values('08','辽宁省');
insert into promary values('09','吉林省');
insert into promary values('10','黑龙江省');
insert into promary values('11','江苏省');
insert into promary values('12','浙江省');
insert into promary values('13','安徽省');
insert into promary values('14','福建省');
insert into promary values('15','江西省');
insert into promary values('16','山东省');
insert into promary values('17','河南省');
insert into promary values('18','湖北省');
insert into promary values('19','湖南省');
insert into promary values('20','广东省');
insert into promary values('21','甘肃省');
insert into promary values('22','四川省');
insert into promary values('23','贵州省');
insert into promary values('24','海南省');
insert into promary values('25','云南省');
insert into promary values('26','青海省');
insert into promary values('27','陕西省');
insert into promary values('28','广西壮族自治区');
insert into promary values('29','西藏自治区');
insert into promary values('30','宁夏回族自治区');
insert into promary values('31','新疆维吾尔自治区')
insert into promary values('32','内蒙古自治区');
insert into promary values('33','澳门特别行政区');
insert into promary values('34','香港特别行政区');
----------------------------------------------------------------------------------------------------------------------------------------
--创建city表
create table city
(
cityID VARCHAR(2) not null,
cityName varchar(50) primary key,
proID V ARCHAR(2) foreign key references promary(proID)
)
----------------------------------------------------------------------------------------------------------------------------------------
insert into city values('01','北京','01');
insert into city values('01','上海','03');
insert into city values('01','天津','02');
insert into city values('01','重庆','04');
insert into city values('01','辽阳市','08');
insert into city values('02','大连市','08');
insert into city values('03','鞍山市','08');
insert into city values('04','抚顺市','08');
insert into city values('05','本溪市','08');
insert into city values('06','丹东市','08');
insert into city values('07','锦州市','08');
insert into city values('08','葫芦岛市','08');
insert into city values('09','营口市','08');
insert into city values('10','盘锦市','08');
insert into city values('11','阜新市','08');
insert into city values('12','辽阳市','08');
insert into city values('13','铁岭市','08');
insert into city values('14','朝阳市','08');
insert into city values('15','凌源市','08');
insert into city values('16','北票市','08');
insert into city values('01','长春市','09');
insert into city values('02','09市','09');
insert into city values('03','四平市','09');
insert into city values('04','辽源市','09');
insert into city values('05','通化市','09');
insert into city values('06','白山市','09');
insert into city values('07','延边朝鲜族自治州','09');
insert into city values('08','白城市','09');
insert into city values('01','哈尔滨市','10'); insert into city values('02','齐齐哈尔市','10'); insert into city values('03','鹤岗市','10'); insert into city values('04','双鸭山市','10'); insert into city values('05','鸡西市','10'); insert into city values('06','大庆市','10'); insert into city values('07','伊春市','10'); insert into city values('08','牡丹江市','10'); insert into city values('09','佳木斯市','10'); insert into city values('10','七台河市','10'); insert into city values('11','黑河市','10'); insert into city values('12','绥化市','10'); insert into city values('13','大兴安岭地区','10'); insert into city values('01','石家庄市','05'); insert into city values('02','唐山市','05'); insert into city values('03','秦皇岛市','05'); insert into city values('04','邯郸市','05'); insert into city values('05','邢台市','05'); insert into city values('06','保定市','05'); insert into city values('07','张家口市','05'); insert into city values('08','承德市','05'); insert into city values('09','廊坊市','05'); insert into city values('10','衡水市','05'); insert into city values('11','沧州市','05'); insert into city values('01','太原市','06'); insert into city values('02','大同市','06'); insert into city values('03','阳泉市','06'); insert into city values('04','长治市','06'); insert into city values('05','晋城市','06'); insert into city values('06','朔州市','06'); insert into city values('07','晋中市','06'); insert into city values('08','运城市','06'); insert into city values('09','忻州市','06'); insert into city values('10','临汾市','06'); insert into city values('11','吕梁地区','06'); insert into city values('01','郑州市','17'); insert into city values('02','开封市','17'); insert into city values('03','洛阳市','17'); insert into city values('04','平顶山市','17'); insert into city values('05','焦作市','17'); insert into city values('06','鹤壁市','17'); insert into city values('07','新乡市','17'); insert into city values('08','安阳市','17');
insert into city values('10','许昌市','17'); insert into city values('11','漯河市','17'); insert into city values('12','三门峡市','17'); insert into city values('13','南阳市','17'); insert into city values('14','商丘市','17'); insert into city values('15','信阳市','17'); insert into city values('16','周口市','17'); insert into city values('17','驻马店市','17'); insert into city values('18','济源市','17'); insert into city values('01','济南市','16'); insert into city values('02','青岛市','16'); insert into city values('03','淄博市','16'); insert into city values('04','枣庄市','16'); insert into city values('05','东营市','16'); insert into city values('06','潍坊市','16'); insert into city values('07','烟台市','16'); insert into city values('08','威海市','16'); insert into city values('09','济宁市','16'); insert into city values('10','泰安市','16'); insert into city values('11','日照市','16'); insert into city values('12','莱芜市','16'); insert into city values('13','临沂市','16'); insert into city values('14','德州市','16'); insert into city values('15','聊城市','16'); insert into city values('16','滨州市','16'); insert into city values('17','菏泽市','16'); insert into city values('01','南京市','11'); insert into city values('02','徐州市','11'); insert into city values('03','连云港市','11'); insert into city values('04','淮安市','11'); insert into city values('05','宿迁市','11'); insert into city values('06','盐城市','11'); insert into city values('07','扬州市','11'); insert into city values('08','泰州市','11'); insert into city values('09','南通市','11'); insert into city values('10','镇江市','11'); insert into city values('11','常州市','11'); insert into city values('12','无锡市','11'); insert into city values('13','苏州市','11'); insert into city values('01','银川市','30'); insert into city values('02','石嘴山市','30'); insert into city values('03','吴忠市','30'); insert into city values('04','固原市','30');
insert into city values('02','柳州市','28');
insert into city values('03','桂林市','28');
insert into city values('04','梧州市','28');
insert into city values('05','北海市','28');
insert into city values('06','防城港市','28');
insert into city values('07','钦州市','28');
insert into city values('08','贵港市','28');
insert into city values('09','玉林市','28');
insert into city values('10','百色市','28');
insert into city values('11','贺州市','28');
insert into city values('12','河池市','28');
insert into city values('13','来宾市','28');
insert into city values('14','崇左市','28');
insert into city values('01','乌鲁木齐市','31');
insert into city values('02','克拉玛依市','31');
insert into city values('03','吐鲁番地区','31');
insert into city values('04','哈密地区','31');
insert into city values('05','和田地区','31');
insert into city values('06','阿克苏地区','31');
insert into city values('07','喀什地区','31');
insert into city values('08','克孜勒苏柯尔克孜自治州','31'); insert into city values('09','巴音郭楞州','31');
insert into city values('10','昌吉州','31');
insert into city values('11','博尔塔拉州','31');
insert into city values('12','伊犁哈萨克自治州','31'); insert into city values('13','塔城地区','31');
insert into city values('14','阿勒泰州','31');
insert into city values('15','石河子市','31');
insert into city values('16','阿拉尔市','31');
insert into city values('17','图木舒克市','31');
insert into city values('18','五家渠市','31');
insert into city values('01','拉萨市','29');
insert into city values('02','昌都地区','29');
insert into city values('03','山南地区','29');
insert into city values('04','日喀则地区','29');
insert into city values('05','阿里地区','29');
insert into city values('06','林芝地区','29');
insert into city values('01','香港','34');
insert into city values('01','澳门','33');
insert into city values('01','基隆市','07');
insert into city values('02','新竹市','07');
insert into city values('03','台中市','07');
insert into city values('04','嘉义市','07');
insert into city values('06','宜兰县','07'); insert into city values('07','台北县','07'); insert into city values('08','桃园县','07'); insert into city values('09','新竹县','07'); insert into city values('10','苗栗县','07'); insert into city values('11','台中县','07'); insert into city values('12','南投县','07'); insert into city values('13','彰化县','07'); insert into city values('14','云林县','07'); insert into city values('15','嘉义县','07'); insert into city values('16','台南县','07'); insert into city values('17','高雄县','07'); insert into city values('18','屏东县','07'); insert into city values('19','台东县','07'); insert into city values('20','花莲县','07'); insert into city values('21','澎湖县','07'); insert into city values('01','南昌市','15'); insert into city values('02','萍乡市','15'); insert into city values('03','九江市','15'); insert into city values('04','新余市','15'); insert into city values('05','鹰潭市','15'); insert into city values('06','赣州市','15'); insert into city values('07','吉安市','15'); insert into city values('08','宜春市','15'); insert into city values('09','抚州市','15'); insert into city values('10','上饶市','15'); insert into city values('01','合肥市','13'); insert into city values('02','芜湖市','13'); insert into city values('03','蚌埠市','13'); insert into city values('04','淮南市','13'); insert into city values('05','马鞍山市','13'); insert into city values('06','淮北市','13'); insert into city values('07','铜陵市','13'); insert into city values('08','安庆市','13'); insert into city values('09','黄山市','13'); insert into city values('10','滁州市','13'); insert into city values('11','阜阳市','13'); insert into city values('12','宿州市','13'); insert into city values('13','巢湖市','13'); insert into city values('14','六安市','13'); insert into city values('15','亳州市','13'); insert into city values('16','池州市','13'); insert into city values('17','宣城市','13');
insert into city values('02','厦门市','14'); insert into city values('03','三明市','14'); insert into city values('04','莆田市','14'); insert into city values('05','泉州市','14'); insert into city values('06','漳州市','14'); insert into city values('07','南平市','14'); insert into city values('08','龙岩市','14'); insert into city values('09','宁德市','14'); insert into city values('01','杭州市','12'); insert into city values('02','宁波市','12'); insert into city values('03','温州市','12'); insert into city values('04','嘉兴市','12'); insert into city values('05','绍兴市','12'); insert into city values('06','金华市','12'); insert into city values('07','衢州市','12'); insert into city values('08','舟山市','12'); insert into city values('09','台州市','12'); insert into city values('10','丽水市','12'); insert into city values('01','海口市','24'); insert into city values('02','三亚市','24'); insert into city values('01','广州市','20'); insert into city values('02','深圳市','20'); insert into city values('03','珠海市','20'); insert into city values('04','汕头市','20'); insert into city values('05','韶关市','20'); insert into city values('06','惠州市','20'); insert into city values('07','河源市','20'); insert into city values('08','梅州市','20'); insert into city values('09','汕尾市','20'); insert into city values('10','东莞市','20'); insert into city values('11','中山市','20'); insert into city values('12','江门市','20'); insert into city values('13','佛山市','20'); insert into city values('14','阳江市','20'); insert into city values('15','湛江市','20'); insert into city values('16','茂名市','20'); insert into city values('17','肇庆市','20'); insert into city values('18','清远市','20'); insert into city values('19','潮州市','20'); insert into city values('20','揭阳市','20'); insert into city values('21','云浮市','20'); insert into city values('01','贵阳市','23'); insert into city values('02','六盘水市','23');
insert into city values('04','安顺市','23');
insert into city values('05','铜仁地区','23');
insert into city values('06','毕节地区','23');
insert into city values('07','黔西南布依族苗族自治州','23'); insert into city values('08','黔东南南苗族侗族自治州','23'); insert into city values('09','黔南布依族苗族自治州','23'); insert into city values('01','成都市','22');
insert into city values('02','自贡市','22');
insert into city values('03','攀枝花市','22');
insert into city values('04','泸州市','22');
insert into city values('05','德阳市','22');
insert into city values('06','绵阳市','22');
insert into city values('07','广元市','22');
insert into city values('08','遂宁市','22');
insert into city values('09','内江市','22');
insert into city values('10','乐山市','22');
insert into city values('11','南充市','22');
insert into city values('12','宜宾市','22');
insert into city values('13','广安市','22');
insert into city values('14','达州市','22');
insert into city values('15','眉山市','22');
insert into city values('16','雅安市','22');
insert into city values('17','巴中市','22');
insert into city values('18','资阳市','22');
insert into city values('19','阿坝藏族羌族自治州','22'); insert into city values('20','甘孜藏族自治州','22');
insert into city values('21','凉山彝族自治州','22');
insert into city values('01','丽江地区','25');
insert into city values('02','文山壮族苗族自治州','25'); insert into city values('03','红河哈尼族彝族自治州','25'); insert into city values('04','西双版纳傣族自治州','25'); insert into city values('05','楚雄彝族自治州','25');
insert into city values('06','大理白族自治州','25');
insert into city values('07','德宏傣族景颇族自治州','25'); insert into city values('08','怒江傈僳族自治州','25'); insert into city values('09','迪庆藏族自治州','25');
insert into city values('10','昆明市','25');
insert into city values('11','玉溪市','25');
insert into city values('12','保山市','25');
insert into city values('13','昭通市','25');
insert into city values('14','思茅地区','25');
insert into city values('15','临沧地区','25');
insert into city values('01','西安市','27');
insert into city values('03','宝鸡市','27');
insert into city values('04','咸阳市','27');
insert into city values('05','渭南市','27');
insert into city values('06','延安市','27');
insert into city values('07','汉中市','27');
insert into city values('08','榆林市','27');
insert into city values('09','安康市','27');
insert into city values('10','商洛市','27');
insert into city values('01','荆门市','18');
insert into city values('02','鄂州市','18');
insert into city values('03','孝感市','18');
insert into city values('04','黄冈市','18');
insert into city values('05','咸宁市','18');
insert into city values('06','随州市','18');
insert into city values('07','恩施土家族苗族自治州','18'); insert into city values('08','仙桃市','18');
insert into city values('09','天门市','18');
insert into city values('10','潜江市','18');
insert into city values('11','神农架林区','18');
insert into city values('12','武汉市','18');
insert into city values('13','黄石市','18');
insert into city values('14','襄樊市','18');
insert into city values('15','十堰市','18');
insert into city values('16','荆州市','18');
insert into city values('17','宜昌市','18');
insert into city values('01','长沙市','19');
insert into city values('02','株洲市','19');
insert into city values('03','湘潭市','19');
insert into city values('04','衡阳市','19');
insert into city values('05','邵阳市','19');
insert into city values('06','岳阳市','19');
insert into city values('07','常德市','19');
insert into city values('08','张家界市','19');
insert into city values('09','益阳市','19');
insert into city values('10','郴州市','19');
insert into city values('11','永州市','19');
insert into city values('12','怀化市','19');
insert into city values('13','娄底市','19');
insert into city values('14','湘西土家族苗族自治州','19'); insert into city values('01','西宁市','26');
insert into city values('02','海东地区','26');
insert into city values('03','海北藏族自治州','26'); insert into city values('04','黄南藏族自治州','26');
insert into city values('05','海南藏族自治州','26'); insert into city values('06','洛藏族自治州','26');
insert into city values('07','玉树藏族自治州','26'); insert into city values('08','海西蒙古族藏族自治州','26'); insert into city values('01','兰州市','21');
insert into city values('02','金昌市','21');
insert into city values('03','白银市','21');
insert into city values('04','天水市','21');
insert into city values('05','嘉峪关市','21');
insert into city values('06','武威市','21');
insert into city values('07','张掖市','21');
insert into city values('08','平凉市','21');
insert into city values('09','酒泉市','21');
insert into city values('10','庆阳市','21');
insert into city values('11','定西地区','21');
insert into city values('12','陇南地区','21');
insert into city values('13','甘南藏族自治州','21'); insert into city values('14','临夏回族自治州','21'); insert into city values('01','呼和浩特市','32');
insert into city values('02','包头市','32');
insert into city values('03','乌海市','32');
insert into city values('04','赤峰市','32');
insert into city values('05','鄂尔多斯市','32');
insert into city values('06','呼伦贝尔市','32');
insert into city values('07','乌兰察布盟','32');
insert into city values('08','锡林郭勒盟','32');
insert into city values('09','巴彦淖尔盟','32');
insert into city values('10','阿拉善盟','32');
insert into city values('11','兴安盟','32');。