Операции с несколькими таблицами в MySQL

Введение

В этом лабораторном занятии мы рассмотрим основы работы с несколькими таблицами в MySQL. Понимание того, как таблицы связаны друг с другом и как объединять данные из нескольких таблиц, является важным аспектом при создании реальных приложений. Мы изучим отношения между таблицами, различные типы операций JOIN и узнаем, как сохранять целостность данных с использованием ограничений внешних ключей. К концу этого лабораторного занятия вы получите практический опыт по созданию связанных таблиц, выполнению запросов к данным в разных таблицах и управлению отношениями между таблицами.

Понимание отношений между таблицами

На этом этапе мы рассмотрим концепцию отношений между таблицами и изучим, как наши таблицы связаны друг с другом. Понимание отношений между таблицами является фундаментальным аспектом работы с реляционными базами данных.

Сначала подключимся к MySQL:

sudo mysql -u root

После подключения выберем нашу базу данных:

USE bookstore;

Проверим структуру наших таблиц и их отношения:

SHOW CREATE TABLE books;

Вы увидите вывод, который включает ограничения внешних ключей:

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

Разберём отношения в нашей базе данных:

1. Каждая книга имеет одного автора (отношение многие-к-одному)

   • Поле author_id в таблице books ссылается на author_id в таблице authors. Это означает, что значение author_id в таблице books должно существовать как author_id в таблице authors.
   • Несколько книг могут иметь одного и того же автора.

2. Каждая книга имеет одного издателя (отношение многие-к-одному)

   • Поле publisher_id в таблице books ссылается на publisher_id в таблице publishers.
   • Несколько книг могут иметь одного и того же издателя.

Чтобы увидеть эти отношения в действии, выполним простой запрос, чтобы узнать, сколько книг написал каждый автор:

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

Вывод будет выглядеть так:

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

Это показывает, что автор с author_id 1 написал 2 книги, а авторы 2, 3 и 4 написали по одной книге. Это наглядно демонстрирует отношение многие-к-одному: несколько записей о книгах могут ссылаться на одну запись об авторе.

Базовые операции INNER JOIN

На этом этапе мы узнаем о INNER JOIN — важном типе операции объединения таблиц. INNER JOIN возвращает только те строки, для которых есть совпадение в обеих объединяемых таблицах. Можно представить это как диаграмму Венна, где INNER JOIN представляет собой пересечение двух таблиц. Если запись в одной таблице не имеет соответствующего совпадения в другой, она исключается из результата.

Начнем с простого INNER JOIN, чтобы получить названия книг вместе с именами их авторов:

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

Этот запрос:

1. Начинается с таблицы books (FROM books).
2. Объединяет ее с таблицей authors (INNER JOIN authors).
3. Указывает условие объединения с помощью ON books.author_id = authors.author_id. Таким образом, база данных понимает, какие строки из таблицы books соответствуют каким строкам из таблицы authors: она ищет записи, в которых значения author_id совпадают в обеих таблицах.
4. Выбирает столбцы, которые мы хотим увидеть: books.title, authors.first_name и authors.last_name.

Вы должны увидеть вывод, похожий на следующий:

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

В операторе SELECT вы видите, что мы используем books.title, authors.first_name и authors.last_name. Это явно сообщает MySQL, из какой таблицы взят каждый столбец. Если имена столбцов уникальны в рамках всех таблиц, можно опустить префикс таблицы (например, использовать просто title). Однако хорошей практикой является всегда указывать префикс таблицы, чтобы избежать неоднозначности, особенно при использовании нескольких объединений.

Мы также можем выполнить объединение с таблицей 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;

В этом случае publishers.name as publisher_name означает, что мы выбираем столбец name из таблицы publishers и переименовываем его в publisher_name в выводе. Это делает более понятным, что представляет собой этот столбец.

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

Операции LEFT JOIN

На этом этапе мы рассмотрим операции LEFT JOIN. LEFT JOIN (иногда называемый LEFT OUTER JOIN) возвращает все записи из "левой" таблицы (первой таблицы, упомянутой в предложении FROM) и соответствующие записи из "правой" таблицы. Основное отличие от INNER JOIN заключается в том, что даже если в правой таблице нет совпадений, записи из левой таблицы все равно включаются в результат, при этом в местах, где нет совпадений в правой таблице, будут использоваться значения NULL.

Добавим автора, который еще не опубликовал ни одной книги:

INSERT INTO authors (first_name, last_name, email)
VALUES ('David', 'Clark', '[email protected]');

Теперь используем LEFT JOIN, чтобы увидеть всех авторов, включая тех, кто еще не опубликовал книги:

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;

Вы должны увидеть вывод, похожий на следующий:

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

