Les requêtes SQL : créer, modifier, interroger et joindre

Ce que SQL sait faire

Le langage est normalisé, et reconnu par tous les grands SGBD (MySQL, PostgreSQL, Oracle, SQL Server, SQLite), chacun avec son dialecte. Ce cours écrit celui de MySQL.

Choisir le type d'une colonne

CHAR ou VARCHAR ?

Utiliser CHAR(n) quand la valeur a toujours exactement la même taille (code postal, numéro INSEE, identifiant fixe).
Utiliser VARCHAR(n) pour les textes de longueur variable (noms, adresses, titres).
CHAR dit au lecteur du schéma que la taille est fixe ; VARCHAR n'occupe que la place du texte.

Cinq familles d'instructions

Le langage SQL est divisé en plusieurs familles de commandes, selon leur rôle dans la gestion d'une base de données relationnelle.

Le classement en DQL est courant dans les cours ; la documentation de MySQL, elle, range SELECT parmi les instructions du DML. Ce chapitre pratique le DDL, le DML et le DQL ; le chapitre Les transactions et les propriétés ACID traite le TCL, et montre ce qui se passe quand deux clients écrivent en même temps ; le chapitre Exploiter une base traite le DCL, avec les comptes et les droits.

Structure d'une base de données

Création d'une base de données

Création d'une table

Ce que la table contient une fois remplie. Les contraintes ne se voient pas dans les lignes, elles se voient dans ce qui a été refusé à l'insertion. Voici deux lignes acceptées :

La base de démonstration de la page reprend cette table avec deux colonnes de plus, ville et idClasse (le bouton Schéma les montre), et d'autres étudiants que ceux du tableau ci-dessus : dix, de Lea Martin à Sofia Petit.

Autres contraintes utiles

La table Cours de la page porte la même contrainte, avec une colonne de plus, idEnseignant. Le bloc suivant essaie d'y créer un cours sans aucune heure :

Le bloc s'arrête sur CHECK constraint failed, et MySQL refuse la même ligne (Check constraint ... is violated). Remplacer 0 par NULL et relancer : la ligne passe, puisqu'une valeur inconnue n'est pas fausse.

Modifier la structure d'une table : ALTER TABLE

ALTER TABLE, du DDL lui aussi, fait évoluer la structure d'une table sans la supprimer ni perdre ses lignes ; seule la suppression d'une colonne détruit les valeurs qu'elle portait.

Manipuler les données avec SQL

Insertion de données : INSERT

Une ligne est ajoutée à Etudiant. idEtudiant n'est pas précisé, il est généré automatiquement ; les colonnes non mentionnées prennent leur valeur par défaut : actif vaut 1, et ville et idClasse, qui n'en ont pas, valent NULL.

Le bloc n'affiche aucun tableau, et c'est normal : un INSERT modifie la table et ne rend aucune ligne. La page annonce seulement « 1 ligne modifiée ».

Insérer plusieurs lignes à la fois :

La page annonce « 2 lignes modifiées » : Pierre Dupont et Sophie Martin reçoivent les identifiants 11 et 12, dans l'ordre de la liste, et la table compte désormais douze étudiants.

Sans nommer les colonnes (déconseillé) :

Sur la base de la page, qui compte huit colonnes, cette insertion échoue d'ailleurs : « table Etudiant has 8 columns but 6 values were supplied ». C'est exactement le risque d'une liste de colonnes omise, le jour où une table en gagne une : nommer les colonnes rend l'ordre robuste à ce changement.

Lecture des données : SELECT

Une requête s'écrit SELECT en tête, mais se construit dans un autre ordre : FROM d'abord, d'où viennent les lignes ; WHERE ensuite, lesquelles garder, avec toutes les colonnes de la table disponibles pour le filtre, même celles qui ne seront pas affichées ; SELECT en dernier, lesquelles afficher. La fin du chapitre montre que c'est l'ordre dans lequel la requête est évaluée.

Construite dans l'ordre : partir de Etudiant, ne garder que les étudiants nés après le 1er janvier 2001, n'afficher que le nom et le prénom, trier par nom croissant.

Le résultat contient dix lignes et deux colonnes, c'est-à-dire toute la table : sur cette base de démonstration, aucun étudiant n'est né avant 2001, et le filtre n'écarte donc personne. Remplacer 2001 par 2004 dans le bloc et relancer : le résultat tombe à cinq lignes. Un filtre qui ne retire rien n'est pas un filtre qui ne marche pas.

Opérateurs de filtre courants

Le bloc contient trois requêtes et affiche donc trois tableaux. La première rend deux lignes, Dupond et Durand. La deuxième en rend deux également, Bernard et Marchand, les deux étudiants dont l'email est absent. La troisième rend onze lignes sur les vingt-cinq inscriptions : les bornes sont incluses, une note de 10 ou de 15 est retenue.

