TRUNCATE TABLE vs. DELETE vs. DROP TABLE : Suppression de tables et de données en SQL

Quelle est la différence entre tronquer, supprimer et abandonner une table en SQL ? Découvrez-le dans cet article.

Il existe de nombreuses façons de supprimer des données en SQL, notamment avec les commandes DELETE, TRUNCATE TABLE et DROP TABLE. Laquelle devez-vous utiliser dans une situation donnée ?

Dans cet article, vous apprendrez la syntaxe de chaque commande dans différents moteurs de base de données comme MySQL, PostgreSQL, SQL Server et Oracle. Et vous comprendrez le débat DROP TABLE vs. DELETE vs. TRUNCATE TABLE.

Si vous souhaitez mettre en pratique vos compétences SQL, je vous recommande notre plateforme d'apprentissage SQL LearnSQL.fr. LearnSQL.fr propose plus de 50 cours SQL interactifs à différents niveaux de compétence. Par exemple, essayez notre piste La pratique du SQL avec plus de 600 exercices SQL pratiques pour mettre en pratique vos compétences SQL.

Maintenant, c'est parti !

DELETE

DELETE est une commande DML (Data Manipulation Language). Cette commande supprime les enregistrements d'une table. Elle est utilisée uniquement pour supprimer les données d'une table, et non pour supprimer la table de la base de données.

Vous pouvez supprimer tous les enregistrements avec la syntaxe :

DELETE FROM name_table;

Ou vous pouvez supprimer un groupe d'enregistrements à l'aide de la clause WHERE :

DELETE FROM name_table WHERE col=value;

Si vous souhaitez supprimer tous les enregistrements d'une table donnée, utilisez DELETE FROM suivi du nom de la table. Remarquez qu'il n'y a pas de nom de colonne dans cette syntaxe ; vous supprimez tous les enregistrements, pas les données des colonnes sélectionnées.

Si vous souhaitez supprimer des enregistrements spécifiques, utilisez WHERE avec des conditions de filtrage, comme dans le deuxième exemple.

Utilisons ces commandes dans un exemple. Voici la table product:

Cette requête ...

DELETE FROM product;

... supprime toutes les données de la table product. Après cette requête, la table product sera vide.

Mais la requête WHERE...

DELETE FROM product WHERE price<2.90;

... ne supprime que les enregistrements relatifs au lait et au sucre, car leurs prix sont inférieurs à 2,90 $. (Le lait est à 2,40 $ et le sucre à 2,88 $).

Maintenant, la table product ne contient que des enregistrements dont le prix est supérieur à 2,90 $ :

Comment fonctionne le DELETE ?

Si vous ne voulez pas supprimer la structure de la table ou si vous ne supprimez que des lignes spécifiques, utilisez la commande DELETE. Elle peut supprimer une, plusieurs ou toutes les lignes d'une table. DELETE renvoie le nombre de lignes supprimées de la table.

Cependant, le DELETE utilise un verrou de ligne pendant l'exécution et peut être annulé. Chaque ligne supprimée est verrouillée, ce qui nécessite un grand nombre de verrous si vous travaillez sur une grande table.

Le DELETE conserve également l'identifiant auto-incrémenté dans la table. Si vous supprimez le dernier enregistrement de la table dont l'identifiant est 20 et que vous ajoutez un nouvel enregistrement, cet enregistrement aura l'identifiant 21, même si l'enregistrement qui le précède immédiatement à l'identifiant 19.

Un DELETE peut être exécuté par des déclencheurs. Un déclencheur peut être appelé avant, après ou à la place de l'opération DELETE. Il peut être exécuté pour tout changement de ligne ou lorsque toutes les lignes sont supprimées. La suppression de lignes dans une autre table peut également déclencher le DELETE.

Bien entendu, pour utiliser la commande DELETE, vous devez disposer de l'autorisation DELETE pour cette table.

TRUNCATE TABLE

TRUNCATE TABLE est similaire à DELETE, mais cette opération est une commande DDL (Data Definition Language). Elle supprime également les enregistrements d'une table sans en supprimer la structure, mais elle n'utilise pas la clause WHERE. Voici la syntaxe :

TRUNCATE TABLE table_name;

