Le langage SQL

Cours complet · NSI (terminale), chapitre 12 · terminale, spécialité numérique et sciences informatiques

Travailler ce chapitre sur Adloun Exercices corrigés de ce chapitre

Les bases de données relationnelles, étudiées dans le chapitre précédent, organisent l'information sous forme de tables liées entre elles. Pour interroger, modifier et structurer ces données, on utilise un langage spécialisé : le SQL (Structured Query Language). Né dans les années 1970, il est aujourd'hui le standard universel des systèmes de gestion de bases de données relationnelles (SGBDR) comme SQLite, PostgreSQL, MySQL ou Oracle.

Dans ce chapitre, nous apprenons à écrire des requêtes pour extraire des données (SELECT), à les agréger et les regrouper, à combiner plusieurs tables grâce aux jointures, puis à modifier les données (INSERT, UPDATE, DELETE) et enfin à créer la structure des tables (CREATE TABLE). Tous les exemples sont écrits en SQL standard, compatibles notamment avec SQLite.

Pour fixer les idées, nous utiliserons tout au long du chapitre une base de données de bibliothèque. Voici la table Livre qui servira de fil rouge.

id	titre	auteur	annee	prix
1	Le Petit Prince	Saint-Exupéry	1943	8
2	1984	Orwell	1949	10
3	La Ferme des animaux	Orwell	1945	7
4	Fahrenheit 451	Bradbury	1953	9
5	Le Meilleur des mondes	Huxley	1932	11

12.1 Interroger une table : la requête SELECT

12.1.1 Sélection de colonnes

La requête SELECT est le cœur du SQL : elle permet de lire des données dans une ou plusieurs tables.

Définition 12.1La requête SELECT

La forme la plus simple d'une requête de sélection est :


SELECT colonne1, colonne2 FROM nom_table;

Elle renvoie le contenu des colonnes indiquées pour toutes les lignes de la table. On peut remplacer la liste des colonnes par l'étoile pour obtenir toutes* les colonnes.

Exemple 12.2Lire des colonnes

Pour afficher le titre et l'auteur de tous les livres :


SELECT titre, auteur FROM Livre;

Pour afficher la table entière :


SELECT * FROM Livre;

12.1.2 Filtrer les lignes avec WHERE

Définition 12.3La clause WHERE

La clause WHERE permet de ne conserver que les lignes vérifiant une condition :


SELECT colonnes FROM table WHERE condition;

La condition combine des comparaisons (=, <>, <, >, <=, >=), des opérateurs logiques (AND, OR, NOT) et des mots-clés comme LIKE, IN ou BETWEEN.

Proposition 12.4Quelques opérateurs de condition

LIKE 'O%' : la valeur commence par « O » (le % remplace une suite quelconque de caractères, _ un seul caractère).
IN (1949, 1945) : la valeur appartient à la liste donnée.
BETWEEN 1940 AND 1950 : la valeur est dans l'intervalle (bornes incluses).

Exemple 12.5Filtrer

Les livres publiés après 1945 :


SELECT titre, annee FROM Livre WHERE annee > 1945;

Les livres d'Orwell coûtant moins de 10 euros :


SELECT titre FROM Livre WHERE auteur = 'Orwell' AND prix < 10;

12.1.3 Trier, dédoublonner, limiter

Méthode : ORDER BY, DISTINCT et LIMIT

Ces trois clauses affinent le résultat d'une requête :

ORDER BY colonne ASC (ou DESC) trie les lignes par ordre croissant (par défaut) ou décroissant.
DISTINCT, placé juste après SELECT, supprime les lignes en double dans le résultat.
LIMIT n ne garde que les n premières lignes du résultat.

L'ordre d'écriture est imposé :

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

Exemple 12.6Trier et limiter

Les trois livres les plus chers, du plus cher au moins cher :


SELECT titre, prix FROM Livre ORDER BY prix DESC LIMIT 3;

La liste des auteurs, sans répétition :


SELECT DISTINCT auteur FROM Livre;

12.2 Les fonctions d'agrégation

12.2.1 Calculer sur un ensemble de lignes

Définition 12.7Fonctions d'agrégation

Une fonction d'agrégation calcule une valeur unique à partir d'un ensemble de lignes. Les cinq principales sont :

COUNT(*) : nombre de lignes.
SUM(col) : somme des valeurs d'une colonne numérique.
AVG(col) : moyenne des valeurs.
MIN(col) et MAX(col) : valeur minimale et maximale.

Exemple 12.8Statistiques globales

Combien y a-t-il de livres, et quel est leur prix moyen ?


SELECT COUNT(*), AVG(prix) FROM Livre;

Le prix du livre le plus cher et celui du moins cher :


SELECT MAX(prix), MIN(prix) FROM Livre;

12.2.2 Regrouper avec GROUP BY et filtrer avec HAVING

Définition 12.9GROUP BY

La clause GROUP BY colonne regroupe les lignes ayant la même valeur dans la colonne indiquée. Les fonctions d'agrégation sont alors calculées pour chaque groupe séparément.

Proposition 12.10HAVING : filtrer les groupes

La clause WHERE filtre les lignes avant le regroupement ; elle ne peut pas porter sur une agrégation. Pour filtrer les groupes après calcul, on utilise HAVING :


