MySQL - Capitulo 2

Una guía através del camino de las bases de datos con MySQL uno de los sistemas de gestión de bases de datos más conocidos y utilizados en el mundo de la programación.

Síguenos con el tutorial

En QuéPasalinux MySQL - Relaciones entre Tablas y Consultas Avanzadas un vídeo práctico sobre MySQL y Docker

Volver a Página Principal

Ver Codigo en GitLab

📚 Curso de Capacitación de MySQL

🤝 Módulo 2: Relaciones entre Tablas y Consultas Avanzadas

¡Bienvenidos al Módulo 2! En esta sección, profundizaremos en el verdadero poder de las bases de datos relacionales. Ya sabes cómo levantar MySQL con Docker, crear y modificar tablas, e insertar y consultar datos básicos. Ahora, es momento de entender cómo las tablas se conectan entre sí y cómo aprovechar esas conexiones para extraer información compleja y valiosa.

2.1. Comprendiendo el Mundo Relacional

¿Por qué Relaciones? Exploraremos la importancia de la normalización para evitar la redundancia de datos, mantener la consistencia y mejorar la eficiencia de la base de datos.
- Ejemplo: Imagina que en una sola tabla Ventas guardas el nombre y dirección del cliente para cada venta. Si un cliente hace 10 compras, su nombre y dirección se repiten 10 veces. Si el cliente cambia de dirección, tendrías que actualizar 10 registros. Con las relaciones, el cliente está en una tabla Clientes una sola vez, y las Ventas solo guardan su id_cliente. ¡Más eficiente y fácil de mantener!
Tipos de Relaciones Esenciales:
- Uno a Muchos (1:N): La relación más común.
  - Ejemplo: Un Cliente puede tener muchas Ventas.
  - Ejemplo: Un Producto puede aparecer en muchas Ventas_Productos (detalles de ventas).
- Muchos a Muchos (N:M): Se resuelven utilizando tablas intermedias o de enlace.
  - Ejemplo: Muchos Productos pueden estar en muchas Ventas, y una Venta puede contener muchos Productos. La tabla Ventas_Productos (o DetalleVenta) es la que resuelve esta relación, guardando el id_venta y el id_producto.
- Uno a Uno (1:1): Casos de uso específicos, a menudo para extender una tabla sin sobrecargarla o para manejar datos muy sensibles separados.
  - Ejemplo: Una tabla Empleados y una tabla InformacionConfidencialEmpleado. Cada empleado tiene una única fila en InformacionConfidencialEmpleado.

2.2. Implementando Relaciones: La Clave Foránea (`FOREIGN KEY`)

El Rol de la Clave Foránea: Una clave foránea es una columna (o conjunto de columnas) en una tabla que se refiere a la clave primaria (o una clave única) en otra tabla. Es la columna que “enlaza” las tablas y garantiza la integridad referencial; es decir, que no puedas tener una venta asignada a un id_cliente que no existe.

Sintaxis y Aplicación:

-- Tabla Clientes (Padre)
CREATE TABLE Clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

-- Tabla Ventas (Hija), con una clave foránea a Clientes
CREATE TABLE Ventas (
    id_venta INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente INT NOT NULL, -- Columna que será la clave foránea
    fecha_venta DATETIME DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10, 2) NOT NULL,
    -- Definición de la Clave Foránea
    FOREIGN KEY (id_cliente) REFERENCES Clientes(id)
);

-- Ejemplo de Ventas_Productos (Tabla de enlace N:M)
CREATE TABLE Ventas_Productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_venta INT NOT NULL,
    id_producto INT NOT NULL,
    cantidad INT NOT NULL,
    FOREIGN KEY (id_venta) REFERENCES Ventas(id_venta),
    FOREIGN KEY (id_producto) REFERENCES Products(id) -- Asumiendo que Products(id) es PK en tu tabla Products
);

-- Añadir una clave foránea a una tabla existente (ALTER TABLE)
-- Si tu tabla Ventas_Pagos ya existe, y quieres añadirle la referencia a Ventas
ALTER TABLE Ventas_Pagos
ADD CONSTRAINT fk_venta_pago -- Nombre opcional para la clave foránea
FOREIGN KEY (id_venta) REFERENCES Ventas(id_venta);

Acciones de Integridad Referencial (ON DELETE, ON UPDATE):
- CASCADE: Si una fila en la tabla padre es eliminada o actualizada, las filas correspondientes en la tabla hija también son eliminadas o actualizadas.
```
-- Si elimino un cliente, todas sus ventas se eliminan automáticamente.
FOREIGN KEY (id_cliente) REFERENCES Clientes(id) ON DELETE CASCADE
```
- SET NULL: Si una fila en la tabla padre es eliminada o actualizada, la clave foránea en la tabla hija se establece a NULL. (La columna id_cliente en Ventas debe permitir NULL).
```
-- Si elimino un cliente, el id_cliente en sus ventas se pone a NULL.
FOREIGN KEY (id_cliente) REFERENCES Clientes(id) ON DELETE SET NULL
```
- RESTRICT (por defecto): Impide la acción si hay filas relacionadas. MySQL no te dejará eliminar un cliente si tiene ventas asociadas.
- NO ACTION: Similar a RESTRICT.

