miércoles, 28 de octubre de 2009

Guía práctica de MySQL GUI Tools

Aprovechando el hecho de que debo comenzar a documentar algunos proyectos que tengo he desarrollado una guía práctica para iniciarse con MySQL GUI Tools que es una pequeña explicación sobre las herramientas y un puñado de consejos que he aprendido durante el tiempo que la he utilizado.

Agradecimientos a Juque por su artículo sobre UTF8

La guía la pueden descargar desde acá


Creative Commons License
Guía practica de MySQL GUI Tools by David Rivera is licensed under a Creative Commons Reconocimiento-No comercial 2.0 Chile License.

jueves, 15 de octubre de 2009

Listado Regiones, provincias y comunas de chile

Hace un tiempo atras publique un listado con las cuidades de chile, bueno ahora me dedique ha hacer una version mejorada de esta entrada la cual tenia un listado con algunas comunas de Chile.

Hoy debido a que necesitaba un combobox para una aplicación con las comunas de Chile, en lugar de reutilizar se me ocurrio la "brillante" idea (me tomo un par de horas y ademas no existia un conglomerado de la información especifica que necesitaba) de hacer un script que tenga las regiones, provincias (con prefijo telefonico) y para obtener la siguiente información Puede tener errores si los tiene agradecería que me los informaran para corregirlos.

Por lo que a continuación estan 3 script para MySQL (pero los insert se pueden usar en cualquier otro Gestor de bases de datos) y un archivo XLS con la información actualizada de las regiones, provincias y comunas de Chile

Regiones.sql
Provincias.sql
Comunas.sql
Listado en Excel
Listado en ODS

si te es util, el feedback se agradece ;D, los archivos tienen Licencia Atribución 2.0 Chile de Creative Commons.
Este listado esta desarrollado en base al articulo de SUBDERE en el cual se cuentan en el 2009, 15 Regiones, 35 Provincias y 346 comunas, muchas gracias por la información.

Es nesesario Cargar los tres scripts para poder utilizarlo, salvo en el caso del archivo XLS

para probar que este cargado correctamente el script se debe ejecutar el siguiente select

SELECT
r.`region_numero_romano` as 'Número Región' ,
r.`nombre_region` as 'Nombre Región',
r.`capital` as 'Capital Regional',
p.`nombre_provincia` as 'Nombre Provincia',
p.`capital` as 'Capital Provincial',
p.`prefijo_telefonico`as 'Prefijo telefonico',
c.`Nombre_comuna` as 'Nombre Comuna'
FROM
regiones r,comunas c,provincias p
WHERE
r.`region_numero`=p.`region_numero`
and c.`provincia_numero`= p.`provincia_numero`;
y se debe obtener 346 registros.


en caso de que no puedan bajar los archivos:


Regiones:


DROP TABLE IF EXISTS `regiones`;
CREATE TABLE `regiones` (
`region_numero` int(4) NOT NULL ,
`region_numero_romano` varchar(4) NOT NULL ,
`nombre_region` text NOT NULL,
`capital` text NOT NULL,
PRIMARY KEY (`region_numero`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO regiones VALUES
(15,"XV","Arica y Parinacota","Arica"),
(1,"I","Tarapacá","Iquique"),
(2,"II","Antofagasta","Antofagasta"),
(3,"III","Atacama","Copiapó"),
(4,"IV","Coquimbo","La Serena"),
(5,"V","Valparaíso","Valparaíso"),
(13,"RM","Metropolitana","Santiago"),
(6,"VI","Libertador General Bernardo O'Higgins Riquelme","Rancagua"),
(7,"VII","Maule","Talca"),
(8,"VIII","Biobío","Concepción"),
(9,"IX","Araucanía","Temuco"),
(14,"XIV","Los Ríos","Valdivia"),
(10,"X","Los Lagos","Puerto Montt"),
(11,"XI","General Carlos Ibáñez del Campo","Coyhaique"),
(12,"XII","Magallanes y Antártica Chilena","Punta Arenas");


Provincias:

DROP TABLE IF EXISTS `provincias`;
CREATE TABLE `provincias` (
provincia_numero int(4) not null,
`region_numero` varchar(4) NOT NULL ,
`nombre_provincia` text NOT NULL,
`capital` text NOT NULL,
prefijo_telefonico int(4),
PRIMARY KEY (`provincia_numero`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO provincias VALUES
(1,15,"Provincia de Arica","Arica",58),
(2,15,"Provincia de Parinacota","Putre",58),
(3,1,"Provincia de Iquique","Iquique",57),
(4,1,"Provincia del Tamarugal","Pozo Almonte",57),
(5,2,"Provincia de Antofagasta","Antofagasta",55),
(6,2,"Provincia de El Loa","Calama",55),
(7,2,"Provincia de Tocopilla","Tocopilla",55),
(8,3,"Provincia de Copiapó","Copiapó",52),
(9,3,"Provincia de Chañaral","Chañaral",52),
(10,3,"Provincia de Huasco","Vallenar",51),
(11,4,"Provincia de Elqui","Coquimbo",51),
(12,4,"Provincia de Choapa","Illapel",53),
(13,4,"Provincia de Limarí","Ovalle",53),
(14,5,"Provincia de Valparaíso","Valparaíso",32),
(15,5,"Provincia de Isla de Pascua","Hanga Roa",39),
(16,5,"Provincia de Los Andes","Los Andes",34),
(17,5,"Provincia de Petorca","La Ligua",33),
(18,5,"Provincia de Quillota","Quillota",33),
(19,5,"Provincia de San Antonio","San Antonio",35),
(20,5,"Provincia de San Felipe de Aconcagua","San Felipe",34),
(21,6,"Provincia de Cachapoal","Rancagua",72),
(22,6,"Provincia de Cardenal Caro","Pichilemu",72),
(23,6,"Provincia de Colchagua","San Fernando",72),
(24,7,"Provincia de Talca","Talca",71),
(25,7,"Provincia de Cauquenes","Cauquenes",73),
(26,7,"Provincia de Curicó","Curicó",75),
(27,7,"Provincia de Linares","Linares",73),
(28,8,"Provincia de Concepción","Concepción",41),
(29,8,"Provincia de Arauco","Lebu",41),
(30,8,"Provincia de Biobío","Los Ángeles",43),
(31,8,"Provincia de Ñuble","Chillán",42),
(32,9,"Provincia de Cautín","Temuco",45),
(33,9,"Provincia de Malleco","Angol",45),
(34,14,"Provincia de Valdivia","Valdivia",63),
(35,14,"Provincia del Ranco","La Unión",63),
(36,10,"Provincia de Llanquihue","Puerto Montt",65),
(37,10,"Provincia de Chiloé","Castro",65),
(38,10,"Provincia de Osorno","Osorno",64),
(39,10,"Provincia de Palena","Chaitén",65),
(40,11,"Provincia de Coyhaique","Coyhaique",67),
(41,11,"Provincia de Aisén","Puerto Aisén",67),
(42,11,"Provincia de Capitán Prat","Cochrane",67),
(43,11,"Provincia de General Carrera","Chile Chico",67),
(44,12,"Provincia de Magallanes","Punta Arenas",61),
(45,12,"Provincia de la Antártica Chilena","Puerto Williams",61),
(46,12,"Provincia de Tierra del Fuego","Porvenir",61),
(47,12,"Provincia de Última Esperanza","Puerto Natales",61),
(48,13,"Provincia de Santiago","Santiago",2),
(49,13,"Provincia de Cordillera","Puente Alto",2),
(50,13,"Provincia de Chacabuco","Colina",2),
(51,13,"Provincia de Maipo","San Bernardo",2),
(52,13,"Provincia de Melipilla","Melipilla",2),
(53,13,"Provincia de Talagante","Talagante",2);

Comunas:
DROP TABLE IF EXISTS `comunas`;
CREATE TABLE `comunas` (
comuna_numero int(4) not null auto_increment ,
`provincia_numero` int(4) NOT NULL ,
`Nombre_comuna` text NOT NULL,
PRIMARY KEY (`comuna_numero`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into comunas values
(null,1,"Arica"),
(null,1,"Camarones"),
(null,2,"Putre"),
(null,2,"General Lagos"),
(null,3,"Iquique"),
(null,3,"Alto Hospicio"),
(null,4,"Pozo Almonte"),
(null,4,"Camiña"),
(null,4,"Colchane"),
(null,4,"Huara"),
(null,4,"Pica"),
(null,5,"Antofagasta"),
(null,5,"Mejillones"),
(null,5,"Sierra Gorda"),
(null,5,"Taltal"),
(null,6,"Calama"),
(null,6,"Ollagüe"),
(null,6,"San Pedro de Atacama"),
(null,7,"Tocopilla"),
(null,7,"María Elena"),
(null,8,"Copiapó"),
(null,8,"Caldera"),
(null,8,"Tierra Amarilla"),
(null,9,"Chañaral"),
(null,9,"Diego de Almagro"),
(null,10,"Vallenar"),
(null,10,"Alto del Carmen"),
(null,10,"Freirina"),
(null,10,"Huasco"),
(null,11,"La Serena"),
(null,11,"Coquimbo"),
(null,11,"Andacollo"),
(null,11,"La Higuera"),
(null,11,"Paiguano"),
(null,11,"Vicuña"),
(null,12,"Illapel"),
(null,12,"Canela"),
(null,12,"Los Vilos"),
(null,12,"Salamanca"),
(null,13,"Ovalle"),
(null,13,"Combarbalá"),
(null,13,"Monte Patria"),
(null,13,"Punitaqui"),
(null,13,"Río Hurtado"),
(null,14,"Valparaíso"),
(null,14,"Casablanca"),
(null,14,"Concón"),
(null,14,"Juan Fernández"),
(null,14,"Puchuncaví"),
(null,14,"Quilpué"),
(null,14,"Quintero"),
(null,14,"Villa Alemana"),
(null,14,"Viña del Mar"),
(null,15,"Isla de Pascua"),
(null,16,"Los Andes"),
(null,16,"Calle Larga"),
(null,16,"Rinconada"),
(null,16,"San Esteban"),
(null,17,"La Ligua"),
(null,17,"Cabildo"),
(null,17,"Papudo"),
(null,17,"Petorca"),
(null,17,"Zapallar"),
(null,18,"Quillota"),
(null,18,"Calera"),
(null,18,"Hijuelas"),
(null,18,"La Cruz"),
(null,18,"Limache"),
(null,18,"Nogales"),
(null,18,"Olmué"),
(null,19,"San Antonio"),
(null,19,"Algarrobo"),
(null,19,"Cartagena"),
(null,19,"El Quisco"),
(null,19,"El Tabo"),
(null,19,"Santo Domingo"),
(null,20,"San Felipe"),
(null,20,"Catemu"),
(null,20,"Llaillay"),
(null,20,"Panquehue"),
(null,20,"Putaendo"),
(null,20,"Santa María"),
(null,21,"Rancagua"),
(null,21,"Codegua"),
(null,21,"Coinco"),
(null,21,"Coltauco"),
(null,21,"Doñihue"),
(null,21,"Graneros"),
(null,21,"Las Cabras"),
(null,21,"Machalí"),
(null,21,"Malloa"),
(null,21,"Mostazal"),
(null,21,"Olivar"),
(null,21,"Peumo"),
(null,21,"Pichidegua"),
(null,21,"Quinta de Tilcoco"),
(null,21,"Rengo"),
(null,21,"Requínoa"),
(null,21,"San Vicente"),
(null,22,"Pichilemu"),
(null,22,"La Estrella"),
(null,22,"Litueche"),
(null,22,"Marchihue"),
(null,22,"Navidad"),
(null,22,"Paredones"),
(null,23,"San Fernando"),
(null,23,"Chépica"),
(null,23,"Chimbarongo"),
(null,23,"Lolol"),
(null,23,"Nancagua"),
(null,23,"Palmilla"),
(null,23,"Peralillo"),
(null,23,"Placilla"),
(null,23,"Pumanque"),
(null,23,"Santa Cruz"),
(null,24,"Talca"),
(null,24,"Constitución"),
(null,24,"Curepto"),
(null,24,"Empedrado"),
(null,24,"Maule"),
(null,24,"Pelarco"),
(null,24,"Pencahue"),
(null,24,"Río Claro"),
(null,24,"San Clemente"),
(null,24,"San Rafael"),
(null,25,"Cauquenes"),
(null,25,"Chanco"),
(null,25,"Pelluhue"),
(null,26,"Curicó"),
(null,26,"Hualañé"),
(null,26,"Licantén"),
(null,26,"Molina"),
(null,26,"Rauco"),
(null,26,"Romeral"),
(null,26,"Sagrada Familia"),
(null,26,"Teno"),
(null,26,"Vichuquén"),
(null,27,"Linares"),
(null,27,"Colbún"),
(null,27,"Longaví"),
(null,27,"Parral"),
(null,27,"Retiro"),
(null,27,"San Javier"),
(null,27,"Villa Alegre"),
(null,27,"Yerbas Buenas"),
(null,28,"Concepción"),
(null,28,"Coronel"),
(null,28,"Chiguayante"),
(null,28,"Florida"),
(null,28,"Hualqui"),
(null,28,"Lota"),
(null,28,"Penco"),
(null,28,"San Pedro de la Paz"),
(null,28,"Santa Juana"),
(null,28,"Talcahuano"),
(null,28,"Tomé"),
(null,28,"Hualpén"),
(null,29,"Lebu"),
(null,29,"Arauco"),
(null,29,"Cañete"),
(null,29,"Contulmo"),
(null,29,"Curanilahue"),
(null,29,"Los Alamos"),
(null,29,"Tirúa"),
(null,30,"Los Angeles"),
(null,30,"Antuco"),
(null,30,"Cabrero"),
(null,30,"Laja"),
(null,30,"Mulchén"),
(null,30,"Nacimiento"),
(null,30,"Negrete"),
(null,30,"Quilaco"),
(null,30,"Quilleco"),
(null,30,"San Rosendo"),
(null,30,"Santa Bárbara"),
(null,30,"Tucapel"),
(null,30,"Yumbel"),
(null,30,"Alto Biobío"),
(null,31,"Chillán"),
(null,31,"Bulnes"),
(null,31,"Cobquecura"),
(null,31,"Coelemu"),
(null,31,"Coihueco"),
(null,31,"Chillán Viejo"),
(null,31,"El Carmen"),
(null,31,"Ninhue"),
(null,31,"Ñiquén"),
(null,31,"Pemuco"),
(null,31,"Pinto"),
(null,31,"Portezuelo"),
(null,31,"Quillón"),
(null,31,"Quirihue"),
(null,31,"Ránquil"),
(null,31,"San Carlos"),
(null,31,"San Fabián"),
(null,31,"San Ignacio"),
(null,31,"San Nicolás"),
(null,31,"Treguaco"),
(null,31,"Yungay"),
(null,32,"Temuco"),
(null,32,"Carahue"),
(null,32,"Cunco"),
(null,32,"Curarrehue"),
(null,32,"Freire"),
(null,32,"Galvarino"),
(null,32,"Gorbea"),
(null,32,"Lautaro"),
(null,32,"Loncoche"),
(null,32,"Melipeuco"),
(null,32,"Nueva Imperial"),
(null,32,"Padre Las Casas"),
(null,32,"Perquenco"),
(null,32,"Pitrufquén"),
(null,32,"Pucón"),
(null,32,"Saavedra"),
(null,32,"Teodoro Schmidt"),
(null,32,"Toltén"),
(null,32,"Vilcún"),
(null,32,"Villarrica"),
(null,32,"Cholchol"),
(null,33,"Angol"),
(null,33,"Collipulli"),
(null,33,"Curacautín"),
(null,33,"Ercilla"),
(null,33,"Lonquimay"),
(null,33,"Los Sauces"),
(null,33,"Lumaco"),
(null,33,"Purén"),
(null,33,"Renaico"),
(null,33,"Traiguén"),
(null,33,"Victoria"),
(null,34,"Valdivia"),
(null,34,"Corral"),
(null,34,"Lanco"),
(null,34,"Los Lagos"),
(null,34,"Máfil"),
(null,34,"Mariquina"),
(null,34,"Paillaco"),
(null,34,"Panguipulli"),
(null,35,"La Unión"),
(null,35,"Futrono"),
(null,35,"Lago Ranco"),
(null,35,"Río Bueno"),
(null,36,"Puerto Montt"),
(null,36,"Calbuco"),
(null,36,"Cochamó"),
(null,36,"Fresia"),
(null,36,"Frutillar"),
(null,36,"Los Muermos"),
(null,36,"Llanquihue"),
(null,36,"Maullín"),
(null,36,"Puerto Varas"),
(null,37,"Castro"),
(null,37,"Ancud"),
(null,37,"Chonchi"),
(null,37,"Curaco de Vélez"),
(null,37,"Dalcahue"),
(null,37,"Puqueldón"),
(null,37,"Queilén"),
(null,37,"Quellón"),
(null,37,"Quemchi"),
(null,37,"Quinchao"),
(null,38,"Osorno"),
(null,38,"Puerto Octay"),
(null,38,"Purranque"),
(null,38,"Puyehue"),
(null,38,"Río Negro"),
(null,38,"San Juan de la Costa"),
(null,38,"San Pablo"),
(null,39,"Chaitén"),
(null,39,"Futaleufú"),
(null,39,"Hualaihué"),
(null,39,"Palena"),
(null,40,"Coihaique"),
(null,40,"Lago Verde"),
(null,41,"Aisén"),
(null,41,"Cisnes"),
(null,41,"Guaitecas"),
(null,42,"Cochrane"),
(null,42,"O'Higgins"),
(null,42,"Tortel"),
(null,43,"Chile Chico"),
(null,43,"Río Ibáñez"),
(null,44,"Punta Arenas"),
(null,44,"Laguna Blanca"),
(null,44,"Río Verde"),
(null,44,"San Gregorio"),
(null,45,"Cabo de Hornos (Ex-Navarino)"),
(null,45,"Antártica"),
(null,46,"Porvenir"),
(null,46,"Primavera"),
(null,46,"Timaukel"),
(null,47,"Natales"),
(null,47,"Torres del Paine"),
(null,48,"Santiago"),
(null,48,"Cerrillos"),
(null,48,"Cerro Navia"),
(null,48,"Conchalí"),
(null,48,"El Bosque"),
(null,48,"Estación Central"),
(null,48,"Huechuraba"),
(null,48,"Independencia"),
(null,48,"La Cisterna"),
(null,48,"La Florida"),
(null,48,"La Granja"),
(null,48,"La Pintana"),
(null,48,"La Reina"),
(null,48,"Las Condes"),
(null,48,"Lo Barnechea"),
(null,48,"Lo Espejo"),
(null,48,"Lo Prado"),
(null,48,"Macul"),
(null,48,"Maipú"),
(null,48,"Ñuñoa"),
(null,48,"Pedro Aguirre Cerda"),
(null,48,"Peñalolén"),
(null,48,"Providencia"),
(null,48,"Pudahuel"),
(null,48,"Quilicura"),
(null,48,"Quinta Normal"),
(null,48,"Recoleta"),
(null,48,"Renca"),
(null,48,"Joaquín"),
(null,48,"San Miguel"),
(null,48,"San Ramón"),
(null,48,"Vitacura"),
(null,49,"Puente Alto"),
(null,49,"Pirque"),
(null,49,"San José de Maipú"),
(null,50,"Colina"),
(null,50,"Lampa"),
(null,50,"Tiltil"),
(null,51,"San Bernardo"),
(null,51,"Buin"),
(null,51,"Calera de Tango"),
(null,51,"Paine"),
(null,52,"Melipilla"),
(null,52,"Alhué"),
(null,52,"Curacaví"),
(null,52,"María Pinto"),
(null,52,"San Pedro"),
(null,53,"Talagante"),
(null,53,"El Monte"),
(null,53,"Isla de Maipo"),
(null,53,"Padre Hurtado"),
(null,53,"Peñaflor");

Creative Commons License

Listado de Regiones, Provincias y regiones de chile para MySQL by David Rivera is licensed under a Creative Commons Atribución 2.0 Chile License.
Based on a work at www.subdere.gov.cl.