中国省市级联

/*--省级   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);   
INSERT INTO city VALUES(23,'运城市',6);   
INSERT INTO city VALUES(24,'忻州市',6);   
INSERT INTO city VALUES(25,'临汾市',6);   
INSERT INTO city VALUES(26,'吕梁市',6);   
/*--select * from city where provincialID=6 order by cityID   
----------------------------------------------------------------   
--7台湾省(台湾本岛和澎湖共设7市、16县，其中台北市和高雄市为“院辖市”，直属“行政院”，其余属台湾省；市下设区，县下设市（县辖市）、镇、乡，合称区市镇乡。);   
*/
INSERT INTO city VALUES(27,'台北市',7);   
INSERT INTO city VALUES(28,'高雄市',7);   
INSERT INTO city VALUES(29,'基隆市',7);   
INSERT INTO city VALUES(30,'台中市',7);   
INSERT INTO city VALUES(31,'台南市',7);   
INSERT INTO city VALUES(32,'新竹市',7);   
INSERT INTO city VALUES(33,'嘉义市',7);   
INSERT INTO city VALUES(34,'台北县',7);   
INSERT INTO city VALUES(35,'宜兰县',7);   
INSERT INTO city VALUES(36,'桃园县',7);   
INSERT INTO city VALUES(37,'新竹县',7);   
INSERT INTO city VALUES(38,'苗栗县',7);   
INSERT INTO city VALUES(39,'台中县',7);   
INSERT INTO city VALUES(40,'彰化县',7);   
INSERT INTO city VALUES(41,'南投县',7);   
INSERT INTO city VALUES(42,'云林县',7);   
INSERT INTO city VALUES(43,'嘉义县',7);   
INSERT INTO city VALUES(44,'台南县',7);   
INSERT INTO city VALUES(45,'高雄县',7);   
INSERT INTO city VALUES(46,'屏东县',7);   
INSERT INTO city VALUES(47,'澎湖县',7);   
INSERT INTO city VALUES(48,'台东县',7);   
INSERT INTO city VALUES(49,'花莲县',7);   
/*--select * from city where provincialID=7 order by cityID   
----------------------------------------------------------------   
--8辽宁省 14个地级市*/   
INSERT INTO city VALUES(50,'沈阳市',8);   
INSERT INTO city VALUES(51,'大连市',8);   
INSERT INTO city VALUES(52,'鞍山市',8);   
INSERT INTO city VALUES(53,'抚顺市',8);   
INSERT INTO city VALUES(54,'本溪市',8);   
INSERT INTO city VALUES(55,'丹东市',8);   
INSERT INTO city VALUES(56,'锦州市',8);   
INSERT INTO city VALUES(57,'营口市',8);   
INSERT INTO city VALUES(58,'阜新市',8);   
INSERT INTO city VALUES(59,'辽阳市',8);   
INSERT INTO city VALUES(60,'盘锦市',8);   
INSERT INTO city VALUES(61,'铁岭市',8);   
INSERT INTO city VALUES(62,'朝阳市',8);   
INSERT INTO city VALUES(63,'葫芦岛市',8);   
/*--select * from city where provincialID=8 order by cityID   
----------------------------------------------------------------   
--9吉林省(2006年，辖：8个地级市、1个自治州；20个市辖区、20个县级市、17个县、3个自治县。);   */
INSERT INTO city VALUES(64,'长春市',9);   
INSERT INTO city VALUES(65,'吉林市',9);   
INSERT INTO city VALUES(66,'四平市',9);   
INSERT INTO city VALUES(67,'辽源市',9);   
INSERT INTO city VALUES(68,'通化市',9);   
INSERT INTO city VALUES(69,'白山市',9);   
INSERT INTO city VALUES(70,'松原市',9);   
INSERT INTO city VALUES(71,'白城市',9);   
INSERT INTO city VALUES(72,'延边朝鲜族自治州',9);   
/*--select * from city where provincialID=9 order by cityID   
----------------------------------------------------------------   
--10黑龙江省(2006年，辖：12地级市、1地区；64市辖区、18县级市、45县、1自治县);   */
INSERT INTO city VALUES(73,'哈尔滨市',10);   
INSERT INTO city VALUES(74,'齐齐哈尔市',10);   
INSERT INTO city VALUES(75,'鹤 岗 市',10);   
INSERT INTO city VALUES(76,'双鸭山市',10);   
INSERT INTO city VALUES(77,'鸡 西 市',10);   
INSERT INTO city VALUES(78,'大 庆 市',10);   
INSERT INTO city VALUES(79,'伊 春 市',10);   
INSERT INTO city VALUES(80,'牡丹江市',10);   
INSERT INTO city VALUES(81,'佳木斯市',10);   
INSERT INTO city VALUES(82,'七台河市',10);   
INSERT INTO city VALUES(83,'黑 河 市',10);   
INSERT INTO city VALUES(84,'绥 化 市',10);   
INSERT INTO city VALUES(85,'大兴安岭地区',10);   
/*--select * from city where provincialID=10 order by cityID   
----------------------------------------------------------------   
--11江苏省(2005年辖：13个地级市；54个市辖区、27个县级市、25个县);   */
INSERT INTO city VALUES(86,'南京市',11);   
INSERT INTO city VALUES(87,'无锡市',11);   
INSERT INTO city VALUES(88,'徐州市',11);   
INSERT INTO city VALUES(89,'常州市',11);   
INSERT INTO city VALUES(90,'苏州市',11);   
INSERT INTO city VALUES(91,'南通市',11);   
INSERT INTO city VALUES(92,'连云港市',11);   
INSERT INTO city VALUES(93,'淮安市',11);   
INSERT INTO city VALUES(94,'盐城市',11);   
INSERT INTO city VALUES(95,'扬州市',11);   
INSERT INTO city VALUES(96,'镇江市',11);   
INSERT INTO city VALUES(97,'泰州市',11);   
INSERT INTO city VALUES(98,'宿迁市',11);   
/*--select * from city where provincialID=11 order by cityID   
----------------------------------------------------------------   
--12浙江省(2006年，辖：11个地级市；32个市辖区、22个县级市、35个县、1个自治县。);   */
INSERT INTO city VALUES(99,'杭州市',12);   
INSERT INTO city VALUES(100,'宁波市',12);   
INSERT INTO city VALUES(101,'温州市',12);   
INSERT INTO city VALUES(102,'嘉兴市',12);   
INSERT INTO city VALUES(103,'湖州市',12);   
INSERT INTO city VALUES(104,'绍兴市',12);   
INSERT INTO city VALUES(105,'金华市',12);   
INSERT INTO city VALUES(106,'衢州市',12);   
INSERT INTO city VALUES(107,'舟山市',12);   
INSERT INTO city VALUES(108,'台州市',12);   
INSERT INTO city VALUES(109,'丽水市',12);   
/*--select * from city where provincialID=12 order by cityID   
----------------------------------------------------------------   
--13安徽省(2005年辖：17个地级市；44个市辖区、5县个级市、56个县。);   */
INSERT INTO city VALUES(110,'合肥市',13);   
INSERT INTO city VALUES(111,'芜湖市',13);   
INSERT INTO city VALUES(112,'蚌埠市',13);   
INSERT INTO city VALUES(113,'淮南市',13);   
INSERT INTO city VALUES(114,'马鞍山市',13);   
INSERT INTO city VALUES(115,'淮北市',13);   
INSERT INTO city VALUES(116,'铜陵市',13);   
INSERT INTO city VALUES(117,'安庆市',13);   
INSERT INTO city VALUES(118,'黄山市',13);   
INSERT INTO city VALUES(119,'滁州市',13);   
INSERT INTO city VALUES(120,'阜阳市',13);   
INSERT INTO city VALUES(121,'宿州市',13);   
INSERT INTO city VALUES(122,'巢湖市',13);   
INSERT INTO city VALUES(123,'六安市',13);   
INSERT INTO city VALUES(124,'亳州市',13);   
INSERT INTO city VALUES(125,'池州市',13);   
INSERT INTO city VALUES(126,'宣城市',13);   
/*--select * from city where provincialID=13 order by cityID   
----------------------------------------------------------------   
--14福建省(2006年辖：9个地级市；26个市辖区、14个县级市、45个县。);   */
INSERT INTO city VALUES(127,'福州市',14);   
INSERT INTO city VALUES(128,'厦门市',14);   
INSERT INTO city VALUES(129,'莆田市',14);   
INSERT INTO city VALUES(130,'三明市',14);   
INSERT INTO city VALUES(131,'泉州市',14);   
INSERT INTO city VALUES(132,'漳州市',14);   
INSERT INTO city VALUES(133,'南平市',14);   
INSERT INTO city VALUES(134,'龙岩市',14);   
INSERT INTO city VALUES(135,'宁德市',14);   
/*--select * from city where provincialID=14 order by cityID   
----------------------------------------------------------------   
--15江西省(2006年全省辖：11个地级市；19个市辖区、10个县级市、70个县。);   */
INSERT INTO city VALUES(136,'南昌市',15);   
INSERT INTO city VALUES(137,'景德镇市',15);   
INSERT INTO city VALUES(138,'萍乡市',15);   
INSERT INTO city VALUES(139,'九江市',15);   
INSERT INTO city VALUES(140,'新余市',15);   
INSERT INTO city VALUES(141,'鹰潭市',15);   
INSERT INTO city VALUES(142,'赣州市',15);   
INSERT INTO city VALUES(143,'吉安市',15);   
INSERT INTO city VALUES(144,'宜春市',15);   
INSERT INTO city VALUES(145,'抚州市',15);   
INSERT INTO city VALUES(146,'上饶市',15);   
/*--select * from city where provincialID=15 order by cityID   
----------------------------------------------------------------   
--16山东省(2005年，辖：17个地级市；49个市辖区、31个县级市、60个县。);   */
INSERT INTO city VALUES(147,'济南市',16);   
INSERT INTO city VALUES(148,'青岛市',16);   
INSERT INTO city VALUES(149,'淄博市',16);   
INSERT INTO city VALUES(150,'枣庄市',16);   
INSERT INTO city VALUES(151,'东营市',16);   
INSERT INTO city VALUES(152,'烟台市',16);   
INSERT INTO city VALUES(153,'潍坊市',16);   
INSERT INTO city VALUES(154,'济宁市',16);   
INSERT INTO city VALUES(155,'泰安市',16);   
INSERT INTO city VALUES(156,'威海市',16);   
INSERT INTO city VALUES(157,'日照市',16);   
INSERT INTO city VALUES(158,'莱芜市',16);   
INSERT INTO city VALUES(159,'临沂市',16);   
INSERT INTO city VALUES(160,'德州市',16);   
INSERT INTO city VALUES(161,'聊城市',16);   
INSERT INTO city VALUES(162,'滨州市',16);   
INSERT INTO city VALUES(163,'菏泽市',16);   
/*--select * from city where provincialID=16 order by cityID   
----------------------------------------------------------------   
--17河南省 17个地级市   */
INSERT INTO city VALUES(164,'郑州市',17);   
INSERT INTO city VALUES(165,'开封市',17);   
INSERT INTO city VALUES(166,'洛阳市',17);   
INSERT INTO city VALUES(167,'平顶山市',17);   
INSERT INTO city VALUES(168,'安阳市',17);   
INSERT INTO city VALUES(169,'鹤壁市',17);   
INSERT INTO city VALUES(170,'新乡市',17);   
INSERT INTO city VALUES(171,'焦作市',17);   
INSERT INTO city VALUES(172,'濮阳市',17);   
INSERT INTO city VALUES(173,'许昌市',17);   
INSERT INTO city VALUES(174,'漯河市',17);   
INSERT INTO city VALUES(175,'三门峡市',17);   
INSERT INTO city VALUES(176,'南阳市',17);   
INSERT INTO city VALUES(177,'商丘市',17);   
INSERT INTO city VALUES(178,'信阳市',17);   
INSERT INTO city VALUES(179,'周口市',17);   
INSERT INTO city VALUES(180,'驻马店市',17);   
INSERT INTO city VALUES(181,'济源市',17);   
/*--select * from city where provincialID=17 order by cityID   
----------------------------------------------------------------   
--18湖北省（截至2005年12月31日，全省辖13个地级单位（12个地级市、1个自治州）；102县级单位（38个市辖区、24个县级市、37个县、2个自治县、1个林区），共有1220个乡级单位（277个街道、733个镇、210个乡）。）   */
INSERT INTO city VALUES(182,'武汉市',18);   
INSERT INTO city VALUES(183,'黄石市',18);   
INSERT INTO city VALUES(184,'十堰市',18);   
INSERT INTO city VALUES(185,'荆州市',18);   
INSERT INTO city VALUES(186,'宜昌市',18);   
INSERT INTO city VALUES(187,'襄樊市',18);   
INSERT INTO city VALUES(188,'鄂州市',18);   
INSERT INTO city VALUES(189,'荆门市',18);   
INSERT INTO city VALUES(190,'孝感市',18);   
INSERT INTO city VALUES(191,'黄冈市',18);   
INSERT INTO city VALUES(192,'咸宁市',18);   
INSERT INTO city VALUES(193,'随州市',18);   
INSERT INTO city VALUES(194,'仙桃市',18);   
INSERT INTO city VALUES(195,'天门市',18);   
INSERT INTO city VALUES(196,'潜江市',18);   
INSERT INTO city VALUES(197,'神农架林区',18);   
INSERT INTO city VALUES(198,'恩施土家族苗族自治州',18);   
/*--select * from city where provincialID=18 order by cityID   
----------------------------------------------------------------   
--19湖南省（2005年辖：13个地级市、1个自治州；34个市辖区、16个县级市、65个县、7个自治县。）   */
INSERT INTO city VALUES(199,'长沙市',19);   
INSERT INTO city VALUES(200,'株洲市',19);   
INSERT INTO city VALUES(201,'湘潭市',19);   
INSERT INTO city VALUES(202,'衡阳市',19);   
INSERT INTO city VALUES(203,'邵阳市',19);   
INSERT INTO city VALUES(204,'岳阳市',19);   
INSERT INTO city VALUES(205,'常德市',19);   
INSERT INTO city VALUES(206,'张家界市',19);   
INSERT INTO city VALUES(207,'益阳市',19);   
INSERT INTO city VALUES(208,'郴州市',19);   
INSERT INTO city VALUES(209,'永州市',19);   
INSERT INTO city VALUES(210,'怀化市',19);   
INSERT INTO city VALUES(211,'娄底市',19);   
INSERT INTO city VALUES(212,'湘西土家族苗族自治州',19);   
/*--select * from city where provincialID=19 order by cityID   
----------------------------------------------------------------   
--20广东省（截至2005年12月31日，广东省辖：21个地级市，54个市辖区、23个县级市、41个县、3个自治县，429个街道办事处、1145个镇、4个乡、7个民族乡。）   */
INSERT INTO city VALUES(213,'广州市',20);   
INSERT INTO city VALUES(214,'深圳市',20);   
INSERT INTO city VALUES(215,'珠海市',20);   
INSERT INTO city VALUES(216,'汕头市',20);   
INSERT INTO city VALUES(217,'韶关市',20);   
INSERT INTO city VALUES(218,'佛山市',20);   
INSERT INTO city VALUES(219,'江门市',20);   
INSERT INTO city VALUES(220,'湛江市',20);   
INSERT INTO city VALUES(221,'茂名市',20);   
INSERT INTO city VALUES(222,'肇庆市',20);   
INSERT INTO city VALUES(223,'惠州市',20);   
INSERT INTO city VALUES(224,'梅州市',20);   
INSERT INTO city VALUES(225,'汕尾市',20);   
INSERT INTO city VALUES(226,'河源市',20);   
INSERT INTO city VALUES(227,'阳江市',20);   
INSERT INTO city VALUES(228,'清远市',20);   
INSERT INTO city VALUES(229,'东莞市',20);   
INSERT INTO city VALUES(230,'中山市',20);   
INSERT INTO city VALUES(231,'潮州市',20);   
INSERT INTO city VALUES(232,'揭阳市',20);   
INSERT INTO city VALUES(233,'云浮市',20);   
/*--select * from city where provincialID=20 order by cityID   
----------------------------------------------------------------   
--21甘肃省 12个地级市、2个自治州   */
INSERT INTO city VALUES(234,'兰州市',21);   
INSERT INTO city VALUES(235,'金昌市',21);   
INSERT INTO city VALUES(236,'白银市',21);   
INSERT INTO city VALUES(237,'天水市',21);   
INSERT INTO city VALUES(238,'嘉峪关市',21);   
INSERT INTO city VALUES(239,'武威市',21);   
INSERT INTO city VALUES(240,'张掖市',21);   
INSERT INTO city VALUES(241,'平凉市',21);   
INSERT INTO city VALUES(242,'酒泉市',21);   
INSERT INTO city VALUES(243,'庆阳市',21);   
INSERT INTO city VALUES(244,'定西市',21);   
INSERT INTO city VALUES(245,'陇南市',21);   
INSERT INTO city VALUES(246,'临夏回族自治州',21);   
INSERT INTO city VALUES(247,'甘南藏族自治州',21);   
/*--select * from city where provincialID=21 order by cityID   
----------------------------------------------------------------   
--22四川省18个地级市、3个自治州   */
INSERT INTO city VALUES(248,'成都市',22);   
INSERT INTO city VALUES(249,'自贡市',22);   
INSERT INTO city VALUES(250,'攀枝花市',22);   
INSERT INTO city VALUES(251,'泸州市',22);   
INSERT INTO city VALUES(252,'德阳市',22);   
INSERT INTO city VALUES(253,'绵阳市',22);   
INSERT INTO city VALUES(254,'广元市',22);   
INSERT INTO city VALUES(255,'遂宁市',22);   
INSERT INTO city VALUES(256,'内江市',22);   
INSERT INTO city VALUES(257,'乐山市',22);   
INSERT INTO city VALUES(258,'南充市',22);   
INSERT INTO city VALUES(259,'眉山市',22);   
INSERT INTO city VALUES(260,'宜宾市',22);   
INSERT INTO city VALUES(261,'广安市',22);   
INSERT INTO city VALUES(262,'达州市',22);   
INSERT INTO city VALUES(263,'雅安市',22);   
INSERT INTO city VALUES(264,'巴中市',22);   
INSERT INTO city VALUES(265,'资阳市',22);   
INSERT INTO city VALUES(266,'阿坝藏族羌族自治州',22);   
INSERT INTO city VALUES(267,'甘孜藏族自治州',22);   
INSERT INTO city VALUES(268,'凉山彝族自治州',22);   
/*--select * from city where provincialID=22 order by cityID   
----------------------------------------------------------------   
--23贵州省(2006年辖：4个地级市、2个地区、3个自治州；10个市辖区、9个县级市、56个县、11个自治县、2个特区。);   */
INSERT INTO city VALUES(269,'贵阳市',23);   
INSERT INTO city VALUES(270,'六盘水市',23);   
INSERT INTO city VALUES(271,'遵义市',23);   
INSERT INTO city VALUES(272,'安顺市',23);   
INSERT INTO city VALUES(273,'铜仁地区',23);   
INSERT INTO city VALUES(274,'毕节地区',23);   
INSERT INTO city VALUES(275,'黔西南布依族苗族自治州',23);   
INSERT INTO city VALUES(276,'黔东南苗族侗族自治州',23);   
INSERT INTO city VALUES(277,'黔南布依族苗族自治州',23);   
/*--select * from city where provincialID=23 order by cityID   
----------------------------------------------------------------   
--24海南省全省有2个地级市，6个县级市，4个县，6个民族自治县，4个市辖区，1个办事处（西南中沙群岛办事处 ，县级）。);   */
INSERT INTO city VALUES(278,'海口市',24);   
INSERT INTO city VALUES(279,'三亚市',24);   
INSERT INTO city VALUES(280,'五指山市',24);   
INSERT INTO city VALUES(281,'琼海市',24);   
INSERT INTO city VALUES(282,'儋州市',24);   
INSERT INTO city VALUES(283,'文昌市',24);   
INSERT INTO city VALUES(284,'万宁市',24);   
INSERT INTO city VALUES(285,'东方市',24);   
INSERT INTO city VALUES(286,'澄迈县',24);   
INSERT INTO city VALUES(287,'定安县',24);   
INSERT INTO city VALUES(288,'屯昌县',24);   
INSERT INTO city VALUES(289,'临高县',24);   
INSERT INTO city VALUES(290,'白沙黎族自治县',24);   
INSERT INTO city VALUES(291,'昌江黎族自治县',24);   
INSERT INTO city VALUES(292,'乐东黎族自治县',24);   
INSERT INTO city VALUES(293,'陵水黎族自治县',24);   
INSERT INTO city VALUES(294,'保亭黎族苗族自治县',24);   
INSERT INTO city VALUES(295,'琼中黎族苗族自治县',24);   
/*--select * from city where provincialID=24 order by cityID   
----------------------------------------------------------------   
--25云南省(2006年辖：8个地级市、8个自治州；12个市辖区、9个县级市、79个县、29个自治县。);   */
INSERT INTO city VALUES(296,'昆明市',25);   
INSERT INTO city VALUES(297,'曲靖市',25);   
INSERT INTO city VALUES(298,'玉溪市',25);   
INSERT INTO city VALUES(299,'保山市',25);   
INSERT INTO city VALUES(300,'昭通市',25);   
INSERT INTO city VALUES(301,'丽江市',25);   
INSERT INTO city VALUES(302,'思茅市',25);   
INSERT INTO city VALUES(303,'临沧市',25);   
INSERT INTO city VALUES(304,'文山壮族苗族自治州',25);   
INSERT INTO city VALUES(305,'红河哈尼族彝族自治州',25);   
INSERT INTO city VALUES(306,'西双版纳傣族自治州',25);   
INSERT INTO city VALUES(307,'楚雄彝族自治州',25);   
INSERT INTO city VALUES(308,'大理白族自治州',25);   
INSERT INTO city VALUES(309,'德宏傣族景颇族自治州',25);   
INSERT INTO city VALUES(310,'怒江傈傈族自治州',25);   
INSERT INTO city VALUES(311,'迪庆藏族自治州',25);   
/*--select * from city where provincialID=25 order by cityID   
----------------------------------------------------------------   
--26青海省(2006年辖：1个地级市、1个地区、6个自治州；4个市辖区、2个县级市、30个县、7个自治县。);   */
INSERT INTO city VALUES(312,'西宁市',26);   
INSERT INTO city VALUES(313,'海东地区',26);   
INSERT INTO city VALUES(314,'海北藏族自治州',26);   
INSERT INTO city VALUES(315,'黄南藏族自治州',26);   
INSERT INTO city VALUES(316,'海南藏族自治州',26);   
INSERT INTO city VALUES(317,'果洛藏族自治州',26);   
INSERT INTO city VALUES(318,'玉树藏族自治州',26);   
INSERT INTO city VALUES(319,'海西蒙古族藏族自治州',26);   
/*--select * from city where provincialID=26 order by cityID   
----------------------------------------------------------------   
--27陕西省(2006年辖：10个地级市；24个市辖区、3个县级市、80个县。);   */
INSERT INTO city VALUES(320,'西安市',27);   
INSERT INTO city VALUES(321,'铜川市',27);   
INSERT INTO city VALUES(322,'宝鸡市',27);   
INSERT INTO city VALUES(323,'咸阳市',27);   
INSERT INTO city VALUES(324,'渭南市',27);   
INSERT INTO city VALUES(325,'延安市',27);   
INSERT INTO city VALUES(326,'汉中市',27);   
INSERT INTO city VALUES(327,'榆林市',27);   
INSERT INTO city VALUES(328,'安康市',27);   
INSERT INTO city VALUES(329,'商洛市',27);   
/*--select * from city where provincialID=27 order by cityID   
----------------------------------------------------------------   
--28广西壮族自治区(2005年辖：14个地级市；34个市辖区、7个县级市、56个县、12个自治县。);   */
INSERT INTO city VALUES(330,'南宁市',28);   
INSERT INTO city VALUES(331,'柳州市',28);   
INSERT INTO city VALUES(332,'桂林市',28);   
INSERT INTO city VALUES(333,'梧州市',28);   
INSERT INTO city VALUES(334,'北海市',28);   
INSERT INTO city VALUES(335,'防城港市',28);   
INSERT INTO city VALUES(336,'钦州市',28);   
INSERT INTO city VALUES(337,'贵港市',28);   
INSERT INTO city VALUES(338,'玉林市',28);   
INSERT INTO city VALUES(339,'百色市',28);   
INSERT INTO city VALUES(340,'贺州市',28);   
INSERT INTO city VALUES(341,'河池市',28);   
INSERT INTO city VALUES(342,'来宾市',28);   
INSERT INTO city VALUES(343,'崇左市',28);   
/*--select * from city where provincialID=28 order by cityID   
----------------------------------------------------------------   
--29西藏自治区(2005年辖：1个地级市、6个地区；1个市辖区、1个县级市、71个县。);   */

