Ejemplos create view

Primero creamos una vista base:

create view country_payment as

select country, payment.* from country
join city using (country_id)
join address using (city_id)
join customer using (address_id)
join payment using (customer_id)

Después nos basamos en esta para crear las siguientes:

create view country_total as

select country, sum(amount) total from country_payment
group by country


create view country_month as

select country, month(payment_date) month, count(payment_id) total from country_payment
group by country, month

Ejemplos funciones MySql

Funciones de control de flujo:

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 staff_id when 1 then 'Ana' when 2 then 'Juan' end as empleado
FROM sakila.payment;

Funciones matemáticas:

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

Funciones de conversión:

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

Ejemplos sentencias sql

Actores con más de 10 películas

select first_name, last_name, count(film_id) peliculas from
actor join film_actor using (actor_id)
group by first_name,last_name
having peliculas>30

Países con más de 10 ciudades

select country, count(city) cities
from country join city using (country_id)
group by country
having cities>10

Los diez países que más gastan

select country, sum(amount) total, avg(amount) media
from country join city using (country_id)
join address using (city_id)
join customer using (address_id)
join payment using (customer_id)
group by country
order by total desc
limit 0,10

El actor con más películas

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