Agrupar los datos

A veces, puede ser necesario calcular un valor para un nivel de agrupamiento:

volumen de negocios por región.
salario mínimo por provincia.

Para este tipo de consulta, pueden utilizarse funciones de agregación (SUM, AVG, etc.) y agrupar los datos mediante la cláusula GROUP BY; como complemento, el resultado final, tras la agrupación, puede restringirse con la cláusula HAVING.

Sintaxis

SELECT expresión[,...] | * 
FROM nombre_tabla 
[WHERE condiciones] 
GROUP BY expresión [,...] 
[HAVING condiciones] 
[ORDER BY expresión [ASC | DESC][,...]] 
[LIMIT [offset,] número_filas]

La cláusula GROUP BY se intercala entre las cláusulas WHERE y ORDER BY (si están presentes). Especifica las expresiones utilizadas para efectuar el agrupamiento.

expresión puede ser:

una columna.
una expresión basada en columnas.
un alias de columna.
un número correspondiente a la posición de una expresión de la cláusula SELECT (sintaxis desaconsejada y obsoleta).

En versiones anteriores, el resultado de la consulta se ordenaba por defecto en orden creciente en las diferentes expresiones de la cláusula GROUP BY; la ordenación de cada nivel de agrupamiento se podía definir explícitamente con las opciones ASC y DESC. Desde la versión 5.7, se recomienda no basarse en el orden implícito de la cláusula GROUP BY porque había quedado obsoleto, y después de la versión 8.0.13, las opciones ASC y DESC de la cláusula GROUP BY, se han eliminado. Para estar seguro de que tiene un orden de clasificación muy preciso, se recomienda utilizar una cláusula ORDER BY explícita.

La mayor parte del tiempo, en la cláusula GROUP BY figurarán todas las expresiones de la cláusula SELECT que no tienen función de agregación. Es el funcionamiento habitual para respetar el estándar SQL.

Ejemplos

mysql> -- Número de libros por colección. 
mysql> SELECT id_coleccion,COUNT(*) 
    -> FROM libro 
    -> GROUP BY id_coleccion; 
+--------------+----------+ 
| id_coleccion | COUNT(*) | 
+--------------+----------+ 
|            1 |        7 | 
|         ...

Utilizar subconsultas

1. Introducción

Una subconsulta es una consulta SELECT utilizada en el interior de otra consulta.

Una subconsulta puede utilizarse:

en la cláusula WHERE de una consulta SELECT, UPDATE o DELETE;
en la cláusula SELECT de una consulta SELECT;
en la cláusula FROM de una consulta FROM;
como valor asignado a una columna en una consulta INSERT o UPDATE;
como origen de datos de una consulta INSERT, en lugar de la cláusula VALUES.

La subconsulta siempre se escribe entre paréntesis.

Puede incluir, sin embargo, combinaciones, así como cláusulas WHERE, GROUP BY, ORDER BY, etc. Una subconsulta también puede anidar a su vez subconsultas.

Desde la versión 8, MySQL soporta las «expresiones de tabla habituales» (Common Table Expresión - CTE), es decir, la posibilidad de definir subconsultas con nombre en una cláusula WITH que preceda a la consulta principal y usarlas en esta última como si fueran tablas.

2. Subconsulta escalar

Una subconsulta que devuelve una sola columna y, como mucho, una sola fila, se denomina subconsulta escalar.

Dicha subconsulta puede utilizarse en cualquier lugar donde se espere un valor (un operando):

en la cláusula WHEREde una consulta SELECT, INSERT, UPDATE o DELETE;
en la cláusula SELECT de una consulta SELECT;
como valor asignado a una columna en una consulta INSERT o UPDATE;
como operando de una función o de una expresión.

Ejemplo:

mysql> -- Diferencia entre el precio de la colección 1 y el precio medio 
mysql> -- de las colecciones. 
mysql> SELECT 
    ->   ROUND(precio_siniva - (SELECT AVG(precio_siniva) FROM coleccion),2) 
espacio 
    -> FROM 
    ->   coleccion 
    -> WHERE 
    ->   id = 1; 
