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 人の著者を持つ（多対 1 の関係）
- booksテーブルのauthor_idはauthorsテーブルのauthor_idを参照しています。これは、booksテーブルのauthor_idがauthorsテーブルのauthor_idとして存在しなければならないことを意味します。
- 複数の本は同じ著者を持つことができます。
各本は 1 つの出版社を持つ（多対 1 の関係）
- booksテーブルのpublisher_idはpublishersテーブルのpublisher_idを参照しています。
- 複数の本は同じ出版社を持つことができます。

これらの関係を実際に見てみましょう。各著者が何冊の本を書いたかを見るための簡単なクエリを試してみます。

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 はそれぞれ 1 冊の本を持つことを示しています。これは多対 1 の関係を示しており、複数の本レコードが 1 つの著者レコードを指すことができます。

基本的な内部結合（INNER JOIN）操作

このステップでは、重要な結合操作の 1 つである「内部結合（INNER JOIN）」について学びます。内部結合は、結合する 2 つのテーブルに一致する行のみを返します。2 つの集合の共通部分を表すベン図のように考えてください。一方のテーブルのレコードがもう一方に対応する一致するレコードを持っていない場合、そのレコードは結果から除外されます。

まずは、書籍のタイトルとその著者の名前を取得するための簡単な内部結合から始めましょう。

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

このクエリは、以下のように動作します。

booksテーブルから始まります（FROM books）。
それをauthorsテーブルと結合します（INNER JOIN authors）。
結合条件をON books.author_id = authors.author_idで指定します。これが、データベースがbooksのどの行がauthorsのどの行と一致するかを知る方法です。両方のテーブルでauthor_idの値が等しいレコードを探します。
表示したい列を選択します：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は、publishersテーブルのname列を選択し、出力で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 OUTER JOIN）と呼ばれます）は、「左」のテーブル（FROM 句で最初に記載されるテーブル）のすべてのレコードと、「右」のテーブルの一致するレコードを返します。内部結合（INNER JOIN）との主な違いは、右のテーブルに一致するレコードがなくても、左のテーブルのレコードは結果に含まれ、右のテーブルに一致するレコードがない場合は NULL 値が入ります。

まだ本を出版していない著者を追加しましょう。

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

次に、左外部結合を使用して、本を出版していない著者も含めたすべての著者を表示します。

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

まだ本を出版していない David Clark が、書籍数が 0 で結果に表示されることに注意してください。これは、左外部結合が authors テーブルのすべての行を含み、David には books テーブルに一致する本がないため、COUNT(books.book_id) の結果が 0 になり、NULL にはならないからです。これは重要な違いを示しています。左外部結合は左のテーブルのすべての行が結果に表示されることを保証しますが、内部結合は両方のテーブルの一致する行のみを含みます。本がある場合、カウントは整数になり、そうでない場合は COUNT() のために 0 になります。

複数テーブルの結合

このステップでは、2 つ以上のテーブルを結合する方法を学びます。これは、複数の関連するテーブルからのデータを結合して、データの全体像を得る必要がある場合に一般的に必要となります。

すべての 3 つのテーブルからの情報を結合するクエリを作成しましょう。

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;

このクエリが何を行うか解説しましょう。

**FROM books b**：これは、booksテーブルから始めることを示し、それにエイリアスbを与えています。エイリアス（b、a、p）を使用するのは、クエリを短くして読みやすくする良い慣例です。
**INNER JOIN authors a ON b.author_id = a.author_id**：これは、author_idに基づいてbooksテーブルとauthorsテーブルを結合します。両方のテーブルに一致するauthor_idがある場合のみ、行が返されます。aはauthorsテーブルのエイリアスです。
**INNER JOIN publishers p ON b.publisher_id = p.publisher_id**：これは、前の結合の結果をpublisher_idに基づいてpublishersテーブルと結合します。両方のテーブルでpublisher_idが一致する場合のみ、行が返されます。pはpublishersテーブルのエイリアスです。
**SELECT b.title, CONCAT(a.first_name,'', a.last_name) as author_name, p.name as publisher_name, b.publication_year, b.price**：booksテーブルからタイトルを選択し、CONCATを使用して著者の名前と姓をauthor_name列に結合し、出版社の名前の列を選択してpublisher_nameにリネームし、それぞれのテーブルから出版年と価格を選択します。
**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     |
+---------------+----------------------------+--------------------+

外部キー制約の操作

この最後のステップでは、外部キー制約が関連するテーブル間のデータの整合性をどのように維持するかを調べます。外部キー制約は、孤立したレコードや不整合なデータを作成する操作を防ぐことで、テーブル間の関係が有効なままであることを保証します。

外部キー制約がどのように機能するかをいくつかの例を通して理解してみましょう。

まず、無効なauthor_idを持つ本を追加しようとしてみましょう。

-- これは外部キー制約のために失敗します
INSERT INTO books (title, author_id, publisher_id, publication_year, price)
VALUES ('Failed Book', 999, 1, 2023, 29.99);

authorsテーブルにauthor_id 999 が存在しないため、エラーメッセージが表示されます。

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

このエラーメッセージは、booksテーブルのauthor_id列に対する外部キー制約が、authorsテーブルに存在しないauthor_idを参照する本を追加しようとしていることを防いでいることを示しています。

同様に、まずその著者の本を処理しないで、本を出版している著者を削除することはできません。

-- これは外部キー制約のために失敗します
DELETE FROM authors WHERE author_id = 1;

author_id = 1の著者を直接削除しようとすると、外部キーエラーが発生します。なぜなら、そのauthor_idを参照する本があるからです。これは、MySQL がデータの整合性を維持するための方法です。

著者とその本を安全に削除するには、次のいずれかの方法が必要です。

まず本を削除してから、著者を削除する。これにより、孤立したレコードがないことが保証されます。
CASCADE DELETEを使用する（高度な実験で調べます）。

まず本を削除することで、本とその著者を適切に削除する方法を見てみましょう。

-- まず、この著者による本を削除する
DELETE FROM books WHERE author_id = 1;

-- 今では、安全に著者を削除できる
DELETE FROM authors WHERE author_id = 1;

まず本を削除することで、外部キー参照を削除し、制約を違反することなく著者を削除できます。

まとめ

この実験では、MySQL における複数のテーブルの操作の重要な側面を学びました。

テーブル間の関係と、主キーと外部キーを使用してそれらがどのように実装されるかを理解すること。
複数のテーブルからの一致するレコードを結合するために内部結合（INNER JOIN）を使用し、接頭辞とエイリアスを使用して関連する列を選択する方法を示すこと。
一方のテーブルのすべてのレコード、他方に一致するレコードがないレコードも含めて、左外部結合（LEFT JOIN）を使用し、NULL 値をどのように解釈するかを理解すること。
複数の結合を使用して複数のテーブルからのデータを結合し、一度に 3 つのテーブルからデータを取得する方法を示すこと。
外部キー制約を使用してデータの整合性を維持し、孤立したレコードを防ぎ、MySQL がテーブル間の関係をどのように管理するかを示すこと。

これらのスキルは、効果的に関係型データベースを操作するための基礎を形成します。複数のテーブルにまたがるデータをどのように結合し、関連付けるかを理解することは、堅牢なデータベースアプリケーションを構築するために重要です。