全国省市级联数据sql语句 mysql版

--省级 provincial
2

create table provincial
3

(
4

provincialID int,
5

provincialName varchar(50),
6

primary key (provincialID)
7

)engine=INNODB default charset=gbk;
8

insert into provincial values(1,'北京市');
10

insert into provincial values(2,'天津市');
11

insert into provincial values(3,'上海市');
12

insert into provincial values(4,'重庆市');
13

insert into provincial values(5,'河北省');
14

insert into provincial values(6,'山西省');
15

insert into provincial values(7,'台湾省');
16

insert into provincial values(8,'辽宁省');
17

insert into provincial values(9,'吉林省');
18

insert into provincial values(10,'黑龙江省');
19

insert into provincial values(11,'江苏省');
20

insert into provincial values(12,'浙江省');
21

insert into provincial values(13,'安徽省');
22

insert into provincial values(14,'福建省');
23

insert into provincial values(15,'江西省');
24

insert into provincial values(16,'山东省');
25

insert into provincial values(17,'河南省');
26

insert into provincial values(18,'湖北省');
27

insert into provincial values(19,'湖南省');
28

insert into provincial values(20,'广东省');
29

insert into provincial values(21,'甘肃省');
30

insert into provincial values(22,'四川省');
31

insert into provincial values(23,'贵州省');
32

insert into provincial values(24,'海南省');
33

insert into provincial values(25,'云南省');
34

insert into provincial values(26,'青海省');
35

insert into provincial values(27,'陕西省');
36

insert into provincial values(28,'广西壮族自治区');
37

insert into provincial values(29,'西藏自治区');
38

insert into provincial values(30,'宁夏回族自治区');
39

insert into provincial values(31,'新疆维吾尔自治区');
40

insert into provincial values(32,'内蒙古自治区');
41

insert into provincial values(33,'澳门特别行政区');
42

insert into provincial values(34,'香港特别行政区');
43

select provincialID,provincialName from provincial;
44

--城市 city
46

create table city
47

(
48

cityID int not null,
49

cityName varchar(50) not null,
50

provincialID int not null,
51

primary key (cityID)
52

)engine=INNODB default charset=gbk;
53

alter table city add constraint FK_pro_city_provincialID foreign key (provincialID)
55

references provincial (provincialID);
56

----------------------------------------------------------------------------------------------------------------------------------------
58

---------------------------------------------------------------
59

--插入各个省的城市数据
60

--4个直辖市
61

insert into city values(1,'北京市',1);
62

insert into city values(2,'天津市',2);
63

insert into city values(3,'上海市',3);
64

insert into city values(4,'重庆市',4);
65

--select * from city where provincialID=4
66

----------------------------------------------------------------
67

--5河北省 11个地级市
68

insert into city values(5,'石家庄市',5);
69

insert into city values(6,'唐山市',5);
70

insert into city values(7,'秦皇岛市',5);
71

insert into city values(8,'邯郸市',5);
72

insert into city values(9,'邢台市',5);
73

insert into city values(10,'保定市',5);
74

insert into city values(11,'张家口市',5);
75

insert into city values(12,'承德市',5);
76

insert into city values(13,'沧州市',5);
77

insert into city values(14,'廊坊市',5);
78

insert into city values(15,'衡水市',5);
79

--select * from city where provincialID=5 order by cityID
80

----------------------------------------------------------------
81

--6山西省 11个城市
82

insert into city values(16,'太原市',6);
83

insert into city values(17,'大同市',6);
84

insert into city values(18,'阳泉市',6);
85

insert into city values(19,'长治市',6);
86

insert into city values(20,'晋城市',6);
87

insert into city values(21,'朔州市',6);
88

insert into city values(22,'晋中市',6);
89

insert into city values(23,'运城市',6);
90

insert into city values(24,'忻州市',6);
91

insert into city values(25,'临汾市',6);
92

insert into city values(26,'吕梁市',6);
93

--select * from city where provincialID=6 order by cityID
94

----------------------------------------------------------------
95

