¶ Consultas SQL
- La sentencia SELECT.
- La sentencia SELECT multitabla.
- Selección y ordenación de registros.
- Operadores. Operadores de comparación. Operadores lógicos.
- Consultas de resumen.
- Agrupamiento de registros.
- Agrupamiento de registros (with rollup).
- Comparación de where con having
- Composiciones internas.
- Composiciones externas.
- Resumen JOIN.
- Subconsultas.
- MyISAM - InnoDB
- Índices Full-Text
- Búsquedas IN BOOLEAN MODE
¶ Funciones de cadenas, fechas y matemáticas
¶ Ejercicios
¶ Examen 2ª evaluación
¶ Ejercicios Geo_places
¶ BBDD Geoplaces con datos
SET @ESPANA=(SELECT COUNT(id) FROM localidades);
SET @ISLAS=(select COUNT(DISTINCT l.id)
from localidades l, municipios m,
provincias p, regiones r
where
l.fk_municipio = m.id and
m.fk_provincia = p.id and
p.fk_region = r.id and
(r.nombre like "%islas%" or
r.nombre = "ceuta" or r.nombre = "melilla"));
set @peninsula:=(select @espana-@islas);
set @lm:=39.840406;
set @lnm:=-2.9949505;
select count(distinct ll.fk_localidad)*100/@peninsula
from loc_localizaciones ll,
geo_localizaciones g where
ll.fk_geolocalizacion = g.id and
g.latitud<@lm and g.longitud<@lm;
create table n10 as
select 1 n union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10;
Ver 1
set @i:=0;
SET @cl:=(SELECT count(*) FROM cod_localidades);
select @i:=@i+1 num, concat("C",FLOOR(RAND()*100000000)) nif,
FLOOR(RAND()*@cl+1) fk_cod_loc
from n10 t1 join n10 t2 join n10 t3 join n10 t4
limit 8434;
Ver 2
SELECT COUNT(*) INTO @cl FROM geo_2026.cod_localidades;
SELECT
ROW_NUMBER() OVER () AS num,
CONCAT('C', FLOOR(RAND()*100000000)) AS nif,
FLOOR(RAND()*@cl) + 1 AS fk_cod_loc
FROM geo_2026.n10 t1
JOIN geo_2026.n10 t2
JOIN geo_2026.n10 t3
JOIN geo_2026.n10 t4
LIMIT 8434;
SET @i:=0;
SET @ncl:=(SELECT COUNT(*) FROM cod_localidades);
TRUNCATE clientes;
INSERT ignore INTO clientes
SELECT NULL id,
CONCAT(if(round(RAND(),2)<=0.3,"A","B"),
10000000+floor(RAND()*90000000))
AS cif, CONCAT_WS("",NULL,"nombre ",
"empresa ",
@i:=@i+1) AS razon_social,
DATE_ADD("1969/03/27", INTERVAL
floor(RAND()*dateDIFF(NOW(),"1969/03/27"))
DAY) fa, NULL fb,
floor(RAND()*@ncl)+1 fk
FROM n10 t1 JOIN n10 t2 JOIN n10 t3
JOIN n10 t4 JOIN n10 t5 JOIN n10 t6
LIMIT 100000;
SET @tc:=(SELECT COUNT(*) FROM clientes);
SELECT @tc, CONCAT_WS(" ",round(COUNT(*)*100/@tc,2),"%") tpsa,
CONCAT_WS(" ",ROUND((@tc-COUNT(*))*100/@tc,2),"%") tpsl
from clientes
WHERE cif LIKE "a%";
UPDATE clientes
SET razon_social=
CONCAT(" ",razon_social,", S.",
if(SUBSTRING(cif,1,1)="A","A.","L.")),
fecha_baja=
if(RAND()<=0.5,NULL,
DATE_ADD(fecha_alta, INTERVAL
1+floor(RAND()*dateDIFF(NOW(),fecha_alta))
DAY));
SELECT DATEDIFF(fecha_baja,fecha_alta)
FROM clientes
ORDER BY 1 desc;
SELECT DATEDIFF(fecha_baja,fecha_alta)
FROM clientes
WHERE fecha_baja IS NOT null
ORDER BY 1 asc;
CREATE TABLE clientes_tmp as
SELECT c.id,
if(ROUND(RAND(),0)=0,1,-1) *
RAND()*3/100 + avg(gl.latitud) lat,
if(ROUND(RAND(),0)=0,1,-1) *
RAND()*3/100 + avg(gl.longitud) lon
FROM clientes c JOIN
cod_localidades cl
ON c.fk_cod_localidad = cl.id
JOIN loc_localizaciones ll
ON cl.fk_localidad = ll.fk_localidad
JOIN geo_localizaciones gl
ON ll.fk_geolocalizacion = gl.id
GROUP BY c.id
UPDATE clientes C
SET C.latitud =(SELECT lat
FROM clientes_tmp WHERE id= C.id),
C.longitud =(SELECT lon
FROM clientes_tmp WHERE id= C.id);
SELECT COUNT(*) INTO @i FROM cargos;
SELECT COUNT(*) INTO @j FROM clientes;
TRUNCATE contactos;
INSERT INTO contactos
SELECT NULL id,
CONCAT_WS(" ",
(SELECT nombre FROM t_nombre1 ORDER BY rand() LIMIT 1),
(SELECT nombre FROM t_nombre2 ORDER BY rand() LIMIT 1),
(SELECT nombre FROM t_ape1 ORDER BY rand() LIMIT 1),
(SELECT nombre FROM t_ape2 ORDER BY rand() LIMIT 1)) minombre,
if(floor(RAND()*3)=0,"gmail.com",
if(floor(RAND()*2)=0,"hotmail.com",
"yahoo.com")) email,
600000000+floor(RAND()*99999999) tel,
if(floor(RAND()*10)>1,1+floor(RAND()*@i),NULL) fk_cargo,
NULL fk_delegacion, 1+floor(RAND()*@j) fk_cliente
FROM n10 JOIN n10 t2 JOIN n10 t3 JOIN n10 t4 JOIN n10 t5 JOIN n10 t6
LIMIT 300000
UPDATE contactos SET email=CONCAT(REPLACE(nombre," ","."),"@",email)