Grundlegende Verwendung von Unterabfragen in MySQL

1. Unterabfragedefinition

Definition:

Unterabfragen ermöglichen Ihnen, eine Abfrage in einer anderen zu verschachteln.

Eine Unterabfrage, auch innere Abfrage genannt, wird im Vergleich zu einer inneren Abfrage als äußere Abfrage bezeichnet.

Eine Unterabfrage kann jede Klausel enthalten, die auch eine normale Auswahl enthalten kann, wie etwa distinct, group by, order by, limit, join und union; die entsprechende äußere Abfrage muss jedoch eine der folgenden Anweisungen sein: select, insert, update, delete, set oder do.

Position der Unterabfrage: in „select“, nach „from“, in „where“. In „group by“ und „order by“ hat dies keine praktische Bedeutung.

2. Unterabfrageklassifizierung

Unterabfragen werden in folgende Kategorien unterteilt:
1. Skalare Unterabfrage: Eine skalare Unterabfrage, die einen einzelnen Wert zurückgibt, die einfachste Form.
2. Spaltenunterabfrage: Der zurückgegebene Ergebnissatz besteht aus N Zeilen und einer Spalte.
3. Zeilenunterabfrage: Der zurückgegebene Ergebnissatz ist eine Zeile mit N Spalten.
4. Tabellenunterabfrage: Der zurückgegebene Ergebnissatz besteht aus N Zeilen und N Spalten.

Verfügbare Operatoren: = > < >= <= <> ANY IN SOME ALL EXISTS

Eine Unterabfrage gibt einen Skalar (nur einen Wert), eine Zeile, eine Spalte oder eine Tabelle zurück. Diese Unterabfragen werden Skalar-, Zeilen-, Spalten- und Tabellenunterabfragen genannt.

Wenn die Unterabfrage einen Skalarwert (nur einen Wert) zurückgibt, kann die äußere Abfrage die Symbole =, >, <, >=, <= und <> zum Vergleich verwenden. Wenn die Unterabfrage keinen Skalarwert zurückgibt und die äußere Abfrage einen Vergleichsoperator verwendet, um das Ergebnis der Unterabfrage zu vergleichen, wird eine Ausnahme ausgelöst.

1. Skalare Unterabfrage:

Dies bedeutet, dass die Unterabfrage einen einzelnen Skalarwert zurückgibt, beispielsweise eine Zahl oder eine Zeichenfolge. Zudem handelt es sich dabei um die einfachste Rückgabeform in einer Unterabfrage. Mit den Operatoren = > < >= <= <> können Sie die skalaren Ergebnisse von Unterabfragen vergleichen. Normalerweise wird die Unterabfrage auf der rechten Seite des Vergleichs platziert.

Beispiel:

SELECT * FROM Artikel WHERE uid = (SELECT uid FROM Benutzer WHERE Status=1 ORDER BY uid DESC LIMIT 1)
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
Wählen Sie * aus Artikel als t, wobei 2 = (Wählen Sie COUNT (*) aus Artikel, wobei Artikel.uid = t.uid)

2. MySQL-Unterabfrage:

Das bedeutet, dass der von der Unterabfrage zurückgegebene Ergebnissatz aus N Zeilen und einer Spalte besteht. Das Ergebnis wird normalerweise von einer Abfrage eines Tabellenfelds zurückgegeben.
Sie können Operatoren wie => < => <= <> verwenden, um die Skalarergebnisse einer Unterabfrage zu vergleichen. Normalerweise wird die Unterabfrage auf der rechten Seite des Vergleichsausdrucks platziert und Sie können Operatoren wie IN, ANY, SOME und ALL verwenden. Sie können Operatoren wie => < => <= <> nicht direkt verwenden, um Skalarergebnisse zu vergleichen.
Beispiel:

 Wählen Sie * aus dem Artikel, wobei uid in (wählen Sie uid aus dem Benutzer, wobei status = 1)
WÄHLEN SIE s1 AUS Tabelle1, WO s1 > BELIEBIG (WÄHLEN SIE s2 AUS Tabelle2)
WÄHLEN SIE s1 AUS Tabelle1, WO s1 > ALLES (WÄHLEN SIE s2 AUS Tabelle2)

