Hace unos dias en el trabajo ayude a frogx3 a hacer unos reportes que incluian varias operaciones que si bien las podia hacer con un poco mas de programación en PHP recorde que en el MySQL Query Browser viene una lista de funciones separadas por tipos que nos pueden ser de mucha utilidad y que muchas veces ni siquiera sabemos que existen, bien pues me dispuse a revisar las funciones que me presentaba dicha lista y me encontre con que mySQL nos puede ahorrar lineas de código si sabemos utilizarlo.
Algunas sentencias que me encontre son las siguientes:
Funciones para el control de flujo
Sentencia IF
Algo que nos puede ser util cuando trabajamos con banderas, se me ocurre por ejemplo cuando tenemos un campo llamado publicado donde 0=No publicado y 1= Publicado a nosotros nos interesara mostrar en pantalla un texto y no el valor (0,1) de la bandera bien podriamos hacer una consulta como la siguiente:
- SELECT IF(t.publicado=1,‘Si’,‘No’) AS publicado FROM tabla t;
CASE
Creo que todos los que hemos programado sabemos para que es un case por lo que obviare la explicación.
Sintaxis:
CASE [valor] WHEN [condicion] THEN resultado [WHEN [condicion] THEN resultado…] [ELSE resultado] END
Ejemplo de Uso:
- SELECT CASE WHEN 1>0 THEN ‘mayor’ ELSE ‘menor’ END;
Nos imprimiría en pantalla “mayor”
Otro ejemplo:
- SELECT CASE 1 WHEN 1 THEN ‘uno’ WHEN 2 THEN ‘dos’ ELSE ‘otro’ END;
Nos imprimiría en pantalla “uno”
Operaciones Con Cadenas
CHAR()
Interpreta cada argumento N como un entero y devuelve una cadena que consiste en lo valores en ascii de dichos enteros. Los valores nulos no son tomados en cuenta.
Sintaxis: CHAR(N,…)
Ejemplo:
- SELECT CHAR(77,121,83,81,’76′);
Imprime en pantalla “MySQL”
CONCAT
Te devuelve la cadena resultante de concatenar los argumentos pasados en la función. Puede tener mas de un argumento.
Sintaxis: CONCAT(cadena1,cadena2,…)
Ejemplo de uso:
- SELECT CONCAT(‘My’, ‘S’, ‘QL’);
Imprime: MySQL
- SELECT u.usuario, CONCAT(u.ciudad,‘, ‘,u.estado,‘, ‘,u.pais) AS localizacion FROM usuarios u;
Mostrará en pantalla un listado de los usuarios junto con un campo localización que es el resultado de concatenar ciudad, estado y pais.
REPLACE
Sintaxis: REPLACE(cadena,cadena_buscada,valor_para_reemplazar)
Devuelve una cadena con todas las ocurrencias de ‘cadena_buscada’ reemplazadas por ‘valor_para_reemplazar’
Ejemplo:
- SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
Imprime: ‘WwWwWw.mysql.com’
TRIM
Una función que seguramente la mayoria de nosotros hemos utilizado en php o su equivalencia en algun otro lenguaje, MySQL tambien nos la proporciona y yo para ser sincero ni lo sabia.
Sintaxis: TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
Devuelve la cadena str con todos los prefijos o sufijos remstr eliminados. Si ninguno de los parametros BOTH, LEADING, or TRAILING es proporcionado,
se asume el parámetro BOTH. remstr es opcional y, si ninguno es especificado, todos los espacios en blanco son removidos.
Ejemplo:
- SELECT TRIM(‘ cadena ‘);
Imprime: cadena
- SELECT TRIM(LEADING ‘a’ FROM ‘aaavaloraaa’);
Imprime: valoraaa
- SELECT TRIM(BOTH ‘a’ FROM ‘aaavaloraaa’);
Imprime: valor
- SELECT TRIM(TRAILING ‘xyz’ FROM ‘cadenaxxyz’);
Imprime: cadena
REPEAT
Sintaxis: REPEAT(str,n)
Devuelve una cadena que consiste en la cadena str repetida n veces. Si n es menor que 1, devuelve una cadena vacia. Devuelve NULL si str o n son NULL
Ejemplo:
- SELECT REPEAT(‘MySQL’, 3);
Imprime MySQLMySQLMySQL
Operaciones con Fechas
Muchas veces tenemos una fecha y deseamos calcular una fecha sumando o restandole cierto número de dias para esto tenemos una sentencia de MySQL que nos permite hacer eso.
DATE_ADD y DATE_SUB
Sintaxis: DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
Estas funciones ejecutan funciones aritmeticas sobre la fecha. date es un valor DATETIME o DATE especificando la fecha de inicio. expr es una expresion especificando el valor del intervalo a ser sumado o restado de la fecha de inicio. expr es una cadena; puede comenzar con un signo ‘-’ para los intervalos negativos. unit es una palabra clave indicando la unidad en la cual la expresion debe ser interpretada.
Los siguientes son los valores que puede tener el parametro expr unit.
| unit Valor | Formato para expr |
MICROSECOND | MICROSECONDS |
SECOND | SECONDS |
MINUTE | MINUTES |
HOUR | HOURS |
DAY | DAYS |
WEEK | WEEKS |
MONTH | MONTHS |
QUARTER | QUARTERS |
YEAR | YEARS |
SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND | 'MINUTES.MICROSECONDS' |
MINUTE_SECOND | 'MINUTES:SECONDS' |
HOUR_MICROSECOND | 'HOURS.MICROSECONDS' |
HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE | 'HOURS:MINUTES' |
DAY_MICROSECOND | 'DAYS.MICROSECONDS' |
DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE | 'DAYS HOURS:MINUTES' |
DAY_HOUR | 'DAYS HOURS' |
YEAR_MONTH | 'YEARS-MONTHS' |
Ejemplos:
- SELECT ’1997-12-31 23:59:59′ + INTERVAL 1 SECOND;
- #IMPRIME ’1998-01-01 00:00:00′
- SELECT INTERVAL 1 DAY + ’1997-12-31′;
- #IMPRIME ’1998-01-01′
- SELECT ’1998-01-01′ - INTERVAL 1 SECOND;
- #IMPRIME ’1997-12-31 23:59:59′
- SELECT DATE_ADD(’1997-12-31 23:59:59′,INTERVAL 1 SECOND);
- #IMPRIME ’1998-01-01 00:00:00′
- SELECT DATE_ADD(’1997-12-31 23:59:59′,INTERVAL 1 DAY);
- #IMPRIME ’1998-01-01 23:59:59′
- SELECT DATE_ADD(’1997-12-31 23:59:59′,INTERVAL ’1:1′ MINUTE_SECOND);
- #IMPRIME ’1998-01-01 00:01:00′
- SELECT DATE_SUB(’1998-01-01 00:00:00′,INTERVAL ’1 1:1:1′ DAY_SECOND);
- #IMPRIME ’1997-12-30 22:58:59′
- SELECT DATE_ADD(’1998-01-01 00:00:00′,INTERVAL ‘-1 10′ DAY_HOUR);
- #IMPRIME ’1997-12-30 14:00:00′
- SELECT DATE_SUB(’1998-01-02′, INTERVAL 31 DAY);
- #IMPRIME ’1997-12-02′
- SELECT DATE_ADD(’1992-12-31 23:59:59.000002′,INTERVAL ’1.999999′ SECOND_MICROSECOND);
- #IMPRIME ’1993-01-01 00:00:01.000001′
Estas son algunas de las funciones que me parecieron que pudieran servirnos mas a menudo y que nos evitarian un poco de lineas de código en nuestra programación, espero les sirvan de algo.
Actualización:
Eduard Capell nos menciona en los comentarios de 2 sentencias igual muy útiles estas son:
INSERT IGNORE
Sintaxis: INSERT IGNORE INTO tabla (campo1,campo2) VALUES (valor1,valor)
Intenta insertar, pero ignora los posibles errores que se puedan producir, por ejemplo, como consecuencia de alguna PRIMARY KEY duplicada.
Ejemplo:
INSERT IGNORE into tabla(id) VALUES (3);
Si el valor 3 correspondia al campo de la clave primaria y dicho valor ya existía en la tabla, no se producirá un error (como es el caso del INSERT “normal”), sino que sencillamente se insertarán 0 filas y no devolverá ningún error.
INSERT … ON DUPLICATE KEY UPDATE
Si especificamos ON DUPLICATE KEY UPDATE en nuestra consulta, y una fila es insertada que puede causar duplicidad con alguna llave primaria o indice unico en nuestra tabla, se realiza una actualización al registro.
Ejemplo:
- INSERT INTO TABLE (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
- #Seria lo mismo que hacer una consulta update como la siguiente
- UPDATE TABLE SET c=c+1 WHERE a=1;
Agradezco a Eduard por su aporte e invito a los demas lectores a poner su granito de arena.
Saludos.
