Aggregatabfrage- und Union-Abfragevorgänge für MySQL-Datenbanken

1. Einfügen der abgefragten Ergebnisse

Grammatik:

in die einzufügende Tabelle einfügen [(Spalte 1, ..., Spalte n)] select {* | (Spalte 1, ..., Spalte n)} aus der abzufragenden Tabelle

Die obige Anweisung kann einige Spalten der abzufragenden Tabelle in einige entsprechende Spalten der neuen Tabelle einfügen.

2. Aggregierte Abfrage

2.1 Einleitung

Aggregierte Abfrage: bezieht sich auf eine Methode zum Durchführen einer teilweisen oder vollständigen statistischen Abfrage der Daten eines Felds in einer Datentabelle (d. h. eine Abfrage, die in der Zeilendimension zusammenführt). Beispielsweise der Durchschnittspreis aller Bücher oder die Gesamtzahl der Bücher usw. In diesen Fällen wird die Aggregationsabfragemethode verwendet.

2.2 Aggregationsfunktionen

Aggregatabfragen können die folgenden allgemeinen Aggregatfunktionen verwenden, die den von SQL bereitgestellten „Bibliotheksfunktionen“ entsprechen:

Auffüllen:

• Wenn beim Abfragen der Zeilenanzahl für eine bestimmte Spalte der Wert einer Zeile null ist, wird diese Zeile im Abfrageergebnis nicht gezählt.
• Beim Summieren von Daten muss der Datentyp numerisch sein; Zeichenfolgen und Daten können nicht summiert werden.
• Wenn kein Syntaxfehler vorliegt, sondern ein Laufzeitfehler auftritt, wird eine Warnmeldung angezeigt. Sie können die Warnmeldung mithilfe der SQL-Anweisung „show warnings“ anzeigen.

Als Nächstes verwenden wir als Beispiel die Tabelle mit dem Namen exam_result mit den folgenden Daten.

2.3 Group-by-Klausel

Durch die Verwendung der vorherigen Aggregatfunktion werden tatsächlich alle Zeilen in der Tabelle kombiniert. Sie können jedoch auch group by verwenden, um eine Gruppenaggregation durchzuführen (fügen Sie nach „group by“ einen angegebenen Spaltennamen hinzu, und Spalten mit demselben Wert in dieser Spalte werden zusammen gruppiert).

Als nächstes zeigen wir ein Beispiel einer Tabelle namens emp mit den folgenden Daten

2.4 mit

Wenn Sie die gruppierten Ergebnisse nach der Gruppierung mithilfe group by “ nach Bedingungen filtern müssen, können Sie where nicht verwenden. Verwenden Sie stattdessen die Klausel „having“.

Beachten:

• where -Anweisung dient zum Filtern vor dem Gruppieren
• having -Anweisung wird zum Filtern nach der Gruppierung verwendet.
• where -Klausel und having -Klausel können gleichzeitig verwendet werden

Beispiel 1: Abfrage von Positionen mit einem Gehalt von über 10.000

3. Gemeinsame Abfrage

3.1 Einleitung

Union-Abfrage : Sie kann die Ergebnismengen mehrerer ähnlicher Auswahlabfragen kombinieren. Das heißt, bei der Durchführung einer Abfrage über mehrere Tabellen besteht die Kernidee darin, das kartesische Produkt zu verwenden

Kartesische Produktidee:

Die Idee der Verwendung des kartesischen Produkts besteht eigentlich darin, die Ergebnisse zweier Tabellen zu permutieren und zu kombinieren. Als nächstes verwenden wir die Idee des kartesischen Produkts, um aus zwei Tabellen A und B eine neue Tabelle C zu erhalten.

Schülertabelle A:

Klassentabelle B:

Neue Tabelle C:

Auffüllen:

• Das Ergebnis des kartesischen Produkts ist immer noch eine Tabelle
• Die Anzahl der Spalten in dieser Tabelle ist die Summe der Spaltenanzahl in den beiden Tabellen.
• Die Zeilenanzahl dieser Tabelle ist das Produkt der Zeilenanzahl der beiden Tabellen.

