mysql中如何實現全國省市級聯數據sql語句

這篇文章主要介紹了mysql中如何實現全國省市級聯數據sql語句，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

--省級   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   
----------------------------------------------------------------  



---查詢視圖 
create or replace view pro_v_city 
as 
select 
pro.*, 
ci.cityID, 
ci.cityName 
from provincial as pro 

left join city as ci on pro.provincialID=ci.provincialID; 

感謝你能夠認真閱讀完這篇文章，希望小編分享的“mysql中如何實現全國省市級聯數據sql語句”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!