+---------+ 
| espacio | 
+---------+ 
| -5.51   | 
+---------+ 
1 row in set (0.00 sec) 
 
mysql> -- Precio con IVA catálogo del artículo de código "RI7PHP". 
mysql> SELECT precio_coniva FROM catalogo WHERE codigo = 'RI7PHP'; 
+---------------+ 
| precio_coniva | 
+---------------+ 
|         34.50 | 
+---------------+ 
1 row in set (0.01 sec) 
 
mysql>...

Usar las funciones de ventana

1. Introducción

Desde la versión 8, MySQL soporta las funciones de ventana (window functions), también llamadas algunas veces funciones analíticas.

Una función de ventana es una función cuyo resultado para una línea dada se deriva de un conjunto de líneas referido a esta línea (una «ventana»). Normalmente, estas funciones permiten resolver de manera sencilla los problemas complejos que hubieran hecho necesario el uso de varias consultas y operaciones en una aplicación cliente. Son particularmente útiles para realizar informes del tipo de toma de decisiones.

Las funciones analíticas permiten:

realizar clasificaciones;
efectuar cálculos sobre las ventanas deslizantes (suma acumulativa, media deslizante, etc.);
acceder a otras líneas diferentes de la línea actual (línea anterior o siguiente, primera o última línea de la ventana, etc.).

En esta parte, presentaremos los conceptos básicos de uso de las funciones de ventana, dando algunos ejemplos representativos de su interés. Para obtener más detalles, no dude en consultar la documentación (capítulo «12.21 Window Functions» del manual «MySQL 8.0 Reference Manual»).

La sintaxis general de una función analítica es la siguiente:

función([argumentos]) OVER([partición][clasificación][ventana])

La presencia de la cláusula OVER indica el uso de una función de ventana; para la llamada de esta última, los paréntesis son obligatorios incluso si no hay argumentos.

La cláusula OVER puede contener tres sub-cláusulas:

partición: permite formar grupos de registros dentro de los cuales realizar cálculos (parecido a una cláusula GROUP BY).
clasificación: permite definir una clasificación intermedia si la función de ventana lo necesita.
ventana: permite definir una ventana deslizante relativa al registro actual.

Las tres sub-cláusulas son opcionales; la función usará entre cero y tres, según el contexto y la necesidad. En una misma consulta, se pueden utilizar diferentes funciones de ventana con la misma cláusula OVER o una cláusula OVER diferente.

Las funciones de ventana se ejecutan en último lugar, después de las operaciones...

Unir los resultados de varias consultas

MySQL admite la utilización del operador UNION que lleva a cabo la unión de los resultados de varias consultas.

Sintaxis

   consulta_SELECT 
UNION [ALL | DISTINCT] 
   consulta_SELECT 
[UNION ...] 
[ORDER BY orden]

Las sentencias SELECT deben tener el mismo número de expresiones y las expresiones correspondientes deben tener normalmente el mismo tipo (según la documentación). En la práctica, si las expresiones correspondientes no son del mismo tipo, parece que son convertidas en cadena de caracteres para realizar la unión.

El título de las columnas del resultado final es definido por la primera consulta.

De manera predeterminada, todas las líneas devueltas son distintas; el mismo resultado se obtiene utilizando la opción DISTINCT. La utilización de la opción ALL permite conservar todas las filas, incluso las duplicadas.

Las sentencias SELECT no deben contener la cláusula ORDER BY; pueden incluir, sin embargo, combinaciones, subconsultas y las cláusulas WHERE, GROUP BY, etc. Para mejorar la legibilidad de las consulta, las sentencias SELECT pueden ponerse entre paréntesis.

El resultado final de la unión puede ordenarse mediante una cláusula ORDER BY, con la misma sintaxis que para una sentencia SELECT simple (véase el capítulo Introducción a MySQL - Ejecutar consultas SQL simples).

Ejemplos

mysql> SELECT titulo FROM catalogo  
    ->   UNION  
    -> SELECT IFNULL(CONCAT(titulo,' - ',subtitulo),titulo)  
    -> FROM libro WHERE id_coleccion = 1;  
