Ch. 4 : Le langage SQL

Cours – Partie 3

Nous avons étudié la structure d’une base de données relationnelle ; nous allons maintenant apprendre à réaliser des requêtes. Nous apprendrons à interroger une base afin d’obtenir des informations, puis à modifier son contenu : ajouter, modifier et supprimer des données.

Pour cela, nous allons apprendre un langage de requêtes : SQL (Structured Query Language), propre aux bases de données relationnelles. Nous travaillons avec SQLite, un système de gestion de bases de données relationnelles très répandu (qui, rappelons-le, n’est pas de type client-serveur). Il existe d’autres SGBD relationnels comme MySQL ou PostgreSQL : dans tous les cas, le langage de requêtes est le SQL, même si l’on peut noter quelques petites différences. Ce qui est vu ici avec SQLite pourra, à quelques modifications près, être utilisé avec MySQL par exemple.

La base d’exemple : Cantal.db

Nous illustrons chacune des instructions SQL du programme avec la base de données Cantal.db, contenant les deux relations « etablissements » et « communes » déjà rencontrées dans la partie précédente (les noms d’attributs ne contiennent pas d’espaces). On peut s’y connecter avec le logiciel DB Browser (SQLite) ou avec Edupython, qui en propose une version portable.

Schéma relationnel :

  • communes(Codecommune : INT, Nomdelacommune : TEXT, Populationtotale : INT, Codepostal : INT)
  • etablissements(code : TEXT, nom : TEXT, statut : TEXT, #id_commune : INT, latitude : FLOAT, longitude : FLOAT)

Voici un extrait de ces deux relations (les attributs latitude et longitude ne sont pas représentés) :

Codecommune Nomdelacommune Populationtotale Codepostal
14 AURILLAC 25 100 15000
85 LABROUSSE 360 15130
120 MAURIAC 3 600 15200
187 SAINT-FLOUR 6 600 15100
201 SANSAC-DE-MARMIESSE 1 900 15130
245 VALJOUZE 23 15170
code nom statut id_commune
0150001A Lycée Émile Duclaux Public 14
0150002B Collège Jules Ferry Public 14
0150003C Collège Saint-Joseph Privé 14
0150004D Collège Marcel Pagnol Public 120
0150005E Lycée Blaise Pascal Public 187
0150006F Collège Notre-Dame Privé 187

Remarque. Les résultats des requêtes sont donnés sur cet extrait, sauf mention contraire. Les résultats des fonctions d’agrégation (Section 3) sont ceux de la base complète. Par convention, on écrit les mots-clefs SQL en majuscules et les chaînes de caractères entre apostrophes ('Public'). SQLite tolère aussi les guillemets doubles, mais la norme SQL les réserve aux noms d’attributs et de tables.

1 Requêtes d’interrogation

1.1 Anatomie d’une requête

Une requête d’interrogation est formée de clauses, écrites dans un ordre imposé :

SELECT   attributs à afficher
FROM     table de départ
JOIN     autre table  ON  condition de jointure
WHERE    condition sur les lignes
ORDER BY attribut de tri
Clause Rôle Présence
SELECT Liste les attributs (colonnes) à afficher ; * désigne tous les attributs. obligatoire
FROM Indique la table de départ. obligatoire
JOIN … ON Joint une autre table, en précisant comment apparier les p-uplets. facultative
WHERE Ne conserve que les lignes qui vérifient une condition. facultative
ORDER BY Trie le résultat (ASC croissant par défaut, DESC décroissant). facultative

1.2 Requêtes simples

Pour extraire des informations d’une table, on effectue une requête d’interrogation à l’aide du mot-clef SELECT. Voici un exemple :

SELECT Nomdelacommune, Populationtotale
FROM communes

Cette requête permet d’obtenir le nom de la commune et sa population pour toutes les communes de la table communes. D’une façon générale, le mot-clef SELECT est suivi des attributs que l’on désire obtenir, et le mot-clef FROM de la table concernée.

Nomdelacommune Populationtotale
AURILLAC 25 100
LABROUSSE 360
MAURIAC 3 600
SAINT-FLOUR 6 600
SANSAC-DE-MARMIESSE 1 900
VALJOUZE 23

Il est possible d’obtenir tous les attributs sans les nommer, grâce au caractère étoile * :

SELECT *
FROM communes

est équivalent à :

SELECT Codecommune, Nomdelacommune, Populationtotale, Codepostal
FROM communes

1.3 Requêtes conditionnelles

La clause WHERE permet d’imposer une (ou des) condition(s) afin de sélectionner uniquement certaines lignes. La condition suit le mot-clef WHERE.

SELECT Nomdelacommune
FROM communes
WHERE Populationtotale > 2500

Cette requête affiche le nom des communes dont la population est strictement supérieure à 2 500 habitants.

Résultat : AURILLAC, MAURIAC, SAINT-FLOUR.

Voici les principaux opérateurs utilisables dans une condition :

Opérateur Signification
= <> (ou !=) égal (un seul signe =) ; différent
< <= > >= comparaisons
BETWEEN a AND b compris entre a et b, bornes incluses
IN (v1, v2, …) appartient à une liste de valeurs
LIKE 'motif' correspond au motif : % remplace n’importe quelle suite de caractères (même vide), _ remplace un seul caractère
IS NULL IS NOT NULL la valeur est absente ; la valeur est présente
NOT, AND, OR négation, « et », « ou » de conditions

Il est possible de combiner les conditions à l’aide d’un OR ou d’un AND :

SELECT Nomdelacommune
FROM communes
WHERE Populationtotale > 2500 AND Populationtotale < 10000

Cette requête donne le nom des communes dont la population est comprise entre 2 500 et 10 000 habitants (bornes exclues).

Résultat : MAURIAC, SAINT-FLOUR.

La requête ci-dessous affiche le nom des communes dont la population est supérieure à 5 000 habitants ou dont le nom contient la lettre « Z » (noter le symbole % qui remplace n’importe quelle séquence de caractères en SQL).

SELECT Nomdelacommune
FROM communes
WHERE Populationtotale > 5000 OR Nomdelacommune LIKE '%Z%'

Résultat : AURILLAC, SAINT-FLOUR, VALJOUZE.

Attention. AND est prioritaire sur OR. En cas de doute, on utilise des parenthèses. La condition Populationtotale > 1000 AND Populationtotale < 5000 OR Nomdelacommune LIKE 'V%' sélectionne MAURIAC, SANSAC-DE-MARMIESSE et VALJOUZE, tandis que Populationtotale > 1000 AND (Populationtotale < 5000 OR Nomdelacommune LIKE 'V%') ne sélectionne que MAURIAC et SANSAC-DE-MARMIESSE.

1.4 Ordonner les résultats

La clause ORDER BY permet d’ordonner les résultats dans l’ordre croissant (ASC, valeur par défaut).

SELECT Nomdelacommune, Populationtotale
FROM communes
WHERE Populationtotale > 2000 ORDER BY Populationtotale

Cette requête affiche le nom et la population des communes de plus de 2 000 habitants dans l’ordre croissant de leur population.

Résultat : MAURIAC (3 600), SAINT-FLOUR (6 600), AURILLAC (25 100).

Pour ordonner les résultats dans l’ordre décroissant, on ajoute DESC. Si la clause ORDER BY porte sur un attribut de type TEXT, on obtient un rangement dans l’ordre alphabétique.

SELECT Nomdelacommune, Populationtotale
FROM communes
WHERE Populationtotale > 2000 ORDER BY Nomdelacommune DESC

Cette requête affiche le nom et la population des communes de plus de 2 000 habitants dans l’ordre inverse de l’ordre alphabétique de leur nom.

Résultat : SAINT-FLOUR (6 600), MAURIAC (3 600), AURILLAC (25 100).

1.5 Éviter les doublons

Pour éviter les doublons dans les résultats d’une requête, on ajoute la clause DISTINCT juste après SELECT. Considérons la requête suivante, qui affiche dans l’ordre croissant la liste des codes postaux des communes :

SELECT Codepostal
FROM communes
ORDER BY Codepostal

Résultat : 15000, 15100, 15130, 15130, 15170, 15200.

Les codes postaux sont répétés autant de fois qu’il y a de communes desservies par ce code postal. La requête suivante, avec DISTINCT, n’affiche qu’une fois chacun des codes postaux :

SELECT DISTINCT Codepostal
FROM communes
ORDER BY Codepostal

Résultat : 15000, 15100, 15130, 15170, 15200.

1.6 Les jointures

Une requête dans une base de données peut nécessiter de regrouper des données provenant de différentes tables.

Définition 1 (Jointure)

Une requête combinant les données de plusieurs relations (tables) est appelée une jointure.

Poursuivons avec la base des établissements scolaires du Cantal. On souhaite obtenir la table des noms d’établissements suivis du nom de leur commune. Il faut pour cela joindre les informations de la table etablissements (nom de l’établissement) avec celles de la table communes (nom de la commune). L’élément qui permet cette jointure est la clef étrangère id_commune de la table etablissements, qui fait référence à la clef primaire Codecommune de la table communes : la jointure apparie chaque établissement avec la commune dont le code est égal à son id_commune.

Appariement de trois établissements avec leurs communes : chaque valeur de id_commune est reliée par une flèche à la ligne de la table communes qui a le même Codecommune (14 pour AURILLAC, 120 pour MAURIAC, 187 pour SAINT-FLOUR).

SELECT etablissements.nom, communes.Nomdelacommune
FROM etablissements JOIN communes
ON etablissements.id_commune = communes.Codecommune
nom Nomdelacommune
Lycée Émile Duclaux AURILLAC
Collège Jules Ferry AURILLAC
Collège Saint-Joseph AURILLAC
Collège Marcel Pagnol MAURIAC
Lycée Blaise Pascal SAINT-FLOUR
Collège Notre-Dame SAINT-FLOUR

Remarque. Lorsqu’on effectue une jointure, plusieurs tables sont en jeu. Pour davantage de clarté, il est recommandé de préfixer chaque attribut par le nom de la table dont il provient, à l’aide d’un point : etablissements.nom est l’attribut nom de la relation etablissements. Le préfixe devient obligatoire quand deux tables possèdent un attribut de même nom (SQL signale alors une erreur d’attribut ambigu). Le mot-clef JOIN seul est équivalent à INNER JOIN, que l’on rencontre souvent dans les sujets.

On peut ajouter à la suite de la jointure une clause WHERE afin de ne sélectionner que quelques lignes de la table obtenue :

SELECT etablissements.nom, communes.Nomdelacommune
FROM etablissements JOIN communes
ON etablissements.id_commune = communes.Codecommune
WHERE etablissements.nom LIKE 'Collège%'

Résultat : la table de tous les collèges avec leur commune : Jules Ferry et Saint-Joseph (AURILLAC), Marcel Pagnol (MAURIAC), Notre-Dame (SAINT-FLOUR).

On peut renommer une table (alias) ou un attribut du résultat avec le mot-clef AS, ce qui allège l’écriture des jointures :

SELECT e.nom, c.Nomdelacommune AS commune
FROM etablissements AS e JOIN communes AS c
ON e.id_commune = c.Codecommune
WHERE c.Populationtotale > 5000

Résultat : les établissements des communes de plus de 5 000 habitants (AURILLAC et SAINT-FLOUR), avec l’en-tête commune pour la seconde colonne.

Pour joindre trois tables A, B et C, on enchaîne les jointures :

SELECT ...
FROM A JOIN B ON A.cle_b = B.cle
       JOIN C ON B.cle_c = C.cle

Attention. Deux pièges classiques avec les jointures. D’abord, si l’on oublie la condition ON, SQL associe chaque ligne de la première table à chaque ligne de la seconde : sur notre extrait, on obtiendrait 6 \times 6 = 36 lignes, la plupart absurdes. Ensuite, une jointure ne renvoie que les lignes qui ont une correspondance : les communes LABROUSSE et VALJOUZE, sans aucun établissement, n’apparaissent pas dans le résultat de la requête précédente.

2 Requêtes de mise à jour

2.1 Ajouter une entrée

Pour ajouter une entrée, on utilise la clause INSERT. Supposons qu’un nouvel établissement soit ouvert à Labrousse (code de commune 85) : il s’agit d’un lycée hôtelier public. La requête suivante permet de créer cette nouvelle entrée. Attention, l’ordre des valeurs doit être strictement le même que l’ordre des attributs cités.

INSERT INTO etablissements
(code, nom, statut, id_commune, latitude, longitude)
VALUES
('0159999Z', 'Lycée hôtelier du Cantal', 'Public', 85, 44.8572222, 2.5427778)

2.2 Modifier une entrée existante

Pour modifier un ou plusieurs attributs d’un p-uplet existant, on utilise la clause UPDATE. Supposons que le nouveau lycée soit en fait un lycée privé, dont le nom doit être corrigé :

UPDATE etablissements
SET nom = 'Lycée hôtelier privé du Cantal', statut = 'Privé'
WHERE code = '0159999Z'

La clause WHERE permet de spécifier le ou les p-uplets à modifier. On évite en général de modifier une clef primaire.

2.3 Supprimer une entrée

Pour supprimer un p-uplet, on utilise la clause DELETE. Finalement, le projet de lycée hôtelier est abandonné :

DELETE FROM etablissements
WHERE code = '0159999Z'

Attention. Les requêtes DELETE et UPDATE sans clause WHERE s’appliquent à tous les p-uplets de la relation : DELETE FROM etablissements supprime tous les établissements !

2.4 Les contraintes d’intégrité en action

Lors de chaque mise à jour, le SGBD vérifie les contraintes d’intégrité et refuse l’opération si l’une d’elles est violée. Avec SQLite, par exemple :

INSERT INTO etablissements
VALUES ('0150001A', 'Doublon', 'Public', 14, 0, 0)
-- refusé : UNIQUE constraint failed: etablissements.code
-- (contrainte de relation : la clef primaire existe déjà)

INSERT INTO etablissements
VALUES ('0159998Z', 'Sans commune', 'Public', 999, 0, 0)
-- refusé : FOREIGN KEY constraint failed
-- (contrainte de référence : aucune commune n'a le code 999)

DELETE FROM communes WHERE Codecommune = 14
-- refusé : FOREIGN KEY constraint failed
-- (des établissements font encore référence à AURILLAC)

Remarque. Dans SQLite, la vérification des clefs étrangères doit être activée avec la commande PRAGMA foreign_keys = ON; (selon le logiciel utilisé, cette option est activée par défaut ou non : à vérifier avant de faire ces essais). De plus, SQLite autorise par défaut une clef primaire de type TEXT à valoir NULL si l’on n’a pas écrit NOT NULL à la création de la table.

3 Quelques compléments

Le langage SQL propose aussi des fonctions d’agrégation permettant de faire des calculs à partir des données d’une table : SUM (somme), AVG (moyenne), MIN et MAX (minimum et maximum) et COUNT (comptage). Sans GROUP BY (hors programme), la fonction s’applique à l’ensemble des lignes sélectionnées et renvoie une seule valeur. Les résultats ci-dessous sont ceux de la base complète Cantal.db.

Calculer une somme

La somme des populations de toutes les communes du Cantal :

SELECT SUM(Populationtotale)
FROM communes

Résultat : 149 664.

Calculer une moyenne

La population moyenne des communes du Cantal dont le code postal est 15250 (AVG = average) :

SELECT AVG(Populationtotale)
FROM communes
WHERE Codepostal = 15250

Résultat : 1 099,67.

Calculer un minimum ou un maximum

Les fonctions MIN et MAX fonctionnent de la même façon. Quel est le nom et la population de la commune du Cantal la moins peuplée ?

SELECT Nomdelacommune, Populationtotale
FROM communes
WHERE Populationtotale = (SELECT MIN(Populationtotale) FROM communes)

Résultat : VALJOUZE, 23 habitants.

Remarque. Ce dernier exemple est un complément un peu plus difficile que les précédents : on a imbriqué deux requêtes l’une dans l’autre (on parle de requêtes composées ; ce n’est pas une exigence du programme). La requête SELECT MIN(Populationtotale) FROM communes, située entre parenthèses, renvoie la valeur minimale des populations de toutes les communes. On demande ensuite le nom de la ou des communes dont la population est égale à cette valeur minimale. Prenez le temps de bien comprendre cet exemple. Écrire directement SELECT Nomdelacommune, MIN(Populationtotale) FROM communes fonctionne avec SQLite mais n’est pas portable vers d’autres SGBD.

Compter des données

La fonction COUNT permet de compter des données. Combien y a-t-il d’établissements scolaires dans le Cantal ?

SELECT COUNT(*)
FROM etablissements

Résultat : 203.

Combien de communes possèdent le code postal 15250 ?

SELECT COUNT(*)
FROM communes
WHERE Codepostal = 15250

Résultat : 9.

Combien de noms d’établissements différents parmi les établissements scolaires ?

SELECT COUNT(DISTINCT nom)
FROM etablissements

Résultat : 88.

Remarque. COUNT(*) compte les lignes ; COUNT(attribut) ne compte que celles où l’attribut n’est pas NULL.

À quelle question répond la requête suivante ?

SELECT COUNT(*)
FROM etablissements JOIN communes
ON etablissements.id_commune = communes.Codecommune
WHERE (etablissements.nom LIKE 'Collège%') AND communes.Nomdelacommune = 'AURILLAC'

Réponse : combien y a-t-il de collèges à Aurillac ? Résultat : 5.

4 Erreurs fréquentes

  • Oublier le ON d’une jointure : on obtient toutes les combinaisons possibles de lignes (produit cartésien).
  • Ne pas préfixer un attribut présent dans plusieurs tables : SQL signale un attribut ambigu.
  • Oublier le WHERE dans un UPDATE ou un DELETE : toutes les lignes sont modifiées ou supprimées.
  • Oublier les apostrophes autour d’un texte : statut = Privé est lu comme une comparaison avec un attribut nommé Privé.
  • Confondre AND et OR, ou oublier les parenthèses quand on mélange les deux.
  • Tester l’absence de valeur avec = NULL : il faut écrire IS NULL.

5 Pour aller plus loin : créer les tables (hors programme)

Les instructions précédentes manipulent des tables qui existent déjà. Voici, à titre de culture, comment le schéma relationnel de Cantal.db se traduit en SQL ; on y retrouve les domaines, les clefs primaires et la clef étrangère.

CREATE TABLE communes (
    Codecommune      INTEGER PRIMARY KEY,
    Nomdelacommune   TEXT,
    Populationtotale INTEGER,
    Codepostal       INTEGER
);

CREATE TABLE etablissements (
    code        TEXT PRIMARY KEY NOT NULL,
    nom         TEXT,
    statut      TEXT,
    id_commune  INTEGER,
    latitude    REAL,
    longitude   REAL,
    FOREIGN KEY (id_commune) REFERENCES communes(Codecommune)
);

À retenir

  • Une requête d’interrogation s’écrit SELECT … FROM … [JOIN … ON …] [WHERE …] [ORDER BY …], dans cet ordre.
  • DISTINCT supprime les doublons ; ORDER BY trie (ASC par défaut, DESC pour décroissant).
  • Une jointure apparie les p-uplets de deux tables en comparant une clef étrangère et la clef primaire qu’elle référence : JOIN … ON clef_étrangère = clef_primaire.
  • INSERT INTO … VALUES …, UPDATE … SET … WHERE … et DELETE FROM … WHERE … modifient le contenu de la base ; sans WHERE, UPDATE et DELETE agissent sur toutes les lignes.
  • Le SGBD refuse toute mise à jour qui viole une contrainte d’intégrité (domaine, relation, référence).
  • Les fonctions d’agrégation SUM, AVG, MIN, MAX, COUNT calculent une valeur à partir des lignes sélectionnées.

Pour compléter : excellentes vidéos sur Lumni : « Qu’est-ce qu’une base de données relationnelle ? » et « Interrogation d’une base de données relationnelle ».