In MySQL levert elke verklaring of query een tijdelijk resultaat of relatie op. Hiervoor wordt een algemene tabelexpressie of CTE gebruikt noem die tijdelijke resultatenset die bestaan binnen het uitvoeringsbereik van die specifieke instructie, zoals CREATE, INVOEGEN , KIES , UPDATE , VERWIJDEREN , enz.
ongedefinieerde helling
Enkele van de belangrijkste punten met betrekking tot CTE zijn:
- Het wordt gedefinieerd met behulp van de MET clausule.
- Met de WITH-clausule kunnen we meer dan één CTE in één query opgeven.
- Een CTE kan verwijzen naar andere CTE's die deel uitmaken van dezelfde WITH-clausule, maar die CTE's moeten eerder worden gedefinieerd.
- Het uitvoeringsbereik van CTE bestaat binnen de specifieke instructie waarin het wordt gebruikt.
MySQL CTE-syntaxis
De syntaxis van MySQL CTE omvat de naam, een optionele kolomlijst en een instructie/query die de algemene tabelexpressie (CTE) definieert. Nadat we de CTE hebben gedefinieerd, kunnen we deze gebruiken als weergave in een SELECT-, INSERT-, UPDATE- en DELETE-query.
Het volgende is de basissyntaxis van CTE in MySQL :
WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;
Het is bedoeld om ervoor te zorgen dat het aantal kolommen in de CTE-argumenten hetzelfde moet zijn als het aantal kolommen in de query. Als we de kolommen in de CTE-argumenten niet hebben gedefinieerd, worden de querykolommen gebruikt die de CTE definiëren.
Net als de afgeleide tabel kan deze niet als object worden opgeslagen en zal deze verloren gaan zodra de uitvoering van een query is voltooid. Een CTE zorgt voor een betere leesbaarheid en verhoogt ook de prestaties in vergelijking met de afgeleide tabel.
In tegenstelling tot een afgeleide tabel is een CTE a subquery dat kan zo zijn naar zichzelf verwijzend onder gebruikmaking van zijn eigen naam. Het is ook bekend als recursieve CTE en er kan ook meerdere keren naar worden verwezen in dezelfde query.
Enkele van de essentiële punten met betrekking tot de recursieve CTE zijn:
- Het wordt gedefinieerd met behulp van de clausule WITH RECURSIVE.
- Een recursieve CTE moet een beëindigende voorwaarde bevatten.
- We zullen de recursieve CTE gebruiken voor het genereren van reeksen en het doorlopen van hiërarchische of boomgestructureerde gegevens.
MySQL recursieve CTE-syntaxis
Het volgende is de basissyntaxis van recursieve CTE in MySQL:
WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;
Hier is de subquery een MySQL-query die zichzelf verwijst door de cte_name als eigen naam te gebruiken.
MySQL CTE-voorbeelden
Laten we aan de hand van verschillende voorbeelden begrijpen hoe CTE werkt in MySQL. Hier gaan we een tabel gebruiken 'medewerkers' voor een demonstratie. Stel dat deze tabel de volgende gegevens bevat:
Java-methode overschrijven
Voer de volgende instructie uit om het concept van CTE te begrijpen. In dit voorbeeld is de CTE-naam werknemer_in_Californië , retourneert de subquery die de CTE definieert de drie kolommen emp_name, emp_age en city. Daarom retourneert de CTE-werknemer_in_california alle werknemers die zich in de Californië stad .
Nadat we de CTE-werknemer_in_california hebben gedefinieerd, hebben we ernaar verwezen in de SELECTEER verklaring voor het selecteren van alleen die werknemers die in Californië zijn gevestigd.
WITH employees_in_california AS ( SELECT * FROM employees WHERE city = 'California' ) SELECT emp_name, emp_age, city FROM employees_in_california WHERE emp_age >= 32 ORDER BY emp_name;
Na het uitvoeren van de bovenstaande instructie zal het de volgende uitvoer opleveren. Hier kunnen we zien dat het resultaat alleen de werknemersgegevens retourneert die zich in Californië bevinden.
SQL-volgorde willekeurig
Het meer geavanceerde MySQL CTE-voorbeeld
Stel dat we een tabel hebben met de naam klant En volgorde die de volgende gegevens bevat:
Tabel: klant
Tabel: bestellingen
Zie de onderstaande verklaring waarin een geavanceerd CTE-voorbeeld wordt uitgelegd met behulp van de BINNENSTE VERBINDING clausule.
WITH total_customer_2020 AS ( SELECT cust_id, name, occupation FROM customer INNER JOIN orders USING (cust_id) ORDER BY age ) SELECT * FROM orders JOIN total_customer_2020 USING (cust_id);
Na het uitvoeren krijgen we de onderstaande uitvoer:
MySQL recursief CTE-voorbeeld
De volgende voorbeelden leggen de werking van de recursieve CTE uit. Beschouw de onderstaande verklaring die een reeks genereert eerste vijf oneven getallen :
Java converteert tekenreeks naar geheel getal
WITH RECURSIVE odd_num_cte (id, n) AS ( SELECT 1, 1 union all SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>After executing the above statement, it will give the output as below:</p> <img src="//techcodeview.com/img/mysql-tutorial/80/mysql-common-table-expression-6.webp" alt="MySQL Common Table Expression (CTE)"> <p>The above statement consists of two parts one is non-recursive, and another is recursive.</p> <p> <strong>Non-recursive: SELECT 1, 1</strong> </p> <p>This part will produce the initial rows with two columns as 'id' and 'n' and a single row.</p> <p> <strong>Recursive: SELECT id+1, n+2 from odd_num_cte where id <5< strong> </5<></strong></p> <p>This part is responsible for adding rows to the previous output until the terminating condition (id <5) will not be satisfied. when the id reached 5, condition becomes false, and recursion process is terminated.< p> <h3>The WITH clause uses</h3> <p>MySQL provides many contexts to use the WITH clause for creating CTE. Let's discuss one by one in detail.</p> <p>First, we can use the WITH clause at the beginning of SELECT, UPDATE, and DELETE query as below.</p> <pre> WITH ... SELECT ... WITH ... UPDATE ... WITH ... DELETE ... </pre> <p>Second, we can use the WITH clause at the beginning of a subquery or a derived table subquery as below:</p> <pre> SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table; </pre> <p>Third, we can use the WITH clause immediately preceding of SELECT statements that include a SELECT clause as below:</p> <pre> CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ... </pre> <h3>Benefits of using CTE</h3> <ul> <li>It provides better readability of the query.</li> <li>It increases the performance of the query.</li> <li>The CTE allows us to use it as an alternative to the VIEW concept</li> <li>It can also be used as chaining of CTE for simplifying the query.</li> <li>It can also be used to implement recursive queries easily.</li> </ul> <hr></5)></p></5>
Ten tweede kunnen we de WITH-clausule gebruiken aan het begin van een subquery of een afgeleide tabel-subquery, zoals hieronder:
SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;
Ten derde kunnen we de WITH-clausule gebruiken die onmiddellijk voorafgaat aan SELECT-instructies die een SELECT-clausule bevatten, zoals hieronder:
CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
Voordelen van het gebruik van CTE
- Het zorgt voor een betere leesbaarheid van de query.
- Het verhoogt de prestaties van de query.
- Dankzij de CTE kunnen we het gebruiken als alternatief voor het VIEW-concept
- Het kan ook worden gebruikt als aaneenschakeling van CTE om de zoekopdracht te vereenvoudigen.
- Het kan ook worden gebruikt om recursieve zoekopdrachten eenvoudig te implementeren.