Prinzip und Anwendung der MySQL-Verbindungsabfrage

Überblick

Eine der leistungsstärksten Funktionen von MySQL ist die Möglichkeit, Tabellen während des Datenabrufs zu verknüpfen. Die meisten Datenabfragen für einzelne Tabellen können unsere Anforderungen nicht erfüllen. Zu diesem Zeitpunkt müssen wir eine oder mehrere Tabellen verbinden und die benötigten Daten unter bestimmten Bedingungen herausfiltern.

Bevor wir uns mit der MySQL-Joinabfrage befassen, wollen wir zunächst das Prinzip des kartesischen Produkts verstehen.

Datenaufbereitung

Verwenden Sie weiterhin die Tabellendaten im vorherigen Abschnitt (einschließlich der Klassentabelle und der Studententabelle):

mysql> wähle * aus Klassen;
+---------+-----------+
| Klassen-ID | Klassenname |
+---------+-----------+
| 1 | Klasse 1, Jahrgangsstufe 9|
| 2 | 9. Klasse, 2. Klasse |
| 3 | 3. Klasse, 3. Klasse |
| 4 | Klasse 3-4 |
+---------+-----------+
4 Reihen im Set

mysql> wähle * aus Studenten;
+-----------+----------+----------+---------+
| Studenten-ID | Studentenname | Punktzahl | Klassen-ID |
+-----------+----------+----------+---------+
| 1 | Marke | 97,5 | 1 |
| 2 | Helene | 96,5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | Sol | 97 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72,5 | 3 |
| 19 | lala | 51 | 0 |
+-----------+----------+----------+---------+
9 Reihen im Set

Kartesisches Produkt

Kartesisches Produkt: auch als kartesisches Produkt bekannt. Unter der Annahme von zwei Mengen A und B stellt das kartesische Produkt alle möglichen Ergebnisse dar, die durch eine beliebige Verbindung zwischen den Elementen in Menge A und den Elementen in Menge B entstehen.

Wenn beispielsweise A m Elemente und B n Elemente enthält, erzeugt das kartesische Produkt von A und B m*n Ergebnisse, was dem Durchlaufen der Elemente beider Mengen in beliebiger Kombination entspricht.

Die Implementierung des kartesischen Produkts in SQL ist ein Cross Join. Alle Join-Methoden generieren zunächst eine temporäre kartesische Produkttabelle. Das kartesische Produkt ist ein Konzept aus der relationalen Algebra, das jede beliebige Kombination der Datenzeilen zweier Tabellen darstellt.

Die obige Tabelle enthält also 4 (Klassentabelle) * 9 (Schülertabelle) = 36 Daten.

Syntaxformat für kartesisches Produkt:

 wähle cname1, cname2, ... aus tname1, tname2, ...;
 oder
 wähle cname aus tname1, verbinde tname2 [verbinde tname...];

Die Legende zeigt:

Die tatsächlichen Ausführungsergebnisse der beiden obigen Tabellen sind wie folgt:

