Mes: marzo 2021

Mini ejercicio funciones

Quiero un listado de películas en las que mostraré:
título
Si el replacement_cost es menor de 20: barato
Si es mayor o igual: caro

SELECT title, if (replacement_cost<20,'barato','caro') tipo FROM sakila.film;

select title, case
when replacement_cost<20 then 'barato'
else 'caro'
end tipo from film

Ejemplos funciones MySql

Todas las funciones:

Referencia de funciones de MySql

Funciones de control de flujo:

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

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

Evalua una condición y dependiendo del resultado nos devuelve un valor u otro.

IF(expr1,valor_si_cierto,valor_si_falso)

Si expresión 1 es cierto devuelve valor_si_cierto y si no devuelve valor_si_falsi

IFNULL(expr1,expr2)

Si la expresión 1 es nulo devuelve expresión 2

select rating,
case rating when 'G' then 'Para todos los públicos'
when 'PG' then 'Menores acompañados'
when 'R' then 'Para adultos'
else 'Otros'
end tipo -- Alias para que no salga un churro
from film

select length,
case when length<70 then 'Cortita'
when length<130 then 'Normal'
else 'Tostón'
end duracion
from film

SELECT *, 
case when amount<4 then 'bajo' when amount <7 then 'medio' else 'alto' end as tipo ,
if (amount<6,'barato','caro') precio,
if (amount between 4 and 6,'medio','') precio2,
if(amount<4,'bajo',if(amount<7,'medio','alto')) tipo2
FROM sakila.payment;

select 
case dayofweek(payment_date)
 when 1 then 'Domingo'
 when 2 then 'Lunes' 
 when 3 then 'Martes' 
 when 4 then 'Miércoles' 
 when 5 then 'Jueves' 
 when 6 then 'Viernes' 
 when 7 then 'Sábado' 
 end dias,count(payment_id) cuenta,
sum(amount) total,avg(amount) media
from payment
group by dias;

select *,
case staff_id when 1 then 'Ana' when 2 then 'Juan' end as empleado
FROM sakila.payment;

select title, if(replacement_cost>20,concat('Descuento: ',replacement_cost*.9) ,replacement_cost) coste from film;

select ifnull(8,0), ifnull(null,0),0+null,concat('Ana',null)

Funciones matemáticas:

ROUND(X), ROUND(X,D)

Redondea un número a las posiciones decimales que queramos

select round(4.9), floor(4.9), 
ceil(4.9),truncate(4.9,0), round(-4.9), 
floor(-4.9), ceil(-4.9),truncate(-4.9,0), sign(4.9),sign(-4.9),abs(-4.9);

Funciones de cadena:

CONCAT(str1,str2,...)

Concatena una o varias cadenas

CONCAT_WS(separator,str1,str2,...)

Lo mismo pero con un separador

LOCATE(substr,str), LOCATE(substr,str,pos)

Devuelve la posición de una cadena dentro de otra

LEFT(str,len)

Devuelve len caracteres de la cadena empezando por la izquierda

LENGTH(str)

Devuelve la longitud de la cadena

LOWER(str) LCASE(str)

Pasa la cadena a minúsculas

REVERSE(str)

Invierte la cadena

RIGHT(str,len)

Devuelve len caracteres de la cadena empezando por la derecha

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

Para obtener subcadenas de una cadena desde una posición y con la longitud que queramos.

UCASE(str) UPPER().

Pasa la cadena a mayúsculas

select concat(first_name,' ',last_name) cliente,
concat_ws(' - ',first_name, last_name,email) nombre ,
length(first_name) longitud,
left(first_name,2) izq,
right(first_name,2) der,
locate('@',email) pos,
substr(last_name,4,2) subcadena,
substr(email,1,locate('@',email)-1) lmail,
substr(email,locate('@',email)+1) rmail,
reverse(first_name) vuelta,
lower(first_name)  minusculas,
upper(email) mayusculas
from customer

SELECT * FROM sakila.film where length(title)>20; 

select length(first_name), concat(first_name,' ',last_name),
 left(first_name,3), right(last_name,3) nombre, lcase(first_name),
 ucase(last_name) , concat(ucase(left(first_name,1)),lcase(substr(first_name,2))), 
