MySQL Subqueries und verschachtelte Operationen – Effiziente Datenfilterung

Einführung

In diesem Lab werden Sie die Leistungsfähigkeit von MySQL-Subqueries und verschachtelten Operationen erkunden. Der Schwerpunkt liegt auf der Verwendung von Subqueries in der WHERE-Klausel, um Daten basierend auf Bedingungen zu filtern, die aus anderen Tabellen oder derselben Tabelle abgeleitet sind.

Sie lernen, wie Sie eine Verbindung zu einem MySQL-Server herstellen, eine Datenbank und Tabellen (Kunden und Bestellungen) erstellen und dann SQL-Abfragen erstellen, die Subqueries verwenden, um Kunden zu identifizieren, die Bestellungen mit einem Gesamtbetrag über einem bestimmten Wert getätigt haben. Das Lab behandelt auch die Verwendung von EXISTS mit einer Subquery, das Testen korrelierter Subqueries und den Vergleich der Leistung von Subqueries.

Subquery in einer WHERE-Klausel schreiben

In diesem Schritt lernen Sie, wie Sie eine Subquery innerhalb der WHERE-Klausel einer SQL-Anweisung verwenden. Eine Subquery ist eine Abfrage, die in eine andere Abfrage verschachtelt ist. Sie ist ein leistungsstarkes Werkzeug zum Abrufen von Daten basierend auf Bedingungen, die aus anderen Tabellen oder derselben Tabelle abgeleitet sind.

Verständnis von Subqueries

Eine Subquery (oder innere Abfrage) ist eine SQL-Abfrage, die in eine größere Abfrage verschachtelt ist. Die Subquery wird zuerst ausgeführt, und ihr Ergebnis wird von der äußeren Abfrage verwendet. Subqueries können in den Klauseln WHERE, SELECT, FROM und HAVING vorkommen.

In der WHERE-Klausel wird eine Subquery typischerweise verwendet, um die Ergebnisse der äußeren Abfrage basierend auf einer Bedingung zu filtern. Die Subquery gibt einen einzelnen Wert oder eine Menge von Werten zurück, die die äußere Abfrage für Vergleiche verwendet.

Szenario

Stellen Sie sich vor, Sie haben zwei Tabellen: customers und orders. Die Tabelle customers enthält Kundeninformationen (z. B. customer_id, name, city), und die Tabelle orders enthält Bestellinformationen (z. B. order_id, customer_id, order_date, total_amount).

Sie möchten alle Kunden finden, die mindestens eine Bestellung mit einem Gesamtbetrag von mehr als 100 $ getätigt haben.

Schritte

Verbindung zum MySQL-Server herstellen:

Öffnen Sie Ihr Terminal und führen Sie den folgenden Befehl aus, um sich als root-Benutzer mit Ihrem MySQL-Server zu verbinden:
```
sudo mysql -u root
```
Sie sollten die MySQL-Eingabeaufforderung sehen: mysql>.

Datenbank und Tabellen erstellen:

Wenn Sie noch keine Datenbank und Tabellen haben, erstellen Sie diese jetzt. Lassen Sie uns eine Datenbank namens labdb und die Tabellen customers und orders erstellen. Führen Sie die folgenden SQL-Befehle in der MySQL-Eingabeaufforderung aus:

CREATE DATABASE IF NOT EXISTS labdb;
USE labdb;

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Beispieldaten einfügen:

Fügen Sie einige Beispieldaten in die Tabellen ein. Führen Sie die folgenden SQL-Befehle in der MySQL-Eingabeaufforderung aus:

INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice Smith', 'New York'),
(2, 'Bob Johnson', 'Los Angeles'),
(3, 'Charlie Brown', 'Chicago'),
(4, 'David Lee', 'Houston');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-01-15', 120.00),
(2, '2023-02-20', 80.00),
(1, '2023-03-10', 150.00),
(3, '2023-04-05', 200.00),
(2, '2023-05-12', 110.00),
(4, '2023-06-18', 90.00);

Die Subquery in der WHERE-Klausel schreiben:

Schreiben Sie nun die Abfrage, um Kunden zu finden, die Bestellungen mit einem Gesamtbetrag von mehr als 100 $ getätigt haben. Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus:
```
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 100);
```
Erklärung:
- Die Subquery (SELECT customer_id FROM orders WHERE total_amount > 100) wählt die customer_id aus der Tabelle orders aus, bei der der total_amount größer als 100 ist.
- Die äußere Abfrage SELECT * FROM customers WHERE customer_id IN (...) wählt alle Spalten aus der Tabelle customers aus, bei denen die customer_id in der Menge der von der Subquery zurückgegebenen customer_ids enthalten ist.