Les jokers de LIKE.

% remplace n'importe quelle suite de caractères (y compris vide).
_ remplace exactement un caractère, et le reste du motif doit coïncider.
Exemples : 'Du%' trouve les noms qui commencent par « Du », '%on' ceux qui finissent par « on ». 'D_pont' trouve « Dupont » et « Dapont », mais ni « Damont », dont la fin diffère, ni « Duupont », qui a deux lettres à la place du joker.

Majuscules et accents. Dans la page, une comparaison de texte par = distingue les majuscules, les minuscules et les accents : WHERE ville = 'paris' ne trouve personne, et WHERE intitule = 'Mathématiques' non plus, puisque la base écrit Mathematiques, sans accent. MySQL, avec ses réglages d'usine, ne fait aucune de ces distinctions, et trouverait les quatre Parisiens. Écrire la valeur exactement comme elle est stockée donne le même résultat partout. LIKE, lui, ignore la casse dans les deux moteurs : 'du%' trouve encore Dupond et Durand. MySQL ignore aussi les accents, et la page non : intitule LIKE 'mathé%' trouve les mathématiques sous MySQL, et aucun cours dans la page. Les noms de table suivent une autre règle : sous Linux, MySQL distingue Etudiant de etudiant, quand la page les confond.

Combiner des conditions : AND, OR et les parenthèses

AND est prioritaire sur OR, exactement comme la multiplication l'est sur l'addition. Les deux requêtes suivantes ne diffèrent que par une paire de parenthèses.

La première rend les quatre Parisiens, tous actifs. La seconde en rend six : les mêmes, plus Tom Durand et Sofia Petit, qui habitent Nantes et ne sont plus actifs. Sans parenthèses, la condition actif = 1 ne s'applique qu'à Paris. Au moindre mélange de AND et de OR, écrire les parenthèses, même quand on croit connaître la priorité.

Afficher des valeurs distinctes : DISTINCT

DISTINCT supprime les doublons du résultat. Client compte quatre lignes et deux clients habitent Paris ; le résultat contient trois lignes et une colonne, Paris, Lyon et Bordeaux. Le dédoublonnage coûte cher au moteur, qui ne le fait que si on le demande.

Limiter le nombre de résultats : LIMIT

LIMIT coupe le résultat après le nombre de lignes indiqué, une fois le tri appliqué. Ici la table compte exactement dix étudiants : le résultat en contient dix et la coupe ne retire rien. Passer à LIMIT 3 pour la voir agir, ce qui laisse Bernard, Diallo et Dupond, les trois premiers dans l'ordre alphabétique. Sans ORDER BY, la coupe porterait sur des lignes quelconques. Trier puis couper répond à toute question de classement.

Mise à jour : UPDATE

Le nom et l'email de l'étudiant 2 sont modifiés. Plusieurs colonnes se modifient d'un coup, séparées par des virgules dans SET.

Comme pour l'insertion, aucun tableau ne s'affiche : le bloc annonce « 1 ligne modifiée », rien de plus. Le seul moyen de vérifier une modification est de relire la table ensuite.

Règle d'or du UPDATE. Toujours utiliser WHERE pour cibler les lignes à modifier. Un UPDATE Etudiant SET nom = 'Dupond' sans WHERE modifie tous les étudiants de la table, et cette erreur est irrécupérable sans sauvegarde.

MySQL Workbench active par défaut l'option « Safe Updates », qui refuse un UPDATE ou un DELETE dont le WHERE ne porte pas sur une clé. C'est un garde-fou à garder, pas une gêne à désactiver.

Suppression : DELETE

Seul l'étudiant 4, Tom Durand, est supprimé de la table Etudiant. Filtrer sur la clé primaire est la façon la plus sûre de ne viser qu'une ligne, puisqu'aucune autre ne peut porter la même valeur.

Là encore, le bloc ne rend aucun tableau : il annonce « 1 ligne modifiée ». Ce compte ne dit pas tout. Les deux inscriptions de Tom ont disparu avec lui, parce que la table Inscription le demande par sa clé étrangère (ON DELETE CASCADE, présentée plus bas) ; le moteur ne compte que les lignes visées par l'ordre lui-même. L'ordre ne visait qu'une ligne, et en a fait disparaître trois.

Supprimer toutes les données de la table (dangereux) :

La règle d'or se met à l'essai sur une suppression dont le WHERE cache un piège.

Relier deux tables : la clé étrangère

Une inscription relie un étudiant à un cours. La table qui la porte désigne l'un et l'autre par leur identifiant, et déclare ce lien pour que la base le fasse respecter.

