MySQL-Unterabfragen und gruppierte Abfragen

Überblick

Unterabfragen sind ein wichtiger Teil von SQL-Abfragen. Sie sind ein Mittel, mit dem wir Daten zwischen mehreren Tabellen aggregieren und beurteilen können, was uns die Verarbeitung komplexer Daten erleichtert. In diesem Abschnitt lernen wir hauptsächlich Unterabfragen kennen.

Bereiten wir zunächst die Daten vor. Hier erstellen wir drei Tabellen: Klassen-, Schüler- und Abschlussnotentabellen für die folgenden Operationen:

Datenbank löschen, falls „Helenlyn_Class“ vorhanden ist;
Datenbank „Helenlyn_Class“ erstellen;

/*Klassentabelle*/
DROP TABLE, WENN `Klassen` EXISTIEREN;
CREATE TABLE `Klassen` (
 `classid` int Primärschlüssel AUTO_INCREMENT Kommentar 'Klassen-ID',
 `classname` varchar(30) DEFAULT NULL Kommentar 'Klassenname'
) ENGINE=InnoDB-Kommentar „Klassentabelle“;

einfügen in `Klassen`(`Klassenname`)
Werte ('Mittelschule 1. Klasse'), ('Mittelschule 2. Klasse'), ('Mittelschule 3. Klasse');

/*Studententabelle: Hier nehmen wir an, dass sowohl die Studenten-ID als auch der Name eindeutig sind*/

DROP TABLE, WENN „Studenten“ EXISTIEREN;
CREATE TABLE `Studenten` (
 `studentid` int Primärschlüssel NOT NULL AUTO_INCREMENT Kommentar 'Studenten-ID',
 `studentname` varchar(20) DEFAULT NULL Kommentar 'Studentenname',
 `score` DECIMAL(10,2) DEFAULT NULL Kommentar 'Abschlussnote',
 `classid` int(4) DEFAULT NULL Kommentar 'Die Klassen-ID aus der Klassen-ID in der Klassentabelle'
)ENGINE=InnoDB-Kommentar „Studententabelle“;
in `students`(`studentname`,`score`,`classid`) Werte einfügen
('Marke',97,5,1),('Helen',96,5,1),('Lyn',96,1),('Sol',97,1),('Weng',100,1),('Diny',92,7,1),
('b1',81,2),('b2',82,2),('b3',83,2),('b4',84,2),('b5',85,2),('b6',86,2),
('c1',71,3),('c2',72.5,3),('c3',73,3),('c4',74,3),('c5',75,3),('c6',76,3);


/*Rangliste der Abschlussprüfungsergebnisse*/
Tabelle löschen, wenn „Ergebnisse“ vorhanden sind;
Tabelle `Ergebnisse` erstellen(
 `scoregrad` varchar(3) Primärschlüssel Kommentar 'Note: S, A, B, C, D',
 `downset` int Kommentar 'Untergrenze der Punktebewertung',
 `verärgert` int Kommentar 'Obergrenze der Punktebewertung'
) Kommentar „Rangliste der Abschlussprüfungsnoten“;
INSERT INTO `scores`-Werte ('S', 91, 100), ('A', 81, 90), ('B', 71, 80), ('C', 61, 70), ('D', 51,60);

Unterabfragen

SQL unterstützt die Erstellung von Unterabfragen, also Abfragen, die in andere Abfragen verschachtelt sind. Anders ausgedrückt können in einer Select-Anweisung andere Select-Anweisungen vorkommen, die wir Unterabfragen oder innere Abfragen nennen. Die externe Auswahlanweisung wird als Hauptabfrage oder äußere Abfrage bezeichnet.

Unterabfrageklassifizierung

Nach den Abfrageergebnissen

1. Einzelne Zeile und einzelne Spalte (skalare Unterabfrage): Gibt den Inhalt einer bestimmten Spalte zurück, der als einwertige Daten verstanden werden kann.

2. Einzelne Zeile und mehrere Spalten (Zeilenunterabfrage): Gibt den Inhalt mehrerer Spalten in einer Datenzeile zurück;

3. Mehrzeilige Einzelspalte (Spaltenunterabfrage): Gibt den Inhalt derselben Spalte in mehreren Zeilen zurück, was der Angabe eines Operationsbereichs entspricht.

