Instructions DML : Instructions INSERT, UPDATE, DELETE et MERGE
Les instructions DML, tirant son nom de l'abréviation de l'anglicisme «Data Manipulation Language», sont l'élément du langage de programmation SQL utilisé pour la récupération et la manipulation des données. À l'aide de ces instructions, vous pouvez effectuer des opérations telles que : ajouter de nouvelles lignes, mettre à jour et supprimer des lignes existantes, fusionner des tables,...
Les instructions du DML sont les suivantes :
| Instruction | Description |
|---|---|
| INSERT | Ajouter de nouvelles lignes à une table |
| UPDATE | Mise à jour des valeurs des champs |
| DELETE | Supprimer des lignes d'une table |
| MERGE | Fusion de tables |
| SELECT | Affiche la liste d'un/des que contient un ou des tables |
Instruction INSERT
L'instruction INSERT de SQL Server est utilisée pour ajouter de nouvelles lignes à une table. Avant de vous familiariser avec cette déclaration, veuillez prendre en compte les points suivants :
- Valeur par défaut : lors de la création d'une nouvelle table (DDL), il est possible de définir une colonne spécifique avec une valeur par défaut. Cela signifie que, sauf indication contraire, il s'agit de la valeur que vous souhaitez saisir dans le champ de cette colonne. Par exemple : le salaire par défaut est 50000 et la date d'emploi par défaut est le «2021-11-01».
- Les démonstrations INSERT suivantes sont basées sur le tableau ci-dessous, où la valeur par défaut 50000 est attribuée à la colonne emp_sal et actuellement le tableau a une seule ligne :
| emp_no | emp_nom | emp_salaire | emp_dateembauche |
|---|---|---|---|
| 1 | Jean Tremblay | 50000 | 2021/11/01 |
L'insertion utilise une syntaxe simplifié suivante :
| INSERT INTO nom_table VALUES (valeur, valeur, valeur,...) |
Insérer une nouvelle ligne dans le tableau :
- INSERT INTO employees VALUES (2 , 'Jacques Gagnon' , 30200 , '2014/02/16')
Le tableau suivant reflète les données de la table employees une fois l'instruction INSERT terminée :
| emp_no | emp_nom | emp_salaire | emp_dateembauche |
|---|---|---|---|
| 1 | Jean Tremblay | 50000 | 2021/11/01 |
| 2 | Jacques Gagnon | 30200 | 2014/02/16 |
Les points importants
- L'ordre des valeurs doit correspondre à l'ordre des colonnes du tableau. Dans le cas ci-dessus, la première valeur doit être associée à la colonne du numéro d'employé, la deuxième valeur doit être associée à la colonne du nom de l'employé, et ainsi de suite.
- Le type de valeur doit correspondre au type de colonnes de la table. Par exemple, il n'est pas possible de saisir la valeur 37 dans la colonne emp_dateembauche.
- Le nombre de valeurs : doit correspondre au nombre de valeurs dans le tableau. Il n'est pas possible de saisir plus ou moins de valeurs que le nombre de valeurs dans le tableau (dans ce cas, 4 valeurs).
- Pour entrer une valeur NULL dans l'un des champs de l'une des colonnes, le mot-clef NULL doit être utilisé :
- INSERT INTO employees VALUES (3 , NULL , 38200 , '2013/09/12')
- Pour saisir une valeur DEFAULT dans l'un des champs de l'une des colonnes, le mot-clef DEFAULT doit être utilisé :
- INSERT INTO employees VALUES (4 , 'Johanne Tremblay', DEFAULT , '2015/08/09')
- GETDATE() : vous pouvez utiliser la fonction GETDATE(), pour entrée la date courante, au lieu de spécifier une date :
- INSERT INTO employees VALUES (5 , 'Anne Robitaille', DEFAULT , GETDATE())
Utilisation d'une liste de colonnes
L'exemple suivant utilise une liste de colonnes pour spécifier explicitement les valeurs qui sont insérées dans chaque colonne (cette syntaxe est plus stable si vous modifier la structure des tables) :
| INSERT INTO nom_table (nom_colonne, nom_colonne , nom_colonne ...) VALUES (valeur, valeur, valeur ...) |
Insertion d'une nouvelle ligne dans le tableau :
- INSERT INTO employees (emp_no, emp_nom, emp_salaire, emp_dateembauche) VALUES (6 , 'Steeve Roy' , 37100 , GETDATE())
- L'ordre des valeurs doit correspondre à l'ordre des colonnes dans la liste des colonnes. Dans le cas ci-dessus, la première valeur doit être associée à la colonne du numéro d'employé ; la deuxième valeur doit être associée à la colonne du nom de l'employé, et ainsi de suite.
- Le type de valeur doit correspondre au type de colonnes dans la liste de colonnes. Par exemple, il n'est pas possible de saisir la valeur 37 dans la colonne emp_dateembauche.
- Le nombre de valeurs : doit correspondre au nombre de valeurs dans la liste des colonnes. Par conséquent, il n'est pas nécessaire d'entrer une valeur dans chaque colonne du tableau.
- INSERT INTO employees (emp_no, emp_nom) VALUES (7 , 'Nathalie Gagné')
- Une valeur NULL est entrée dans une colonne n'ayant pas été spécifiée dans la liste des colonnes, si aucune valeur par défaut n'existe ; sinon, la valeur DEFAULT est entrée.
- Pour saisir explicitement une valeur NULL dans l'un des champs de l'une des colonnes, la valeur NULL doit être spécifiée :
- INSERT INTO employees (emp_no , emp_nom, emp_salaire) VALUES (8 , 'Benoit Gamache' , NULL)
- Pour saisir explicitement une valeur DEFAULT dans l'un des champs de l'une des colonnes, la valeur DEFAULT doit être spécifiée :
- INSERT INTO employees (emp_no , emp_nom, emp_salaire) VALUES (9, 'Olivier Lacroix' , DEFAULT)
- GETDATE : vous pouvez utiliser la fonction GETDATE() au lieu de spécifier une date.
Instruction INSERT INTO SELECT
Cette syntaxe permet de copier des éléments de données d'une autre table dans la table cible demandée :
|
INSERT INTO nom_table_cible (nom_colonne, nom_colonne, nom_colonne ...) SELECT ... FROM nom_table_source WHERE ... |
On aura par exemple l'instruction suivante pour l'insertion d'employée pour le département numéro 50 :
- INSERT INTO employees (emp_no , emp_nom , emp_salaire) SELECT emp_no , emp_nom , emp_salaire FROM employees WHERE no_departement = 50
- Vous pouvez utiliser l'instruction INSERT INTO SELECT avec ou sans la liste de colonnes
- Il n'est pas obligatoire d'inclure une instruction WHERE dans la requête ; cependant, il est conseillé d'inclure une telle déclaration si vous souhaitez éviter de copier l'intégralité du tableau.
Instruction UPDATE
L'instruction UPDATE est utilisée pour modifier les lignes existantes :
|
UPDATE table_name SET column=value, column=value ... WHERE condition |
Mise à jour du salaire de l'employé numéro 77 :
- UPDATE employees SET emp_salaire = 50000 WHERE emp_no = 77
Il est possible de mettre à jour plusieurs champs en même temps, par exemple mettre à jour le salaire, le nom et le prénom du salarié numéro 77 :
- UPDATE employees SET emp_salaire = 50000, emp_nom = 'Steeve Tremblay' WHERE emp_no = 77
Une sous-requête peut être imbriquée dans l'instruction UPDATE de SQL Server. Par exemple, mettre à jour le salaire des employés du département 71 afin qu'il corresponde au salaire moyen des employés du département 70 :
Il est possible de mettre à jour vers une valeur NULL ou DEFAULT :
L'exécution de l'instruction UPDATE de SQL Server sans utiliser la clause WHERE de SQL Server entraîne la mise à jour de tous les champs d'une colonne.
Instruction DELETE
L'instruction DELETE de SQL Server est utilisée pour supprimer des lignes existantes d'une table :
| DELETE FROM nom_table WHERE condition |
L'exemple suivant permet de supprimer ligne contenant les données de l'employé numéro 107 :
- DELETE FROM employees WHERE emp_no = 107
Une sous-requête peut être imbriquée dans l'instruction DELETE :
- DELETE FROM employees WHERE no_departement = (SELECT no_departement FROM departement WHERE nom_departement = 'Ventes')
- L'exécution de l'instruction DELETE du SQL Server sans utiliser la clause WHERE de SQL Server entraîne la suppression de toutes les lignes de la table.
- Il n'est pas possible de supprimer un seul champ à l'aide de l'instruction DELETE de SQL Server. L'instruction DELETE de SQL Server supprime des lignes.
- Vous pouvez écrire une instruction DELETE de SQL Server avec ou sans le mot clef FROM de SQL Server.
- DELETE FROM employees
- DELETE employees
ou
Instruction MERGE
L'instruction MERGE de SQL Server est utilisée pour synchroniser les données de deux tables, en fonction des différences trouvées entre elles, si la même ligne existe dans les deux tables (ligne avec le même identifiant client par exemple), mais que chaque ligne a toujours des valeurs différentes (chaque table contient un numéro de téléphone différent de ce client), l'opération UPDATE sera exécutée. Si la ligne n'existe que dans une table, l'opération INSERT sera exécutée.
Les illustrations suivantes illustrent le concept de fusion. A la fin de chaque mois, les données de la table Clients sont fusionnées avec les données de la table Sauvegardes. Au début du mois, la seule activité MERGE transférait (INSERT) toutes les lignes, telles quelles, vers la table de sauvegarde (à condition que ce soit la première fois que la table de sauvegarde soit remplie).
|
→ |
|
En fin de mois, du fait des modifications ayant été effectuées dans la table Clients, les données sont fusionnées avec la table Sauvegardes comme suit :
- Pour les clients existant déjà dans la table de sauvegarde, une opération de UPDATE est effectuée, par exemple, Antoine Compagnies passera de 2,5 Mo à 5,0 Mo.
- Pour les nouveaux clients, une opération INSERT est effectuée, ajoutant ainsi un nouveau client, par exemple, Annie Roy, à la table de Sauvegardes.
|
→ |
|
Voici la syntaxe :
|
MERGE INTO table_destination alias USING table_source alias ON condition WHEN MATCHED THEN UPDATE SET alias_table_destination.colonne = alias_table_source.colonne, alias_table_destination.colonne = alias_table_source.colonne ... WHEN NOT MATCHED THEN INSERT VALUES (alias_table_source.colonne, alias_table_source.colonne .. ) |
on aura par exemple :
- MERGE INTO sauvegarde_client Sauvegardes
- USING Clients clts
- ON (Sauvegardes.no_client = clts.no_client)
- WHEN MATCHED THEN
- UPDATE SET
- Sauvegardes.nom_client = clts.nom_client ,
- Sauvegardes.client_paquet = clts.client_paquet
- WHEN NOT MATCHED THEN
- INSERT VALUES(clts.no_client , clts.nom_client , clts.client_paquet)
Transactions de base de données
Les transactions sont une seule unité de diverses commandes de modification (telles que UPDATE, INSERT, DELETE), qui dans la plupart des cas sont associées à un seul groupe logique. Le terme «un seul groupe logique» fait référence à un ensemble d'opérations avec connexion logique ; par exemple : un lot d'opérations DML destinées à mettre à jour des éléments de données spécifiques dans la table clients.
- La commande COMMIT est utilisée pour enregistrer toutes les modifications apportées par la transaction dans la base de données. La commande COMMIT enregistre toutes les modifications depuis la dernière commande COMMIT ou ROLLBACK.
- La commande ROLLBACK utilisée pour annuler les modifications apportées par une transaction. La commande ROLLBACK ne peut annuler les modifications que depuis la dernière commande COMMIT ou ROLLBACK émise.
- Mode de validation automatique de SQL Server : Dans SQL Server, par défaut, chaque modification (telle que UPDATE, DELETE, INSERT) est validée automatiquement une fois terminée. Dans SQL Server, vous pouvez démarrer une transaction explicite (que vous décidez quand et comment fermer) à l'aide de la commande BEGIN TRANSACTION.