MySQL-Mehrtabellen-Operationen

Einführung

In diesem Lab werden wir die Grundlagen der Arbeit mit mehreren Tabellen in MySQL erkunden. Das Verständnis, wie Tabellen miteinander in Beziehung stehen und wie man Daten aus mehreren Tabellen kombiniert, ist für die Entwicklung von realen Anwendungen von entscheidender Bedeutung. Wir werden uns mit Tabellenbeziehungen, verschiedenen Arten von JOIN-Operationen und der Aufrechterhaltung der Datenintegrität mithilfe von Fremdschlüsselbedingungen befassen. Am Ende dieses Labs haben Sie praktische Erfahrungen in der Erstellung verwandter Tabellen, der Abfrage von Daten über Tabellen hinweg und der Verwaltung von Beziehungen zwischen Tabellen gesammelt.

Verständnis von Tabellenbeziehungen

In diesem Schritt werden wir das Konzept von Tabellenbeziehungen erkunden und untersuchen, wie unsere Tabellen miteinander verbunden sind. Das Verständnis von Tabellenbeziehungen ist grundlegend für die Arbeit mit relationalen Datenbanken.

Zunächst verbinden wir uns mit MySQL:

sudo mysql -u root

Nachdem die Verbindung hergestellt ist, wählen wir unsere Datenbank aus:

USE bookstore;

Lassen Sie uns die Struktur unserer Tabellen und ihre Beziehungen untersuchen:

SHOW CREATE TABLE books;

Sie werden eine Ausgabe sehen, die die Fremdschlüsselbedingungen enthält:

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

Lassen Sie uns die Beziehungen in unserer Datenbank analysieren:

1. Jedes Buch hat einen Autor (Viele-zu-eins-Beziehung)

   • Die author_id in der books-Tabelle verweist auf die author_id in der authors-Tabelle. Dies bedeutet, dass die author_id in der books-Tabelle als author_id in der authors-Tabelle existieren muss.
   • Mehrere Bücher können denselben Autor haben.

2. Jedes Buch hat einen Verlag (Viele-zu-eins-Beziehung)

   • Die publisher_id in der books-Tabelle verweist auf die publisher_id in der publishers-Tabelle.
   • Mehrere Bücher können denselben Verlag haben.

Um diese Beziehungen in Aktion zu sehen, versuchen wir eine einfache Abfrage, um zu sehen, wie viele Bücher jeder Autor geschrieben hat:

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

Die Ausgabe wird wie folgt aussehen:

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

Dies zeigt uns, dass der Autor mit der author_id 1 zwei Bücher hat und die Autoren 2, 3 und 4 jeweils ein Buch haben. Dies veranschaulicht die Viele-zu-eins-Beziehung: Mehrere Buchdatensätze können auf einen einzigen Autoren-Datensatz verweisen.

Grundlegende INNER JOIN-Operationen

In diesem Schritt werden wir uns mit INNER JOIN befassen, einer wichtigen Art von Join-Operation. Ein INNER JOIN gibt nur die Zeilen zurück, bei denen in beiden verbundenen Tabellen eine Übereinstimmung besteht. Stellen Sie sich dies wie ein Venn-Diagramm vor, bei dem der INNER JOIN den Schnittpunkt der beiden Tabellen liefert. Wenn ein Datensatz in einer Tabelle keine entsprechende Übereinstimmung in der anderen Tabelle hat, wird er aus dem Ergebnis ausgeschlossen.

Beginnen wir mit einem einfachen INNER JOIN, um die Titel der Bücher zusammen mit den Namen ihrer Autoren zu erhalten:

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

Diese Abfrage:

1. Beginnt mit der books-Tabelle (FROM books).
2. Verbindet sie mit der authors-Tabelle (INNER JOIN authors).
3. Gibt die Join-Bedingung mit ON books.author_id = authors.author_id an. So weiß die Datenbank, welche Zeilen aus books mit welchen Zeilen aus authors übereinstimmen: Sie sucht nach Datensätzen, bei denen die author_id-Werte in beiden Tabellen gleich sind.
4. Wählt die Spalten aus, die wir sehen möchten: books.title, authors.first_name und authors.last_name.

Sie sollten eine Ausgabe wie die folgende sehen:

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

In der SELECT-Anweisung sehen Sie, dass wir books.title, authors.first_name und authors.last_name verwenden. Dies sagt MySQL explizit, aus welcher Tabelle jede Spalte stammt. Wenn die Spaltennamen in allen Tabellen eindeutig sind, können Sie das Tabellenpräfix weglassen (z. B. einfach title verwenden). Es ist jedoch eine gute Praxis, das Tabellenpräfix immer anzugeben, um Mehrdeutigkeiten zu vermeiden, insbesondere bei mehreren Joins.