4. Mehrere Zeilen und mehrere Spalten (Tabellenunterabfrage): Das von der Abfrage zurückgegebene Ergebnis ist eine temporäre Tabelle.

Unterscheiden nach Unterabfrageposition

Unterabfrage nach Auswahl: Es werden nur skalare Unterabfragen unterstützt, d. h. es kann nur ein einzelner Datenwert zurückgegeben werden.

Unterabfrage vom Typ „Von“: Das innere Abfrageergebnis wird als temporäre Tabelle für die erneute Abfrage durch das äußere SQL verwendet, daher werden Tabellenunterabfragen unterstützt.

Wobei oder mit Unterabfrage: bezieht sich auf die Verwendung des Ergebnisses der inneren Abfrage als Vergleichsbedingung der äußeren Abfrage und unterstützt skalare Unterabfragen (einzelne Spalte und einzelne Zeile), Spaltenunterabfragen (einzelne Spalte und mehrere Zeilen) und Zeilenunterabfragen (mehrere Spalten und mehrere Zeilen).

Es wird normalerweise in Verbindung mit den folgenden Methoden verwendet:

1) IN-Unterabfrage: Die innere Abfrageanweisung gibt nur eine Datenspalte zurück, und der Wert dieser Datenspalte wird von der äußeren Abfrageanweisung zum Vergleich verwendet.

2) Beliebige Unterabfrage: Solange eine beliebige Vergleichsbedingung in der inneren Unterabfrage erfüllt ist, wird ein Ergebnis als äußere Abfragebedingung zurückgegeben.

3) Alle Unterabfragen: Die von der inneren Unterabfrage zurückgegebenen Ergebnisse müssen alle Bedingungen der inneren Abfrage gleichzeitig erfüllen.

4) Vergleichsoperator-Unterabfrage: Zu den Vergleichsoperatoren, die in Unterabfragen verwendet werden können, gehören >, >=, <=, <, =, <>

Existiert Unterabfrage: Übernimmt das Abfrageergebnis der äußeren Ebene (unterstützt mehrere Zeilen und Spalten) zur inneren Ebene, um zu prüfen, ob die innere Ebene eingerichtet ist. Einfach ausgedrückt wird die äußere Ebene (dh die vorherige Anweisung) nur ausgeführt, wenn letztere true zurückgibt, andernfalls wird sie nicht ausgeführt.

Lassen Sie uns sie einzeln testen.

Unterabfrage nach Auswahl

Es befindet sich nach „Select“ und unterstützt nur skalare Unterabfragen, d. h. es kann nur einen einzelnen Datenwert zurückgeben. Beispielsweise können wir in der obigen Schülerklassentabelle die Anzahl der Schüler in jeder Klasse wie folgt abfragen:

mysql> wähle a.classid als Klassennummer, a.classname als Klassennamen,
(select count(*) from students b where b.classid = a.classid) als Anzahl der Schüler aus Klasse a;
+----------+----------+----------+
| Klassennummer| Klassenname| Anzahl der Schüler|
+----------+----------+----------+
| 1 | Klasse 1, Jahrgangsstufe 9 | 6 |
| 2 | Klasse 2, Jahrgangsstufe 9 | 6 |
| 3 | 3. Klasse, 9. Klasse | 6 |
+----------+----------+----------+
3 Reihen im Set

Um die Klasse abzufragen, zu der eine Studentenmarke gehört, können Sie Folgendes schreiben:

mysql> auswählen
(Wählen Sie den Klassennamen aus den Klassen a, Schüler b, wobei a.classid = b.classid und b.studentname = „Marke“)
als Klasse;
+----------+
| Klasse|
+----------+
| Klasse 1, Jahrgangsstufe 9|
+----------+
1 Reihe im Set

von nach Unterabfrage

Das innere Abfrageergebnis wird als temporäre Tabelle behandelt und das äußere SQL wird für weitere Abfragen bereitgestellt, die Tabellenunterabfragen unterstützen. Allerdings muss die Unterabfrage mit einem Alias ​​versehen werden, sonst kann die Tabelle nicht gefunden werden.