Über die neu erhaltene Tabelle C können wir die beiden Tabellen A und B verknüpfen, und die Verknüpfung im obigen Beispiel ist die Klassen-ID. Obwohl die beiden Tabellen jetzt verknüpft sind, sind nicht alle Daten in der neuen Tabelle sinnvoll. Beispielsweise sind die Informationen in Zeile 2 tatsächlich falsch. Daher müssen nach dem Verknüpfen der beiden Tabellen einige Einschränkungen hinzugefügt werden, z. B. müssen die Klassen-IDs der Tabellen A und B gleich sein. Zu diesem Zeitpunkt kann eine Tabelle D mit sinnvolleren Daten abgerufen werden.

Neue Tabelle D:

An diesem Punkt können wir eine Multi-Table-Abfrage durchführen

Beachten:

Da die gemeinsame Abfrage das kartesische Produkt verwendet, ist die Anzahl der Zeilen in der neuen Tabelle das Produkt der Vereinigungsmenge aller Tabellen. Daher können die Daten des gemeinsamen Abfrageergebnisses sehr umfangreich sein. Gehen Sie daher mit Vorsicht vor.

Die folgenden Beispiele werden alle durch die Tabelle ausgeführt und erlernt, die durch die folgende SQL-Anweisung erstellt wurde. Wenn Sie im folgenden Inhalt arbeiten möchten, können Sie ihn direkt kopieren und verwenden

Tabelle löschen, wenn Klassen vorhanden sind;
Tabelle löschen, wenn Student vorhanden ist;
Tabelle löschen, falls Kurs vorhanden;
Tabelle löschen, wenn Punktzahl vorhanden ist;

Tabellenklassen erstellen (ID int Primärschlüssel auto_increment, Name varchar(20), `desc` varchar(100));

Tabelle Student erstellen (ID int Primärschlüssel auto_increment, sn varchar(20), Name varchar(20), qq_mail varchar(20),
        Klassen_ID int);

Tabelle „Kurs“ erstellen (ID „int“, Primärschlüssel „auto_increment“, Name „varchar (20)“).

Tabelle erstellen (Punktzahl Dezimalzahl (3, 1), Studenten-ID int, Kurs-ID int);

in Klassen einfügen (Name, „Beschreibung“) - Werte 
('Abteilung Informatik 2019 Klasse 1', 'Studierte Computerprinzipien, C- und Java-Sprachen, Datenstrukturen und Algorithmen'),
('Chinesische Abteilung 2019 Klasse 3', 'Traditionelle chinesische Literatur studiert'),
('Automatisierung 2019 Klasse 5', 'Mechanische Automatisierung studiert');

in student(sn, name, qq_mail, classes_id) Werte einfügen
('09982','Schwarzer Wirbelwind Li Kui','[email protected]',1),
('00835','Bodhi-Patriarch',null,1),
('00391','Nicht zutreffend',null,1),
('00031','Xu Xian','[email protected]',1),
('00054','Ich möchte keinen Abschluss machen',null,1),
('51234','Sprich gut','[email protected]',2),
('83223','sag es mir',null,2),
('09527','Ausländer lernen Chinesisch','[email protected]',2);

in Kurs(Name)-Werte einfügen
('Java'),('Traditionelle chinesische Kultur'),('Computerprinzipien'),('Chinesisch'),('Höhere Mathematik'),('Englisch');

in score(score, student_id, course_id) Werte einfügen
-- Schwarzer Wirbelwind Li Kui (70,5, 1, 1), (98,5, 1, 3), (33, 1, 5), (98, 1, 6),
-- Bodhi Patriarch (60, 2, 1), (59.5, 2, 5),
-- Bai Suzhen (33, 3, 1), (68, 3, 3), (99, 3, 5),
-- Xu Xian (67, 4, 1), (23, 4, 3), (56, 4, 5), (72, 4, 6),
-- Ich möchte nicht abschließen (81, 5, 1), (37, 5, 5),
-- Sprich nett (56, 6, 2), (43, 6, 4), (79, 6, 6),
-- Sag mir
(80, 7, 2),(92, 7, 6);

