全国省市数据

/*
--创建测试数据 
CREATE DATABASE Test
GO
---
DROP TABLE City
DROP TABLE Province
*/

--USE Test
--GO

--创建Province
CREATE TABLE Province
(
 ProID INT IDENTITY(1,1) PRIMARY KEY,
 Area  VARCHAR(20) not null,
 ProvinceName VARCHAR(50) not null
)

----------------------------------------------------------------------------------------------------------------------------------------
--中国34个省级行政单 23个省 5个自治区 4个直辖市 2特别行政
insert into Province values('华北','北京')
insert into Province values('华北','天津')
insert into Province values('华东','上海')
insert into Province values('西南','重庆')
insert into Province values('华北','河北')
insert into Province values('华北','山西')
insert into Province values('港澳','台湾')
insert into Province values('东北','辽宁')
insert into Province values('东北','吉林')
insert into Province values('东北','黑龙江省')
insert into Province values('华东','江苏')
insert into Province values('华东','浙江')
insert into Province values('华东','安徽')
insert into Province values('华东','福建')
insert into Province values('华东','江西')
insert into Province values('华东','山东')
insert into Province values('华中','河南')
insert into Province values('华中','湖北')
insert into Province values('华中','湖南')
insert into Province values('华南','广东')
insert into Province values('西北','甘肃')
insert into Province values('西南','四川')
insert into Province values('西南','贵州')
insert into Province values('华南','海南')
insert into Province values('西南','云南')
insert into Province values('西北','青海')
insert into Province values('西北','陕西')
insert into Province values('华南','广西壮族自治')
insert into Province values('西南','西藏自治')
insert into Province values('西北','宁夏回族自治')
insert into Province values('西北','新疆维吾尔自治区')
insert into Province values('华北','内蒙古自治区')
insert into Province values('港澳','澳门特别行政')
insert into Province values('港澳','香港特别行政')
----------------------------------------------------------------------------------------------------------------------------------------

--创建City
CREATE TABLE City
(
 CID INT IDENTITY(1,1) PRIMARY KEY,
 CityID INT NOT NULL,
 CityName VARCHAR(50), 
 ParentID INT 
)