INSERT INTO city VALUES(344,'拉萨市',29);   
INSERT INTO city VALUES(345,'那曲地区',29);   
INSERT INTO city VALUES(346,'昌都地区',29);   
INSERT INTO city VALUES(347,'山南地区',29);   
INSERT INTO city VALUES(348,'日喀则地区',29);   
INSERT INTO city VALUES(349,'阿里地区',29);   
INSERT INTO city VALUES(350,'林芝地区',29);   
/*--select * from city where provincialID=29 order by cityID   
----------------------------------------------------------------   
--30宁夏回族自治区   */
INSERT INTO city VALUES(351,'银川市',30);   
INSERT INTO city VALUES(352,'石嘴山市',30);   
INSERT INTO city VALUES(353,'吴忠市',30);   
INSERT INTO city VALUES(354,'固原市',30);   
INSERT INTO city VALUES(355,'中卫市',30);   
/*--select * from city where provincialID=30 order by cityID   
----------------------------------------------------------------   
--31新疆维吾尔自治区(2005年辖：2个地级市、7个地区、5个自治州；11个市辖区、20个县级市、62个县、6个自治县);   */
INSERT INTO city VALUES(356,'乌鲁木齐市',31);   
INSERT INTO city VALUES(357,'克拉玛依市',31);   
INSERT INTO city VALUES(358,'石河子市　',31);   
INSERT INTO city VALUES(359,'阿拉尔市',31);   
INSERT INTO city VALUES(360,'图木舒克市',31);   
INSERT INTO city VALUES(361,'五家渠市',31);   
INSERT INTO city VALUES(362,'吐鲁番市',31);   
INSERT INTO city VALUES(363,'阿克苏市',31);   
INSERT INTO city VALUES(364,'喀什市',31);   
INSERT INTO city VALUES(365,'哈密市',31);   
INSERT INTO city VALUES(366,'和田市',31);   
INSERT INTO city VALUES(367,'阿图什市',31);   
INSERT INTO city VALUES(368,'库尔勒市',31);   
INSERT INTO city VALUES(369,'昌吉市　',31);   
INSERT INTO city VALUES(370,'阜康市',31);   
INSERT INTO city VALUES(371,'米泉市',31);   
INSERT INTO city VALUES(372,'博乐市',31);   
INSERT INTO city VALUES(373,'伊宁市',31);   
INSERT INTO city VALUES(374,'奎屯市',31);   
INSERT INTO city VALUES(375,'塔城市',31);   
INSERT INTO city VALUES(376,'乌苏市',31);   
INSERT INTO city VALUES(377,'阿勒泰市',31);   
/*--select * from city where provincialID=31 order by cityID   
----------------------------------------------------------------   
--32内蒙古自治区(2006年，辖：9个地级市、3个盟；21个市辖区、11个县级市、17个县、49个旗、3个自治旗。);   */
INSERT INTO city VALUES(378,'呼和浩特市',32);   
INSERT INTO city VALUES(379,'包头市',32);   
INSERT INTO city VALUES(380,'乌海市',32);   
INSERT INTO city VALUES(381,'赤峰市',32);   
INSERT INTO city VALUES(382,'通辽市',32);   
INSERT INTO city VALUES(383,'鄂尔多斯市',32);   
INSERT INTO city VALUES(384,'呼伦贝尔市',32);   
INSERT INTO city VALUES(385,'巴彦淖尔市',32);   
INSERT INTO city VALUES(386,'乌兰察布市',32);   
INSERT INTO city VALUES(387,'锡林郭勒盟',32);   
INSERT INTO city VALUES(388,'兴安盟',32);   
INSERT INTO city VALUES(389,'阿拉善盟',32);   
/*--select * from city where provincialID=32 order by    
----------------------------------------------------------------   
--33澳门特别行政区   */
INSERT INTO city VALUES(390,'澳门特别行政区',33);   
/*--select * from city where provincialID=33 order by cityID   
----------------------------------------------------------------   
--34香港特别行政区   */
INSERT INTO city VALUES(391,'香港特别行政区',34);   
/*--select * from city where provincialID=34 order by cityID   
----------------------------------------------------------------  */

关联查询SQL语句：

SELECT 
pro.*, 
ci.cityID, 
ci.cityName 
FROM provincial AS pro 
LEFT JOIN city AS ci ON pro.provincialID=ci.provincialID