Die Ausgabe beobachten:

Sie sollten die folgende Ausgabe sehen, die die Kunden anzeigt, die Bestellungen mit einem Gesamtbetrag von mehr als 100 $ getätigt haben:

+-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
+-------------+-------------+-----------+
3 rows in set (0.00 sec)

EXISTS mit einer Subquery verwenden

In diesem Schritt lernen Sie, wie Sie den EXISTS-Operator mit einer Subquery in MySQL verwenden. Der EXISTS-Operator wird verwendet, um die Existenz von Zeilen in einer Subquery zu testen. Er gibt TRUE zurück, wenn die Subquery Zeilen zurückgibt, und FALSE andernfalls.

Verständnis von EXISTS

Der EXISTS-Operator wird häufig in der WHERE-Klausel einer SQL-Anweisung verwendet, um Ergebnisse basierend auf der Existenz verwandter Daten in einer anderen Tabelle zu filtern. Er ist eine leistungsstarke Alternative zur Verwendung von IN- oder JOIN-Operationen und kann manchmal effizienter sein, insbesondere bei der Arbeit mit großen Datensätzen.

Im Gegensatz zu IN ruft EXISTS die Daten aus der Subquery nicht tatsächlich ab. Es prüft lediglich, ob Zeilen zurückgegeben werden. Dies kann es schneller als IN machen, wenn Sie nur wissen müssen, ob eine Übereinstimmung existiert, nicht die tatsächlichen Werte.

Szenario

Weiter mit den Tabellen customers und orders aus dem vorherigen Schritt, finden wir nun alle Kunden, die mindestens eine Bestellung getätigt haben.

Voraussetzungen

Stellen Sie sicher, dass Sie den vorherigen Schritt ("Eine Subquery in einer WHERE-Klausel schreiben") abgeschlossen haben und die Datenbank labdb sowie die Tabellen customers und orders mit Daten gefüllt sind.

Schritte

Die Abfrage mit EXISTS schreiben:

Schreiben Sie die Abfrage, um Kunden zu finden, die mindestens eine Bestellung getätigt haben. Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus:
```
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
Erklärung:
- Die äußere Abfrage SELECT * FROM customers c wählt alle Spalten aus der Tabelle customers aus, die mit c aliasiert ist.
- Die Klausel WHERE EXISTS (...) prüft, ob die Subquery Zeilen zurückgibt.
- Die Subquery SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id wählt den Wert 1 (es könnte jeder konstante Wert sein) aus der Tabelle orders, die mit o aliasiert ist, aus, wobei die customer_id in der Tabelle orders mit der customer_id in der Tabelle customers übereinstimmt.
- Der EXISTS-Operator gibt TRUE zurück, wenn die Subquery mindestens eine Zeile zurückgibt, was bedeutet, dass der Kunde mindestens eine Bestellung getätigt hat.

Die Ausgabe beobachten:

Sie sollten die folgende Ausgabe sehen, die alle Kunden anzeigt, die mindestens eine Bestellung getätigt haben:

+-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
|           4 | David Lee   | Houston   |
+-------------+-------------+-----------+
4 rows in set (0.00 sec)

Die Abfrage ändern (Optional):

Ändern wir die Abfrage, um Kunden zu finden, die keine Bestellungen getätigt haben. Dies können Sie tun, indem Sie NOT EXISTS verwenden. Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus:
```
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
Die Ausgabe beobachten:

Da alle Kunden in unseren Beispieldaten Bestellungen getätigt haben, sollte diese Abfrage ein leeres Ergebnis liefern:
```
Empty set (0.00 sec)
```

Korrelierte Subquery testen

In diesem Schritt lernen Sie korrelierte Subqueries in MySQL kennen. Eine korrelierte Subquery ist eine Subquery, die auf eine Spalte aus der äußeren Abfrage verweist. Das bedeutet, dass die Subquery für jede Zeile in der äußeren Abfrage einmal ausgeführt wird.

Verständnis von korrelierten Subqueries

Im Gegensatz zu einer einfachen Subquery, die nur einmal ausgeführt wird, hängt eine korrelierte Subquery für ihre Werte von der äußeren Abfrage ab. Die Subquery verwendet Werte aus der aktuellen Zeile der äußeren Abfrage, um ihr Ergebnis zu bestimmen. Dies macht korrelierte Subqueries für bestimmte Abfragetypen leistungsfähiger, aber auch potenziell weniger effizient als einfache Subqueries, insbesondere bei großen Datensätzen.

Szenario

