Les requêtes SQL

À quoi sert SQL ?

Les chapitres précédents ont montré comment modéliser une base de données (MCD, MLD) et raisonner sur les données avec l'algèbre relationnelle. SQL est la traduction concrète de tout cela : c'est le langage qu'on tape pour interroger une vraie base de données.

SQL est omniprésent dans les systèmes informatiques modernes. Il permet à la fois :

• De créer et structurer une base de données (CREATE, ALTER, DROP) ;
• D'ajouter, modifier ou supprimer des données (INSERT, UPDATE, DELETE) ;
• D'interroger et exploiter les données (SELECT, JOIN, GROUP BY, etc.) ;
• De contrôler les accès et la sécurité (GRANT, REVOKE) ;
• De générer des statistiques simples (moyennes, totaux, comptages).

Pourquoi le SQL est-il toujours utilisé aujourd'hui ?

• Standardisé : SQL est un langage reconnu par tous les grands systèmes de gestion de bases de données (MySQL, PostgreSQL, Oracle, SQL Server…).
• Lisible : sa syntaxe proche du langage naturel le rend facilement compréhensible.
• Puissant : il permet de traiter efficacement de très grandes quantités de données.
• Transverse : il est utilisé dans presque tous les métiers liés à la donnée.

Types de données courants

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 est légèrement plus rapide ; VARCHAR économise de l'espace disque.

Familles de commandes SQL

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

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 :

idEtudiant a été généré automatiquement grâce à AUTO_INCREMENT.

Autres contraintes utiles

Modifier la structure d'une table : ALTER TABLE

Après la création, il est possible de modifier la structure d'une table sans la supprimer.

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, ici actif, prennent leur valeur par défaut.

Le bloc n'affiche aucun tableau, et c'est normal : un INSERT modifie la table et ne rend aucune ligne. Pour voir le résultat, ajouter SELECT * FROM Etudiant WHERE nom = 'Durand'; sous l'insertion et relancer : Claire y apparaît avec l'identifiant 11 et actif à 1.

Insérer plusieurs lignes à la fois :

Sans nommer les colonnes (déconseillé) :

Lecture des données : SELECT

Une requête s'écrit de haut en bas, SELECT sur la première ligne. Elle ne se construit pas dans cet ordre, et confondre les deux est la source d'erreur la plus fréquente au début.

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.

Le caractère joker % dans LIKE.

% remplace n'importe quelle suite de caractères (y compris vide).
_ remplace exactement un caractère.
Exemples : 'Du%' → commence par "Du" | '%on' → finit par "on" | 'D_pont' → "Dupont", "Damont", etc.

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.

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 une ligne modifiée, rien de plus. Le seul moyen de vérifier une modification est de relire la table ensuite.

Suppression : DELETE

Seul l'étudiant 4 est supprimé. 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 une ligne supprimée.

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

DELETE vs TRUNCATE

DELETE sans WHERE supprime toutes les lignes mais conserve la structure et peut être annulé avec ROLLBACK.
TRUNCATE vide la table et remet l'AUTO_INCREMENT à 1. L'opération n'est pas annulable, et se réserve aux environnements de développement.

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.

Données d'exemple

Pour illustrer les jointures, imaginons ces deux tables :

Table Etudiant :

Inscription (idCours = 10 = "Maths", 20 = "SQL")

Emma (id=3) n'a aucune inscription. Alice (id=1) en a deux. Ces deux particularités ne sont pas décoratives : ce sont elles qui font toute la différence entre les types de jointure.

La figure suivante prend ces deux tables et les joint des cinq façons possibles. L'appariement, lui, ne change jamais : ce qui change est le sort réservé aux lignes qui n'ont trouvé personne. Basculer d'un onglet à l'autre pour voir les orphelines apparaître et disparaître.

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

Trois faits se lisent directement sur cette figure.

• Alice se dédouble. Elle a deux inscriptions, elle apparaît donc deux fois. Une jointure peut rendre plus de lignes que la table de départ n'en contient, ce qui surprend systématiquement la première fois.
• Emma disparaît en INNER, revient en LEFT. Elle n'a aucune inscription : la jointure interne l'écarte, la jointure externe gauche la garde en complétant sa droite par des NULL.
• Ces NULL sont fabriqués. Aucune ligne de la base ne contient cette valeur. C'est la jointure qui l'invente pour combler la place d'une ligne qui n'existe pas, et c'est une source d'erreurs classique dans 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 :

Le bloc, lui, ne travaille pas sur ces trois lignes : il s'exécute sur la base de démonstration, qui compte dix étudiants et vingt-cinq inscriptions. Il rend donc vingt-cinq lignes et trois colonnes, une ligne par inscription appariée. Le principe est le même dans les deux cas.

