TRUNCATE TABLE vs. DELETE vs. DROP TABLE: Entfernen von Tabellen und Daten in SQL

Was ist der Unterschied zwischen "DELETE", "DROP" und "TRUNCATE" in SQL? Finden Sie es in diesem Artikel heraus.

Es gibt viele Möglichkeiten, Daten in SQL zu löschen. Darunter die Befehle DELETE, TRUNCATE TABLE und DROP TABLE. Welchen Befehl sollten Sie in einer bestimmten Situation verwenden?

In diesem Artikel lernen Sie die Syntax der einzelnen Befehle in verschiedenen Datenbank-Engines wie MySQL, PostgreSQL, SQL Server und Oracle kennen. Und Sie werden die Details über DROP TABLE vs. DELETE vs. TRUNCATE TABLE verstehen.

Wenn Sie Ihre SQL-Kenntnisse verbessern möchten, empfehle ich Ihnen unsere SQL-Lernplattform LearnSQL.de. LearnSQL.de bietet über 50 interaktive SQL-Kurse auf verschiedenen Niveaus an. Probieren Sie zum Beispiel unseren SQL-Praxis-Kurs mit über 600 praktischen SQL-Übungen zum Üben Ihrer SQL-Kenntnisse.

Also, fangen wir an!

DELETE

DELETE ist ein DML-Befehl (Data Manipulation Language). Mit diesem Befehl werden Datensätze aus einer Tabelle entfernt. Er wird nur zum Löschen von Daten aus einer Tabelle verwendet, nicht zum Entfernen der Tabelle aus der Datenbank.

Sie können mit dieser Syntax alle Datensätze löschen:

DELETE FROM name_table;

Oder Sie können eine Gruppe von Datensätzen mit der WHERE-Klausel löschen:

DELETE FROM name_table WHERE col=value;

Wenn Sie alle Datensätze aus einer bestimmten Tabelle löschen möchten, verwenden Sie DELETE FROM gefolgt vom Tabellennamen. Beachten Sie, dass in dieser Syntax keine Spaltennamen vorkommen. Sie entfernen alle Datensätze, nicht die Daten in ausgewählten Spalten.

Wenn Sie bestimmte Datensätze entfernen möchten, verwenden Sie WHERE mit Filterbedingungen, wie im zweiten Beispiel.

Lassen Sie uns diese Befehle in einem Beispiel verwenden. Hier ist die Tabelle product:

Diese Abfrage ...

DELETE FROM product;

... entfernt alle Daten aus der Tabelle product. Nach dieser Abfrage ist die Tabelle product leer.

Aber die Abfrage WHERE...

DELETE FROM product WHERE price<2.90;

... löscht nur die Datensätze für Milch und Zucker, da deren Preise niedriger als 2,90 $ sind. (Milch kostet $2,40 und Zucker $2,88.)

Jetzt enthält die Tabelle product nur noch Datensätze mit Preisen über $2.90:

Wie funktioniert DELETE?

Wenn Sie die Tabellenstruktur nicht entfernen wollen oder nur bestimmte Zeilen löschen wollen, verwenden Sie den Befehl DELETE. Er kann eine, einige oder alle Zeilen in einer Tabelle entfernen. DELETE gibt die Anzahl der aus der Tabelle entfernten Zeilen zurück.

DELETE verwendet jedoch während der Ausführung eine Zeilensperre und kann rückgängig gemacht werden. Jede gelöschte Zeile ist gesperrt, so dass viele Sperren erforderlich sind, wenn Sie mit einer großen Tabelle arbeiten.

DELETE behält auch die automatisch inkrementierende ID in der Tabelle. Wenn Sie den letzten Datensatz in der Tabelle mit der ID=20 löschen und dann einen neuen Datensatz hinzufügen, hat dieser Datensatz die ID=21 - auch wenn der Datensatz unmittelbar davor die ID=19 hat.

DELETE können von Triggern ausgeführt werden. Ein Trigger kann vor, nach oder anstelle der DELETE Operation aufgerufen werden. Er kann bei jeder Zeilenänderung ausgeführt werden oder wenn alle Zeilen entfernt werden. Das Entfernen von Zeilen in einer anderen Tabelle kann auch DELETE auslösen.

Um den Befehl DELETE zu verwenden, benötigen Sie natürlich die Berechtigung DELETE für diese Tabelle.

TRUNCATE TABLE

TRUNCATE TABLE ist ähnlich wie DELETE, aber diese Operation ist ein DDL-Befehl (Data Definition Language). Sie löscht ebenfalls Datensätze aus einer Tabelle, ohne die Tabellenstruktur zu entfernen, aber sie verwendet nicht die WHERE Klausel. Hier ist die Syntax:

TRUNCATE TABLE table_name;

Wenn Sie diesen Befehl verwenden, werden alle Zeilen in dieser Tabelle entfernt. Die folgende Abfrage ...

