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

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:

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:

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:

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