MySQL With-Klausel

1. Verwendung der MySQL WITH-Klausel a.k.a. Common Table Expression
2. Verwendung der WITH-Klausel von MySQL mit mehreren Common Table Expressions
3. Fazit

In diesem Tutorial lernen wir die MySQL-Klausel WITH kennen, die auch als Common Table Expression (CTE) bekannt ist. CTEs werden immer dann verwendet, wenn Sie schwierige Unterabfragedaten manipulieren möchten.

Außerdem erfahren Sie, wie Sie mit Common Table Expression (CTE) komplexe Abfragen leicht lesbar und verständlich schreiben können. Wir werden auch sehen, ob wir die verschachtelte WITH-Klausel verwenden können oder nicht.

Bitte beachten Sie, dass Common Table Expression vor MySQL Version 8.0 nicht verfügbar war. Sie müssen MySQL Version 8.0 oder höher haben, um es zu verwenden. Sie können hier sehen, was in MySQL Version 8.0 neu ist.

Verwendung der MySQL WITH-Klausel a.k.a. Common Table Expression

Um die WITH-Klausel von MySQL zu verwenden, wollen wir zuerst die CTEs verstehen. Common Table Expressions (CTEs) sind benannte temporäre Ergebnismengen, die nur im Ausführungsbereich innerhalb der Anweisung vorhanden sind, in der sie geschrieben sind.

Durch die Verwendung der WITH-Klausel können Sie einer komplexen Unterabfrage einen Namen zuweisen, den Sie einfach innerhalb der Hauptabfrage verwenden können (SELECT, INSERT, UPDATE oder DELETE). Beachten Sie, dass nicht alle Datenbanken die WITH-Klausel unterstützen.

Sie können eine oder mehrere Unterabfragen und CTEs innerhalb derselben WITH-Klausel verwenden, aber Sie können kein verschachteltes WITH verwenden (ein weiteres WITH innerhalb der WITH-Klausel). Lassen Sie uns eine Tabel namens tb_order erstellen und sie mit einigen Daten füllen, um die WITH-Klausel zu üben.

Beispielcode:

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `practice_with_clause`.`tb_order` (
ORDER_ID INTEGER NOT NULL,
CUSTOMER_FIRST_NAME	VARCHAR(30) NOT NULL,
CUSTOMER_LAST_NAME VARCHAR(30) NOT NULL,
CITY_NAME VARCHAR(64) NOT NULL,
PURCHASED_PRODUCTS VARCHAR(64) NOT NULL,
ORDER_DATE DATE NOT NULL,
PRIMARY KEY (ORDER_ID)
);

Stellen Sie in Ihrer Datenbank unter Tabel sicher, dass Ihre Tabelle erfolgreich erstellt wurde.

Verwenden Sie den folgenden INSERT-Befehl, um die Tabelle mit 7 Datensätzen zu füllen.

# SQL Programming Using MySQL Version 8.27
INSERT INTO practice_with_clause.tb_order 
(ORDER_ID, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, CITY_NAME, PURCHASED_PRODUCTS, ORDER_DATE)
VALUES
(1,'John','Horton', 'Washington', 'Books', '2021-05-03'),
(2,'Banji','Horton',  'Florida', 'Pens', '2010-5-6'),
(3,'Nayya','Sofia',  'South Carolina', 'Books', '2011-10-15'),
(4,'Martell','Daniel',  'Michigan', 'NoteBooks', '2012-12-02'),
(5,'Sana','Preston',  'Michigan', 'White Board Marker', '2013-08-27'),
(6,'Gulraiz','Yonja', 'Washington', 'Books', '2021-05-03'),
(7,'Mashal','Naaz',  'Florida', 'Comic Books', '2019-01-01');

Verwenden Sie nun den Befehl SELECT, um Daten anzuzeigen.

# SQL Programming Using MySQL Version 8.27
SELECT * FROM practice_with_clause.tb_order;

An dieser Stelle verwenden wir die WITH-Klausel, um Common Table Expression zu verwenden und komplexe Unterabfragen wie unten angegeben zu manipulieren.

# SQL Programming Using MySQL Version 8.27
WITH cte_order AS 
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category"
FROM cte_order;

Lassen Sie uns die obige Abfrage zum besseren Verständnis in Abschnitte unterteilen:

Allgemeiner Tabellenausdruck: cte_order

Unterabfrage:

SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders FROM practice_with_clause.tb_order GROUP BY PURCHASED_PRODUCTS

Hauptabfrage:

SELECT AVG(Number_of_Orders) AS "Average Orders Per Category" FROM cte_order;

Beachten Sie, dass der CTE in der Hauptabfrage auf sich selbst verweist, um die Daten zu lesen. Es wird die folgende Ausgabe gemäß meinen Daten zeigen (Ihre Ausgabe kann anders sein).

Common Table Expression-Ausführungsbereich

Wie gesagt, funktioniert CTE nur innerhalb seines Ausführungsbereichs, wie? Siehe folgenden Screenshot.

Wenn Sie nur den mit dem grünen Kästchen hervorgehobenen Code auswählen, bleiben Sie im Ausführungsbereich des CTE mit dem Namen cte_order, aber wenn Sie nur den Code im roten Kästchen auswählen, befinden Sie sich jetzt außerhalb des Ausführungsbereichs und können nicht darauf verweisen Gemeinsamer Tabellenausdruck namens cte_order. Das bedeutet, dass Sie den CTE innerhalb derselben WITH-Klausel referenzieren können, in der er geschrieben ist.

Verwendung der WITH-Klausel von MySQL mit mehreren Common Table Expressions

Lassen Sie uns die WITH-Klausel üben, indem wir mehrere allgemeine Tabellenausdrücke verwenden.

WITH 
cte_order AS 
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
),
cte_location AS
(
SELECT COUNT(CITY_NAME) as City
FROM practice_with_clause.tb_order
WHERE CITY_NAME = 'Washington'
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category", City
FROM cte_order,cte_location;

Nun sehen Sie die folgende Ausgabe.

In ähnlicher Weise können wir auch auf einen gemeinsamen Tabellenausdruck verweisen, der zuvor von einem anderen CTE definiert wurde. Stellen Sie sicher, dass beide Common Table Expressions in derselben WITH-Klausel geschrieben sind.

Fazit

In Anbetracht der obigen Diskussion sind wir zu dem Schluss gekommen, dass die WITH-Klausel verwendet wird, um den Vorteil des Common Table Expression zu nutzen, der hilft, die schwierigen Unterabfragen zu manipulieren. Wir können mehrere Unterabfragen und allgemeine Tabellenausdrücke innerhalb derselben WITH-Klausel verwenden, aber keine verschachtelte WITH-Klausel haben. Wir können auch nicht auf die CTEs aus verschiedenen WITH-Klauseln verweisen.