
En esta entrada me voy a realizar un caso práctico que abarca las consultas habituales que se realizan en Mysql.
La intención es disponer de una guía rápida de consultas y no dar una explicación detallada de cada una. Es una entrada con la intención de recordar lo que ya se sabe sobre las consultas sql en Mysql.
Voy a crear una base de datos sencilla con varias tablas relacionadas entre sí. Después iré alimentando con datos las tablas y se realizarán ciertas operaciones sobre ellas.
El módelo ER inicial quedaría así:

La base de datos a crear dispone de cuatro tablas. Contiene relaciones entre ellas para poder realizar un juego mínimo de consultas habituales. Inicialmente se creará la base de datos, después las tablas, se alimentarán las mismas y se realizan consultas sobre ellas. Por último se borrarán las tablas y la base de datos para terminar el ciclo completo.
Para lanzar las consultas he utilizado un software gratuito llamado HeidiSQL. Es un cliente sql para varias bases de datos como mysql, mariadb, sql server o postgresql. En la página principal de HeidiSQL se puede descargar el programa. Aunque se puede utilizar otro tipo de software sin ningún problema.
El uso de este software es muy sencillo y he trabajado con él sin problemas para hacer esta entrada. En esta captura se puede ver la hoja de trabajo que he utilizado para lanzar todas las consultas.
Índice de acciones
Crear base de datos
Seleccionar la base de datos
Crear tabla asignatura
Crear tabla alumno
Crear tabla nota
Crear tabla labor_extra
Inserción de los datos
Select con *
Select seleccionando los campos
Insertar un nuevo campo a una tabla
Actualizar el valor de un campo de una tabla
Ejemplo de uso de INNER JOIN
Uso de IN en las consultas
Uso de LIKE para el filtrado
Uso de LEFT OUTER JOIN
Uso de GROUP BY y funciones agregadas
Filtrado de los resultados agregados mediante HAVING
Uso de DELETE
Borrado de tablas
Borrado de la base de datos
Conectar a mariadb desde springboot
Crear base de datos
Existen muchas opciones de crear una base de datos que quedan fuera de este blog. Voy a utilizar una instrucción sencilla. El comando sería:
CREATE DATABASE colegio CHARACTER SET UTF8mb4 COLLATE utf8mb4_bin
Seleccionar la base de datos
Es necesario escribir:
USE colegio
Crear tabla asignatura
Creación de la tabla asignatura.
CREATE TABLE asignatura ( id INT(6) NOT NULL auto_increment, nombre VARCHAR(25) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Crear tabla alumno
Creación de la tabla alumno.
CREATE TABLE alumno ( id INT(6) NOT NULL auto_increment, nombre VARCHAR(25) NOT NULL, apellido VARCHAR (25) NOT NULL, fecha_nacimiento DATE NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Crear tabla nota
La tabla nota como se puede observar el diagrama ER disponde de dos claves foráneas que son alumno y asignatura.
CREATE TABLE nota ( id INT(6) NOT NULL auto_increment, asignatura_id INT, calificacion FLOAT NOT NULL, fecha_examen DATE NOT NULL, convocatoria INT(6), alumno_id INT, INDEX alum_ind (alumno_id), FOREIGN KEY (alumno_id) REFERENCES alumno(id) ON DELETE CASCADE, INDEX asignat_ind (asignatura_id), FOREIGN KEY (asignatura_id) REFERENCES asignatura(id) ON DELETE CASCADE, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Crear tabla labor_extra
La tabla labor_extra como se puede observar el diagrama ER disponde de una clave foránea que es alumno.
CREATE TABLE labor_extra ( id INT(6) NOT NULL auto_increment, asignatura_id INT, calificacion FLOAT NOT NULL, fecha_examen DATE NOT NULL, convocatoria INT(6), alumno_id INT, INDEX alum_ind (alumno_id), FOREIGN KEY (alumno_id) REFERENCES alumno(id) ON DELETE CASCADE, INDEX asignat_ind (asignatura_id), FOREIGN KEY (asignatura_id) REFERENCES asignatura(id) ON DELETE CASCADE, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Inserción de los datos
Mediante la instrucción INSERT se guarda la información. En algunos casos se debe conocer el valor numérico de las claves primarias que se utilizan en las claves foráneas. En una aplicación real este valor ya se debe conocer, probablemente porque se pase por parámetro.
-- insertar asignatura INSERT INTO asignatura(nombre) VALUES ('Matemáticas'); INSERT INTO asignatura(nombre) VALUES ('Lengua'); -- insertar alumno INSERT INTO alumno(nombre, apellido, fecha_nacimiento) VALUES ('Juan', 'Pardo', '1978-08-03'); INSERT INTO alumno(nombre, apellido, fecha_nacimiento) VALUES ('Manuel', 'Fernandez', '1982-11-10'); INSERT INTO alumno(nombre, apellido, fecha_nacimiento) VALUES ('Pedro', 'Lopez', '1980-03-21'); INSERT INTO alumno(nombre, apellido, fecha_nacimiento) VALUES ('Maria', 'Gutierrez', '1976-12-19'); -- insertar nota INSERT INTO nota(asignatura_id, calificacion, fecha_examen,convocatoria, alumno_id) VALUES (1, 7, '2018-12-19', 1, 1); INSERT INTO nota(asignatura_id, calificacion, fecha_examen,convocatoria, alumno_id) VALUES (2, 5, '2018-11-03', 2, 1); INSERT INTO nota(asignatura_id, calificacion, fecha_examen,convocatoria, alumno_id) VALUES (1, 3, '2018-11-03', 3, 2); INSERT INTO nota(asignatura_id, calificacion, fecha_examen,convocatoria, alumno_id) VALUES (2, 8, '2018-11-03', 1, 2); INSERT INTO nota(asignatura_id, calificacion, fecha_examen,convocatoria, alumno_id) VALUES (1, 2, '2018-07-05', 2, 3); INSERT INTO nota(asignatura_id, calificacion, fecha_examen,convocatoria, alumno_id) VALUES (2, 5, '2018-11-03', 1, 3); INSERT INTO nota(asignatura_id, calificacion, fecha_examen,convocatoria, alumno_id) VALUES (1, 9, '2018-09-13', 3, 4); INSERT INTO nota(asignatura_id, calificacion, fecha_examen,convocatoria, alumno_id) VALUES (2, 5, '2018-11-23', 1, 4); -- insertar labor extra INSERT INTO labor_extra(puesto, alumno_id) VALUES ('Delegado de clase', 1); INSERT INTO labor_extra(puesto, alumno_id) VALUES ('Director del centro', 2);
Select con *
A modo de consulta sencilla el uso del * puede ser útil pero en consultas complejas no está recomendado. Es mejor indicar exactamente que campos se quieren mostrar.
select * from asignatura
Se obtiene:
id | nombre |
1 | Matemáticas |
2 | Lengua |
Select seleccionando los campos
Una manera más habitual es seleccionar los campos de manera específica
select a.nombre, a.apellido from alumno a
Se obtiene:
nombre | apellido |
Juan | Pardo |
Manuel | Fernandez |
Pedro | Lopez |
María | Gutierrez |
Insertar un nuevo campo a una tabla
Para insertar a la tabla
ALTER TABLE alumno ADD COLUMN apellido2 VARCHAR(25) AFTER apellido;
Actualizar el valor de un campo de una tabla
Mediante la instrucción UPDATE es posible actualizar un conjunto de datos sobre una tabla. Hay que tener mucho cuidado con filtrar de manera adecuada cuales serán las filas a actualizar. Antes de lanzar la consulta hay que comprobar que la condición WHERE es la adecuada. En este ejemplo actualizamos los segundos apellidos de todos los alumnos
UPDATE alumno SET apellido2 = 'Palazon' WHERE nombre = 'Juan' and apellido = 'Pardo'; UPDATE alumno SET apellido2 = 'Gomez' WHERE nombre = 'Manuel' and apellido = 'Fernandez'; UPDATE alumno SET apellido2 = 'Leost' WHERE nombre = 'Pedro' and apellido = 'Lopez'; UPDATE alumno SET apellido2 = 'Martin' WHERE nombre = 'Maria' and apellido = 'Gutierrez';
Ejemplo de uso de INNER JOIN
En este ejemplo se muestran las calificaciones de los alumnos. Es necesario usar las tablas alumno, nota y asignatura . Se ha ordenado por apellido mediante ORDER BY. Por defecto el resultado obtenido es en orden ascendente.
SELECT a.nombre, asig.nombre as asignatura, n.calificacion FROM alumno a INNER JOIN nota n ON a.id = n.alumno_id INNER JOIN asignatura asig ON asig.id = n.asignatura_id ORDER BY apellido
Se obtiene:
nombre | asignatura | calificacion |
Manuel | Matemáticas | 3 |
Manuel | Lengua | 8 |
Maria | Lengua | 5 |
Maria | Matemáticas | 9 |
Pedro | Matemáticas | 2 |
Pedro | Lengua | 5 |
Juan | Lengua | 5 |
Juan | Matemáticas | 7 |
Uso de IN en las consultas
Se puede seleccionar mediante IN alumnos que tengan alguna de las notas deseados. En este ejemplo se quieren obtener los alumnos que tengan de nota de examen 5,6,7 ó 8.
SELECT a.nombre, asig.nombre as asignatura, n.calificacion FROM alumno a INNER JOIN nota n ON a.id = n.alumno_id INNER JOIN asignatura asig ON asig.id = n.asignatura_id WHERE n.calificacion in (5, 6, 7, 8) ORDER BY calificacion
Se obtiene:
nombre | asignatura | calificacion |
Maria | Lengua | 5 |
Juan | Lengua | 5 |
Pedro | Lengua | 5 |
Juan | Matemáticas | 7 |
Manuel | Lengua | 8 |
Si sólo hubieramos deseado alumnos con calificacion = 5 hubiera bastado poner en la sección WHERE :
WHERE n.calificacion = 5
Uso de LIKE para el filtrado
Sobre los campos de texto se puede emplear el comando LIKE. En la siguiente consulta se muestran los alumnos cuyo nombre empieza por 'Ma' o bien su apellido contiene los caracteres 'ar' .
SELECT a.nombre, a.apellido FROM alumno a WHERE a.nombre LIKE 'Ma%' OR a.apellido LIKE '%ar%'
Se obtiene:
nombre | apellido |
Juan | Pardo |
Manuel | Fernandez |
María | Gutierrez |
Uso de LEFT OUTER JOIN
En ocasiones queremos unir el contenido de 2 tablas. Aunque la clave de la primera tabla no exista en la segunda queremos sacar todo el contenido de la primera. En estos casos se utiliza LEFT OUTER JOIN. En el ejemplo queremos sacar todos los alumnos y además indicar la labor extra que realicen. Si no disponen de ninguna labor los queremos mostrar igualmente. En ese caso el valor del puesto será NULL como se muestra en la salida obtenida
SELECT a.nombre, l.puesto FROM alumno a LEFT OUTER JOIN labor_extra l ON a.id = l.alumno_id
Se obtiene:
nombre | puesto |
Juan | Delegado de clase |
Manuel | Director del centro |
Pedro | (NULL) |
María | (NULL) |
Uso de GROUP BY y funciones agregadas
Mediante el uso de GROUP BY podemos agrupar por nombre y apellido y calcular la media de las calificaciones de cada alumno (empleando la función agregada AVG). Hay que tener en cuenta que los campos que se encuentran en la sección SELECT sólo pueden ser los campos que se encuentran en la sección GROUP BY (aparte de la función agregada). La consulta ordena de manera descendente los resultados mediante el uso de la palabra DESC en ORDER BY.
Las funciones agregadas disponibles en mysql son:
Nombre | Función |
SUM | Realiza la suma de los valores agrupados |
AVG | Media de los valores agrupados |
COUNT | Cuenta los valores agrupados |
SELECT a.nombre, a.apellido, avg(n.calificacion) as media FROM alumno a INNER JOIN nota n ON a.id = n.alumno_id INNER JOIN asignatura asig ON asig.id = n.asignatura_id GROUP BY a.nombre, a.apellido ORDER BY media DESC
Se obtiene:
nombre | apellido | media |
María | Gutierrez | 7 |
Juan | Pardo | 6 |
Manuel | Fernandez | 5,5 |
Pedro | Lopez | 3,5 |
Filtrado de los resultados agregados mediante HAVING
Mediante el uso de HAVING podemos filtrar los resultados obtenidos mediante la función agregada AVG. La consulta devuelve las medias de notas de los alumnos cuya media es mayor ó igual de 5.
SELECT a.nombre, a.apellido, avg(n.calificacion) as media FROM alumno a INNER JOIN nota n ON a.id = n.alumno_id INNER JOIN asignatura asig ON asig.id = n.asignatura_id GROUP BY a.nombre, a.apellido HAVING media >= 6 ORDER BY media desc
Se obtiene:
nombre | apellido | media |
María | Gutierrez | 7 |
Juan | Pardo | 6 |
Uso de DELETE
Para el borrado de datos en una tabla se utiliza DELETE. La condición WHERE será necesaria para borrar solamente los datos deseados. En este caso se desea borrar aquellos alumnos que tengan el puesto de 'Director del centro' en la tabla labor_extra
DELETE FROM labor_extra WHERE puesto = 'Director del centro'
Borrado de tablas
Para borrar tablas se usa el comando DROP TABLE. Una vez que se disponen de claves foráneas (FK) el borrado de tablas se debe realizar en un orden concreto ya que existen dependencias entre tablas. En este caso he borrado las tablas en el orden inverso de creación para evitar estos problemas.
DROP TABLE labor_extra; DROP TABLE nota; DROP TABLE alumno; DROP TABLE asignatura;
Borrado de la base de datos
Si se disponen de los permisos necesarios el borrado de la base de datos se realiza mediante DROP DATABASE. En este caso sería:
DROP DATABASE colegio
Configuración desde springboot para conectar con mariaDB
En muchos tutoriales donde se explica como se debe conectar un aplicación springboot con una base de datos mysql se describe una configuración en el properties basada en mysql. He observado que con mariadb el driver de mysql no funciona bien (con mariadb en linux) y es necesario utilizar el driver específico de esta base de datos. Dejo a continuación el driver necesario a usar en el pom de tu proyecto spring boot y un fichero properties para la conexión con la misma.
Elimina la dependencia mysql y añade esta a tu pom.xml:
El gist con el fichero properties para conectar con una base de datos mariadb:
A veces no se recuerda muy bien como realizar cierta consultas en mysql y esta entrada puede servir de recordatorio. Han faltado algunas instrucciones que se añadirán en una futura revisión de la entrada. Por ejemplo: manejo de fechas con TIMESTAMP ó el uso de UNION.
Es muy importante revisar e investigar en el manual de referencia que aporta mysql en su versión 8.0 si se tienen dudas. Es un manual muy completo y muy extenso donde buscar información más específica.