NOT IN ist ein Alias ​​für <> ALL und sie sind gleich.

Besondere Umstände

• Wenn Tabelle2 eine leere Tabelle ist, ist das Ergebnis nach ALL TRUE;
• Wenn die Unterabfrage ein Ergebnis wie (0,NULL,1) zurückgibt, wobei s1 größer als das zurückgegebene Ergebnis ist, aber leere Zeilen vorhanden sind, ist das Ergebnis nach ALL UNKNOWN.

Hinweis: Wenn Tabelle2 leer ist, geben die folgenden Anweisungen NULL zurück:

WÄHLEN SIE s1 AUS Tabelle1, WO s1 > (WÄHLEN SIE s2 AUS Tabelle2)
WÄHLEN SIE s1 AUS Tabelle1, WO s1 > ALLES (WÄHLEN SIE MAX(s1) AUS Tabelle2)

3. MySQL-Zeilenunterabfrage:

Das bedeutet, dass der von der Unterabfrage zurückgegebene Ergebnissatz aus einer Zeile und N Spalten besteht. Das Ergebnis der Unterabfrage ist normalerweise der Ergebnissatz, der durch die Abfrage einer Datenzeile in der Tabelle zurückgegeben wird.

Beispiel:

Wählen Sie * aus Tabelle1, wobei (1,2) = (wählen Sie Spalte1, Spalte2 aus Tabelle2)
Hinweis: (1,2) ist gleichbedeutend mit Zeile(1,2)
SELECT * FROM Artikel WHERE (Titel, Inhalt, UID) = (SELECT Titel, Inhalt, UID FROM Blog WHERE Gebot=2)

4. MySQL-Tabellenunterabfrage:

Dies bedeutet, dass der von der Unterabfrage zurückgegebene Ergebnissatz eine Datentabelle mit N Zeilen und N Spalten ist.

Beispiel:

SELECT * FROM Artikel WHERE (Titel, Inhalt, UID) IN (SELECT Titel, Inhalt, UID FROM Blog)

3. Beispiele für Wortsuche

1. JEDE Unterabfrage

Das Schlüsselwort any bedeutet: „Wenn das Vergleichsergebnis WAHR ist, gib für jeden Wert in der von der Unterabfrage zurückgegebenen Spalte WAHR zurück.“

Beispiel: „10 > beliebig(11, 20, 2, 30)“, da 10 > 2, wird diese Beurteilung WAHR zurückgeben; solange 10 mit einem beliebigen Wert im Satz verglichen wird und WAHR erhalten wird, wird WAHR zurückgegeben.

Wählen Sie Tabelle1.Kunden-ID, Stadt, Anzahl (Bestell-ID)
von Tabelle1 zu Tabelle2
auf Tabelle1.Kunden-ID=Tabelle2.Kunden-ID
wobei table1.customer_id<>'tx' und table1.customer_id<>'9you'
Gruppieren nach Kunden-ID
mit Anzahl(Bestell-ID) >
beliebig (
Wählen Sie Anzahl (Bestell-ID)
aus Tabelle2
wobei customer_id='tx' oder customer_id='9you'
Gruppierung nach Kunden-ID);

Die Bedeutung von any ist relativ einfach zu verstehen. Es bedeutet wörtlich irgendeins. Solange eine der Bedingungen erfüllt ist, wird TRUE zurückgegeben.

2. Verwenden Sie IN für Unterabfragen

Die Verwendung von „in“ für Unterabfragen begegnet uns im Alltag häufig, wenn wir SQL schreiben. in bedeutet, ob ein angegebener Wert in diesem Satz enthalten ist. Wenn ja, wird TRUE zurückgegeben, andernfalls FALSE.

in ist ein Alias ​​für „=any“. Wir können es durch „in“ ersetzen, wo immer „=any“ verwendet wird.

Wenn „in“ vorkommt, muss „not in“ vorkommen; „not in“ hat nicht die gleiche Bedeutung wie „<>any“, „not in“ und „<>all“ haben die gleiche Bedeutung.

3. Verwenden Sie SOME für Unterabfragen

Some ist ein Alias ​​von any und wird weniger häufig verwendet.

4. Verwenden Sie ALL für Unterabfragen

