中国省份城市数据库表(mysql)
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
insert into Provincial values(31,'新疆维吾尔自治区')
insert into Provincial values(32,'内蒙古自治区')
insert into Provincial values(33,'澳门特别行政区')
insert into Provincial values(34,'香港特别行政区')
--select * from City where pid=4
----------------------------------------------------------------
--5河北省 11个地级市
insert into City values(1,'石家庄市',5)
insert into City values(2,'唐山市',5)
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(12,'苗栗县',7)
insert into City values(13,'台中县',7)
insert into City values(14,'彰化县',7)
insert into City values(15,'南投县',7)
insert into City values(16,'云林县',7)
insert into City values(7,'嘉义市',7)
insert into City values(8,'台北县',7)
insert into City values(9,'宜兰县',7)
insert into City values(10,'桃园县',7)
insert into City values(11,'新竹县',7)
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 Provincial values(15,'江西省')
insert into Provincial values(16,'山东省')
insert into Provincial values(17,'河南省')
insert into Provincial values(18,'湖北省')
insert into City values(17,'嘉义县',7)
insert into City values(18,'台南县',7)
insert39;高雄县',7)
insert into City values(20,'屏东县',7)
insert into City values(21,'澎湖县',7)
insert into City values(22,'台东县',7)
insert into City values(23,'花莲县',7)
--select * from City where pid=7 order by cid
----------------------------------------------------------------
--select pid,Provincial from Provincial
create table City(cid int not null,city varchar(50) primary key,pid int foreign key references Provincial(pid))
insert into Provincial values(3,'上海市')
insert into Provincial values(4,'重庆市')
insert into Provincial values(5,'河北省')
insert into Provincial values(6,'山西省')
----------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
insert into Provincial values(27,'陕西省')
insert into Provincial values(28,'广西壮族自治区')
insert into Provincial values(29,'西藏自治区')
insert into Provincial values(30,'宁夏回族自治区')
--select * from City where pid=8 order by cid
----------------------------------------------------------------
--9吉林省(2006年，辖：8个地级市、1个自治州；20个市辖区、20个县级市、17个县、3个自治县。)
insert into Provincial values(11,'江苏省')
insert into Provincial values(12,'浙江省')
insert into Provincial values(13,'安徽省')
insert into Provincial values(14,'福建省')
insert into City values(9,'忻州市',6)
insert into City values(10,'临汾市',6)
insert into City values(11,'吕梁市',6)
--select * from City where pid=6 order by cid
--省级 Provincial
--城市 City
create table Provincial(pid int,Provincial varchar(50),primary key (pid))
insert into Provincial values(1,'北京市')
insert into Provincial values(2,'天津市')
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 Provincial values(23,'贵州省')
insert into Provincial values(24,'海南省')
insert into Provincial values(25,'云南省')
insert into Provincial values(26,'青海省')
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(6,'白山市',9)
insert into City values(8,'承德市',5)
insert into City values(9,'沧州市',5)
insert into City values(10,'廊坊市',5)
insert into City values(11,'衡水市',5)
--select * from City where pid=5 order by cid
----------------------------------------------------------------
--6山西省 11个城市
insert into City values(1,'太原市',6)
insert into City values(2,'大同市',6)
insert into City values(3,'阳泉市',6)
--8辽宁省 14个地级市
insert into City values(1,'沈阳市',8)
insert into City values(2,'大连市',8)
insert into City values(3,'鞍山市',8)
insert into City values(4,'抚顺市',8)
--插入各个省的城市数据
--4个直辖市
insert into City values(1,'北京市',1)
insert into City values(1,'天津市',2)
insert into City values(1,'上海市',3)
insert into City values(1,'重庆市',4)
insert into City values(10,'辽阳市',8)
insert into City values(11,'盘锦市',8)
insert into City values(12,'铁岭市',8)
insert into City values(13,'朝阳市',8)
insert into City values(14,'葫芦岛市',8)
insert into Provincial values(19,'湖南省')
insert into Provincial values(20,'广东省')
insert into Provincial values(21,'甘肃省')
insert into Provincial values(22,'四川省')
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 Provincial values(7,'台湾省')
insert into Provincial values(8,'辽宁省')
insert into Provincial values(9,'吉林省')
insert into Provincial values(10,'黑龙江省')
----------------------------------------------------------------
--7台湾省(台湾本岛和澎湖共设7市、16县，其中台北市和高雄市为“院辖市”，直属“行政院”，其余属台湾省；市下设区，县下设市（县辖市）、镇、乡，合称区市镇乡。)
insert into City values(1,'台北市',7)
insert into Provincial values(32,'内蒙古自治区')
insert into Provincial values(33,'澳门特别行政区')
insert into Provincial values(34,'香港特别行政区')
--select * from City where pid=4
----------------------------------------------------------------
--5河北省 11个地级市
insert into City values(1,'石家庄市',5)
insert into City values(2,'唐山市',5)
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(12,'苗栗县',7)
insert into City values(13,'台中县',7)
insert into City values(14,'彰化县',7)
insert into City values(15,'南投县',7)
insert into City values(16,'云林县',7)
insert into City values(7,'嘉义市',7)
insert into City values(8,'台北县',7)
insert into City values(9,'宜兰县',7)
insert into City values(10,'桃园县',7)
insert into City values(11,'新竹县',7)
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 Provincial values(15,'江西省')
insert into Provincial values(16,'山东省')
insert into Provincial values(17,'河南省')
insert into Provincial values(18,'湖北省')
insert into City values(17,'嘉义县',7)
insert into City values(18,'台南县',7)
insert39;高雄县',7)
insert into City values(20,'屏东县',7)
insert into City values(21,'澎湖县',7)
insert into City values(22,'台东县',7)
insert into City values(23,'花莲县',7)
--select * from City where pid=7 order by cid
----------------------------------------------------------------
--select pid,Provincial from Provincial
create table City(cid int not null,city varchar(50) primary key,pid int foreign key references Provincial(pid))
insert into Provincial values(3,'上海市')
insert into Provincial values(4,'重庆市')
insert into Provincial values(5,'河北省')
insert into Provincial values(6,'山西省')
----------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
insert into Provincial values(27,'陕西省')
insert into Provincial values(28,'广西壮族自治区')
insert into Provincial values(29,'西藏自治区')
insert into Provincial values(30,'宁夏回族自治区')
--select * from City where pid=8 order by cid
----------------------------------------------------------------
--9吉林省(2006年，辖：8个地级市、1个自治州；20个市辖区、20个县级市、17个县、3个自治县。)
insert into Provincial values(11,'江苏省')
insert into Provincial values(12,'浙江省')
insert into Provincial values(13,'安徽省')
insert into Provincial values(14,'福建省')
insert into City values(9,'忻州市',6)
insert into City values(10,'临汾市',6)
insert into City values(11,'吕梁市',6)
--select * from City where pid=6 order by cid
--省级 Provincial
--城市 City
create table Provincial(pid int,Provincial varchar(50),primary key (pid))
insert into Provincial values(1,'北京市')
insert into Provincial values(2,'天津市')
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 Provincial values(23,'贵州省')
insert into Provincial values(24,'海南省')
insert into Provincial values(25,'云南省')
insert into Provincial values(26,'青海省')
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(6,'白山市',9)
insert into City values(8,'承德市',5)
insert into City values(9,'沧州市',5)
insert into City values(10,'廊坊市',5)
insert into City values(11,'衡水市',5)
--select * from City where pid=5 order by cid
----------------------------------------------------------------
--6山西省 11个城市
insert into City values(1,'太原市',6)
insert into City values(2,'大同市',6)
insert into City values(3,'阳泉市',6)
--8辽宁省 14个地级市
insert into City values(1,'沈阳市',8)
insert into City values(2,'大连市',8)
insert into City values(3,'鞍山市',8)
insert into City values(4,'抚顺市',8)
--插入各个省的城市数据
--4个直辖市
insert into City values(1,'北京市',1)
insert into City values(1,'天津市',2)
insert into City values(1,'上海市',3)
insert into City values(1,'重庆市',4)
insert into City values(10,'辽阳市',8)
insert into City values(11,'盘锦市',8)
insert into City values(12,'铁岭市',8)
insert into City values(13,'朝阳市',8)
insert into City values(14,'葫芦岛市',8)
insert into Provincial values(19,'湖南省')
insert into Provincial values(20,'广东省')
insert into Provincial values(21,'甘肃省')
insert into Provincial values(22,'四川省')
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 Provincial values(7,'台湾省')
insert into Provincial values(8,'辽宁省')
insert into Provincial values(9,'吉林省')
insert into Provincial values(10,'黑龙江省')
----------------------------------------------------------------
--7台湾省(台湾本岛和澎湖共设7市、16县，其中台北市和高雄市为“院辖市”，直属“行政院”，其余属台湾省；市下设区，县下设市（县辖市）、镇、乡，合称区市镇乡。)
insert into City values(1,'台北市',7)