Sous MySQL, Etudiant et Cours doivent exister avant Inscription : une clé étrangère ne peut viser qu'une table déjà créée, et la création échoue sinon (Failed to open the referenced table). On crée donc d'abord les tables que d'autres désignent. Ce bloc part d'une base vide dans la page, et SQLite, plus souple, accepte la déclaration avant que les deux tables existent.

Ce que garantit l'intégrité référentielle. La table Inscription de la page est déclarée ainsi. Le bloc suivant y insère deux lignes, dont la seconde désigne un étudiant qui n'existe pas :

Le bloc s'arrête sur FOREIGN KEY constraint failed : la seconde ligne n'est jamais écrite. MySQL refuse la même insertion, avec le message Cannot add or update a child row: a foreign key constraint fails. SQLite, lui, ne vérifie les clés étrangères que si on le lui demande ; la page le lui demande à chaque exécution, pour se comporter comme MySQL, qui les vérifie toujours. Une contrainte que le moteur ne vérifie pas est une contrainte que personne ne respecte.

Les deux règles de suppression se voient aussi. Supprimer un étudiant emporte ses inscriptions :

Le premier compte vaut 2, le second 0. Supprimer un cours qui a des inscrits, en revanche, est refusé :

Le moteur répond de nouveau FOREIGN KEY constraint failed ; MySQL écrit Cannot delete or update a parent row. Pour supprimer ce cours, il faudrait d'abord supprimer ses inscriptions, ce qui oblige à le faire exprès.

Requêtes avec jointures

Une jointure permet de combiner des données provenant de plusieurs tables. C'est l'opération clé dans une base relationnelle, où les informations sont réparties sur plusieurs tables reliées par des clés. Dans un tableur, c'est ce que fait RECHERCHEV, une ligne à la fois et une colonne à la fois ; le chapitre Excel sur la recherche en montre les limites, que la jointure n'a pas.

Données d'exemple

Les jointures s'illustrent sur deux petites tables :

Table Etudiant :

Table Inscription (le cours 10 est « Maths », le 20 est « SQL ») :

Emma (id=3) n'a aucune inscription. Alice (id=1) en a deux. Ce sont ces deux particularités qui distinguent les types de jointure. La figure suivante joint les deux tables des cinq façons possibles : l'appariement ne change jamais, seul change le sort des lignes qui n'ont trouvé personne.

SELECT *
FROM Etudiant
INNER JOIN Inscription
  ON Etudiant.idEtudiant = Inscription.idEtudiant

Alice, elle, figure deux fois dans chaque jointure, une par inscription : une jointure peut rendre plus de lignes que la table de départ n'en contient. Quant aux NULL qui complètent Emma en LEFT, aucune ligne de la base ne les contient : la jointure les invente pour combler une ligne qui n'existe pas, et ils faussent souvent les calculs qui suivent.

Jointure interne (INNER JOIN)

Construite dans l'ordre : partir de Etudiant, l'apparier à Inscription sur l'identifiant d'étudiant, puis n'afficher que le nom, le prénom et le cours.

Sur les trois étudiants de la figure ci-dessus, le résultat compterait trois lignes :

Une JOIN (ou INNER JOIN) ne conserve que les lignes ayant une correspondance dans les deux tables. Le bloc, lui, s'exécute sur la base de démonstration, qui compte dix étudiants et vingt-cinq inscriptions : il rend vingt-cinq lignes et trois colonnes, une par inscription appariée.

Jointure externe gauche (LEFT JOIN)

Sur les données de la figure, le résultat compterait quatre lignes, une de plus que la jointure interne :

Une LEFT JOIN conserve toutes les lignes de la table de gauche (Etudiant), même sans correspondance à droite. Exécuté sur la base de démonstration, ce bloc rend pourtant les mêmes vingt-cinq lignes que le précédent : aucun étudiant de cette base n'est sans inscription, il n'y a aucune orpheline à rattraper. La différence ne se voit que lorsqu'une ligne reste seule, et c'est pour cela que la figure invente Emma.

Une condition sur la table de droite : dans ON, pas dans WHERE

La scolarité veut la liste de tous les étudiants, chacun avec sa note de mathématiques s'il en a une. La première idée ajoute la condition sur le cours dans WHERE :

Cinq lignes, et non dix : les cinq étudiants qui ne suivent pas les mathématiques ont disparu. La jointure externe les avait pourtant gardés, une ligne par cours suivi, ou une ligne de NULL pour qui n'en suit aucun. Puis WHERE passe sur ces lignes assemblées, et i.idCours = 3 y est faux pour un autre cours, inconnu sur une ligne de NULL : dans les deux cas la ligne tombe. La jointure externe n'a servi à rien, la requête rend ce que rendrait une jointure interne.

