--省表
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 字段关连