mysql中如何實(shí)現(xiàn)全國(guó)省市級(jí)聯(lián)數(shù)據(jù)sql語(yǔ)句

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

--省級(jí)   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,'臺(tái)灣省'); 
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,'廣西壯族自治區(qū)'); 
insert into provincial values(29,'西藏自治區(qū)'); 
insert into provincial values(30,'寧夏回族自治區(qū)'); 
insert into provincial values(31,'新疆維吾爾自治區(qū)'); 
insert into provincial values(32,'內(nèi)蒙古自治區(qū)'); 
insert into provincial values(33,'澳門(mén)特別行政區(qū)'); 
insert into provincial values(34,'香港特別行政區(qū)'); 
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); 
      
----------------------------------------------------------------------------------------------------------------------------------------   
---------------------------------------------------------------   
--插入各個(gè)省的城市數(shù)據(jù)   
--4個(gè)直轄市   
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個(gè)地級(jí)市   
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,'邢臺(tái)市',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個(gè)城市   
insert into city values(16,'太原市',6);   
insert into city values(17,'大同市',6);   
insert into city values(18,'陽(yáng)泉市',6);   
insert into city values(19,'長(zhǎng)治市',6);   
insert into city values(20,'晉城市',6);   
insert into city values(21,'朔州市',6);   
insert into city values(22,'晉中市',6);   
insert into city values(23,'運(yùn)城市',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臺(tái)灣省(臺(tái)灣本島和澎湖共設(shè)7市、16縣，其中臺(tái)北市和高雄市為“院轄市”，直屬“行政院”，其余屬臺(tái)灣省；市下設(shè)區(qū)，縣下設(shè)市（縣轄市）、鎮(zhèn)、鄉(xiāng)，合稱(chēng)區(qū)市鎮(zhèn)鄉(xiāng)。);   
insert into city values(27,'臺(tái)北市',7);   
insert into city values(28,'高雄市',7);   
insert into city values(29,'基隆市',7);   
insert into city values(30,'臺(tái)中市',7);   
insert into city values(31,'臺(tái)南市',7);   
insert into city values(32,'新竹市',7);   
insert into city values(33,'嘉義市',7);   
insert into city values(34,'臺(tái)北縣',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,'臺(tái)中縣',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,'臺(tái)南縣',7);   
insert into city values(45,'高雄縣',7);   
insert into city values(46,'屏東縣',7);   
insert into city values(47,'澎湖縣',7);   
insert into city values(48,'臺(tái)東縣',7);   
insert into city values(49,'花蓮縣',7);   
--select * from city where provincialID=7 order by cityID   
----------------------------------------------------------------   
--8遼寧省 14個(gè)地級(jí)市   
insert into city values(50,'沈陽(yáng)市',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,'營(yíng)口市',8);   
insert into city values(58,'阜新市',8);   
insert into city values(59,'遼陽(yáng)市',8);   
insert into city values(60,'盤(pán)錦市',8);   
insert into city values(61,'鐵嶺市',8);   
insert into city values(62,'朝陽(yáng)市',8);   
insert into city values(63,'葫蘆島市',8);   
--select * from city where provincialID=8 order by cityID   
----------------------------------------------------------------   
--9吉林省(2006年，轄：8個(gè)地級(jí)市、1個(gè)自治州；20個(gè)市轄區(qū)、20個(gè)縣級(jí)市、17個(gè)縣、3個(gè)自治縣。);   
insert into city values(64,'長(zhǎng)春市',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地級(jí)市、1地區(qū)；64市轄區(qū)、18縣級(jí)市、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,'七臺(tái)河市',10);   
insert into city values(83,'黑 河 市',10);   
insert into city values(84,'綏 化 市',10);   
insert into city values(85,'大興安嶺地區(qū)',10);   
--select * from city where provincialID=10 order by cityID   
----------------------------------------------------------------   
--11江蘇省(2005年轄：13個(gè)地級(jí)市；54個(gè)市轄區(qū)、27個(gè)縣級(jí)市、25個(gè)縣);   
insert into city values(86,'南京市',11);   
insert into city values(87,'無(wú)錫市',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,'揚(yáng)州市',11);   
insert into city values(96,'鎮(zhèn)江市',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個(gè)地級(jí)市；32個(gè)市轄區(qū)、22個(gè)縣級(jí)市、35個(gè)縣、1個(gè)自治縣。);   
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,'臺(tái)州市',12);   
insert into city values(109,'麗水市',12);   
--select * from city where provincialID=12 order by cityID   
----------------------------------------------------------------   
--13安徽省(2005年轄：17個(gè)地級(jí)市；44個(gè)市轄區(qū)、5縣個(gè)級(jí)市、56個(gè)縣。);   
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,'阜陽(yáng)市',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個(gè)地級(jí)市；26個(gè)市轄區(qū)、14個(gè)縣級(jí)市、45個(gè)縣。);   
insert into city values(127,'福州市',14);   
insert into city values(128,'廈門(mén)市',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個(gè)地級(jí)市；19個(gè)市轄區(qū)、10個(gè)縣級(jí)市、70個(gè)縣。);   
insert into city values(136,'南昌市',15);   
insert into city values(137,'景德鎮(zhèn)市',15);   
insert into city values(138,'萍鄉(xiāng)市',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個(gè)地級(jí)市；49個(gè)市轄區(qū)、31個(gè)縣級(jí)市、60個(gè)縣。);   
insert into city values(147,'濟(jì)南市',16);   
insert into city values(148,'青島市',16);   
insert into city values(149,'淄博市',16);   
insert into city values(150,'棗莊市',16);   
insert into city values(151,'東營(yíng)市',16);   
insert into city values(152,'煙臺(tái)市',16);   
insert into city values(153,'濰坊市',16);   
insert into city values(154,'濟(jì)寧市',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個(gè)地級(jí)市   
insert into city values(164,'鄭州市',17);   
insert into city values(165,'開(kāi)封市',17);   
insert into city values(166,'洛陽(yáng)市',17);   
insert into city values(167,'平頂山市',17);   
insert into city values(168,'安陽(yáng)市',17);   
insert into city values(169,'鶴壁市',17);   
insert into city values(170,'新鄉(xiāng)市',17);   
insert into city values(171,'焦作市',17);   
insert into city values(172,'濮陽(yáng)市',17);   
insert into city values(173,'許昌市',17);   
insert into city values(174,'漯河市',17);   
insert into city values(175,'三門(mén)峽市',17);   
insert into city values(176,'南陽(yáng)市',17);   
insert into city values(177,'商丘市',17);   
insert into city values(178,'信陽(yáng)市',17);   
insert into city values(179,'周口市',17);   
insert into city values(180,'駐馬店市',17);   
insert into city values(181,'濟(jì)源市',17);   
--select * from city where provincialID=17 order by cityID   
----------------------------------------------------------------   
--18湖北?。ń刂?005年12月31日，全省轄13個(gè)地級(jí)單位（12個(gè)地級(jí)市、1個(gè)自治州）；102縣級(jí)單位（38個(gè)市轄區(qū)、24個(gè)縣級(jí)市、37個(gè)縣、2個(gè)自治縣、1個(gè)林區(qū)），共有1220個(gè)鄉(xiāng)級(jí)單位（277個(gè)街道、733個(gè)鎮(zhèn)、210個(gè)鄉(xiāng)）。）   
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,'荊門(mén)市',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,'天門(mén)市',18);   
insert into city values(196,'潛江市',18);   
insert into city values(197,'神農(nóng)架林區(qū)',18);   
insert into city values(198,'恩施土家族苗族自治州',18);   
--select * from city where provincialID=18 order by cityID   
----------------------------------------------------------------   
--19湖南?。?005年轄：13個(gè)地級(jí)市、1個(gè)自治州；34個(gè)市轄區(qū)、16個(gè)縣級(jí)市、65個(gè)縣、7個(gè)自治縣。）   
insert into city values(199,'長(zhǎng)沙市',19);   
insert into city values(200,'株洲市',19);   
insert into city values(201,'湘潭市',19);   
insert into city values(202,'衡陽(yáng)市',19);   
insert into city values(203,'邵陽(yáng)市',19);   
insert into city values(204,'岳陽(yáng)市',19);   
insert into city values(205,'常德市',19);   
insert into city values(206,'張家界市',19);   
insert into city values(207,'益陽(yáng)市',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廣東?。ń刂?005年12月31日，廣東省轄：21個(gè)地級(jí)市，54個(gè)市轄區(qū)、23個(gè)縣級(jí)市、41個(gè)縣、3個(gè)自治縣，429個(gè)街道辦事處、1145個(gè)鎮(zhèn)、4個(gè)鄉(xiāng)、7個(gè)民族鄉(xiāng)。）   
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,'韶關(guān)市',20);   
insert into city values(218,'佛山市',20);   
insert into city values(219,'江門(mén)市',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,'陽(yáng)江市',20);   
insert into city values(228,'清遠(yuǎn)市',20);   
insert into city values(229,'東莞市',20);   
insert into city values(230,'中山市',20);   
insert into city values(231,'潮州市',20);   
insert into city values(232,'揭陽(yáng)市',20);   
insert into city values(233,'云浮市',20);   
--select * from city where provincialID=20 order by cityID   
----------------------------------------------------------------   
--21甘肅省 12個(gè)地級(jí)市、2個(gè)自治州   
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,'嘉峪關(guān)市',21);   
insert into city values(239,'武威市',21);   
insert into city values(240,'張掖市',21);   
insert into city values(241,'平?jīng)鍪?#39;,21);   
insert into city values(242,'酒泉市',21);   
insert into city values(243,'慶陽(yáng)市',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個(gè)地級(jí)市、3個(gè)自治州   
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,'德陽(yáng)市',22);   
insert into city values(253,'綿陽(yáng)市',22);   
insert into city values(254,'廣元市',22);   
insert into city values(255,'遂寧市',22);   
insert into city values(256,'內(nèi)江市',22);   
insert into city values(257,'樂(lè)山市',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,'達(dá)州市',22);   
insert into city values(263,'雅安市',22);   
insert into city values(264,'巴中市',22);   
insert into city values(265,'資陽(yáng)市',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個(gè)地級(jí)市、2個(gè)地區(qū)、3個(gè)自治州；10個(gè)市轄區(qū)、9個(gè)縣級(jí)市、56個(gè)縣、11個(gè)自治縣、2個(gè)特區(qū)。);   
insert into city values(269,'貴陽(yáng)市',23);   
insert into city values(270,'六盤(pán)水市',23);   
insert into city values(271,'遵義市',23);   
insert into city values(272,'安順市',23);   
insert into city values(273,'銅仁地區(qū)',23);   
insert into city values(274,'畢節(jié)地區(qū)',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個(gè)地級(jí)市，6個(gè)縣級(jí)市，4個(gè)縣，6個(gè)民族自治縣，4個(gè)市轄區(qū)，1個(gè)辦事處（西南中沙群島辦事處 ，縣級(jí)）。);   
insert into city values(278,'?？谑?#39;,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,'萬(wàn)寧市',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,'樂(lè)東黎族自治縣',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個(gè)地級(jí)市、8個(gè)自治州；12個(gè)市轄區(qū)、9個(gè)縣級(jí)市、79個(gè)縣、29個(gè)自治縣。);   
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個(gè)地級(jí)市、1個(gè)地區(qū)、6個(gè)自治州；4個(gè)市轄區(qū)、2個(gè)縣級(jí)市、30個(gè)縣、7個(gè)自治縣。);   
insert into city values(312,'西寧市',26);   
insert into city values(313,'海東地區(qū)',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,'玉樹(shù)藏族自治州',26);   
insert into city values(319,'海西蒙古族藏族自治州',26);   
--select * from city where provincialID=26 order by cityID   
----------------------------------------------------------------   
--27陜西省(2006年轄：10個(gè)地級(jí)市；24個(gè)市轄區(qū)、3個(gè)縣級(jí)市、80個(gè)縣。);   
insert into city values(320,'西安市',27);   
insert into city values(321,'銅川市',27);   
insert into city values(322,'寶雞市',27);   
insert into city values(323,'咸陽(yáng)市',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廣西壯族自治區(qū)(2005年轄：14個(gè)地級(jí)市；34個(gè)市轄區(qū)、7個(gè)縣級(jí)市、56個(gè)縣、12個(gè)自治縣。);   
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,'來(lái)賓市',28);   
insert into city values(343,'崇左市',28);   
--select * from city where provincialID=28 order by cityID   
----------------------------------------------------------------   
--29西藏自治區(qū)(2005年轄：1個(gè)地級(jí)市、6個(gè)地區(qū)；1個(gè)市轄區(qū)、1個(gè)縣級(jí)市、71個(gè)縣。);   
insert into city values(344,'拉薩市',29);   
insert into city values(345,'那曲地區(qū)',29);   
insert into city values(346,'昌都地區(qū)',29);   
insert into city values(347,'山南地區(qū)',29);   
insert into city values(348,'日喀則地區(qū)',29);   
insert into city values(349,'阿里地區(qū)',29);   
insert into city values(350,'林芝地區(qū)',29);   
--select * from city where provincialID=29 order by cityID   
----------------------------------------------------------------   
--30寧夏回族自治區(qū)   
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,'中衛(wèi)市',30);   
--select * from city where provincialID=30 order by cityID   
----------------------------------------------------------------   
--31新疆維吾爾自治區(qū)(2005年轄：2個(gè)地級(jí)市、7個(gè)地區(qū)、5個(gè)自治州；11個(gè)市轄區(qū)、20個(gè)縣級(jí)市、62個(gè)縣、6個(gè)自治縣);   
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,'庫(kù)爾勒市',31);   
insert into city values(369,'昌吉市　',31);   
insert into city values(370,'阜康市',31);   
insert into city values(371,'米泉市',31);   
insert into city values(372,'博樂(lè)市',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內(nèi)蒙古自治區(qū)(2006年，轄：9個(gè)地級(jí)市、3個(gè)盟；21個(gè)市轄區(qū)、11個(gè)縣級(jí)市、17個(gè)縣、49個(gè)旗、3個(gè)自治旗。);   
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澳門(mén)特別行政區(qū)   
insert into city values(390,'澳門(mén)特別行政區(qū)',33);   
--select * from city where provincialID=33 order by cityID   
----------------------------------------------------------------   
--34香港特別行政區(qū)   
insert into city values(391,'香港特別行政區(qū)',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; 

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“mysql中如何實(shí)現(xiàn)全國(guó)省市級(jí)聯(lián)數(shù)據(jù)sql語(yǔ)句”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!