Abfrage der Durchschnittsnote jeder Klasse:

mysql> wähle a.classid,avg(a.score) aus den Studenten, eine Gruppe nach a.classid;

+---------+--------------+
| Klassen-ID | Durchschnitt(a.score) |
+---------+--------------+
| 1 | 96,616667 |
| 2 | 83,500000 |
| 3 | 73,583333 |
+---------+--------------+
3 Reihen im Set

Abfrage der Rangliste der Abschlussbewertungen: Sortieren von S bis niedrig.

mysql> wähle * aus den Punktzahlen, sortiert nach Upset Desc;

+--------------+---------+-------+
| Punktestand | niedergeschlagen | verärgert |
+--------------+---------+-------+
| S | 91 | 100 |
| EIN | 81 | 90 |
| B | 71 | 80 |
| C | 61 | 70 |
| D | 51 | 60 |
+--------------+---------+-------+
5 Reihen im Set

Wenn Sie die Durchschnittspunktzahl jeder Klasse anhand der Ergebnisse der beiden Abfragen ermitteln möchten, können Sie die Unterabfrage nach from verwenden. Der Code lautet wie folgt:

Wählen Sie a.classid als Klassen-ID, a.avgscore durchschnittliche Abschlussnote, b.scoregrad Bewertung von
(wählen Sie classid,avg(score) als Durchschnittsscore aus der Studentengruppe nach classid) als,
Punktzahl b, wobei a.avgscore zwischen b.downset und b.upset liegt;

+--------+--------------+----------+
| Klassen-ID | durchschnittliche Abschlussnote | Bewertung |
+--------+--------------+----------+
| 1 | 96,616667 | S |
| 2 | 83,500000 | EIN |
| 3 | 73,583333 | B |
+--------+--------------+----------+
3 Reihen im Set

Bei Untertabellenabfragen muss ein Alias ​​angegeben werden, sonst erscheint die Meldung: Jede abgeleitete Tabelle muss ihren eigenen Alias ​​haben. Sie können es versuchen.

Where und Unterabfragen

Gemäß dem, was wir oben erwähnt haben, können Sie nach „where“ oder „having“ drei Methoden verwenden: Skalare Unterabfrage (Unterabfrage für eine Zeile und eine Spalte); Spaltenunterabfrage (Unterabfrage für eine Spalte und mehrere Zeilen); Zeilenunterabfrage (mehrere Zeilen und mehrere Spalten);

Er hat folgende gemeinsame Merkmale:

1. Unterabfragen werden im Allgemeinen in Klammern eingeschlossen.

2. Unterabfragen werden grundsätzlich auf der rechten Seite der Bedingungen platziert.

3. Skalare Unterabfragen, im Allgemeinen mit einzeiligen Operatoren und mehrzeiligen Operatoren >, <, >=, <=, =, <> verwendet

4. Spaltenunterabfrage, normalerweise mit Mehrzeilenoperatoren verwendet

5. Verwenden Sie in, nicht in, all und any. in bezieht sich auf ein beliebiges Element in der Liste. any vergleicht ein beliebiges Element in der Liste. Wenn score>any(60,70,80), dann score>60. all vergleicht alle Elemente in der Liste. Wenn score>(60,70,80), muss score>80 sein.

Anwendung für einzelne skalare Unterabfragen

Das heißt, auf „where“ oder „having“ folgt nur eine Skalarabfrage, um beispielsweise Studenten abzufragen, die bessere Noten als diny (92,7 Punkte) haben:

mysql> wähle * von Studenten A, wobei A.Score >(wähle B.Score von Studenten B, wobei B.Studentenname='diny');
+-----------+----------+----------+---------+
| Studenten-ID | Studentenname | Punktzahl | Klassen-ID |
+-----------+----------+----------+---------+
| 1 | Marke | 97,5 | 1 |
| 2 | Helene | 96,5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | Sol | 97 | 1 |
| 5 | weng | 100 | 1 |
+-----------+----------+----------+---------+
5 Reihen im Set

Anwendungen für mehrere skalare Unterabfragen

Dabei folgt auf „oder mit“ nur eine Skalarabfrage, um beispielsweise Studenten abzufragen, die schlechtere Noten als diny (92,7 Punkte) haben und deren Unterricht nicht mit dem von diny übereinstimmt:

mysql> select * von Studenten a wo
a.score <(wähle b.score aus Studenten b, wobei b.studentname='diny')
und a.classid <> (wählen Sie b.classid aus Studenten b, wobei b.studentname='diny');
+-----------+----------+----------+---------+
| Studenten-ID | Studentenname | Punktzahl | Klassen-ID |
+-----------+----------+----------+---------+
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72,5 | 3 |
| 15 | c3 | 73 | 3 |
| 16 | c4 | 74 | 3 |
| 17 | c5 | 75 | 3 |
| 18 | c6 | 76 | 3 |
+-----------+----------+----------+---------+
12 Reihen im Set

Unterabfrage + Gruppierungsfunktion

Holen Sie sich die Durchschnittsnoten von jeweils drei Klassen und filtern Sie die Klasseninformationen mit Noten heraus, die niedriger sind als die Durchschnittsnote der gesamten Klasse, indem Sie den Ausdruck „Haben“ verwenden.

mysql> wähle a.classid,avg(a.score) als avgscore von Studenten einer Gruppe nach a.classid
mit Durchschnittspunktzahl < (wählen Sie Durchschnittspunktzahl (von Studenten) aus);
+---------+-----------+
| Klassen-ID | Durchschnittsergebnis |
+---------+-----------+
| 2 | 83,500000 |
| 3 | 73,583333 |
+---------+-----------+
2 Reihen im Set

Beschreibung der Unterabfrage

Spaltenunterabfragen müssen mit Mehrzeilenoperatoren verwendet werden: in (nicht in), any/some, all. Die Verwendung des Schlüsselworts „distinct“ zum Entfernen von Duplikaten kann die Ausführungseffizienz verbessern.

Beispiel Unterabfrage + in: alle Schüler, die nicht in der dritten Klasse sind

mysql> wähle * aus Studenten a, wobei a.classid in (wähle eindeutige b.classid aus Klassen b, wobei b.classid <3);
+-----------+----------+----------+---------+
| Studenten-ID | Studentenname | Punktzahl | Klassen-ID |
+-----------+----------+----------+---------+
| 1 | Marke | 97,5 | 1 |
| 2 | Helene | 96,5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | Sol | 97 | 1 |
| 5 | weng | 100 | 1 |
| 6 | klein | 92,7 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
+-----------+----------+----------+---------+
12 Reihen im Set

Beispiel-Unterabfrage + any: alle Schüler, die nicht in Klasse 3 sind

mysql> wähle * aus Studenten a, wobei a.classid = beliebig (wähle eindeutige b.classid aus Klassen b, wobei b.classid <3);
+-----------+----------+----------+---------+
| Studenten-ID | Studentenname | Punktzahl | Klassen-ID |
+-----------+----------+----------+---------+
| 1 | Marke | 97,5 | 1 |
| 2 | Helene | 96,5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | Sol | 97 | 1 |
| 5 | weng | 100 | 1 |
| 6 | klein | 92,7 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
+-----------+----------+----------+---------+
12 Reihen im Set

Unterabfrage + alle: entspricht nicht in

mysql> wähle * aus Studenten a, wobei a.classid <> alle (wähle unterschiedliche b.classid aus Klassen b, wobei b.classid <3);
+-----------+----------+----------+---------+
| Studenten-ID | Studentenname | Punktzahl | Klassen-ID |
+-----------+----------+----------+---------+
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72,5 | 3 |
| 15 | c3 | 73 | 3 |
| 16 | c4 | 74 | 3 |
| 17 | c5 | 75 | 3 |
| 18 | c6 | 76 | 3 |
+-----------+----------+----------+---------+
6 Reihen im Set

Beschreibung der Zeilenunterabfrage

Abfrage des Studierenden mit der kleinsten Matrikelnummer aber den besten Noten:

mysql> wähle * aus Studenten a, wobei (a.Studenten-ID, a.Punktzahl) in (wähle max(Studenten-ID), min(Punktzahl) aus Studenten);
+-----------+----------+----------+---------+
| Studenten-ID | Studentenname | Punktzahl | Klassen-ID |
+-----------+----------+----------+---------+
| 19 | lala | 51 | 0 |
+-----------+----------+----------+---------+
1 Reihe im Set