alle müssen mit einem Vergleichsoperator verwendet werden. all bedeutet „für alle Werte in der von der Unterabfrage zurückgegebenen Spalte gilt: Wenn der Vergleich WAHR ergibt, WAHR zurückgeben“.

Beispiel: „10 > alle (2, 4, 5, 1)“, da 10 größer als alle Werte im Satz ist, gibt diese Beurteilung TRUE zurück; und wenn es „10 > alle (20, 3, 2, 1, 4)“ ist, dann gibt die Beurteilung FALSE zurück, da 10 kleiner als 20 ist.

Das Synonym von <>all ist not in, was bedeutet, dass nicht alle Werte im Set gleich sind. Dies kann leicht mit <>any verwechselt werden, also achten Sie einfach mehr darauf.

5. Skalare Unterabfrage

Entsprechend der Anzahl der von der Unterabfrage zurückgegebenen Werte können Unterabfragen in skalare Unterabfragen und mehrwertige Unterabfragen unterteilt werden. Wenn Sie einen Vergleichsoperator für eine Unterabfrage verwenden, muss es sich um eine skalare Unterabfrage handeln. Wenn ein Vergleichsoperator für eine Unterabfrage mit mehreren Werten verwendet wird, wird eine Ausnahme ausgelöst.

6. Mehrwertige Unterabfrage

Das Gegenstück zur skalaren Unterabfrage ist die mehrwertige Unterabfrage, die eine Spalte, eine Zeile oder eine Tabelle zurückgibt, die einen Satz bilden. Wir verwenden im Allgemeinen Wörter wie „any“, „in“, „all“ und „some“, um die Ergebnisse der äußeren Abfrage und der Unterabfrage zu beurteilen. Wenn Sie die Wörter any, in, all und some mit einer skalaren Unterabfrage verwenden, erhalten Sie leere Ergebnisse.

7. Unabhängige Unterabfrage

Eine unabhängige Unterabfrage ist eine Unterabfrage, die ausgeführt wird, ohne auf eine äußere Abfrage angewiesen zu sein. Was bedeutet es, sich auf externe Abfragen zu verlassen? Sehen Sie sich zunächst die folgenden beiden SQL-Anweisungen an.

SQL-Anweisung 1: Holen Sie sich die Bestellnummern aller Hangzhou-Kunden.

Wählen Sie die Bestell-ID aus
aus Tabelle2
wobei customer_idin
          (wählen Sie Kunden-ID
          aus Tabelle1
          wobei Stadt = "Hangzhou");
   SQL-Anweisung 2: Holen Sie Benutzer, deren Stadt Hangzhou ist und die Bestellungen haben.
 
wählen *
aus Tabelle1
wobei city='hangzhou' und existiert
                (wählen *
                aus Tabelle2
                wobei table1.customer_id=table2.customer_id);

Die beiden obigen SQL-Anweisungen reichen zur Veranschaulichung des Problems aus, auch wenn die angeführten Beispiele nicht besonders geeignet sind.

Für SQL-Anweisung 1 kopieren wir die Unterabfrage separat und sie kann separat ausgeführt werden, d. h. die Unterabfrage hat nichts mit der äußeren Abfrage zu tun.

Wenn wir für SQL-Anweisung 2 die Unterabfrage separat kopieren, können wir sie nicht separat ausführen. Da die Unterabfrage von SQL-Anweisung 2 von bestimmten Feldern der äußeren Abfrage abhängt, hängt die Unterabfrage von der äußeren Abfrage ab, wodurch eine Korrelation entsteht.

Bei Unterabfragen spielt die Effizienz häufig eine Rolle. Wenn wir eine Select-Anweisung ausführen, können wir das Schlüsselwort „explain“ hinzufügen, um den Abfragetyp, den während der Abfrage verwendeten Index und andere Informationen anzuzeigen. Verwenden Sie beispielsweise Folgendes:

erklärenWählen Sie die Bestell-ID aus
  aus Tabelle2
  wobei customer_idin
            (wählen Sie Kunden-ID
            aus Tabelle1
            wobei Stadt = "Hangzhou");

