SQL für Anfänger: Teil 3 - Datenbankbeziehungen

() translation by (you can also view the original English article)

Heute setzen wir unsere Reise in die Welt der SQL- und relationalen Datenbanksysteme fort. In diesem dritten Teil der Serie lernen wir, wie man mit mehreren Tabellen arbeitet, die Beziehungen zueinander haben. Zuerst werden wir einige Kernkonzepte durchgehen und dann mit JOIN-Abfragen in SQL arbeiten.

Sie können SQL-Datenbanken auch in Aktion sehen, indem Sie die SQL-Skripts, Apps und Add-Ons auf Envato Market überprüfen.

Aufholen

Einführung

Beim Erstellen einer Datenbank schreibt der gesunde Menschenverstand vor, dass für verschiedene Entitätstypen separate Tabellen verwendet werden. Einige Beispiele sind: Kunden, Bestellungen, Artikel, Nachrichten usw. Aber wir müssen auch Beziehungen zwischen diesen Tabellen haben. Beispielsweise geben Kunden Bestellungen auf und Bestellungen enthalten Artikel. Diese Beziehungen müssen in der Datenbank dargestellt werden. Außerdem müssen wir beim Abrufen von Daten mit SQL bestimmte Arten von JOIN-Abfragen verwenden, um das zu erhalten, was wir benötigen.

Es gibt verschiedene Arten von Datenbankbeziehungen. Heute werden wir Folgendes behandeln:

Eins-zu-eins-Beziehungen
Eins zu viele und viele zu eins Beziehungen
Viele zu viele Beziehungen
Selbstreferenzierende Beziehungen

Bei der Auswahl von Daten aus mehreren Tabellen mit Beziehungen verwenden wir die JOIN-Abfrage. Es gibt verschiedene Arten von JOINs, und wir werden Folgendes lernen:

Cross Joins
Natürliche Joins
Innere Joins
Links (außen) Joins
Rechts (außen) Joins

Wir werden auch etwas über die ON-Klausel und die USING-Klausel erfahren.

Eins-zu-eins-Beziehungen

Angenommen, Sie haben einen Tisch für Kunden:

Wir können die Kundenadressinformationen in eine separate Tabelle aufnehmen:

Jetzt haben wir eine Beziehung zwischen der Customers-Tabelle und der Addresses-Tabelle. Wenn jede Adresse nur einem Kunden gehören kann, lautet diese Beziehung "Eins zu Eins". Denken Sie daran, dass diese Art von Beziehung nicht sehr häufig ist. Unsere erste Tabelle, die die Adresse zusammen mit dem Kunden enthielt, hätte in den meisten Fällen gut funktionieren können.

Beachten Sie, dass jetzt in der Customers-Tabelle ein Feld mit dem Namen "address_id" vorhanden ist, das auf den übereinstimmenden Datensatz in der Address-Tabelle verweist. Dies wird als "Fremdschlüssel" bezeichnet und für alle Arten von Datenbankbeziehungen verwendet. Wir werden dieses Thema später in diesem Artikel behandeln.

Wir können die Beziehung zwischen dem Kunden und Adressdatensätzen folgendermaßen visualisieren:

Beachten Sie, dass das Bestehen einer Beziehung optional sein kann, z. B. ein Kundendatensatz ohne zugehörigen Adressdatensatz.

Eins zu viele und viele zu eins Beziehungen

Dies ist die am häufigsten verwendete Art von Beziehung. Stellen Sie sich eine E-Commerce-Website mit folgenden Optionen vor:

Kunden können viele Bestellungen aufgeben.
Bestellungen können viele Artikel enthalten.
Artikel können Beschreibungen in vielen Sprachen haben.

In diesen Fällen müssten wir "Eins zu Viele"-Beziehungen erstellen. Hier ist ein Beispiel:

Jeder Kunde kann null, eine oder mehrere Bestellungen haben. Eine Bestellung kann jedoch nur einem Kunden gehören.

Viele zu viele Beziehungen

