Mysql mit gemeinsamem Tabellenausdruck

Common Table Expression (CTE) ist ein wichtiges Merkmal von MySQL, mit dem ein temporäres Ergebnissatz erzeugt wird. Es kann mit jeder SQL -Anweisung wie Auswahl, Einfügen, Aktualisieren usw. verwendet werden. Die komplizierten Abfragen können durch CTE vereinfacht werden. Der Ergebnissatz einer Abfrage wird als Objekt für die abgeleitete Tabelle zum Zeitpunkt der Abfrageausführung gespeichert. CTE kann jedoch eine Selbstreferenzung sein, was bedeutet. Aus diesem Grund ist die CTE -Leistung besser als die abgeleitete Tabelle. Mit der Klausel wird ein CTE definiert, und mehr als ein CTE kann in einer einzigen Anweisung mit dieser Klausel definiert werden. Wie ein CTE in der Abfrage angewendet werden kann, um sie lesbarer zu machen und die Leistung der Abfrage zu erhöhen, wird in diesem Artikel erläutert.

Vorteile der Verwendung von CTE:

• Es macht die Abfrage lesbarer.
• Es verbessert die Abfrageleistung.
• Es kann als Alternative zur Ansicht verwendet werden.
• Es ist möglich, eine Verkettung von CTE zu erstellen, um die Abfrage zu vereinfachen.
• Rekursive Abfragen können einfach mit CTE implementiert werden.

Syntax:

Mit CTE-Name (Spalte1, Spalte2,… Spalten) als (
Anfrage
)
Wählen Sie * aus cte-name;

Hier können Sie jede SQL -Anweisung als Abfrage definieren, auswählen, aktualisieren, löschen, einfügen oder erstellen Anweisungen. Wenn Sie die Spaltenliste mit Klausel definieren, muss die Anzahl der Spalten in der Abfrage mit der Anzahl der in der Klausel definierten Spalten gleich sein.

Voraussetzung:

Die CTE -Funktion wird von einer MySQL -Version weniger als 8 unterstützt.0. Sie müssen also MySQL 8 installieren.0 Vor dem Üben des Beispiels dieses Artikels. Sie können die aktuell installierte Version von MySQL überprüfen, indem Sie den folgenden Befehl ausführen.

$ mysql -v

Die Ausgabe zeigt, dass MySQL Version 8.0.19 ist im System installiert.

Wenn die richtige Version installiert ist, erstellen Sie eine Datenbank mit dem Namen mydb und erstellen Sie zwei genannte Tabellen Benutzer Und user_profile Mit einigen Daten, um die Verwendung von CTE in MySQL zu kennen. Führen Sie die folgenden SQL -Anweisungen aus, um die Aufgaben zu erledigen. Diese Aussagen erstellen zwei verwandte Tabellen, die benannt sind Benutzer Und user_profile. Als nächstes werden einige Daten in beiden Tabellen durch Einfügenanweisungen eingefügt.

Datenbank erstellen mydb;
Benutze mydb;
Tabellenbenutzer erstellen (Benutzer
Benutzername VARCHAR (50) Primärschlüssel,
Passwort varchar (50) NICHT NULL,
Status varchar (10) nicht null);
Tabelle erstellen user_profile (
Benutzername VARCHAR (50) Primärschlüssel,
Nennen Sie Varchar (50) NICHT NULL,
Adress varchar (50) nicht null,
E -Mail Varchar (50) NICHT NULL,
Fremdschlüssel (Benutzername) Referenzen Benutzer (Benutzername) bei Löschen von Kaskade);
In Benutzerwerte einfügen
('Admin', '7856', 'Active'),
('Staff', '90802', 'Active'),
('Manager', '35462', 'inaktiv');
Einfügen in user_profile -Werte ein
('Admin', 'Administrator', 'Dhanmondi', '[email protected] ')) ,
('Stab', 'Jakir Nayek', 'Mirpur', '[email protected] ')),
('Manager', 'Mehr afroz', 'Eskaton', '[email protected] ');

Verwendung von einfachem CTE:

Hier ein sehr einfacher CTE namens namens CTE_USERS_PROFILE wird erstellt, wenn keine Feldliste mit CTE -Namen mit Klausel definiert ist und alle Daten von der abgerufen wird user_profile Tisch. Als nächstes wird die Auswahlanweisung verwendet, um alle Datensätze von zu lesen CTE_USERS_PROFILE CTE.

Mit CTE_USERS_PROFILE AS (
Wählen Sie * aus user_profile
)
Wählen Sie * aus cte_users_profile;

