Le langage SQL : interroger une base
Partie 4 sur 4
9Modifier les données
9.1INSERT
Un utilisateur qui dispose des droits nécessaires peut ajouter, modifier ou supprimer des lignes, que la théorie relationnelle appelle des tuples. L’ordre INSERT ajoute des lignes ; il est suivi de la clause INTO, du nom de la table et de la liste des colonnes à remplir ; les colonnes omises reçoivent leur valeur par défaut, le plus souvent NULL. Les valeurs sont données de deux façons :
-- avec VALUES : une ou plusieurs lignes, valeurs dans l'ordre des colonnes
INSERT INTO occaz (Marque, Modele, Serie, Numero, Compteur)
VALUES ('Fiat', 'Punto', 'GTI', '8941 UD 61', 80232),
('Audi', 'A4', 'Quattro', '7846 AZS 75', 21350);
-- avec SELECT : autant de lignes que la requête en renvoie
INSERT INTO archives (Marque, Modele, Numero)
SELECT Marque, Modele, Numero FROM occaz WHERE Compteur > 150000;
Quand on remplit toutes les colonnes, dans l’ordre de la table, la liste des colonnes est facultative, mais la donner rend la requête plus robuste aux évolutions de la table. Les chaînes de caractères doivent être délimitées, par des apostrophes selon la norme. On précise parfois qu’on ne peut pas lire, dans le SELECT, la table dans laquelle on insère : c’était une limite de certains SGBD de l’époque, pas une règle de SQL.
9.2UPDATE
L’ordre UPDATE modifie des lignes existantes ; on parle aussi de mise à jour. La clause SET indique la nouvelle valeur de chaque colonne, constante, expression ou résultat d’une sous-requête ; la clause WHERE désigne les lignes concernées :
UPDATE occaz SET Compteur = 98000 WHERE Numero = '8562 EV 23';
9.3DELETE
L’ordre DELETE supprime des lignes : il est suivi de FROM, du nom de la table, puis d’une clause WHERE qui désigne les lignes à supprimer.
DELETE FROM occaz WHERE Compteur > 180000;
Ces ordres sont à utiliser avec précaution. Un UPDATE ou un DELETE sans WHERE s’applique à toutes les lignes de la table (exercice 7). On qualifie la suppression d’irréversible ; c’est vrai une fois la modification validée, mais on peut se protéger : vérifier d’abord, avec un SELECT utilisant la même condition, quelles lignes seront touchées ; travailler dans une transaction, qui permet d’annuler avec ROLLBACK tant qu’on n’a pas validé avec COMMIT (voir les bases de données) ; disposer de sauvegardes.
BEGIN; DELETE FROM occaz WHERE Compteur > 180000; -- vérifier le nombre de lignes supprimées, puis : COMMIT; -- ou ROLLBACK; pour tout annuler
Lorsque des tables sont liées, par exemple des commandes qui font référence à des clients, il faut supprimer d’abord les lignes de la table qui fait référence, ici les commandes, puis celles de la table référencée, les clients : on parle de suppression en cascade ascendante. Les SGBD actuels peuvent l’automatiser grâce aux contraintes de clés étrangères, avec l’option ON DELETE CASCADE, ou au contraire refuser la suppression d’un client qui a encore des commandes (voir SQL : tables, contraintes et droits).
Pour s’exercer
Exercice 7 : la condition oubliée
Une employée veut corriger le compteur de la Ford et tape UPDATE occaz SET Compteur = 98000;, puis valide. Que s’est-il passé ? Comment l’éviter ?
Voir la réponse
Sans clause WHERE, la mise à jour s’applique à toutes les lignes : les six voitures ont désormais 98 000 km, et les anciennes valeurs sont perdues. Il faut restaurer la table depuis une sauvegarde. Pour l’éviter : écrire d’abord le SELECT avec la condition, SELECT * FROM occaz WHERE Numero = '8562 EV 23', vérifier le résultat, puis le transformer en UPDATE ; travailler dans une transaction et contrôler le nombre de lignes modifiées, que le SGBD affiche, avant de valider ; activer dans l’outil le mode qui refuse les mises à jour sans clé dans le WHERE, comme le « mode sûr » de MySQL ; et ne pas donner aux comptes du quotidien le droit de modifier les tables de production.
10Pour aller plus loinApprofondir
10.1CASE et les fonctions
L’expression CASE calcule une valeur selon des conditions, comme un « si… alors… sinon » à l’intérieur d’une requête. Les SGBD fournissent aussi de nombreuses fonctions sur les chaînes, les nombres et les dates, dont les noms varient malheureusement d’un système à l’autre.
SELECT Marque, Modele,
CASE
WHEN Compteur IS NULL THEN 'inconnu'
WHEN Compteur < 50000 THEN 'faible'
WHEN Compteur < 150000 THEN 'moyen'
ELSE 'élevé'
END AS kilometrage
FROM occaz;
10.2Les fonctions de fenêtrage
Les fonctions de fenêtrage, apparues dans SQL:2003, calculent une valeur pour chaque ligne à partir d’un ensemble de lignes voisines, sans les regrouper. Elles permettent les classements, les totaux cumulés ou les comparaisons avec la ligne précédente :
SELECT Marque, Modele, Compteur,
RANK() OVER (PARTITION BY Marque ORDER BY Compteur DESC) AS rang,
AVG(Compteur) OVER (PARTITION BY Marque) AS moyenne_marque
FROM occaz;
Chaque voiture garde sa ligne, accompagnée de son rang dans sa marque et de la moyenne de sa marque : un calcul qui exigerait, sans ces fonctions, des sous-requêtes ou des jointures complexes.
10.3Comprendre l’exécution
La commande EXPLAIN, disponible dans la plupart des SGBD, affiche le plan d’exécution choisi par l’optimiseur : quelles tables sont lues en entier, quels index sont utilisés, dans quel ordre se font les jointures. C’est le premier outil pour comprendre pourquoi une requête est lente, et décider de créer un index (voir SQL : tables, contraintes et droits).
11SQL dans les programmes
Les applications envoient leurs requêtes au SGBD par l’intermédiaire de bibliothèques, appelées pilotes ou connecteurs, disponibles pour tous les langages. Beaucoup de projets utilisent aussi un outil de correspondance objet-relationnel (en anglais Object-Relational Mapping, ORM), qui génère le SQL à partir d’objets du programme, comme Hibernate en Java, Entity Framework en C# ou SQLAlchemy en Python (voir la programmation orientée objet). Ces outils font gagner du temps, mais il reste indispensable de comprendre le SQL qu’ils produisent, notamment pour les performances.
La règle d’or est de ne jamais construire une requête en collant du texte saisi par l’utilisateur, mais d’utiliser des requêtes paramétrées, où les valeurs sont transmises séparément du texte de la requête :
# Python, avec la bibliothèque standard sqlite3
cur.execute(
"SELECT * FROM occaz WHERE Marque = ? AND Compteur < ?",
(marque_saisie, plafond)
)
Le SGBD reçoit d’un côté la structure de la requête, avec des emplacements marqués par ?, de l’autre les valeurs : quoi que contienne la saisie, elle sera traitée comme une simple donnée, jamais comme du code SQL (coin cyber).
SQL a par ailleurs largement débordé des bases relationnelles classiques : les entrepôts de données dans le cloud, les moteurs de traitement de très gros volumes et des outils d’analyse comme DuckDB l’utilisent pour interroger des fichiers de données (voir le big data). Et les assistants d’intelligence artificielle savent désormais traduire une question en français en requête SQL ; c’est pratique, à condition de relire la requête et de limiter les droits de ce qui l’exécute (voir programmer avec l’IA).