MYSQL COMMON TABLE EXPRESSION (CTE)

I MySQL producerer hver sætning eller forespørgsel et midlertidigt resultat eller en midlertidig relation. Et almindeligt tabeludtryk eller CTE bruges til navngiv disse midlertidige resultatsæt der findes inden for udførelsesomfanget af den pågældende erklæring, såsom CREATE, INDSÆT , VÆLG , OPDATERING , SLET , etc.

Neena Gupta

Nogle af nøglepunkterne i forbindelse med CTE er:

Det defineres ved at bruge MED klausul.
WITH-klausulen giver os mulighed for at specificere mere end én CTE'er i en enkelt forespørgsel.
En CTE kan referere til andre CTE'er, der er en del af den samme WITH-klausul, men disse CTE'er bør defineres tidligere.
CTE's eksekveringsomfang findes inden for den særlige erklæring, hvori den bruges.

MySQL CTE-syntaks

Syntaksen for MySQL CTE inkluderer navnet, en valgfri kolonneliste og en sætning/forespørgsel, der definerer det fælles tabeludtryk (CTE). Efter at have defineret CTE, kan vi bruge det som en visning i en SELECT, INSERT, UPDATE og DELETE-forespørgsel.

Følgende er den grundlæggende syntaks for CTE i MySQL :

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;

Det er for at sikre, at antallet af kolonner i CTE-argumenterne skal være det samme som antallet af kolonner i forespørgslen. Hvis vi ikke har defineret kolonnerne i CTE-argumenterne, vil den bruge forespørgselskolonnerne, der definerer CTE.

I lighed med den afledte tabel kan den ikke gemmes som et objekt og vil gå tabt, så snart udførelsen af en forespørgsel er fuldført. En CTE giver bedre læsbarhed og øger også ydeevnen sammenlignet med den afledte tabel.

I modsætning til en afledt tabel er en CTE en underforespørgsel det kan være selvreference ved at bruge sit eget navn. Det er også kendt som rekursiv CTE og kan også refereres flere gange i samme forespørgsel.

Nogle af de væsentlige punkter relateret til den rekursive CTE er:

Det defineres ved at bruge WITH RECURSIVE-sætningen.
En rekursiv CTE skal indeholde en terminerende betingelse.
Vi vil bruge den rekursive CTE til seriegenerering og gennemløb af hierarkiske eller træstrukturerede data.

MySQL rekursiv CTE-syntaks

Følgende er den grundlæggende syntaks for rekursiv CTE i MySQL:

 WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;

Her er underforespørgslen en MySQL-forespørgsel, der henviser til sig selv ved at bruge cte_name som sit eget navn.

MySQL CTE eksempler

Lad os forstå, hvordan CTE fungerer i MySQL ved hjælp af forskellige eksempler. Her skal vi bruge et bord 'medarbejdere' til en demonstration. Antag, at denne tabel indeholder følgende data:

symmetrisk forskel

Udfør følgende sætning for at forstå begrebet CTE. I dette eksempel er CTE-navnet medarbejder_i_californien , returnerer underforespørgslen, der definerer CTE'en, de tre kolonner emp_name, emp_age og city. Derfor vil CTE-medarbejder_in_california returnere alle medarbejdere, der er placeret i Californien by .

Efter at have defineret CTE-medarbejder_in_californien, har vi henvist til det i VÆLG erklæring for kun at vælge de medarbejdere, der er placeret i Californien.

 WITH employees_in_california AS ( SELECT * FROM employees WHERE city = &apos;California&apos; ) SELECT emp_name, emp_age, city FROM employees_in_california WHERE emp_age &gt;= 32 ORDER BY emp_name;

Efter at have udført ovenstående sætning, vil den give følgende output. Her kan vi se, at resultatet kun returnerer de medarbejderdata, der er placeret i Californien.

hvilket år computeren blev opfundet

Det mere avancerede MySQL CTE eksempel

Antag, at vi har en tabel ved navn kunde og bestille der indeholder følgende data:

Tabel: kunde

Tabel: ordrer

Se nedenstående erklæring, der forklarer avanceret CTE-eksempel ved hjælp af INDRE JOIN klausul.

 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);

Efter udførelse får vi output som nedenfor:

MySQL Rekursiv CTE-eksempel

Følgende eksempler forklarer, hvordan den rekursive CTE fungerer. Overvej nedenstående udsagn, som genererer en række af første fem ulige tal :

 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 &apos;id&apos; and &apos;n&apos; 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&apos;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>

For det andet kan vi bruge WITH-sætningen i begyndelsen af en underforespørgsel eller en afledt tabelunderforespørgsel som nedenfor:

 SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;

For det tredje kan vi bruge WITH-sætningen umiddelbart foran SELECT-sætninger, der inkluderer en SELECT-sætning som nedenfor:

streng json java

 CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...

Fordele ved at bruge CTE

Det giver bedre læsbarhed af forespørgslen.
Det øger ydelsen af forespørgslen.
CTE giver os mulighed for at bruge det som et alternativ til VIEW-konceptet
Det kan også bruges som kæde af CTE for at forenkle forespørgslen.
Det kan også bruges til nemt at implementere rekursive forespørgsler.