Wenn bei Verwendung unabhängiger Unterabfragen die maximale Anzahl der Durchläufe des Unterabfrageteils zur Sammlung n und die maximale Anzahl der Durchläufe der äußeren Abfrage m beträgt, können wir dies wie folgt aufzeichnen: O(m+n). Wenn eine korrelierte Unterabfrage verwendet wird, kann die Anzahl der Durchläufe O(m+m*n) erreichen. Wie Sie sehen, sinkt die Effizienz exponentiell. Daher müssen Sie bei der Verwendung von Unterabfragen die Relevanz der Unterabfragen berücksichtigen.

8. Korrelierte Unterabfragen

Eine korrelierte Unterabfrage ist eine Unterabfrage, die auf Spalten in einer äußeren Abfrage verweist. Das heißt, die Unterabfrage wird einmal für jede Zeile in der äußeren Abfrage berechnet. Innerhalb von MySQL wird jedoch eine dynamische Optimierung durchgeführt, die je nach Situation variieren kann. Bei der Verwendung korrelierter Unterabfragen treten am wahrscheinlichsten Leistungsprobleme auf. Was die Optimierung von SQL-Anweisungen betrifft, so ist dies ein sehr umfangreiches Thema. Nur durch das Sammeln praktischer Erfahrungen können wir besser verstehen, wie wir optimieren können.

9.EXISTS-Prädikat

EXISTS ist ein sehr leistungsfähiges Prädikat, mit dem die Datenbank effizient prüfen kann, ob eine bestimmte Abfrage bestimmte Zeilen erzeugt. Dieses Prädikat gibt TRUE oder FALSE zurück, abhängig davon, ob die Unterabfrage Zeilen zurückgibt. Im Gegensatz zu anderen Prädikaten und logischen Ausdrücken gibt EXISTS nicht UNKNOWN zurück, unabhängig davon, ob die Eingabeunterabfrage Zeilen zurückgibt. Für EXISTS ist UNKNOWN FALSE. Mit der obigen Anweisung können wir Benutzer abrufen, deren Stadt Hangzhou ist und die Bestellungen haben.

wählen *
aus Tabelle1
wobei city='hangzhou' und existiert
                (wählen *
                aus Tabelle2
                wobei table1.customer_id=table2.customer_id);

Der Hauptunterschied zwischen IN und EXISTS liegt in der Beurteilung der dreiwertigen Logik. EXISTS gibt immer TRUE oder FALSE zurück, während für IN neben den Werten TRUE und FALSE auch UNKNOWN für NULL-Werte zurückgegeben werden kann. In Filtern wird UNKNOWN jedoch genauso behandelt wie FALSE, sodass der SQL-Optimierer bei Verwendung von IN denselben Ausführungsplan wählt wie bei Verwendung von EXISTS.

Wir haben erwähnt, dass IN und EXISTS fast dasselbe sind, aber wir müssen über NOT IN und NOT EXISTS sprechen. Wenn die Eingabeliste NULL-Werte enthält, wird der Unterschied zwischen NOT EXISTS und NOT IN sehr groß. Wenn die Eingabeliste NULL-Werte enthält, gibt IN immer TRUE und UNKNOWN zurück, sodass NOT IN NOT TRUE und NOT UNKNOWN, also FALSE und UNKNOWN, erhält.

10. Abgeleitete Tabellen

Wie oben erwähnt, kann im von der Unterabfrage zurückgegebenen Wert auch eine Tabelle zurückgegeben werden. Wenn die von der Unterabfrage zurückgegebene virtuelle Tabelle erneut als Eingabe der FROM-Klausel verwendet wird, wird die virtuelle Tabelle der Unterabfrage zu einer abgeleiteten Tabelle. Die grammatische Struktur ist wie folgt:

FROM (Unterabfrageausdruck) AS derived_table_alias

Da abgeleitete Tabellen vollständig virtuelle Tabellen sind, können sie nicht physisch materialisiert werden und sind es auch nicht.

4. Unterabfrageoptimierung

Viele Abfragen erfordern die Verwendung von Unterabfragen. Durch die Verwendung von Unterabfragen können viele SQL-Vorgänge abgeschlossen werden, die logischerweise mehrere Schritte gleichzeitig erfordern, und auch Transaktions- oder Tabellensperren können vermieden werden. Unterabfragen können Abfrageanweisungen sehr flexibel machen, die Ausführungseffizienz von Unterabfragen ist jedoch nicht hoch.