----------------------------------------------------------------------------------------------------------------------------------------
--插入各个省的城市数据
--4个直辖市
insert into City values(1,'北京',1)
insert into City values(1,'天津',2)
insert into City values(1,'上海',3)
insert into City values(1,'重庆',4)
--5河北(2005年辖11个地级市36个市辖区22个县级市108个县6个自治县)
insert into City values(1,'石家庄市',5)
insert into City values(2,'唐山',5)
insert into City values(3,'秦皇岛市',5)
insert into City values(4,'邯郸',5)
insert into City values(5,'邢台',5)
insert into City values(6,'保定',5)
insert into City values(7,'张家口市',5)
insert into City values(8,'承德',5)
insert into City values(9,'沧州',5)
insert into City values(10,'廊坊',5)
insert into City values(11,'衡水',5)
--6山西11个城
insert into City values(1,'太原',6)
insert into City values(2,'大同',6)
insert into City values(3,'阳泉',6)
insert into City values(4,'长治',6)
insert into City values(5,'晋城',6)
insert into City values(6,'朔州',6)
insert into City values(7,'晋中',6)
insert into City values(8,'运城',6)
insert into City values(9,'忻州',6)
insert into City values(10,'临汾',6)
insert into City values(11,'吕梁',6)
--7台湾(台湾本岛和澎湖共7市16县，其中台北市和高雄市为“院辖市”，直属“行政院”，其余属台湾省；市下设区，县下设市（县辖市）、镇、乡，合称区市镇乡)
insert into City values(1,'台北',7)
insert into City values(2,'高雄',7)
insert into City values(3,'基隆',7)
insert into City values(4,'台中',7)
insert into City values(5,'台南',7)
insert into City values(6,'新竹',7)
insert into City values(7,'嘉义',7)
insert into City values(8,'台北',7)
insert into City values(9,'宜兰',7)
insert into City values(10,'桃园',7)
insert into City values(11,'新竹',7)
insert into City values(12,'苗栗',7)
insert into City values(13,'台中',7)
insert into City values(14,'彰化',7)
insert into City values(15,'南投',7)
insert into City values(16,'云林',7)
insert into City values(17,'嘉义',7)
insert into City values(18,'台南',7)
insert into City values(19,'高雄',7)
insert into City values(20,'屏东',7)
insert into City values(21,'澎湖',7)
insert into City values(22,'台东',7)
insert into City values(23,'花莲',7)
--8辽宁(2006年，辖：14个地级市56个市辖区17个县级市19个县8个自治县)
insert into City values(1,'沈阳',8)
insert into City values(2,'大连',8)
insert into City values(3,'鞍山',8)
insert into City values(4,'抚顺',8)
insert into City values(5,'本溪',8)
insert into City values(6,'丹东',8)
insert into City values(7,'锦州',8)
insert into City values(8,'营口',8)
insert into City values(9,'阜新',8)
insert into City values(10,'辽阳',8)
insert into City values(11,'盘锦',8)
insert into City values(12,'铁岭',8)
insert into City values(13,'朝阳',8)
insert into City values(14,'葫芦岛市',8)
--9吉林(2006年，辖：8个地级市1个自治州20个市辖区20个县级市17个县3个自治县)
insert into City values(1,'长春',9)
insert into City values(2,'吉林',9)
insert into City values(3,'四平',9)
insert into City values(4,'辽源',9)
insert into City values(5,'通化',9)
insert into City values(6,'白山',9)
insert into City values(7,'松原',9)
insert into City values(8,'白城',9)
insert into City values(9,'延边朝鲜族自治州',9)
--10黑龙江省(2006年，辖：12地级市1地区64市辖区18县级市45县1自治)
insert into City values(1,'哈尔滨市',10)
insert into City values(2,'齐齐哈尔',10)
insert into City values(3,'  ',10)
insert into City values(4,'双鸭山市',10)
insert into City values(5,'  ',10)
insert into City values(6,'  ',10)
insert into City values(7,'  ',10)
insert into City values(8,'牡丹江市',10)
insert into City values(9,'佳木斯市',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)
--11江苏(2005年辖13个地级市54个市辖区27个县级市25个县)
insert into City values(1,'南京',11)
insert into City values(2,'无锡',11)
insert into City values(3,'徐州',11)
insert into City values(4,'常州',11)
insert into City values(5,'苏州',11)
insert into City values(6,'南通市',11)
insert into City values(7,'连云港市',11)
insert into City values(8,'淮安',11)
insert into City values(9,'盐城',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)
--12浙江(2006年，辖：11个地级市32个市辖区22个县级市35个县1个自治县)
insert into City values(1,'杭州',12)
insert into City values(2,'宁波',12)
insert into City values(3,'温州',12)
insert into City values(4,'嘉兴',12)
insert into City values(5,'湖州',12)
insert into City values(6,'绍兴',12)
insert into City values(7,'金华',12)
insert into City values(8,'衢州',12)
insert into City values(9,'舟山',12)
insert into City values(10,'台州',12)
insert into City values(11,'丽水',12)
--13安徽(2005年辖17个地级市44个市辖区5县个级市56个县)
insert into City values(1,'合肥',13)
insert into City values(2,'芜湖',13)
insert into City values(3,'蚌埠',13)
insert into City values(4,'淮南',13)
insert into City values(5,'马鞍山市',13)
insert into City values(6,'淮北',13)
insert into City values(7,'铜陵',13)
insert into City values(8,'安庆',13)
insert into City values(9,'黄山',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)
--14福建(2006年辖9个地级市26个市辖区14个县级市45个县)
insert into City values(1,'福州',14)
insert into City values(2,'厦门',14)
insert into City values(3,'莆田',14)
insert into City values(4,'三明',14)
insert into City values(5,'泉州',14)
insert into City values(6,'漳州',14)
insert into City values(7,'南平',14)
insert into City values(8,'龙岩',14)
insert into City values(9,'宁德',14)
--15江西(2006年全省辖11个地级市19个市辖区10个县级市70个县)
insert into City values(1,'南昌',15)
insert into City values(2,'景德镇市',15)
insert into City values(3,'萍乡',15)
insert into City values(4,'九江',15)
insert into City values(5,'新余',15)
insert into City values(6,'鹰潭',15)
insert into City values(7,'赣州',15)
insert into City values(8,'吉安',15)
insert into City values(9,'宜春',15)
insert into City values(10,'抚州',15)
insert into City values(11,'上饶',15)
--16山东(2005年，辖：17个地级市49个市辖区31个县级市60个县)
insert into City values(1,'济南',16)
insert into City values(2,'青岛',16)
insert into City values(3,'淄博',16)
insert into City values(4,'枣庄',16)
insert into City values(5,'东营',16)
insert into City values(6,'烟台',16)
insert into City values(7,'潍坊',16)
insert into City values(8,'济宁',16)
insert into City values(9,'泰安',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)
--17河南省（2005年辖17个地级市50个市辖区21个县级市88个县。）
insert into City values(1,'郑州',17)
insert into City values(2,'开封市',17)
insert into City values(3,'洛阳',17)
insert into City values(4,'平顶山市',17)
insert into City values(5,'安阳',17)
insert into City values(6,'鹤壁',17)
insert into City values(7,'新乡',17)
insert into City values(8,'焦作',17)
insert into City values(9,'濮阳',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)
--18湖北省（截至20051231日，全省13个地级单位（12个地级市1个自治州）；102县级单位38个市辖区24个县级市37个县2个自治县1个林区），共1220个乡级单位（277个街道733个镇210个乡）。）
insert into City values(1,'武汉',18)
insert into City values(2,'黄石',18)
insert into City values(3,'十堰',18)
insert into City values(4,'荆州',18)
insert into City values(5,'宜昌',18)
insert into City values(6,'襄樊',18)
insert into City values(7,'鄂州',18)
insert into City values(8,'荆门',18)
insert into City values(9,'孝感',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)
--19湖南省（2005年辖13个地级市1个自治州34个市辖区16个县级市65个县7个自治县。）
insert into City values(1,'长沙',19)
insert into City values(2,'株洲',19)
insert into City values(3,'湘潭',19)
insert into City values(4,'衡阳',19)
insert into City values(5,'邵阳',19)
insert into City values(6,'岳阳',19)
insert into City values(7,'常德',19)
insert into City values(8,'张家界市',19)
insert into City values(9,'益阳',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)
--20广东省（截至20051231日，广东省辖21个地级市54个市辖区23个县级市41个县3个自治县429个街道办事处1145个镇4个乡7个民族乡。）
insert into City values(1,'广州',20)
insert into City values(2,'深圳',20)
insert into City values(3,'珠海',20)
insert into City values(4,'汕头',20)
insert into City values(5,'韶关',20)
insert into City values(6,'佛山',20)
insert into City values(7,'江门',20)
insert into City values(8,'湛江',20)
insert into City values(9,'茂名',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)
--21甘肃省（2006年辖12个地级市2个自治州17个市辖区4个县级市58个县7个自治县。）
insert into City values(1,'兰州',21)
insert into City values(2,'金昌',21)
insert into City values(3,'白银',21)
insert into City values(4,'天水',21)
insert into City values(5,'嘉峪关市',21)
insert into City values(6,'武威',21)
insert into City values(7,'张掖',21)
insert into City values(8,'平凉',21)
insert into City values(9,'酒泉',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)
--22四川省（2006年辖18个地级市3个自治州43个市辖区14个县级市120个县4个自治县。）
insert into City values(1,'成都',22)
insert into City values(2,'自贡',22)
insert into City values(3,'攀枝花',22)
insert into City values(4,'泸州',22)
insert into City values(5,'德阳',22)
insert into City values(6,'绵阳',22)
insert into City values(7,'广元',22)
insert into City values(8,'遂宁',22)
insert into City values(9,'内江',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)

