這篇文章主要介紹了MySQL中如何實(shí)現(xiàn)全國省市級(jí)聯(lián)數(shù)據(jù)sql語句，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
創(chuàng)新互聯(lián)公司是創(chuàng)新、創(chuàng)意、研發(fā)型一體的綜合型網(wǎng)站建設(shè)公司，自成立以來公司不斷探索創(chuàng)新，始終堅(jiān)持為客戶提供滿意周到的服務(wù)，在本地打下了良好的口碑，在過去的10年時(shí)間我們累計(jì)服務(wù)了上千家以及全國政企客戶，如混凝土攪拌機(jī)等企業(yè)單位，完善的項(xiàng)目管理流程，嚴(yán)格把控項(xiàng)目進(jìn)度與質(zhì)量監(jiān)控加上過硬的技術(shù)實(shí)力獲得客戶的一致稱譽(yù)。
--省級(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,'澳門特別行政區(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,'陽泉市',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,'運(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)，合稱區(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,'沈陽市',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個(gè)地級(jí)市、1個(gè)自治州；20個(gè)市轄區(qū)、20個(gè)縣級(jí)市、17個(gè)縣、3個(gè)自治縣。);
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地級(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,'無錫市',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,'阜陽市',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,'廈門市',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,'東營市',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,'開封市',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,'新鄉(xiāng)市',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,'濟(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,'荊門市',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,'神農(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,'長沙市',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個(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,'江門市',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,'清遠(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,'揭陽市',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,'慶陽市',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,'德陽市',22);
insert into city values(253,'綿陽市',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,'樂山市',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,'資陽市',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,'貴陽市',23);
insert into city values(270,'六盤水市',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,'海口市',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個(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,'玉樹藏族自治州',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,'咸陽市',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,'來賓市',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,'庫爾勒市',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內(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澳門特別行政區(qū)
insert into city values(390,'澳門特別行政區(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)全國省市級(jí)聯(lián)數(shù)據(jù)sql語句”這篇文章對大家有幫助，同時(shí)也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來學(xué)習(xí)!
重慶分公司
重慶分公司
