Gestión y Consultas Avanzadas de MySQL en Contenedores Docker

Despliegue de MySQL en Docker

Para ejecutar una instancia de MySQL utilizando Docker, es fundamental configurar la persistencia de datos mediante volúmenes y establecer las credenciales del administrador. A continuación, se muestra el comando para levantar el contenedor mapeando el puerto y montando un directorio local:

docker run -d \
  -p 3307:3306 \
  -v /ruta/local/datos:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=SuperSecretPass \
  --name gestor-mysql \
  mysql:8.0

Resolución de Problemas de Autenticación

Al conectar herramientas gráficas como Navicat o DBeaver, es común encontrar el error Authentication plugin 'caching_sha2_password' cannot be loaded debido a los cambios de seguridad en MySQL 8. Para solucionarlo, accede al contenedor y modifica el plugin de autenticación del usuario raíz:

mysql -h localhost -u root -p

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'SuperSecretPass';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SuperSecretPass';

FLUSH PRIVILEGES;

Creación de Esquema y Manipulación de Datos

Una vez configurado el entorno, procedemos a crear una base de datos y una tabla para almacenar información de vehículos. Definiremos la estructura con tipos de datos apropiados y restricciones de no nulidad:

CREATE TABLE vehiculo (
   id INT PRIMARY KEY AUTO_INCREMENT,
   marca VARCHAR(50) NOT NULL,
   modelo VARCHAR(50) NOT NULL,
   anio INT NOT NULL,
   color VARCHAR(20) NOT NULL,
   precio DECIMAL(10, 2) NOT NULL
) CHARSET=utf8mb4;

Insertamos un conjunto de registros de prueba para realizar las consultas posteriores:

INSERT INTO vehiculo (marca, modelo, anio, color, precio) VALUES
('Toyota', 'Corolla', 2020, 'Rojo', 18000.00),
('Honda', 'Civic', 2019, 'Azul', 19500.00),
('Ford', 'Mustang', 2022, 'Negro', 35000.00),
('Chevrolet', 'Camaro', 2021, 'Amarillo', 32000.00),
('Tesla', 'Model 3', 2023, 'Blanco', 40000.00),
('BMW', 'Serie 3', 2020, 'Gris', 38000.00),
('Audi', 'A4', 2018, 'Rojo', 29000.00),
('Mercedes', 'Clase C', 2021, 'Negro', 42000.00),
('Nissan', 'Altima', 2019, 'Azul', 17000.00),
('Hyundai', 'Sonata', 2022, 'Blanco', 22000.00);

Consultas Básicas y Filtrado

La extracción de datos específicos se realiza mediante la cláusula SELECT. Podemos renombrar las columnas en la salida utilizando alias con AS:

SELECT marca AS 'Marca', precio AS 'Precio' FROM vehiculo;

Para filtrar resultados, utilizamos WHERE combinado con operadores lógicos y de comparación:

-- Filtrado por año
SELECT marca AS 'Marca', color AS 'Color' FROM vehiculo WHERE anio >= 2021;

-- Múltiples condiciones con AND
SELECT marca AS 'Marca', color AS 'Color' FROM vehiculo WHERE color = 'Rojo' AND precio > 20000;

-- Búsqueda de patrones con LIKE
SELECT * FROM vehiculo WHERE marca LIKE 'To%';

-- Filtro por conjuntos con IN y NOT IN
SELECT * FROM vehiculo WHERE color IN ('Rojo', 'Azul');
SELECT * FROM vehiculo WHERE color NOT IN ('Rojo', 'Azul');

-- Rango de valores con BETWEEN
SELECT * FROM vehiculo WHERE anio BETWEEN 2019 AND 2021;

La paginación de resultados se controla mediante LIMIT, y el ordenamiento se define con ORDER BY:

SELECT * FROM vehiculo LIMIT 0, 5;
SELECT * FROM vehiculo LIMIT 5, 5;

-- Ordenamiento multinivel
SELECT marca, precio, anio FROM vehiculo ORDER BY precio ASC, anio DESC;

Funciones Integradas y Agregaciones

Las funciones de agregación permiten realizar cálculos estadísticos sobre conjuntos de datos, combinadas con GROUP BY para segmentar los resultados:

SELECT 
    color AS 'Color', 
    AVG(precio) AS 'Precio Promedio', 
    COUNT(*) AS 'Cantidad', 
    SUM(precio) AS 'Total', 
    MIN(precio) AS 'Minimo', 
    MAX(precio) AS 'Maximo' 
FROM vehiculo 
GROUP BY color;

Para filtrar grupos ya agregados, utilizamos HAVING en lugar de WHERE, y DISTINCT para eliminar duplicados:

SELECT color, AVG(precio) AS promedio 
FROM vehiculo 
GROUP BY color 
HAVING promedio > 25000;

SELECT DISTINCT color FROM vehiculo;

MySQL ofrece una amplia variedad de funciones nativas para manipular datos:

Funciones de cadena: CONCAT, SUBSTRING, LENGTH, UPPER, LOWER.

SELECT CONCAT('Auto: ', marca, ' ', modelo), SUBSTRING(modelo, 1, 3) AS 'Modelo Corto', LENGTH(marca) FROM vehiculo;

Funciones numéricas: ROUND, CEIL, FLOOR, ABS, MOD.

