Mes: marzo 2021

Ejemplos de funciones en Mysql


DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
FUNCTION `doble`(numero int) RETURNS int(11)
BEGIN

RETURN numero*2;
END$$

DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `tipo_pelicula`
(p_film_id int) RETURNS varchar(50) CHARSET latin1
BEGIN
declare v_length int;
select length into v_length from film 
where film_id=p_film_id;

if v_length<=140 then
return 'CORTA';
else
RETURN 'LARGA';
end if;

END$$

DELIMITER ;

DELIMITER $$
USE `sakila`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `factorial`(p_numero int) RETURNS int(11)
BEGIN
declare total,cont int default 1;

while cont<=p_numero do
 set total=total*cont;
 set cont=cont+1;
end while;
RETURN total;
END$$

DELIMITER ;

Uso:


select doble(8);

select title,length,tipo_pelicula(film_id) from film;

select factorial(5);

Procedimiento almacenado alta clientes

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_cliente2` (in p_country varchar(50),
in p_city varchar(50),
in p_address varchar(50),
in p_first_name varchar(50),
in p_last_name varchar(50),
out p_customer_id int)
BEGIN

declare v_country_id int;
declare v_city_id int;
declare v_address_id int;
declare v_customer_id int;

select country_id into v_country_id from country
where country=p_country;

IF v_country_id is null then
 insert into country(country) values (p_country);
 select last_insert_id() into v_country_id;
END IF;

select city_id into v_city_id from city
where city=p_city and country_id=v_country_id;

IF v_city_id is null then
 insert into city(city,country_id)
 values (p_city,v_country_id);
 select last_insert_id() into v_city_id;
END IF;

select address_id into v_address_id from address
where address=p_address and city_id=v_city_id;

IF v_address_id is null then
 insert into address(address,city_id)
 values (p_address,v_city_id);
 select last_insert_id() into v_address_id;
END IF;

select customer_id into v_customer_id from customer
where first_name=p_first_name and last_name=p_last_name and address_id=v_address_id;

IF v_customer_id is null then
 insert into customer(first_name,last_name,address_id,store_id)
 values (p_first_name,p_last_name, v_address_id,1);
 select last_insert_id() into v_customer_id;
END IF;

select v_customer_id into p_customer_id;

END$$
delimiter ;

Uso:

call alta_cliente('Spain','Logroño','Laurel, 4','Juan','Pi',@id);
select @id;

Mini ejercicio sub consultas

1.- Películas con más actores de la media

Pistas:

¿Cual es la media de actores por película? Por un lado necesitamos obtener el total de actores por película, que no lo sabemos. Una consulta entre film, film_actor y actor con un count de actores y un group by de películas.

Con ese total podemos averiguar la media de dos maneras:
a) Creando una vista: Si creo una vista total_actores_pelicula solo tengo que lanzar un avg sobre el total de los actores.
b) Creando una subconsulta con tabla temporal. Si no quiero crear una vista utilizo la consulta anterior como tabla temporal y calculo el avg sobre el total.

Una vez tenemos la media volvemos a la consulta original del total de actores por película. SI la tenemos en una vista no nos hace falta repetirla, en caso contrario la volvemos a hacer. Y ponemos como condición que el total de actores sea mayor que la media.

-- Total de actores por película

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

-- ¿Media?
-- Creando una vista
create view total_actores_por_pelicula as
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id;

select avg(total) from total_actores_por_pelicula;

-- Sin vista, con subtabla o tabla derivada

select avg(total) from (
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
) temporal;

-- Si tengo la consulta la puedo usar

select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
having total>(
select avg(total) from total_actores_por_pelicula
);

-- Si no repito la subconsulta

select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
having total>(
select avg(total) from (
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
) temporal
);

2.- Clientes con películas alquiladas por encima de la media

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

select first_name,last_name, count(rental_id) total
from customer join rental using(customer_id)
group by customer_id
having total>(select avg(total) from (
select first_name,last_name, count(rental_id) total
from customer join rental using(customer_id)
group by customer_id) temporal)

3.- Clientes que no hayan alquilado películas de acción

-- Los que sí han alquilado acción
select first_name, last_name, 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';

-- Los que no estén en la lista anterior

select first_name, last_name 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)
join film_category using(film_id)
join category using(category_id)
where name='Action'
)

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

Ejemplo subconsultas de tipo NO

-- ¿Qué actores no han trabajo en películas de rating 'R'?

-- Los actores que SI han trabajado en películas de rating R
-- Mostrar los que no son esos

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

-- Pues ahora los actores que no están en la anterior consulta

select * from actor
where actor_id not in (
	select distinct actor_id from actor join film_actor using(actor_id)
	join film using(film_id)
	where rating ='R'
)

-- ¿Qué actores no han trabajo en la categoría 'Música'?

-- Los actores que SI han trabajado en películas de categoría Música
-- Mostrar los que no son esos

select actor_id 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='Music';

-- Pues ahora los actores que no están en la anterior consulta

select * from actor
where actor_id not in (
select actor_id 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='Music'
)

Mini ejercicio subconsultas

Películas de la categoría con menos películas

-- Categoría con menos películas

select category_id from category join film_category using(category_id)
group by category_id
order by count(film_id)
limit 1;

-- Películas con categoría

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

Ejemplos subconsultas


-- Quiero saber las ciudades del país con más clientes

-- ¿Cual es el país con más clientes?

select country from country join city using(country_id)
		join address using(city_id) join customer using(address_id)
		group by country_id
		order by count(customer_id) desc
		limit 1;
        
-- Entonces buscamos las ciudades cuyo país sea el resultado de la consulta anterior
        
select country,city,count(customer_id) total from country join city using(country_id)
	join address using(city_id) join customer using(address_id)
    where country=(
		select country from country join city using(country_id)
		join address using(city_id) join customer using(address_id)
		group by country_id
		order by count(customer_id) desc
		limit 1
)
	group by country_id, city_id;

-- Que películas están por encima de la media en total de alquileres

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

select avg(total) from total_peliculas;

select * from total_peliculas where total>(
select avg(total) from total_peliculas
);

Tutorial trigger

http://www.techonthenet.com/mysql/triggers/before_insert.php

http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

http://www.w3resource.com/mysql/mysql-triggers.php

http://www.sitepoint.com/how-to-create-mysql-triggers/

Ejemplo de desencadenador:


CREATE TABLE `customer_log` (
 `idcustomer_log` int(11) NOT NULL AUTO_INCREMENT,
 `customer_id` int(11) DEFAULT NULL,
 `user` varchar(45) DEFAULT NULL,
 `fecha` datetime DEFAULT NULL,
 `nombre` varchar(45) DEFAULT NULL,
 `nombre_ant` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idcustomer_log`)
);

DELIMITER //
CREATE TRIGGER cliente_actualizado BEFORE UPDATE ON customer
FOR EACH ROW BEGIN

 DECLARE vUser varchar(50);

 -- Find username of person performing the INSERT into table
 SELECT USER() INTO vUser;

 -- Insert record into audit table
 INSERT INTO customer_log
 ( customer_id,
 fecha,
 user,nombre,nombre_ant)
 VALUES
 ( NEW.customer_id,
 SYSDATE(),
 vUser,NEW.first_name, OLD.first_name );

END;//

DELIMITER ;

CREATE TRIGGER `sakila`.`actor_BEFORE_DELETE` BEFORE DELETE ON `actor` FOR EACH ROW
BEGIN

if OLD.actor_id between 1 and 20 then
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No puedes borrar';
else
 insert into actor_log(first_name, last_name) values (OLD.first_name, OLD.last_name);
end if;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_INSERT` BEFORE INSERT ON `payment` FOR EACH ROW
BEGIN
if NEW.amount>=10 then
 set NEW.amount=9.99;
end if;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_UPDATE` BEFORE UPDATE ON `payment` FOR EACH ROW
BEGIN

if NEW.amount > OLD.amount then
 set NEW.amount=OLD.amount;
end if;

END