Mysql avec une expression de table commune

L'expression de la table commune (CTE) est une caractéristique importante de MySQL qui est utilisée pour générer un ensemble de résultats temporaire. Il peut être utilisé avec n'importe quelle instruction SQL comme SELECT, INSERT, UPDATE, etc. Les requêtes compliquées peuvent être simplifiées en utilisant CTE. L'ensemble de résultats de toute requête est stocké comme un objet pour le tableau dérivé au moment de l'exécution de la requête. Mais le CTE peut être l'auto-référence, ce qui signifie que la même requête peut être référencée plusieurs fois en utilisant CTE. Pour cette raison, les performances du CTE sont meilleures que le tableau dérivé. Avec la clause est utilisée pour définir un CTE et plus d'un CTE peut être défini dans une seule déclaration en utilisant cette clause. Comment un CTE peut être appliqué dans la requête pour le rendre plus lisible et augmenter les performances de la requête est expliquée dans cet article.

Avantages de l'utilisation du CTE:

• Cela rend la requête plus lisible.
• Il améliore les performances de la requête.
• Il peut être utilisé comme alternative à la vue.
• Il est possible de créer un chaînage de CTE pour simplifier la requête.
• Les requêtes récursives peuvent être mises en œuvre facilement en utilisant CTE.

Syntaxe:

Avec cte-name (Column1, Column2,… Columnn) comme (
Mettre en doute
)
SELECT * FROM CTE-NAME;

Ici, vous pouvez définir n'importe quelle instruction SQL comme requête, sélectionnez, mise à jour, supprimer, insérer ou créer. Si vous définissez la liste des colonnes avec clause, alors le nombre de colonnes dans la requête doit être la même avec le nombre de colonnes définies avec la clause.

Prérequis:

La fonction CTE n'est pas prise en charge par aucune version MySQL inférieure à 8.0. Donc, vous devez installer mysql 8.0 Avant de pratiquer l'exemple de cet article. Vous pouvez vérifier la version actuellement installée de MySQL en exécutant la commande suivante.

$ mysql -v

La sortie montre que MySQL Version 8.0.19 est installé dans le système.

Si la version correcte est installée, créez une base de données nommée mydb et créer deux tables nommées utilisateurs et users_profile avec certaines données pour connaître les utilisations du CTE dans MySQL. Exécutez les instructions SQL suivantes pour effectuer les tâches. Ces déclarations créeront deux tables connexes nommées utilisateurs et users_profile. Ensuite, certaines données seront insérées dans les deux tableaux par insert des instructions.

