全国省市级联数据sql语句 mysql版
--省级   provincial  
create table provincial
(
  provincialID int,
  provincialName varchar(50),
  primary key (provincialID)
)engine=INNODB default charset=gbk;
  
insert into provincial values(1,'北京市');
insert into provincial values(2,'天津市');
insert into provincial values(3,'上海市');
insert into provincial values(4,'重庆市');
insert into provincial values(5,'河北省');
insert into provincial values(6,'山西省');
insert into provincial values(7,'台湾省');
insert into provincial values(8,'辽宁省');
insert into provincial values(9,'吉林省');
insert into provincial values(10,'黑龙江省');
insert into provincial values(11,'江苏省');
insert into provincial values(12,'浙江省');
insert into provincial values(13,'安徽省');
insert into provincial values(14,'福建省');
insert into provincial values(15,'江西省');
insert into provincial values(16,'山东省');
insert into provincial values(17,'河南省');
insert into provincial values(18,'湖北省');
insert into provincial values(19,'湖南省');
insert into provincial values(20,'广东省');
insert into provincial values(21,'甘肃省');
insert into provincial values(22,'四川省');
insert into provincial values(23,'贵州省');
insert into provincial values(24,'海南省');
insert into provincial values(25,'云南省');
insert into provincial values(26,'青海省');
insert into provincial values(27,'陕西省');
insert into provincial values(28,'广西壮族自治区');
insert into provincial values(29,'西藏自治区');
insert into provincial values(30,'宁夏回族自治区');
insert into provincial values(31,'新疆维吾尔自治区');
insert into provincial values(32,'内蒙古自治区');
insert into provincial values(33,'澳门特别行政区');
insert into provincial values(34,'香港特别行政区');
select provincialID,provincialName from provincial;
   
--城市   city
create table city
(
cityID int not null,
cityName varchar(50) not null,
provincialID int not null,
primary key (cityID)
)engine=INNODB default charset=gbk;

alter table city add constraint FK_pro_city_provincialID foreign key (provincialID)
      references provincial (provincialID);
     
----------------------------------------------------------------  
---------------------------  
--插入各个省的城市数据  
--4个直辖市  
insert into city values(1,'北京市',1);
insert into city values(2,'天津市',2);
insert into city values(3,'上海市',3);
insert into city values(4,'重庆市',4);
--select * from city where provincialID=4  
----------------------------  
--5河北省 11个地级市  
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(13,'沧州市',5);  
insert into city values(14,'廊坊市',5);  
insert into city values(15,'衡水市',5);  
--select * from city where provincialID=5 order by cityID  
----------------------------  
--6山西省 11个城市  
insert into city values(16,'太原市',6);  
insert into city values(17,'大同市',6);  
insert into city values(18,'阳泉市',6);  
insert into city values(19,'长治市',6);  
insert into city values(20,'晋城市',6);  
insert into city values(21,'朔州市',6);  
insert into city values(22,'晋中市',6);