省市数据库源码

----------------------------------------------------------------------------------------------------------------------------------------
--创建province表
go
create table province
(
proID int primary key,
proName varchar(50) not null
)
insert into province values(1,'北京')
insert into province values(2,'天津')
insert into province values(3,'河北')
insert into province values(4,'山西')
insert into province values(5,'内蒙古')
insert into province values(6,'辽宁')
insert into province values(7,'吉林')
insert into province values(8,'黑龙江')
insert into province values(9,'上海')
insert into province values(10,'江苏')
insert into province values(11,'浙江')
insert into province values(12,'安徽')
insert into province values(13,'福建')
insert into province values(14,'江西')
insert into province values(15,'山东')
insert into province values(16,'河南')
insert into province values(17,'湖北')
insert into province values(18,'湖南')
insert into province values(19,'广东')
insert into province values(20,'广西')
insert into province values(21,'海南')
insert into province values(22,'重庆')
insert into province values(23,'四川')
insert into province values(24,'贵州')
insert into province values(25,'云南')
insert into province values(26,'西藏')
insert into province values(27,'陕西')
insert into province values(28,'甘肃')
insert into province values(29,'青海')
insert into province values(30,'宁夏')
insert into province values(31,'新疆')
insert into province values(32,'香港')
insert into province values(33,'澳门')
insert into province values(34,'台湾')
----------------------------------------------------------------------------------------------------------------------------------------

--创建city表
go
create table city
(
cityID int not null,
cityName varchar(50) primary key,
proID int foreign key references province(proID)
)

