Ejemplos funciones

SELECT concat(SUBSTRING(first_name,1,2),SUBSTRING(last_name,1,2)) ref from actor;
SELECT LENGTH('MySQL') AS longitud;
SELECT UPPER('mysql') AS mayusculas, LOWER('MySQL') AS minusculas;
select concat(upper(substring(first_name,1,1)),
lower(substring(first_name,2))) nombre from actor;
SELECT REPLACE('Aprender SQL', 'SQL', 'MySQL') AS resultado;

update film set description= replace(description,'Crocodile','Cocodrilo') ;

SELECT TRIM('   Hola    que   ') AS resultado;

SELECT CONCAT_WS('-', '2023', '10', '26') AS fecha;

select * from actor
where length(first_name)>8;

select count(*) from actor;

select concat('hola',5), '5'+3 foo;

select cast('2021-1-1' as date) fecha;
SELECT STR_TO_DATE('1/1/2023', '%d/%m/%Y');

-- SImilar al if... elseif... else
select first_name,
 CASE
        WHEN length(first_name)<5 THEN 'Nombre corto'
        WHEN length(first_name)<8 THEN 'Nombre medio'
        ELSE 'Nombre largo'
    
END tipo
    from actor;

-- Similar al switch
select rating,
case rating
when 'r' then 'Para adultos'
else  'Todos los publicos'
end tipo
from film;

-- Similar al operador ternario
select if(length(first_name)<5,'corto','largo') longitud from actor;

select coalesce(first_name,'') from actor;

select concat(coalesce(first_name,'.'),coalesce(last_name,''))nombre from actor;

select now(),CURDATE() AS fecha_actual, CURTIME() AS hora_actual;

SELECT EXTRACT(YEAR FROM NOW()) AS año_actual, EXTRACT(MONTH FROM NOW()) AS mes_actual;

-- Imprescindible
select month(now()),year(now()),day(now());

select month(payment_date) from payment;

select * from payment
where month(payment_date) =5;

select year(payment_date) anyo, month(payment_date) mes,sum(amount) total from payment
group by anyo,mes;

select month('1-1-2023');


SELECT month(STR_TO_DATE('1/1/2023', '%d/%m/%Y'));

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i %w') AS fecha_formateada;

-- Alquileres en fin de semana

select count(rental_id) from rental
where date_format(rental_date,'%w') in (0,6);

SELECT ADDDATE(NOW(), 7) AS fecha_en_7_dias, SUBDATE(NOW(), 3) p;

SELECT DATEDIFF(NOW(), '2023-01-01') AS dias_desde_inicio_de_ano;

SELECT ROUND(3.14159) AS redondeo_entero, ROUND(3.14159, 3) AS redondeo_dos_decimales;

select * from actor order by rand() limit 10;

SELECT SCHEMA() AS nombre_de_base_de_datos;

SELECT concat(address,coalesce(address2,'')) dir FROM sakila.address;

insert into user (name,tarjeta)
values ('tarjeta',AES_ENCRYPT('1112222333434', 'mi_clave_secreta') )

SELECT *, cast(AES_deCRYPT(tarjeta, 'mi_clave_secreta') as char) tarjeta FROM sakila.user