Wir können auch mit der publishers-Tabelle verbinden:

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;

In diesem Fall bedeutet publishers.name as publisher_name, dass wir die name-Spalte aus der publishers-Tabelle auswählen und sie in der Ausgabe in publisher_name umbenennen. Dies macht klarer, was die Spalte darstellt.

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

In diesem Schritt werden wir uns mit LEFT JOIN-Operationen befassen. Ein LEFT JOIN (manchmal auch LEFT OUTER JOIN genannt) gibt alle Datensätze aus der "linken" Tabelle (der ersten Tabelle, die in der FROM-Klausel erwähnt wird) und die übereinstimmenden Datensätze aus der "rechten" Tabelle zurück. Der wichtigste Unterschied zum INNER JOIN besteht darin, dass auch dann, wenn es in der rechten Tabelle keine Übereinstimmung gibt, die Datensätze aus der linken Tabelle immer noch im Ergebnis enthalten sind, wobei für die fehlenden Übereinstimmungen in der rechten Tabelle NULL-Werte angezeigt werden.

Fügen wir einen Autor hinzu, der noch keine Bücher veröffentlicht hat:

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

Nun verwenden wir einen LEFT JOIN, um alle Autoren anzuzeigen, einschließlich derer, die noch keine Bücher veröffentlicht haben:

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;

Sie sollten eine Ausgabe wie die folgende sehen:

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

Beachten Sie, dass David Clark in den Ergebnissen mit einer Buchzahl von 0 erscheint, obwohl er noch keine Bücher veröffentlicht hat. Dies geschieht, weil LEFT JOIN alle Zeilen aus der authors-Tabelle einschließt und für David keine übereinstimmenden Bücher in der books-Tabelle vorhanden sind. Daher ergibt COUNT(books.book_id) 0 und nicht NULL. Dies veranschaulicht den entscheidenden Unterschied: LEFT JOIN garantiert, dass alle Zeilen aus der linken Tabelle im Ergebnis erscheinen, während INNER JOIN nur die übereinstimmenden Zeilen in beiden Tabellen einschließt. Wenn es ein Buch gibt, wird die Anzahl eine Ganzzahl sein, andernfalls wird sie aufgrund von COUNT() 0 sein.

Verknüpfung mehrerer Tabellen

In diesem Schritt lernen wir, wie man mehr als zwei Tabellen miteinander verknüpft. Dies ist häufig erforderlich, wenn Sie Daten aus mehreren verwandten Tabellen kombinieren müssen, um eine umfassendere Sicht auf Ihre Daten zu erhalten.

Erstellen wir eine Abfrage, die Informationen aus allen drei Tabellen kombiniert:

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;

Lassen Sie uns analysieren, was diese Abfrage tut:

1. FROM books b: Dies zeigt an, dass wir mit der books-Tabelle beginnen und ihr einen Alias b geben. Die Verwendung von Aliasen (b, a, p) ist eine gute Praxis, die die Abfrage kürzer und leichter lesbar macht.
2. INNER JOIN authors a ON b.author_id = a.author_id: Diese Zeile verknüpft die books-Tabelle mit der authors-Tabelle anhand der author_id. Es werden nur Zeilen zurückgegeben, wenn es in beiden Tabellen eine übereinstimmende author_id gibt. a ist der Alias für die authors-Tabelle.
3. INNER JOIN publishers p ON b.publisher_id = p.publisher_id: Diese Zeile verknüpft das Ergebnis der vorherigen Verknüpfung mit der publishers-Tabelle anhand der publisher_id. Es werden nur Zeilen zurückgegeben, wenn es in beiden Tabellen eine Übereinstimmung bei der publisher_id gibt. p ist der Alias für die publishers-Tabelle.
4. SELECT b.title, CONCAT(a.first_name, ' ', a.last_name) as author_name, p.name as publisher_name, b.publication_year, b.price: Wir wählen den Titel aus der books-Tabelle aus, kombinieren den Vornamen und den Nachnamen des Autors zu einer Spalte author_name mit CONCAT, verwenden die Spalte mit dem Namen des Verlages und benennen sie in publisher_name um, sowie das Erscheinungsjahr und den Preis aus ihren jeweiligen Tabellen.
5. ORDER BY b.title: Diese Zeile sortiert die Ausgabe nach dem Buchtitel in aufsteigender Reihenfolge.

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

