Operaciones de múltiples tablas en MySQL

Introducción

En este laboratorio, exploraremos los fundamentos del trabajo con múltiples tablas en MySQL. Comprender cómo se relacionan las tablas entre sí y cómo combinar datos de múltiples tablas es crucial para desarrollar aplicaciones del mundo real. Aprenderemos sobre las relaciones entre tablas, los diferentes tipos de operaciones JOIN y cómo mantener la integridad de los datos utilizando restricciones de clave externa (foreign key constraints). Al final de este laboratorio, tendrás experiencia práctica en la creación de tablas relacionadas, la consulta de datos entre tablas y la gestión de relaciones entre tablas.

Comprender las relaciones entre tablas

En este paso, exploraremos el concepto de relaciones entre tablas y examinaremos cómo están conectadas nuestras tablas. Comprender las relaciones entre tablas es fundamental para trabajar con bases de datos relacionales.

Primero, conectémonos a MySQL:

sudo mysql -u root

Una vez conectados, seleccionemos nuestra base de datos:

USE bookstore;

Examinemos la estructura de nuestras tablas y sus relaciones:

SHOW CREATE TABLE books;

Verás una salida que incluye las restricciones de clave externa (foreign key constraints):

| books | CREATE TABLE `books` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  `publisher_id` int(11) DEFAULT NULL,
  `publication_year` int(11) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `author_id` (`author_id`),
  KEY `publisher_id` (`publisher_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`),
  CONSTRAINT `books_ibfk_2` FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`publisher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

Desglosemos las relaciones en nuestra base de datos:

1. Cada libro tiene un autor (relación de muchos a uno)

   • El author_id en la tabla books hace referencia al author_id en la tabla authors. Esto significa que el author_id en la tabla books debe existir como un author_id en la tabla authors.
   • Varios libros pueden tener el mismo autor.

2. Cada libro tiene una editorial (relación de muchos a uno)

   • El publisher_id en la tabla books hace referencia al publisher_id en la tabla publishers.
   • Varios libros pueden tener la misma editorial.

Para ver estas relaciones en acción, probemos una consulta simple para ver cuántos libros ha escrito cada autor:

SELECT author_id, COUNT(*) as book_count
FROM books
GROUP BY author_id;

La salida se verá así:

+-----------+------------+
| author_id | book_count |
+-----------+------------+
|         1 |          2 |
|         2 |          1 |
|         3 |          1 |
|         4 |          1 |
+-----------+------------+

Esto nos dice que el autor con author_id 1 tiene 2 libros, y los autores 2, 3 y 4 cada uno tiene 1 libro. Esto ilustra la relación de muchos a uno: múltiples registros de libros pueden apuntar a un solo registro de autor.

Operaciones básicas de INNER JOIN

En este paso, aprenderemos sobre INNER JOIN, un tipo crucial de operación de unión (join). Un INNER JOIN devuelve solo las filas donde hay una coincidencia en ambas tablas que se están uniendo. Piénsalo como un diagrama de Venn donde el INNER JOIN te da la intersección de las dos tablas. Si un registro en una tabla no tiene una coincidencia correspondiente en la otra, se excluye del resultado.

Comencemos con un INNER JOIN simple para obtener los títulos de los libros junto con los nombres de sus autores:

SELECT
    books.title,
    authors.first_name,
    authors.last_name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

Esta consulta:

1. Comienza con la tabla books (FROM books).
2. La une con la tabla authors (INNER JOIN authors).
3. Especifica la condición de unión con ON books.author_id = authors.author_id. Así es como la base de datos sabe qué filas de books coinciden con qué filas de authors: busca registros donde los valores de author_id sean iguales en ambas tablas.
4. Selecciona las columnas que queremos ver: books.title, authors.first_name y authors.last_name.

Deberías ver una salida como esta:

+----------------------------+------------+-----------+
| title                      | first_name | last_name |
+----------------------------+------------+-----------+
| The MySQL Guide            | John       | Smith     |
| Data Design Patterns       | Emma       | Wilson    |
| Database Fundamentals      | Michael    | Brown     |
| SQL for Beginners          | Sarah      | Johnson   |
| Advanced Database Concepts | John       | Smith     |
+----------------------------+------------+-----------+

En la declaración SELECT, vemos que estamos usando books.title, authors.first_name y authors.last_name. Esto le dice explícitamente a MySQL de qué tabla proviene cada columna. Si los nombres de las columnas son únicos en todas las tablas, se puede omitir el prefijo de la tabla (por ejemplo, simplemente usar title). Sin embargo, es una buena práctica siempre especificar el prefijo de la tabla para evitar ambigüedades, especialmente con múltiples uniones.

También podemos unir con la tabla publishers:

SELECT
    books.title,
    publishers.name as publisher_name,
    books.publication_year,
    books.price
FROM books
INNER JOIN publishers ON books.publisher_id = publishers.publisher_id;

En este caso, publishers.name as publisher_name significa que estamos seleccionando la columna name de la tabla publishers y renombrándola a publisher_name en la salida. Esto hace más claro lo que representa la columna.

+----------------------------+--------------------+------------------+-------+
| title                      | publisher_name     | publication_year | price |
+----------------------------+--------------------+------------------+-------+
| The MySQL Guide            | Tech Books Pro     |             2023 | 45.99 |
| Data Design Patterns       | Tech Books Pro     |             2022 | 39.99 |
| Database Fundamentals      | Database Press     |             2021 | 29.99 |
| SQL for Beginners          | Database Press     |             2023 | 34.99 |
| Advanced Database Concepts | Query Publications |             2023 | 54.99 |
+----------------------------+--------------------+------------------+-------+

Operaciones de LEFT JOIN

En este paso, exploraremos las operaciones de LEFT JOIN. Un LEFT JOIN (a veces llamado LEFT OUTER JOIN) devuelve todos los registros de la tabla "izquierda" (la primera tabla mencionada en la cláusula FROM) y los registros coincidentes de la tabla "derecha". La diferencia clave con INNER JOIN es que, incluso si no hay coincidencia en la tabla derecha, los registros de la tabla izquierda todavía se incluyen en el resultado, con valores NULL donde no hay coincidencia en la tabla derecha.

Agreguemos un autor que aún no ha publicado ningún libro:

INSERT INTO authors (first_name, last_name, email)
VALUES ('David', 'Clark', 'david.clark@email.com');

Ahora, usemos un LEFT JOIN para ver a todos los autores, incluyendo aquellos que no han publicado libros:

SELECT
    authors.first_name,
    authors.last_name,
    COUNT(books.book_id) as book_count
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id;

Deberías ver una salida como esta:

+------------+-----------+------------+
| first_name | last_name | book_count |
+------------+-----------+------------+
| John       | Smith     |          2 |
| Emma       | Wilson    |          1 |
| Michael    | Brown     |          1 |
| Sarah      | Johnson   |          1 |
| David      | Clark     |          0 |
+------------+-----------+------------+

Observa que David Clark aparece en los resultados con un recuento de libros de 0, aunque no ha publicado ningún libro. Esto sucede porque LEFT JOIN incluye todas las filas de la tabla authors y, para David, no hay libros coincidentes en la tabla books, por lo que COUNT(books.book_id) da como resultado 0, no NULL. Esto ilustra la diferencia crucial: LEFT JOIN garantiza que todas las filas de la tabla izquierda aparecerán en el resultado, mientras que INNER JOIN solo incluye las filas coincidentes en ambas tablas. Si hay un libro, el recuento será un número entero, de lo contrario, será 0 debido a COUNT().

Uniones de múltiples tablas

En este paso, aprenderemos cómo unir más de dos tablas. Esto es comúnmente necesario cuando se necesita combinar datos de varias tablas relacionadas para obtener una visión más holística de los datos.

Creemos una consulta que combine información de las tres tablas:

SELECT
    b.title,
    CONCAT(a.first_name, ' ', a.last_name) as author_name,
    p.name as publisher_name,
    b.publication_year,
    b.price
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY b.title;

Desglosemos lo que hace esta consulta:

1. FROM books b: Esto indica que comenzamos con la tabla books y le damos un alias b. Usar alias (b, a, p) es una buena práctica que hace la consulta más corta y fácil de leer.
2. INNER JOIN authors a ON b.author_id = a.author_id: Esto une la tabla books con la tabla authors basándose en el author_id. Solo se devolverán las filas si hay un author_id coincidente en ambas tablas. a es el alias para la tabla authors.
3. INNER JOIN publishers p ON b.publisher_id = p.publisher_id: Esto une el resultado de la unión anterior con la tabla publishers basándose en el publisher_id. Solo se devuelven las filas si hay una coincidencia en el publisher_id en ambas tablas. p es el alias para la tabla publishers.
4. SELECT b.title, CONCAT(a.first_name, ' ', a.last_name) as author_name, p.name as publisher_name, b.publication_year, b.price: Seleccionamos el título de la tabla books, combinamos el nombre y apellido del autor en una columna author_name usando CONCAT, usamos la columna del nombre de la editorial y la renombramos a publisher_name, el año de publicación y el precio de sus respectivas tablas.
5. ORDER BY b.title: Esto ordena la salida por el título del libro en orden ascendente.

+----------------------------+---------------+--------------------+------------------+-------+
| title                      | author_name   | publisher_name     | publication_year | price |
+----------------------------+---------------+--------------------+------------------+-------+
| Advanced Database Concepts | John Smith    | Query Publications |             2023 | 54.99 |
| Data Design Patterns       | Emma Wilson   | Tech Books Pro     |             2022 | 39.99 |
| Database Fundamentals      | Michael Brown | Database Press     |             2021 | 29.99 |
| SQL for Beginners          | Sarah Johnson | Database Press     |             2023 | 34.99 |
| The MySQL Guide            | John Smith    | Tech Books Pro     |             2023 | 45.99 |
+----------------------------+---------------+--------------------+------------------+-------+

Esta consulta muestra cómo se pueden reunir datos de diferentes tablas para obtener una imagen más completa. Cada entrada de libro está asociada con información del autor y de la editorial.

Probemos otro ejemplo que muestre libros y sus autores, incluyendo autores sin libros, junto con información de la editorial cuando esté disponible:

SELECT
    CONCAT(a.first_name, ' ', a.last_name) as author_name,
    b.title,
    p.name as publisher_name
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id
LEFT JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY author_name;

En este ejemplo, usamos un LEFT JOIN. Esto garantizará que se muestren todos los autores independientemente de si han publicado un libro. La salida para un autor sin libro mostrará NULL para el título y el nombre de la editorial.

+---------------+----------------------------+--------------------+
| author_name   | title                      | publisher_name     |
+---------------+----------------------------+--------------------+
| David Clark   | NULL                       | NULL               |
| Emma Wilson   | Data Design Patterns       | Tech Books Pro     |
| John Smith    | The MySQL Guide            | Tech Books Pro     |
| John Smith    | Advanced Database Concepts | Query Publications |
| Michael Brown | Database Fundamentals      | Database Press     |
| Sarah Johnson | SQL for Beginners          | Database Press     |
+---------------+----------------------------+--------------------+

Trabajando con restricciones de clave externa (Foreign Key Constraints)

En este último paso, exploraremos cómo las restricciones de clave externa (foreign key constraints) ayudan a mantener la integridad de los datos entre tablas relacionadas. Las restricciones de clave externa garantizan que las relaciones entre tablas sigan siendo válidas al evitar operaciones que crearían registros huérfanos o datos inconsistentes.

Intentemos entender cómo funcionan las restricciones de clave externa a través de algunos ejemplos:

Primero, intentemos agregar un libro con un author_id no válido:

-- Esto fallará debido a la restricción de clave externa
INSERT INTO books (title, author_id, publisher_id, publication_year, price)
VALUES ('Failed Book', 999, 1, 2023, 29.99);

Verás un mensaje de error porque el author_id 999 no existe en la tabla authors:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookstore`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`))

Este mensaje de error indica que la restricción de clave externa en la columna author_id de la tabla books nos impide agregar un libro que hace referencia a un author_id que no existe en la tabla authors.

Del mismo modo, no podemos eliminar un autor que ha publicado libros sin manejar primero sus libros:

-- Esto fallará debido a la restricción de clave externa
DELETE FROM authors WHERE author_id = 1;

Intentar eliminar directamente al autor con author_id = 1 resulta en un error de clave externa porque hay libros que hacen referencia a ese author_id. Esta es la forma en que MySQL asegura que tus datos permanezcan consistentes.

Para eliminar de forma segura un autor y sus libros, necesitamos hacer lo siguiente:

1. Eliminar primero los libros y luego al autor. Esto asegura que no tengamos registros huérfanos.
2. Usar CASCADE DELETE (lo exploraremos en laboratorios avanzados).

Veamos cómo eliminar adecuadamente un libro y su autor eliminando primero el libro:

-- Primero, eliminamos los libros de este autor
DELETE FROM books WHERE author_id = 1;

-- Ahora podemos eliminar al autor de forma segura
DELETE FROM authors WHERE author_id = 1;

Al eliminar primero el/los libro(s), estás eliminando las referencias de clave externa, lo que permite eliminar al autor sin violar las restricciones.

Resumen

En este laboratorio, hemos cubierto los aspectos esenciales del trabajo con múltiples tablas en MySQL:

1. Comprender las relaciones entre tablas y cómo se implementan utilizando claves primarias (primary keys) y claves externas (foreign keys).
2. Utilizar INNER JOIN para combinar registros coincidentes de múltiples tablas, mostrando cómo seleccionar columnas relevantes utilizando prefijos y alias.
3. Utilizar LEFT JOIN para incluir todos los registros de una tabla, incluyendo aquellos que no tienen registros coincidentes en la otra, y comprender cómo interpretar los valores NULL.
4. Combinar datos de múltiples tablas utilizando múltiples uniones (joins), ilustrando cómo obtener datos de tres tablas a la vez.
5. Trabajar con restricciones de clave externa (foreign key constraints) para mantener la integridad de los datos, evitar registros huérfanos y demostrar cómo MySQL gestiona las relaciones entre tablas.

Estas habilidades forman la base para trabajar de manera efectiva con bases de datos relacionales. Comprender cómo combinar y relacionar datos entre múltiples tablas es fundamental para construir aplicaciones de base de datos sólidas.