+-------------------------------------------------------------------------------+  
| titulo                                                                        |  ...

Administrar las transacciones y los accesos coincidentes

1. Definición

El término «transacción» en el ámbito de las bases de datos relacionales se refiere a un conjunto de sentencias de actualización que forma un todo indisociable desde el punto de vista de la lógica aplicativa. Las sentencias de actualización de una transacción solo pueden registrarse definitivamente en la base de datos si todas ellas se ejecutan sin errores; si una de las sentencias de actualización falla, todas las modificaciones ya efectuadas en la transacción deben anularse. Al final de una transacción, la base de datos siempre se encuentra en un estado coherente desde el punto de vista de la lógica aplicativa.

A modo de ejemplo, consideremos una transacción de ingreso bancario constituida por tres sentencias de actualización:

un primer UPDATE para cargar la primera cuenta;
un segundo UPDATE para abonar la segunda cuenta;
un INSERT para guardar la operación en un registro.

Si el segundo UPDATE no tiene éxito, por una u otra razón, es necesario anular el primer UPDATE y no llevar a cabo la sentencia INSERT.

2. Administrar las transacciones

De manera predeterminada, MySQL funciona en un modo de validación automática (opción AUTOCOMMIT igual a 1): cada modificación efectuada es inmediata y definitivamente registrada en la base de datos, lo que no permite administrar correctamente las transacciones.

Por otro lado, la administración de las transacciones solo es soportada para el tipo de tabla InnoDB.

A partir del momento en que se utiliza una tabla que soporta las transacciones, se puede administrar las transacciones mediante las siguientes instrucciones:

START TRANSACTION | BEGIN [WORK]

SET AUTOCOMMIT = { 0 | 1 }

COMMIT [WORK]

ROLLBACK [WORK]

Las instrucciones START TRANSACTION o BEGIN (WORK es opcional) permiten iniciar explícitamente una nueva transacción. Es recomendable utilizar preferentemente la instrucción START TRANSACTION, que se ajusta al estándar SQL. Cuando una transacción es iniciada explícitamente de esta manera, la validación automática es desactivada el tiempo de la transacción; al final de la transacción, la validación automática vuelve a su estado anterior.

La instrucción SET AUTOCOMMIT permite activar (1) o desactivar...

Efectuar búsquedas mediante expresiones regulares

MySQL permite hacer búsquedas mediante expresiones regulares. Las expresiones regulares permiten especificar patrones complejos para la búsqueda en las cadenas.

MySQL propone varias funciones y operadores para realizar las operaciones mediante una expresión regular.

REGEXP_LIKE REGEXP RLIKE	Prueba si una cadena se corresponde con una expresión racional.
REGEXP_INSTR	Posición de una cadena dentro de otra cadena que se corresponde con una expresión racional.
REGEXP_SUBSTR	Porción de una cadena que se corresponde con una expresión racional.
REGEXP_REPLACE	Sustitución de las ocurrencias de una cadena que se corresponde con una expresión racional por otra cadena.

Las funciones REGEXP_% aparecieron en la versión 8.0.4; antes de esta versión, solo estaban disponibles los operadores REGEXP y RLIKE (sinónimo de REGEXP). Desde la versión 8.0.4, la gestión de las expresiones racionales se ha vuelto a escribir usando el estándar International Components for Unicode (ICU) y se han añadido nuevas funciones. Por razones de compatibilidad hacia atrás, los operadores REGEXP y RLIKE todavía existen, pero ahora son alias de la función REGEXP_LIKE.

Sintaxis de las funciones

REGEXP_LIKE(expresión,modelo[,modo])  
expresión [NOT] REGEXP modelo  
expresión [NOT] RLIKE modelo  
REGEXP_INSTR(expresión,modelo[,posición[,ocurrencia[,opción[,modo]]]]) 
REGEXP_SUBSTR(expresión,modelo[,posición[,ocurrencia[,modo]]])  
REGEXP_REPLACE(expresión,modelo,sustitución[,posición[,ocurrencia 
[,modo]]])