3.2 Innerer Join

Grammatik:

-- Methode 1:
Wählen Sie die angezeigten Spaltennamen aus Tabelle 1 [Alias ​​Tabelle 1], Tabelle 2 [Alias ​​Tabelle 2] aus, wo die Join-Bedingung;

-- Methode 2: Verwenden Sie [inner] join auf
Wähle den angezeigten Spaltennamen aus Tabelle 1 [Alias ​​Tabelle 1] [inner] und verbinde Tabelle 2 [Alias ​​Tabelle 2] unter Verbindungsbedingung.

Auffüllen:

• Bei der Verwendung einer Abfrage mit mehreren Tabellen werden die darin enthaltenen Spalten wie folgt verwendet, da mehrere Tabellen vorhanden sind: Tabellenname.Spaltenname
• Mit der Methode „Tabellennamenalias“ können Sie der Tabelle einen Alias ​​zuweisen.
• Verwenden Sie die Join-Methode [inner]. Wenn „inner“ weggelassen wird, wird standardmäßig ein innerer Join ausgeführt.

Beispiel 1: Abfrage der Noten von Xu Xian in jedem Kurs

3.3 Äußerer Join

Äußerer Join: unterteilt in linken äußeren Join und rechten äußeren Join. Wenn Sie eine Union-Abfrage verwenden, wird bei Verwendung eines Left Outer Join die linke Tabelle vollständig angezeigt, bei Verwendung eines Right Outer Join die rechte Tabelle vollständig angezeigt.

Äußere Verknüpfungen ähneln inneren Verknüpfungen darin, dass beide kartesische Produkte verwenden. Der innere Join dient dazu, dass alle Daten in den beiden Tabellen 1:1 korrespondieren. Wie kann es also sein, dass es sich nicht um eine 1:1-Entsprechung handelt? Beispielsweise die folgenden beiden Tabellen A und B

Eine Tabelle:

Tabelle B:

Wir haben festgestellt, dass beim Erstellen der neuen Tabelle nach dem kartesischen Produkt der Datensatz mit der ID 3 in Tabelle A keine entsprechenden Daten in Tabelle B hat und der Datensatz mit der Studenten-ID 4 in Tabelle B keine entsprechenden Daten in Tabelle A hat. Daher können diese beiden Tabellen nicht mit der Methode des inneren Joins abgefragt werden, und es muss ein äußerer Join verwendet werden.

Wenn die Left-Join-Methode verwendet wird, sieht die neue Tabelle C wie folgt aus:

Bei Verwendung der richtigen Join-Methode sieht die neue Tabelle D wie folgt aus:

Auffüllen:

• Wenn die Daten in den beiden Tabellen eins zu eins übereinstimmen, ist die Verwendung von äußeren und inneren Verknüpfungen gleichwertig.
• Neben Inner Join, Left Outer Join und Right Outer Join gibt es auch Full Outer Join, aber MySQL unterstützt keine Full Outer Join-Operationen.

Grammatik:

-- Left Join, Tabelle 1 wird vollständig angezeigt. Wähle die angezeigten Spaltennamen aus Tabelle 1 aus [Tabelle 1 Alias] [links], verknüpfe Tabelle 2 [Tabelle 2 Alias] unter Join-Bedingung.

-- Rechts verbinden, Tabelle 2 wird vollständig angezeigt. Wähle die angezeigten Spaltennamen aus Tabelle 1 aus [Tabelle 1 Alias] [rechts] Verbinde Tabelle 2 [Tabelle 2 Alias] unter Verbindungsbedingung.

3.4 Selbstbeitritt

