6. Langage DML

Ce chapitre décrit les instructions de manipulation des données (DML) : INSERT, UPDATE, DELETE, les requêtes paramétrées et les instructions préparées (PREPARE / EXECUTE / DEALLOCATE PREPARE).

Les exemples s'appuient sur deux tables utilisées tout au long de ce chapitre :

CREATE TABLE clients (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    nom     VARCHAR(50) NOT NULL,
    ville   VARCHAR(50),
    solde   DECIMAL(10,2) DEFAULT 0
);

CREATE TABLE commandes (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    montant  DECIMAL(10,2) NOT NULL,
    date_cmd DATE
);

6.1 INSERT#

Synopsis#

INSERT [IGNORE] INTO table [(colonne, ...)]
    VALUES (valeur, ...) [, (valeur, ...) ...]
    | SET colonne = valeur [, colonne = valeur ...]
    | requête_select
    [ON DUPLICATE KEY UPDATE colonne = valeur [, colonne = valeur ...]]

REPLACE INTO table [(colonne, ...)]
    VALUES (valeur, ...) [, (valeur, ...) ...]
    | requête_select

Insertion d'une ligne ou de plusieurs lignes#

INSERT INTO clients (nom, ville) VALUES ('Alice', 'Oran');

INSERT INTO clients (nom, ville, solde) VALUES
    ('Bob', 'Alger', 50.00),
    ('Chloé', 'Oran', 0),
    ('David', 'Constantine', NULL);

La forme SET affecte les colonnes une par une, sans liste de colonnes ni liste de valeurs séparée :

INSERT INTO clients SET nom = 'Émile', solde = 10;

Une ligne peut être vide (VALUES ()) quand la table n'a que des colonnes à valeur par défaut ou nullables. Une valeur peut être DEFAULT, qui vaut explicitement la valeur par défaut de la colonne :

INSERT INTO clients (nom, solde) VALUES ('Fatima', DEFAULT);

INSERT ... SELECT#

Les lignes insérées viennent du résultat d'une requête plutôt que d'une liste VALUES :

CREATE TABLE gros_clients (client_id INT, total DECIMAL(10,2));

INSERT INTO gros_clients
SELECT client_id, SUM(montant)
FROM commandes
GROUP BY client_id
HAVING SUM(montant) > 100;

INSERT IGNORE transforme en avertissement les erreurs qui, sinon, feraient échouer la ligne (valeur trop longue, NOT NULL violé, doublon de clé) : la ligne fautive est sautée, les autres sont insérées.

AUTO_INCREMENT#

Une colonne AUTO_INCREMENT reçoit automatiquement la valeur suivante quand l'insertion ne lui donne pas de valeur (colonne absente de la liste, ou NULL explicite) :

INSERT INTO clients (nom) VALUES ('Grace');
SELECT LAST_INSERT_ID();   -- valeur attribuée à la ligne qui vient d'être insérée

Donner explicitement une valeur plus grande que le compteur courant le fait avancer d'autant ; les insertions suivantes repartent au-delà de cette valeur.

DEFAULT et colonnes générées#

Une colonne omise de la liste des colonnes reçoit sa valeur par défaut (DEFAULT de la définition, ou NULL si la colonne est nullable sans DEFAULT). Un DEFAULT peut être une expression, évaluée ligne par ligne et pouvant citer les autres colonnes de la même ligne.

