Operações com Múltiplas Tabelas MySQL

Introdução

Neste laboratório, exploraremos os fundamentos de como trabalhar com múltiplas tabelas no MySQL. Compreender como as tabelas se relacionam entre si e como combinar dados de múltiplas tabelas é crucial para construir aplicações do mundo real. Aprenderemos sobre relacionamentos de tabelas, diferentes tipos de operações JOIN e como manter a integridade dos dados usando restrições de chave estrangeira (foreign key constraints). Ao final deste laboratório, você terá experiência prática na criação de tabelas relacionadas, na consulta de dados em várias tabelas e no gerenciamento de relacionamentos entre tabelas.

Entendendo os Relacionamentos entre Tabelas

Nesta etapa, exploraremos o conceito de relacionamentos de tabelas e examinaremos como nossas tabelas estão conectadas. Compreender os relacionamentos de tabelas é fundamental para trabalhar com bancos de dados relacionais.

Primeiro, vamos conectar ao MySQL:

sudo mysql -u root

Uma vez conectado, selecione nosso banco de dados:

USE bookstore;

Vamos examinar a estrutura de nossas tabelas e seus relacionamentos:

SHOW CREATE TABLE books;

Você verá uma saída que inclui as restrições de chave estrangeira (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 |

Vamos detalhar os relacionamentos em nosso banco de dados:

1. Cada livro tem um autor (relacionamento muitos-para-um)

   • O author_id na tabela books referencia author_id na tabela authors. Isso significa que o author_id na tabela books deve existir como um author_id na tabela authors.
   • Múltiplos livros podem ter o mesmo autor.

2. Cada livro tem uma editora (relacionamento muitos-para-um)

   • O publisher_id na tabela books referencia publisher_id na tabela publishers.
   • Múltiplos livros podem ter a mesma editora.

Para ver esses relacionamentos em ação, vamos tentar uma consulta simples para ver quantos livros cada autor escreveu:

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

A saída será semelhante a esta:

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

Isso nos diz que o autor com author_id 1 tem 2 livros, e os autores 2, 3 e 4 têm 1 livro cada. Isso ilustra o relacionamento muitos-para-um: múltiplos registros de livros podem apontar para um único registro de autor.

Operações Básicas de INNER JOIN

Nesta etapa, aprenderemos sobre INNER JOIN, um tipo crucial de operação de junção. Um INNER JOIN retorna apenas as linhas onde há uma correspondência em ambas as tabelas que estão sendo unidas. Pense nisso como um diagrama de Venn, onde o INNER JOIN fornece a interseção das duas tabelas. Se um registro em uma tabela não tiver uma correspondência correspondente na outra, ele será excluído do resultado.

Vamos começar com um INNER JOIN simples para obter os títulos dos livros junto com os nomes de seus 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. Começa com a tabela books (FROM books).
2. Junta-se a ela com a tabela authors (INNER JOIN authors).
3. Especifica a condição de junção com ON books.author_id = authors.author_id. É assim que o banco de dados sabe quais linhas de books correspondem a quais linhas de authors: ele procura registros onde os valores de author_id são iguais em ambas as tabelas.
4. Seleciona as colunas que queremos ver: books.title, authors.first_name e authors.last_name.

Você deve ver uma saída como:

+----------------------------+------------+-----------+
| 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     |
+----------------------------+------------+-----------+

Na instrução SELECT, você vê que estamos usando books.title, authors.first_name e authors.last_name. Isso informa explicitamente ao MySQL de qual tabela cada coluna vem. Se os nomes das colunas forem exclusivos em todas as tabelas, você pode remover o prefixo da tabela (por exemplo, use apenas title). No entanto, é uma boa prática sempre especificar o prefixo da tabela para evitar ambiguidade, especialmente com várias junções.

Também podemos fazer a junção com a tabela 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;

Neste caso, publishers.name as publisher_name significa que estamos selecionando a coluna name da tabela publishers e renomeando-a para publisher_name na saída. Isso torna mais claro o que a coluna representa.

+----------------------------+--------------------+------------------+-------+
| 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 |
+----------------------------+--------------------+------------------+-------+

Operações de LEFT JOIN

Nesta etapa, exploraremos as operações de LEFT JOIN. Um LEFT JOIN (às vezes chamado de LEFT OUTER JOIN) retorna todos os registros da tabela "esquerda" (a primeira tabela mencionada na cláusula FROM) e os registros correspondentes da tabela "direita". A principal diferença em relação ao INNER JOIN é que, mesmo que não haja correspondência na tabela direita, os registros da tabela esquerda ainda são incluídos no resultado, com valores NULL onde não há correspondência na tabela direita.

Vamos adicionar um autor que ainda não publicou nenhum livro:

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

Agora, vamos usar um LEFT JOIN para ver todos os autores, incluindo aqueles que não publicaram livros:

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;

Você deve ver uma saída como:

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

Observe que David Clark aparece nos resultados com uma contagem de livros de 0, embora ele não tenha publicado nenhum livro. Isso acontece porque o LEFT JOIN inclui todas as linhas da tabela authors e, para David, não há livros correspondentes na tabela books, então COUNT(books.book_id) resulta em 0, não NULL. Isso ilustra a diferença crucial: LEFT JOIN garante que todas as linhas da tabela esquerda aparecerão no resultado, enquanto INNER JOIN inclui apenas as linhas correspondentes em ambas as tabelas. Se houver um livro, a contagem será um inteiro, caso contrário, será 0 por causa de COUNT().

Junções de Múltiplas Tabelas

Nesta etapa, aprenderemos como juntar mais de duas tabelas. Isso é comumente necessário quando você precisa combinar dados de várias tabelas relacionadas para obter uma visão mais holística de seus dados.

Vamos criar uma consulta que combine informações de todas as três tabelas:

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;

Vamos detalhar o que essa consulta faz:

1. FROM books b: Isso indica que estamos começando com a tabela books e estamos dando a ela um alias b. Usar aliases (b, a, p) é uma boa prática que torna a consulta mais curta e fácil de ler.
2. INNER JOIN authors a ON b.author_id = a.author_id: Isso junta a tabela books com a tabela authors com base no author_id. As linhas serão retornadas somente se houver um author_id correspondente em ambas as tabelas. a é um alias para a tabela authors.
3. INNER JOIN publishers p ON b.publisher_id = p.publisher_id: Isso junta o resultado da junção anterior com a tabela publishers com base em publisher_id. As linhas são retornadas somente se houver uma correspondência em publisher_id em ambas as tabelas. p é um alias para a tabela 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: Selecionamos o título da tabela books, combinamos o primeiro e o último nome do autor em uma coluna author_name usando CONCAT, usamos a coluna de nome do editor e a renomeamos para publisher_name, ano de publicação e preço de suas respectivas tabelas.
5. ORDER BY b.title: Isso classifica a saída pelo título do livro em ordem crescente.

+----------------------------+---------------+--------------------+------------------+-------+
| 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 mostra como você pode reunir dados de diferentes tabelas para obter uma imagem mais completa. Cada entrada de livro está associada a informações do autor e do editor.

Vamos tentar outro exemplo que mostra livros e seus autores, incluindo autores sem livros, junto com informações do editor quando disponíveis:

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;

Neste exemplo, usamos um LEFT JOIN. Isso garantirá que todos os autores sejam mostrados, independentemente de terem publicado um livro. A saída para um autor sem um livro mostrará NULL para o título e o nome do editor.

+---------------+----------------------------+--------------------+
| 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     |
+---------------+----------------------------+--------------------+

Trabalhando com Restrições de Chave Estrangeira

Nesta etapa final, exploraremos como as restrições de chave estrangeira ajudam a manter a integridade dos dados entre tabelas relacionadas. As restrições de chave estrangeira garantem que os relacionamentos entre as tabelas permaneçam válidos, impedindo operações que criariam registros órfãos ou dados inconsistentes.

Vamos tentar entender como as restrições de chave estrangeira funcionam por meio de alguns exemplos:

Primeiro, vamos tentar adicionar um livro com um author_id inválido:

-- This will fail due to foreign key constraint
INSERT INTO books (title, author_id, publisher_id, publication_year, price)
VALUES ('Failed Book', 999, 1, 2023, 29.99);

Você verá uma mensagem de erro porque author_id 999 não existe na tabela 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`))

Esta mensagem de erro indica que a restrição de chave estrangeira na coluna author_id na tabela books está nos impedindo de adicionar um livro que referencia um author_id que não existe na tabela authors.

Da mesma forma, não podemos excluir um autor que publicou livros sem primeiro lidar com seus livros:

-- This will fail due to foreign key constraint
DELETE FROM authors WHERE author_id = 1;

Tentar excluir o autor com author_id = 1 diretamente resulta em um erro de chave estrangeira porque há livros que referenciam esse author_id. Esta é a maneira do MySQL de garantir que seus dados permaneçam consistentes.

Para excluir com segurança um autor e seus livros, precisamos:

1. Excluir os livros primeiro, depois o autor. Isso garante que não tenhamos registros órfãos.
2. Usar CASCADE DELETE (que exploraremos em laboratórios avançados).

Vamos ver como remover corretamente um livro e seu autor excluindo o livro primeiro:

-- First, delete the books by this author
DELETE FROM books WHERE author_id = 1;

-- Now we can safely delete the author
DELETE FROM authors WHERE author_id = 1;

Ao excluir os livros primeiro, você está removendo as referências de chave estrangeira, permitindo que o autor seja excluído sem violar as restrições.

Resumo

Neste laboratório, cobrimos os aspectos essenciais de como trabalhar com múltiplas tabelas no MySQL:

1. Compreensão dos relacionamentos entre tabelas e como eles são implementados usando chaves primárias e estrangeiras.
2. Usando INNER JOIN para combinar registros correspondentes de várias tabelas, mostrando como selecionar colunas relevantes usando prefixos e aliases.
3. Usando LEFT JOIN para incluir todos os registros de uma tabela, incluindo aqueles que não possuem registros correspondentes na outra, e entendendo como interpretar valores NULL.
4. Combinando dados de várias tabelas usando múltiplas junções, ilustrando como obter dados de três tabelas de uma vez.
5. Trabalhando com restrições de chave estrangeira para manter a integridade dos dados, impedindo registros órfãos e demonstrando como o MySQL gerencia os relacionamentos entre as tabelas.

Essas habilidades formam a base para trabalhar com bancos de dados relacionais de forma eficaz. Entender como combinar e relacionar dados em várias tabelas é crucial para construir aplicações de banco de dados robustas.