· 6 years ago · Jul 17, 2019, 03:28 PM
1/* Estructura y Datos de las Regiones, Provincias */
2/* y Comunas de Chile. */
3/* */
4/* Fecha: Julio 2010 */
5/* Autor: Juan Pablo Aqueveque - juque.cl */
6
7--
8-- Comunas
9--
10DROP TABLE IF EXISTS `comunas`;
11CREATE TABLE `comunas` (
12 `comuna_id` int(11) NOT NULL AUTO_INCREMENT,
13 `comuna_nombre` varchar(64) NOT NULL,
14 `provincia_id` int(11) NOT NULL,
15 PRIMARY KEY (`comuna_id`)
16) ENGINE=MyISAM AUTO_INCREMENT=346 DEFAULT CHARSET=utf8;
17
18INSERT INTO `comunas` (`comuna_id`,`comuna_nombre`,`provincia_id`)
19VALUES
20 (1,'Arica',1),
21 (2,'Camarones',1),
22 (3,'General Lagos',2),
23 (4,'Putre',2),
24 (5,'Alto Hospicio',3),
25 (6,'Iquique',3),
26 (7,'Camiña',4),
27 (8,'Colchane',4),
28 (9,'Huara',4),
29 (10,'Pica',4),
30 (11,'Pozo Almonte',4),
31 (12,'Antofagasta',5),
32 (13,'Mejillones',5),
33 (14,'Sierra Gorda',5),
34 (15,'Taltal',5),
35 (16,'Calama',6),
36 (17,'Ollague',6),
37 (18,'San Pedro de Atacama',6),
38 (19,'María Elena',7),
39 (20,'Tocopilla',7),
40 (21,'Chañaral',8),
41 (22,'Diego de Almagro',8),
42 (23,'Caldera',9),
43 (24,'Copiapó',9),
44 (25,'Tierra Amarilla',9),
45 (26,'Alto del Carmen',10),
46 (27,'Freirina',10),
47 (28,'Huasco',10),
48 (29,'Vallenar',10),
49 (30,'Canela',11),
50 (31,'Illapel',11),
51 (32,'Los Vilos',11),
52 (33,'Salamanca',11),
53 (34,'Andacollo',12),
54 (35,'Coquimbo',12),
55 (36,'La Higuera',12),
56 (37,'La Serena',12),
57 (38,'Paihuaco',12),
58 (39,'Vicuña',12),
59 (40,'Combarbalá',13),
60 (41,'Monte Patria',13),
61 (42,'Ovalle',13),
62 (43,'Punitaqui',13),
63 (44,'Río Hurtado',13),
64 (45,'Isla de Pascua',14),
65 (46,'Calle Larga',15),
66 (47,'Los Andes',15),
67 (48,'Rinconada',15),
68 (49,'San Esteban',15),
69 (50,'La Ligua',16),
70 (51,'Papudo',16),
71 (52,'Petorca',16),
72 (53,'Zapallar',16),
73 (54,'Hijuelas',17),
74 (55,'La Calera',17),
75 (56,'La Cruz',17),
76 (57,'Limache',17),
77 (58,'Nogales',17),
78 (59,'Olmué',17),
79 (60,'Quillota',17),
80 (61,'Algarrobo',18),
81 (62,'Cartagena',18),
82 (63,'El Quisco',18),
83 (64,'El Tabo',18),
84 (65,'San Antonio',18),
85 (66,'Santo Domingo',18),
86 (67,'Catemu',19),
87 (68,'Llaillay',19),
88 (69,'Panquehue',19),
89 (70,'Putaendo',19),
90 (71,'San Felipe',19),
91 (72,'Santa María',19),
92 (73,'Casablanca',20),
93 (74,'Concón',20),
94 (75,'Juan Fernández',20),
95 (76,'Puchuncaví',20),
96 (77,'Quilpué',20),
97 (78,'Quintero',20),
98 (79,'Valparaíso',20),
99 (80,'Villa Alemana',20),
100 (81,'Viña del Mar',20),
101 (82,'Colina',21),
102 (83,'Lampa',21),
103 (84,'Tiltil',21),
104 (85,'Pirque',22),
105 (86,'Puente Alto',22),
106 (87,'San José de Maipo',22),
107 (88,'Buin',23),
108 (89,'Calera de Tango',23),
109 (90,'Paine',23),
110 (91,'San Bernardo',23),
111 (92,'Alhué',24),
112 (93,'Curacaví',24),
113 (94,'María Pinto',24),
114 (95,'Melipilla',24),
115 (96,'San Pedro',24),
116 (97,'Cerrillos',25),
117 (98,'Cerro Navia',25),
118 (99,'Conchalí',25),
119 (100,'El Bosque',25),
120 (101,'Estación Central',25),
121 (102,'Huechuraba',25),
122 (103,'Independencia',25),
123 (104,'La Cisterna',25),
124 (105,'La Granja',25),
125 (106,'La Florida',25),
126 (107,'La Pintana',25),
127 (108,'La Reina',25),
128 (109,'Las Condes',25),
129 (110,'Lo Barnechea',25),
130 (111,'Lo Espejo',25),
131 (112,'Lo Prado',25),
132 (113,'Macul',25),
133 (114,'Maipú',25),
134 (115,'Ñuñoa',25),
135 (116,'Pedro Aguirre Cerda',25),
136 (117,'Peñalolén',25),
137 (118,'Providencia',25),
138 (119,'Pudahuel',25),
139 (120,'Quilicura',25),
140 (121,'Quinta Normal',25),
141 (122,'Recoleta',25),
142 (123,'Renca',25),
143 (124,'San Miguel',25),
144 (125,'San Joaquín',25),
145 (126,'San Ramón',25),
146 (127,'Santiago',25),
147 (128,'Vitacura',25),
148 (129,'El Monte',26),
149 (130,'Isla de Maipo',26),
150 (131,'Padre Hurtado',26),
151 (132,'Peñaflor',26),
152 (133,'Talagante',26),
153 (134,'Codegua',27),
154 (135,'Coínco',27),
155 (136,'Coltauco',27),
156 (137,'Doñihue',27),
157 (138,'Graneros',27),
158 (139,'Las Cabras',27),
159 (140,'Machalí',27),
160 (141,'Malloa',27),
161 (142,'Mostazal',27),
162 (143,'Olivar',27),
163 (144,'Peumo',27),
164 (145,'Pichidegua',27),
165 (146,'Quinta de Tilcoco',27),
166 (147,'Rancagua',27),
167 (148,'Rengo',27),
168 (149,'Requínoa',27),
169 (150,'San Vicente de Tagua Tagua',27),
170 (151,'La Estrella',28),
171 (152,'Litueche',28),
172 (153,'Marchihue',28),
173 (154,'Navidad',28),
174 (155,'Peredones',28),
175 (156,'Pichilemu',28),
176 (157,'Chépica',29),
177 (158,'Chimbarongo',29),
178 (159,'Lolol',29),
179 (160,'Nancagua',29),
180 (161,'Palmilla',29),
181 (162,'Peralillo',29),
182 (163,'Placilla',29),
183 (164,'Pumanque',29),
184 (165,'San Fernando',29),
185 (166,'Santa Cruz',29),
186 (167,'Cauquenes',30),
187 (168,'Chanco',30),
188 (169,'Pelluhue',30),
189 (170,'Curicó',31),
190 (171,'Hualañé',31),
191 (172,'Licantén',31),
192 (173,'Molina',31),
193 (174,'Rauco',31),
194 (175,'Romeral',31),
195 (176,'Sagrada Familia',31),
196 (177,'Teno',31),
197 (178,'Vichuquén',31),
198 (179,'Colbún',32),
199 (180,'Linares',32),
200 (181,'Longaví',32),
201 (182,'Parral',32),
202 (183,'Retiro',32),
203 (184,'San Javier',32),
204 (185,'Villa Alegre',32),
205 (186,'Yerbas Buenas',32),
206 (187,'Constitución',33),
207 (188,'Curepto',33),
208 (189,'Empedrado',33),
209 (190,'Maule',33),
210 (191,'Pelarco',33),
211 (192,'Pencahue',33),
212 (193,'Río Claro',33),
213 (194,'San Clemente',33),
214 (195,'San Rafael',33),
215 (196,'Talca',33),
216 (197,'Arauco',34),
217 (198,'Cañete',34),
218 (199,'Contulmo',34),
219 (200,'Curanilahue',34),
220 (201,'Lebu',34),
221 (202,'Los Álamos',34),
222 (203,'Tirúa',34),
223 (204,'Alto Biobío',35),
224 (205,'Antuco',35),
225 (206,'Cabrero',35),
226 (207,'Laja',35),
227 (208,'Los Ángeles',35),
228 (209,'Mulchén',35),
229 (210,'Nacimiento',35),
230 (211,'Negrete',35),
231 (212,'Quilaco',35),
232 (213,'Quilleco',35),
233 (214,'San Rosendo',35),
234 (215,'Santa Bárbara',35),
235 (216,'Tucapel',35),
236 (217,'Yumbel',35),
237 (218,'Chiguayante',36),
238 (219,'Concepción',36),
239 (220,'Coronel',36),
240 (221,'Florida',36),
241 (222,'Hualpén',36),
242 (223,'Hualqui',36),
243 (224,'Lota',36),
244 (225,'Penco',36),
245 (226,'San Pedro de La Paz',36),
246 (227,'Santa Juana',36),
247 (228,'Talcahuano',36),
248 (229,'Tomé',36),
249 (230,'Bulnes',37),
250 (231,'Chillán',37),
251 (232,'Chillán Viejo',37),
252 (233,'Cobquecura',37),
253 (234,'Coelemu',37),
254 (235,'Coihueco',37),
255 (236,'El Carmen',37),
256 (237,'Ninhue',37),
257 (238,'Ñiquen',37),
258 (239,'Pemuco',37),
259 (240,'Pinto',37),
260 (241,'Portezuelo',37),
261 (242,'Quillón',37),
262 (243,'Quirihue',37),
263 (244,'Ránquil',37),
264 (245,'San Carlos',37),
265 (246,'San Fabián',37),
266 (247,'San Ignacio',37),
267 (248,'San Nicolás',37),
268 (249,'Treguaco',37),
269 (250,'Yungay',37),
270 (251,'Carahue',38),
271 (252,'Cholchol',38),
272 (253,'Cunco',38),
273 (254,'Curarrehue',38),
274 (255,'Freire',38),
275 (256,'Galvarino',38),
276 (257,'Gorbea',38),
277 (258,'Lautaro',38),
278 (259,'Loncoche',38),
279 (260,'Melipeuco',38),
280 (261,'Nueva Imperial',38),
281 (262,'Padre Las Casas',38),
282 (263,'Perquenco',38),
283 (264,'Pitrufquén',38),
284 (265,'Pucón',38),
285 (266,'Saavedra',38),
286 (267,'Temuco',38),
287 (268,'Teodoro Schmidt',38),
288 (269,'Toltén',38),
289 (270,'Vilcún',38),
290 (271,'Villarrica',38),
291 (272,'Angol',39),
292 (273,'Collipulli',39),
293 (274,'Curacautín',39),
294 (275,'Ercilla',39),
295 (276,'Lonquimay',39),
296 (277,'Los Sauces',39),
297 (278,'Lumaco',39),
298 (279,'Purén',39),
299 (280,'Renaico',39),
300 (281,'Traiguén',39),
301 (282,'Victoria',39),
302 (283,'Corral',40),
303 (284,'Lanco',40),
304 (285,'Los Lagos',40),
305 (286,'Máfil',40),
306 (287,'Mariquina',40),
307 (288,'Paillaco',40),
308 (289,'Panguipulli',40),
309 (290,'Valdivia',40),
310 (291,'Futrono',41),
311 (292,'La Unión',41),
312 (293,'Lago Ranco',41),
313 (294,'Río Bueno',41),
314 (295,'Ancud',42),
315 (296,'Castro',42),
316 (297,'Chonchi',42),
317 (298,'Curaco de Vélez',42),
318 (299,'Dalcahue',42),
319 (300,'Puqueldón',42),
320 (301,'Queilén',42),
321 (302,'Quemchi',42),
322 (303,'Quellón',42),
323 (304,'Quinchao',42),
324 (305,'Calbuco',43),
325 (306,'Cochamó',43),
326 (307,'Fresia',43),
327 (308,'Frutillar',43),
328 (309,'Llanquihue',43),
329 (310,'Los Muermos',43),
330 (311,'Maullín',43),
331 (312,'Puerto Montt',43),
332 (313,'Puerto Varas',43),
333 (314,'Osorno',44),
334 (315,'Puero Octay',44),
335 (316,'Purranque',44),
336 (317,'Puyehue',44),
337 (318,'Río Negro',44),
338 (319,'San Juan de la Costa',44),
339 (320,'San Pablo',44),
340 (321,'Chaitén',45),
341 (322,'Futaleufú',45),
342 (323,'Hualaihué',45),
343 (324,'Palena',45),
344 (325,'Aisén',46),
345 (326,'Cisnes',46),
346 (327,'Guaitecas',46),
347 (328,'Cochrane',47),
348 (329,'O\'higgins',47),
349 (330,'Tortel',47),
350 (331,'Coihaique',48),
351 (332,'Lago Verde',48),
352 (333,'Chile Chico',49),
353 (334,'Río Ibáñez',49),
354 (335,'Antártica',50),
355 (336,'Cabo de Hornos',50),
356 (337,'Laguna Blanca',51),
357 (338,'Punta Arenas',51),
358 (339,'Río Verde',51),
359 (340,'San Gregorio',51),
360 (341,'Porvenir',52),
361 (342,'Primavera',52),
362 (343,'Timaukel',52),
363 (344,'Natales',53),
364 (345,'Torres del Paine',53);
365
366--
367-- Provincias
368--
369
370DROP TABLE IF EXISTS `provincias`;
371CREATE TABLE `provincias` (
372 `provincia_id` int(11) NOT NULL AUTO_INCREMENT,
373 `provincia_nombre` varchar(64) NOT NULL,
374 `region_id` int(11) NOT NULL,
375 PRIMARY KEY (`provincia_id`)
376) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8;
377
378INSERT INTO `provincias` (`provincia_id`,`provincia_nombre`,`region_id`)
379VALUES
380 (1,'Arica',1),
381 (2,'Parinacota',1),
382 (3,'Iquique',2),
383 (4,'El Tamarugal',2),
384 (5,'Antofagasta',3),
385 (6,'El Loa',3),
386 (7,'Tocopilla',3),
387 (8,'Chañaral',4),
388 (9,'Copiapó',4),
389 (10,'Huasco',4),
390 (11,'Choapa',5),
391 (12,'Elqui',5),
392 (13,'Limarí',5),
393 (14,'Isla de Pascua',6),
394 (15,'Los Andes',6),
395 (16,'Petorca',6),
396 (17,'Quillota',6),
397 (18,'San Antonio',6),
398 (19,'San Felipe de Aconcagua',6),
399 (20,'Valparaiso',6),
400 (21,'Chacabuco',7),
401 (22,'Cordillera',7),
402 (23,'Maipo',7),
403 (24,'Melipilla',7),
404 (25,'Santiago',7),
405 (26,'Talagante',7),
406 (27,'Cachapoal',8),
407 (28,'Cardenal Caro',8),
408 (29,'Colchagua',8),
409 (30,'Cauquenes',9),
410 (31,'Curicó',9),
411 (32,'Linares',9),
412 (33,'Talca',9),
413 (34,'Arauco',10),
414 (35,'Bio Bío',10),
415 (36,'Concepción',10),
416 (37,'Ñuble',10),
417 (38,'Cautín',11),
418 (39,'Malleco',11),
419 (40,'Valdivia',12),
420 (41,'Ranco',12),
421 (42,'Chiloé',13),
422 (43,'Llanquihue',13),
423 (44,'Osorno',13),
424 (45,'Palena',13),
425 (46,'Aisén',14),
426 (47,'Capitán Prat',14),
427 (48,'Coihaique',14),
428 (49,'General Carrera',14),
429 (50,'Antártica Chilena',15),
430 (51,'Magallanes',15),
431 (52,'Tierra del Fuego',15),
432 (53,'Última Esperanza',15);
433
434--
435-- Regiones
436--
437
438DROP TABLE IF EXISTS `regiones`;
439CREATE TABLE `regiones` (
440 `region_id` int(11) NOT NULL AUTO_INCREMENT,
441 `region_nombre` varchar(64) NOT NULL,
442 `region_ordinal` varchar(4) NOT NULL,
443 PRIMARY KEY (`region_id`)
444) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
445
446INSERT INTO `regiones` (`region_id`,`region_nombre`,`region_ordinal`)
447VALUES
448 (1,'Arica y Parinacota','XV'),
449 (2,'Tarapacá','I'),
450 (3,'Antofagasta','II'),
451 (4,'Atacama','III'),
452 (5,'Coquimbo','IV'),
453 (6,'Valparaiso','V'),
454 (7,'Metropolitana de Santiago','RM'),
455 (8,'Libertador General Bernardo O\'Higgins','VI'),
456 (9,'Maule','VII'),
457 (10,'Biobío','VIII'),
458 (11,'La Araucanía','IX'),
459 (12,'Los Ríos','XIV'),
460 (13,'Los Lagos','X'),
461 (14,'Aisén del General Carlos Ibáñez del Campo','XI'),
462 (15,'Magallanes y de la Antártica Chilena','XII');
463
464
465
466/*
467
468Consulta de prueba
469------------------
470
471SELECT
472 comuna_nombre AS comuna,
473 provincia_nombre AS provincia,
474 region_nombre AS region
475FROM comunas
476INNER JOIN provincias ON
477 comunas.provincia_id = provincias.provincia_id
478INNER JOIN regiones ON
479 provincias.region_id = regiones.region_id
480WHERE region_ordinal LIKE "IX";
481
482*/