mysql> select * aus Klassen a, Studenten b, sortiert nach a.classid, b.studentid;
+---------+-----------+--------------+-------------+-----------+---------+
| Klassen-ID | Klassenname | Studenten-ID | Studentenname | Punktzahl | Klassen-ID |
+---------+-----------+--------------+-------------+-----------+---------+
| 1 | Klasse 1, Stufe 9 | 1 | Marke | 97,5 | 1 |
| 1 | Klasse 1, Jahrgangsstufe 9 | 2 | Helen | 96,5 | 1 |
| 1 | Klasse 1, Jahrgangsstufe 9 | 3 | lyn | 96 | 1 |
| 1 | Klasse 1, Jahrgangsstufe 9 | 4 | sol | 97 | 1 |
| 1 | Klasse 1, Jahrgangsstufe 9 | 7 | b1 | 81 | 2 |
| 1 | Klasse 1, Jahrgangsstufe 9 | 8 | b2 | 82 | 2 |
| 1 | Klasse 1, Jahrgangsstufe 9 | 13 | C1 | 71 | 3 |
| 1 | Klasse 1, Stufe 9 | 14 | C2 | 72,5 | 3 |
| 1 | Klasse 1, Klasse 9 | 19 | lala | 51 | 0 |
| 2 | Klasse 2, Stufe 9 | 1 | Marke | 97,5 | 1 |
| 2 | Klasse 2, Jahrgangsstufe 9 | 2 | Helen | 96,5 | 1 |
| 2 | Klasse 2, Jahrgangsstufe 9 | 3 | lyn | 96 | 1 |
| 2 | Klasse 2, Stufe 9 | 4 | sol | 97 | 1 |
| 2 | Klasse 2, Jahrgangsstufe 9 | 7 | b1 | 81 | 2 |
| 2 | Klasse 2, Jahrgangsstufe 9 | 8 | b2 | 82 | 2 |
| 2 | Klasse 2, Jahrgangsstufe 9 | 13 | C1 | 71 | 3 |
| 2 | Klasse 2, Stufe 9 | 14 | C2 | 72,5 | 3 |
| 2 | Klasse 2, Jahrgangsstufe 9 | 19 | lala | 51 | 0 |
| 3 | Klasse 3, Stufe 9 | 1 | Marke | 97,5 | 1 |
| 3 | Klasse 3, Stufe 9 | 2 | Helen | 96,5 | 1 |
| 3 | Klasse 3, Jahrgangsstufe 9 | 3 | lyn | 96 | 1 |
| 3 | Klasse 3, Stufe 9 | 4 | sol | 97 | 1 |
| 3 | Klasse 3, Jahrgangsstufe 9 | 7 | b1 | 81 | 2 |
| 3 | Klasse 3, Jahrgangsstufe 9 | 8 | b2 | 82 | 2 |
| 3 | Klasse 3, Jahrgangsstufe 9 | 13 | C1 | 71 | 3 |
| 3 | Klasse 3, Stufe 9 | 14 | C2 | 72,5 | 3 |
| 3 | Klasse 3, Stufe 9 | 19 | lala | 51 | 0 |
| 4 | Klasse 3-4 | 1 | Marke | 97,5 | 1 |
| 4 | Klasse 3-4 | 2 | Helen | 96,5 | 1 |
| 4 | Klasse 3-4 | 3 | lyn | 96 | 1 |
| 4 | Klasse 3-4 | 4 | Sol | 97 | 1 |
| 4 | Klasse 3-4 | 7 | b1 | 81 | 2 |
| 4 | Klasse 3-4 | 8 | b2 | 82 | 2 |
| 4 | Klasse 3-4 | 13 | C1 | 71 | 3 |
| 4 | Klasse 3-4 | 14 | C2 | 72,5 | 3 |
| 4 | Klasse 3-4 | 19 | lala | 51 | 0 |
+---------+-----------+--------------+-------------+-----------+---------+
36 Reihen im Set

Diese Art von Daten ist definitiv nicht das, was wir wollen. In praktischen Anwendungen müssen wir beim Verknüpfen von Tabellen Einschränkungen hinzufügen, um die Daten herauszufiltern, die wir wirklich brauchen.

Unsere wichtigsten Verbindungsabfragen sind: innere Verbindung, linke (äußere) Verbindung, rechte (äußere) Verbindung. Sehen wir sie uns einzeln an.

Inner Join-Abfrage Inner Join

Syntaxformat:

 Wählen Sie CNAME aus dem inneren Join TNAME1 TNAME2 unter Join-Bedingung aus.
 Oder wählen Sie cname aus tname1 und verbinden Sie tname2 gemäß Verbindungsbedingung;
 Oder wählen Sie cname aus tname1,tname2 [wobei Join-Bedingung gilt].

Hinweis: Auf der Grundlage des kartesischen Produkts wird eine Verbindungsbedingung hinzugefügt, die beiden Tabellen werden kombiniert und die Datensätze zurückgegeben, die die Verbindungsbedingung erfüllen, d. h. der Schnittpunkt (schattiert) der beiden Tabellen wird zurückgegeben. Wenn diese Verbindungsbedingung nicht hinzugefügt wird, ist das Ergebnis das obige kartesische Produkt.

mysql> wähle a.klassenname,b.studentenname,b.score aus den Klassen a, innerer Join für Studenten b auf a.classid = b.classid;
+--------------+----------+----------+
| Klassenname | Studentenname | Punktzahl |
+--------------+----------+----------+
| Klasse 1, Stufe 9 | Marke | 97,5 |
| Klasse 1, Klasse 9 | Helen | 96,5 |
| Klasse 1, Klasse 9 | lyn | 96 |
| Klasse 1, Stufe 9 | sol | 97 |
| Klasse 2, Stufe 9 | b1 | 81 |
| Klasse 2, Stufe 9 | b2 | 82 |
| Klasse 3, Stufe 9 | C1 | 71 |
| Klasse 3, Stufe 9 | C2 | 72,5 |
+--------------+----------+----------+
8 Reihen im Set

Aus den obigen Daten können wir ersehen, dass die Klassen-ID der dritten und vierten Klasse 4 ist und herausgefiltert wird, da kein zugeordneter Schüler vorhanden ist. Die Klassen-ID von lala ist 0 und kann keiner bestimmten Klasse zugeordnet werden. Sie wird ebenfalls herausgefiltert. Es wird nur die Schnittmenge der Daten beider Tabellen verwendet.

