Share
Heute setzen wir unsere Reise in die Welt der SQL- und relationalen Datenbanksysteme fort. In diesem dritten Teil der Serie erfahren Sie, wie Sie mit mehreren Tabellen arbeiten, 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 von Envato Market auschecken.
Wenn Sie eine Datenbank erstellen, müssen Sie für verschiedene Arten von Entitäten separate Tabellen verwenden. Einige Beispiele sind: Kunden, Bestellungen, Artikel, Nachrichten usw. Aber zwischen diesen Tabellen müssen auch Beziehungen bestehen. Zum Beispiel machen Kunden Bestellungen und Bestellungen enthalten Artikel. Diese Beziehungen müssen in der Datenbank dargestellt werden. Beim Abrufen von Daten mit SQL müssen bestimmte Arten von JOIN-Abfragen verwendet werden, um das zu erhalten, was wir benötigen.
Es gibt verschiedene Arten von Datenbankbeziehungen. Heute werden wir folgendes abdecken:
Bei der Auswahl von Daten aus mehreren Tabellen mit Beziehungen verwenden wir die JOIN-Abfrage. Es gibt verschiedene Arten von JOINs, und wir werden folgende Informationen erhalten:
Wir werden auch etwas über die ON-Klausel und die USING-Klausel lernen.
Nehmen wir an, Sie haben einen Tisch für Kunden:
Wir können die Kundenadresseninformationen in eine separate Tabelle stellen:
Jetzt haben wir eine Beziehung zwischen der Customers-Tabelle und der Addresses-Tabelle. Wenn jede Adresse nur zu 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 es jetzt in der Customers-Tabelle ein Feld mit dem Namen "address_id" gibt, das auf den übereinstimmenden Datensatz in der Address-Tabelle verweist. Dies wird als "Fremdschlüssel" bezeichnet und wird für alle Arten von Datenbankbeziehungen verwendet. Wir werden dieses Thema später im Artikel behandeln.
Wir können die Beziehung zwischen den Kunden- und Adressdatensätzen folgendermaßen visualisieren:
Beachten Sie, dass das Vorhandensein einer Beziehung optional sein kann, z. B. wenn Sie einen Kundendatensatz haben, der keinen zugehörigen Adressdatensatz hat.
Dies ist die am häufigsten verwendete Art der Beziehung. Betrachten Sie eine E-Commerce-Website mit den folgenden:
In diesen Fällen müssten wir "One-To-Many-Beziehungen" schaffen. Hier ist ein Beispiel:
Jeder Kunde kann keine, eine oder mehrere Bestellungen haben. Eine Bestellung kann jedoch nur einem Kunden gehören.
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 mehrere Bestellungen haben.
Für diese Beziehungen müssen wir eine zusätzliche Tabelle erstellen:
Die Items_Orders-Tabelle hat nur einen Zweck, nämlich eine "Viele-zu-viele" -Beziehung zwischen den Artikeln und den Bestellungen herzustellen.
So können wir diese Art von Beziehung visualisieren:
Wenn Sie die items_orders-Datensätze in das Diagramm aufnehmen möchten, kann dies folgendermaßen aussehen:
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 Shopping-Website verweisen. Die Tabelle kann so 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 verweisen kann. Es kann auch wie eine Baumstruktur dargestellt werden:
Ein Kunde kann sich auf null, einen oder mehrere Kunden beziehen. Jeder Kunde kann von nur einem Kunden oder gar keinem Kunden zugewiesen werden.
Wenn Sie eine selbstbezogene "Viele-zu-viele-Beziehung" erstellen möchten, benötigen Sie eine zusätzliche Tabelle, wie wir sie im letzten Abschnitt besprochen haben.
Bisher haben wir nur einige Konzepte kennengelernt. Jetzt ist es an der 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 referenzierten. In diesem Beispiel handelt es sich bei der customer_id-Spalte in der Tabelle Orders um eine Fremdschlüsselspalte:
Bei einer Datenbank wie MySQL gibt es zwei Möglichkeiten, Fremdschlüsselspalten zu erstellen:
Lassen Sie uns eine einfache Kundentabelle erstellen:
CREATE TABLE-Kunden (customer_id INT AUTO_INCREMENT PRIMARY KEY, Kundenname VARCHAR (100));
Nun die Bestellungstabelle, die einen Fremdschlüssel enthalten wird:
CREATE TABLE-Aufträge (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, Betrag DOUBLE, FOREIGN KEY (customer_id) REFERENCES customers (customer_id));
Beide Spalten (customers.customer_id und orders.customer_id) sollten dieselbe Datenstruktur haben. Wenn eine INT ist, sollte die andere beispielsweise nicht BIGINT sein.
Bitte beachten Sie, dass in MySQL nur die InnoDB-Engine vollständige Unterstützung für Fremdschlüssel bietet. Bei anderen Speicher-Engines können Sie sie jedoch dennoch ohne Fehler angeben. Auch die Spalte Fremdschlüssel wird automatisch indiziert, sofern Sie keinen anderen Index dafür angeben.
Dieselbe Auftragstabelle kann erstellt werden, ohne dass die customer_id-Spalte explizit als Fremdschlüssel deklariert wird:
CREATE TABLE-Aufträge (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, Betrag DOUBLE, INDEX (customer_id));
Wenn Sie Daten mit einer JOIN-Abfrage abrufen, können Sie diese Spalte immer noch als Fremdschlüssel behandeln, obwohl das Datenbankmodul diese Beziehung nicht kennt.
SELECT * FROM Bestellungen JOIN Kunden USING (customer_id)
In diesem Artikel erfahren Sie mehr über JOIN-Anfragen.
Meine aktuelle Lieblingssoftware zum Entwerfen von Datenbanken und zur Visualisierung 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.
Um Daten aus einer Datenbank abzurufen, die Beziehungen aufweist, müssen häufig JOIN-Abfragen verwendet werden.
Bevor wir beginnen, erstellen wir die Tabellen und einige Beispieldaten, um damit zu arbeiten.
Accentsconagua
CREATE TABLE-Kunden (customer_id INT AUTO_INCREMENT PRIMARY KEY, Kundenname VARCHAR (100)); CREATE TABLE-Aufträge (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, Betrag DOUBLE, FOREIGN KEY (customer_id) REFERENCES customers (customer_id)); INSERT INTO 'customer' ('customer_id', 'customer_name') WERTE (1, 'Adam'), (2, 'Andy'), (3, 'Joe'), (4, 'Sandy'); INSERT INTO-Orders ('order_id', 'customer_id', 'betrag') WERTE (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. Nun sehen wir uns die verschiedenen Arten von JOIN-Abfragen an, die wir für diese Tabellen ausführen können.
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 der ersten Tabelle mit jeder Zeile der zweiten Tabelle übereinstimmt. Da jede Tabelle 4 Zeilen hatte, erhielten wir ein Ergebnis von 16 Zeilen.
Das JOIN-Schlüsselwort kann stattdessen optional durch ein Komma ersetzt werden.
Natürlich ist ein solches Ergebnis normalerweise nicht sinnvoll. Schauen wir uns also die anderen Join-Typen an.
Bei dieser Art von JOIN-Abfrage müssen die Tabellen einen übereinstimmenden Spaltennamen haben. In unserem Fall haben beide Tabellen die Spalte customer_id. MySQL verknüpft die Datensätze also nur dann, wenn der Wert dieser Spalte in zwei Datensätzen übereinstimmt.
Wie Sie sehen, wird die customer_id-Spalte dieses Mal nur einmal angezeigt, da die Datenbank-Engine dies als allgemeine Spalte behandelt. Wir können die zwei Bestellungen von Adam und die beiden anderen Bestellungen von Joe und Sandy sehen. Zum Schluss erhalten wir einige nützliche Informationen.
Wenn eine Join-Bedingung angegeben ist, wird ein Inner Join durchgeführt. In diesem Fall ist es sinnvoll, dass das Feld customer_id in beiden Tabellen übereinstimmt. Die Ergebnisse sollten dem Natural Join ähneln.
Die Ergebnisse sind bis auf einen kleinen Unterschied gleich. Die customer_id-Spalte wird zweimal für jede Tabelle wiederholt. Der Grund ist, wir haben die Datenbank lediglich gebeten, die Werte in diesen beiden Spalten abzugleichen. Es ist jedoch eigentlich nicht bekannt, dass sie die gleiche Information darstellen.
Fügen wir der Abfrage einige weitere Bedingungen hinzu.
Dieses Mal haben wir nur die Bestellungen über 15 Dollar erhalten.
Bevor wir uns anderen Join-Typen zuwenden, müssen wir uns die ON-Klausel ansehen. Dies ist hilfreich, wenn Sie die JOIN-Bedingungen in eine separate Klausel stellen.
Jetzt können wir die JOIN-Bedingung von den WHERE-Klauselbedingungen unterscheiden. Aber auch die Funktionalität unterscheidet sich geringfügig. Wir werden das in den LEFT JOIN-Beispielen sehen.
Die USING-Klausel ähnelt der ON-Klausel, ist jedoch kürzer. Wenn eine Spalte in beiden Tabellen den gleichen Namen hat, können Sie sie hier angeben.
Tatsächlich ist dies dem NATURAL JOIN sehr ähnlich, sodass die Join-Spalte (customer_id) in den Ergebnissen nicht zweimal wiederholt wird.
Ein LEFT JOIN ist eine Art Outer Join. Wenn in diesen Abfragen keine Übereinstimmung in der zweiten Tabelle gefunden wird, wird der Datensatz der ersten Tabelle weiterhin angezeigt.
Obwohl Andy keine Aufträge 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. Wir können beispielsweise nach Kunden suchen, die noch keine Bestellungen aufgegeben haben.
Wir haben nur nach NULL-Werten für order_id gesucht.
Beachten Sie auch, dass das OUTER-Schlüsselwort optional ist. Sie können LEFT JOIN anstelle von LEFT OUTER JOIN verwenden.
Lassen Sie uns nun eine Abfrage mit einer Bedingung betrachten.
Was ist also 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 auch versuchen, die NULL-Bedingung einzubeziehen.
Wir haben Andy, aber keine Sandy. Trotzdem sieht das nicht richtig aus. Um zu bekommen, was wir wollen, müssen wir die ON-Klausel verwenden.
Jetzt haben wir alle und alle Bestellungen über 15 $. Wie ich bereits gesagt habe, hat die ON-Klausel manchmal eine etwas andere Funktionalität als die WHERE-Klausel. In einem äußeren Join wie diesem werden Zeilen eingefügt, auch wenn sie nicht den Bedingungen der ON-Klausel entsprechen.
Ein Right-Right-Join funktioniert genauso, aber die Reihenfolge der Tabellen ist umgekehrt.
Diesmal haben wir keine NULL-Ergebnisse, da jeder Auftrag einen übereinstimmenden Kundendatensatz hat. Wir können die Reihenfolge der Tabellen ändern und dieselben Ergebnisse erzielen wie bei LEFT OUTER JOIN.
Jetzt haben wir diese NULL-Werte, da sich die Kundentabelle auf der rechten Seite des Joins befindet.
Vielen Dank, dass Sie den Artikel gelesen haben. Ich hoffe, dass es Ihnen gefallen hat! Bitte hinterlassen Sie Ihre Kommentare und Fragen und haben Sie einen schönen Tag!
Vergessen Sie nicht, die SQL-Skripts, Apps und Add-ons von Envato Market zu testen. Sie erhalten ein Gefühl dafür, was mit SQL-Datenbanken möglich ist, und Sie 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 für die besten Webentwicklungs-Tutorials im Web.