2.3. Uniendo Tablas: El Poder de `JOIN`

Combinando Información: Las cláusulas JOIN te permiten combinar filas de dos o más tablas basándose en una columna relacionada.

Tipos de JOIN Fundamentales:

INNER JOIN: Retorna solo las filas donde hay una coincidencia en ambas tablas. Es el tipo de JOIN más común y el que se asume si solo usas JOIN.

Cuándo usarlo: Cuando quieres ver solo los registros que tienen información en ambas tablas. Por ejemplo, clientes que han realizado ventas, o productos que han sido vendidos.

Sintaxis:

SELECT columnas
FROM Tabla1
INNER JOIN Tabla2 ON Tabla1.columna_comun = Tabla2.columna_comun;

Ejemplos Prácticos:

-- Ejemplo 1: Obtener el nombre del cliente y los detalles de sus ventas
-- Solo mostrará clientes que tienen al menos una venta.
SELECT
    C.nombre AS NombreCliente,
    V.id_venta AS NumeroVenta,
    V.fecha_venta AS FechaVenta,
    V.total AS TotalVenta
FROM
    Clientes AS C -- 'AS C' es un alias, ¡muy útil para abreviar!
INNER JOIN
    Ventas AS V ON C.id = V.id_cliente;

-- Ejemplo 2: Listar productos y en qué ventas han aparecido
-- Solo mostrará productos que han sido parte de alguna venta.
SELECT
    P.nombre_producto,
    VP.cantidad,
    VP.precio_venta AS PrecioVendido,
    V.fecha_venta AS FechaDeVenta
FROM
    Products AS P
INNER JOIN Ventas_Productos AS VP ON P.id = VP.id_producto
INNER JOIN Ventas AS V ON VP.id_venta = V.id_venta;

LEFT JOIN (o LEFT OUTER JOIN): Retorna todas las filas de la tabla “izquierda” (la que está en el FROM o antes del LEFT JOIN) y las filas coincidentes de la “derecha”. Si no hay coincidencia en la derecha, los campos de la tabla derecha serán NULL.

Cuándo usarlo: Cuando quieres ver todos los registros de una tabla, incluso si no tienen registros relacionados en otra. Por ejemplo, todos los clientes (hayan comprado o no), o todos los productos (se hayan vendido o no).

Sintaxis:

SELECT columnas
FROM Tabla1
LEFT JOIN Tabla2 ON Tabla1.columna_comun = Tabla2.columna_comun;

Ejemplos Prácticos:

-- Ejemplo 1: Obtener todos los clientes y, si tienen, sus ventas.
-- Clientes sin ventas también aparecerán, y sus columnas de Venta serán NULL.
SELECT
    C.nombre AS NombreCliente,
    V.id_venta AS NumeroVenta,
    V.total AS TotalVenta
FROM
    Clientes AS C
LEFT JOIN
    Ventas AS V ON C.id = V.id_cliente;

-- Ejemplo 2: Todos los productos y las ventas en las que han estado.
-- Incluye productos que nunca han sido vendidos (sus campos de venta serán NULL).
SELECT
    P.nombre_producto,
    VP.cantidad,
    V.fecha_venta
FROM
    Products AS P
LEFT JOIN Ventas_Productos AS VP ON P.id = VP.id_producto
LEFT JOIN Ventas AS V ON VP.id_venta = V.id_venta;

-- Ejemplo 3: Clientes que NO han realizado ninguna venta (usando LEFT JOIN y WHERE IS NULL)
SELECT
    C.nombre AS ClienteSinVentas
FROM
    Clientes AS C
LEFT JOIN
    Ventas AS V ON C.id = V.id_cliente
WHERE
    V.id_cliente IS NULL; -- Donde la coincidencia en Ventas es nula

RIGHT JOIN (o RIGHT OUTER JOIN): Similar a LEFT JOIN pero prioriza la tabla “derecha”. Retorna todas las filas de la tabla “derecha” y las filas coincidentes de la “izquierda”. Si no hay coincidencia en la izquierda, los campos de la tabla izquierda serán NULL.
- Cuándo usarlo: Menos común, ya que la mayoría de los RIGHT JOIN pueden reescribirse como LEFT JOIN simplemente invirtiendo el orden de las tablas en la consulta.
- Sintaxis:
```
SELECT columnas
FROM Tabla1
RIGHT JOIN Tabla2 ON Tabla1.columna_comun = Tabla2.columna_comun;
```
- Ejemplo Práctico:
```
-- Ejemplo: Mostrar todas las ventas y, si tienen, el nombre del cliente.
-- En este caso, todas las ventas deberían tener un cliente, por la integridad referencial.
SELECT
    V.id_venta,
    V.fecha_venta,
    C.nombre AS NombreCliente
FROM
    Clientes AS C
RIGHT JOIN
    Ventas AS V ON C.id = V.id_cliente;
```

