Nutzung von verschachtelten Abfragen in SQL

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 Abfrage, auch bekannt als Unterabfrage oder verschachtelte Abfrage. Eine verschachtelte Abfrage ist eine SELECT-Anweisung, die typischerweise in Klammern eingeschlossen 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 werden auch Aggregatfunktionen innerhalb einer verschachtelten Abfrage verwenden, um die Datenwerte mit den sortierten Datenwerten zu vergleichen, die Sie für die WHERE- und LIKE-Klauseln angegeben haben.

Voraussetzungen für verschachtele Abfragen in SQL

Um dieser Anleitung zu folgen, benötigen Sie einen Computer mit einem relationalen Datenbankmanagementsystem (RDBMS), das SQL verwendet. Die Anweisungen und Beispiele in diesem Tutorial wurden in der folgenden Umgebung validiert:

• Ein Server mit Ubuntu 20.04, ein Nicht-Root-Benutzer mit sudo-Administrationsrechten und aktivierter Firewall. Folgen Sie unserer Initial Server Setup mit Ubuntu 20.04, um loszulegen.
• MySQL auf dem Server installiert und abgesichert. Folgen Sie unserer Anleitung „How To Install MySQL on Ubuntu 20.04“, um dies einzurichten. Diese Anleitung geht davon aus, dass Sie auch einen Nicht-Root-MySQL-Benutzer eingerichtet haben, wie in Schritt 3 dieser Anleitung beschrieben.

Hinweis: Bitte beachten Sie, dass viele relationale Datenbankmanagementsysteme ihre eigenen einzigartigen Implementierungen von SQL verwenden. Obwohl die in diesem Tutorial beschriebenen Befehle in den meisten RDBMS funktionieren, können Syntax oder Ausgabe abweichen, wenn Sie sie auf einem anderen System als MySQL testen.

Um in diesem Tutorial verschachtelte Abfragen zu üben, benötigen Sie eine Datenbank und eine Tabelle, die mit Beispieldaten geladen sind. Wenn Sie noch keine zum Einfügen bereit haben, können Sie den folgenden Abschnitt „Connecting to MySQL and Setting up a Sample Database“ lesen, um zu erfahren, wie Sie eine Datenbank und Tabelle erstellen. Dieses Tutorial wird sich durchgehend auf diese Beispieldatenbank und Tabelle beziehen.

Verbindung zu MySQL und Einrichtung einer Beispieldatenbank

Falls Ihre SQL-Datenbank auf einem Remote-Server läuft, stellen Sie über SSH eine Verbindung zu Ihrem Server von Ihrem lokalen Rechner her:

ssh sammy@your_server_ip

Öffnen Sie als nächstes die MySQL-Eingabeaufforderung und ersetzen Sie „sammy“ durch Ihre MySQL-Benutzerinformationen:

mysql -u sammy -p

Erstellen Sie eine Datenbank namens zooDB:

CREATE DATABASE zooDB;

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

Ausgabe
Query OK, 1 row affected (0.01 sec)

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

USE zooDB;

Ausgabe

Datenbank gewechselt

Nachdem Sie die Datenbank ausgewählt haben, erstellen Sie eine Tabelle darin. Für dieses Beispiel-Tutorial erstellen wir eine Tabelle, die Informationen über Besucher des Zoos speichert. Diese Tabelle wird die folgenden sieben Spalten enthalten:

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

Erstellen Sie eine Tabelle namens 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)
);

Fügen Sie als nächstes 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', 'Erwachsener', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Erwachsener', 'Tagespass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Erwachsener', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Kind', 'Jugendpass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Erwachsener', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Erwachsener', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Kind', 'Jugendpass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Kind', 'Jugendpass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Erwachsener', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Erwachsener', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Erwachsener', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Erwachsener', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Kind', 'Tagespass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Kind', 'Tagespass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Erwachsener', 'Tagespass', 62.0, 1);

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