Créer une base de données MyDB;
Utilisez MyDB;
Créer des utilisateurs de table (
Nom d'utilisateur Varchar (50) Clé primaire,
mot de passe varchar (50) pas null,
statut varchar (10) non nul);
Créer des tableaux Users_Profile (
Nom d'utilisateur Varchar (50) Clé primaire,
nom varchar (50) pas null,
Adresse Varchar (50) pas nul,
Email Varchar (50) pas null,
La clé étrangère (nom d'utilisateur) fait référence aux utilisateurs (nom d'utilisateur) sur Delete Cascade);
Insérer dans les valeurs des utilisateurs
('admin', '7856', 'actif'),
(«personnel», «90802», «actif»),
('Manager', '35462', 'inactive');
Insérer dans les valeurs uservy_profile
(«Admin», «Administrator», «Dhanmondi», «Admin @ Test.com ') ,
(«Staff», «Jakir Nayek», «Mirpur», «Zakir @ Test.com '),
('Manager', 'Mehr Afroz', 'Eskaton', 'mehr @ test.com ');

Utilisation du CTE simple:

Ici un CTE très simple nommé cte_users_profile est créé où aucune liste de champs n'est définie avec le nom CTE avec la clause et il récupérera toutes les données de la users_profile tableau. Ensuite, l'instruction SELECT est utilisée pour lire tous les enregistrements de cte_users_profile Cte.

Avec cte_users_profile comme (
Sélectionner * dans USERS_PROFILE
)
SELECT * FROM CTE_USERS_PROFILE;

La sortie suivante apparaîtra après avoir exécuté l'instruction.

Utilisation du CTE simple avec la liste des colonnes:

Vous pouvez créer CTE plus spécifiquement en définissant la liste des champs avec le nom CTE avec clause. Dans ce cas, les noms de champ définis avec le nom CTE seront les mêmes que les noms de champ définis dans la requête de sélection à l'intérieur de la clause avec. Ici, nom et e-mail Les champs sont utilisés aux deux endroits.

Avec cte_users_profile (nom, e-mail) comme (
Sélectionnez le nom, e-mail
De users_profile
)
SELECT * FROM CTE_USERS_PROFILE;

La sortie suivante apparaîtra après avoir exécuté l'instruction ci-dessus.

Utilisation du CTE simple avec la clause Where:

L'instruction SELECT avec la clause où peut être définie dans la déclaration CTE comme une autre requête sélectionnée. La requête sélectionnée avec des enregistrements de récupération à partir de utilisateurs et users_profile tableaux où les valeurs de Nom des utilisateurs le champ est égal pour les deux tables et la valeur de nom d'utilisateur n'est pas 'personnel'.

Avec cte_users comme (
Sélectionner les utilisateurs.Nom d'utilisateur, utilisateurs_profile.Nom, utilisateurs_profile.adresse, utilisateurs_profile.e-mail
Des utilisateurs, utilisateurs_profile
Où les utilisateurs.username = users_profile.Nom d'utilisateur et utilisateurs_profile.nom d'utilisateur «personnel»
)
Sélectionnez le nom comme nom, adresse comme adresse
De CTE_USERS;

La sortie suivante apparaîtra après avoir exécuté l'instruction.

Utilisation du CTE simple avec groupe par clause:

Toute fonction agrégée peut être utilisée dans la requête utilisée dans CTE. L'instruction CTE suivante affiche l'utilisation de la requête sélectionnée avec la fonction Count (). La première instruction SELECT est utilisée pour afficher tous les enregistrements de utilisateurs la table et la dernière instruction SELECT est utilisée pour afficher la sortie de CTE qui comptera le nombre total d'utilisateurs de utilisateurs table qui est active.

Sélectionner * chez les utilisateurs;
Avec cte_users comme (
Sélectionnez le nombre (*) comme total
Des utilisateurs
Où status = statut "actif" par statut
)
Sélectionnez le total comme «Total des utilisateurs actifs»
De CTE_USERS;

La sortie suivante apparaîtra après avoir exécuté l'instruction.

Utilisation du CTE simple avec l'opérateur syndical:

La déclaration CTE suivante montre l'utilisation de l'opérateur syndical dans la déclaration CTE. La sortie affichera les valeurs de nom d'utilisateur depuis utilisateurs table où le statut La valeur est 'Inactif'Et les autres valeurs de nom d'utilisateur depuis users_profile tableau.

Avec cte_users comme (
Sélectionner les utilisateurs.nom d'utilisateur
Des utilisateurs
Où status = 'inactif'
SYNDICAT
Sélectionnez Users_Profile.nom d'utilisateur
De users_profile
)
SELECT * FROM CTE_USERS;

La sortie suivante apparaîtra après avoir exécuté l'instruction.

Utilisation de CTE simple avec jointure de gauche:

L'instruction CTE suivante montre l'utilisation de la jointure de gauche dans CTE. La sortie affichera les valeurs de nom et e-mail champs de users_profile table en appliquant une jointure de gauche en fonction de nom d'utilisateur champ entre utilisateurs et users_profile tableaux et où la condition, qui filtrera ces enregistrements utilisateurs table où la valeur de statut est 'Inactif'.

Avec cte_users comme (
Sélectionnez le nom, e-mail
De users_profile
Utilisateurs de jointure de gauche
Sur les utilisateurs.username = users_profile.Nom d'utilisateur où les utilisateurs.status = 'inactif'
)
SELECT * FROM CTE_USERS;

La sortie suivante apparaîtra après avoir exécuté l'instruction.

Conclusion:

Si vous souhaitez augmenter les performances de la requête et obtenir la sortie de la requête plus rapidement, le CTE est la meilleure option que les autres options MySQL. Cet article aidera les utilisateurs de MySQL à apprendre très facilement l'utilisation du CTE pour la requête sélectionnée.