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 alumno el campo apellido2 bastaría con lanzar la instrucción:

 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:

 

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.