modelo es una expresión racional que describe la estructura de la cadena buscada (ver más adelante).

modo es una cadena que permite indicar cómo realizar la búsqueda; puede contener uno o varios de los siguientes caracteres:

c	Búsqueda sensible a la diferencia entre mayúsculas y minúsculas.
i	Búsqueda no sensible a la diferencia entre mayúsculas y minúsculas.
m	Modo multi-línea (trata los retornos de carro dentro de la cadena).
n	El símbolo «punto» (.) incluye el retorno de carro.
u	Para la finalización de línea solo se considera el retorno de carro Unix.

Si hay caracteres que especifican...

Realizar búsquedas de texto completo

1. Principios

MySQL permite realizar búsquedas de palabras en el conjunto de un texto (o varios textos).

Para utilizar esta característica, es necesario:

crear un índice especial de tipo FULLTEXT;
utilizar la función MATCH AGAINST en las búsquedas.

Los índices FULLTEXT pueden crearse en columnas de tipo CHAR, VARCHAR o TEXT, pero solo en tablas MyISAM o InnoDB.

2. Creación del índice FULLTEXT

Los índices FULLTEXT pueden constituirse en la creación inicial de la tabla (en la sentencia CREATE TABLE) o ulteriormente (mediante una sentencia ALTER TABLE o CREATE FULLTEXT INDEX).

Para crear un índice FULLTEXT en una sentencia CREATE TABLE o ALTER TABLE, es necesario utilizar una cláusula FULLTEXT similar a la cláusula INDEX presentada en el capítulo Construir una base de datos en MySQL. La sentencia CREATE FULLTEXT INDEX es una variante de la sentencia CREATE INDEX presentada en el capítulo Construir una base de datos en MySQL.

Sintaxis

CREATE TABLE nombre_tabla 
( 
especificacion_columnas, 
FULLTEXT(nombre_columna[,...]) 
) 
ALTER TABLE nombre_tabla ADD FULLTEXT(nombre_columna[,...]) 
CREATE FULLTEXT INDEX nombre_indice ON nombre_tabla(nombre_columna[,...])

Ejemplo

mysql> CREATE FULLTEXT INDEX ind_texto 
    -> ON libro(titulo,subtitulo,descripcion); 
Query OK, 0 rows affected, 1 warning (0.09 sec) 
Records: 0 Duplicates: 0 Warnings: 1 
mysql> SHOW WARNINGS; 
+---------+------+--------------------------------------------------+ 
| Level   | Code | Message                                          | 
+---------+------+--------------------------------------------------+ 
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID | 
+---------+------+--------------------------------------------------+ 
1 row in set (0.00 sec)

Un índice FULLTEXT InnoDB necesita una columna FTS_DOC_ID en la tabla indexada para almacenar un identificador único de documento. Si esta columna no existe, InnoDB añade automáticamente una columna FTS_DOC_ID oculta cuando el índice se crea y genera una alerta (véase el ejemplo anterior).

3. Realizar una búsqueda de texto completo...

Desarrollar rutinas

1. Introducción

Una rutina es un conjunto de sentencias y de instrucciones de procedimiento (estructuras de control, declaración de variables, etc.) que realiza una tarea específica y que se guarda con un nombre en la base de datos. La rutina puede invocarse desde diferentes entornos de desarrollo para ejecutar la tarea en cuestión.

Utilizar rutinas ofrece varias ventajas:

Mejorar el rendimiento: el código se almacena en la base de datos y hay menos intercambio entre el cliente y el servidor.
Reutilizar código: el código almacenado puede ser utilizado por otras rutinas sin tener que implementar de nuevo la lógica aplicativa.
Mejorar la integridad de los datos: las reglas de gestión pueden crearse en un solo lugar, en las rutinas. Si las aplicaciones cliente no tienen privilegios para acceder directamente a las tablas, pero deben utilizar las rutinas, la integridad de los datos está garantizada.

Existen dos tipos de rutinas:

los procedimientos;
las funciones.

2. Administración de los privilegios

Los privilegios siguientes son necesarios para administrar las rutinas:

CREATE ROUTINE para crear una rutina;
ALTER ROUTINE para modificar o eliminar una rutina (automáticamente atribuida al creador de una rutina).

Por otro lado, para crear una rutina, es necesario tener los privilegios adaptados a los objetos (tablas, vistas, etc.) manipulados por la rutina.

Para ejecutar una rutina, el usuario debe disponer del privilegio EXECUTE sobre la rutina en cuestión (atribuido automáticamente al creador de la rutina).

De manera predeterminada, una rutina se ejecuta con los privilegios del propietario de la rutina. Esto significa que un usuario con el privilegio de ejecutar una rutina no necesita tener los privilegios sobre los objetos (tablas, vistas, etc.) manipulados por la rutina. Este funcionamiento es interesante en términos de administración de privilegios: para acceder a los objetos manipulados por la rutina, el usuario está obligado a ejecutar la rutina que puede implementar todas las reglas de administración o de seguridad adecuadas. Si es necesario, es posible definir programas de almacenamiento que se ejecuten con los permisos del llamador.

3. Administración de las rutinas

Las sentencias CREATE PROCEDURE y CREATE FUNCTION permiten crear un procedimiento almacenado o una función almacenada.

Sintaxis

CREATE PROCEDURE...

Desarrollar triggers

1. Definición

Un trigger (desencadenador o disparador en español) es una rutina almacenada asociada a una tabla que se desencadena automáticamente cuando un evento de actualización (INSERT, UPDATE o DELETE) se lleva a cabo en la tabla. Un trigger nunca es ejecutado explícitamente por otra rutina.

Los triggers permiten implementar reglas de gestión del lado del servidor. Los principales usos de los triggers son los siguientes:

Calcular automáticamente el valor de una columna: por ejemplo, un trigger puede utilizarse para calcular automáticamente un precio con IVA a partir de un precio sin IVA y un índice de IVA.
Hacer un seguimiento de las actualizaciones en la base de datos: por ejemplo, cada vez que se elimina un artículo, un trigger registra la eliminación (quién, cuándo, qué) en una tabla de auditoría.

2. Administración de los triggers

La sentencia CREATE TRIGGER permite crear trigger.

Sintaxis:

CREATE TRIGGER [IF NOT EXISTS] [nombre_base.]nombre_trigger 
  {BEFORE | AFTER} 
  {INSERT | UPDATE | DELETE} 
  ON nombre_tabla 
  FOR EACH ROW 
  [{FOLLOWS | PRECEDES} otro_trigger] 
BEGIN 
  instrucciones; 
END;

La cláusula IF NOT EXISTS impide que se produzca un error si ya existe un trigger con el mismo nombre; esta cláusula apareció en la versión 8.0.29.

nombre_base designa la base de datos en la que debe definirse el trigger. De manera predeterminada, la rutina se almacena en la base de datos actual.

nombre_trigger especifica el nombre del trigger. Este nombre debe respetar las reglas para los nombres de objetos MySQL.

nombre_tabla especifica la tabla a la que se asocia el trigger; la tabla y el trigger deben almacenarse en la misma base de datos.

La cláusula BEFORE o AFTER permite indicar en qué momento se desencadena el trigger: justo antes de que la actualización se produzca (BEFORE) o justo después (AFTER).

Las palabras clave INSERT, UPDATE o DELETE indican en qué sentencia SQL de actualización debe desencadenarse el trigger....

Técnicas avanzadas con MySQL

Agrupar los datos

Utilizar subconsultas

1. Introducción

2. Subconsulta escalar

Usar las funciones de ventana

1. Introducción

Unir los resultados de varias consultas

Administrar las transacciones y los accesos coincidentes

1. Definición

2. Administrar las transacciones

Efectuar búsquedas mediante expresiones regulares

Realizar búsquedas de texto completo

1. Principios

2. Creación del índice FULLTEXT

3. Realizar una búsqueda de texto completo...

Desarrollar rutinas

1. Introducción

2. Administración de los privilegios

3. Administración de las rutinas

Desarrollar triggers

1. Definición

2. Administración de los triggers