Обратите внимание, что Дэвид Кларк появляется в результатах с количеством книг, равным 0, даже несмотря на то, что он еще не опубликовал ни одной книги. Это происходит потому, что LEFT JOIN включает все строки из таблицы authors, и для Дэвида в таблице books нет соответствующих книг, поэтому COUNT(books.book_id) дает 0, а не NULL. Это наглядно демонстрирует важное отличие: LEFT JOIN гарантирует, что все строки из левой таблицы будут присутствовать в результате, в то время как INNER JOIN включает только совпадающие строки в обеих таблицах. Если есть книга, то счетчик будет целым числом, в противном случае он будет равен 0 из-за функции COUNT().

Объединение нескольких таблиц

На этом этапе мы узнаем, как объединять более двух таблиц. Это часто требуется, когда необходимо объединить данные из нескольких связанных таблиц, чтобы получить более полное представление о данных.

Создадим запрос, который объединяет информацию из всех трех таблиц:

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;

Разберём, что делает этот запрос:

1. FROM books b: Это указывает, что мы начинаем с таблицы books и даем ей псевдоним b. Использование псевдонимов (b, a, p) - хорошая практика, которая делает запрос короче и проще для чтения.
2. INNER JOIN authors a ON b.author_id = a.author_id: Это объединяет таблицу books с таблицей authors на основе author_id. Строки будут возвращены только в том случае, если в обеих таблицах есть совпадающее значение author_id. a - это псевдоним для таблицы authors.
3. INNER JOIN publishers p ON b.publisher_id = p.publisher_id: Это объединяет результат предыдущего объединения с таблицей publishers на основе publisher_id. Строки возвращаются только в том случае, если в обеих таблицах есть совпадение по publisher_id. p - это псевдоним для таблицы 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: Мы выбираем название книги из таблицы books, объединяем имя и фамилию автора в столбец author_name с помощью функции CONCAT, используем столбец с именем издателя и переименовываем его в publisher_name, а также год публикации и цену из соответствующих таблиц.
5. ORDER BY b.title: Это сортирует вывод по названию книги в порядке возрастания.

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

Этот запрос показывает, как можно собрать данные из разных таблиц, чтобы получить более полную картину. Каждая запись о книге связана с информацией об авторе и издателе.

Попробуем другой пример, который показывает книги и их авторов, включая авторов без книг, а также информацию об издателе, если она доступна:

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;

В этом примере мы используем LEFT JOIN. Это гарантирует, что будут показаны все авторы, независимо от того, опубликовали они книгу или нет. В выводе для автора без книги будут отображены значения NULL для названия книги и имени издателя.

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

Работа с ограничениями внешних ключей

На этом последнем этапе мы рассмотрим, как ограничения внешних ключей помогают поддерживать целостность данных между связанными таблицами. Ограничения внешних ключей гарантируют, что отношения между таблицами остаются действительными, предотвращая операции, которые могут привести к созданию "сирот" (orphaned records) или несогласованным данным.

Попробуем понять, как работают ограничения внешних ключей на примерах:

Сначала попробуем добавить книгу с недействительным author_id:

-- 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);

Вы увидите сообщение об ошибке, потому что author_id 999 не существует в таблице 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`))

Это сообщение об ошибке показывает, что ограничение внешнего ключа для столбца author_id в таблице books не позволяет добавить книгу, которая ссылается на author_id, не существующий в таблице authors.

Аналогично, мы не можем удалить автора, который опубликовал книги, не обработав сначала его книги:

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

Попытка удалить автора с author_id = 1 напрямую приводит к ошибке внешнего ключа, потому что есть книги, которые ссылаются на этот author_id. Это способ MySQL гарантировать, что ваши данные остаются согласованными.

Чтобы безопасно удалить автора и его книги, нам нужно либо:

1. Удалить сначала книги, а затем автора. Это гарантирует, что у нас не будет "сирот" (orphaned records).
2. Использовать CASCADE DELETE (мы рассмотрим это в продвинутых лабораторных занятиях).

Посмотрим, как правильно удалить книгу и ее автора, удалив сначала книгу:

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

Удаяв первую книгу (или книги), вы удаляете ссылки внешнего ключа, что позволяет удалить автора без нарушения ограничений.

Итог

В этом лабораторном занятии мы рассмотрели основные аспекты работы с несколькими таблицами в MySQL:

1. Понимание отношений между таблицами и их реализации с использованием первичных и внешних ключей.
2. Использование INNER JOIN для объединения совпадающих записей из нескольких таблиц, а также показано, как выбирать соответствующие столбцы с использованием префиксов и псевдонимов.
3. Использование LEFT JOIN для включения всех записей из одной таблицы, включая те, которые не имеют совпадающих записей в другой, и понимание, как интерпретировать значения NULL.
4. Объединение данных из нескольких таблиц с использованием множественных объединений, показано, как получить данные из трех таблиц одновременно.
5. Работа с ограничениями внешних ключей для поддержания целостности данных, предотвращения создания "сирот" (orphaned records) и демонстрация того, как MySQL управляет отношениями между таблицами.

Эти навыки являются основой для эффективной работы с реляционными базами данных. Понимание того, как объединять и связывать данные из нескольких таблиц, является важным для создания надежных базовых приложений.