MySQL-Unterabfrage und Details zur Verknüpfungstabelle

1. Was ist eine Unterabfrage?

Liste aller Kunden, die den Artikel TNT2 bestellt haben:

wählen Sie Kunden-ID
von Bestellungen
wobei Bestellnummer IN (SELECT Bestellnummer

von Bestellartikeln
wobei prod_id = "TNT2"
)

Formatierte SQL SELECT -Anweisungen, die Unterabfragen enthalten, sind schwer zu lesen und zu debuggen, insbesondere wenn sie komplex sind. Die Verwendung von Unterabfragen kann erheblich vereinfacht werden, indem die Unterabfrage wie oben gezeigt in mehrere Zeilen aufgeteilt und entsprechend eingerückt wird.

Es gibt keine Begrenzung für die Anzahl der Unterabfragen, die verschachtelt werden können. In der Praxis können jedoch aufgrund von Leistungseinschränkungen nicht zu viele Unterabfragen verschachtelt werden.

Notiz:

Spalten müssen übereinstimmen Wenn Sie eine Unterabfrage in der WHERE Klausel verwenden (wie hier gezeigt), sollten Sie sicherstellen, dass die SELECT Anweisung die gleiche Anzahl Spalten hat wie in der WHERE Klausel. allgemein,
Die Unterabfrage gibt eine einzelne Spalte zurück und gleicht diese ab, kann aber bei Bedarf auch mehrere Spalten verwenden.

Unterabfragen können nicht nur in where , sondern auch in select platziert werden.

Angenommen, Sie müssen die Gesamtzahl der Bestellungen für jeden Kunden in der customers anzeigen.

Wählen Sie Kundenname, Kundenstatus, (SELECT COUNT(*) FROM Bestellungen WHERE Bestellungen.Kunden-ID = Kunden.Kunden-ID) als Bestellungen
von Kunden
ORDER BY Kundenname

Der mysql -Operationsprozess besteht darin, zuerst customers auszuführen, um cust_name , cust_state und cust_id herauszufinden, und dann 5 Unterabfragen auszuführen, um die Ergebnisse herauszufinden.

Erstellen von Abfragen durch schrittweises Hinzufügen von Unterabfragen Das Testen und Debuggen von Abfragen, die Unterabfragen verwenden, kann schwierig sein, insbesondere wenn die Komplexität dieser Anweisungen zunimmt. Die zuverlässigste Methode zum Erstellen (und Testen) von Abfragen mit Unterabfragen besteht darin, dies inkrementell zu tun. Dies entspricht weitgehend der Art und Weise, wie MySQL sie handhabt. Erstellen und testen Sie zunächst die innerste Abfrage. Erstellen und testen Sie dann die äußere Abfrage mit fest codierten Daten und betten Sie die Unterabfrage erst ein, nachdem Sie überprüft haben, dass sie funktioniert. Testen Sie es jetzt erneut. Wiederholen Sie diese Schritte für jede Abfrage, die Sie hinzufügen möchten. Dadurch wird die Erstellung der Abfrage nur geringfügig zeitaufwändiger, Sie sparen aber später viel Zeit bei der Ermittlung der Gründe, warum die Abfrage nicht funktioniert hat. Zudem erhöht sich die Wahrscheinlichkeit, dass die Abfrage überhaupt funktioniert, erheblich.

Hier ist die Verbindung:

SELECT Verkäufername, Produktname, Produktpreis
VON Anbietern, Produkten
WO anbieter.vend_id = produkte.vend_id
ORDER BY Verkaufsname, Produktname

Notiz:

Vollqualifizierte Spaltennamen Vollqualifizierte Spaltennamen (Tabellen- und Spaltennamen durch einen Punkt getrennt) müssen verwendet werden, wenn der Verweis auf die Spalte möglicherweise mehrdeutig ist. Wenn Sie auf einen mehrdeutigen Spaltennamen verweisen, ohne ihn mit einem Tabellennamen zu qualifizieren, gibt MySQL einen Fehler zurück.

Dies ist die Funktion der Where-Anweisung zum Verknüpfen:

Es mag etwas seltsam erscheinen, eine WHERE-Klausel zum Herstellen einer Join-Beziehung zu verwenden, aber es gibt tatsächlich einen sehr guten Grund dafür. Bedenken Sie, dass beim Verknüpfen mehrerer Tabellen in einer SELECT-Anweisung die entsprechenden Relationen spontan erstellt werden. In der Datenbanktabellendefinition gibt es keine Angaben dazu, wie MySQL die Tabellen verknüpfen soll. Dies müssen Sie selbst tun. Wenn Sie zwei Tabellen verknüpfen, verknüpfen Sie tatsächlich jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle. Die WHERE-Klausel fungiert als Filterbedingung, die nur diejenigen Zeilen einschließt, die der angegebenen Bedingung (hier: der Join-Bedingung) entsprechen. Ohne eine WHERE-Klausel wird jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle gepaart, unabhängig davon, ob sie logisch zusammengehören.

Notiz:

cartesian product Produkt Das Ergebnis einer Tabellenbeziehung ohne Join-Bedingung ist das kartesische Produkt. Die Anzahl der abgerufenen Zeilen entspricht der Anzahl der Zeilen in der ersten Tabelle multipliziert mit der Anzahl der Zeilen in der zweiten Tabelle. Die bisher verwendeten Verknüpfungen werden als equijoin bezeichnet und basieren auf einem Gleichheitstest zwischen zwei Tabellen. Diese Art der Verbindung wird auch als innere Verbindung bezeichnet. Tatsächlich können Sie für diese Art von Verknüpfung eine leicht andere Syntax verwenden, um den Verknüpfungstyp explizit anzugeben.

Die folgende SELECT-Anweisung gibt genau dieselben Daten zurück wie das vorherige Beispiel:

SELECT Verkäufername, Produktname, Produktpreis
VON Anbietern INNER JOIN Produkte auf Anbieter.vend_id = products.vend_id
ORDER BY Händlername, Produktname

Welche Syntax soll verwendet werden? ANSI SQL Spezifikation bevorzugt die INNER JOIN Syntax. Obwohl es tatsächlich einfacher ist, Verknüpfungen mit der WHERE Klausel zu definieren, stellt die Verwendung einer expliziten Verknüpfungssyntax sicher, dass Sie die Verknüpfungsbedingung nicht vergessen, was manchmal die Leistung beeinträchtigen kann.

Überlegungen zur Leistung: MySQL verknüpft jede zur Laufzeit angegebene Tabelle, um die Verknüpfung zu verarbeiten. Diese Verarbeitung kann sehr ressourcenintensiv sein. Achten Sie daher darauf, keine unnötigen Tabellen zu verknüpfen. Je mehr Tabellen verknüpft werden, desto stärker verschlechtert sich die Leistung.

Experimentieren Sie viel. Wie Sie sehen, gibt es normalerweise mehr als eine Möglichkeit, einen bestimmten SQL-Vorgang auszuführen. Es gibt selten absolut richtige oder falsche Wege, Dinge zu tun. Die Leistung kann durch die Art des Vorgangs, die Datenmenge in der Tabelle, das Vorhandensein von Indizes oder Schlüsseln und andere Bedingungen beeinträchtigt werden. Daher ist es notwendig, mit verschiedenen Auswahlmechanismen zu experimentieren, um denjenigen zu finden, der für eine bestimmte Situation am besten geeignet ist. Wir können auch mehrere Tabellenverknüpfungen verwenden, aber dabei tritt ein Problem auf: Da der Tabellenname an mehreren Stellen verwendet wird und daher sehr lang ist, können Sie den Tabellenalias verwenden.

wie:

Nachfolgend werden einige spezielle Verbindungen vorgestellt.

2. Selbst beitreten

Angenommen, Sie stellen fest, dass ein Artikel (mit der ID DTNTR ) Probleme aufweist, und Sie möchten wissen, ob diese Probleme auch bei anderen Artikeln desselben Lieferanten vorliegen. Für diese Abfrage müssen Sie zunächst den Lieferanten ermitteln, der den Artikel mit ID DTNTR herstellt. Anschließend müssen Sie weitere von diesem Lieferanten hergestellte Artikel ermitteln.

So können Sie dieses Problem lösen:

Sie könnten eine Unterabfrage wie diese verwenden:

Wählen Sie Produkt-ID, Produktname aus.
von Produkten
wobei vend_id = (SELECT vend_id aus products WHERE prod_id ='DTNTR')

Es können auch Self-Joins verwendet werden.

Wählen Sie t1.prod_id,t2.prod_name
aus Produkten t1, Produkten t2
wobei t1.vend_id = t2.vend_id und t1.prod_id='DTNTR'

