Wie man verschachtelte Abfragen in SQL verwendet

Einführung

Structured Query Language (SQL) wird verwendet, um Daten in einem relationalen Datenbankmanagementsystem (RDBMS) zu verwalten. Eine nützliche Funktion in SQL ist das Erstellen einer Abfrage innerhalb einer anderen Abfrage, auch bekannt als Unterabfrage oder verschachtelte Abfrage. Eine verschachtelte Abfrage ist eine SELECT-Anweisung, die normalerweise in Klammern eingeschlossen ist und in eine primäre SELECT-, INSERT– oder DELETE-Operation eingebettet ist.

In diesem Tutorial verwenden Sie verschachtelte Abfragen mit den Anweisungen SELECT, INSERT und DELETE. Sie verwenden auch Aggregatfunktionen innerhalb einer verschachtelten Abfrage, um die Datenwerte mit den sortierten Datenwerten zu vergleichen, die Sie mit den WHERE– und LIKE-Klauseln angegeben haben.

Voraussetzungen

Um dieser Anleitung folgen zu können, benötigen Sie einen Computer, auf dem eine Art von relationalem Datenbankmanagementsystem (RDBMS) läuft, das SQL verwendet. Die Anweisungen und Beispiele in diesem Tutorial wurden unter Verwendung der folgenden Umgebung validiert:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL ist auf dem Server installiert und gesichert. Befolgen Sie unsere Anleitung zur Installation von MySQL auf Ubuntu 20.04, um dies einzurichten. Diese Anleitung setzt voraus, dass Sie auch einen nicht-root MySQL-Benutzer eingerichtet haben, wie in Schritt 3 dieser Anleitung beschrieben.

Um das Arbeiten mit verschachtelten Abfragen in diesem Tutorial zu üben, benötigen Sie eine Datenbank und eine Tabelle mit Beispieldaten. Wenn Sie keine bereit haben, können Sie den folgenden Verbindungsaufbau zu MySQL und Einrichten einer Beispieldatenbank-Abschnitt lesen, um zu erfahren, wie Sie eine Datenbank und Tabelle erstellen. In diesem Tutorial wird währenddessen auf diese Beispieldatenbank und -tabelle verwiesen.

Verbindungsaufbau zu MySQL und Einrichten einer Beispieldatenbank

Wenn Ihre SQL-Datenbank auf einem Remote-Server läuft, melden Sie sich von Ihrem lokalen Rechner aus über SSH auf Ihrem Server an:

ssh sammy@your_server_ip

Öffnen Sie dann die MySQL-Eingabeaufforderung und ersetzen Sie sammy durch Ihre MySQL-Benutzerkontoinformationen:

mysql -u sammy -p

Erstellen Sie eine Datenbank mit dem Namen zooDB:

CREATE DATABASE zooDB;

Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie folgende Ausgabe:

Output
Query OK, 1 row affected (0.01 sec)

Um die Datenbank zooDB auszuwählen, führen Sie das folgende USE-Statement aus:

USE zooDB;

Output
Database changed

Nachdem Sie die Datenbank ausgewählt haben, erstellen Sie eine Tabelle darin. Für das Beispiel dieses Tutorials erstellen wir eine Tabelle, die Informationen über Gäste speichert, die den Zoo besuchen. Diese Tabelle wird die folgenden sieben Spalten enthalten:

• guest_id: speichert Werte für Gäste, die den Zoo besuchen, und verwendet den Datentyp int. Dies dient auch als Primärschlüssel, was bedeutet, dass jeder Wert in dieser Spalte als eindeutiger Bezeichner für die jeweilige Zeile fungiert.
• first_name: enthält den Vornamen jedes Gasts unter Verwendung des Datentyps varchar mit maximal 30 Zeichen.
• last_name: verwendet den Datentyp varchar, wiederum mit maximal 30 Zeichen, um den Nachnamen jedes Gasts zu speichern.
• guest_type: enthält den Gästetyp (Erwachsener oder Kind) für jeden Gast unter Verwendung des Datentyps varchar mit maximal 15 Zeichen.
• membership_type: stellt den Mitgliedstyp dar, den jeder Gast besitzt, unter Verwendung des Datentyps varchar, um maximal 30 Zeichen zu speichern.
• membership_cost: speichert die Kosten für verschiedene Mitgliedschaftstypen. Diese Spalte verwendet den Datentyp decimal mit einer Präzision von fünf und einer Skala von zwei, was bedeutet, dass Werte in dieser Spalte fünf Stellen haben können und zwei Stellen rechts vom Dezimalpunkt.
• total_visits: verwendet den Datentyp int, um die Gesamtanzahl der Besuche von jedem Gast zu erfassen.

Erstellen Sie eine Tabelle mit dem Namen guests, die jede dieser Spalten enthält, indem Sie den folgenden CREATE TABLE-Befehl ausführen:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

Anschließend fügen Sie einige Beispieldaten in die leere Tabelle ein:

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

Wenn die Daten eingefügt wurden, können Sie damit beginnen, verschachtelte Abfragen in SQL zu verwenden.

Verwendung von Verschachtelten Abfragen mit SELECT