TRUNCATE TABLE product;

... löscht alle in der Tabelle gespeicherten Datensätze product.

Wie funktioniert TRUNCATE TABLE?

Seien Sie vorsichtig bei der Verwendung dieses Befehls. TRUNCATE-Transaktionen können in Datenbank-Engines wie SQL Server und PostgreSQL rückgängig gemacht werden, nicht aber in MySQL und Oracle.

TRUNCATE ist schneller als DELETE, da nicht jeder Datensatz vor dem Entfernen gescannt wird. TRUNCATE TABLE sperrt die gesamte Tabelle, um Daten aus dieser zu entfernen. Daher verbraucht dieser Befehl auch weniger Transaktionsspeicher als DELETE.

Im Gegensatz zu DELETE gibt TRUNCATE nicht die Anzahl der aus der Tabelle gelöschten Zeilen zurück. Außerdem wird der Wert für das automatische Erhöhen der Tabelle auf den Anfangswert (normalerweise 1) zurückgesetzt. Wenn Sie einen Datensatz hinzufügen, nachdem Sie die Tabelle abgeschnitten haben, hat dieser die ID=1. Hinweis: In PostgreSQL können Sie wählen, ob Sie den Auto-Inkrement-Wert neu starten oder fortsetzen wollen. Oracle verwendet eine Sequenz zum Inkrementieren von Werten, die durch TRUNCATE nicht zurückgesetzt wird.

Natürlich benötigen Sie die Berechtigung, TRUNCATE TABLE zu verwenden. In PostgreSQL benötigen Sie das Privileg TRUNCATE; in SQL Server ist das Mindestrecht ALTER table. In MySQL benötigen Sie das Privileg DROP. Oracle schließlich benötigt das DROP ANY TABLE Systemprivileg, um diesen Befehl zu verwenden.

Mehr dazu erfahren Sie im Kurs "The Basics of Creating Tables in SQL", der Teil unseres Data Engineering Kurs ist.

DROP TABLE

DROP TABLE ist eine weitere DDL-Operation (Data Definition Language). Sie wird jedoch nicht einfach zum Entfernen von Daten aus einer Tabelle verwendet, sondern löscht die Tabellenstruktur aus der Datenbank. Zusammen mit allen in der Tabelle gespeicherten Daten.

Hier ist die Syntax dieses Befehls:

DROP TABLE table_name;

Alles, was Sie nach DROP TABLE brauchen, ist der Name der Tabelle, die Sie löschen wollen. Wenn Sie zum Beispiel die gesamte Tabelle product Tabelle aus der Datenbank entfernen möchten, schreiben Sie:

DROP TABLE product;

Dies entfernt alle Daten in der Tabelle product und die Struktur der Tabelle.

Wie funktioniert DROP TABLE?

Die Operation DROP TABLE entfernt die Tabellendefinition und die Daten sowie die Indizes, Beschränkungen und Trigger, die mit der Tabelle verbunden sind.

Dieser Befehl gibt den Speicherplatz frei.

Bei der Ausführung von DROP TABLE werden keine Trigger ausgelöst.

Dieser Vorgang kann in MySQL nicht rückgängig gemacht werden. In Oracle, SQL Server und PostgreSQL hingegen schon.

In SQL Server erfordert DROP TABLE die Berechtigung ALTER in dem Schema, zu dem die Tabelle gehört. MySQL erfordert die DROP-Berechtigung und Oracle erfordert die DROP ANY TABLE-Berechtigung. In PostgreSQL können Benutzer ihre eigenen Tabellen löschen.

DROP TABLE vs. DELETE TABLE vs. TRUNCATE TABLE in SQL

In welchen Fällen ist DROP TABLE erforderlich? Wann sollten Sie TRUNCATE verwenden oder sich für ein einfaches DELETE entscheiden? In der folgenden Tabelle haben wir die Eigenschaften der einzelnen Befehle zusammengefasst:

Welche Operation ist für welchen Anwendungsfall am besten geeignet?

• Um bestimmte Zeilen zu entfernen, verwenden Sie DELETE.
• Um alle Zeilen aus einer großen Tabelle zu entfernen und die Tabellenstruktur zu erhalten, verwenden Sie TRUNCATE TABLE. Er ist schneller als DELETE.
• Um eine ganze Tabelle zu entfernen, einschließlich ihrer Struktur und Daten, verwenden Sie DROP TABLE.

Sie können mehr über das Erstellen von Datenbankstrukturen in unserem Data Engineering Kurs erfahren.

In SQL haben sowohl DELETE, TRUNCATE TABLE, als auch DELETE TABLE Vor- und Nachteile. Ich hoffe, dieser Artikel hat Ihnen geholfen zu verstehen, wann und wie Sie die einzelnen Befehle verwenden sollten.