Si vous utilisez cette commande, toutes les lignes de cette table seront supprimées. La requête suivante ...

TRUNCATE TABLE product;

... supprime tous les enregistrements stockés dans la table product.

Comment fonctionne la commande TRUNCATE TABLE ?

Faites attention en utilisant cette commande. Les transactions TRUNCATE peuvent être annulées dans les moteurs de base de données comme SQL Server et PostgreSQL, mais pas dans MySQL et Oracle.

TRUNCATE est plus rapide que DELETE, car elle n'analyse pas chaque enregistrement avant de le supprimer. TRUNCATE TABLE verrouille la table entière pour supprimer des données d'une table ; cette commande utilise donc moins d'espace de transaction que DELETE.

Contrairement à DELETE, TRUNCATE ne renvoie pas le nombre de lignes supprimées de la table. Elle réinitialise également la valeur d'auto-incrémentation de la table à la valeur de départ (généralement 1). Si vous ajoutez un enregistrement après avoir tronqué la table, il aura un identifiant égal à 1. Remarque : Dans PostgreSQL, vous pouvez choisir de redémarrer ou de poursuivre la valeur d'auto-incrémentation. Oracle utilise une séquence pour incrémenter les valeurs, qui n'est pas remise à zéro avec TRUNCATE.

Bien sûr, vous devez avoir la permission d'utiliser TRUNCATE TABLE. Dans PostgreSQL, vous avez besoin du privilège TRUNCATE ; dans SQL Server, l'autorisation minimale est ALTER table ; dans MySQL, vous avez besoin du privilège DROP. Enfin, Oracle requiert le privilège système DROP ANY TABLE pour utiliser cette commande.

Vous pouvez en savoir plus dans le cours "Les bases de la création de tables en SQL", qui fait partie de notre parcours d'ingénierie des données.

DROP TABLE

La commande DROP TABLE est une autre opération DDL (Data Definition Language). Mais elle n'est pas utilisée pour supprimer simplement les données d'une table ; elle supprime la structure de la table de la base de données, ainsi que toutes les données stockées dans la table.

Voici la syntaxe de cette commande :

DROP TABLE table_name;

Tout ce dont vous avez besoin après DROP TABLE est le nom de la table que vous voulez supprimer. Par exemple, si vous souhaitez supprimer l'intégralité de la table product de la base de données, vous écrirez :

DROP TABLE product;

Ceci supprime toutes les données de la table product et la structure de la table.

Comment fonctionne DROP TABLE ?

L'opération DROP TABLE supprime la définition et les données de la table ainsi que les index, les contraintes et les déclencheurs liés à la table.

Cette commande libère l'espace mémoire.

Aucun déclencheur n'est déclenché lors de l'exécution de DROP TABLE.

Cette opération ne peut pas être annulée dans MySQL, mais elle peut l'être dans Oracle, SQL Server et PostgreSQL.

Dans SQL Server, DROP TABLE requiert l'autorisation de ALTER dans le schéma auquel la table appartient ; MySQL requiert le privilège DROP ; Oracle requiert le privilège DROP ANY TABLE. Dans PostgreSQL, les utilisateurs peuvent supprimer leurs propres tables.

DROP TABLE vs. DELETE TABLE vs. TRUNCATE TABLE en SQL

Dans quels cas faut-il utiliser DROP TABLE ? Quand faut-il utiliser TRUNCATE ou opter pour un simple DELETE ? Nous avons préparé le tableau ci-dessous pour résumer les propriétés de chaque commande :

Quelle opération est la meilleure pour quel cas d'utilisation ?

• Pour supprimer des lignes spécifiques, utilisez DELETE.
• Pour supprimer toutes les lignes d'une grande table et laisser la structure de la table, utilisez TRUNCATE TABLE. C'est plus rapide que DELETE.
• Pour supprimer une table entière, y compris sa structure et ses données, utilisez DROP TABLE.

Vous pouvez en savoir plus sur la création de structures de bases de données dans notre parcours d'ingénierie des données.

En SQL, DELETE, TRUNCATE TABLE et DELETE TABLE ont tous des avantages et des inconvénients. Nous espérons que cet article vous a aidé à comprendre quand et comment utiliser chaque commande.