--7台湾省(台湾本岛和澎湖共设7市、16县，其中台北市和高雄市为“院辖市”，直属“行政院”，其余属台湾省；市下设区，县下设市（县辖市）、镇、乡，合称区市镇乡。);
96

insert into city values(27,'台北市',7);
97

insert into city values(28,'高雄市',7);
98

insert into city values(29,'基隆市',7);
99

insert into city values(30,'台中市',7);
100

insert into city values(31,'台南市',7);
101

insert into city values(32,'新竹市',7);
102

insert into city values(33,'嘉义市',7);
103

insert into city values(34,'台北县',7);
104

insert into city values(35,'宜兰县',7);
105

insert into city values(36,'桃园县',7);
106

insert into city values(37,'新竹县',7);
107

insert into city values(38,'苗栗县',7);
108

insert into city values(39,'台中县',7);
109

insert into city values(40,'彰化县',7);
110

insert into city values(41,'南投县',7);
111

insert into city values(42,'云林县',7);
112

insert into city values(43,'嘉义县',7);
113

insert into city values(44,'台南县',7);
114

insert into city values(45,'高雄县',7);
115

insert into city values(46,'屏东县',7);
116

insert into city values(47,'澎湖县',7);
117

insert into city values(48,'台东县',7);
118

insert into city values(49,'花莲县',7);
119

--select * from city where provincialID=7 order by cityID
120

----------------------------------------------------------------
121

--8辽宁省 14个地级市
122

insert into city values(50,'沈阳市',8);
123

insert into city values(51,'大连市',8);
124

insert into city values(52,'鞍山市',8);
125

insert into city values(53,'抚顺市',8);
126

insert into city values(54,'本溪市',8);
127

insert into city values(55,'丹东市',8);
128

insert into city values(56,'锦州市',8);
129

insert into city values(57,'营口市',8);
130

insert into city values(58,'阜新市',8);
131

insert into city values(59,'辽阳市',8);
132

insert into city values(60,'盘锦市',8);
133

insert into city values(61,'铁岭市',8);
134

insert into city values(62,'朝阳市',8);
135

insert into city values(63,'葫芦岛市',8);
136

--select * from city where provincialID=8 order by cityID
137

----------------------------------------------------------------
138

--9吉林省(2006年，辖：8个地级市、1个自治州；20个市辖区、20个县级市、17个县、3个自治县。);
139

insert into city values(64,'长春市',9);
140

insert into city values(65,'吉林市',9);
141

insert into city values(66,'四平市',9);
142

insert into city values(67,'辽源市',9);
143

insert into city values(68,'通化市',9);
144

insert into city values(69,'白山市',9);
145

insert into city values(70,'松原市',9);
146

insert into city values(71,'白城市',9);
147

insert into city values(72,'延边朝鲜族自治州',9);
148

--select * from city where provincialID=9 order by cityID
149

----------------------------------------------------------------
150

--10黑龙江省(2006年，辖：12地级市、1地区；64市辖区、18县级市、45县、1自治县);
151

insert into city values(73,'哈尔滨市',10);
152

insert into city values(74,'齐齐哈尔市',10);
153

insert into city values(75,'鹤岗市',10);
154

insert into city values(76,'双鸭山市',10);
155

insert into city values(77,'鸡西市',10);
156

insert into city values(78,'大庆市',10);
157

insert into city values(79,'伊春市',10);
158

insert into city values(80,'牡丹江市',10);
159

insert into city values(81,'佳木斯市',10);
160

insert into city values(82,'七台河市',10);
161

insert into city values(83,'黑河市',10);
162

insert into city values(84,'绥化市',10);
163

insert into city values(85,'大兴安岭地区',10);
164

--select * from city where provincialID=10 order by cityID
165

----------------------------------------------------------------
166

--11江苏省(2005年辖：13个地级市；54个市辖区、27个县级市、25个县);
167

insert into city values(86,'南京市',11);
168

insert into city values(87,'无锡市',11);
169

insert into city values(88,'徐州市',11);
170

insert into city values(89,'常州市',11);
171

insert into city values(90,'苏州市',11);
172

insert into city values(91,'南通市',11);
173

insert into city values(92,'连云港市',11);
174

