Ejemplos funciones MySql

Todas las funciones:

https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html

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

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

Ejercicios subconsultas

Clientes que no han alquilado películas del actor con actor_id=1

Actores que no han trabajado en películas que duren más de 180 minutos

Actores que han trabajado en películas que no duran más de 180 minutos

Categorías que no tienen películas que duren más de 180 minutos

Clientes que no han pagado más de 10 dólares por alquilar una película.

Ejercicios subconsultas

Alquileres por encima de la media

select title, amount from film join inventory using(film_id)
join rental using (inventory_id)
join payment using (rental_id)
where amount> (select avg(amount) from payment)

Clientes con más películas alquiladas que alguno de los que empiezan por ‘A’

select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by first_name
having total> any (select count(rental_id) total from
customer join rental using (customer_id)
where first_name like 'a%'
group by first_name
)

Clientes con más películas alquiladas que todos los que empiezan por ‘A’

select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by first_name
having total> all (select count(rental_id) total from
customer join rental using (customer_id)
where first_name like 'a%'
group by first_name
)

Actores con más películas que el actor de id 1

select first_name, count(film_id) total 
from actor join film_actor using(actor_id)
group by actor_id
having total>(select count(film_id) from film_actor where actor_id=1)

Actores que trabajan en películas con rating ‘R’

select distinct first_name, last_name from actor join film_actor using(actor_id)
where film_id in
(SELECT film_id FROM sakila.film
where rating='R')

o

select distinct first_name, last_name from actor join film_actor using(actor_id)
join film using (film_id) where rating='R'

Actores que no han trabajado en películas con rating ‘R’

select distinct first_name, last_name from actor 
where actor_id not in
(SELECT actor_id FROM film_actor join sakila.film using(film_id)
where rating='R')

Actores que no hayan trabajado en películas de acción

select * from actor where actor_id not in (
select actor_id from film_actor join film using(film_id)
join film_category using (film_id)
join category using(category_id)
where name='ACTION'
)

Actores que no hayan trabajado en películas de rating ‘R’ con exists

select distinct first_name, last_name from actor a
where not exists 
(SELECT film_id FROM sakila.film join film_actor fa using(film_id)
where rating='R' and a.actor_id=fa.actor_id)

Eliminar actores que no hayan trabajado en películas de rating ‘R’

delete from actor 
where not exists 
(SELECT film_id FROM sakila.film join film_actor fa using(film_id)
where rating='R' and actor.actor_id=fa.actor_id);

delete from actor
where actor_id in 
(select actor_id 
from film_actor join film using(film_id) 
where rating='R');

Clientes que no han alquilado  películas de rating ‘R’

select * from customer where customer_id not in (
select customer_id from customer join rental using(customer_id)
join inventory using(inventory_id)
join film using(film_id)
where rating='R');

select * from customer where not exists(
select 1 from rental 
join inventory using(inventory_id)
join film using(film_id)
where rating='R' and customer.customer_id=rental.customer_id)

Media de películas alquiladas por clientes:

select avg(total) from
(select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by customer_id) ttt

Media de películas por categoría

select avg(total) media from
(select name, count(film_id) total
from category join film_category using (category_id)
group by category_id) tabla

Ejercicios left/right

Número de veces que se ha alquilado una película, incluyendo las que no se han alquilado nunca

Número de actores por película, incluyendo las películas que no tengan actores

Una consulta que me muestre juntas los nombres de las ciudades y los nombres de los distritos en las direcciones.


select title, count(rental_id) total
from film left join inventory using (film_id)
left join rental using (inventory_id)
group by film_id

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

select city territorio from city
union
select district territorio from address
order by territorio

Ejemplos left/right join


SELECT country,count(city_id) total FROM sakila.country left join city using(country_id)
group by country_id
having total=0

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

select * from film left join film_actor using(film_id)
where actor_id is null;

select * from film_actor right join film using(film_id)
where actor_id is null;

select * from actor left join film_actor using(actor_id)
where film_id is null

SELECT * FROM customer left join rental using(customer_id)
where rental_id is null

Ejercicios agrupados

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 where + join

Buscar los países que tengan entre 10 y 20 ciudades que tengan una letra ‘a’


select country, count(city_id) total
from country join city using(country_id)
where city like '%a%' -- Primero filtro las ciudades con una 'a' y después las cuento
group by country_id
having total between 10 and 20 -- este filtro se aplica después de contar
order by total desc

Ejemplos group_concat


SELECT first_name,last_name,
group_concat(title order by title separator ' \\ ') films,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by actor.actor_id

SELECT title,
group_concat(first_name,' ', last_name
order by first_name,last_name separator ' \\ ') actors,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by film_id

SELECT title,
group_concat(concat(first_name,' ', last_name)
order by concat(first_name,' ', last_name) separator ' \\ ') actors,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by film_id

Ejercicios agrupados

Número de películas para cada actor

Número de clientes por tienda

Total de ventas (amount) por película

Total de ventas(amount) por categoría


SELECT first_name,last_name,count(film_id) total
FROM actor join film_actor using(actor_id)
group by actor_id

select store.store_id, count(customer_id) total
from customer join store using(store_id)
group by store_id

select title,sum(amount) total from film join inventory using (film_id)
join rental using (inventory_id)
join payment using (rental_id)
group by film_id

select category.name, sum(amount) total
from category join film_category using (category_id)
join film using (film_id)
join inventory using (film_id)
join rental using (inventory_id)
join payment using (rental_id)
group by category_id -- comentario

Ejercicios Sakila

Películas de la categoría ‘Action’

Actores que han trabajado en películas de la categoría ‘Action’

Clientes de Argentina

Clientes que hayan alquilado películas de la categoría ‘Action’


select title from film join film_category using (film_id)
join category using (category_id)
where name='Action'

select distinct first_name,last_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)
where name='action'
order by first_name, last_name

select country,first_name,last_name from country join city
on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
where country='argentina'
order by first_name, last_name

select distinct first_name, last_name
from customer join rental using (customer_id)
join inventory using (inventory_id)
join film using (film_id)
join film_category using (film_id)
join category using (category_id)
where name='Action'

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