Expressions de Table Communes (CTEs) MySQL

Introduction

Dans ce laboratoire, vous apprendrez à utiliser les Common Table Expressions (CTEs) en MySQL. Les CTEs aident à simplifier les requêtes complexes en les décomposant en étapes logiques et lisibles. Un CTE crée un ensemble de résultats temporaire et nommé que vous pouvez référencer dans une seule instruction SQL.

Vous commencerez par configurer une base de données et par créer un CTE simple pour filtrer les données des employés. Ensuite, vous explorerez les CTEs récursifs pour gérer les données hiérarchiques, comme un organigramme. Enfin, vous apprendrez à joindre un CTE à une table pour effectuer une analyse de données plus avancée.

Configurer la Base de Données et un CTE Simple

Dans cette première étape, vous allez configurer l'environnement de la base de données et définir un Common Table Expression (CTE) simple. Les CTEs sont définis à l'aide de la clause WITH et agissent comme une vue temporaire qui n'existe que pour la durée d'une seule requête. Cela rend les requêtes complexes plus faciles à lire et à gérer.

Tout d'abord, ouvrez le terminal depuis votre bureau.

Connectez-vous au serveur MySQL en tant qu'utilisateur root. Dans cet environnement de laboratoire, vous pouvez utiliser sudo pour vous connecter sans mot de passe.

sudo mysql -u root

Une fois connecté, vous verrez l'invite MySQL (mysql>). Maintenant, créez une base de données nommée labex_db et basculez vers celle-ci.

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;

Ensuite, créez une table employees et insérez quelques données d'exemple. Cette table stockera des informations de base sur les employés, y compris leur département et leur salaire.

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);

Maintenant que la table est prête, définissons un CTE simple pour sélectionner uniquement les employés du département 'Sales'.

WITH SalesEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;

Analysons cette requête :

WITH SalesEmployees AS (...) : Ceci définit un CTE nommé SalesEmployees.
La requête entre parenthèses sélectionne les employés de la table employees où le department est 'Sales'.
SELECT * FROM SalesEmployees; : C'est la requête principale qui récupère toutes les données de notre ensemble de résultats temporaire SalesEmployees.

Vous devriez voir la sortie suivante, listant uniquement les employés du département des ventes :

+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary   |
+-------------+------------+-----------+----------+
|           1 | John       | Doe       | 60000.00 |
|           3 | Robert     | Jones     | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)

Cela confirme que votre premier CTE fonctionne correctement. Veuillez rester dans le shell MySQL pour la prochaine étape.

Créer un CTE Récursif pour les Données Hiérarchiques

Les CTEs récursifs sont une fonctionnalité puissante pour interroger des données hiérarchiques, telles que des organigrammes ou des catégories imbriquées. Un CTE récursif se réfère à lui-même pour traiter une structure arborescente niveau par niveau. Il se compose d'un "membre d'ancrage" (le point de départ) et d'un "membre récursif" (l'itération).

Dans cette étape, vous allez créer une table représentant une hiérarchie d'employés et utiliser un CTE récursif pour l'afficher.

Tout d'abord, tout en étant dans le shell MySQL, créez la table employee_hierarchy. Cette table inclut un manager_id qui pointe vers l'employee_id d'un autre employé, créant ainsi la hiérarchie.

CREATE TABLE IF NOT EXISTS employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);

Dans cette structure, 'David' est le manager de niveau supérieur car son manager_id est NULL.

Maintenant, écrivez un CTE récursif pour parcourir cette hiérarchie. Le mot-clé RECURSIVE est requis.

WITH RECURSIVE EmployeeHierarchyCTE AS (
    -- Membre d'ancrage : sélectionne le manager de niveau supérieur
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE manager_id IS NULL

    UNION ALL

    -- Membre récursif : jointure avec lui-même pour trouver les subordonnés
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employee_hierarchy e
    INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;

Analysons cette requête :

Membre d'ancrage : La première instruction SELECT trouve la racine de la hiérarchie (où manager_id est NULL) et lui attribue le level 0.
Membre récursif : La deuxième instruction SELECT joint employee_hierarchy avec le CTE lui-même (EmployeeHierarchyCTE). Elle trouve tous les employés dont le manager_id correspond à un employee_id déjà présent dans le CTE, et elle incrémente le level.
UNION ALL : Cet opérateur combine les résultats des membres d'ancrage et récursifs.

La requête produira la sortie suivante, montrant l'organigramme complet avec les niveaux :

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | David         |       NULL |     0 |
|           2 | Emily         |          1 |     1 |
|           3 | Frank         |          1 |     1 |
|           4 | Grace         |          2 |     2 |
|           5 | Henry         |          2 |     2 |
|           6 | Ivy           |          3 |     2 |
|           7 | Jack          |          3 |     2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)

Vous avez utilisé avec succès un CTE récursif pour interroger des données hiérarchiques. Veuillez rester dans le shell MySQL pour la dernière étape.

Joindre un CTE avec une Table

Les CTEs peuvent être joints à d'autres tables comme des tables régulières. Ceci est utile pour combiner des données agrégées d'un CTE avec des données détaillées d'une autre table. Dans cette étape, vous allez créer un CTE pour calculer le salaire moyen par département, puis le joindre à la table employees.

Tout en étant dans le shell MySQL, exécutez la requête suivante. Elle définit un CTE nommé AvgSalaryByDepartment puis le joint à la table employees.

WITH AvgSalaryByDepartment AS (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.employee_id,
    e.first_name,
    e.department,
    e.salary,
    a.avg_salary
FROM
    employees e
JOIN
    AvgSalaryByDepartment a ON e.department = a.department;

Voici la décomposition :

Le CTE AvgSalaryByDepartment calcule le salaire moyen pour chaque département.
La requête principale joint ensuite la table employees (alias e) à ce CTE (alias a) sur la colonne department.
Cela vous permet d'afficher le salaire de chaque employé à côté du salaire moyen de son département.

La sortie attendue sera :

+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary   | avg_salary   |
+-------------+------------+------------+----------+--------------+
|           1 | John       | Sales      | 60000.00 | 57500.000000 |
|           2 | Jane       | Marketing  | 75000.00 | 72500.000000 |
|           3 | Robert     | Sales      | 55000.00 | 57500.000000 |
|           4 | Emily      | IT         | 90000.00 | 90000.000000 |
|           5 | Michael    | Marketing  | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Vous pouvez vérifier manuellement les résultats. Par exemple, le salaire moyen pour le département 'Sales' est (60000 + 55000) / 2 = 57500, ce qui correspond à la sortie. Cela confirme que votre requête fonctionne correctement.

Vous avez réussi à joindre un CTE à une table. Vous pouvez maintenant quitter le shell MySQL.

exit;

Résumé

Dans ce laboratoire, vous avez appris à utiliser efficacement les Expressions de Table Communes (CTEs) en MySQL. Vous avez commencé par configurer une base de données et des tables, puis vous avez progressé dans la création de différents types de CTEs.

Vous avez appris à :

Définir un CTE simple en utilisant la clause WITH pour filtrer les données et améliorer la lisibilité des requêtes.
Créer un CTE récursif pour naviguer et afficher des données hiérarchiques à partir d'une table.
Joindre un CTE à une table pour combiner des résultats agrégés avec des données détaillées au niveau des lignes pour une analyse plus complexe.

Ces compétences sont fondamentales pour écrire des requêtes SQL propres, maintenables et puissantes, en particulier lorsque vous traitez une logique ou des structures de données complexes.