Verwenden von Self-Joins anstelle von Unterabfragen Self-Joins werden häufig als äußere Anweisungen verwendet, um Unterabfragen zu ersetzen, die beim Abrufen von Daten aus derselben Tabelle verwendet werden. Obwohl das Endergebnis dasselbe ist, kann ein Join manchmal viel schneller verarbeitet werden als eine Unterabfrage. Sie sollten beide Ansätze ausprobieren, um festzustellen, welcher besser funktioniert.

3. Natürlicher Join

Wenn Sie Tabellen verknüpfen, sollte es mindestens eine Spalte geben, die in mehr als einer Tabelle vorkommt (die verknüpfte Spalte). Ein Standard-Join (der im vorherigen Kapitel beschriebene Inner Join) gibt alle Daten zurück, auch wenn dieselben Spalten mehrfach vorkommen. Durch den natürlichen Join werden Mehrfachvorkommen eliminiert, so dass jede Spalte nur einmal zurückgegeben wird.

Wie kann diese Arbeit abgeschlossen werden? Die Antwort ist: Nicht das System erledigt die Arbeit, sondern Sie selbst. Bei einem natürlichen Join wählen Sie nur eindeutige Spalten aus. Dies geschieht normalerweise durch die Verwendung eines Platzhalters ( SELECT * ) für die Tabelle und eine explizite Teilmenge der Spalten aller anderen Tabellen.

4. Externe Verbindungen

Viele Verknüpfungen verknüpfen Zeilen einer Tabelle mit Zeilen einer anderen Tabelle. Manchmal müssen Sie jedoch Zeilen einschließen, die keine zugehörigen Zeilen haben. Beispielsweise möchten Sie einen Join möglicherweise für Folgendes verwenden:

Beispiel: Zählen Sie, wie viele Bestellungen jeder Kunde aufgegeben hat, einschließlich derjenigen, die noch keine Bestellung aufgegeben haben;

SELECT Kunden.Kunden-ID,Bestellnummer
von Kunden LEFT OUTER JOIN Bestellungen auf Kunden.cust_id = orders.cust_id

Diese SELECT Anweisung verwendet die Schlüsselwörter OUTER JOIN , um den Verknüpfungstyp anzugeben (anstatt ihn in der WHERE Klausel anzugeben). Im Gegensatz zu einem inneren Join, der Zeilen aus zwei Tabellen verknüpft, schließt ein äußerer Join jedoch auch Zeilen ein, für die es keine verknüpften Zeilen gibt. Wenn Sie die OUTER JOIN -Syntax verwenden, müssen Sie das Schlüsselwort RIGHT oder LEFT verwenden, um die Tabelle anzugeben, die alle ihre Zeilen enthält ( RIGHT bezieht sich auf die Tabelle auf der rechten Seite von OUTER JOIN und LEFT bezieht sich auf die Tabelle auf der linken Seite von OUTER JOIN ).

Verwenden eines Join mit einer Aggregatfunktion:

So rufen Sie alle Kunden und die Anzahl der von jedem Kunden aufgegebenen Bestellungen ab:

Wählen Sie Kunden.Kunden-ID, ANZAHL(Bestellnummer) als Nummer aus.
von Kunden LEFT OUTER JOIN Bestellungen auf Kunden.cust_id = orders.cust_id
GRUPPE NACH Kunden-ID

Notiz:
1. Achten Sie auf die Art der verwendeten Verbindung. Im Allgemeinen verwenden wir innere Verknüpfungen, aber auch äußere Verknüpfungen sind effektiv.
2. Stellen Sie sicher, dass die richtigen Join-Bedingungen verwendet werden. Andernfalls werden falsche Daten zurückgegeben.
3. Die Join-Bedingung sollte immer angegeben werden, da sonst ein kartesisches Produkt entsteht.
4. In einen Join können mehrere Tabellen einbezogen werden und für jeden Join können sogar unterschiedliche Join-Typen verwendet werden. Dies ist zwar zulässig und im Allgemeinen sinnvoll, Sie sollten jedoch jede Verbindung einzeln testen, bevor Sie sie gemeinsam testen. Dies erleichtert die Fehlerbehebung.

Dies ist das Ende dieses Artikels über MySQL-Unterabfragen und Join-Tabellen. Weitere relevante MySQL-Unterabfragen und Join-Tabellen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!