----------------------------------------------------------------------------------------------------------------------------------------
--插入各个省的城市数据
--北京
insert into city values(1,'东城',1)
insert into city values(2,'西城',1)
insert into city values(3,'崇文',1)
insert into city values(4,'宣武',1)
insert into city values(5,'朝阳',1)
insert into city values(6,'丰台',1)
insert into city values(7,'石景山',1)
insert into city values(8,'海淀',1)
insert into city values(9,'门头沟',1)
insert into city values(10,'房山',1)
insert into city values(11,'通州',1)
insert into city values(12,'顺义',1)
insert into city values(13,'昌平',1)
insert into city values(14,'大兴',1)
insert into city values(15,'怀柔',1)
insert into city values(16,'平谷',1)
insert into city values(17,'密云',1)
insert into city values(18,'延庆',1)
--天津
insert into city values(1,'和平',2)
insert into city values(2,'河东',2)
insert into city values(3,'河西',2)
insert into city values(4,'南开',2)
insert into city values(5,'河北',2)
insert into city values(6,'红桥',2)
insert into city values(7,'塘沽',2)
insert into city values(8,'汉沽',2)
insert into city values(9,'大港',2)
insert into city values(10,'东丽',2)
insert into city values(11,'西青',2)
insert into city values(12,'津南',2)
insert into city values(13,'北辰',2)
insert into city values(14,'武清',2)
insert into city values(15,'宝坻',2)
insert into city values(16,'宁河',2)
insert into city values(17,'静海',2)
insert into city values(18,'蓟县',2)
--河北
insert into city values(1,'石家庄',3)
insert into city values(2,'唐山',3)
insert into city values(3,'秦皇岛',3)
insert into city values(4,'邯郸',3)
insert into city values(5,'邢台',3)
insert into city values(6,'保定',3)
insert into city values(7,'张家口',3)
insert into city values(8,'承德',3)
insert into city values(9,'沧州',3)
insert into city values(10,'廊坊',3)
insert into city values(11,'衡水',3)
--山西
insert into city values(1,'太原',4)
insert into city values(2,'太同',4)
insert into city values(3,'阳泉',4)
insert into city values(4,'长治',4)
insert into city values(5,'晋城',4)
insert into city values(6,'朔州',4)
insert into city values(7,'晋中',4)
insert into city values(8,'运城',4)
insert into city values(9,'忻州',4)
insert into city values(10,'临汾',4)
insert into city values(11,'吕梁',4)
--内蒙古
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)
insert into city values(12,'阿拉善',5)
--辽宁
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)
insert into city values(12,'铁岭',6)
insert into city values(13,'朝阳市',6)
insert into city values(14,'葫芦岛',6)
--吉林
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(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(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)
insert into city values(10,'闵行',9)
insert into city values(11,'宝山区',9)
insert into city values(12,'嘉定',9)
insert into city values(13,'浦东新',9)
insert into city values(14,'金山',9)
insert into city values(15,'松江',9)
insert into city values(16,'南汇',9)
insert into city values(17,'奉贤',9)
insert into city values(18,'青浦',9)
insert into city values(19,'崇明',9)
--江苏
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)
--浙江
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(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)
insert into city values(12,'宿州',12)
insert into city values(13,'巢湖',12)
insert into city values(14,'六安',12)
insert into city values(15,'毫州',12)
insert into city values(16,'池州',12)
insert into city values(17,'宣城',12)
--福建
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(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)
insert into city values(10,'抚州',14)
insert into city values(11,'上饶',14)
--山东
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)
insert into city values(12,'莱芜',15)
insert into city values(13,'临忻',15)
insert into city values(14,'德州',15)
insert into city values(15,'聊城',15)
insert into city values(16,'滨州',15)
insert into city values(17,'菏泽',15)
--河南
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)
insert into city values(18,'济源',16)
--湖北
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(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(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)
insert into city values(15,'阳江',19)
insert into city values(16,'清远',19)
insert into city values(17,'东莞',19)
insert into city values(18,'中山',19)
insert into city values(19,'潮州',19)
insert into city values(20,'揭阳',19)
insert into city values(21,'云浮',19)
--广西
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(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)
insert into city values(15,'乐东',21)
insert into city values(16,'陵水',21)
insert into city values(17,'保亭',21)
insert into city values(18,'琼中',21)
insert into city values(19,'西沙',21)
insert into city values(20,'南沙',21)
insert into city values(21,'中沙',21)
--重庆
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)
insert into city values(22,'铜梁',22)
insert into city values(23,'大足',22)
insert into city values(24,'荣昌',22)
insert into city values(25,'璧山',22)
insert into city values(26,'垫江',22)
insert into city values(27,'武隆',22)
insert into city values(28,'丰都',22)
insert into city values(29,'城口',22)
insert into city values(30,'梁平',22)
--四川
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)
insert into city values(10,'乐山',23)
insert into city values(11,'南充',23)
insert into city values(12,'眉山',23)
insert into city values(13,'宜宾',23)
insert into city values(14,'广安',23)
insert into city values(15,'达州',23)
insert into city values(16,'雅安',23)
insert into city values(17,'巴中',23)
insert into city values(18,'资阳',23)
insert into city values(19,'阿坝',23)
insert into city values(20,'甘孜',23)
insert into city values(21,'凉山',23)
--贵州
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(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)
--西藏
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(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)
--甘肃
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)
--青海
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)
--宁夏
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)
--新疆
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(1,'香港',32)
--澳门
insert into city values(1,'澳门',33)
--台湾
insert into city values(1,'台北市',34)
insert into city values(2,'高雄市',34)
insert into city values(3,'基隆市',34)
insert into city values(4,'台中市',34)
insert into city values(5,'台南市',34)
insert into city values(6,'新竹市',34)
insert into city values(7,'嘉义市',34)
insert into city values(8,'台北县',34)
insert into city values(9,'宜兰县',34)
insert into city values(10,'桃园县',34)
insert into city values(11,'新竹县',34)
insert into city values(12,'苗栗县',34)
insert into city values(13,'台中县',34)
insert into city values(14,'彰化县',34)
insert into city values(15,'南投县',34)
insert into city values(16,'云林县',34)
insert into city values(17,'嘉义县',34)
insert into city values(18,'台南县',34)
insert into city values(19,'高雄县',34)
insert into city values(10,'屏东县',34)
insert into city values(21,'台东县',34)
insert into city values(22,'花莲县',34)
insert into city values(23,'澎湖县',34)