Mes: julio 2024

-- funciones de compresión
-- COMPRESS y UNCOMPRESS

-- Si yo tengo información que pueda comprimirse
select length(repeat('ab',500)) foo;

select compress(repeat('ab',500)) foo;
-- Paso de una longitud de 1000 caracteres a 22
select length(compress(repeat('ab',500))) foo;

select length(description) foo, length(compress(description)) foo2
from film;

update film set comprimido=compress(description);

SELECT cast(uncompress(comprimido) as char) FROM thecorner_sakila.film;

-- Encriptar y desencriptar
-- Porque hay veces que necesitamos almacenar información
-- que no queremos que se vea pero queremos
-- obtenerla de nuevo
-- P. ej. tarjeta de crédito

-- Para usamos el par aes_encrypt y aes_decrypt
-- Nos sirven para encriptar una información con una contraseña
-- Y con la misma contraseña podemos desencriptar
-- Nos devuelve un objeto binario, si lo tenemos que almacenar
-- tenemos que usar BLOB o varbinary
select aes_encrypt('cadena a encriptar','cadena que es la clave') foo;
select aes_decrypt(aes_encrypt('cadena a encriptar','cadena que es la clave'),'cadena que es la clave') foo;

-- SIempre tenemos que tener una clave, y la usamos tanto para encriptar
-- como para desencriptar. Si no la conocemos no podemos desencriptar

update actor set apellido=aes_encrypt(last_name,'contraseña');

-- Usando la contraseña puedo decodificar el campo encriptado
-- Lo convierto a cadena para poder leerlo
select cast(aes_decrypt(apellido,'contraseña') as char) ap from actor;

-- Si uso contraseñas cuanto más largas y complicadas mejor
-- Muchas veces se usan cosas como esta:

update actor set apellido=aes_encrypt(last_name,sha2('contraseña',512));
select cast(aes_decrypt(apellido,sha2('contraseña',512)) as char) ap from actor;

SELECT last_name, cast(apellido as char) apell, cast(aes_decrypt(apellido,sha2('contraseña',512)) as char) ap FROM thecorner_sakila.actor;

-- Puedo generar contraseñas que tengan en cuenta datos del registro
update actor set apellido=aes_encrypt(last_name,sha2(concat(actor_id,'contraseña'),512));