Dix lignes : les cinq notes de mathématiques, et cinq NULL pour Durand, Lefebvre, Moreau, Marchand et Petit. Dans ON, la condition ne trie plus le résultat : elle dit quelles inscriptions s'associent à un étudiant. Un étudiant sans inscription en mathématiques n'en reçoit aucune, et la jointure externe le garde, avec des NULL. Dans une jointure externe, une condition sur la table de droite se met donc dans ON ; une condition sur la table de gauche, comme e.actif = 1, va dans WHERE, comme d'habitude.

La même règle, sur d'autres tables :

Jointure sur 3 tables

Les jointures se chaînent pour remonter les données à travers la table de liaison Inscription : d'abord l'étudiant vers ses inscriptions, puis chaque inscription vers son cours. Le résultat contient vingt-cinq lignes et deux colonnes, le nom de l'étudiant et l'intitulé du cours. Un étudiant y figure autant de fois qu'il suit de cours, ce qu'il faut garder en tête avant de compter quoi que ce soit sur ce résultat. Un compte fait sur une jointure peut tomber juste sur certaines données, et faux sur d'autres.

Une table jointe à elle-même

La base de la page contient aussi une table Employe, avec le service de chacun. Pour associer chaque employé à ses collègues de service, la table se joint à elle-même :

Quatre lignes, chaque paire dans les deux sens : Girard et Barbier aux Etudes, Lemoine et Roche au Support. Vidal, seule au Commerce, n'a personne. Les alias a et b font de la même table deux sources distinctes : sans eux, Employe.service ne désignerait aucune des deux. La condition b.idEmploye <> a.idEmploye écarte l'employé apparié à lui-même.

Résumé des types de jointures

RIGHT JOIN sert peu : la même requête s'écrit plus lisiblement en inversant les deux tables autour d'un LEFT JOIN.

Prévoir avant d'exécuter

Le nombre de lignes d'une jointure se prévoit à partir de ce que l'on sait des tables, avant de lancer quoi que ce soit. Les quatre questions suivantes portent sur les cours et les enseignants de la base : 7 cours, dont 2 sans enseignant, et 3 enseignants.

Mettre deux résultats bout à bout : UNION

L'annuaire de l'école réunit les étudiants et les enseignants, deux tables aux colonnes différentes. Deux requêtes qui rendent autant de colonnes l'une que l'autre se combinent comme deux ensembles, par l'union, l'intersection et la différence du chapitre sur l'algèbre.

Douze noms dans le premier tableau, treize dans le second : UNION retire les doublons, et Durand, porté par Tom l'étudiant et par Claire l'enseignante, n'y figure qu'une fois. Il compare des lignes entières : avec le prénom, le troisième tableau retrouve les treize personnes. INTERSECT garde le seul nom commun, Durand ; EXCEPT, les neuf noms d'étudiants qu'aucun enseignant ne porte. Les colonnes s'apparient par leur position, jamais par leur nom : unir nom, prenom à prenomEns, nomEns s'exécute sans erreur, et range Claire parmi les noms de famille. MySQL connaît INTERSECT et EXCEPT depuis sa version 8.0.31.

L'ordre logique d'évaluation d'une requête

L'ordre de construction annoncé au début du chapitre, FROM puis WHERE puis SELECT, n'était pas un conseil de rédaction : c'est l'ordre logique dans lequel une requête est évaluée. Le moteur est libre de s'y prendre autrement pour aller plus vite, en filtrant avant de joindre ou en passant par un index, mais son résultat est toujours celui de cet ordre. Le voici en entier, avec les clauses vues depuis et les deux du chapitre suivant.

GROUP BY et HAVING, qui forment des paquets de lignes et les filtrent, sont l'objet du chapitre sur le SQL avancé. La figure suivante fait tourner une requête sans regroupement, étape par étape, et montre la relation obtenue après chaque clause.

SELECT nom, villeFROM EtudiantWHERE actif = 1ORDER BY nomLIMIT 3

Ce que ce tableau explique. Un alias défini dans SELECT n'existe pas encore à l'étape 2 : la norme SQL interdit de l'employer dans WHERE, et MySQL le refuse (« Unknown column »). Il est permis dans ORDER BY, qui vient après.

Deux employés, Barbier à 37 200 et Vidal à 33 000. Le WHERE répète le calcul au lieu de citer annuel ; ORDER BY, évalué après SELECT, cite l'alias.

Chaque moteur s'accorde ainsi des libertés : MySQL accepte un alias dans GROUP BY et HAVING, SQLite partout. L'écriture qui répète l'expression passe partout.

Ressources complémentaires

• SQLZOO (exercices interactifs)
• w3schools, SQL Tutorial
• OpenClassrooms, Requêtez une base de données avec SQL
• Codecademy, Learn SQL