--省表
create table province(
iAutoID int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
sName varchar(32)
)
--市表
create table city(
iAutoID int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
sName varchar(32),
iProvinceId int
)
--省
insert into province(sName) values('安徽');
insert into province(sName) values('北京');
insert into province(sName) values('福建');
insert into province(sName) values('甘肅');
insert into province(sName) values('廣東');
insert into province(sName) values('廣西');
insert into province(sName) values('貴州');
insert into province(sName) values('海南');
insert into province(sName) values('河北');
insert into province(sName) values('河南');
insert into province(sName) values('黑龍江');
insert into province(sName) values('湖北');
insert into province(sName) values('湖南');
insert into province(sName) values('吉林');
insert into province(sName) values('江蘇');
insert into province(sName) values('江西');
insert into province(sName) values('遼寧');
insert into province(sName) values('內蒙古');
insert into province(sName) values('寧夏');
insert into province(sName) values('青海');
insert into province(sName) values('山東');
insert into province(sName) values('山西');
insert into province(sName) values('陜西');
insert into province(sName) values('上海');
insert into province(sName) values('四川');
insert into province(sName) values('天津');
insert into province(sName) values('西藏');
insert into province(sName) values('新疆');
insert into province(sName) values('云南');
insert into province(sName) values('浙江');
insert into province(sName) values('重慶');
insert into province(sName) values('香港');
insert into province(sName) values('澳門');
insert into province(sName) values('臺灣');
--市
insert into city(iProvinceId, sName) values(1,'安慶');
insert into city(iProvinceId, sName) values(1,'蚌埠');
insert into city(iProvinceId, sName) values(1,'巢湖');
insert into city(iProvinceId, sName) values(1,'池州');
insert into city(iProvinceId, sName) values(1,'滁州');
insert into city(iProvinceId, sName) values(1,'阜陽');
insert into city(iProvinceId, sName) values(1,'合肥');
insert into city(iProvinceId, sName) values(1,'淮北');
insert into city(iProvinceId, sName) values(1,'淮南');
insert into city(iProvinceId, sName) values(1,'黃山');
insert into city(iProvinceId, sName) values(1,'六安');
insert into city(iProvinceId, sName) values(1,'馬鞍山');
insert into city(iProvinceId, sName) values(1,'宿州');
insert into city(iProvinceId, sName) values(1,'銅陵');
insert into city(iProvinceId, sName) values(1,'蕪湖');
insert into city(iProvinceId, sName) values(1,'宣城');
insert into city(iProvinceId, sName) values(1,'亳州');
insert into city(iProvinceId, sName) values(2,'北京');
insert into city(iProvinceId, sName) values(3,'福州');
insert into city(iProvinceId, sName) values(3,'龍巖');
insert into city(iProvinceId, sName) values(3,'南平');
insert into city(iProvinceId, sName) values(3,'寧德');
insert into city(iProvinceId, sName) values(3,'莆田');
insert into city(iProvinceId, sName) values(3,'泉州');
insert into city(iProvinceId, sName) values(3,'三明');
insert into city(iProvinceId, sName) values(3,'廈門');
insert into city(iProvinceId, sName) values(3,'漳州');
insert into city(iProvinceId, sName) values(4,'白銀');
insert into city(iProvinceId, sName) values(4,'定西');
insert into city(iProvinceId, sName) values(4,'甘南藏族自治州');
insert into city(iProvinceId, sName) values(4,'嘉峪關');
insert into city(iProvinceId, sName) values(4,'金昌');
insert into city(iProvinceId, sName) values(4,'酒泉');
insert into city(iProvinceId, sName) values(4,'蘭州');
insert into city(iProvinceId, sName) values(4,'臨夏回族自治州');
insert into city(iProvinceId, sName) values(4,'隴南');
insert into city(iProvinceId, sName) values(4,'平涼');
insert into city(iProvinceId, sName) values(4,'慶陽');
insert into city(iProvinceId, sName) values(4,'天水');
insert into city(iProvinceId, sName) values(4,'武威');
insert into city(iProvinceId, sName) values(4,'張掖');
insert into city(iProvinceId, sName) values(5,'潮州');
insert into city(iProvinceId, sName) values(5,'東莞');
insert into city(iProvinceId, sName) values(5,'佛山');
insert into city(iProvinceId, sName) values(5,'廣州');
insert into city(iProvinceId, sName) values(5,'河源');
insert into city(iProvinceId, sName) values(5,'惠州');
insert into city(iProvinceId, sName) values(5,'江門');
insert into city(iProvinceId, sName) values(5,'揭陽');
insert into city(iProvinceId, sName) values(5,'茂名');
insert into city(iProvinceId, sName) values(5,'梅州');
insert into city(iProvinceId, sName) values(5,'清遠');
insert into city(iProvinceId, sName) values(5,'汕頭');
insert into city(iProvinceId, sName) values(5,'汕尾');
insert into city(iProvinceId, sName) values(5,'韶關');
insert into city(iProvinceId, sName) values(5,'深圳');
insert into city(iProvinceId, sName) values(5,'陽江');
insert into city(iProvinceId, sName) values(5,'云浮');
insert into city(iProvinceId, sName) values(5,'湛江');
insert into city(iProvinceId, sName) values(5,'肇慶');
insert into city(iProvinceId, sName) values(5,'中山');
insert into city(iProvinceId, sName) values(5,'珠海');
insert into city(iProvinceId, sName) values(6,'百色');
insert into city(iProvinceId, sName) values(6,'北海');
insert into city(iProvinceId, sName) values(6,'崇左');
insert into city(iProvinceId, sName) values(6,'防城港');
insert into city(iProvinceId, sName) values(6,'桂林');
insert into city(iProvinceId, sName) values(6,'貴港');
insert into city(iProvinceId, sName) values(6,'河池');
insert into city(iProvinceId, sName) values(6,'賀州');
insert into city(iProvinceId, sName) values(6,'來賓');
insert into city(iProvinceId, sName) values(6,'柳州');
insert into city(iProvinceId, sName) values(6,'南寧');
insert into city(iProvinceId, sName) values(6,'欽州');
insert into city(iProvinceId, sName) values(6,'梧州');
insert into city(iProvinceId, sName) values(6,'玉林');
insert into city(iProvinceId, sName) values(7,'安順');
insert into city(iProvinceId, sName) values(7,'畢節');
insert into city(iProvinceId, sName) values(7,'貴陽');
insert into city(iProvinceId, sName) values(7,'六盤水');
insert into city(iProvinceId, sName) values(7,'黔東南苗族侗族自治州');
insert into city(iProvinceId, sName) values(7,'黔南布依族苗族自治州');
insert into city(iProvinceId, sName) values(7,'黔西南布依族苗族自治州');
insert into city(iProvinceId, sName) values(7,'銅仁');
insert into city(iProvinceId, sName) values(7,'遵義');
insert into city(iProvinceId, sName) values(8,'白沙黎族自治縣');
insert into city(iProvinceId, sName) values(8,'保亭黎族苗族自治縣');
insert into city(iProvinceId, sName) values(8,'昌江黎族自治縣');
insert into city(iProvinceId, sName) values(8,'澄邁縣');
insert into city(iProvinceId, sName) values(8,'定安縣');
insert into city(iProvinceId, sName) values(8,'東方');
insert into city(iProvinceId, sName) values(8,'海口');
insert into city(iProvinceId, sName) values(8,'樂東黎族自治縣');
insert into city(iProvinceId, sName) values(8,'臨高縣');
insert into city(iProvinceId, sName) values(8,'陵水黎族自治縣');
insert into city(iProvinceId, sName) values(8,'瓊海');
insert into city(iProvinceId, sName) values(8,'瓊中黎族苗族自治縣');
insert into city(iProvinceId, sName) values(8,'三亞');
insert into city(iProvinceId, sName) values(8,'屯昌縣');
insert into city(iProvinceId, sName) values(8,'萬寧');
insert into city(iProvinceId, sName) values(8,'文昌');
insert into city(iProvinceId, sName) values(8,'五指山');
insert into city(iProvinceId, sName) values(8,'儋州');
insert into city(iProvinceId, sName) values(9,'保定');
insert into city(iProvinceId, sName) values(9,'滄州');
insert into city(iProvinceId, sName) values(9,'承德');
insert into city(iProvinceId, sName) values(9,'邯鄲');
insert into city(iProvinceId, sName) values(9,'衡水');
insert into city(iProvinceId, sName) values(9,'廊坊');
insert into city(iProvinceId, sName) values(9,'秦皇島');
insert into city(iProvinceId, sName) values(9,'石家莊');
insert into city(iProvinceId, sName) values(9,'唐山');
insert into city(iProvinceId, sName) values(9,'邢臺');
insert into city(iProvinceId, sName) values(9,'張家口');
insert into city(iProvinceId, sName) values(10,'安陽');
insert into city(iProvinceId, sName) values(10,'鶴壁');
insert into city(iProvinceId, sName) values(10,'濟源');
insert into city(iProvinceId, sName) values(10,'焦作');
insert into city(iProvinceId, sName) values(10,'開封');
insert into city(iProvinceId, sName) values(10,'洛陽');
insert into city(iProvinceId, sName) values(10,'南陽');
insert into city(iProvinceId, sName) values(10,'平頂山');
insert into city(iProvinceId, sName) values(10,'三門峽');
insert into city(iProvinceId, sName) values(10,'商丘');
insert into city(iProvinceId, sName) values(10,'新鄉');
insert into city(iProvinceId, sName) values(10,'信陽');
insert into city(iProvinceId, sName) values(10,'許昌');
insert into city(iProvinceId, sName) values(10,'鄭州');
insert into city(iProvinceId, sName) values(10,'周口');
insert into city(iProvinceId, sName) values(10,'駐馬店');
insert into city(iProvinceId, sName) values(10,'漯河');
insert into city(iProvinceId, sName) values(10,'濮陽');
insert into city(iProvinceId, sName) values(11,'大慶');
insert into city(iProvinceId, sName) values(11,'大興安嶺');
insert into city(iProvinceId, sName) values(11,'哈爾濱');
insert into city(iProvinceId, sName) values(11,'鶴崗');
insert into city(iProvinceId, sName) values(11,'黑河');
insert into city(iProvinceId, sName) values(11,'雞西');
insert into city(iProvinceId, sName) values(11,'佳木斯');
insert into city(iProvinceId, sName) values(11,'牡丹江');
insert into city(iProvinceId, sName) values(11,'七臺河');
insert into city(iProvinceId, sName) values(11,'齊齊哈爾');
insert into city(iProvinceId, sName) values(11,'雙鴨山');
insert into city(iProvinceId, sName) values(11,'綏化');
insert into city(iProvinceId, sName) values(11,'伊春');
insert into city(iProvinceId, sName) values(12,'鄂州');
insert into city(iProvinceId, sName) values(12,'恩施土家族苗族自治州');
insert into city(iProvinceId, sName) values(12,'黃岡');
insert into city(iProvinceId, sName) values(12,'黃石');
insert into city(iProvinceId, sName) values(12,'荊門');
insert into city(iProvinceId, sName) values(12,'荊州');
insert into city(iProvinceId, sName) values(12,'潛江');
insert into city(iProvinceId, sName) values(12,'神農架林區');
insert into city(iProvinceId, sName) values(12,'十堰');
insert into city(iProvinceId, sName) values(12,'隨州');
insert into city(iProvinceId, sName) values(12,'天門');
insert into city(iProvinceId, sName) values(12,'武漢');
insert into city(iProvinceId, sName) values(12,'仙桃');
insert into city(iProvinceId, sName) values(12,'咸寧');
insert into city(iProvinceId, sName) values(12,'襄樊');
insert into city(iProvinceId, sName) values(12,'孝感');
insert into city(iProvinceId, sName) values(12,'宜昌');
insert into city(iProvinceId, sName) values(13,'常德');
insert into city(iProvinceId, sName) values(13,'長沙');
insert into city(iProvinceId, sName) values(13,'郴州');
insert into city(iProvinceId, sName) values(13,'衡陽');
insert into city(iProvinceId, sName) values(13,'懷化');
insert into city(iProvinceId, sName) values(13,'婁底');
insert into city(iProvinceId, sName) values(13,'邵陽');
insert into city(iProvinceId, sName) values(13,'湘潭');
insert into city(iProvinceId, sName) values(13,'湘西土家族苗族自治州');
insert into city(iProvinceId, sName) values(13,'益陽');
insert into city(iProvinceId, sName) values(13,'永州');
insert into city(iProvinceId, sName) values(13,'岳陽');
insert into city(iProvinceId, sName) values(13,'張家界');
insert into city(iProvinceId, sName) values(13,'株洲');
insert into city(iProvinceId, sName) values(14,'白城');
insert into city(iProvinceId, sName) values(14,'白山');
insert into city(iProvinceId, sName) values(14,'長春');
insert into city(iProvinceId, sName) values(14,'吉林');
insert into city(iProvinceId, sName) values(14,'遼源');
insert into city(iProvinceId, sName) values(14,'四平');
insert into city(iProvinceId, sName) values(14,'松原');
insert into city(iProvinceId, sName) values(14,'通化');
insert into city(iProvinceId, sName) values(14,'延邊朝鮮族自治州');
insert into city(iProvinceId, sName) values(15,'常州');
insert into city(iProvinceId, sName) values(15,'淮安');
insert into city(iProvinceId, sName) values(15,'連云港');
insert into city(iProvinceId, sName) values(15,'南京');
insert into city(iProvinceId, sName) values(15,'南通');
insert into city(iProvinceId, sName) values(15,'蘇州');
insert into city(iProvinceId, sName) values(15,'宿遷');
insert into city(iProvinceId, sName) values(15,'泰州');
insert into city(iProvinceId, sName) values(15,'無錫');
insert into city(iProvinceId, sName) values(15,'徐州');
insert into city(iProvinceId, sName) values(15,'鹽城');
insert into city(iProvinceId, sName) values(15,'揚州');
insert into city(iProvinceId, sName) values(15,'鎮江');
insert into city(iProvinceId, sName) values(16,'撫州');
insert into city(iProvinceId, sName) values(16,'贛州');
insert into city(iProvinceId, sName) values(16,'吉安');
insert into city(iProvinceId, sName) values(16,'景德鎮');
insert into city(iProvinceId, sName) values(16,'九江');
insert into city(iProvinceId, sName) values(16,'南昌');
insert into city(iProvinceId, sName) values(16,'萍鄉');
insert into city(iProvinceId, sName) values(16,'上饒');
insert into city(iProvinceId, sName) values(16,'新余');
insert into city(iProvinceId, sName) values(16,'宜春');
insert into city(iProvinceId, sName) values(16,'鷹潭');
insert into city(iProvinceId, sName) values(17,'鞍山');
insert into city(iProvinceId, sName) values(17,'本溪');
insert into city(iProvinceId, sName) values(17,'朝陽');
insert into city(iProvinceId, sName) values(17,'大連');
insert into city(iProvinceId, sName) values(17,'丹東');
insert into city(iProvinceId, sName) values(17,'撫順');
insert into city(iProvinceId, sName) values(17,'阜新');
insert into city(iProvinceId, sName) values(17,'葫蘆島');
insert into city(iProvinceId, sName) values(17,'錦州');
insert into city(iProvinceId, sName) values(17,'遼陽');
insert into city(iProvinceId, sName) values(17,'盤錦');
insert into city(iProvinceId, sName) values(17,'沈陽');
insert into city(iProvinceId, sName) values(17,'鐵嶺');
insert into city(iProvinceId, sName) values(17,'營口');
insert into city(iProvinceId, sName) values(18,'阿拉善盟');
insert into city(iProvinceId, sName) values(18,'巴彥淖爾盟');
insert into city(iProvinceId, sName) values(18,'包頭');
insert into city(iProvinceId, sName) values(18,'赤峰');
insert into city(iProvinceId, sName) values(18,'鄂爾多斯');
insert into city(iProvinceId, sName) values(18,'呼和浩特');
insert into city(iProvinceId, sName) values(18,'呼倫貝爾');
insert into city(iProvinceId, sName) values(18,'通遼');
insert into city(iProvinceId, sName) values(18,'烏海');
insert into city(iProvinceId, sName) values(18,'烏蘭察布盟');
insert into city(iProvinceId, sName) values(18,'錫林郭勒盟');
insert into city(iProvinceId, sName) values(18,'興安盟');
insert into city(iProvinceId, sName) values(19,'固原');
insert into city(iProvinceId, sName) values(19,'石嘴山');
insert into city(iProvinceId, sName) values(19,'吳忠');
insert into city(iProvinceId, sName) values(19,'銀川');
insert into city(iProvinceId, sName) values(20,'果洛藏族自治州');
insert into city(iProvinceId, sName) values(20,'海北藏族自治州');
insert into city(iProvinceId, sName) values(20,'海東');
insert into city(iProvinceId, sName) values(20,'海南藏族自治州');
insert into city(iProvinceId, sName) values(20,'海西蒙古族藏族自治州');
insert into city(iProvinceId, sName) values(20,'黃南藏族自治州');
insert into city(iProvinceId, sName) values(20,'西寧');
insert into city(iProvinceId, sName) values(20,'玉樹藏族自治州');
insert into city(iProvinceId, sName) values(21,'濱州');
insert into city(iProvinceId, sName) values(21,'德州');
insert into city(iProvinceId, sName) values(21,'東營');
insert into city(iProvinceId, sName) values(21,'菏澤');
insert into city(iProvinceId, sName) values(21,'濟南');
insert into city(iProvinceId, sName) values(21,'濟寧');
insert into city(iProvinceId, sName) values(21,'萊蕪');
insert into city(iProvinceId, sName) values(21,'聊城');
insert into city(iProvinceId, sName) values(21,'臨沂');
insert into city(iProvinceId, sName) values(21,'青島');
insert into city(iProvinceId, sName) values(21,'日照');
insert into city(iProvinceId, sName) values(21,'泰安');
insert into city(iProvinceId, sName) values(21,'威海');
insert into city(iProvinceId, sName) values(21,'濰坊');
insert into city(iProvinceId, sName) values(21,'煙臺');
insert into city(iProvinceId, sName) values(21,'棗莊');
insert into city(iProvinceId, sName) values(21,'淄博');
insert into city(iProvinceId, sName) values(22,'長治');
insert into city(iProvinceId, sName) values(22,'大同');
insert into city(iProvinceId, sName) values(22,'晉城');
insert into city(iProvinceId, sName) values(22,'晉中');
insert into city(iProvinceId, sName) values(22,'臨汾');
insert into city(iProvinceId, sName) values(22,'呂梁');
insert into city(iProvinceId, sName) values(22,'朔州');
insert into city(iProvinceId, sName) values(22,'太原');
insert into city(iProvinceId, sName) values(22,'忻州');
insert into city(iProvinceId, sName) values(22,'陽泉');
insert into city(iProvinceId, sName) values(22,'運城');
insert into city(iProvinceId, sName) values(23,'安康');
insert into city(iProvinceId, sName) values(23,'寶雞');
insert into city(iProvinceId, sName) values(23,'漢中');
insert into city(iProvinceId, sName) values(23,'商洛');
insert into city(iProvinceId, sName) values(23,'銅川');
insert into city(iProvinceId, sName) values(23,'渭南');
insert into city(iProvinceId, sName) values(23,'西安');
insert into city(iProvinceId, sName) values(23,'咸陽');
insert into city(iProvinceId, sName) values(23,'延安');
insert into city(iProvinceId, sName) values(23,'榆林');
insert into city(iProvinceId, sName) values(24,'上海');
insert into city(iProvinceId, sName) values(25,'阿壩藏族羌族自治州');
insert into city(iProvinceId, sName) values(25,'巴中');
insert into city(iProvinceId, sName) values(25,'成都');
insert into city(iProvinceId, sName) values(25,'達州');
insert into city(iProvinceId, sName) values(25,'德陽');
insert into city(iProvinceId, sName) values(25,'甘孜藏族自治州');
insert into city(iProvinceId, sName) values(25,'廣安');
insert into city(iProvinceId, sName) values(25,'廣元');
insert into city(iProvinceId, sName) values(25,'樂山');
insert into city(iProvinceId, sName) values(25,'涼山彝族自治州');
insert into city(iProvinceId, sName) values(25,'眉山');
insert into city(iProvinceId, sName) values(25,'綿陽');
insert into city(iProvinceId, sName) values(25,'南充');
insert into city(iProvinceId, sName) values(25,'內江');
insert into city(iProvinceId, sName) values(25,'攀枝花');
insert into city(iProvinceId, sName) values(25,'遂寧');
insert into city(iProvinceId, sName) values(25,'雅安');
insert into city(iProvinceId, sName) values(25,'宜賓');
insert into city(iProvinceId, sName) values(25,'資陽');
insert into city(iProvinceId, sName) values(25,'自貢');
insert into city(iProvinceId, sName) values(25,'瀘州');
insert into city(iProvinceId, sName) values(26,'天津');
insert into city(iProvinceId, sName) values(27,'阿里');
insert into city(iProvinceId, sName) values(27,'昌都');
insert into city(iProvinceId, sName) values(27,'拉薩');
insert into city(iProvinceId, sName) values(27,'林芝');
insert into city(iProvinceId, sName) values(27,'那曲');
insert into city(iProvinceId, sName) values(27,'日喀則');
insert into city(iProvinceId, sName) values(27,'山南');
insert into city(iProvinceId, sName) values(28,'阿克蘇');
insert into city(iProvinceId, sName) values(28,'阿拉爾');
insert into city(iProvinceId, sName) values(28,'巴音郭楞蒙古自治州');
insert into city(iProvinceId, sName) values(28,'博爾塔拉蒙古自治州');
insert into city(iProvinceId, sName) values(28,'昌吉回族自治州');
insert into city(iProvinceId, sName) values(28,'哈密');
insert into city(iProvinceId, sName) values(28,'和田');
insert into city(iProvinceId, sName) values(28,'喀什');
insert into city(iProvinceId, sName) values(28,'克拉瑪依');
insert into city(iProvinceId, sName) values(28,'克孜勒蘇柯爾克孜自治州');
insert into city(iProvinceId, sName) values(28,'石河子');
insert into city(iProvinceId, sName) values(28,'圖木舒克');
insert into city(iProvinceId, sName) values(28,'吐魯番');
insert into city(iProvinceId, sName) values(28,'烏魯木齊');
insert into city(iProvinceId, sName) values(28,'五家渠');
insert into city(iProvinceId, sName) values(28,'伊犁哈薩克自治州');
insert into city(iProvinceId, sName) values(29,'保山');
insert into city(iProvinceId, sName) values(29,'楚雄彝族自治州');
insert into city(iProvinceId, sName) values(29,'大理白族自治州');
insert into city(iProvinceId, sName) values(29,'德宏傣族景頗族自治州');
insert into city(iProvinceId, sName) values(29,'迪慶藏族自治州');
insert into city(iProvinceId, sName) values(29,'紅河哈尼族彝族自治州');
insert into city(iProvinceId, sName) values(29,'昆明');
insert into city(iProvinceId, sName) values(29,'麗江');
insert into city(iProvinceId, sName) values(29,'臨滄');
insert into city(iProvinceId, sName) values(29,'怒江傈傈族自治州');
insert into city(iProvinceId, sName) values(29,'曲靖');
insert into city(iProvinceId, sName) values(29,'思茅');
insert into city(iProvinceId, sName) values(29,'文山壯族苗族自治州');
insert into city(iProvinceId, sName) values(29,'西雙版納傣族自治州');
insert into city(iProvinceId, sName) values(29,'玉溪');
insert into city(iProvinceId, sName) values(29,'昭通');
insert into city(iProvinceId, sName) values(30,'杭州');
insert into city(iProvinceId, sName) values(30,'湖州');
insert into city(iProvinceId, sName) values(30,'嘉興');
insert into city(iProvinceId, sName) values(30,'金華');
insert into city(iProvinceId, sName) values(30,'麗水');
insert into city(iProvinceId, sName) values(30,'寧波');
insert into city(iProvinceId, sName) values(30,'紹興');
insert into city(iProvinceId, sName) values(30,'臺州');
insert into city(iProvinceId, sName) values(30,'溫州');
insert into city(iProvinceId, sName) values(30,'舟山');
insert into city(iProvinceId, sName) values(30,'衢州');
insert into city(iProvinceId, sName) values(31,'重慶');
insert into city(iProvinceId, sName) values(32,'香港');
insert into city(iProvinceId, sName) values(33,'澳門');
insert into city(iProvinceId, sName) values(34,'臺灣');
全國省市表
將以上代碼放入 Sql Server 查詢分析器內直接執行即可生成兩張表一張省表，一張市表，
其中City表內的 iProvinceId 字段與 Province 表內的 iAutoId 字段關連