Une colonne générée (GENERATED ALWAYS AS (expr), VIRTUAL ou STORED/PERSISTENT) n'est jamais renseignée par l'insertion : une valeur explicite donnée pour une colonne générée est ignorée (elle n'est pas refusée), et la colonne prend la valeur de son expression.

REPLACE#

REPLACE INTO insère une ligne comme INSERT, mais si elle entre en conflit avec une ligne existante sur une clé primaire ou une contrainte UNIQUE, la ligne en conflit est d'abord supprimée avant l'insertion (déclencheurs DELETE et INSERT exécutés comme pour ces instructions) :

REPLACE INTO clients (id, nom, ville) VALUES (1, 'Alice', 'Alger');

REPLACE accepte aussi une source SELECT :

REPLACE INTO clients (id, nom) SELECT id + 100, nom FROM clients WHERE ville = 'Oran';

ON DUPLICATE KEY UPDATE#

Une alternative à REPLACE qui met à jour la ligne en conflit au lieu de la remplacer : la ligne existante garde son identité (pas de suppression, pas de nouveau déclenchement DELETE/INSERT pour elle) et seules les colonnes citées dans ON DUPLICATE KEY UPDATE sont modifiées.

INSERT INTO clients (id, nom, solde) VALUES (1, 'Alice', 100)
ON DUPLICATE KEY UPDATE solde = solde + VALUES(solde);

VALUES(colonne) désigne, dans la clause ON DUPLICATE KEY UPDATE, la valeur que la ligne en conflit aurait reçue à l'insertion. Une ligne insérée avec VALUES ... AS alias peut aussi être désignée par son alias :

INSERT INTO clients (id, nom, solde) VALUES (1, 'Alice', 100) AS nouvelle
ON DUPLICATE KEY UPDATE solde = solde + nouvelle.solde;

Erreurs typiques#

6.2 UPDATE#

Synopsis#

UPDATE table [[AS] alias]
    SET colonne = valeur [, colonne = valeur ...]
    [WHERE condition]
    [ORDER BY ...] [LIMIT n]

UPDATE table1 [[AS] alias1]
    {[INNER] JOIN | CROSS JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN} table2 [[AS] alias2]
        {ON condition | USING (colonne, ...)}
    [...autres jointures...]
    SET colonne = valeur [, colonne = valeur ...]
    [WHERE condition]

UPDATE mono-table#

UPDATE clients SET solde = solde + 20 WHERE ville = 'Oran';

ORDER BY et LIMIT, sur un UPDATE d'une seule table, choisissent quelles lignes parmi celles retenues par WHERE sont effectivement modifiées :

UPDATE clients SET solde = 0 WHERE solde < 0 ORDER BY id LIMIT 1;

UPDATE multi-tables (mise à jour avec jointure)#

UPDATE accepte les mêmes types de jointure qu'un SELECT (voir chapitre 7) : JOIN / INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, avec ON ou USING, des alias, et une table dérivée (sous-requête) comme table jointe. Une seule table est réellement modifiée par l'instruction : celle nommée dans la clause SET. Les autres tables de la jointure ne servent qu'à calculer les nouvelles valeurs et à filtrer les lignes.

UPDATE clients c
JOIN commandes o ON o.client_id = c.id
SET c.solde = c.solde - o.montant
WHERE o.id = 42;

Avec USING, quand les deux tables ont une colonne de même nom :

UPDATE clients c
LEFT JOIN commandes o USING (id)
SET c.solde = c.solde + 1
WHERE o.id IS NULL;   -- clients sans commande n° 1..N (selon la jointure)

Avec une table dérivée comme source des valeurs :

UPDATE clients c
CROSS JOIN (
    SELECT client_id, SUM(montant) AS total
    FROM commandes
    GROUP BY client_id
) t ON t.client_id = c.id
SET c.solde = c.solde - t.total;

Restrictions propres à l'UPDATE multi-tables (vérifiées par le moteur) :

• SET ne peut affecter que des colonnes de la table réellement modifiée ; affecter une colonne d'une autre table de la jointure échoue.
• ORDER BY et LIMIT ne sont pas admis dès qu'il y a plusieurs tables.
• La table modifiée ne peut pas être une vue non modifiable.

Erreurs typiques#

6.3 DELETE#

Synopsis#

DELETE FROM table [WHERE condition] [ORDER BY ...] [LIMIT n]

DELETE table1 [, table2 ...] FROM table1 [JOIN ...] [WHERE condition]
DELETE FROM table1 USING table1 [JOIN ...] [WHERE condition]

DELETE simple#

DELETE FROM commandes WHERE montant IS NULL;
DELETE FROM clients WHERE solde = 0 ORDER BY id LIMIT 1;
DELETE FROM commandes;   -- vide la table (une ligne à la fois, contrairement à TRUNCATE)

DELETE multi-tables#

Deux formes équivalentes suppriment des lignes d'une ou plusieurs tables d'une jointure, en se servant des autres tables jointes uniquement pour filtrer :

-- table(s) à vider nommées avant FROM
DELETE c FROM clients c
JOIN commandes o ON o.client_id = c.id
WHERE o.montant < 0;

-- table(s) à vider nommées après USING
DELETE FROM c USING clients c
JOIN commandes o ON o.client_id = c.id
WHERE o.montant < 0;

ORDER BY et LIMIT ne sont pas admis dans la forme multi-tables. Une table nommée devant FROM (ou après USING) qui ne fait pas partie de la jointure est une erreur (table inconnue) ; une table de la jointure qui n'est pas listée comme cible n'a pas ses lignes supprimées.

Erreurs typiques#

6.4 Requêtes paramétrées#

Deux styles de paramètres sont acceptés dans le texte SQL, à la place d'une valeur littérale :

SELECT * FROM clients WHERE ville = ? AND solde > ?;
SELECT * FROM clients WHERE ville = :ville AND solde > :seuil;

Les deux styles ne sont pas mélangés dans une même instruction. Les paramètres peuvent apparaître partout où une expression est attendue : WHERE, SET, VALUES, LIMIT, etc. C'est la forme recommandée pour toute valeur qui vient de l'utilisateur ou de l'application (via l'API, un connecteur réseau ou miraj-cli), plutôt que de composer le texte SQL par concaténation : les valeurs liées ne sont jamais interprétées comme du SQL, ce qui évite les injections et évite de reformater les littéraux (dates, chaînes à échapper, nombres) à la main.