-- Las funciones de encriptación
-- Hay ciertos datos que se tienen que guardar encriptados
-- filtraciones de bases de datos hay siempre
-- Si yo guardo el dato encriptado como se si es igual a algo
-- Si yo quiero saber si la contraseña que me ha introducido un usuario
-- Es igual a la que tengo almacenada lo único que tengo que hace
-- Es encriptar la contraseña que me han introducido y comprobar que es igual
-- a la de la base de datos
-- Porque las funciones de encriptación con la misma entrada
-- Devuelven la misma salida
-- Nunca a partir de los datos encriptados se puede obtener el valor original
-- El problema es que se pueden generar diccionarios con combinaciones de letras
-- Y no hace falta poder obtener el valor original
-- hay muchas páginas que hacen md5 reverse (https://md5.gromweb.com/ , https://md5decrypt.net/en/)
-- En realidad son funciones de HASH: obtener un número único más pequeño a partir de
-- un conjunto de datos, como en el DNI
-- 12345678 Z (Z es el HASH de 12345678)

-- Vamos al turrón ¿Cómo codificar?
-- La más simple_ md5 devuelve una cadena de 32 caracteres de longitud

select md5('cualquier cosa');
select md5('a');
select md5('cualquier cosa en un lugar de la mancha de cuyo nombre no quiero acordarme') foo;

-- No se recomienda usar md5 porque tiene unos años y está muy investigada
-- Sobre todo hay muchos diccionarios con casi todas las combinaciones
-- SI yo al md5 le concateno una cadena aleatoria gano en complejidad

select  md5('cadena') res;
select  md5(concat('cadena','una cadena aletoria con cosas raras &(4&%$U0atgoNBl3vl8gGauSUhZUVxJH59lrdB')) res;

-- Ahora se usa el SHA que hay SHA1  SHA2
-- Esta función es más segura que MD5

select sha('cadena');
select sha2('cadena',224);

-- El SHA2 viene en varios sabores: 224, 256, 384, 512
-- Número de bits de longitud de la clave generada, cuanto más grande
-- más seguro y también más coste de cálculo

select sha2('cadena',256);
select sha2('cadena',384);
select sha2('cadena',512);

-- Mi recomendación particular es que concatenemos una cadena incluso
-- si usamos SHA2 '%&34m.ñAA'

update actor set password=sha2(concat(first_name,'%&34m.ñAA'),512);

-- Como se yo si por ejemplo me entra un usuario y me dice
-- Soy el usuario 1 y mi password es 'PENELOPE'
-- El resultado de sha2(concat('PENELOPE','%&34m.ñAA'),512)
-- debería ser igual a lo que tengo en la BD
select * from actor
where actor_id=1 and
password=sha2(concat('PENELOPE','%&34m.ñAA'),512);

-- Esto no me devuelve nada porque la contraseña no coincide
select * from actor
where actor_id=1 and
password=sha2(concat('penelope','%&34m.ñAA'),512);

-- Lo mismo para cualquier actor

select * from actor
where actor_id=152 and
password=sha2(concat('BEN','%&34m.ñAA'),512);

-- Hay mil sitios que nos permiten decodificar así que cuidado
-- https://10015.io/tools/sha512-encrypt-decrypt

-- Mostrar los nombres de los clientes que han alquilado 
-- una película los fines de semana

select distinct first_name, last_name
from customer c join rental r on c.customer_id=r.customer_id -- Uso alias para evitar repetir el nombre de la tabla
where weekday(rental_date)>=5; -- weekday(rental_date) in (5,6) weekday(rental_date) between 5 and 6

-- Mostrar el total de pagos realizados en 2006

select sum(amount) total
from payment
where year(payment_date)=2006;

-- Mostrar las películas alquiladas en agosto de 2005

select distinct f.* from film f
join inventory i on f.film_id=i.film_id
join rental r on i.inventory_id=r.inventory_id
where year(rental_date)=2005 and month(rental_date)=8;

-- ¿En qué mes de qué año se han alquilado más películas?

select year(rental_date) anyo, month(rental_date) mes, count(rental_id) total
from rental
group by anyo,mes
order by total desc
limit 1;

-- si soy purista y quiero comprobar repetidos

select year(rental_date) anyo, month(rental_date) mes, count(rental_id) total
from rental
group by anyo,mes
having total=(
	select  count(rental_id) total
	from rental
	group by year(rental_date) , month(rental_date) 
	order by total desc
	limit 1
);

-- Hacer una consulta que nos muestre el total de pagos 
-- realizados entre semana y en fin de semana

select if(weekday(payment_date)<5,'Diario','Fin de semana') tipo, sum(amount) total
from payment
group by tipo;

select case 
	when weekday(payment_date)<5 then 'Diario'
	else 'Fin de semana'
end tipo, sum(amount) total
from payment
group by tipo;

-- En esta solución Jaume utiliza una subconsulta para crear una variable virtual
-- weekday_payment (los pagos de fin de semana)

SELECT SUM(`amount`) AS total_payments, weekday_payment, SUM(`amount`) - weekday_payment AS weekend_payment
FROM `payment`
JOIN (
-- Aquí se usa la subconsulta para crear una variable
    SELECT SUM(`amount`) AS weekday_payment
    FROM `payment`
    WHERE WEEKDAY(`payment_date`) < 5
) AS weekday_payment_tmp;

-- Funciones matemáticas
-- Mysql como no podía ser menos incorpora funciones para realizar calculos matemáticos
-- Tenemos los sospechosos habituales: logaritmos, trigonométricas...

-- Valor absoluto: Se usa porque si tenga restas (de fechas, de valores...)

SELECT ABS(-5) AS valor_absoluto;

-- Redondear

SELECT ROUND(3.14159) AS redondeo_entero, ROUND(3.14159, 2) AS redondeo_dos_decimales,
sqrt(2) raiz, round(sqrt(2),3) raiz_redondeada;

-- Truncate nos quita los decimales SIN redondear

SELECT TRUNCATE(1.223,1) a, TRUNCATE(1.263,1) b;

-- round vs truncate

select round(1.2), truncate(1.2,0),round(1.5), truncate(1.5,0),round(1.8), truncate(1.8,0);

-- Trigonométricas y logaritmos...

SELECT pi(),SIN(PI()/2) AS seno_90_grados, COS(0) AS coseno_0_radianes, TAN(PI()/4) AS tangente_45_grados;
SELECT LOG(10) AS logaritmo_10;

-- Raices y exponente

SELECT POW(2,4), sqrt(2), POW(65536,1/4);

-- Números aleatorios
-- el primero es un número aleatorio entre 0 y 1 (no incluído)
-- El segundo es la fórmula para obtener números entre 1 y 6
-- El tercero es el mismo pero si le pongo un valor se utiliza como semilla
-- Y siempre nos devolverá lo mismo
select rand(), FLOOR(1 + (RAND() * 6)), FLOOR(1 + (RAND(15) * 6));

-- A veces se usa random para esto:

select * from actor
order by rand()

-- Funciones de fecha
-- Podemos seleccionar el momento actual, la fecha actual y la hora actual

select now() ahora, curdate() fecha,curtime() hora;

-- Una vez tenemos una fecha podemos obtener todo tipo de información de la misma

select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());

