Scopri come funzionano le istruzioni TRUNCATE e DELETE in SQL, le differenze tra loro e quando dovresti preferirne l’uso.
Quando si lavora con le tabelle del database, potrebbe essere necessario eliminare un sottoinsieme di righe o tutte le righe. Per eliminare righe da una tabella di database, è possibile utilizzare le istruzioni SQL TRUNCATE o DELETE a seconda del caso d’uso.
In questo tutorial, daremo un’occhiata più da vicino a ciascuna delle istruzioni, capiremo come funzionano e decideremo quando preferire l’uso di TRUNCATE rispetto a DELETE e viceversa.
Prima di andare oltre, è utile esaminare i seguenti sottoinsiemi SQL:
- Le istruzioni DDL (Data Definition Language) vengono utilizzate per creare e gestire oggetti di database come le tabelle. Le istruzioni SQL CREATE, DROP e TRUNCATE sono esempi di istruzioni DDL.
- Le istruzioni DML (Data Manipulation Language) vengono utilizzate per manipolare i dati all’interno di oggetti di database. Le istruzioni DML vengono utilizzate per eseguire le operazioni di creazione, lettura, aggiornamento ed eliminazione dei record.
- Le istruzioni Data Query Language (DQL) vengono utilizzate per recuperare i dati dalle tabelle del database. Tutte le istruzioni SELECT rientrano nel sottoinsieme DQL.
Sommario:
Come utilizzare l’istruzione TRUNCATE SQL
Sintassi dell’istruzione SQL TRUNCATE
La sintassi per utilizzare l’istruzione SQL TRUNCATE è la seguente:
TRUNCATE TABLE table_name;
L’esecuzione del comando TRUNCATE precedente elimina tutte le righe nella tabella specificata da table_name e non elimina la tabella.
L’operazione di troncamento non esegue la scansione di tutti i record nella tabella. Quindi è ragionevolmente più veloce quando si lavora con tabelle di database di grandi dimensioni.
Esempio di utilizzo di SQL TRUNCATE
📑 Nota: se hai MySQL installato sulla tua macchina, puoi programmare insieme usando il client della riga di comando MySQL. Puoi anche seguire in un altro DBMS di tua scelta come PostgreSQL.
Creiamo prima un database con cui lavorare:
mysql> CREATE DATABASE db1; Query OK, 1 row affected (1.50 sec)
Quindi seleziona il database che abbiamo appena creato:
mysql> use db1; Database changed
Il passaggio successivo consiste nel creare una tabella di database. Eseguire la seguente istruzione CREATE TABLE per creare una semplice tabella delle attività:
-- Create the tasks table
CREATE TABLE tasks (
task_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
due_date DATE,
status ENUM('Pending', 'In Progress', 'Completed') DEFAULT 'Pending',
assignee VARCHAR(100)
);
In questo esempio, la tabella delle attività ha le seguenti colonne:
- task_id: un identificatore univoco che incrementa automaticamente per ogni attività.
- titolo: il titolo o il nome dell’attività, limitato a 255 caratteri.
- due_date: la data di scadenza dell’attività, rappresentata come una data.
- stato: lo stato dell’attività, che può essere “In sospeso”, “In corso” o “Completato”. Il valore predefinito è impostato su ‘In attesa’.
- assegnatario: l’assegnatario per l’attività specifica.
Ora che abbiamo creato la tabella delle attività, inseriamo i record in essa:
-- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
('Task 1', '2023-08-10', 'Pending', 'John'),
('Task 2', '2023-08-11', 'In Progress', 'Jane'),
('Task 3', '2023-08-12', 'Completed', 'Mike'),
('Task 4', '2023-08-13', 'Pending', 'Alice'),
('Task 5', '2023-08-14', 'In Progress', 'Bob'),
('Task 6', '2023-08-15', 'Completed', 'Emily'),
('Task 7', '2023-08-16', 'Pending', 'David'),
('Task 8', '2023-08-17', 'In Progress', 'Olivia'),
('Task 9', '2023-08-18', 'Pending', 'Daniel'),
('Task 10', '2023-08-19', 'Completed', 'Sophia'),
('Task 11', '2023-08-20', 'Pending', 'Matthew'),
('Task 12', '2023-08-21', 'In Progress', 'Ava'),
('Task 13', '2023-08-22', 'Completed', 'William'),
('Task 14', '2023-08-23', 'Pending', 'Ella'),
('Task 15', '2023-08-24', 'In Progress', 'James'),
('Task 16', '2023-08-25', 'Completed', 'Lily'),
('Task 17', '2023-08-26', 'Pending', 'Benjamin'),
('Task 18', '2023-08-27', 'In Progress', 'Mia'),
('Task 19', '2023-08-28', 'Pending', 'Henry'),
('Task 20', '2023-08-29', 'Completed', 'Isabella');
Dopo aver eseguito l’istruzione insert, dovresti vedere un output simile:
Query OK, 20 rows affected (0.18 sec) Records: 20 Duplicates: 0 Warnings: 0
Ora esegui il comando TRUNCATE table per eliminare tutti i record dalla tabella delle attività:
TRUNCATE TABLE tasks; Query OK, 0 rows affected (0.72 sec)
In questo modo vengono rimossi tutti i record e non la tabella. Puoi verificarlo eseguendo SHOW TABLES; così:
SHOW TABLES;
+---------------+ | Tables_in_db1 | +---------------+ | tasks | +---------------+ 1 row in set (0.00 sec)
E una query SELECT per recuperare i dati dalla tabella delle attività restituisce un set vuoto:
SELECT * FROM tasks; Empty set (0.00 sec)
Come utilizzare l’istruzione SQL DELETE
Sintassi dell’istruzione SQL DELETE
La sintassi generale per utilizzare l’istruzione SQL DELETE è la seguente:
DELETE FROM table_name WHERE condition;
La condizione nella clausola WHERE è il predicato che determina quale delle righe deve essere cancellata. L’istruzione DELETE elimina tutte le righe per le quali il predicato è True.
L’istruzione DELETE, pertanto, consente di avere un maggiore controllo su quali record vengono eliminati.
Ma cosa succede quando usi l’istruzione DELETE senza la clausola WHERE?🤔
DELETE FROM table_name;
L’esecuzione dell’istruzione DELETE come mostrato elimina tutte le righe nella tabella del database.
Se un’istruzione DELETE o un insieme di istruzioni DELETE fanno parte di una transazione di cui non è stato eseguito il commit, è possibile eseguire il rollback delle modifiche. Tuttavia, si consiglia di eseguire il backup dei dati altrove.
Esempio di utilizzo di SQL DELETE
Ora vediamo l’istruzione SQL delete in azione.
Abbiamo eliminato tutti i record dalla tabella delle attività. Quindi puoi rieseguire l’istruzione INSERT (che abbiamo eseguito in precedenza) per inserire i record:
-- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
('Task 1', '2023-08-10', 'Pending', 'John'),
('Task 2', '2023-08-11', 'In Progress', 'Jane'),
('Task 3', '2023-08-12', 'Completed', 'Mike'),
...
('Task 18', '2023-08-27', 'In Progress', 'Mia'),
('Task 19', '2023-08-28', 'Pending', 'Henry'),
('Task 20', '2023-08-29', 'Completed', 'Isabella');
Per prima cosa usiamo l’istruzione DELETE con la clausola WHERE. La seguente query elimina tutte le righe per le quali lo stato è ‘Completato’:
DELETE FROM tasks WHERE status="Completed"; Query OK, 6 rows affected (0.14 sec)
Ora esegui questa query SELECT:
SELECT * FROM tasks;
Vedrai che attualmente ci sono 14 righe:
+---------+---------+------------+-------------+----------+ | task_id | title | due_date | status | assignee | +---------+---------+------------+-------------+----------+ | 1 | Task 1 | 2023-08-10 | Pending | John | | 2 | Task 2 | 2023-08-11 | In Progress | Jane | | 4 | Task 4 | 2023-08-13 | Pending | Alice | | 5 | Task 5 | 2023-08-14 | In Progress | Bob | | 7 | Task 7 | 2023-08-16 | Pending | David | | 8 | Task 8 | 2023-08-17 | In Progress | Olivia | | 9 | Task 9 | 2023-08-18 | Pending | Daniel | | 11 | Task 11 | 2023-08-20 | Pending | Matthew | | 12 | Task 12 | 2023-08-21 | In Progress | Ava | | 14 | Task 14 | 2023-08-23 | Pending | Ella | | 15 | Task 15 | 2023-08-24 | In Progress | James | | 17 | Task 17 | 2023-08-26 | Pending | Benjamin | | 18 | Task 18 | 2023-08-27 | In Progress | Mia | | 19 | Task 19 | 2023-08-28 | Pending | Henry | +---------+---------+------------+-------------+----------+ 14 rows in set (0.00 sec)
L’esecuzione della seguente istruzione DELETE elimina tutti i restanti 14 record nella tabella:
DELETE FROM tasks; Query OK, 14 rows affected (0.20 sec)
E la tabella delle attività ora è vuota:
SELECT * FROM tasks; Empty set (0.00 sec)
L’istruzione DROP SQL
Finora abbiamo imparato:
- L’istruzione TRUNCATE elimina tutte le righe dalla tabella.
- L’istruzione DELETE, senza una clausola WHERE, elimina tutti i record dalla tabella.
Tuttavia, le istruzioni TRUNCATE e DELETE non eliminano la tabella. Se vuoi eliminare la tabella dal database, puoi usare il comando DROP TABLE in questo modo:
DROP TABLE table_name;
Ora cancelliamo la tabella delle attività dal database:
mysql> DROP TABLE tasks; Query OK, 0 rows affected (0.43 sec)
Vedrai che SHOW TABLES; restituisce un set vuoto (poiché abbiamo eliminato l’unica tabella che era presente nel database):
mysql> SHOW TABLES; Empty set (0.00 sec)
Quando utilizzare TRUNCATE rispetto a DELETE in SQL
CaratteristicaTRUNCATEDELETESyntaxTRUNCATE TABLE nome_tabella;con clausola WHERE: DELETE FROM nome_tabella WHERE condizione;
Senza clausola WHERE: DELETE TABLE table_name;SQL SubsetData Definition Language (DDL)Data Manipulation Language (DML)EffettoElimina tutte le righe nella tabella del database.Quando viene eseguita senza la clausola WHERE, l’istruzione DELETE elimina tutti i record nella tabella del database.PrestazioniAltro efficiente dell’istruzione DELETE quando si lavora con tabelle di grandi dimensioni. Meno efficiente dell’istruzione TRUNCATE.
Per riassumere:
- Quando è necessario eliminare tutte le righe da una tabella di database di grandi dimensioni, utilizzare l’istruzione TRUNCATE.
- Per eliminare un sottoinsieme delle righe in base a condizioni specifiche, utilizzare l’istruzione DELETE.
Riassumendo
Concludiamo la nostra discussione con un riassunto:
- Quando si lavora con le tabelle del database, potrebbe essere necessario rimuovere il sottoinsieme di righe o tutte le righe in una particolare tabella. Per fare ciò puoi usare le istruzioni TRUNCATE o DELETE.
- L’istruzione TRUNCATE accetta la sintassi: TRUNCATE TABLE nome_tabella;. Elimina tutte le righe nella tabella specificata da table_name ma non elimina la tabella stessa.
- L’istruzione DELETE accetta la sintassi: DELETE FROM nome_tabella WHERE condizione;. Ciò rimuove le righe per le quali la condizione del predicato è vera.
- L’esecuzione dell’istruzione SQL DELETE senza la clausola WHERE elimina tutte le righe nella tabella. Quindi funzionalmente, questo ottiene lo stesso risultato dell’istruzione SQL TRUNCATE.
- L’esecuzione di TRUNCATE è particolarmente più veloce quando si lavora con tabelle più grandi, poiché non esegue la scansione dell’intera tabella. Pertanto, quando è necessario eliminare tutte le righe in una tabella di database di grandi dimensioni, l’esecuzione di truncate può essere più efficiente.
- Quando è necessario eliminare un sottoinsieme di righe, in base a una condizione specifica, è possibile utilizzare l’istruzione SQL DELETE.
Per una rapida rassegna dei comandi SQL comunemente usati, dai un’occhiata a questo cheat sheet SQL.