A MySQL, cada declaració o consulta produeix un resultat o relació temporal. S'utilitza una expressió de taula comuna o CTE anomena aquest conjunt de resultats temporals que existeixen dins de l'àmbit d'execució d'aquesta instrucció en particular, com ara CREATE, INSERT , SELECCIONAR , ACTUALITZACIÓ , ELIMINAR , etc.
vaja conceptes en java
Alguns dels punts clau relacionats amb CTE són:
- Es defineix utilitzant el AMB clàusula.
- La clàusula WITH ens permet especificar més d'un CTE en una sola consulta.
- Un CTE pot fer referència a altres CTE que formen part de la mateixa clàusula WITH, però aquests CTE s'han de definir abans.
- L'abast d'execució de CTE existeix dins de la instrucció particular en què s'utilitza.
Sintaxi MySQL CTE
La sintaxi de MySQL CTE inclou el nom, una llista de columnes opcional i una instrucció/consulta que defineix l'expressió de taula comuna (CTE). Després de definir el CTE, el podem utilitzar com a vista en una consulta SELECT, INSERT, UPDATE i DELETE.
A continuació es mostra la sintaxi bàsica de CTE a MySQL :
WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;
És per garantir que el nombre de columnes dels arguments CTE ha de ser el mateix que el nombre de columnes de la consulta. Si no hem definit les columnes als arguments CTE, utilitzarà les columnes de consulta que defineixen el CTE.
De manera similar a la taula derivada, no es pot emmagatzemar com a objecte i es perdrà tan bon punt s'hagi completat l'execució d'una consulta. Un CTE proporciona una millor llegibilitat i també augmenta el rendiment en comparació amb la taula derivada.
A diferència d'una taula derivada, un CTE és a subconsulta això pot ser auto-referència utilitzant el seu propi nom. També es coneix com CTE recursiu i també es pot fer referència diverses vegades en la mateixa consulta.
Alguns dels punts essencials relacionats amb el CTE recursiu són:
- Es defineix utilitzant la clàusula WITH RECURSIVE.
- Un CTE recursiu ha de contenir una condició de finalització.
- Utilitzarem el CTE recursiu per a la generació de sèries i el recorregut de dades jeràrquiques o estructurades en arbre.
Sintaxi CTE recursiva de MySQL
La següent és la sintaxi bàsica del CTE recursiu a MySQL:
WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;
Aquí, la subconsulta és una consulta de MySQL utilitzant el nom cte_name com el seu propi nom.
Exemples de MySQL CTE
Entendrem com funciona CTE a MySQL utilitzant diversos exemples. Aquí, farem servir una taula 'empleats' per a una manifestació. Suposem que aquesta taula conté les dades següents:
treball informàtic
Executeu la següent afirmació per entendre el concepte de CTE. En aquest exemple, el nom CTE és empleat_a_Califòrnia , la subconsulta que defineix el CTE retorna les tres columnes emp_name, emp_age i city. Per tant, el CTE employee_in_california retornarà tots els empleats que es troben al ciutat de Califòrnia .
Després de definir el CTE employee_in_california, l'hem fet referència al SELECCIONA declaració per seleccionar només aquells empleats que es troben a Califòrnia.
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;
Després d'executar la instrucció anterior, donarà la següent sortida. Aquí, podem veure que el resultat només retorna les dades dels empleats que es troben a Califòrnia.
desviació estàndard numpy
L'exemple de MySQL CTE més avançat
Suposem que tenim una taula anomenada client i ordre que conté les dades següents:
Taula: client
Taula: comandes
Vegeu la declaració següent que explica l'exemple de CTE avançat utilitzant el COMBINACIÓ INTERNA clàusula.
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);
Després d'executar, obtindrem la sortida de la següent manera:
Exemple de CTE recursiu de MySQL
Els exemples següents expliquen el funcionament del CTE recursiu. Considereu la declaració següent que genera una sèrie de primers cinc nombres senars :
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>
En segon lloc, podem utilitzar la clàusula WITH al començament d'una subconsulta o d'una subconsulta de taula derivada com es mostra a continuació:
SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;
En tercer lloc, podem utilitzar la clàusula WITH immediatament anterior a les declaracions SELECT que inclouen una clàusula SELECT de la següent manera:
números romans de l'1 al 100
CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
Beneficis d'utilitzar CTE
- Proporciona una millor llegibilitat de la consulta.
- Augmenta el rendiment de la consulta.
- El CTE ens permet utilitzar-lo com a alternativa al concepte VIEW
- També es pot utilitzar com a encadenament de CTE per simplificar la consulta.
- També es pot utilitzar per implementar consultes recursives fàcilment.