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语句”这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!