Weiter mit den Tabellen customers und orders, finden wir alle Kunden, die eine Bestellung mit einem Betrag getätigt haben, der über dem durchschnittlichen Bestellbetrag für alle Bestellungen liegt.

Voraussetzungen

Stellen Sie sicher, dass Sie die vorherigen Schritte abgeschlossen haben und die Datenbank labdb sowie die Tabellen customers und orders mit Daten gefüllt sind.

Schritte

Die korrelierte Subquery schreiben:

Schreiben Sie die Abfrage, um Kunden zu finden, die eine Bestellung mit einem Betrag getätigt haben, der über dem durchschnittlichen Bestellbetrag liegt. Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus:
```
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > (SELECT AVG(total_amount) FROM orders)
);
```
Erklärung:
- Die äußere Abfrage SELECT c.customer_id, c.name FROM customers c wählt die customer_id und den name aus der Tabelle customers aus, die mit c aliasiert ist.
- Die Klausel WHERE EXISTS (...) prüft, ob die Subquery Zeilen zurückgibt.
- Die korrelierte Subquery SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total_amount > (SELECT AVG(total_amount) FROM orders) wählt den Wert 1 aus der Tabelle orders, die mit o aliasiert ist, aus, wobei:
  - o.customer_id = c.customer_id: Dies ist die Korrelation. Die Subquery verweist auf die customer_id aus der customers-Tabelle der äußeren Abfrage.
  - o.total_amount > (SELECT AVG(total_amount) FROM orders): Diese Bedingung prüft, ob der Bestellbetrag höher ist als der durchschnittliche Bestellbetrag über alle Bestellungen. Die Subquery AVG(total_amount) ist eine nicht-korrelierte Subquery, die nur einmal ausgeführt wird, um den durchschnittlichen Bestellbetrag zu ermitteln.

Die Ausgabe beobachten:

Sie sollten die folgende Ausgabe sehen, die die Kunden anzeigt, die Bestellungen mit einem Betrag getätigt haben, der über dem durchschnittlichen Bestellbetrag liegt:

+-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           1 | Alice Smith |
|           3 | Charlie Brown |
+-------------+-------------+
2 rows in set (0.00 sec)

Ein weiteres Beispiel: Den höchsten Bestellbetrag für jeden Kunden ermitteln

Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus, um die ID, den Namen und den höchsten Bestellbetrag jedes Kunden abzurufen. Die korrelierte Subquery berechnet den maximalen Bestellbetrag für jeden Kunden einzeln.
```
SELECT c.customer_id, c.name, (
    SELECT MAX(o.total_amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) AS highest_order_amount
FROM customers c;
```

Die Ausgabe beobachten:

Sie sollten die folgende Ausgabe sehen:

+-------------+-------------+-----------------------+
| customer_id | name        | highest_order_amount  |
+-------------+-------------+-----------------------+
|           1 | Alice Smith |                150.00 |
|           2 | Bob Johnson |                110.00 |
|           3 | Charlie Brown |                200.00 |
|           4 | David Lee   |                 90.00 |
+-------------+-------------+-----------------------+
4 rows in set (0.00 sec)

Subquery-Leistung vergleichen

In diesem Schritt lernen Sie, wie Sie die Leistung verschiedener Arten von Subqueries in MySQL vergleichen. Das Verständnis der Leistungseigenschaften von Subqueries ist entscheidend für das Schreiben effizienter SQL-Abfragen, insbesondere bei der Arbeit mit großen Datensätzen.

Leistungsüberlegungen verstehen

Die Leistung einer Subquery kann von mehreren Faktoren beeinflusst werden, darunter:

Datengröße: Die Größe der an der Abfrage beteiligten Tabellen.
Subquery-Typ: Ob die Subquery korreliert oder nicht-korreliert ist.
Indizierung: Das Vorhandensein und die Effektivität von Indizes auf den Tabellen.
MySQL-Version: Die spezifische verwendete MySQL-Version, da sich die Techniken zur Abfrageoptimierung unterscheiden können.

Szenario

Weiter mit den Tabellen customers und orders, vergleichen wir die Leistung einer Subquery mit IN gegenüber einer Subquery mit EXISTS, um alle Kunden zu finden, die mindestens eine Bestellung getätigt haben.

Voraussetzungen

Stellen Sie sicher, dass Sie die vorherigen Schritte abgeschlossen haben und die Datenbank labdb sowie die Tabellen customers und orders mit Daten gefüllt sind. Um den Leistungsvergleich aussagekräftiger zu gestalten, fügen wir der Tabelle orders weitere Daten hinzu.

Schritte

Weitere Daten zur Tabelle orders hinzufügen:

Um den Leistungsvergleich realistischer zu gestalten, fügen wir der Tabelle orders eine beträchtliche Menge an Daten hinzu. Wir fügen für jeden Kunden 1000 Bestellungen über eine gespeicherte Prozedur ein. Führen Sie die folgenden SQL-Befehle in der MySQL-Eingabeaufforderung aus:

DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000 DO
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
    SET i = i + 1;
  END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;

Erklärung:

Dieses SQL-Skript erstellt eine gespeicherte Prozedur namens insert_many_orders.
Die Prozedur fügt 1000 Bestellungen für jeden der vier Kunden in die Tabelle orders ein.
Nach dem Einfügen der Daten wird die Prozedur gelöscht.

Abfrage mit IN:

Führen Sie die folgende Abfrage mit IN aus, um alle Kunden zu finden, die mindestens eine Bestellung getätigt haben:
```
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
```
Abfrageausführungsplan mit EXPLAIN analysieren:

Verwenden Sie vor der Ausführung der Abfrage den Befehl EXPLAIN, um den Abfrageausführungsplan zu analysieren. Dies gibt Ihnen Einblicke, wie MySQL die Abfrage auszuführen plant, und identifiziert potenzielle Leistungsengpässe. Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus:
```
EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
```
Die Ausgabe von EXPLAIN zeigt Ihnen die zugegriffenen Tabellen, die verwendeten Indizes (falls vorhanden) und die Reihenfolge, in der die Operationen ausgeführt werden. Achten Sie auf die Spalte type, die die Art des verwendeten Joins oder der Zugriffsmethode angibt.
Abfrage mit EXISTS:

Führen Sie die folgende Abfrage mit EXISTS aus, um alle Kunden zu finden, die mindestens eine Bestellung getätigt haben:
```
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
Abfrageausführungsplan mit EXPLAIN analysieren:

Verwenden Sie den Befehl EXPLAIN, um den Abfrageausführungsplan für die EXISTS-Abfrage zu analysieren. Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus:
```
EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
Vergleichen Sie den Ausführungsplan mit dem der IN-Abfrage. Gibt es Unterschiede bei den zugegriffenen Tabellen, den verwendeten Indizes oder den Zugriffsmethoden?
Beobachtungen:

Im Allgemeinen tendiert EXISTS dazu, besser zu performen als IN, wenn die Subquery eine große Anzahl von Zeilen zurückgibt. Dies liegt daran, dass IN die Werte der äußeren Abfrage mit allen von der Subquery zurückgegebenen Werten vergleichen muss, während EXISTS stoppt, sobald es eine Übereinstimmung findet. Die tatsächliche Leistung kann jedoch je nach spezifischer Abfrage, den Daten und dem Datenbanksystem variieren. Sie können die Funktion BENCHMARK() (wie im Originaldokument gezeigt) verwenden, um eine genauere Messung der Ausführungszeit zu erhalten, aber für dieses Labor liefert die Analyse der EXPLAIN-Ausgabe ausreichende Einblicke in die Abfragepläne.
Bereinigung (Optional):

Wenn Sie die Datenbank und die Tabellen bereinigen möchten, können Sie die folgenden Befehle in der MySQL-Eingabeaufforderung ausführen:
```
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
```
Wenn Sie alle Schritte abgeschlossen haben, können Sie den MySQL-Client verlassen, indem Sie Folgendes eingeben:
```
exit
```

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Subqueries innerhalb der WHERE-Klausel einer SQL-Anweisung verwenden, um Daten basierend auf Bedingungen zu filtern, die aus anderen Tabellen oder derselben Tabelle abgeleitet werden. Sie haben geübt, sich mit einem MySQL-Server zu verbinden, eine Datenbank und Tabellen zu erstellen und Beispieldaten einzufügen.

Sie haben die Verwendung des IN-Operators mit einer Subquery untersucht, um Kunden basierend auf verwandten Daten in der orders-Tabelle zu finden. Sie haben auch den EXISTS-Operator als Alternative zu IN kennengelernt und geübt, ihn zu verwenden, um die Existenz verwandter Zeilen zu überprüfen.

Darüber hinaus wurden Sie mit korrelierten Subqueries vertraut gemacht, die auf Spalten aus der äußeren Abfrage verweisen, und haben eine verwendet, um Kunden mit Bestellungen zu finden, die den durchschnittlichen Bestellbetrag übersteigen. Schließlich haben Sie die Leistung von IN- und EXISTS-Subqueries verglichen, indem Sie ihre Ausführungspläne mit dem EXPLAIN-Befehl analysiert haben, um Einblicke zu gewinnen, wie MySQL diese Abfragen verarbeitet.