Die folgende Ausgabe wird nach dem Ausführen der Anweisung angezeigt.

Verwendung eines einfachen CTE mit Spaltenliste:

Sie können CTE genauer erstellen, indem Sie die Feldliste mit CTE -Namen in der Klausel definieren. In diesem Fall entsprechen die mit dem CTE -Namen definierten Feldnamen den in der Abfrage in der With -Klausel definierten Feldnamen. Hier, Name Und Email Felder werden an beiden Stellen verwendet.

Mit CTE_USERS_PROFILE (Name, E -Mail) als (
Wählen Sie Name, E -Mail
Von user_profile
)
Wählen Sie * aus cte_users_profile;

Nach dem Ausführen der obigen Anweisung wird die folgende Ausgabe angezeigt.

Verwendung eines einfachen CTE mit der Klausel:

Die Auswahlanweisung mit der Klausel kann in der CTE -Anweisung wie eine andere Auswahlabfrage definiert werden. Die Auswahl von Abfragen mit Abrufen von Datensätzen von Benutzer Und user_profile Tabellen, in denen die Werte von Benutzername Das Feld ist für beide Tabellen und den Wert von gleich Nutzername ist nicht 'Mitarbeiter''.

Mit cte_users als ((
Wählen Sie Benutzer aus.Benutzername, user_profile.Name, user_profile.Adresse, user_profile.Email
Von Benutzern, user_profile
Wo Benutzer.userername = user_profile.Benutzername und user_profile.Benutzername "Stab"
)
Wählen Sie den Namen als Name, Adresse als Adresse aus
Von cte_users;

Die folgende Ausgabe wird nach dem Ausführen der Anweisung angezeigt.

Verwendung von einfachem CTE mit Gruppen nach Klausel:

Jede Aggregatfunktion kann in der in CTE verwendeten Abfrage verwendet werden. Die folgende CTE -Anweisung zeigt die Verwendung der SELECT -Abfrage mit count () -Funktion an. Die erste Auswahlanweisung wird verwendet, um alle Datensätze von anzuzeigen Benutzer Tabelle und die letzte Auswahlanweisung werden verwendet, um die Ausgabe von CTE anzuzeigen, die die Gesamtzahl der Benutzer aus zählt Benutzer Tisch, die aktiv sind.

Wählen Sie * von Benutzern aus;
Mit cte_users als ((
Wählen Sie Count (*) als Gesamtsumme aus
Von Benutzern
Wobei Status = "aktive" Gruppe nach Status
)
Wählen Sie insgesamt "Gesamtaktive Benutzer" aus
Von cte_users;

Die folgende Ausgabe wird nach dem Ausführen der Anweisung angezeigt.

Verwendung von einfachem CTE mit Gewerkschaftsbetreiber:

Die folgende CTE -Erklärung zeigt die Verwendung des Gewerkschaftsbetreibers in der CTE -Erklärung. Die Ausgabe zeigt die Werte von an Nutzername aus Benutzer Tisch, wo die Status Wert ist 'Inaktiv'und die anderen Werte von Nutzername aus user_profile Tisch.

Mit cte_users als ((
Wählen Sie Benutzer aus.Nutzername
Von Benutzern
Wo Status = 'inaktiv'
UNION
Wählen Sie user_profile.Nutzername
Von user_profile
)
Wählen Sie * aus cte_users;

Die folgende Ausgabe wird nach dem Ausführen der Anweisung angezeigt.

Verwendung eines einfachen CTE mit linksgerichtetem Join:

Die folgende CTE -Anweisung zeigt die Verwendung des linken Join in CTE. Die Ausgabe zeigt die Werte von an Name Und Email Felder von user_profile Tabelle durch Anwenden links Join basierend auf Nutzername Feld zwischen Benutzer Und user_profile Tabellen und wohin wird diese Aufzeichnungen herausgefiltert Benutzer Tabelle, in der der Wert von Status Ist 'Inaktiv''.

Mit cte_users als ((
Wählen Sie Name, E -Mail
Von user_profile
Links Join User
Auf Benutzer.userername = user_profile.Benutzername wo Benutzer.Status = 'inaktiv'
)
Wählen Sie * aus cte_users;

Die folgende Ausgabe wird nach dem Ausführen der Anweisung angezeigt.

Abschluss:

Wenn Sie die Abfrageleistung erhöhen und die Abfrageausgabe schneller erhalten möchten, ist die CTE die bessere Option als andere MySQL -Optionen. Dieser Artikel hilft MySQL -Benutzern, die Verwendung von CTE für die Auswahlabfrage sehr einfach zu lernen.