Une JOIN (ou INNER JOIN) ne conserve que les lignes ayant une correspondance dans les deux tables. Emma n'apparaît pas car elle n'a aucune inscription.

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. Emma apparaît avec NULL dans idCours.

Exécuté sur la base de démonstration, ce bloc rend exactement les mêmes vingt-cinq lignes que le précédent. La raison compte : aucun étudiant de cette base n'est sans inscription, il n'y a donc aucune orpheline à rattraper. La différence entre les deux jointures ne se voit que lorsqu'une ligne reste seule, et c'est pour cela que la figure invente Emma.

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.

Résumé des types de jointures

Requêtes avec agrégats

Une jointure rend une ligne par appariement, et ce compte devient vite trop gros pour être lu. La suite consiste à le réduire à un résumé.

Les fonctions d'agrégation font un calcul sur plusieurs lignes et rendent une seule valeur. Elles transforment une liste en statistique.

Fonctions d'agrégation disponibles

COUNT(*) ou COUNT(colonne) ?

COUNT(*) compte toutes les lignes, y compris celles dont les colonnes sont à NULL.
COUNT(colonne) ne compte que les lignes où cette colonne n'est pas à NULL.

L'écart entre les deux comptes est donc le nombre de valeurs manquantes. C'est le moyen le plus rapide de savoir si une colonne est bien remplie.

Renommer une colonne avec AS

AS donne un alias lisible à la colonne résultante. C'est particulièrement utile avec les agrégats, qui n'ont pas de nom par défaut.

Le résultat contient une seule ligne et deux colonnes : 25 inscriptions, et une moyenne de 12,91. Deux pièges tiennent dans ces deux nombres. Le compte porte sur les lignes de Inscription, donc sur les inscriptions et non sur les étudiants, qui sont dix ; nommer cette colonne total_etudiants aurait fait mentir le résultat. Et la moyenne ne porte que sur 23 notes, parce que deux inscriptions n'en ont pas et que AVG ignore les NULL au lieu de les compter pour zéro.

Regrouper des résultats : GROUP BY

Le nombre d'inscrits et la moyenne sont maintenant calculés pour chaque cours. Le résultat contient sept lignes, une par cours de la base, et trois colonnes. GROUP BY ne filtre rien : il découpe les lignes en paquets, et chaque fonction d'agrégation travaille ensuite à l'intérieur d'un paquet.

Filtrer les groupes : HAVING

Le résultat contient cinq lignes au lieu de sept : deux cours sortent, leur moyenne ne dépassant pas 12. Ce sont bien des groupes qui ont été écartés, pas des lignes.

WHERE ou HAVING ?

WHERE filtre les lignes avant le regroupement : il s'applique aux données brutes.
HAVING filtre les groupes après le GROUP BY : il s'applique aux résultats agrégés.

WHERE AVG(note) > 12 n'existe pas, et la raison est la même que pour l'ordre de construction vu plus haut : au moment où WHERE s'applique, les groupes ne sont pas formés et la moyenne n'existe pas encore.

Exemple complet avec jointure + agrégat

Cette requête enchaîne les quatre gestes du chapitre :

• Assembler Cours et Inscription sur l'identifiant de cours ;
• Regrouper par intitulé, pour obtenir un paquet de lignes par cours ;
• Écarter les groupes dont la moyenne est inférieure à 10 ;
• Trier ce qui reste par moyenne décroissante.

Le résultat contient sept lignes et trois colonnes : sur cette base, les sept cours atteignent tous la moyenne de 10, et le HAVING n'écarte donc personne. Remplacer 10 par 13 et relancer : il reste quatre cours, Mathematiques en tête avec 14,4.

Ordre d'exécution d'une requête SQL

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 dans lequel le moteur travaille. Le voici en entier, les clauses vues depuis intercalées à leur place.

Ce que ce tableau explique. Un alias défini dans SELECT ne peut pas servir dans WHERE : à l'étape 2, l'étape 5 n'a pas eu lieu et l'alias n'existe pas.
Le même alias fonctionne en revanche dans ORDER BY, qui vient après. Les deux règles ne s'apprennent pas séparément : elles se déduisent de la colonne de gauche.

La figure suivante fait tourner une requête à jointure et agrégat, étape par étape. Cliquer sur chaque clause montre la relation à ce moment précis.

SELECT nomClasse, COUNT(*) AS effectifFROM EtudiantJOIN Classe ON Etudiant.idClasse = Classe.idClasseWHERE actif = 1GROUP BY nomClasseHAVING COUNT(*) >= 3ORDER BY effectif DESC

Suivre la clause HAVING est instructif : elle porte sur COUNT(*), une valeur qui n'existe qu'à partir de l'étape 3. L'écrire dans WHERE reviendrait à la demander à l'étape 2, avant qu'elle soit calculable, et le moteur refuse.

