Ejemplo trigger

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 ;

Procedimiento almacenado alta clientes

CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_cliente`(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

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

Procedimientos almacenados

Ejemplos de procedimientos almacenados

CREATE DEFINER=`root`@`localhost`
 PROCEDURE film_in_stock
 (IN p_film_id INT, IN p_store_id INT,
 OUT p_film_count INT)
 READS SQL DATA
BEGIN
 SELECT inventory_id
 FROM inventory
 WHERE film_id = p_film_id
 AND store_id = p_store_id
 AND inventory_in_stock(inventory_id);

 SELECT FOUND_ROWS() INTO p_film_count;
END

call film_in_stock(123,1,@w);
select @w

CREATE DEFINER=`root`@`localhost` PROCEDURE 
`actores_por_categoria`
(in p_categoria varchar(50))
BEGIN
select distinct concat(first_name,' ',last_name) actor 
from actor join film_actor using (actor_id)
join film using (film_id)
join film_category using (film_id)
join category c using (category_id)
where c.name=p_categoria
order by actor;

END

call actores_por_categoria('Animation')

Crear funciones

Ejemplos:

CREATE DEFINER=`root`@`localhost` 
FUNCTION `inventory_in_stock`(p_inventory_id INT)
 RETURNS tinyint(1)
 READS SQL DATA
BEGIN
 DECLARE v_rentals INT;
 DECLARE v_out INT;
 SELECT COUNT(*) INTO v_rentals
 FROM rental
 WHERE inventory_id = p_inventory_id;

 IF v_rentals = 0 THEN
 RETURN TRUE;
 END IF;

 SELECT COUNT(rental_id) INTO v_out
 FROM inventory LEFT JOIN rental USING(inventory_id)
 WHERE inventory.inventory_id = p_inventory_id
 AND rental.return_date IS NULL;

 IF v_out > 0 THEN
 RETURN FALSE;
 ELSE
 RETURN TRUE;
 END IF;
END

select inventory_in_stock(30)

CREATE DEFINER=`root`@`localhost`
 FUNCTION `clientes_por_pais`
 (p_country varchar(50)) RETURNS int(11)
BEGIN
declare total int;

SELECT count(customer_id) into total
 FROM sakila.country
left join city using (country_id)
left join address using (city_id)
left join customer using (address_id)
where country=p_country
group by country;

RETURN total;
END

select clientes_por_pais('Algeria')

CREATE DEFINER=`root`@`localhost` FUNCTION `peliculas_por_actor`(p_actor_id int) RETURNS int(11)
BEGIN

declare total int;

select count(film_id) into total from film join
film_actor using (film_id)
join actor using(actor_id)
where actor.actor_id=p_actor_id;

RETURN total;
END

select peliculas_por_actor(1)

CREATE DEFINER=`root`@`localhost` 
FUNCTION `total_ventas`() RETURNS decimal(10,2)
BEGIN
declare total decimal(10,2);
select sum(amount) into total from payment;
RETURN total;
END


Sql views

create or replace view cliente_gaston as
select customer.customer_id first_name, last_name, sum(amount) as total
from customer join payment using(customer_id)
group by first_name, last_name
order by total desc
limit 0,5
update customer set active=5
where customer_id in (select customer_id from cliente_gaston)

Más SQL

Países con tiendas:

select country, count(store_id) as total
from country left join city using (country_id)
left join address using (city_id)
left join store using (address_id)
group by country
having total>0

Cliente que más gasta:

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

Ejemplos de CASE e IF:

SELECT *,
case when amount<1 then 'barato'
when amount between 1 and 3 then 'medio'
else 'caro' end precio,
if (amount<3,'barato','caro') precio2
FROM sakila.payment;

Con funciones de agregado:

select first_name, last_name, sum(amount) as total,
if (sum(amount)>100,'gastador','rácano') tipo
from customer join payment using(customer_id)
group by customer_id

Pagos formateados:

SELECT lpad(format(amount,3),10,' ') from payment

Nombre formateado y ordenar por longitud de apellido:

select ucase(first_name), lcase(last_name),
concat(
ucase(substring(first_name,1,1)),
lcase(substring(first_name,2))
) cliente,
length(last_name) longitud
from customer
order by longitud desc