mysql> wähle a.klassenname,b.studentenname,b.score aus den Klassen a,Studenten b, wobei a.classid = b.classid und a.classid=1;
+--------------+----------+----------+
| Klassenname | Studentenname | Punktzahl |
+--------------+----------+----------+
| Klasse 1, Stufe 9 | Marke | 97,5 |
| Klasse 1, Klasse 9 | Helen | 96,5 |
| Klasse 1, Klasse 9 | lyn | 96 |
| Klasse 1, Stufe 9 | sol | 97 |
+--------------+----------+----------+
4 Reihen im Set

Um die Noteninformationen der Schüler der Klasse 1 zu finden, verwenden Sie das dritte Syntaxformat oben. Diese Methode ist prägnant und effizient, und die Where-Bedingung wird direkt nach dem Ergebnis der Verbindungsabfrage gefiltert.

Linke Join-Abfrage Linke Join

Left Join aktiviert / Left Outer Join aktiviert, Syntaxformat:

Wähle CNAME aus TNAME1 links und verbinde TNAME2 gemäß Verbindungsbedingung.

Hinweis: Left Join ist die Abkürzung für Left Outer Join, der vollständige Name lautet Left Outer Join, eine Art Outer Join. Bei einem linken (äußeren) Join werden alle Datensätze der linken Tabelle (Klassen) angezeigt, während in der rechten Tabelle (Studenten) nur die Datensätze angezeigt werden, die den Suchkriterien entsprechen. Die Inhalte, die nicht der richtigen Tabelle zugeordnet werden können, sind alle Null.

mysql> wähle a.classname,b.studentname,b.score aus den Klassen a links und schließe dich den Schülern b an mit a.classid = b.classid;
+--------------+----------+----------+
| Klassenname | Studentenname | Punktzahl |
+--------------+----------+----------+
| Klasse 1, Stufe 9 | Marke | 97,5 |
| Klasse 1, Klasse 9 | Helen | 96,5 |
| Klasse 1, Klasse 9 | lyn | 96 |
| Klasse 1, Stufe 9 | sol | 97 |
| Klasse 2, Stufe 9 | b1 | 81 |
| Klasse 2, Stufe 9 | b2 | 82 |
| Klasse 3, Stufe 9 | C1 | 71 |
| Klasse 3, Stufe 9 | C2 | 72,5 |
| Klasse 3-4 | NULL | NULL |
+--------------+----------+----------+
9 Reihen im Set

Aus den obigen Ergebnissen können wir erkennen, dass in der dritten und vierten Klasse keine entsprechenden Schüler gefunden werden können, weshalb die letzten beiden Felder mit Null markiert sind.

Richtige Join-Abfrage

Rechtsverknüpfung aktiviert / Rechtsaußenverknüpfung aktiviert, Syntaxformat:

Wählen Sie CNAME aus TNAME1 und verbinden Sie TNAME2 entsprechend der Verbindungsbedingung.

Hinweis: Right Join ist die Abkürzung für Right Outer Join und der vollständige Name lautet Right Outer Join, was eine Art von Outer Join ist. Im Gegensatz zum linken (äußeren) Join werden beim rechten (äußeren) Join nur die Datensätze der linken Tabelle (Klassen) angezeigt, die den Suchkriterien entsprechen, während in der rechten Tabelle (Schüler) alle Datensätze angezeigt werden. Die Stellen, an denen die linke Tabelle nicht genügend Datensätze hat, sind alle NULL.

mysql> wähle a.classname,b.studentname,b.score aus den Klassen a und schließe Studenten b mit a.classid = b.classid ab;
+--------------+----------+----------+
| Klassenname | Studentenname | Punktzahl |
+--------------+----------+----------+
| Klasse 1, Stufe 9 | Marke | 97,5 |
| Klasse 1, Klasse 9 | Helen | 96,5 |
| Klasse 1, Klasse 9 | lyn | 96 |
| Klasse 1, Stufe 9 | sol | 97 |
| Klasse 2, Stufe 9 | b1 | 81 |
| Klasse 2, Stufe 9 | b2 | 82 |
| Klasse 3, Stufe 9 | C1 | 71 |
| Klasse 3, Stufe 9 | C2 | 72,5 |
| NULL | lala | 51 |
+--------------+----------+----------+
9 Reihen im Set

Aus den obigen Ergebnissen können wir ersehen, dass Lala die Klasse nicht finden kann und daher das Feld „Klassenname“ null ist.

Join-Abfrage + Aggregatfunktion