SELECT colonne, COUNT(*)
FROM table
GROUP BY colonne
HAVING COUNT(*) > 1;

Exemple 12.11Nombre de livres par auteur


SELECT auteur, COUNT(*) AS nb
FROM Livre
GROUP BY auteur;

Le mot-clé AS renomme la colonne de résultat (alias). Pour ne garder que les auteurs ayant écrit plus d'un livre :


SELECT auteur, COUNT(*) AS nb
FROM Livre
GROUP BY auteur
HAVING COUNT(*) > 1;

12.3 Combiner plusieurs tables : les jointures

Une base de données relationnelle répartit l'information entre plusieurs tables liées par des clés étrangères. Pour rassembler ces données dans une même requête, on utilise une jointure.

Définition 12.12Clé étrangère

Une clé étrangère est une colonne d'une table dont les valeurs référencent la clé primaire d'une autre table. Elle matérialise le lien entre les deux tables et garantit l'intégrité référentielle (on ne peut pas référencer une ligne qui n'existe pas).

Considérons deux tables supplémentaires : Emprunteur et Emprunt.

id	nom
1	Alice
2	Bob
3	Chloé

id	id_livre	id_emprunteur
1	2	1
2	3	1
3	2	3

Ici, dans la table Emprunt, id_livre est une clé étrangère vers Livre(id) et id_emprunteur une clé étrangère vers Emprunteur(id).

Méthode : La jointure interne INNER JOIN

Pour combiner deux tables sur la base de leur lien, on écrit :


SELECT colonnes
FROM tableA
INNER JOIN tableB ON tableA.cle = tableB.cle_etrangere;

La condition placée après ON précise les colonnes qui doivent correspondre. L'INNER JOIN ne conserve que les lignes ayant une correspondance dans les deux tables. On préfixe les colonnes par le nom de leur table (Livre.titre) en cas d'ambiguïté.

Exemple 12.13Qui a emprunté quoi ?

Pour afficher le nom de l'emprunteur et le titre du livre emprunté :


SELECT Emprunteur.nom, Livre.titre
FROM Emprunt
INNER JOIN Livre ON Emprunt.id_livre = Livre.id
INNER JOIN Emprunteur ON Emprunt.id_emprunteur = Emprunteur.id;

On enchaîne ici deux jointures pour relier les trois tables.

12.4 Modifier les données

12.4.1 Ajouter des lignes : INSERT

Définition 12.14INSERT INTO

La commande INSERT ajoute une nouvelle ligne dans une table :


INSERT INTO table (col1, col2, ...) VALUES (val1, val2, ...);

Les valeurs sont fournies dans l'ordre des colonnes énumérées. Les chaînes de caractères sont entre apostrophes simples.

Exemple 12.15Ajouter un livre


INSERT INTO Livre (id, titre, auteur, annee, prix)
VALUES (6, 'Le Monde de Sophie', 'Gaarder', 1991, 12);

12.4.2 Modifier et supprimer : UPDATE et DELETE

Méthode : UPDATE et DELETE

UPDATE modifie des lignes existantes, DELETE les supprime :


UPDATE table SET col1 = val1, col2 = val2 WHERE condition;

DELETE FROM table WHERE condition;

Attention : sans clause WHERE, la modification ou la suppression s'applique à toutes les lignes de la table !

Exemple 12.16Mettre à jour et supprimer

Augmenter de 1 euro le prix de tous les livres d'Orwell :


UPDATE Livre SET prix = prix + 1 WHERE auteur = 'Orwell';

Supprimer le livre dont l'identifiant est 5 :


DELETE FROM Livre WHERE id = 5;

12.5 Créer la structure d'une table

Définition 12.17CREATE TABLE

La commande CREATE TABLE définit une nouvelle table en précisant le nom et le type de chaque colonne (INTEGER, REAL, TEXT, etc.) :


CREATE TABLE nom_table (
    colonne1 TYPE contraintes,
    colonne2 TYPE contraintes,
    ...
);

Proposition 12.18Les contraintes courantes

Les contraintes garantissent la cohérence des données :

PRIMARY KEY : clé primaire (identifiant unique et non nul).
NOT NULL : la colonne doit toujours avoir une valeur.
UNIQUE : toutes les valeurs de la colonne sont distinctes.
DEFAULT v : valeur par défaut si aucune n'est fournie.
FOREIGN KEY (col) REFERENCES autre_table(cle) : déclare une clé étrangère.

Exemple 12.19Créer les tables de la bibliothèque


CREATE TABLE Livre (
    id      INTEGER PRIMARY KEY,
    titre   TEXT NOT NULL,
    auteur  TEXT NOT NULL,
    annee   INTEGER,
    prix    REAL DEFAULT 0
);

CREATE TABLE Emprunt (
    id              INTEGER PRIMARY KEY,
    id_livre        INTEGER,
    id_emprunteur   INTEGER,
    FOREIGN KEY (id_livre) REFERENCES Livre(id),
    FOREIGN KEY (id_emprunteur) REFERENCES Emprunteur(id)
);

Continuer sur Adloun : animation, QCM, fiches, exercices