In MySQL, ogni istruzione o query produce un risultato o una relazione temporanea. Viene utilizzata un'espressione di tabella comune o CTE nominare il set di risultati temporanei che esistono nell'ambito di esecuzione di quella particolare istruzione, come CREATE, INSERIRE , SELEZIONARE , AGGIORNAMENTO , ELIMINARE , eccetera.
mouse e tipi di mouse
Alcuni dei punti chiave relativi al CTE sono:
- È definito utilizzando il file CON clausola.
- La clausola WITH ci consente di specificare più di un CTE in una singola query.
- Una CTE può fare riferimento ad altre CTE che fanno parte della stessa clausola WITH, ma tali CTE devono essere definite in precedenza.
- L'ambito di esecuzione di CTE esiste all'interno della particolare istruzione in cui viene utilizzato.
Sintassi MySQL CTE
La sintassi di MySQL CTE include il nome, un elenco di colonne facoltativo e un'istruzione/query che definisce l'espressione di tabella comune (CTE). Dopo aver definito il CTE, possiamo usarlo come vista in una query SELECT, INSERT, UPDATE e DELETE.
Quella che segue è la sintassi di base di CTE in MySQL :
WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;
Serve per garantire che il numero di colonne negli argomenti CTE debba essere uguale al numero di colonne nella query. Se non abbiamo definito le colonne negli argomenti CTE, utilizzerà le colonne di query che definiscono il CTE.
Similmente alla tabella derivata, non può essere archiviata come oggetto e verrà persa non appena completata l'esecuzione di una query. Una CTE fornisce una migliore leggibilità e aumenta anche le prestazioni rispetto alla tabella derivata.
A differenza di una tabella derivata, una CTE è a sottoquery Quello può essere autoreferenzialità utilizzando il proprio nome. È anche noto come CTE ricorsiva e può anche essere fatto riferimento più volte nella stessa query.
Alcuni dei punti essenziali relativi alla CTE ricorsiva sono:
- Viene definito utilizzando la clausola WITH RECURSIVE.
- Una CTE ricorsiva deve contenere una condizione di terminazione.
- Utilizzeremo il CTE ricorsivo per la generazione di serie e l'attraversamento di dati gerarchici o strutturati ad albero.
Sintassi CTE ricorsiva MySQL
Quella che segue è la sintassi di base del CTE ricorsivo in MySQL:
WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;
In questo caso, la sottoquery è una query MySQL che si riferisce a se stessa utilizzando cte_name come nome proprio.
Esempi di CTE MySQL
Cerchiamo di capire come funziona CTE in MySQL utilizzando vari esempi. Qui utilizzeremo una tabella 'dipendenti' per una dimostrazione. Supponiamo che questa tabella contenga i seguenti dati:
concatenare stringhe
Eseguire la seguente istruzione per comprendere il concetto di CTE. In questo esempio, il nome CTE è dipendente_in_california , la sottoquery che definisce la CTE restituisce le tre colonne emp_name, emp_age e city. Pertanto, il CTE Employee_in_california restituirà tutti i dipendenti che si trovano in Città della California .
Dopo aver definito il CTE Employee_in_california, ne abbiamo fatto riferimento nel file SELEZIONARE dichiarazione per selezionare solo i dipendenti che si trovano in California.
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;
Dopo aver eseguito l'istruzione precedente, fornirà il seguente output. Qui possiamo vedere che il risultato restituisce solo i dati dei dipendenti che si trovano in California.
caratteri di escape Java
L'esempio più avanzato di MySQL CTE
Supponiamo di avere una tabella denominata cliente E ordine che contiene i seguenti dati:
Tabella: cliente
Tabella: ordini
Vedere la dichiarazione seguente che spiega l'esempio CTE avanzato utilizzando il file UNIONE INTERNA clausola.
aggiungendo all'array java
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);
Dopo l'esecuzione, otterremo l'output come di seguito:
Esempio di CTE ricorsivo MySQL
Gli esempi seguenti spiegano il funzionamento della CTE ricorsiva. Considera l'affermazione seguente che genera una serie di primi cinque numeri dispari :
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>
In secondo luogo, possiamo utilizzare la clausola WITH all'inizio di una sottoquery o di una sottoquery di una tabella derivata come di seguito:
SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;
In terzo luogo, possiamo utilizzare la clausola WITH immediatamente precedente alle istruzioni SELECT che includono una clausola SELECT come di seguito:
CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
Vantaggi dell'utilizzo di CTE
- Fornisce una migliore leggibilità della query.
- Aumenta le prestazioni della query.
- Il CTE ci permette di usarlo come alternativa al concetto VIEW
- Può anche essere utilizzato come concatenamento di CTE per semplificare la query.
- Può anche essere utilizzato per implementare facilmente query ricorsive.