¶ Una tabla
INSERT INTO estados VALUES
(DEFAULT,"Activa"),
(DEFAULT,"Inactiva"),
(DEFAULT,"Liquidada"),
(DEFAULT,"Suspendida");
create table examen.semilla(
id int);
insert into examen.semilla values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
create table examen.numeros(
id int);
set @i:=0;
insert into examen.numeros
select @i:=@i+1
from semilla s1 cross join semilla s2
cross join semilla s3 cross join semilla s4
cross join semilla s5 cross join semilla s6;
INSERT INTO cnae VALUES (DEFAULT, "0000","SIN CLASIFICAR");
INSERT INTO comerciales VALUES
(DEFAULT, "Juan","644 655 666","[email protected]","juan",MD5("juan"),null),
(DEFAULT, "Perico","666 655 644","[email protected]","perico",MD5("perico"),1),
(DEFAULT, "Andrés","633 622 611","[email protected]","andres",MD5("andres"),1),
(DEFAULT, "Amparito","622 655 699","[email protected]","amparito",MD5("amparito"),2),
(DEFAULT, "Gertrudis","600 600 600","[email protected]","ger",MD5("ger"),3),
(DEFAULT, "Genoveva","643 678 699","[email protected]","genoveva",MD5("genoveva"),5);
INSERT INTO clientes
(cif,nombre,direccion,
web,email,telefono,latitud,
longitud,validado,fk_cnae,
fk_user,fk_estado,fk_comercial)
SELECT
CONCAT(
if(n.id%2=0,"A","B"),
truncate(RAND()*10,0),
truncate(RAND()*10,0),
truncate(RAND()*10,0),
truncate(RAND()*10,0),
if(n.id>999,n.id,
if(n.id>99,CONCAT("0",n.id),
if(n.id>9,CONCAT("00",n.id),
CONCAT("000",n.id))))) AS cif,
CONCAT("Cliente ",n.id) nombre,
CONCAT("C/ Del Cliente Nº",n.id) direccion,
CONCAT("cliente",n.id,".com") web,
CONCAT("info@cliente",n.id,".com") email,
CONCAT("9",
truncate(RAND()*10,0),
truncate(RAND()*10,0),
truncate(RAND()*10,0),
truncate(RAND()*10,0),
if(n.id>999,n.id,
if(n.id>99,CONCAT("0",n.id),
if(n.id>9,CONCAT("00",n.id),
CONCAT("000",n.id))))) AS telefono,
0,
0,
if(truncate(RAND()*5,0)%2=0,true,FALSE) validado,
1,
1,
1,
TRUNCATE(1+RAND()*3,0)
FROM numeros n LIMIT 9876;
INSERT INTO portales
SELECT
NULL,
CONCAT("sector",n.id,".com") dominio,
CONCAT("título del sector",n.id) titulo,
CONCAT("descripcion del sector",n.id) descripcion,
0,1,1,1
FROM numeros n LIMIT 1000;
INSERT INTO examen.regiones (nombre)
SELECT nombre FROM geo_normalizada.regiones
ORDER BY id;
INSERT INTO examen.provincias (nombre, fk_region)
SELECT nombre, fk_region FROM geo_normalizada.provincias
ORDER BY id;
INSERT INTO examen.comarcas (nombre)
SELECT nombre FROM geo_normalizada.comarcas
ORDER BY id;
INSERT INTO examen.prov_comarca
SELECT fk_comarca, fk_provincia
FROM geo_normalizada.prov_comarcas;
INSERT INTO portales
SELECT null,
concat(REPLACE(REPLACE(
lower(replace(m.nombre,"'","-"))
," ","-"),"í","i"),".com") AS dominio,
CONCAT_WS(" ","Portal de",m.nombre) as titulo,
CONCAT_WS(" ","Directorio de empresas de",m.nombre) as
descripcion, 0 visitas, 1 coeficiente, 2 , 1
FROM geo_normalizada.municipios m;
INSERT INTO municipios
SELECT null,
m.nombre AS dominio,
m.latitud,
m.longitud,
0,
(select p.id from portales p where
substr(p.titulo,11) =m.nombre) AS fk_portal,
m.fk_comarca,
m.fk_provincia
FROM geo_normalizada.municipios m;
INSERT INTO examen.localidades
SELECT NULL, l.nombre, l.fk_municipio
FROM geo_normalizada.localidades l;
INSERT INTO examen.cpostales
SELECT codpostal
FROM geo_normalizada.cod_postales;
INSERT INTO examen.cp_localidad
SELECT CP.fk_localidad, b.codpostal
FROM geo_normalizada.localidades_cp CP join
geo_normalizada.cod_postales b
ON CP.fk_cod_postal = b.id;
INSERT INTO examen.cnae
SELECT NULL, c.cod_integrado,
c.titulo_cnae2009
FROM geo_normalizada.sector_cnae2009 c;
SET @i:= (SELECT COUNT(*) FROM cnae);
INSERT INTO sectores
SELECT
NULL,
CONCAT("sector",n.id) nombre,
truncate(RAND()*@i,0)+1 AS fk_cnae,
n.id AS fk_portal
FROM numeros n LIMIT 1000;
insert INTO campanas VALUES
(DEFAULT,"Default","https://x.com",1);
insert INTO publicidad VALUES
(DEFAULT,"https://x.com/slide.jpg",1,0,0,1),
(DEFAULT,"https://x.com/lateral.jpg",1,0,0,2),
(DEFAULT,"https://x.com/foot.jpg",1,0,0,3);
CREATE TABLE semilla4
SELECT * FROM semilla LIMIT 4;
INSERT INTO anuncios
SELECT NULL,
ta.id AS fk_publicidad,
ta.id AS fk_tipo,
p.id AS fk_portal,
s4.id AS posicion
FROM tipos_anuncios as ta INNER join
publicidad pu
ON ta.id = pu.fk_tipo
cross JOIN
semilla4 s4 cross JOIN portales p ;
CREATE TABLE diccionario
(id INT, texto VARCHAR(100));
INSERT INTO diccionario VALUES
(1,"Navidad 2022"),
(2,"Navidad 2023"),
(3,"Black Friday 2022"),
(4,"Black Friday 2023"),
(5,"Rebajas Enero 2022"),
(6,"Rebajas Enero 2023"),
(7,"Rebajas Agosto 2022"),
(8,"Rebajas Agosto 2023"),
(9,"San Valentín 2022"),
(10,"San Valentín 2023"),
(11,"Carnaval 2022"),
(12,"Carnaval 2023"),
(13,"Publicidad corporativa 2022"),
(14,"Publicidad corporativa 2023");
INSERT INTO campanas
SELECT
NULL,
CONCAT((SELECT texto
FROM diccionario d where
d.id = 1+(c.id MOD 14))," ",c.nombre) t1,
lower(
CONCAT("http://",replace(c.nombre," ",""),".com","/",
REPLACE((SELECT texto
FROM diccionario d where
d.id = 1+(c.id MOD 14))," ","")
)
) t2,
c.id as fk
FROM clientes c
WHERE c.validado = TRUE;
INSERT INTO cargos (cargo) VALUES
("Gerente"),("Marketing"),("Financiero"),("Empleado");
UPDATE clientes
SET acronimo =
if (id<10,
CONCAT("CLT00",clientes.id),
if (id<100, CONCAT("CLT0",clientes.id), CONCAT("CLT",clientes.id) )); INSERT INTO publicidad SELECT NULL AS id, concat(c.link,"/imagen-",t.ancho,"-",t.alto,".jpg") as link, c.id AS fk_campana, 0,0, t.id as fk_tipo FROM campanas c cross join tipos_anuncios t WHERE c.fk_cliente !=1; UPDATE anuncios A SET fk_publicidad = (SELECT id FROM publicidad P where A.fk_tipo = P.fk_tipo ORDER BY RAND() LIMIT 1) WHERE A.id MOD 50 = 0; INSERT INTO facturas SELECT DISTINCT NULL, SYSDATE(), c.fk_cliente FROM anuncios a JOIN publicidad p ON a.fk_publicidad = p.id JOIN campanas c ON p.fk_campana = c.id WHERE fk_cliente !=1; UPDATE facturas set fecha = DATE_ADD("2021/01/01",interval RAND()*720 DAY); INSERT INTO lineas_fac SELECT NULL AS id,f.fecha AS fecha_inicio, DATE_ADD(f.fecha,INTERVAL 12+ RAND()*12 MONTH) AS fecha_fin, f.id AS fk_factura, c.id AS fk_campana, tp.precio_base AS precio, a.id AS fk_anuncio FROM tipos_anuncios AS tp JOIN anuncios AS a ON tp.id = a.fk_tipo JOIN publicidad AS p ON a.fk_publicidad = p.id JOIN campanas AS c ON p.fk_campana = c.id JOIN clientes cl ON c.fk_cliente = cl.id JOIN facturas AS f ON cl.id = f.fk_cliente WHERE cl.id != 1; CREATE TABLE nombres ( id INT AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(30), apellido VARCHAR(30)); INSERT INTO nombres VALUES (DEFAULT,"María","Pedraza"), (DEFAULT,"Laura","Martínez"), (DEFAULT,"Felipe","Hermoso"), (DEFAULT,"Ramón","Villanueva"), (DEFAULT,"Lucio","Sobrino"), (DEFAULT,"Armando","Camorra"), (DEFAULT,"Isabel","Ayuso"), (DEFAULT,"Pedro","Piqueras"), (DEFAULT,"Rodolfo","Langostino"), (DEFAULT,"Pablo","Iglesias"), (DEFAULT,"Carmen","López"), (DEFAULT,"Remigio","Carpintero"), (DEFAULT,"Victoria","Abril"), (DEFAULT,"Joselín","Ubrique"), (DEFAULT,"Jesús","Gracias"), (DEFAULT,"Marcelino","Pan y Vino"), (DEFAULT,"Perico","Sánchez"); INSERT INTO contactos (nombre, telefono, fk_cliente, fk_cargo) SELECT CONCAT_WS(" ",(SELECT nombre FROM nombres ORDER BY RAND() LIMIT 1) , (SELECT apellido FROM nombres ORDER BY RAND() LIMIT 1) ) nombre, CONCAT("666",(SELECT id from numeros where id > 99999 ORDER BY RAND() LIMIT 1)) telefono,
(SELECT id FROM clientes ORDER BY RAND() LIMIT 1) AS fk_clientes,
(SELECT id FROM cargos ORDER BY RAND() LIMIT 1) AS fk_cargos
FROM numeros LIMIT 500;
UPDATE clientes
SET fk_cnae =
(SELECT id FROM cnae
ORDER BY RAND() LIMIT 1),
fk_cp = (SELECT codigo FROM cpostales
ORDER BY RAND() LIMIT 1)
WHERE TRUE;
--
UPDATE clientes C
SET fk_localidad =
(SELECT CP.fk_localidad FROM cp_localidad CP
WHERE C.fk_cp = CP.fk_cp LIMIT 1);
--
UPDATE clientes C
SET fk_comercial =
(SELECT id FROM comerciales
ORDER BY RAND() LIMIT 1);
--
INSERT cliente_sector
SELECT S.id, C.id FROM clientes C JOIN cnae CN
ON C.fk_cnae = CN.id JOIN sectores S
ON CN.id = S.fk_cnae;
--
TRUNCATE crm;
INSERT IGNORE crm
SELECT DATE_ADD("2020/01/01",interval RAND()*1080 DAY),fk_comercial, id,
CONCAT("llamada telefónica al ", telefono)
FROM clientes
WHERE validado = TRUE AND
id MOD 2 =0
union
SELECT DATE_ADD("2020/01/01",interval RAND()*1080 DAY),fk_comercial, id,
CONCAT("envío de correo electrónico ", email)
FROM clientes
WHERE id MOD 3 =0;
INSERT INTO visitas
SELECT NULL,
DATE_ADD("2020/01/01",INTERVAL N.id DAY),
truncate(RAND()*150,0),
P.id
FROM
numeros N cross join portales P
WHERE N.id <=720;
UPDATE portales SET
visitas = (SELECT 50+round(RAND()*300,0));
DROP TABLE numeros;
ALTER TABLE localidades
ADD COLUMN altura INT,
ADD COLUMN longitud decimal(10,6),
ADD COLUMN latitud decimal(10,6);
UPDATE localidades l
SET
altura = (SELECT altura FROM geo_normalizada.localidades m WHERE m.id = l.id),
longitud = (SELECT round(longitud,4) FROM geo_normalizada.localidades m WHERE m.id = l.id),
latitud = (SELECT round(latitud,4) FROM geo_normalizada.localidades m WHERE m.id = l.id);
ALTER TABLE municipios
ADD COLUMN altura INT AFTER longitud;
update municipios m
SET
altura = (SELECT round(avg(altura),0) FROM geo_normalizada.localidades l WHERE l.fk_municipio = m.id),
longitud = (SELECT round(avg(longitud),4) FROM geo_normalizada.localidades l WHERE l.fk_municipio = m.id),
latitud = (SELECT round(avg(latitud),4) FROM geo_normalizada.localidades l WHERE l.fk_municipio = m.id);
UPDATE examen.municipios m
SET m.habitantes = (SELECT n.hab FROM geo_normalizada.municipios_hab n
WHERE n.nombre = m.nombre)
UPDATE clientes c
SET c.fk_localidad = (SELECT cp.fk_localidad FROM cp_localidad cp WHERE cp.fk_cp=c.fk_cp LIMIT 1)
-
-Workbench geoplaces.mwb
-Base de datos - todo_geo_places.7z
-geo_normalizada_2024-25 -
Ejemplos de ficheros construdata21.
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema geo
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema geo
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS geo DEFAULT CHARACTER SET utf8mb4 ;
USE geo ;
-- -----------------------------------------------------
-- Table geo.utcs
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.utcs (
id INT NOT NULL AUTO_INCREMENT,
utc VARCHAR(6) NULL,
PRIMARY KEY (id))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.timezones
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.timezones (
id INT NOT NULL AUTO_INCREMENT,
zona VARCHAR(15) NULL,
fk_utc INT NOT NULL,
PRIMARY KEY (id),
INDEX fk_timezones_utcs1_idx (fk_utc ASC),
CONSTRAINT fk_timezones_utcs1
FOREIGN KEY (fk_utc)
REFERENCES geo.utcs (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.regiones
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.regiones (
id INT NOT NULL AUTO_INCREMENT,
nombre VARCHAR(26) NOT NULL,
fips VARCHAR(4) NULL,
fk_timezone INT NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX nombre_UNIQUE (nombre ASC),
INDEX fk_regiones_timezones1_idx (fk_timezone ASC),
CONSTRAINT fk_regiones_timezones1
FOREIGN KEY (fk_timezone)
REFERENCES geo.timezones (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.comarcas
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.comarcas (
id INT NOT NULL AUTO_INCREMENT,
nombre VARCHAR(42) NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX nombre_UNIQUE (nombre ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.provincias
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.provincias (
id INT NOT NULL AUTO_INCREMENT,
nombre VARCHAR(22) NOT NULL,
fk_region INT NOT NULL,
codigo SMALLINT NULL,
iso2 VARCHAR(5) NULL,
division VARCHAR(8) NULL,
PRIMARY KEY (id),
UNIQUE INDEX nombre_UNIQUE (nombre ASC),
INDEX fk_provincias_regiones_idx (fk_region ASC),
UNIQUE INDEX codigo_UNIQUE (codigo ASC),
CONSTRAINT fk_provincias_regiones
FOREIGN KEY (fk_region)
REFERENCES geo.regiones (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.prov_comarcas
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.prov_comarcas (
id INT NOT NULL AUTO_INCREMENT,
fk_provincia INT NOT NULL,
fk_comarca INT NOT NULL,
PRIMARY KEY (id),
INDEX fk_comarcas_has_provincias_provincias1_idx (fk_provincia ASC),
INDEX fk_comarcas_has_provincias_comarcas1_idx (fk_comarca ASC),
UNIQUE INDEX prov_comarcas_indice (fk_provincia ASC, fk_comarca ASC),
CONSTRAINT fk_comarcas_has_provincias_comarcas1
FOREIGN KEY (fk_comarca)
REFERENCES geo.comarcas (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_comarcas_has_provincias_provincias1
FOREIGN KEY (fk_provincia)
REFERENCES geo.provincias (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.municipios
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.municipios (
id INT NOT NULL AUTO_INCREMENT,
nombre VARCHAR(47) NOT NULL,
stat VARCHAR(5) NULL,
fk_prov_camarca INT NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX nombre_UNIQUE (nombre ASC),
INDEX fk_municpios_prov_comarcas1_idx (fk_prov_camarca ASC),
CONSTRAINT fk_municpios_prov_comarcas1
FOREIGN KEY (fk_prov_camarca)
REFERENCES geo.prov_comarcas (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.localidades
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.localidades (
id INT NOT NULL AUTO_INCREMENT,
nombre VARCHAR(56) NOT NULL,
lat DOUBLE NULL,
lng DOUBLE NULL,
altura INT NULL,
fk_municipio INT NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX nombre_UNIQUE (nombre ASC),
INDEX fk_localidades_municpios1_idx (fk_municipio ASC),
CONSTRAINT fk_localidades_municpios1
FOREIGN KEY (fk_municipio)
REFERENCES geo.municipios (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.cod_postales
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.cod_postales (
id INT NOT NULL AUTO_INCREMENT,
codigo VARCHAR(5) NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX codigo_UNIQUE (codigo ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.unidades_t
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.unidades_t (
id INT NOT NULL AUTO_INCREMENT,
nut VARCHAR(5) NOT NULL,
fk_region INT NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX codigo_UNIQUE (nut ASC),
INDEX fk_unidades_t_regiones1_idx (fk_region ASC),
CONSTRAINT fk_unidades_t_regiones1
FOREIGN KEY (fk_region)
REFERENCES geo.regiones (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table geo.localidades_cp
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS geo.localidades_cp (
fk_cod_postal INT NOT NULL,
fk_localidad INT NOT NULL,
PRIMARY KEY (fk_cod_postal, fk_localidad),
INDEX fk_cod_postales_has_localidades_localidades1_idx (fk_localidad ASC),
INDEX fk_cod_postales_has_localidades_cod_postales1_idx (fk_cod_postal ASC),
CONSTRAINT fk_cod_postales_has_localidades_cod_postales1
FOREIGN KEY (fk_cod_postal)
REFERENCES geo.cod_postales (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_cod_postales_has_localidades_localidades1
FOREIGN KEY (fk_localidad)
REFERENCES geo.localidades (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
truncate regiones;
INSERT INTO regiones
(nombre, fips, fk_timezone)
SELECT DISTINCT
g.Region1, g.FIPS, t.id as fk_timezone
FROM geo_places g, timezones t
WHERE g.Timezone = t.zona;
SELECT * FROM regiones;
SET FOREIGN_KEY_CHECKS=1;