Dans MySQL, chaque instruction ou requête produit un résultat ou une relation temporaire. Une expression de table commune ou CTE est utilisée pour nommer ces ensembles de résultats temporaires qui existent dans la portée d'exécution de cette instruction particulière, comme CREATE, INSÉRER , SÉLECTIONNER , MISE À JOUR , SUPPRIMER , etc.
versions Android
Certains des points clés liés au CTE sont :
- Il est défini en utilisant le AVEC clause.
- La clause WITH nous permet de spécifier plusieurs CTE dans une seule requête.
- Un CTE peut référencer d’autres CTE qui font partie de la même clause WITH, mais ces CTE doivent être définis plus tôt.
- La portée d'exécution de CTE existe dans l'instruction particulière dans laquelle il est utilisé.
Syntaxe MySQL CTE
La syntaxe de MySQL CTE comprend le nom, une liste de colonnes facultative et une instruction/requête qui définit l'expression de table commune (CTE). Après avoir défini le CTE, nous pouvons l'utiliser comme vue dans une requête SELECT, INSERT, UPDATE et DELETE.
Voici la syntaxe de base de CTE dans MySQL :
WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;
Il s'agit de garantir que le nombre de colonnes dans les arguments CTE doit être le même que le nombre de colonnes dans la requête. Si nous n'avons pas défini les colonnes dans les arguments CTE, il utilisera les colonnes de requête qui définissent le CTE.
Semblable à la table dérivée, elle ne peut pas être stockée en tant qu'objet et sera perdue dès que l'exécution d'une requête est terminée. Un CTE offre une meilleure lisibilité et augmente également les performances par rapport à la table dérivée.
Contrairement à une table dérivée, un CTE est un sous-requête ça peut être auto-référencement en utilisant son propre nom. Il est également connu sous le nom CTE récursif et peut également être référencé plusieurs fois dans la même requête.
Certains des points essentiels liés au CTE récursif sont :
- Il est défini à l'aide de la clause WITH RECURSIVE.
- Un CTE récursif doit contenir une condition de fin.
- Nous utiliserons le CTE récursif pour la génération de séries et le parcours de données hiérarchiques ou arborescentes.
Syntaxe CTE récursive MySQL
Voici la syntaxe de base du CTE récursif dans MySQL :
WITH RECURSIVE cte_name (column_names) AS ( subquery ) SELECT * FROM cte_name;
Ici, la sous-requête est une requête MySQL qui se réfère elle-même en utilisant cte_name comme son propre nom.
Exemples de CTE MySQL
Comprenons comment fonctionne CTE dans MySQL à l'aide de divers exemples. Ici, nous allons utiliser un tableau 'employés' pour une démonstration. Supposons que ce tableau contienne les données suivantes :
fenêtre.ouvrir
Exécutez l'instruction suivante pour comprendre le concept de CTE. Dans cet exemple, le nom du CTE est employé_en_californie , la sous-requête qui définit le CTE renvoie les trois colonnes emp_name, emp_age et city. Par conséquent, le CTE Employee_in_california renverra tous les employés situés dans le Ville de Californie .
Après avoir défini le CTE Employee_in_california, nous l'avons référencé dans le SÉLECTIONNER déclaration pour sélectionner uniquement les employés situés en Californie.
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;
Après avoir exécuté l’instruction ci-dessus, le résultat suivant sera obtenu. Ici, nous pouvons voir que le résultat renvoie uniquement les données des employés situés en Californie.
types d'arbres binaires
L'exemple MySQL CTE le plus avancé
Supposons que nous ayons une table nommée client et commande qui contient les données suivantes :
Tableau : client
Tableau : commandes
Voir la déclaration ci-dessous qui explique l'exemple CTE avancé utilisant le JOINTURE INTERNE clause.
Qu'est-ce qu'un ordinateur
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);
Après l'exécution, nous obtiendrons le résultat ci-dessous :
Exemple de CTE récursif MySQL
Les exemples suivants expliquent le fonctionnement du CTE récursif. Considérez la déclaration ci-dessous qui génère une série de cinq premiers nombres impairs :
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>
Deuxièmement, nous pouvons utiliser la clause WITH au début d'une sous-requête ou d'une sous-requête de table dérivée comme ci-dessous :
SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;
Troisièmement, nous pouvons utiliser la clause WITH qui précède immédiatement les instructions SELECT qui incluent une clause SELECT comme ci-dessous :
CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
Avantages de l'utilisation du CTE
- Cela permet une meilleure lisibilité de la requête.
- Cela augmente les performances de la requête.
- Le CTE nous permet de l'utiliser comme alternative au concept VIEW
- Il peut également être utilisé comme chaînage de CTE pour simplifier la requête.
- Il peut également être utilisé pour implémenter facilement des requêtes récursives.