Bei Verwendung einer Unterabfrage muss MySQL eine temporäre Tabelle für die Abfrageergebnisse der inneren Abfrageanweisung erstellen. Anschließend fragt die äußere Abfrageanweisung die Datensätze in der temporären Tabelle ab. Nachdem die Abfrage abgeschlossen ist, muss MySQL diese temporären Tabellen löschen. Daher wird die Geschwindigkeit der Unterabfrage bis zu einem gewissen Grad beeinträchtigt. Bei einer großen abgefragten Datenmenge verstärkt sich dieser Einfluss entsprechend.

In MySQL können Sie Join-Abfragen anstelle von Unterabfragen verwenden. Eine Join-Abfrage erfordert nicht die Erstellung einer temporären Tabelle und ist schneller als eine Unterabfrage.

Verwenden Sie JOIN anstelle einer Unterabfrage

wie:

Beispiel 1:

WÄHLEN SIE * VON t1
WO t1.a1 NICHT in (SELECT a2 FROM t2)
Nach der Optimierung:
WÄHLEN SIE * VON t1
LINKS JOIN t2 AUF t1.a1=t2.a2
Wobei t2.a2 NULL ist

Beispiel 2:

SELECT * FROM Artikel WHERE (Titel, Inhalt, UID) IN (SELECT Titel, Inhalt, UID FROM Blog)
Nach der Optimierung:
Wählen Sie * aus Artikel
Innerjoin-Blog
auf (Artikel.Titel=Blog.Titel UND Artikel.Inhalt=Blog.Inhalt UND Artikel.UID=Blog.UID)

Unterabfragen, die nicht optimiert werden können:
1. MySQL unterstützt keine Unterabfragezusammenführung und Unterabfrageoptimierung von Aggregatfunktionen, während MariaDB eine materialisierte Optimierung für Unterabfragen von Aggregatfunktionen durchführt.
2. MySQL unterstützt keine Abfrageoptimierung aus Unterklauseln, während MariaDB eine Pull-up-Optimierung für Unterabfragen aus Unterklauseln durchführt.
3. MySQL und MariaDB bieten nur eingeschränkte Unterstützung für die Erweiterung von Unterabfragen. Beispielsweise können nur Operationen an Primärschlüsseln zur Optimierung von Pull-up-Unterabfragen verwendet werden.
4. MySQL unterstützt keine EXISTS-Unterabfrageoptimierung. MariaDB führt Semi-Join-Optimierung für EXISTS-assoziierte Unterabfragen durch, optimiert jedoch nicht EXISTS-assoziierte Unterabfragen nicht weiter.
5. MySQL und MariaDB unterstützen keine NOT EXISTS-Unterabfrageoptimierung;
6. MySQL und MariaDB führen Semi-Join-Optimierungen für IN-Unterabfragen und Abfragen durch, die die Semi-Join-Semantik erfüllen, und optimieren dann basierend auf der Kostenbewertung. Die beiden haben unterschiedliche Auswahlmethoden für die Kostenbewertung von Semi-Joins.
7. MySQL unterstützt keine Nicht-in-Unterabfrage-Optimierung. MariaDB verwendet materialisierte Optimierung für nicht korrelierte Nicht-in-Unterabfragen und optimiert keine korrelierten Nicht-in-Unterabfragen.
8. MySQL und MariaDB verwenden die Max-Funktion für nicht korrelierte Unterabfragen > alle, die Min-Funktion für nicht korrelierte Unterabfragen < alle und die Exist-Optimierung für = alle und nicht korrelierte Unterabfragen;
9. Verwenden Sie die Min-Funktion für >einige und >beliebige nicht korrelierte Unterabfragen, verwenden Sie die Max-Funktion für <einige und <beliebige nicht korrelierte Unterabfragen, verwenden Sie Semi-Join zur Optimierung von =beliebigen und =einigen Unterabfragen und verwenden Sie die Exist-Optimierung nur für >einige und >beliebige korrelierte Unterabfragen und <einige und <beliebige korrelierte Unterabfragen.

Dies ist das Ende dieses Artikels über die grundlegende Verwendung von Unterabfragen in MySQL. Weitere relevante MySQL-Unterabfragen 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!