In SQL ist eine Abfrage eine Operation, die Daten aus einer Tabelle in einer Datenbank abruft und immer eine SELECT-Anweisung enthält. Eine verschachtelte Abfrage ist eine vollständige Abfrage, die in einer anderen Operation eingebettet ist. Eine verschachtelte Abfrage kann alle Elemente einer regulären Abfrage enthalten, und jede gültige Abfrage kann in einer anderen Operation eingebettet werden, um eine verschachtelte Abfrage zu bilden. Zum Beispiel kann eine verschachtelte Abfrage in INSERT– und DELETE-Operationen eingebettet sein. Abhängig von der Operation sollte eine verschachtelte Abfrage eingebettet werden, indem die Anweisung in der richtigen Anzahl von Klammern eingeschlossen wird, um einer bestimmten Reihenfolge von Operationen zu folgen. Eine verschachtelte Abfrage ist auch nützlich in Szenarien, in denen Sie mehrere Befehle in einer Abfrageanweisung ausführen möchten, anstatt mehrere zu schreiben, um Ihre gewünschten Ergebnisse zurückzugeben.

Um verschachtelte Abfragen besser zu verstehen, lassen Sie uns illustrieren, wie sie anhand der Beispieldaten aus dem vorherigen Schritt nützlich sein können. Nehmen wir zum Beispiel an, Sie möchten alle Gäste in der guests-Tabelle finden, die den Zoo häufiger besucht haben als die durchschnittliche Anzahl. Sie könnten annehmen, dass Sie diese Informationen mit einer Abfrage wie der folgenden finden können:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

Allerdings wird eine Abfrage mit dieser Syntax einen Fehler zurückgeben:

Output
ERROR 1111 (HY000): Invalid use of group function

Der Grund für diesen Fehler ist, dass Aggregatfunktionen wie AVG() nicht funktionieren, es sei denn, sie werden innerhalb einer SELECT-Klausel ausgeführt.

Eine Möglichkeit, diese Informationen abzurufen, wäre zunächst eine Abfrage auszuführen, um die durchschnittliche Anzahl der Gästebesuche zu finden, und dann eine weitere Abfrage auszuführen, um Ergebnisse basierend auf diesem Wert zu finden, wie in den folgenden beiden Beispielen:

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

Sie können jedoch dasselbe Ergebnisset mit einer einzigen Abfrage erhalten, indem Sie die erste Abfrage (SELECT AVG(total_visits) FROM guests;) innerhalb der zweiten verschachteln. Beachten Sie, dass bei verschachtelten Abfragen die Verwendung der richtigen Anzahl von Klammern erforderlich ist, um die gewünschte Operation abzuschließen. Dies liegt daran, dass die verschachtelte Abfrage die erste Operation ist, die ausgeführt wird:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

Gemäß dieser Ausgabe besuchten fünf Gäste häufiger als der Durchschnitt. Diese Informationen könnten nützliche Einblicke bieten, um kreative Möglichkeiten zu überlegen, damit aktuelle Mitglieder den Zoo weiterhin häufig besuchen und ihre Mitgliedschaftspässe jedes Jahr erneuern. Darüber hinaus zeigt dieses Beispiel den Wert der Verwendung einer verschachtelten Abfrage in einer einzigen vollständigen Anweisung für die gewünschten Ergebnisse, anstatt zwei separate Abfragen ausführen zu müssen.

Die Verwendung von verschachtelten Abfragen mit INSERT

Mit einer verschachtelten Abfrage sind Sie nicht auf das Einbetten in andere SELECT-Anweisungen beschränkt. Tatsächlich können Sie verschachtelte Abfragen auch verwenden, um Daten in eine vorhandene Tabelle einzufügen, indem Sie Ihre verschachtelte Abfrage in eine INSERT-Operation einbetten.

Um das zu verdeutlichen, nehmen wir an, ein angeschlossener Zoo bittet um einige Informationen über Ihre Gäste, weil sie daran interessiert sind, Gästen, die eine „Resident“-Mitgliedschaft an ihrem Standort erwerben, einen Rabatt von 15% anzubieten. Verwenden Sie dazu CREATE TABLE, um eine neue Tabelle namens upgrade_guests mit sechs Spalten zu erstellen. Achten Sie genau auf die Datentypen, wie z.B. int und varchar, und die maximalen Zeichen, die sie aufnehmen können. Wenn sie nicht mit den ursprünglichen Datentypen aus der guests-Tabelle übereinstimmen, die Sie im Bereich Einrichten einer Beispieldatenbank erstellt haben, erhalten Sie einen Fehler, wenn Sie Daten aus der guests-Tabelle mithilfe einer verschachtelten Abfrage einfügen, und die Daten werden nicht korrekt übertragen. Erstellen Sie Ihre Tabelle mit folgenden Informationen:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

Zur Konsistenz und Genauigkeit haben wir die meisten Informationen zu den Datentypen in dieser Tabelle gleich wie in der guests-Tabelle gehalten. Wir haben auch alle zusätzlichen Spalten entfernt, die wir nicht in der neuen Tabelle möchten. Mit dieser leeren Tabelle bereit für den Einsatz ist der nächste Schritt, die gewünschten Datenwerte in die Tabelle einzufügen.