EXECUTE ... USING (ci-dessous) lie des valeurs aux paramètres ? d'une instruction préparée, dans l'ordre.

6.5 PREPARE, EXECUTE, DEALLOCATE PREPARE#

Une instruction préparée sépare l'analyse du texte SQL (faite une fois par PREPARE) de son exécution (faite autant de fois que nécessaire par EXECUTE), avec des paramètres ? liés à chaque exécution. C'est la forme dynamique du SQL, utile quand le texte de l'instruction est construit par le code (nom de table variable, requête générée) plutôt qu'écrit une fois pour toutes.

Synopsis#

PREPARE nom FROM expression

EXECUTE nom [USING expression [, expression ...]]
EXECUTE IMMEDIATE expression [USING expression [, expression ...]]

{DEALLOCATE | DROP} PREPARE nom

• expression de PREPARE est le texte de l'instruction à préparer : un littéral chaîne, une variable utilisateur (@sql), ou toute expression qui produit une chaîne, évaluée à l'exécution de PREPARE. Le texte doit contenir exactement une instruction (un ; final est toléré).
• nom n'est pas sensible à la casse et désigne l'instruction préparée pour les EXECUTE et DEALLOCATE PREPARE suivants, dans la même session.
• Préparer de nouveau sous un nom déjà utilisé remplace silencieusement l'instruction préparée précédente.
• EXECUTE nom USING ... lie les valeurs de USING, dans l'ordre, aux paramètres ? du texte préparé ; leur nombre doit correspondre exactement à celui des paramètres.
• EXECUTE IMMEDIATE expression prépare, exécute puis oublie une instruction en une seule étape, sans lui donner de nom.
• DEALLOCATE PREPARE nom (ou DROP PREPARE nom, synonymes) libère l'instruction préparée ; les EXECUTE suivants sous ce nom échouent.

Cycle de vie#

1. PREPARE analyse le texte et le range sous un nom, pour la session courante.
2. EXECUTE (une ou plusieurs fois) lie les paramètres et exécute l'instruction déjà analysée.
3. DEALLOCATE PREPARE libère la préparation ; elle est aussi implicitement perdue à la fin de la session.

Exemple complet#

PREPARE lire FROM 'SELECT nom FROM clients WHERE id = ?';
EXECUTE lire USING 1;      -- 'Alice'
EXECUTE lire USING 3;      -- 'Chloé'
DEALLOCATE PREPARE lire;
EXECUTE lire USING 1;      -- erreur 1243 : instruction préparée inconnue

Texte construit dynamiquement (nom de table variable), un cas d'usage typique dans une routine :

SET @table = 'clients';
SET @sql = CONCAT('UPDATE ', @table, ' SET solde = solde * (1 - ?) WHERE id = ?');
PREPARE remise FROM @sql;
EXECUTE remise USING 0.10, 1;
DEALLOCATE PREPARE remise;

EXECUTE IMMEDIATE, pour une instruction à usage unique :

EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ', @table);
EXECUTE IMMEDIATE 'INSERT INTO clients (nom) VALUES (?)' USING 'Hicham';

Erreurs typiques#