reverse(first_name), replace(first_name,'P','##') 
from actor where first_name=reverse(first_name) 

update actor set first_name=concat(ucase(left(first_name,1)),lcase(substr(first_name,2))), 
last_name=concat(ucase(left(last_name,1)),lcase(substr(last_name,2))) where actor_id>=1

Funciones de fecha:

CURDATE()

Fecha actual

CURTIME([fsp])

Hora actual

NOW([fsp])

Fecha y hora actual.

DATE_FORMAT(date,format)

Formatea una fecha con el formato específicado.

DAYOFMONTH(date) DAYOFWEEK(date)DAYOFYEAR(date)

Devuelven el día del mes, de la semana y del año respectivamente

HOUR(time) MINUTE(time)SECOND(time)MONTH(date)YEAR(date)

Devuelven la hora, el minuto, el segundo, el mes y el año de una fecha

select curtime(),curdate(),now(),
week(curdate()),year(curdate()),month(curdate()),
day(curdate()),hour(curtime()),monthname(now()),
dayname(now()),dayname('1985-03-07'),
dayofyear(now()),last_day(now()),
date_format(now(),'%e-%m-%y %H:%i'),utc_date()

SELECT * FROM sakila.payment
where dayofweek(payment_date)=3;

SELECT * FROM sakila.payment
where date_format(payment_date,'%w')=2;

SELECT * FROM sakila.payment
where day(payment_date)=day(curdate()) ;

Intervalos en Mysql:

https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals

Funciones de conversión:

CAST(expr AS type)

Convierte una expresión al tipo que le digamos

select '8'>'100',
cast('8' as signed)>cast('100' as signed);

Funciones de encriptación:

select aes_decrypt(aes_encrypt('Hola que tal','123'),'123');

select md5('hola que tal'),
md5('tomateofñlkfñdfñlghfñgsipshdfisu'),
sha1('hola que tal'),
sha2('hola que tal', 256)

Funciones de información:

select database(), current_user(), 
last_insert_id(), row_count(), version();

insert into country(country) values('Utopía');
set @a=last_insert_id();
insert into city(city,country_id) values ('Nowhere',@a);
insert into city(city,country_id) values ('Somewhere',@a);

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id

Ejemplos sql agrupados

Estadísticas de países:


select country pais,count(amount),sum(amount) total, avg(amount) media,max(amount) máximo, min(amount) minimo, variance(amount),std(amount) from country join city using(country_id)
join address using(city_id)
join customer using(address_id)
join payment using(customer_id)
group by pais
order by media desc

Número de películas por actor:


SELECT first_name,last_name, count(film_id) total from actor left join film_actor using(actor_id)
left join film using(film_id)
group by first_name,last_name
order by first_name desc

Ciudades por país:

select country,count(city_id) total from country join city using(country_id)
group by country
order by total asc

Categorías con más de 60 películas:


select name,count(film_id) total from category join film_category using(category_id)
group by name
having total>=60

Actor que ha participado en más películas:

select first_name,last_name,count(film_id) total from actor join film_actor using(actor_id)
join film using(film_id)
group by actor_id
order by total desc
limit 1

Total de categorías por actor (diferentes):


