Categoría: BD

-- 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;

-- Funciones específicas de Mysql
-- Funciones de cadena, de fecha, aritméticas,....

-- Funciones de cadena

-- CONCAT para concatenar cadenas

select CONCAT(first_name,' ',last_name) nombre from actor;

-- Cuidado porque al concatenar un valor nulo con valor válidos el nulo 'se contagia'
-- es decir, concat(cualquier-valor,null)-->null
select  CONCAT(address,' - ',address2,' (',postal_code,')') direccion from address;

-- Subcadenas SUBSTRING(posicion, longitud)
-- Ojo, las cadenas empiezan en 1
select substring(first_name,2,3) from actor;

-- Podemos usar valores negativos
select substring(first_name,-3,1) from actor;

-- Por ejemplo la primera letra:
select substring(first_name,1,1) from actor;

-- La última
select substring(first_name,-1,1) from actor;

-- Las funciones las pudo utilizar en el select o en otras partes de la consulta
-- Por ejemplo: actores cuyo nombre acaba en 'E'
-- El substring lo utilizo en el where
select * from actor
where substring(first_name,-1,1)='e';

-- Actores ordenador por la última letra del nombre
select * from actor
order by substring(first_name,-1,1);

-- longitud de ua cadena

select length(first_name) longitud from actor;

-- Paises ordenados por la longitud de su nombre
select * from country
order by length(country);

-- Pasar a mayúsculas o minúsculas
select upper(country) mayusculas,lower(country) minusculas from country;

-- Puedo reemplazar una cadena por otra

select replace(country,'a','@') pais from country;

-- trim nos elimina espacios del principio y del fin
-- Esto es super útil en la vida real porque ni os imagináis la cantidad de
-- espacios basura que hay en las bases de datos

select trim('    asdasd    ') foo;

-- Yo muchas veces hago cosas como esta
-- Esto me limpia de espacios ese campo
update actor set first_name=trim(first_name);

-- Concatena con el reparador que yo le ponga
select concat_ws(' ',first_name,last_name) nombre from actor;

-- Es muy útil cuando quiero juntar varios campos usando el mismo separador
select concat_ws(' ',address,address2,district,postal_code) direccion from address;

-- Lo índices nos sirven para acelerar búsquedas sobre un campo
-- Lo que hace la base de datos es crear una copia ordenada
-- de los valores de ese campo
-- por lo que al buscar hace la busqueda binaria que es mucho más rápida

-- Crear un índice tiene pROS y CONTRAS
-- PRO: mayor velocidad en la búsqueda
-- CONTRA: Ocupa más espacio y las modificaciones e inserciones tardan más
-- COn la consideración de que hoy en día no vamos cortos de espacio
-- Que el retraso en la inserción es inapreciable
-- Utilizar índices es bastante recomendable

-- ¿Usamos índices para todo? NO
-- Un poco de cabeza:
-- Si tengo consultas que tardan mucho tengo que evaluar usar índices
-- Si yo sé que se realizan muchas consultas sobre ciertos campos
-- Puedo curarme en salud y usar índices
-- Los campos clave son siempre índices
-- Las claves extrangeras son siempre índices
-- Cuando nunca usar índices
-- Cuando tenemos campos con valores que se repiten mucho

-- Podemos tener índices combinados de varios campos
-- No son frecuentes, pero se usan cuando la búsqueda se hace por varios
-- campos a la vez y a lo mejor individualmente, como he dicho antes
-- se repiten mucho

-- Cuando yo creo un índice por defecto se entiende que los valores se pueden repetir
-- Pero puedo crear índices únicos con valores que no se repiten

-- Resumiendo: los índices se crean para acelerar búsquedas, usamos el prefijo
-- ix_ o idx_ tenemos la posibilidad de que sean únicos

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