Nachdem Sie die Daten eingefügt haben, können Sie mit der Verwendung verschachtelter Abfragen in SQL beginnen.

Verschachtelte Abfragen in SQL mit SELECT verwenden

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 eine andere Operation eingebettet ist. Eine verschachtelte Abfrage kann alle Elemente verwenden, die in einer regulären Abfrage verwendet werden, und jede gültige Abfrage kann in eine andere Operation eingebettet werden, um eine verschachtelte Abfrage zu werden. Beispielsweise kann eine verschachtelte Abfrage in INSERT- und DELETE-Operationen eingebettet werden. Abhängig von der Operation sollte eine verschachtelte Abfrage durch das Einfügen der Anweisung in die richtige Anzahl von Klammern eingebettet werden, um eine bestimmte Reihenfolge der Operationen einzuhalten. Eine verschachtelte Abfrage ist auch in Szenarien nützlich, in denen Sie mehrere Befehle in einer Abfrageanweisung ausführen möchten, anstatt mehrere zu schreiben, um das gewünschte Ergebnis zurückzugeben.

Um verschachtelte Abfragen besser zu verstehen, illustrieren wir, wie sie mit den Beispieldaten aus dem vorherigen Schritt nützlich sein können. Angenommen, Sie möchten alle Gäste in der Tabelle guests finden, die den Zoo häufiger als der Durchschnitt besucht haben. 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);

Eine Abfrage mit dieser Syntax gibt jedoch einen Fehler zurück:

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

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

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

SELECT AVG(total_visits) FROM guests;

Ausgabe
+-----------------+
| 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;

Ausgabe
+----------+---------+------------+
| 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 Ergebnis mit einer einzigen Abfrage erhalten, indem Sie die erste Abfrage (SELECT AVG(total_visits) FROM guests;) in die zweite einbetten. Beachten Sie, dass bei verschachtelten Abfragen die richtige Anzahl an Klammern verwendet werden muss, um die gewünschte Operation abzuschließen. Dies liegt daran, dass die verschachtelte Abfrage die erste durchzuführende Operation ist:

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

Ausgabe
+------------+-----------+--------------+
| 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)

Laut dieser Ausgabe haben fünf Gäste mehr als der Durchschnitt besucht. Diese Informationen könnten nützliche Einblicke bieten, um kreative Möglichkeiten zu finden, um sicherzustellen, dass aktuelle Mitglieder den Zoo regelmäßig besuchen und ihre Mitgliedskarten jedes Jahr erneuern. Außerdem zeigt dieses Beispiel den Wert der Verwendung einer verschachtelten Abfrage in einer vollständigen Anweisung für die gewünschten Ergebnisse, anstatt zwei separate Abfragen auszuführen.

Verschachtelte Abfragen in SQL mit INSERT verwenden

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

Um dies zu veranschaulichen, nehmen wir an, dass ein angeschlossener Zoo einige Informationen über Ihre Gäste anfordert, da er daran interessiert ist, Gästen, die ein „Resident“-Abonnement an ihrem Standort erwerben, einen 15% Rabatt zu gewähren. Erstellen Sie dazu eine neue Tabelle namens upgrade_guests, die sechs Spalten enthält, mit CREATE TABLE. Achten Sie genau auf die Datentypen, wie z. B. int und varchar, und die maximale Anzahl von Zeichen, die sie enthalten können. Wenn diese nicht mit den ursprünglichen Datentypen aus der Tabelle guests, die Sie im Abschnitt Einrichten einer Beispieldatenbank erstellt haben, übereinstimmen, erhalten Sie einen Fehler, wenn Sie versuchen, Daten aus der Tabelle guests mit einer verschachtelten Abfrage einzufügen, und die Daten werden nicht korrekt übertragen. Erstellen Sie Ihre Tabelle mit den 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)
);