select dayofyear(now()) dia_anyo,dayofweek(now()) dia_semana,weekday(now()) dia_semana0,dayname(now()) nombre,last_day(now()) dia_ultimo,microsecond(now()) microsegundos,week(now()) semana;

-- Que alquileres se han hecho en martes

select title, rental_date from film join inventory using(film_id) join rental using(inventory_id)
where weekday(rental_date)=1;

select dayname(rental_date) dia, count(rental_date) from film join inventory using(film_id) join rental using(inventory_id)
group by dia;

-- Con extract e intervalos

select extract(microsecond from now()) m,extract(second from now()) s,
extract(minute from now()) mi,extract(hour from now()) h,
extract(day from now()) d,extract(week from now()) w,
extract(month from now()) mo,extract(quarter from now()) q,
extract(year from now()) y,extract(HOUR_MICROSECOND from now()) sm;

-- Formatear fechas

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i:%s') AS fecha_formateada;

-- Sumar o restar días

SELECT ADDDATE(NOW(), 7) AS fecha_en_7_dias, SUBDATE(NOW(), 3) AS fecha_hace_3_dias;

-- Sumar o restar cualquier intervalo

SELECT DATE_ADD(now(),INTERVAL 1 DAY);
SELECT DATE_ADD('2018-05-01',INTERVAL -1 YEAR);

SELECT DATE_SUB('2018-05-01',INTERVAL -1 DAY);
SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);

-- pedidos para la próxima semana
-- where dia_pedido between now() and adddate(now(),7)

-- Restar fechas

-- En días
SELECT DATEDIFF(NOW(), '2024-01-01') ;

-- Con un intervalo determinado

SELECT TIMESTAMPDIFF(MINUTE,now(),'2024-01-01 12:05:55');

-- Si el case os permite evaluar varias opciones
-- El if solamente una condición y valor si cierto, valor si falso
-- Se parece mucho al operador ternario
-- if(condicion,valor_si_cierto,valor_si_falso)

select title, if(length>120,'Tostón','Entretenida') tipo
from film;

select first_name,last_name, 
if(right(first_name,1)=right(last_name,1),'SI','NO') final
from actor;

-- Tenemos dos funciones que nos permiten detectar nulos
-- Porque al concatenar con nulos se 'contagian'

select address,address2,postal_code,concat(address,address2,postal_code) dir
from address;

select 1+null+99;

-- Para solucionar esto tenemos
-- ifnull(valor, valor si nulo)

select address,address2,ifnull(postal_code,'NO POSTAL CODE') postal_code,concat(address,address2,ifnull(postal_code,'')) dir
from address;

select 1+ifnull(null,0)+99;

-- Podemos utilizar COALESCE
-- Es más genérico, le paso una lista de valores y me devuelve el primero que no es nulo

select address,address2,coalesce(postal_code,'NO POSTAL CODE') postal_code,concat(address,address2,coalesce(postal_code,'')) dir
from address;

select 1+coalesce(null,null,null,0)+99;

https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html

https://www.w3schools.com/sql/func_mysql_coalesce.asp

-- Crear una consulta que me 
-- muestre las iniciales de los clientes en formato 'A.D.'

select concat_ws('. ',substring(first_name,1,1),substring(last_name,1,1),'') nombre
from customer;

select concat(substring(first_name,1,1),'. ',substring(last_name,1,1),'.') nombre
from customer;

select concat(left(first_name,1),'. ',left(last_name,1),'.') nombre
from customer;

-- Crear una consulta que me muestre los paises que tengan una ciudad que empiece por la misma letra que el país.

select distinct country from country
join city using(country_id)
where substring(country,1,1)=substring(city,1,1);

-- Crear una consulta que me muestre todos los nombres de actores que
-- contengan la letra z pero sin la letra z es decir si tengo un actor gomez me mostrará gome

select replace(first_name,'Z','') nombre, replace(last_name,'Z','') apellido from actor
where first_name like '%z%' or last_name like '%z%';

select replace(upper(first_name),'Z','') nombre, replace(upper(last_name),'Z','') apellido from actor
where first_name like '%z%' or last_name like '%z%';