Existiert Unterabfrage

Wird auch als korrelierte Unterabfrage bezeichnet. Sie nimmt das Abfrageergebnis der äußeren Ebene (unterstützt mehrere Zeilen und Spalten) und bringt es zur inneren Ebene, um zu prüfen, ob die innere Ebene eingerichtet ist. Einfach ausgedrückt wird die äußere Ebene (d. h. die vorherige Anweisung) nur ausgeführt, wenn letztere true zurückgibt, andernfalls wird sie nicht ausgeführt.

1. Ergebnis der Exists-Abfrage: 1 oder 0, 1 ist wahr, 0 ist falsch. Das Ergebnis der Exists-Abfrage wird verwendet, um zu bestimmen, ob im Ergebnissatz der Unterabfrage ein Wert vorhanden ist.

2. Die Unterabfrage „exists“ kann im Allgemeinen durch „in“ ersetzt werden. Daher wird „exists“ selten verwendet.

3. Anders als bei den vorherigen Abfragemethoden wird zuerst die Hauptabfrage ausgeführt und dann werden die Ergebnisse der Unterabfrage zum Filtern entsprechend den Ergebnissen der Hauptabfrage verwendet. Da die Unterabfrage die in der Hauptabfrage verwendeten Felder enthält, wird sie auch als korrelierte Unterabfrage bezeichnet.

Beispiel: Abfrage der Klassennamen aller Schüler

mysql> wähle Klassennamen aus Klassen a, wo vorhanden (wähle 1 aus Schülern b, wobei b.classid = a.classid);

+-------------+
|Klassenname|
+-------------+
| Klasse 1, Jahrgangsstufe 9|
| Klasse 2, Jahrgangsstufe 9|
| Klasse 3, Klasse 9|
+-------------+
3 Reihen im Set

Verwenden Sie stattdessen in (das sieht einfacher aus):

mysql> wähle Klassennamen aus Klassen a, wobei a.classid in (wähle Klassen-ID aus Studenten);

+-------------+
|Klassenname|
+-------------+
| Klasse 1, Jahrgangsstufe 9|
| Klasse 2, Jahrgangsstufe 9|
| Klasse 3, Klasse 9|
+-------------+
3 Reihen im Set

Kombinierte Abfrage

Die meisten SQL-Abfragen bestehen aus einer einzelnen SELECT-Anweisung, die Daten aus einer oder mehreren Tabellen zurückgibt. MySQL ermöglicht Ihnen auch, mehrere Abfragen (mehrere SELECT-Anweisungen) auszuführen und die Ergebnisse als einzigen Abfrageergebnissatz zurückzugeben. Diese kombinierten Abfragen werden oft als Union- oder zusammengesetzte Abfragen bezeichnet.

Mehrere Rückgaben für eine einzelne Tabelle

Kombinieren Sie Ergebnisse aus verschiedenen Abfragen

 wähle cname1,cname2 aus tname, wobei Bedingung1
 Union
 wähle cname1,cname2 aus tname, wobei Bedingung2

Mehrere Tabellen geben die gleiche Struktur zurück

Felder mit gleichem Mengengerüst zusammenfassen

 Wählen Sie t1_cname1, t1_cname2 aus tname1, wobei die Bedingung
 Union
 wähle t2_cname1,t_2cname2 aus tname2, wobei Bedingung

Ich werde hier nicht näher darauf eingehen, hierzu gibt es später ein gesondertes Kapitel.

Zusammenfassen

Sie können aus zwei Aspekten lernen: dem Rückgabetyp der Abfrage und der Position der Unterabfrage in der Anweisung.

Achten Sie auf die Verwendung von in, any, some und all

Egal ob Vergleich, Abfrage oder Zählung, Nullwerte im Feld führen immer zu Missverständnissen. Es wird empfohlen, das Feld beim Erstellen der Tabelle nicht leer zu lassen oder einen Standardwert anzugeben.

Oben sind die Details der MySQL-Unterabfrage und Gruppenabfrage aufgeführt. Weitere Informationen zur MySQL-Abfrage finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!