Diese Abfrage zeigt, wie Sie Daten aus verschiedenen Tabellen zusammenbringen können, um ein vollständigeres Bild zu erhalten. Jeder Buch-Eintrag ist mit Informationen über den Autor und den Verlag verknüpft.

Versuchen wir ein weiteres Beispiel, das Bücher und ihre Autoren anzeigt, einschließlich Autoren ohne Bücher, sowie Verlagsinformationen, wenn verfügbar:

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;

In diesem Beispiel verwenden wir einen LEFT JOIN. Dadurch wird sichergestellt, dass alle Autoren angezeigt werden, unabhängig davon, ob sie ein Buch veröffentlicht haben. Die Ausgabe für einen Autor ohne Buch wird NULL für Titel und Verlagsname anzeigen.

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

Arbeiten mit Fremdschlüsselbedingungen

In diesem letzten Schritt werden wir untersuchen, wie Fremdschlüsselbedingungen dazu beitragen, die Datenintegrität zwischen verwandten Tabellen aufrechtzuerhalten. Fremdschlüsselbedingungen stellen sicher, dass die Beziehungen zwischen Tabellen gültig bleiben, indem sie Operationen verhindern, die isolierte Datensätze (orphaned records) oder inkonsistente Daten erzeugen würden.

Versuchen wir, zu verstehen, wie Fremdschlüsselbedingungen funktionieren, anhand einiger Beispiele:

Zunächst versuchen wir, ein Buch mit einer ungültigen author_id hinzuzufügen:

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

Sie werden eine Fehlermeldung sehen, weil author_id 999 in der authors-Tabelle nicht existiert:

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

Diese Fehlermeldung zeigt an, dass die Fremdschlüsselbedingung für die Spalte author_id in der books-Tabelle es verhindert, ein Buch hinzuzufügen, das auf eine author_id verweist, die in der authors-Tabelle nicht existiert.

Ebenso können wir keinen Autor löschen, der Bücher veröffentlicht hat, ohne zunächst seine Bücher zu behandeln:

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

Wenn Sie versuchen, den Autor mit author_id = 1 direkt zu löschen, tritt ein Fremdschlüsselfehler auf, weil es Bücher gibt, die auf diese author_id verweisen. Dies ist MySQLs Weg, sicherzustellen, dass Ihre Daten konsistent bleiben.

Um einen Autor und seine Bücher sicher zu löschen, müssen wir entweder:

1. Zunächst die Bücher löschen und dann den Autor. Dies stellt sicher, dass wir keine isolierten Datensätze haben.
2. CASCADE DELETE verwenden (das wir in fortgeschrittenen Labs untersuchen werden).

Sehen wir uns an, wie man ein Buch und seinen Autor richtig entfernt, indem man zunächst das Buch löscht:

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

Indem Sie zunächst das/die Buch(er) löschen, entfernen Sie die Fremdschlüsselreferenzen, sodass der Autor ohne Verletzung der Bedingungen gelöscht werden kann.

Zusammenfassung

In diesem Lab haben wir die wesentlichen Aspekte der Arbeit mit mehreren Tabellen in MySQL behandelt:

1. Das Verständnis von Tabellenbeziehungen und wie sie mithilfe von Primär- und Fremdschlüsseln implementiert werden.
2. Die Verwendung von INNER JOIN, um übereinstimmende Datensätze aus mehreren Tabellen zu kombinieren, und das Zeigen, wie man relevante Spalten mit Präfixen und Aliasen auswählt.
3. Die Verwendung von LEFT JOIN, um alle Datensätze aus einer Tabelle einzuschließen, einschließlich derer, die keine übereinstimmenden Datensätze in der anderen Tabelle haben, und das Verständnis, wie man NULL-Werte interpretiert.
4. Das Kombinieren von Daten aus mehreren Tabellen mithilfe mehrerer Verknüpfungen und das Veranschaulichen, wie man Daten aus drei Tabellen auf einmal abruft.
5. Die Arbeit mit Fremdschlüsselbedingungen, um die Datenintegrität aufrechtzuerhalten, isolierte Datensätze zu vermeiden und das Zeigen, wie MySQL die Beziehungen zwischen Tabellen verwaltet.

Diese Fähigkeiten bilden die Grundlage für die effektive Arbeit mit relationalen Datenbanken. Das Verständnis, wie man Daten aus mehreren Tabellen kombiniert und in Beziehung setzt, ist entscheidend für das Erstellen robuster Datenbankanwendungen.