insert into city values(93,'淮安市',11);
175

insert into city values(94,'盐城市',11);
176

insert into city values(95,'扬州市',11);
177

insert into city values(96,'镇江市',11);
178

insert into city values(97,'泰州市',11);
179

insert into city values(98,'宿迁市',11);
180

--select * from city where provincialID=11 order by cityID
181

----------------------------------------------------------------
182

--12浙江省(2006年，辖：11个地级市；32个市辖区、22个县级市、35个县、1个自治县。);
183

insert into city values(99,'杭州市',12);
184

insert into city values(100,'宁波市',12);
185

insert into city values(101,'温州市',12);
186

insert into city values(102,'嘉兴市',12);
187

insert into city values(103,'湖州市',12);
188

insert into city values(104,'绍兴市',12);
189

insert into city values(105,'金华市',12);
190

insert into city values(106,'衢州市',12);
191

insert into city values(107,'舟山市',12);
192

insert into city values(108,'台州市',12);
193

insert into city values(109,'丽水市',12);
194

--select * from city where provincialID=12 order by cityID
195

----------------------------------------------------------------
196

--13安徽省(2005年辖：17个地级市；44个市辖区、5县个级市、56个县。);
197

insert into city values(110,'合肥市',13);
198

insert into city values(111,'芜湖市',13);
199

insert into city values(112,'蚌埠市',13);
200

insert into city values(113,'淮南市',13);
201

insert into city values(114,'马鞍山市',13);
202

insert into city values(115,'淮北市',13);
203

insert into city values(116,'铜陵市',13);
204

insert into city values(117,'安庆市',13);
205

insert into city values(118,'黄山市',13);
206

insert into city values(119,'滁州市',13);
207

insert into city values(120,'阜阳市',13);
208

insert into city values(121,'宿州市',13);
209

insert into city values(122,'巢湖市',13);
210

insert into city values(123,'六安市',13);
211

insert into city values(124,'亳州市',13);
212

insert into city values(125,'池州市',13);
213

insert into city values(126,'宣城市',13);
214

--select * from city where provincialID=13 order by cityID
215

----------------------------------------------------------------
216

--14福建省(2006年辖：9个地级市；26个市辖区、14个县级市、45个县。);
217

insert into city values(127,'福州市',14);
218

insert into city values(128,'厦门市',14);
219

insert into city values(129,'莆田市',14);
220

insert into city values(130,'三明市',14);
221

insert into city values(131,'泉州市',14);
222

insert into city values(132,'漳州市',14);
223

insert into city values(133,'南平市',14);
224

insert into city values(134,'龙岩市',14);
225

insert into city values(135,'宁德市',14);
226

--select * from city where provincialID=14 order by cityID
227

----------------------------------------------------------------
228

--15江西省(2006年全省辖：11个地级市；19个市辖区、10个县级市、70个县。);
229

insert into city values(136,'南昌市',15);
230

insert into city values(137,'景德镇市',15);
231

insert into city values(138,'萍乡市',15);
232

insert into city values(139,'九江市',15);
233

insert into city values(140,'新余市',15);
234

insert into city values(141,'鹰潭市',15);
235

insert into city values(142,'赣州市',15);
236

insert into city values(143,'吉安市',15);
237

insert into city values(144,'宜春市',15);
238

insert into city values(145,'抚州市',15);
239

insert into city values(146,'上饶市',15);
240

--select * from city where provincialID=15 order by cityID
241

----------------------------------------------------------------
242

--16山东省(2005年，辖：17个地级市；49个市辖区、31个县级市、60个县。);
243

insert into city values(147,'济南市',16);
244

insert into city values(148,'青岛市',16);
245

insert into city values(149,'淄博市',16);
246

insert into city values(150,'枣庄市',16);
247

insert into city values(151,'东营市',16);
248

insert into city values(152,'烟台市',16);
249

insert into city values(153,'潍坊市',16);
250

insert into city values(154,'济宁市',16);
251

insert into city values(155,'泰安市',16);
252

insert into city values(156,'威海市',16);
253

insert into city values(157,'日照市',16);
254