Aus Konsistenz- und Genauigkeitsgründen haben wir die meisten Datentypinformationen in dieser Tabelle wie in der Tabelle guests beibehalten. Wir haben auch alle zusätzlichen Spalten entfernt, die wir nicht in der neuen Tabelle haben möchten. Mit dieser leeren Tabelle bereit für die Eingabe, 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, um die Richtung klar anzugeben, wohin die Daten eingefügt werden. Schreiben Sie dann Ihre verschachtelte Abfrage mit der SELECT-Anweisung, um die relevanten Datenwerte abzurufen, und verwenden Sie FROM, um sicherzustellen, dass diese aus der Tabelle guests stammen.

Wenden Sie außerdem den 15% Rabatt auf alle „Resident“-Mitglieder an, indem Sie die Multiplikationsoperation * verwenden, 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 Spalte membership_type zu sortieren. Sie können es weiter eingrenzen, um nur Ergebnisse für „Resident“-Mitgliedschaften mit der LIKE-Klausel auszuwählen und das Prozentzeichen % vor und nach dem Wort „Resident“ in Anführungszeichen zu setzen, um Mitgliedschaften mit derselben Formulierung auszuwählen. 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%';

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

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

SELECT * FROM upgrade_guests;

Ausgabe
+----------+------------+------------+-----------------------+-----------------+--------------+
| 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)

Diese Ausgabe aus Ihrer neuen Tabelle upgrade_guests zeigt, dass die „Resident“-bezogenen Mitgliedschaftsinformationen aus der Tabelle guests korrekt eingefügt wurden. Außerdem wurden die neuen Mitgliedskosten mit dem 15%-Rabatt neu berechnet. Dadurch konnten Sie das Zielpublikum segmentieren und die rabattierten Preise potenziellen neuen Mitgliedern zur Verfügung stellen.

Verschachtelte Abfragen in SQL mit DELETE verwenden

Üben Sie eine verschachtelte DELETE-Abfrage, indem Sie alle Gäste entfernen, die den Zoo häufig besuchen.

Beginnen Sie mit der DELETE FROM-Anweisung, um anzugeben, dass Daten aus der Tabelle upgrade_guests gelöscht werden. Verwenden Sie dann die WHERE-Klausel, um nach allen total_visits zu sortieren, die größer als der Wert sind, der in der verschachtelten Abfrage angegeben ist. Nutzen Sie in der verschachtelten Abfrage SELECT, um den Durchschnitt (AVG) von total_visits für die WHERE-Klausel zu finden. 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);

Ausgabe
Query OK, 2 rows affected (0.00 sec)

Hinweis: Durch das Löschen der Datensätze mit der DELETE-Anweisung aus Ihrer neuen Tabelle werden sie nicht aus der Originaltabelle gelöscht. Führen Sie SELECT * FROM original_table aus, um zu bestätigen, dass alle Originaldatensätze noch vorhanden sind.

SELECT * FROM upgrade_guests ORDER BY total_visits;

Ausgabe
+----------+------------+------------+-----------------------+-----------------+--------------+
| 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, funktionierten die DELETE-Anweisung und die verschachtelte Abfrage einwandfrei und löschten die angegebenen Datenwerte. Diese Tabelle enthält jetzt die Informationen der drei Gäste mit weniger als der durchschnittlichen Anzahl von Besuchen. Dies ist eine gute Basis für den Zoovertreter, um Premium-Pass-Upgrades anzubieten und Besuche zu fördern.

Fazit

Verschachtelte Abfragen in SQL sind nützlich, da sie es Ihnen ermöglichen, hochgradig granulare Ergebnisse zu erzielen. INSERT- und DELETE-Anweisungen mit verschachtelten Abfragen ermöglichen das Einfügen oder Löschen von Daten in einem Schritt. Möchten Sie mehr über die Datenorganisation lernen? Sehen Sie sich unsere Serie zur Verwendung von SQL an.