--23贵州(2006年辖4个地级市2个地区3个自治州10个市辖区9个县级市56个县11个自治县2个特区)
insert into City values(1,'贵阳',23)
insert into City values(2,'六盘水市',23)
insert into City values(3,'遵义',23)
insert into City values(4,'安顺',23)
insert into City values(5,'铜仁地区',23)
insert into City values(6,'毕节地区',23)
insert into City values(7,'黔西南布依族苗族自治',23)
insert into City values(8,'黔东南苗族侗族自治州',23)
insert into City values(9,'黔南布依族苗族自治州',23)
--24海南(20032005年　全省2个地级市6个县级市4个县6个民族自治县4个市辖区1个办事处（西南中沙群岛办事处，县级）)
insert into City values(1,'海口',24)
insert into City values(2,'三亚',24)
insert into City values(3,'五指山市',24)
insert into City values(4,'琼海',24)
insert into City values(5,'儋州',24)
insert into City values(6,'文昌',24)
insert into City values(7,'万宁',24)
insert into City values(8,'东方',24)
insert into City values(9,'澄迈',24)
insert into City values(10,'定安',24)
insert into City values(11,'屯昌',24)
insert into City values(12,'临高',24)
insert into City values(13,'白沙黎族自治',24)
insert into City values(14,'昌江黎族自治',24)
insert into City values(15,'乐东黎族自治',24)
insert into City values(16,'陵水黎族自治',24)
insert into City values(17,'保亭黎族苗族自治',24)
insert into City values(18,'琼中黎族苗族自治',24)
--25云南(2006年辖8个地级市8个自治州12个市辖区9个县级市79个县29个自治县)
insert into City values(1,'昆明',25)
insert into City values(2,'曲靖',25)
insert into City values(3,'玉溪',25)
insert into City values(4,'保山',25)
insert into City values(5,'昭通市',25)
insert into City values(6,'丽江',25)
insert into City values(7,'思茅',25)
insert into City values(8,'临沧',25)
insert into City values(9,'文山壮族苗族自治',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(16,'迪庆藏族自治',25)
--26青海(2006年辖1个地级市1个地区6个自治州4个市辖区2个县级市30个县7个自治县)
insert into City values(1,'西宁',26)
insert into City values(2,'海东地区',26)
insert into City values(3,'海北藏族自治',26)
insert into City values(4,'黄南藏族自治',26)
insert into City values(5,'海南藏族自治',26)
insert into City values(6,'果洛藏族自治',26)
insert into City values(7,'玉树藏族自治',26)
insert into City values(8,'海西蒙古族藏族自治州',26)
--27陕西(2006年辖10个地级市24个市辖区3个县级市80个县)
insert into City values(1,'西安',27)
insert into City values(2,'铜川',27)
insert into City values(3,'宝鸡',27)
insert into City values(4,'咸阳',27)
insert into City values(5,'渭南',27)
insert into City values(6,'延安',27)
insert into City values(7,'汉中',27)
insert into City values(8,'榆林',27)
insert into City values(9,'安康',27)
insert into City values(10,'商洛',27)
--28广西壮族自治(2005年辖14个地级市34个市辖区7个县级市56个县12个自治县)
insert into City values(1,'南宁',28)
insert into City values(2,'柳州',28)
insert into City values(3,'桂林',28)
insert into City values(4,'梧州',28)
insert into City values(5,'北海',28)
insert into City values(6,'防城港市',28)
insert into City values(7,'钦州',28)
insert into City values(8,'贵港',28)
insert into City values(9,'玉林',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)
--29西藏自治(2005年辖1个地级市6个地区；1个市辖区1个县级市71个县)
insert into City values(1,'拉萨',29)
insert into City values(2,'那曲地区',29)
insert into City values(3,'昌都地区',29)
insert into City values(4,'山南地区',29)
insert into City values(5,'日喀则地',29)
insert into City values(6,'阿里地区',29)
insert into City values(7,'林芝地区',29)
--30宁夏回族自治
insert into City values(1,'银川',30)
insert into City values(2,'石嘴山市',30)
insert into City values(3,'吴忠',30)
insert into City values(4,'固原',30)
insert into City values(5,'中卫',30)
--31新疆维吾尔自治区(2005年辖2个地级市7个地区5个自治州11个市辖区20个县级市62个县6个自治县)
insert into City values(1,'乌鲁木齐',31)
insert into City values(2,'克拉玛依',31)
insert into City values(3,'石河子市　',31)
insert into City values(4,'阿拉尔市',31)
insert into City values(5,'图木舒克',31)
insert into City values(6,'五家渠市',31)
insert into City values(7,'吐鲁番市',31)
insert into City values(8,'阿克苏市',31)
insert into City values(9,'喀什',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(19,'奎屯',31)
insert into City values(20,'塔城',31)
insert into City values(21,'乌苏',31)
insert into City values(22,'阿勒泰市',31)
--32内蒙古自治区(2006年，辖：9个地级市3个盟21个市辖区11个县级市17个县49个旗3个自治旗)
insert into City values(1,'呼和浩特',32)
insert into City values(2,'包头',32)
insert into City values(3,'乌海',32)
insert into City values(4,'赤峰',32)
insert into City values(5,'通辽',32)
insert into City values(6,'鄂尔多斯',32)
insert into City values(7,'呼伦贝尔',32)
insert into City values(8,'巴彦淖尔',32)
insert into City values(9,'乌兰察布',32)
insert into City values(10,'锡林郭勒',32)
insert into City values(11,'兴安',32)
insert into City values(12,'阿拉善盟',32)
--33澳门特别行政
insert into City values(1,'澳门特别行政',33)
--34香港特别行政
insert into City values(1,'香港特别行政',34)

📱 扫码关注公众号

扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满