Cómo escribir consultas MySQL utilizando funciones

Funciones
Las funciones en MySQL se reconocen por los paréntesis al final. Entre las consideraciones al trabajar con funciones se pueden nombrar:

1. Los resultados de las funciones se devuelven en tablas, el primer renglón corresponde a los encabezados de las columnas. 
2. Es importante no dejar espacio entre el nombre de la función y los paréntesis, de otro forma MySQL genera un mensaje de error.
3. Cuando se utilizan cadenas de caracteres la primera posición es la uno (1).

A continuación se explican las funciones de cadena de caracteres, numéricas, Fecha y hora más utilizadas:

FUNCIONES DE CADENAS DE CARACTERES

  CONCAT(str1,str2,...)

  Retorna una cadena como resultado de concatenar los str1,str2,strn. Retorna   NULL si algún str es NULL. Puede tener uno o más argumentos. 

 mysql> SELECT CONCAT('Mi',' ', 'libro');

        -> 'Mi libro'

 mysql> SELECT CONCAT('Mi', NULL, 'libro','nuevo');

        -> NULL

  LOWER(str) o LCASE(str)
  Retorna la cadena str con todos los caracteres cambiados a minúsculas.
 mysql> SELECT LOWER('MYSQL');

        -> 'mysql'

  UPPER(str) o UCASE(str)
  Retorna la cadena str con todos los caracteres cambiados a mayúsculas.
 mysql> SELECT UPPER('mysql');

        -> 'MYSQL'

  LEFT(str,len)
  Retorna los len caracteres comenzando por la izquierda de la cadena str.
 mysql> SELECT LEFT('foobarbar', 5);

        -> 'fooba'

  RIGHT(str,len)
  Retorna los len caracteres comenzando por la derecha de la cadena str.
 mysql> SELECT RIGHT('foobarbar', 4);

        -> 'rbar'

  SUBSTRING(str,pos,len) o  SUBSTR(str,pos,len) o MID(str,pos,len)

  Retorna la cadena strLas formas sin el argumento len retornan una subcadena de la cadena str comenzando en la posición pos. Las formas con el argumento len retornan una subcadena de longitud len a partir de la cadena str, comenzando en la posición pos.

  mysql> SELECT SUBSTRING('lacasaazul',6);

        -> 'aazul'

  mysql> SELECT SUBSTRING('lacasaazul',6,4);

        -> 'aazu'       

  LENGTH(str)
  Retorna la cantidad de caracteres de la cadena str.
  mysql> SELECT LENGTH('Hola');
       -> 4
  mysql> SELECT LENGHT('Esto es otro ejemplo ');
       -> 21

  INSTR(str,substr)
  Retorna el valor con la posición de la primera ocurrencia de substr dentro de str. Retorna 0 si no se encuentra ninguna ocurrencia.
 mysql> SELECT INSTR('Real Madrid, 'Madrid');
       -> 6
 mysql> SELECT INSTR('Barcelona', 'lina');
       -> 0

       

   LTRIM(str)
   Retorna la cadena str con los caracteres en blanco iniciales eliminados.
  mysql> SELECT LTRIM('  barbar');
      -> 'barbar'

   RTRIM(str)

Retorna la cadena str con los espacios precedentes eliminados.

mysql> SELECT RTRIM('barbar   ');

        -> 'barbar'

TRIM(str)

Retorna la cadena la cadena str pero sin los espacios en blanco que tenga al principio y al final.

mysql> SELECT TRIM('  bar   ');

        -> 'bar'

   ASCII(str)

Retorna el valor numérico del carácter más a la izquierda de la cadena de caracteres str. Retorna 0 si str es una cadena vacía. Retorna NULL si str es NULL. ASCII() funciona para caracteres con valores numéricos de 0 a 255.

mysql> SELECT ASCII('A');

        -> 65

mysql> SELECT ASCII('*');

        -> 42

mysql> SELECT ASCII('A*');

        -> 42

   CHAR(X)

Retorna el caracter ASCII correspondiente al valor númerico X. Los valores NULL no se tienen en cuenta.

mysql> SELECT CHAR(77,121,83,81,76);

        -> 'MySQL'

mysql> SELECT CHAR(65,42);

        -> 'A*'

FUNCIONES MATEMÁTICAS
   MySQL puede realizar operaciones aritméticas tales como: suma(+), resta(-), multiplicación(+) y división(/).
   mysql> SELECT 5 * 6/2 + 4:
             -> 19

Además MySQL contiene una serie de funciones para se trabajar con números. Entre las más utilizadas tenemos: 

SQRT(X)

Retorna el valor de la raíz cuadrada de X. Si  X es negativo retornará NULL.

mysql> SELECT SQRT(9);

        -> 3

mysql> SELECT SQRT(-9);

        -> NULL       

   POW(X,Y) , POWER(X,Y)

Retorna el valor de X a la potencia de Y.

mysql> SELECT POW(2,3);

        -> 8

   MOD(X,Y) , X % Y,  X MOD Y

Retorna el valor del resto obtenido de dividir X entre Y.

mysql> SELECT MOD(16, 5);

        -> 1

mysql> SELECT 16 MOD 5;

        -> 1

mysql> SELECT 16 % 5;

        -> 1

   RAND()

   Retorna un valor aleatorio entre 0 a 1.0.

mysql> SELECT RAND();

        -> 0.49780351289541

   ROUND(X), ROUND(X,D)

Retorna el valor de X, redondeado al entero más cercano.  Si se incluye el argumento D retornará el valor de X redondeado a D decimales.

mysql> SELECT ROUND(122.14);

        -> -122

mysql> SELECT ROUND(122.89);

        -> 123

mysql> SELECT ROUND(122.16, 1);

        -> 122.2

   TRUNCATE(X,D)

Retorna el valor de X, truncado a D decimales. Si D es 0, el resultado solo retornará la parte entera.

mysql> SELECT TRUNCATE(1.273,0);

        -> 1

mysql> SELECT TRUNCATE(1.273,1);

        -> 1.2

mysql> SELECT TRUNCATE(1.273,2);

        -> 1.27

   FUNCIONES DE FECHA Y HORA

Las funciones que esperan valores de fecha aceptan valores de fecha y hora e ignoran la parte de hora y las funciones que esperan valores de hora aceptan valores de hora e ignoran la parte de la fecha.

NOW(), LOCALTIME(), SYSDATE()

Retorna la fecha y hora actual como valor en formato 'YYYY-MM-DD HH:MM:SS'.

mysql> SELECT NOW();

        -> '2012-09-27 05:12:21'

   CURDATE(), CURRENT_DATE()

Retorna la fecha actual con el formato 'YYYY-MM-DD'.

mysql> SELECT CURDATE();

        -> '2012-09-27'

   CURTIME(), CURRENT_TIME()

Retorna la hora actual con el formato 'HH:MM:SS' .

mysql> SELECT CURTIME();

        -> '23:50:26'

   
   DATE(fechor)

Retorna la parte de la fecha de la expresión fecha y hora fechor.

mysql> SELECT DATE('2003-12-31 01:02:03');

        -> '2003-12-31'

   TIME(fechor)

Extrae la parte de hora de la expresión hora o fecha/hora fechor.

mysql> SELECT TIME('2003-12-31 01:02:03');

        -> '01:02:03'

mysql> SELECT TIME('2003-12-31 01:02:03.000123');

        -> '01:02:03.000123'

   DATEDIFF(fechor1,fechor2)

   Retorna el número de días entre fechor1  y fechor2.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');

        -> 1

mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');

        -> -31

   TIMEDIFF(fechor1,fechor2)

Retorna el tiempo entre la hora entre fechor1 y fechor2.

mysql> SELECT TIMEDIFF('2012:09:27 00:00:00','2012:09:28 00:00:00');

        -> '-24:00:00'

mysql> SELECT TIMEDIFF('15:30.01', '18:01:20');

        -> '-02:31:190000'

   DAYNAME(fecha)

Retorna el nombre del día de la semana de la fecha.

mysql> SELECT DAYNAME('2012-09-27');

        -> 'Thursday'

MONTHNAME(fecha)

Retorna el nombre del mes para fecha.

mysql> SELECT MONTHNAME('2012-09-27');

        -> 'September'

    DAY(fecha)

    Retorna el número del dia mes para fecha.

     mysql> SELECT DAY('2012-09-27');

        -> 27

MONTH(fecha)

Retorna el número del mes para fecha.

mysql> SELECT MONTH('2012-09-27');

        -> 9

   YEAR(fecha)

Retorna el número del año para fecha.

mysql> SELECT YEAR('12-02-03');

        -> 2012

   LAST_DAY(fecha)

Retorna la fecha correspondiente al último día del mes de fecha.

mysql> SELECT LAST_DAY('2012-09-27');

        -> '2012-09-30'

mysql> SELECT LAST_DAY('2012-02-09');

        -> '2012-02-29'

  OTRAS FUNCIONES

   MD5(str)
   Retorna una cadena encriptada de str  de 32 dígitos hexadecimales. Utilizado
   para almacenar contraseñas.

 mysql> SELECT MD5('testing');

     -> 'ae2b1fca515949e5d54fb22b8ed95575'

   SHA(str)
   Retorna una cadena encriptada de str  de 40 dígitos hexadecimales. Utilizado para almacenar contraseñas. Se considera más segura que MD5().

  mysql> SELECT SHA1('abc');

      -> 'a9993e364706816aba3e25717850c26c9cd0d89d'

   USER(), CURRENT_USER()

       Retorna el nombre de usuario y de equipo que tiene la sesión actual del MySQL. Este valor está contenido en la tabla MySQL que determina los privilegios de acceso. Puede ser distinto al valor de CURRENT_USER().

  mysql> SELECT USER();
      -> 'david@localhost'
  mysql> SELECT CURRENT_USER();

  -> '@localhost'

   DATABASE()
   Retorna el nombre de base de datos activa. Si no hay alguna base de datos activa se retorna NULL. 

mysql> SELECT DATABASE();

        -> 'ejemplo'

   VERSION()

Retorna una cadena que indica la versión del servidor MySQL.

mysql> SELECT VERSION();

        -> '5.0.9-standard'

EJERCICIOS

1. Unir el nombre y el apellido, separados por una coma.

@nombre:="Maria"

@apellido:="Perez";

2. Crear una sentencia que permita separar el nombre y el apellido.

SELECT @nom1:=”Ramon, Marin”;
SELECT @nom2:="Norberto, Valdiviezo";

3. Convertir 3 campos separados en un valor tipo fecha:

SELECT @dia:=1; @mes:=5; @ano:=2012;

SELECT @fecha:="25-04-2014";

RESPUESTAS:

Solución 2:
SELECT LEFT(@nom,INSTR(@nom,”,”)-1);

SELECT RIGHT(@nom,INST(@nom,”,”)-1);

Solución 4:

SELECT @fecha:=CONCAT(SUBSTR(@fecha,7,4) ),”-”,

LPAD(SUBSTR(@fecha,4,2),2,”0”),”-”,

LPAD(SUBSTR(@fecha,4,2),2,”0”);