insert into city values(158,'莱芜市',16);
255

insert into city values(159,'临沂市',16);
256

insert into city values(160,'德州市',16);
257

insert into city values(161,'聊城市',16);
258

insert into city values(162,'滨州市',16);
259

insert into city values(163,'菏泽市',16);
260

--select * from city where provincialID=16 order by cityID
261

----------------------------------------------------------------
262

--17河南省 17个地级市
263

insert into city values(164,'郑州市',17);
264

insert into city values(165,'开封市',17);
265

insert into city values(166,'洛阳市',17);
266

insert into city values(167,'平顶山市',17);
267

insert into city values(168,'安阳市',17);
268

insert into city values(169,'鹤壁市',17);
269

insert into city values(170,'新乡市',17);
270

insert into city values(171,'焦作市',17);
271

insert into city values(172,'濮阳市',17);
272

insert into city values(173,'许昌市',17);
273

insert into city values(174,'漯河市',17);
274

insert into city values(175,'三门峡市',17);
275

insert into city values(176,'南阳市',17);
276

insert into city values(177,'商丘市',17);
277

insert into city values(178,'信阳市',17);
278

insert into city values(179,'周口市',17);
279

insert into city values(180,'驻马店市',17);
280

insert into city values(181,'济源市',17);
281

--select * from city where provincialID=17 order by cityID
282

----------------------------------------------------------------
283

--18湖北省（截至2005年12月31日，全省辖13个地级单位（12个地级市、1个自治州）；102县级单位（38个市辖区、24个县级市、37个县、2个自治县、1个林区），共有1220个乡级单位（277个街道、733个镇、210个乡）。）
284

insert into city values(182,'武汉市',18);
285

insert into city values(183,'黄石市',18);
286

insert into city values(184,'十堰市',18);
287

insert into city values(185,'荆州市',18);
288

insert into city values(186,'宜昌市',18);
289

insert into city values(187,'襄樊市',18);
290

insert into city values(188,'鄂州市',18);
291

insert into city values(189,'荆门市',18);
292

insert into city values(190,'孝感市',18);
293

insert into city values(191,'黄冈市',18);
294

insert into city values(192,'咸宁市',18);
295

insert into city values(193,'随州市',18);
296

insert into city values(194,'仙桃市',18);
297

insert into city values(195,'天门市',18);
298

insert into city values(196,'潜江市',18);
299

insert into city values(197,'神农架林区',18);
300

insert into city values(198,'恩施土家族苗族自治州',18);
301

--select * from city where provincialID=18 order by cityID
302

----------------------------------------------------------------
303

--19湖南省（2005年辖：13个地级市、1个自治州；34个市辖区、16个县级市、65个县、7个自治县。）
304

insert into city values(199,'长沙市',19);
305

insert into city values(200,'株洲市',19);
306

insert into city values(201,'湘潭市',19);
307

insert into city values(202,'衡阳市',19);
308

insert into city values(203,'邵阳市',19);
309

insert into city values(204,'岳阳市',19);
310

insert into city values(205,'常德市',19);
311

insert into city values(206,'张家界市',19);
312

insert into city values(207,'益阳市',19);
313

insert into city values(208,'郴州市',19);
314

insert into city values(209,'永州市',19);
315

insert into city values(210,'怀化市',19);
316

insert into city values(211,'娄底市',19);
317

insert into city values(212,'湘西土家族苗族自治州',19);
318

--select * from city where provincialID=19 order by cityID
319

----------------------------------------------------------------
320

--20广东省（截至2005年12月31日，广东省辖：21个地级市，54个市辖区、23个县级市、41个县、3个自治县，429个街道办事处、1145个镇、4个乡、7个民族乡。）
321

insert into city values(213,'广州市',20);
322

insert into city values(214,'深圳市',20);
323

insert into city values(215,'珠海市',20);
324

insert into city values(216,'汕头市',20);
325

insert into city values(217,'韶关市',20);
326

insert into city values(218,'佛山市',20);
327

insert into city values(219,'江门市',20);
328

insert into city values(220,'湛江市',20);
329