-- Crear una consulta que me muestre el título de la película y 'barata' si su replacement_const es menor de 15,
-- 'cara' si es mayor de 25 y 'media' en cualquier otro caso.

select title,
 case
	when replacement_cost<15 then 'barata'
	when replacement_cost>25 then 'cara'
	else 'media'
	end tipo
 from film;
 
 -- Crear una consulta que me muestre el total de alquileres de las películas
 -- y me lo clasifique en 3 tramos 'alto', 'medio' y 'bajo' dependiendo de la
 -- cantidad de veces que se ha alquilado. Pensad bien que valores vamos a usar como límites
 
 select title, case
 when count(rental_id) <15 then 'bajo'
 when count(rental_id) <25 then 'medio'
 else 'alto'
 end tipo
 from film join inventory using(film_id)
 join rental using(inventory_id)
 group by film_id;
 
 -- Que pasa si queremos que sea dinámico, es decir, que si tengo más alquileres
 -- el rango cambie
 
 SELECT 
    COUNT(ren.`rental_id`) AS total_rental, 
    fil.`title`,
    CASE 
		WHEN COUNT(ren.`rental_id`) > (max_rental / 3) * 2 THEN 'Alto'
		WHEN COUNT(ren.`rental_id`) > (max_rental / 3) THEN 'Medio'
        ELSE 'Bajo'
    END AS type_rental,
    max_rental    
FROM `rental` ren
JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
JOIN `film` fil ON inv.`film_id` = fil.`film_id`
JOIN (
    SELECT 
        MAX(count_total) AS max_rental
    FROM (
        SELECT COUNT(ren.`rental_id`) AS count_total
        FROM `rental` ren
        JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
        JOIN `film` fil ON inv.`film_id` = fil.`film_id`
        GROUP BY inv.`film_id`
    ) AS total_rental_tmp
) AS max_rental_tmp
GROUP BY inv.`film_id`
ORDER BY total_rental DESC;

-- Otra manera de hacerlo

create view alquileres_por_pelicula as
 SELECT fil.film_id,title, COUNT(ren.`rental_id`) AS total
        FROM `rental` ren
        JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
        JOIN `film` fil ON inv.`film_id` = fil.`film_id`
        GROUP BY fil.`film_id`;
        
-- con esta vista creada la cosa se quedda así:
select title, CASE 
		WHEN total > ((select max(total) from alquileres_por_pelicula) / 3) * 2 THEN 'Alto'
		WHEN total > ((select max(total) from alquileres_por_pelicula) / 3) THEN 'Medio'
        ELSE 'Bajo'
    END AS type_rental
    from alquileres_por_pelicula;

-- Funciones de control de flujo
-- Tienen un comportamiento similar al if de programación
-- Sirven para devolver valores dependiendo de condiciones

-- CASE sirve para evaluar múltiples valores de un campo
-- O múltiples condiciones

-- CASE value WHEN compare_value THEN result 
-- [WHEN compare_value THEN result ...] [ELSE result] END

select case rating 
	when 'g' then 'Para todos los públicos'
	when 'r' then 'Para mayores de edad'
	when 'pg' then 'menores acompañados'
	else 'No lo se'
end clasificacion
from film;

-- CASE WHEN condition THEN result 
-- [WHEN condition THEN result ...] [ELSE result] END

select case 
	when length<90 then 'Corta'
	when length<120 then 'Media'
	else 'Larga'
end duracion
from film;

select first_name, case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end letra
from actor;

select first_name
from actor
order by  case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end;

select first_name
from actor
where  case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end='A';

select title, case
	when rating='R' and length>120 then 'Adultos intelectuales'
    when rating<>'R' and length>120 then 'Todos los públicos con aguante'
    else 'Todos los públicos'
    end tipo
from film;

-- Funciones de conversión de tipos
-- CAST es estándar y nos permite convertir un tipo en otro
-- Los nombres de los tipos cuando convertimos no son los mismos
-- que cuando los creamos
-- BINARY,CHAR,DATE,DATETIME,TIME,DECIMAL,SIGNED,UNSIGNED
-- Todos los tipos los tenéis en https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
select cast('1.23' as DECIMAL(8,2)) n;

-- Para qué convertir: si tengo valores de cadena que yo sé que
-- son numéricos o de fecha para operar y extraer información
-- Y al revés, si tengo datos numéricos para concatenar o extraer información
-- por posición

select convert('123',SIGNED) n;

-- Usando CAST
SELECT CAST('2023-01-01' AS DATE) fecha;

-- Usando CONVERT
SELECT CONVERT('2023-01-01', DATE) fecha;