中国各省份及城市sql server 的sql语句

2010-08-18

 

省份

create table tb_province
(
id int primary key,
name nvarchar(50)
)

insert into tb_province (id,name) values (1,'北京市')
insert into tb_province (id,name) values (2,'天津市')
insert into tb_province (id,name) values (3,'河北省')
insert into tb_province (id,name) values (4,'山西省')
insert into tb_province (id,name) values (5,'内蒙古自治区')
insert into tb_province (id,name) values (6,'辽宁省')
insert into tb_province (id,name) values (7,'吉林省')
insert into tb_province (id,name) values (8,'黑龙江省')
insert into tb_province (id,name) values (9,'上海市')
insert into tb_province (id,name) values (10,'江苏省')
insert into tb_province (id,name) values (11,'浙江省')
insert into tb_province (id,name) values (12,'安徽省')
insert into tb_province (id,name) values (13,'福建省')
insert into tb_province (id,name) values (14,'江西省')
insert into tb_province (id,name) values (15,'山东省')
insert into tb_province (id,name) values (16,'河南省')
insert into tb_province (id,name) values (17,'湖北省')
insert into tb_province (id,name) values (18,'湖南省')
insert into tb_province (id,name) values (19,'广东省')
insert into tb_province (id,name) values (20,'广西壮族自治区')
insert into tb_province (id,name) values (21,'海南省')
insert into tb_province (id,name) values (22,'重庆市')
insert into tb_province (id,name) values (23,'四川省')
insert into tb_province (id,name) values (24,'贵州省')
insert into tb_province (id,name) values (25,'云南省')
insert into tb_province (id,name) values (26,'西藏自治区')
insert into tb_province (id,name) values (27,'陕西省')
insert into tb_province (id,name) values (28,'甘肃省')
insert into tb_province (id,name) values (29,'青海省')
insert into tb_province (id,name) values (30,'宁夏回族自治区')
insert into tb_province (id,name) values (31,'新疆维吾尔自治区')
insert into tb_province (id,name) values (32,'香港特别行政区')
insert into tb_province (id,name) values (33,'澳门特别行政区')
insert into tb_province (id,name) values (34,'台湾省')

select * from tb_province

 

城市

create table tb_city
(
id int primary key,
name nvarchar(50),
provinceid int
)

insert into tb_city (id,name,provinceid) values (1,'北京市',1)
insert into tb_city (id,name,provinceid) values (2,'天津市',2)
insert into tb_city (id,name,provinceid) values (3,'石家庄市',3)
insert into tb_city (id,name,provinceid) values (4,'唐山市',3)
insert into tb_city (id,name,provinceid) values (5,'秦皇岛市',3)
insert into tb_city (id,name,provinceid) values (6,'邯郸市',3)
insert into tb_city (id,name,provinceid) values (7,'邢台市',3)
insert into tb_city (id,name,provinceid) values (8,'保定市',3)
insert into tb_city (id,name,provinceid) values (9,'张家口市',3)
insert into tb_city (id,name,provinceid) values (10,'承德市',3)
insert into tb_city (id,name,provinceid) values (11,'沧州市',3)
insert into tb_city (id,name,provinceid) values (12,'廊坊市',3)
insert into tb_city (id,name,provinceid) values (13,'衡水市',3)
insert into tb_city (id,name,provinceid) values (14,'太原市',4)
insert into tb_city (id,name,provinceid) values (15,'大同市',4)
insert into tb_city (id,name,provinceid) values (16,'阳泉市',4)
insert into tb_city (id,name,provinceid) values (17,'长治市',4)
insert into tb_city (id,name,provinceid) values (18,'晋城市',4)
insert into tb_city (id,name,provinceid) values (19,'朔州市',4)
insert into tb_city (id,name,provinceid) values (20,'晋中市',4)
insert into tb_city (id,name,provinceid) values (21,'运城市',4)
insert into tb_city (id,name,provinceid) values (22,'忻州市',4)
insert into tb_city (id,name,provinceid) values (23,'临汾市',4)
insert into tb_city (id,name,provinceid) values (24,'吕梁市',4)
insert into tb_city (id,name,provinceid) values (25,'呼和浩特市',5)
insert into tb_city (id,name,provinceid) values (26,'包头市',5)
insert into tb_city (id,name,provinceid) values (27,'乌海市',5)
insert into tb_city (id,name,provinceid) values (28,'赤峰市',5)
insert into tb_city (id,name,provinceid) values (29,'通辽市',5)
insert into tb_city (id,name,provinceid) values (30,'鄂尔多斯市',5)
insert into tb_city (id,name,provinceid) values (31,'呼伦贝尔市',5)
insert into tb_city (id,name,provinceid) values (32,'巴彦淖尔市',5)
insert into tb_city (id,name,provinceid) values (33,'乌兰察布市',5)
insert into tb_city (id,name,provinceid) values (34,'兴安盟',5)
insert into tb_city (id,name,provinceid) values (35,'锡林郭勒盟',5)
insert into tb_city (id,name,provinceid) values (36,'阿拉善盟',5)
insert into tb_city (id,name,provinceid) values (37,'沈阳市',6)
insert into tb_city (id,name,provinceid) values (38,'大连市',6)
insert into tb_city (id,name,provinceid) values (39,'鞍山市',6)
insert into tb_city (id,name,provinceid) values (40,'抚顺市',6)
insert into tb_city (id,name,provinceid) values (41,'本溪市',6)
insert into tb_city (id,name,provinceid) values (42,'丹东市',6)
insert into tb_city (id,name,provinceid) values (43,'锦州市',6)
insert into tb_city (id,name,provinceid) values (44,'营口市',6)