FULL JOIN (o FULL OUTER JOIN): (No directamente soportado en MySQL con una sintaxis simple). Recuperaría todas las filas de ambas tablas, llenando con NULL donde no haya coincidencia. Se simula con LEFT JOIN y UNION ALL RIGHT JOIN.

Cuándo usarlo: Cuando quieres ver todos los registros de ambas tablas, hayan o no coincidencias.

Simulación en MySQL:

-- Ejemplo: Todos los clientes y todas las ventas, con coincidencias donde existan.
-- Este ejemplo puede ser más complejo dependiendo del resultado deseado.
(SELECT C.nombre, V.id_venta, V.total
 FROM Clientes C LEFT JOIN Ventas V ON C.id = V.id_cliente)
UNION ALL
(SELECT C.nombre, V.id_venta, V.total
 FROM Clientes C RIGHT JOIN Ventas V ON C.id = V.id_cliente
 WHERE C.id IS NULL); -- Para evitar duplicar los registros ya obtenidos por LEFT JOIN

2.4. Agregación y Agrupación de Datos

Funciones Agregadas:

COUNT():

-- Contar el total de clientes
SELECT COUNT(*) FROM Clientes;
-- Contar el número de ventas
SELECT COUNT(id_venta) FROM Ventas;

SUM():

-- Sumar el total de todas las ventas
SELECT SUM(total) AS IngresoTotal FROM Ventas;
-- Sumar el subtotal de todos los productos vendidos
SELECT SUM(subtotal) FROM Ventas_Productos;

AVG():

-- Calcular el precio promedio de los productos vendidos en Ventas_Productos
SELECT AVG(precio_venta) FROM Ventas_Productos;

MIN() y MAX():

-- Encontrar la venta más barata y la más cara
SELECT MIN(total) AS VentaMinima, MAX(total) AS VentaMaxima FROM Ventas;

GROUP BY: Agrupa filas que tienen el mismo valor en una o más columnas, permitiendo aplicar funciones agregadas a cada grupo.

-- Total de ventas por cliente
SELECT
    C.nombre AS Cliente,
    COUNT(V.id_venta) AS NumeroDeVentas,
    SUM(V.total) AS TotalGastado
FROM
    Clientes AS C
INNER JOIN
    Ventas AS V ON C.id = V.id_cliente
GROUP BY
    C.nombre; -- O C.id si el nombre no es único

HAVING: Filtra los resultados de GROUP BY basándose en condiciones aplicadas a los resultados agregados. ¡Recuerda que WHERE filtra filas antes de agrupar, y HAVING filtra grupos después de agrupar!

-- Clientes que han gastado más de $1000 en total
SELECT
    C.nombre AS Cliente,
    SUM(V.total) AS TotalGastado
FROM
    Clientes AS C
INNER JOIN
    Ventas AS V ON C.id = V.id_cliente
GROUP BY
    C.nombre
HAVING
    SUM(V.total) > 1000;

2.5. Subconsultas: Consultas Anidadas

Subqueries o Nested Queries: Una consulta dentro de otra.

Usos Comunes:

En la cláusula WHERE (para filtrar):

-- Obtener los nombres de los clientes que han realizado al menos una venta
SELECT nombre FROM Clientes
WHERE id IN (SELECT id_cliente FROM Ventas);

-- Clientes que han comprado 'Laptop Gamer'
SELECT C.nombre
FROM Clientes AS C
WHERE C.id IN (
    SELECT V.id_cliente
    FROM Ventas AS V
    INNER JOIN Ventas_Productos AS VP ON V.id_venta = VP.id_venta
    INNER JOIN Products AS P ON VP.id_producto = P.id
    WHERE P.nombre_producto = 'Laptop Gamer'
);

Como tablas derivadas en la cláusula FROM:

-- Clientes con su total de ventas (similar a JOIN con GROUP BY, pero la subconsulta actúa como una tabla temporal)
SELECT
    C.nombre,
    TotalVentasCliente.monto_total
FROM
    Clientes AS C
INNER JOIN (
    SELECT id_cliente, SUM(total) AS monto_total
    FROM Ventas
    GROUP BY id_cliente
) AS TotalVentasCliente ON C.id = TotalVentasCliente.id_cliente
WHERE TotalVentasCliente.monto_total > 500;

Como expresiones escalares en la cláusula SELECT: (La subconsulta debe retornar un solo valor).