In dieser Operation schreiben Sie INSERT INTO und die neue upgrade_guests-Tabelle, damit klar ist, wohin die Daten eingefügt werden. Schreiben Sie dann Ihre verschachtelte Abfrage mit der SELECT-Anweisung, um die relevanten Datenwerte abzurufen, und FROM, um sicherzustellen, dass sie aus der guests-Tabelle stammen.

Zusätzlich wenden Sie den 15% Rabatt auf alle „Resident“-Mitglieder an, indem Sie die Multiplikationsmathematische Operation, *, einfügen, um mit 0,85 zu multiplizieren, innerhalb der verschachtelten Abfrageanweisung (membership_cost * 0.85). Verwenden Sie dann die WHERE-Klausel, um nach Werten in der membership_type-Spalte zu sortieren. Sie können es sogar noch weiter einschränken, um nur Ergebnisse für „Resident“-Mitgliedschaften mithilfe der LIKE-Klausel auszuwählen und das Prozentzeichen % vor und nach dem Wort „Resident“ in einfachen Anführungszeichen zu platzieren, um Mitgliedschaften auszuwählen, die demselben Muster folgen, oder in diesem Fall der gleichen Wortwahl. Ihre Abfrage wird wie folgt geschrieben:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

Die Ausgabe zeigt an, dass fünf Datensätze zur neuen upgrade_guests-Tabelle hinzugefügt wurden. Um zu bestätigen, dass die angeforderten Daten erfolgreich von der guests-Tabelle in die leere von Ihnen erstellte upgrade_guests-Tabelle übertragen wurden und die von Ihnen festgelegten Bedingungen für die verschachtelte Abfrage und die WHERE-Klausel erfüllt wurden, führen Sie Folgendes aus:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

Laut dieser Ausgabe aus Ihrer neuen upgrade_guests-Tabelle wurden die „Resident“-bezogenen Gästemitgliedschaftsinformationen aus der guest-Tabelle korrekt eingefügt. Darüber hinaus wurde der neue membership_cost mit dem angewendeten 15% Rabatt neu berechnet. Diese Operation hat dazu beigetragen, das richtige Publikum zu segmentieren und anzusprechen, und die rabattierten Preise sind nun für diese potenziellen neuen Mitglieder leicht verfügbar.

Verwendung von Verschachtelten Abfragen mit DELETE

Um die Verwendung einer verschachtelten Abfrage mit einer DELETE-Anweisung zu üben, nehmen wir an, Sie möchten alle Gäste entfernen, die häufige Besucher sind, weil Sie sich nur darauf konzentrieren möchten, den verbesserten Premium-Pass-Rabatt für Mitglieder zu bewerben, die den Zoo derzeit nicht oft besuchen.

Beginnen Sie diesen Vorgang mit der DELETE FROM-Anweisung, damit klar ist, von wo aus die Daten gelöscht werden, in diesem Fall von der Tabelle upgrade_guests. Verwenden Sie dann die WHERE-Klausel, um alle total_visits zu sortieren, die größer sind als der Betrag, der in der verschachtelten Abfrage angegeben ist. In Ihrer eingebetteten verschachtelten Abfrage verwenden Sie SELECT, um den Durchschnitt, AVG, von total_visits zu finden, damit die vorherige WHERE-Klausel die entsprechenden Datenwerte zum Vergleich hat. Verwenden Sie schließlich FROM, um diese Informationen aus der Tabelle guests abzurufen. Die vollständige Abfrageanweisung lautet wie folgt:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

Bestätigen Sie, dass diese Datensätze erfolgreich aus der Tabelle upgrade_guests gelöscht wurden, und verwenden Sie ORDER BY, um die Ergebnisse nach total_visits in numerischer und aufsteigender Reihenfolge zu organisieren:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

Wie diese Ausgabe zeigt, haben das DELETE-Statement und die verschachtelte Abfrage ordnungsgemäß funktioniert, um die angegebenen Datensätze zu löschen. Diese Tabelle enthält jetzt die Informationen für die drei Gäste mit weniger als der durchschnittlichen Anzahl von Besuchen, was ein großartiger Ausgangspunkt für den Zoovertreter ist, um sie zu kontaktieren und sie zu einem Upgrade auf einen Premium-Pass zu einem ermäßigten Preis zu ermutigen und hoffentlich dazu zu bringen, öfter in den Zoo zu gehen.

Fazit

Verschachtelte Abfragen sind nützlich, da sie es Ihnen ermöglichen, äußerst granulare Ergebnisse zu erhalten, die Sie sonst nur durch separate Abfragen erhalten würden. Darüber hinaus bieten Ihnen die Verwendung von INSERT und DELETE-Statements mit verschachtelten Abfragen eine weitere Möglichkeit, Daten in einem Schritt einzufügen oder zu löschen. Wenn Sie mehr darüber erfahren möchten, wie Sie Ihre Daten organisieren können, schauen Sie sich unsere Serie über Wie man SQL verwendet an.