Modifier une base de données avec SQL

Toute la leçon part des deux tables suivantes, créées ainsi :

CREATE TABLE classes (nom TEXT PRIMARY KEY, salle TEXT);
CREATE TABLE eleves (prenom TEXT PRIMARY KEY, classe TEXT REFERENCES classes(nom), note INTEGER, age INTEGER);

La table classes contient les classes A (salle B12), B (C04) et C (B07). Trois requêtes modifient les données. Chaque requête de cette leçon est exécutée sur ces tables de départ, et non à la suite des précédentes :

Insérer une ligne avec INSERT INTO … VALUES :

INSERT INTO eleves VALUES ('Gabin', 'B', 11, 16);
SELECT COUNT(prenom) FROM eleves;

La table compte alors 7 lignes.

Modifier des lignes avec UPDATE … SET … WHERE : seules les lignes qui vérifient la condition changent.

UPDATE eleves SET note = note + 2 WHERE classe = 'C';
SELECT prenom, note FROM eleves WHERE classe = 'C';

Supprimer des lignes avec DELETE FROM … WHERE :

DELETE FROM eleves WHERE note < 10;
SELECT prenom FROM eleves;

Sur la table de départ, Bilal (9) et Farah (8) sont supprimés : il reste Alice, Chloé, David et Emma.

Attention à l'ordre. Si l'on exécute réellement les trois requêtes à la suite (INSERT, puis UPDATE, puis DELETE), Farah a déjà 10 quand le DELETE arrive : seul Bilal est supprimé, et il reste Alice, Chloé, David, Emma, Farah et Gabin. Chaque requête porte sur la table telle que l'ont laissée les précédentes.

On peut nommer les colonnes à remplir, dans l'ordre que l'on veut :

INSERT INTO eleves (prenom, classe, note, age) VALUES ('Hugo', 'A', 13, 17);
SELECT * FROM eleves WHERE classe = 'A';

Une mise à jour doit respecter les contraintes d'intégrité de la base ; sinon, le système de gestion de bases de données la refuse.

Clé primaire unique. prenom est la clé primaire : on ne peut pas insérer un second « Alice ».

INSERT INTO eleves VALUES ('Alice', 'C', 11, 17);

UNIQUE constraint failed: eleves.prenom

Clé étrangère existante. La colonne classe fait référence à la clé primaire de classes : la classe D n'existe pas, et l'on ne peut pas non plus supprimer la classe B tant que des élèves y sont inscrits. Dans SQLite, ce contrôle doit être activé par PRAGMA foreign_keys = ON (sans cette ligne, SQLite accepte l'insertion d'Inès en classe D).

PRAGMA foreign_keys = ON;
INSERT INTO eleves VALUES ('Inès', 'D', 13, 16);

FOREIGN KEY constraint failed

PRAGMA foreign_keys = ON;
DELETE FROM classes WHERE nom = 'B';

FOREIGN KEY constraint failed

Contrainte de domaine. Chaque colonne a un domaine, l'ensemble des valeurs permises (ici le type TEXT ou INTEGER). Un système de gestion de bases de données refuse en principe une valeur hors du domaine. SQLite fait exception : son typage est souple, et il accepte le texte 'abc' dans la colonne note de type INTEGER de cette leçon. Il ne refuse une valeur hors du domaine que si la table est déclarée STRICT, ou si une contrainte CHECK exclut cette valeur :

CREATE TABLE notes (prenom TEXT PRIMARY KEY, note INTEGER) STRICT;
INSERT INTO notes VALUES ('Jade', 'abc');

cannot store TEXT value in INTEGER column notes.note

CREATE TABLE notes (prenom TEXT PRIMARY KEY, note INTEGER CHECK (note BETWEEN 0 AND 20));
INSERT INTO notes VALUES ('Jade', 25);

CHECK constraint failed: note BETWEEN 0 AND 20

Énoncé. Sur la table eleves de départ (somme des notes : 14 + 9 + 17 + 12 + 15 + 8 = 75), on exécute :

UPDATE eleves SET note = note + 3
WHERE note >= 12 AND classe = 'A';
SELECT SUM(note) FROM eleves;

Méthode.

1. Quelles lignes la clause WHERE désigne-t-elle ? Classe A et note au moins 12 : Alice (14) et Chloé (17).
2. Effet de la mise à jour : leurs notes augmentent de 3 ; les autres lignes ne changent pas.
3. Requête d'interrogation, sur la table APRÈS mise à jour : 75 + 2 × 3 = 81.

Variante avec DELETE.

DELETE FROM eleves
WHERE NOT classe = 'C' AND age >= 17;
SELECT COUNT(prenom) FROM eleves;

Seul Bilal n'est pas en classe C et a au moins 17 ans : une ligne supprimée, il en reste 6 − 1 = 5.

Sans clause WHERE, UPDATE et DELETE touchent toutes les lignes de la table.