insert into city values(221,'茂名市',20);
330

insert into city values(222,'肇庆市',20);
331

insert into city values(223,'惠州市',20);
332

insert into city values(224,'梅州市',20);
333

insert into city values(225,'汕尾市',20);
334

insert into city values(226,'河源市',20);
335

insert into city values(227,'阳江市',20);
336

insert into city values(228,'清远市',20);
337

insert into city values(229,'东莞市',20);
338

insert into city values(230,'中山市',20);
339

insert into city values(231,'潮州市',20);
340

insert into city values(232,'揭阳市',20);
341

insert into city values(233,'云浮市',20);
342

--select * from city where provincialID=20 order by cityID
343

----------------------------------------------------------------
344

--21甘肃省 12个地级市、2个自治州
345

insert into city values(234,'兰州市',21);
346

insert into city values(235,'金昌市',21);
347

insert into city values(236,'白银市',21);
348

insert into city values(237,'天水市',21);
349

insert into city values(238,'嘉峪关市',21);
350

insert into city values(239,'武威市',21);
351

insert into city values(240,'张掖市',21);
352

insert into city values(241,'平凉市',21);
353

insert into city values(242,'酒泉市',21);
354

insert into city values(243,'庆阳市',21);
355

insert into city values(244,'定西市',21);
356

insert into city values(245,'陇南市',21);
357

insert into city values(246,'临夏回族自治州',21);
358

insert into city values(247,'甘南藏族自治州',21);
359

--select * from city where provincialID=21 order by cityID
360

----------------------------------------------------------------
361

--22四川省18个地级市、3个自治州
362

insert into city values(248,'成都市',22);
363

insert into city values(249,'自贡市',22);
364

insert into city values(250,'攀枝花市',22);
365

insert into city values(251,'泸州市',22);
366

insert into city values(252,'德阳市',22);
367

insert into city values(253,'绵阳市',22);
368

insert into city values(254,'广元市',22);
369

insert into city values(255,'遂宁市',22);
370

insert into city values(256,'内江市',22);
371

insert into city values(257,'乐山市',22);
372

insert into city values(258,'南充市',22);
373

insert into city values(259,'眉山市',22);
374

insert into city values(260,'宜宾市',22);
375

insert into city values(261,'广安市',22);
376

insert into city values(262,'达州市',22);
377

insert into city values(263,'雅安市',22);
378

insert into city values(264,'巴中市',22);
379

insert into city values(265,'资阳市',22);
380

insert into city values(266,'阿坝藏族羌族自治州',22);
381

insert into city values(267,'甘孜藏族自治州',22);
382

insert into city values(268,'凉山彝族自治州',22);
383

--select * from city where provincialID=22 order by cityID
384

----------------------------------------------------------------
385

--23贵州省(2006年辖：4个地级市、2个地区、3个自治州；10个市辖区、9个县级市、56个县、11个自治县、2个特区。);
386

insert into city values(269,'贵阳市',23);
387

insert into city values(270,'六盘水市',23);
388

insert into city values(271,'遵义市',23);
389

insert into city values(272,'安顺市',23);
390

insert into city values(273,'铜仁地区',23);
391

insert into city values(274,'毕节地区',23);
392

insert into city values(275,'黔西南布依族苗族自治州',23);
393

insert into city values(276,'黔东南苗族侗族自治州',23);
394

insert into city values(277,'黔南布依族苗族自治州',23);
395

--select * from city where provincialID=23 order by cityID
396

----------------------------------------------------------------
397

--24海南省全省有2个地级市，6个县级市，4个县，6个民族自治县，4个市辖区，1个办事处（西南中沙群岛办事处，县级）。);
398

insert into city values(278,'海口市',24);
399

insert into city values(279,'三亚市',24);
400

insert into city values(280,'五指山市',24);
401

insert into city values(281,'琼海市',24);
402

insert into city values(282,'儋州市',24);
403

insert into city values(283,'文昌市',24);
404

insert into city values(284,'万宁市',24);
405

insert into city values(285,'东方市',24);
406

insert into city values(286,'澄迈县',24);
407

insert into city values(287,'定安县',24);
408