-- Listar cada cliente y el número total de ventas que ha realizado
SELECT
    C.nombre,
    (SELECT COUNT(*) FROM Ventas WHERE id_cliente = C.id) AS NumeroVentas
FROM
    Clientes AS C;

2.6. Modificación de Datos Avanzada

UPDATE con JOIN:

-- Aumentar el precio de venta en Ventas_Productos en un 10% para productos de la categoría 'Electrónica'
-- (¡Ojo! Esto es solo si Ventas_Productos tuviera una referencia a categorías o si tuvieras que unir con Products)
-- Ejemplo más sencillo: Actualizar el correo de un cliente si ha hecho más de 5 ventas
UPDATE Clientes AS C
INNER JOIN (
    SELECT id_cliente, COUNT(id_venta) AS num_ventas
    FROM Ventas
    GROUP BY id_cliente
    HAVING num_ventas > 5
) AS ClientesFrecuentes ON C.id = ClientesFrecuentes.id_cliente
SET C.email = CONCAT('vip_', C.email)
WHERE C.email NOT LIKE 'vip_%';

DELETE con JOIN:

-- Eliminar clientes que no han realizado ninguna venta
DELETE C
FROM Clientes AS C
LEFT JOIN Ventas AS V ON C.id = V.id_cliente
WHERE V.id_cliente IS NULL;

Precauciones con UPDATE y DELETE: ¡Siempre usa la cláusula WHERE para evitar modificar o eliminar todos los registros de una tabla! Haz una copia de seguridad o usa transacciones antes de ejecutar comandos DELETE o UPDATE complejos en un entorno de producción.

🚀 Tarea Práctica del Módulo 2

¡Es hora de poner en práctica lo aprendido! Utilizaremos el esquema de tablas que ya tienes: Clientes, Ventas, Productos, Ventas_Productos, Ventas_Pagos (si la creaste), y Users.

Objetivos:

Validación de Relaciones:
- Asegúrate de que las FOREIGN KEY entre tus tablas (Ventas y Clientes, Ventas_Productos y Ventas/Productos, etc.) estén correctamente definidas y entiendas cómo operan las acciones ON DELETE y ON UPDATE.
- Si aún no lo hiciste, añade la clave foránea para id_modified en tus tablas de auditoría, referenciando a Users(id).
Consultas con JOIN:
- Crea una consulta que liste todas las ventas, mostrando el nombre del cliente que realizó la compra.
- Obtén la lista de todos los productos vendidos en una venta específica, incluyendo el precio al que se vendió y la cantidad.
- Muestra el nombre de todos los clientes que han comprado el producto ‘X’ (por ejemplo, ‘Teclado Mecánico’).
- Genera un reporte que muestre el nombre del producto y la cantidad total vendida de cada uno.
Consultas Agregadas y Agrupación:
- Calcula el total de ingresos generado por todas las ventas.
- Determina el monto total de compras por cada cliente, mostrando el nombre del cliente y el total.
- Encuentra el producto más vendido por cantidad total.
- Identifica al cliente que ha gastado más dinero en el sistema.
Subconsultas:
- Lista los clientes que tienen ventas registradas con un monto total superior a X (ej., $750).
- Muestra los detalles de las ventas que incluyen al menos un producto cuyo precio unitario sea mayor a $100.
Manipulación de Datos con Relaciones:
- Actualiza el Limite_Credito de los clientes que tengan un Total_Credito usado superior a 500 y que hayan realizado más de 3 ventas.
- Simula la eliminación de una Venta específica y observa el comportamiento de las filas en Ventas_Productos (gracias a ON DELETE CASCADE).

Aquí tienes el apartado de referencias y recursos adicionales en formato Markdown, listo para añadir a tu README.md:

📚 Referencias y Recursos Adicionales

Para profundizar en los conceptos de bases de datos relacionales y MySQL, te recomiendo explorar los siguientes recursos:

Documentación Oficial de MySQL: Siempre es la fuente más precisa y actualizada.
Libros Clásicos de SQL y Bases de Datos:
- SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL por John L. Viescas y Michael J. Hernandez: Un excelente recurso práctico con muchos ejemplos.
- An Introduction to Database Systems por C.J. Date: Un texto fundamental si buscas una comprensión teórica profunda del modelo relacional.
Plataformas de Aprendizaje Online:
- Khan Academy: Ofrece un curso gratuito sobre SQL para los fundamentos.
- W3Schools SQL Tutorial: Ideal para referencias rápidas y ejemplos claros de sintaxis básica.
- Coursera, Udemy, edX: Plataformas con cursos más estructurados y especializados sobre MySQL y bases de datos en general.

¡Este módulo te dará una base sólida para diseñar y manipular bases de datos relacionales de manera efectiva! Recuerda que una buena planificación lógica de tus bases de datos te permitirá una buena gestión!!!