Self-Join: bezieht sich auf das Verbinden derselben Tabelle mit sich selbst für die Abfrage. Durch die Verwendung von Self-Join können tatsächlich „Zeilen in Spalten umgewandelt“ werden, um Operationen durchzuführen.

Warum kann Self-Join Zeilen für den Vorgang in Spalten umwandeln? Angenommen, es gibt eine Tabelle A

Wenn ich die Informationen zu den Studierenden finden möchte, deren student_id 1 ist und deren Punktzahl in Kurs 2 höher ist als in Kurs 3 in der Originaltabelle, muss ich die Zeilen vergleichen, aber dieser Vorgang kann nicht für eine einzelne Tabelle ausgeführt werden.

Nach der Ausführung eines kartesischen Produkts auf sich selbst erhalten wir eine neue Tabelle B

An diesem Punkt stellen wir fest, dass wir, wenn wir ein kartesisches Produkt auf der ursprünglichen Tabelle durchführen, zwei identische Tabellen haben und Operationen zwischen Zeilen durchführen können.

Beispiel: Abfrage von Studenten, deren Java-Ergebnisse höher sind als die Ergebnisse in Computer Principles

3.5 Unterabfragen

Unterabfrage: bezieht sich auf eine in andere SQL-Anweisungen eingebettete select Anweisung, auch verschachtelte Abfrage genannt

Einstufung:

• Einzeilige Unterabfrage: Eine Unterabfrage, die eine einzelne Zeile von Datensätzen zurückgibt
• Mehrzeilige Unterabfrage: Eine Unterabfrage, die mehrere Zeilen zurückgibt (mit „in“ oder „exists“).

Auffüllen:

• Verwenden Sie in , um einen mehrzeiligen Abfrageprozess durchzuführen: Wenn Sie eine Unterabfrage verwenden, führen Sie zuerst die Unterabfrage aus, speichern Sie die Abfrageergebnisse im Speicher und führen Sie dann die äußere Abfrage aus, um entsprechend den Ergebnissen im Speicher zu filtern.
• Verwenden Sie exists , um einen Abfrageprozess für mehrere Zeilen durchzuführen: Führen Sie zuerst die äußere Schleife aus, um möglichst viele Datensätze abzurufen, und fügen Sie sie dann in die Unterabfrage für jede Datensatzzeile ein. Behalten Sie diejenigen bei, die die Bedingungen erfüllen („exists“ dient zum Erkennen, ob das Ergebnis der Unterabfrage eine leere Menge ist).

Zusammenfassend:

Basierend auf der in ist es schnell und für Situationen geeignet, in denen der Ergebnissatz der Unterabfrage relativ klein ist (ein großer Speicher kann ihn nicht aufnehmen).
Basierend auf der Methode zum Schreiben exists ist die Geschwindigkeit langsam und es eignet sich für Situationen, in denen der Unterabfrage-Ergebnissatz relativ groß und die Anzahl der äußeren Abfrageergebnisse relativ gering ist.

Beispiel 1: Abfrage der Klassenkameraden des Klassenkameraden, der keinen Abschluss machen möchte (zuerst müssen Sie die Klasse des Klassenkameraden kennen, der keinen Abschluss machen möchte, und dann die Schüler nach Klasse filtern)

3.6 Abfrage zusammenführen

Zusammenführungsabfrage: Verwenden Sie den Set-Operator union oder union all um die Ausführungsergebnisse mehrerer Auswahlen zusammenzuführen. Bei der Verwendung einer Merge-Abfrage müssen die Felder in den Ergebnismengen der vorherigen und nächsten Abfragen konsistent sein.

Auffüllen:

• union Operator dedupliziert die Daten im Ergebnissatz nicht, union all hingegen schon.
• Die Funktion des Set-Operators ähnelt eigentlich der des Operators or, aber wenn Sie unterschiedliche Tabellen abfragen, kann or nicht verwendet werden.

Beispiel: Informationen zu Kursen mit einer ID kleiner als 3 oder Java anzeigen