insert into city values(288,'屯昌县',24);
409

insert into city values(289,'临高县',24);
410

insert into city values(290,'白沙黎族自治县',24);
411

insert into city values(291,'昌江黎族自治县',24);
412

insert into city values(292,'乐东黎族自治县',24);
413

insert into city values(293,'陵水黎族自治县',24);
414

insert into city values(294,'保亭黎族苗族自治县',24);
415

insert into city values(295,'琼中黎族苗族自治县',24);
416

--select * from city where provincialID=24 order by cityID
417

----------------------------------------------------------------
418

--25云南省(2006年辖：8个地级市、8个自治州；12个市辖区、9个县级市、79个县、29个自治县。);
419

insert into city values(296,'昆明市',25);
420

insert into city values(297,'曲靖市',25);
421

insert into city values(298,'玉溪市',25);
422

insert into city values(299,'保山市',25);
423

insert into city values(300,'昭通市',25);
424

insert into city values(301,'丽江市',25);
425

insert into city values(302,'思茅市',25);
426

insert into city values(303,'临沧市',25);
427

insert into city values(304,'文山壮族苗族自治州',25);
428

insert into city values(305,'红河哈尼族彝族自治州',25);
429

insert into city values(306,'西双版纳傣族自治州',25);
430

insert into city values(307,'楚雄彝族自治州',25);
431

insert into city values(308,'大理白族自治州',25);
432

insert into city values(309,'德宏傣族景颇族自治州',25);
433

insert into city values(310,'怒江傈傈族自治州',25);
434

insert into city values(311,'迪庆藏族自治州',25);
435

--select * from city where provincialID=25 order by cityID
436

----------------------------------------------------------------
437

--26青海省(2006年辖：1个地级市、1个地区、6个自治州；4个市辖区、2个县级市、30个县、7个自治县。);
438

insert into city values(312,'西宁市',26);
439

insert into city values(313,'海东地区',26);
440

insert into city values(314,'海北藏族自治州',26);
441

insert into city values(315,'黄南藏族自治州',26);
442

insert into city values(316,'海南藏族自治州',26);
443

insert into city values(317,'果洛藏族自治州',26);
444

insert into city values(318,'玉树藏族自治州',26);
445

insert into city values(319,'海西蒙古族藏族自治州',26);
446

--select * from city where provincialID=26 order by cityID
447

----------------------------------------------------------------
448

--27陕西省(2006年辖：10个地级市；24个市辖区、3个县级市、80个县。);
449

insert into city values(320,'西安市',27);
450

insert into city values(321,'铜川市',27);
451

insert into city values(322,'宝鸡市',27);
452

insert into city values(323,'咸阳市',27);
453

insert into city values(324,'渭南市',27);
454

insert into city values(325,'延安市',27);
455

insert into city values(326,'汉中市',27);
456

insert into city values(327,'榆林市',27);
457

insert into city values(328,'安康市',27);
458

insert into city values(329,'商洛市',27);
459

--select * from city where provincialID=27 order by cityID
460

----------------------------------------------------------------
461

--28广西壮族自治区(2005年辖：14个地级市；34个市辖区、7个县级市、56个县、12个自治县。);
462

insert into city values(330,'南宁市',28);
463

insert into city values(331,'柳州市',28);
464

insert into city values(332,'桂林市',28);
465

insert into city values(333,'梧州市',28);
466

insert into city values(334,'北海市',28);
467

insert into city values(335,'防城港市',28);
468

insert into city values(336,'钦州市',28);
469

insert into city values(337,'贵港市',28);
470

insert into city values(338,'玉林市',28);
471

insert into city values(339,'百色市',28);
472

insert into city values(340,'贺州市',28);
473

insert into city values(341,'河池市',28);
474

insert into city values(342,'来宾市',28);
475

insert into city values(343,'崇左市',28);
476

--select * from city where provincialID=28 order by cityID
477

----------------------------------------------------------------
478

--29西藏自治区(2005年辖：1个地级市、6个地区；1个市辖区、1个县级市、71个县。);
479

insert into city values(344,'拉萨市',29);
480