Bei der Verwendung von Join-Abfragen werden häufig Aggregatfunktionen zum Zusammenfassen von Daten verwendet. Basierend auf den obigen Daten können Sie beispielsweise die Anzahl der Schüler in jeder Klasse, die Durchschnittsnote und die Gesamtnote der Klasse abfragen.

mysql> select a.classname als „Klassenname“, count(b.studentid) als „Gesamtzahl der Studenten“, sum(b.score) als „Gesamtpunktzahl“, avg(b.score) als „Durchschnittspunktzahl“
von Klassen a innerer Join Studenten b auf a.classid = b.classid
Gruppieren nach a.classid,a.classname;
+----------+--------+--------+-----------+
| Klassenname| Gesamtzahl der Schüler| Gesamtpunktzahl| Durchschnittspunktzahl|
+----------+--------+--------+-----------+
| Klasse 1, Jahrgangsstufe 9 | 4 | 387,00 | 96,750000 |
| Klasse 2, Jahrgangsstufe 9 | 2 | 163,00 | 81,500000 |
| Klasse 3, Jahrgangsstufe 9 | 2 | 143,50 | 71,750000 |
+----------+--------+--------+-----------+
3 Reihen im Set

Bei der Abfrage der Tabellen werden hier die Klassen (classid, classname) gruppiert und die Schülerzahl, die Durchschnittsnote und die Gesamtnote jeder Klasse ausgegeben.

Zusätzliche Filterbedingungen für die Verbindungsabfrage

Nach der Verwendung der Verbindungsabfrage werden die Daten höchstwahrscheinlich gefiltert, sodass wir nach der Verbindungsabfrage eine Where-Bedingung hinzufügen können. Beispielsweise können wir basierend auf den obigen Ergebnissen nur die Schülerinformationen einer Klasse abrufen.

mysql> wähle a.klassenname,b.studentenname,b.score aus den Klassen a, innerer Join für Studenten b auf a.classid = b.classid, wobei a.classid=1;
+--------------+----------+----------+
| Klassenname | Studentenname | Punktzahl |
+--------------+----------+----------+
| Klasse 1, Stufe 9 | Marke | 97,5 |
| Klasse 1, Klasse 9 | Helen | 96,5 |
| Klasse 1, Klasse 9 | lyn | 96 |
| Klasse 1, Stufe 9 | sol | 97 |
+--------------+----------+----------+
4 Reihen im Set

Wie oben werden nur Schüler einer Klasse ausgegeben. Ebenso können Sie Grenzwertbeschränkungen, Sortierung und andere Operationen hinzufügen.

Zusammenfassen

1. Die Join-Abfrage muss eine Join-Bedingung enthalten, sonst werden daraus kartesische Produktdaten. Die Verwendung falscher Join-Bedingungen führt ebenfalls zu falschen Daten.

2. Die SQL-Spezifikation empfiehlt, als erste Wahl die INNER JOIN-Syntax zu verwenden. Es gibt jedoch keinen offensichtlichen Leistungsunterschied zwischen den verschiedenen Verbindungsmethoden selbst. Der Leistungsunterschied wird hauptsächlich durch eine Kombination mehrerer Bedingungen wie Datenstruktur, Verbindungsbedingungen und Indexverwendung bestimmt.

Wir sollten dies auf der Grundlage des tatsächlichen Geschäftsszenarios entscheiden, beispielsweise des obigen Datenszenarios: Wenn Sie Klassen mit Schülern zurückgeben müssen, verwenden Sie den Inner Join. Wenn Sie alle Klassen ausgeben müssen, verwenden Sie den Left Join. Wenn Sie alle Schüler ausgeben müssen, verwenden Sie den Right Join.

3. Leistungsüberlegungen: MySQL verarbeitet verbundene Tabellen während der Laufzeit gemäß den Zuordnungsbedingungen. Diese Verarbeitung kann sehr ressourcenintensiv sein. Je mehr Tabellen verbunden sind, desto schwerwiegender ist die Leistungsverschlechterung. Daher ist es notwendig, unnötige Verbindungen und Felder, die nicht angezeigt werden müssen, zu analysieren und zu entfernen.

Als mein Projektteam früher alte Geschäftscodes optimierte, stellten wir fest, dass aufgrund von Geschäftsänderungen einige Daten nicht mehr angezeigt werden mussten und die entsprechende Verbindung nicht mehr benötigt wurde. Nach dem Entfernen wurde die Leistung erheblich verbessert.

Oben finden Sie ausführliche Informationen zum Prinzip und zur Anwendung der MySQL-Verbindungsabfrage. Weitere Informationen zur MySQL-Verbindungsabfrage finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!