In einigen Fällen benötigen Sie möglicherweise mehrere Instanzen auf beiden Seiten der Beziehung. Beispielsweise kann jede Bestellung mehrere Artikel enthalten. Und jeder Artikel kann auch in mehreren Bestellungen sein.

Für diese Beziehungen müssen wir eine zusätzliche Tabelle erstellen:

Die Items_Orders-Tabelle hat nur einen Zweck: eine "Viele zu Viele"-Beziehung zwischen den Artikeln und den Bestellungen herzustellen.

So können wir uns diese Art von Beziehung vorstellen:

Wenn Sie die Datensätze items_orders in das Diagramm aufnehmen möchten, sieht dies möglicherweise folgendermaßen aus:

Selbstreferenzierende Beziehungen

Dies wird verwendet, wenn eine Tabelle eine Beziehung zu sich selbst haben muss. Angenommen, Sie haben ein Empfehlungsprogramm. Kunden können andere Kunden auf Ihre Einkaufswebsite verweisen. Die Tabelle könnte folgendermaßen aussehen:

Die Kunden 102 und 103 wurden vom Kunden 101 verwiesen.

Dies kann auch einer "Eins-zu-Viele"-Beziehung ähneln, da ein Kunde mehrere Kunden empfehlen kann. Es kann auch wie eine Baumstruktur dargestellt werden:

Ein Kunde kann null, einen oder mehrere Kunden empfehlen. Jeder Kunde kann nur von einem oder gar keinem Kunden empfohlen werden.

Wenn Sie eine selbstreferenzierende Beziehung "Viele zu Viele" erstellen möchten, benötigen Sie eine zusätzliche Tabelle, wie wir sie im letzten Abschnitt beschrieben haben.

Fremde Schlüssel

Bisher haben wir nur einige der Konzepte kennengelernt. Jetzt ist es Zeit, sie mit SQL zum Leben zu erwecken. Für diesen Teil müssen wir verstehen, was Fremdschlüssel sind.

In den obigen Beziehungsbeispielen hatten wir immer diese "****_ id"-Felder, die auf eine Spalte in einer anderen Tabelle verweisen. In diesem Beispiel ist die Spalte customer_id in der Tabelle Orders eine Fremdschlüsselspalte:

Bei einer Datenbank wie MySQL gibt es zwei Möglichkeiten, Fremdschlüsselspalten zu erstellen:

Fremdschlüssel explizit definieren

Erstellen wir eine einfache Kundentabelle:

1	CREATE TABLE customers (
2	customer_id INT AUTO_INCREMENT PRIMARY KEY,
3	customer_name VARCHAR(100)
4	);

Nun die Auftragstabelle, die einen Fremdschlüssel enthält:

CREATE TABLE orders (
	order_id INT AUTO_INCREMENT PRIMARY KEY,
	customer_id INT,
	amount DOUBLE,
	FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Beide Spalten (customers.customer_id und orders.customer_id) sollten dieselbe exakte Datenstruktur haben. Wenn einer INT ist, sollte der andere zum Beispiel nicht BIGINT sein.

Bitte beachten Sie, dass in MySQL nur die InnoDB-Engine die volle Unterstützung für Fremdschlüssel bietet. Bei anderen Speicher-Engines können Sie diese jedoch weiterhin angeben, ohne Fehler zu machen. Außerdem wird die Spalte Fremdschlüssel automatisch indiziert, sofern Sie keinen anderen Index dafür angeben.

Ohne explizite Erklärung

Dieselbe Auftragstabelle kann erstellt werden, ohne die Spalte customer_id explizit als Fremdschlüssel zu deklarieren:

CREATE TABLE orders (
	order_id INT AUTO_INCREMENT PRIMARY KEY,
	customer_id INT,
	amount DOUBLE,
	INDEX (customer_id)
);

Wenn Sie Daten mit einer JOIN-Abfrage abrufen, können Sie diese Spalte weiterhin als Fremdschlüssel behandeln, obwohl das Datenbankmodul diese Beziehung nicht kennt.

1	SELECT * FROM orders
2	JOIN customers USING(customer_id)

Weitere Informationen zu JOIN-Abfragen finden Sie im Artikel.

Visualisierung der Beziehungen

Meine derzeitige Lieblingssoftware zum Entwerfen von Datenbanken und zum Visualisieren der Fremdschlüsselbeziehungen ist MySQL Workbench.

Sobald Sie Ihre Datenbank entworfen haben, können Sie die SQL exportieren und auf Ihrem Server ausführen. Dies ist sehr praktisch für größere und komplexere Datenbankdesigns.

JOIN-Abfragen

Um Daten aus einer Datenbank mit Beziehungen abzurufen, müssen häufig JOIN-Abfragen verwendet werden.

Bevor wir beginnen, erstellen wir die Tabellen und einige Beispieldaten, mit denen wir arbeiten möchten.

CREATE TABLE customers (
	customer_id INT AUTO_INCREMENT PRIMARY KEY,
	customer_name VARCHAR(100)
);

CREATE TABLE orders (
	order_id INT AUTO_INCREMENT PRIMARY KEY,
	customer_id INT,
	amount DOUBLE,
	FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO `customers` (`customer_id`, `customer_name`) VALUES
(1, 'Adam'),
(2, 'Andy'),
(3, 'Joe'),
(4, 'Sandy');

INSERT INTO `orders` (`order_id`, `customer_id`, `amount`) VALUES
(1, 1, 19.99),
(2, 1, 35.15),
(3, 3, 17.56),
(4, 4, 12.34);

Wir haben 4 Kunden. Ein Kunde hat zwei Bestellungen, zwei Kunden haben jeweils eine Bestellung und ein Kunde hat keine Bestellung. Lassen Sie uns nun die verschiedenen Arten von JOIN-Abfragen sehen, die wir für diese Tabellen ausführen können.

Cross Join

Dies ist der Standardtyp der JOIN-Abfrage, wenn keine Bedingung angegeben ist.

Das Ergebnis ist ein sogenanntes "kartesisches Produkt" der Tabellen. Dies bedeutet, dass jede Zeile aus der ersten Tabelle mit jeder Zeile aus der zweiten Tabelle übereinstimmt. Da jede Tabelle 4 Zeilen hatte, erhielten wir ein Ergebnis von 16 Zeilen.

Das Schlüsselwort JOIN kann stattdessen optional durch ein Komma ersetzt werden.

Natürlich ist diese Art von Ergebnis normalerweise nicht nützlich. Schauen wir uns also die anderen Join-Typen an.

Natürliche Join

Bei dieser Art von JOIN-Abfrage müssen die Tabellen einen übereinstimmenden Spaltennamen haben. In unserem Fall haben beide Tabellen die Spalte customer_id. Daher wird MySQL den Datensätzen nur dann beitreten, wenn der Wert dieser Spalte mit zwei Datensätzen übereinstimmt.

Wie Sie sehen, wird die Spalte customer_id diesmal nur einmal angezeigt, da das Datenbankmodul dies als allgemeine Spalte behandelt. Wir können die beiden Bestellungen von Adam und die beiden anderen Bestellungen von Joe und Sandy sehen. Endlich bekommen wir einige nützliche Informationen.

Inner Join

Wenn eine Verknüpfungsbedingung angegeben wird, wird eine innere Verknüpfung ausgeführt. In diesem Fall ist es eine gute Idee, das Feld customer_id in beiden Tabellen übereinstimmen zu lassen. Die Ergebnisse sollten dem Natural Join ähnlich sein.

Die Ergebnisse sind bis auf einen kleinen Unterschied gleich. Die Spalte customer_id wird zweimal wiederholt, einmal für jede Tabelle. Der Grund dafür ist, dass wir die Datenbank lediglich gebeten haben, die Werte in diesen beiden Spalten abzugleichen. Es ist jedoch nicht bekannt, dass sie dieselben Informationen darstellen.

Fügen wir der Abfrage weitere Bedingungen hinzu.

Diesmal haben wir nur Bestellungen über 15 US-Dollar erhalten.

ON-Klausel

Bevor wir zu anderen Join-Typen übergehen, müssen wir uns die ON-Klausel ansehen. Dies ist nützlich, um die JOIN-Bedingungen in eine separate Klausel aufzunehmen.

Jetzt können wir die JOIN-Bedingung von den WHERE-Klauselbedingungen unterscheiden. Es gibt aber auch einen kleinen Unterschied in der Funktionalität. Wir werden das in den LEFT JOIN-Beispielen sehen.

USING-Klausel

Die USING-Klausel ähnelt der ON-Klausel, ist jedoch kürzer. Wenn eine Spalte in beiden Tabellen den gleichen Namen hat, können wir sie hier angeben.

Tatsächlich ähnelt dies dem NATURAL JOIN, sodass die Join-Spalte (customer_id) in den Ergebnissen nicht zweimal wiederholt wird.

Linke äußere Join

Ein LEFT JOIN ist eine Art Outer Join. Wenn in diesen Abfragen keine Übereinstimmung aus der zweiten Tabelle gefunden wird, wird der Datensatz aus der ersten Tabelle weiterhin angezeigt.

Obwohl Andy keine Befehle hat, wird sein Datensatz immer noch angezeigt. Die Werte unter den Spalten der zweiten Tabelle werden auf NULL gesetzt.

Dies ist auch nützlich, um Datensätze zu finden, die keine Beziehungen haben. Zum Beispiel können wir nach Kunden suchen, die keine Bestellungen aufgegeben haben.

Wir haben nur nach NULL-Werten für die order_id gesucht.

Beachten Sie auch, dass das Schlüsselwort OUTER optional ist. Sie können einfach LEFT JOIN anstelle von LEFT OUTER JOIN verwenden.

Bedingungen

Betrachten wir nun eine Abfrage mit einer Bedingung.

Was ist mit Andy und Sandy passiert? LEFT JOIN sollte Kunden ohne passende Bestellungen zurückgeben. Das Problem ist, dass die WHERE-Klausel diese Ergebnisse blockiert. Um sie zu erhalten, können wir versuchen, auch die NULL-Bedingung einzuschließen.

Wir haben Andy, aber keine Sandy. Trotzdem sieht das nicht richtig aus. Um das zu bekommen, was wir wollen, müssen wir die ON-Klausel verwenden.

Jetzt haben wir alle und alle Bestellungen über 15 US-Dollar. Wie ich bereits sagte, hat die ON-Klausel manchmal eine etwas andere Funktionalität als die WHERE-Klausel. In einem Outer Join wie diesem sind Zeilen enthalten, auch wenn sie nicht den Bedingungen der ON-Klausel entsprechen.

Right (Outer) Join

Ein RIGHT OUTER JOIN funktioniert genauso, aber die Reihenfolge der Tabellen ist umgekehrt.

Diesmal haben wir keine NULL-Ergebnisse, da jede Bestellung einen passenden Kundendatensatz hat. Wir können die Reihenfolge der Tabellen ändern und die gleichen Ergebnisse erzielen wie beim LEFT OUTER JOIN.

Jetzt haben wir diese NULL-Werte, da sich die Kundentabelle auf der rechten Seite des Joins befindet.

Abschluss

Vielen Dank für das Lesen des Artikels. Ich hoffe, dass es Ihnen gefallen hat! Bitte hinterlassen Sie Ihre Kommentare und Fragen und haben einen schönen Tag!

Vergessen Sie nicht, die SQL-Skripte, Apps und Add-Ons auf Envato Market zu lesen. Sie erhalten einen Eindruck davon, was mit SQL-Datenbanken möglich ist, und finden möglicherweise die perfekte Lösung, um Sie bei Ihrem aktuellen Entwicklungsprojekt zu unterstützen.

Folgen Sie uns auf Twitter oder abonnieren Sie den Nettuts+ RSS-Feed, um die besten Webentwicklungs-Tutorials im Web zu erhalten.