insert into city values(345,'那曲地区',29);
481

insert into city values(346,'昌都地区',29);
482

insert into city values(347,'山南地区',29);
483

insert into city values(348,'日喀则地区',29);
484

insert into city values(349,'阿里地区',29);
485

insert into city values(350,'林芝地区',29);
486

--select * from city where provincialID=29 order by cityID
487

----------------------------------------------------------------
488

--30宁夏回族自治区
489

insert into city values(351,'银川市',30);
490

insert into city values(352,'石嘴山市',30);
491

insert into city values(353,'吴忠市',30);
492

insert into city values(354,'固原市',30);
493

insert into city values(355,'中卫市',30);
494

--select * from city where provincialID=30 order by cityID
495

----------------------------------------------------------------
496

--31新疆维吾尔自治区(2005年辖：2个地级市、7个地区、5个自治州；11个市辖区、20个县级市、62个县、6个自治县);
497

insert into city values(356,'乌鲁木齐市',31);
498

insert into city values(357,'克拉玛依市',31);
499

insert into city values(358,'石河子市　',31);
500

insert into city values(359,'阿拉尔市',31);
501

insert into city values(360,'图木舒克市',31);
502

insert into city values(361,'五家渠市',31);
503

insert into city values(362,'吐鲁番市',31);
504

insert into city values(363,'阿克苏市',31);
505

insert into city values(364,'喀什市',31);
506

insert into city values(365,'哈密市',31);
507

insert into city values(366,'和田市',31);
508

insert into city values(367,'阿图什市',31);
509

insert into city values(368,'库尔勒市',31);
510

insert into city values(369,'昌吉市　',31);
511

insert into city values(370,'阜康市',31);
512

insert into city values(371,'米泉市',31);
513

insert into city values(372,'博乐市',31);
514

insert into city values(373,'伊宁市',31);
515

insert into city values(374,'奎屯市',31);
516

insert into city values(375,'塔城市',31);
517

insert into city values(376,'乌苏市',31);
518

insert into city values(377,'阿勒泰市',31);
519

--select * from city where provincialID=31 order by cityID
520

----------------------------------------------------------------
521

--32内蒙古自治区(2006年，辖：9个地级市、3个盟；21个市辖区、11个县级市、17个县、49个旗、3个自治旗。);
522

insert into city values(378,'呼和浩特市',32);
523

insert into city values(379,'包头市',32);
524

insert into city values(380,'乌海市',32);
525

insert into city values(381,'赤峰市',32);
526

insert into city values(382,'通辽市',32);
527

insert into city values(383,'鄂尔多斯市',32);
528

insert into city values(384,'呼伦贝尔市',32);
529

insert into city values(385,'巴彦淖尔市',32);
530

insert into city values(386,'乌兰察布市',32);
531

insert into city values(387,'锡林郭勒盟',32);
532

insert into city values(388,'兴安盟',32);
533

insert into city values(389,'阿拉善盟',32);
534

--select * from city where provincialID=32 order by
535

----------------------------------------------------------------
536

--33澳门特别行政区
537

insert into city values(390,'澳门特别行政区',33);
538

--select * from city where provincialID=33 order by cityID
539

----------------------------------------------------------------
540

--34香港特别行政区
541

insert into city values(391,'香港特别行政区',34);
542

--select * from city where provincialID=34 order by cityID
543

----------------------------------------------------------------
544

545

546

547

---查询视图
548

create or replace view pro_v_city
549

as
550

select
551

pro.*,
552

ci.cityID,
553

ci.cityName
554

from provincial as pro
555

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

557

posted on 2010-11-02 21:21 麟德阅读(2389) 评论(0) 编辑收藏所属分类: mysql

新用户注册刷新评论列表


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻知识库 C++博客博问管理
相关文章: mysql 创建数据库和表的法语以及常用的操作全国省市级联数据sql语句 mysql版 mysql 主键自增长

全国省市级联数据sql语句 mysql版

导航

统计

常用链接

留言簿

随笔分类(2)

随笔档案(2)

文章分类(23)

文章档案(23)

最新随笔

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