select distinct first_name,last_name,count(distinct name) from actor join film_actor using(actor_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
group by actor_id

Películas alquiladas por clientes:

select first_name,last_name, count(film_id) total from film join inventory using(film_id)
join rental using(inventory_id)
join customer using(customer_id)
group by customer_id

Categoría más alquilada:

select name,count(customer_id) total from category join film_category using(category_id)
join film using(film_id)
join inventory using(film_id)
join rental using(inventory_id)
group by category_id
order by total desc
limit 1

 

Ejercicio agregados

Mostrar la categoría con más películas

Mostrar los cinco clientes que más han gastado

Mostrar los países que tengan menos de 10 clientes

Mostrar los actores que han trabajado en más de 20 películas

Mostrar los actores que han trabajado en 5 o más películas de acción


select name,count(film_id) total
from category join film_category using (category_id)
join film using (film_id)
group by category_id
order by total desc
limit 1;

select first_name, last_name, sum(amount) total
from customer join payment using (customer_id)
group by customer_id
order by total desc limit 5;

select country,count(customer_id) total
from customer join address using (address_id)
join city using (city_id)
join country using (country_id)
group by country_id
having total<10;

select first_name,last_name, count(film_id) total
from actor join film_actor using (actor_id)
join film using (film_id)
group by actor_id
having total>20;

select first_name,last_name, count(film_id) total
from actor join film_actor using (actor_id)
join film using (film_id)
join film_category using (film_id)
join category using (category_id)
where name='action'
group by actor_id
having total>=5;

Ejemplos agregados

select first_name, last_name, sum(amount) total ,
 count(amount) numero , avg(amount) media,
 max(amount) maximo , min(amount) minimo
from customer join payment using (customer_id)
group by customer_id
order by numero desc
limit 5 
-- limit solo un número: devuelve las primeras n filas dos numeros: a partir del primer número n filas
-- limit 10: los 10 primeros. limit 10,10 a partir de la fila 10, 10 registros

select first_name, last_name, sum(amount) total ,
 count(amount) numero , avg(amount) media,
 max(amount) maximo , min(amount) minimo
from customer join payment using (customer_id)
where first_name like 'M%' -- Antes de calcular el total
group by customer_id
having total>150 -- Cuando ya se han calculado los totales

-- ¿Cuál es el país con menos ciudades?

select country, count(city_id) total 
from country join city using(country_id)
group by country_id
order by total asc
limit 1;

select country, count(customer_id) total from customer join address using(address_id)
join city using(city_id)
join country using(country_id)
group by country_id
order by total desc

-- Películas y categorías

select * from category join film_category using(category_id)
join film using (film_id);

-- Añado agrupación

select name,count(film_id) from category join film_category using(category_id)
join film using (film_id)
group by category_id;

-- Añado restricción sobre los campos
-- ¿Cuantas películas de cada categoría tienen rating 'G'

select name,count(film_id) total from category join film_category using(category_id)
join film using (film_id)
where rating='G'
group by category_id;

-- Añado restricción sobre el total
-- ¿Cuantas categorías tienen más de 10 películas con rating 'G'?

select name,count(film_id) total from category join film_category using(category_id)
join film using (film_id)
where rating='G'
group by category_id
having total>10

Agregados en Mysql

-- Cuantos hay de algo, cuanto suma algo...
-- ¿Cuantas películas hay por categoría?
-- funciones de agregado: count, sum, min, max, avg...
-- La opción de agrupar por algo GROUP BY

-- Cuantas películas hay por categoría
select name, count(film_id) from category join film_category using(category_id)
join film using (film_id)
group by name;

-- Cuantos actores tienen el mismo nombre
select first_name, count(last_name) total from actor
group by first_name;

-- ¿Cuantos actores trabajan en cada película?

select title, count(actor_id) num_actores from actor join film_actor using(actor_id)
join film using(film_id)
group by title ;

-- ¿Cual es la película con más actores?

select title, count(actor_id) num_actores from actor join film_actor using(actor_id)
join film using(film_id)
group by title
order by num_actores desc 
limit 1;

-- Total de pagos por cliente

select first_name, last_name, sum(amount) total 
from customer join payment using (customer_id)
group by customer_id

Categorías y países

select name,country from category join film_category using (category_id)
join film using (film_id)
join inventory using (film_id)
join rental using (inventory_id)
join customer using (customer_id)
join address using (address_id)
join city using (city_id)
join country using (country_id)
where name='Action' and country='Argentina'

left y right join

-- Relacionar los registros que aparecen en las dos tablas
-- Si mi registro no tiene registros relacionados en la otra tabla
-- NO APARECE

select country,city
from country inner join city using(country_id)
where city like 'T%';

-- ¿Cómo puedo hacer que aparezcan?
-- Cambiar el tipo de join: left, right

select country,city
from country left join city using(country_id)
where country like 'A%';


select country,city
from country right join city using(country_id)
where city like 'T%';

select country,city
from country left join city using(country_id)
where city is null;


select country,city
from country right join city using(country_id)
where country is null