SELECT ROUND(12345.6789, 2), CEIL(12345.6789), FLOOR(12345.6789), ABS(-12345.6789), MOD(10, 3);

Funciones de fecha: YEAR, MONTH, DAY, DATE, TIME.

SELECT YEAR('2023-10-25 14:30:00'), MONTH('2023-10-25 14:30:00'), DAY('2023-10-25 14:30:00');

Funciones condicionales: IF, CASE.

SELECT marca, precio, 
CASE 
    WHEN precio > 35000 THEN 'Premium' 
    WHEN precio > 20000 THEN 'Estándar' 
    ELSE 'Básico' 
END AS 'Categoria' 
FROM vehiculo;

Funciones del sistema y utilitarias: VERSION, DATABASE, NULLIF, COALESCE, GREATEST, LEAST.

SELECT VERSION(), DATABASE(), USER();
SELECT NULLIF(1,1), COALESCE(NULL, 1), GREATEST(10, 20, 30), LEAST(10, 20, 30, 5);

Relaciones Uno a Uno y Joins

En el modelado relacional, las dependencias uno a uno se representan mediante claves foráneas. Por ejemplo, un cliente posee una única licencia de conducir. Creamos las tablas cliente (tabla principal) y licencia (tabla dependiente):

CREATE TABLE cliente (
    id INT NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE licencia (
    id INT NOT NULL AUTO_INCREMENT,
    numero VARCHAR(20) NOT NULL,
    cliente_id INT DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_cliente (cliente_id),
    CONSTRAINT fk_cliente FOREIGN KEY (cliente_id) REFERENCES cliente(id)
) CHARSET=utf8mb4;

Para extraer datos de ambas tablas simultáneamente, utilizamos JOIN. Por defecto, esto equivale a un INNER JOIN, que solo devuelve las filas con coincidencias en ambas tablas:

SELECT c.id, c.nombre, l.id AS licencia_id, l.numero
FROM cliente c
INNER JOIN licencia l ON c.id = l.cliente_id;

Si existen registros en la tabla izquierda (cliente) que no tienen una licencia asociada, podemos utilizar LEFT JOIN para incluirlos, rellenando con valores nulos las columnas de la tabla derecha. De manera inversa, RIGHT JOIN prioriza los registros de la tabla derecha (licencia).

Acciones en Cascada y Restricciones

Al definir claves foráneas, es crucial establecer el comportamiento ante actualizaciones o eliminaciones en la tabla principal. Las opciones principales son:

CASCADE: Propaga la eliminación o actualización a la tabla dependiente.
SET NULL: Establece la clave foránea en NULL si el registro principle es eliminado o modificado.
RESTRICT / NO ACTION: Impide la eliminación o actualización del registro principal si existen registros dependientes asociados.

Si intentamos eliminar un cliante que tiene una licencia asociada bajo una restricción RESTRICT, la operación fallará. Si cambiamos la restricción a CASCADE, al eliminar el cliente, su licencia se eliminará automáticamente.

Relaciones Uno a Muchos

Este tipo de relación es común en escenarios como proveedores y sus productos. Un proveeodr puede ofrecer múltiples productos, pero cada producto pertenece a un único proveedor. La clave foránea se coloca en la tabla del lado "muchos" (producto):

CREATE TABLE proveedor (
    id INT NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE producto (
    id INT NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    proveedor_id INT DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_proveedor FOREIGN KEY (proveedor_id) REFERENCES proveedor(id) ON DELETE SET NULL
);

Al utilizar ON DELETE SET NULL, si un proveedor es eliminado del sistema, sus productos asociados no se borran, sino que su campo proveedor_id pasa a ser NULL, indicando que quedaron sin asignación.

Diseño de Relaciones Muchos a Muchos

Para modelar relaciones donde ambas entidades pueden tener múltiples instancias de la otra (ej. actores y películas), se requiere una tabla intermedia o de enlace. Esta tabla contiene las claves foráneas de ambas entidades y forma una clave primaria compuesta:

CREATE TABLE actor (
    id INT NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE pelicula (
    id INT NOT NULL AUTO_INCREMENT,
    titulo VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE actor_pelicula (
    actor_id INT NOT NULL,
    pelicula_id INT NOT NULL,
    PRIMARY KEY (actor_id, pelicula_id),
    CONSTRAINT fk_actor FOREIGN KEY (actor_id) REFERENCES actor(id) ON DELETE CASCADE,
    CONSTRAINT fk_pelicula FOREIGN KEY (pelicula_id) REFERENCES pelicula(id) ON DELETE CASCADE
);

Para consultar los actores de una película específica, debemos encadenar dos operaciones JOIN a través de la tabla intermedia:

SELECT p.titulo, a.nombre
FROM pelicula p
JOIN actor_pelicula ap ON p.id = ap.pelicula_id
JOIN actor a ON a.id = ap.actor_id
WHERE p.id = 1;

Gracias a la directiva ON DELETE CASCADE definida en las restricciones de la tabla actor_pelicula, si eliminamos un registro de la tabla pelicula, todas las filas correspondientes en la tabla intermedia que referencian a esa película se purgarán automáticamente, manteniendo la integridad referencial sin afectar a los registros de la tabla actor.

Etiquetas: Docker MySQL SQL relational-database database-design

Publicado el 8-13 12:27

Friki Work