Créer une table avec une clé étrangère

Ce que garantit l'intégrité référentielle. Sous MySQL, la seconde insertion ci-dessous est refusée avant même d'être écrite :

-- Accepté : l'étudiant 1 et le cours 10 existent tous les deux
INSERT INTO Inscription VALUES (1, 10, 14.5);

-- Refusé : l'étudiant 99 n'existe pas dans la table Etudiant
INSERT INTO Inscription VALUES (99, 10, 12.0);
-- Error: Cannot add or update a child row: a foreign key constraint fails

Ce bloc est en lecture seule, contrairement aux autres : le moteur embarqué dans cette page ne vérifie pas les clés étrangères, et laisserait passer les deux insertions sans rien dire. C'est justement ce que garantit un vrai SGBD et que la page ne peut pas montrer. Une contrainte non vérifiée par le moteur devient une contrainte que personne ne respecte.

La méthode

1. Écrire FROM en premier, avec les tables nécessaires et leurs alias. Une table qui n'apporte ni colonne affichée ni colonne filtrée est de trop.
2. Poser chaque jointure avec son ON dès que FROM cite plus d'une table. Une jointure oubliée ne provoque aucune erreur : elle produit un produit cartésien, et le nombre de lignes explose sans prévenir.
3. Écrire WHERE ensuite, en filtrant sur les colonnes brutes. Toutes les colonnes des tables sont disponibles ici, y compris celles qui ne seront pas affichées.
4. Regrouper puis filtrer les groupes quand la question porte sur un résumé : GROUP BY sur les colonnes qui identifient un paquet, HAVING sur les fonctions d'agrégation, jamais l'inverse.
5. Écrire SELECT en dernier, en nommant les colonnes une à une et en donnant un alias à chaque agrégat. Un alias qui décrit mal ce qui est compté fait mentir le résultat.
6. Ajouter ORDER BY puis LIMIT en toute fin, pour la lecture seulement : ils ne changent jamais quelles lignes sont retenues, seulement lesquelles sont montrées.
7. Compter le résultat attendu avant de lancer. Un résultat plus long que prévu signale une jointure qui dédouble ; un résultat vide signale un filtre trop strict ou un NULL oublié.
8. Relire tout UPDATE et tout DELETE en cherchant son WHERE avant de l'exécuter. C'est le seul geste de cette liste qui protège des données.

Synthèse

• SQL est un langage déclaratif : une requête décrit le résultat voulu, jamais la marche à suivre. C'est le SGBD qui choisit comment l'obtenir.
• Les commandes se rangent en familles : le DDL définit les structures, le DML modifie les données, le DQL les interroge, le DCL gère les droits, le TCL les transactions. Au quotidien, le DML et le DQL font l'essentiel.
• Créer une table consiste à nommer ses colonnes, leur donner un type et leur attacher des contraintes. PRIMARY KEY, NOT NULL, UNIQUE, CHECK et DEFAULT font refuser par la base ce qui serait découvert trop tard dans l'application.
• Une clé étrangère relie une table à une autre et garantit l'intégrité référentielle : aucune ligne ne peut désigner une ligne inexistante. ON DELETE CASCADE propage la suppression, ON DELETE RESTRICT la bloque.
• INSERT, UPDATE et DELETE ne rendent aucun tableau : ils modifient la table et annoncent un nombre de lignes touchées. Un UPDATE ou un DELETE sans WHERE s'applique à toute la table.
• Une requête se construit dans l'ordre FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, qui est aussi l'ordre dans lequel le moteur l'exécute. L'ordre d'écriture, lui, commence par SELECT : les confondre explique la plupart des erreurs de débutant.
• Une jointure interne ne garde que les lignes appariées des deux côtés ; une jointure externe garde les orphelines d'un côté et fabrique des NULL pour l'autre. Ces NULL n'existent nulle part dans la base.
• Une jointure peut rendre plus de lignes que la table de départ n'en contient, puisqu'une ligne de gauche appariée trois fois produit trois lignes. Tout comptage fait ensuite sur ce résultat porte sur les appariements, pas sur les lignes d'origine.
• Les fonctions d'agrégation résument un paquet de lignes en une valeur. COUNT(*) compte les lignes, COUNT(colonne) seulement celles qui portent une valeur, et AVG, SUM, MIN, MAX ignorent les NULL au lieu de les compter pour zéro.
• WHERE filtre des lignes avant le regroupement, HAVING filtre des groupes après. Écrire une fonction d'agrégation dans WHERE est refusé, parce qu'à cette étape les groupes n'existent pas encore.

Quiz

Ressources complémentaires

• SQLZOO (exercices interactifs)
• w3schools, SQL Tutorial
• OpenClassrooms, Apprenez à créer votre base de données
• Codecademy, Learn SQL