Poznaj tajniki działania instrukcji TRUNCATE i DELETE w języku SQL, odkryj różnice między nimi oraz zrozum, kiedy zastosowanie jednej z nich będzie bardziej korzystne.
Podczas zarządzania bazami danych często pojawia się potrzeba usunięcia części lub całości danych z tabel. W SQL możemy to osiągnąć za pomocą instrukcji TRUNCATE lub DELETE, w zależności od konkretnej sytuacji.
W tym artykule szczegółowo omówimy obie instrukcje, przeanalizujemy ich działanie i określimy, w jakich przypadkach preferowane jest użycie TRUNCATE zamiast DELETE i na odwrót.
Zanim przejdziemy do sedna, warto przypomnieć sobie o podziałach SQL:
- Instrukcje języka definicji danych (DDL) służą do kreowania i zarządzania strukturą bazy danych, w tym tworzenia tabel. Przykłady instrukcji DDL to CREATE, DROP i TRUNCATE.
- Instrukcje języka manipulacji danymi (DML) pozwalają na modyfikowanie danych w tabelach. Używamy ich do operacji takich jak dodawanie, odczytywanie, aktualizowanie i usuwanie rekordów.
- Instrukcje języka zapytań (DQL) służą do pobierania danych z bazy. Wszystkie polecenia SELECT należą do tej kategorii.
Jak stosować instrukcję SQL TRUNCATE
Składnia polecenia SQL TRUNCATE
Instrukcja SQL TRUNCATE ma następującą formę:
TRUNCATE TABLE nazwa_tabeli;
Wykonanie tego polecenia powoduje usunięcie wszystkich wierszy z tabeli wskazanej przez *nazwa_tabeli*, sama tabela pozostaje nienaruszona.
Operacja obcięcia tabeli nie wymaga przeszukiwania wszystkich rekordów, co sprawia, że jest znacznie szybsza przy dużych zbiorach danych.
Przykłady użycia SQL TRUNCATE
📑 Uwaga: Jeśli masz MySQL zainstalowany na swoim komputerze, możesz eksperymentować, korzystając z klienta wiersza poleceń. Możesz też użyć innego systemu zarządzania bazami danych, jak PostgreSQL.
Zacznijmy od utworzenia bazy danych, na której będziemy pracować:
mysql> CREATE DATABASE db1; Query OK, 1 row affected (1.50 sec)
Następnie wybierzmy utworzoną bazę danych:
mysql> use db1; Database changed
Kolejnym krokiem jest utworzenie tabeli. Uruchom poniższy kod CREATE TABLE, aby stworzyć prostą tabelę zadań:
-- Utworzenie tabeli zadań
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)
);
W tej tabeli mamy następujące kolumny:
- task_id: Automatycznie generowany unikalny identyfikator zadania.
- title: Nazwa zadania, ograniczona do 255 znaków.
- due_date: Termin realizacji zadania (data).
- status: Status zadania (oczekujące, w toku, ukończone). Domyślnie ustawiony na „Oczekujące”.
- assignee: Osoba odpowiedzialna za zadanie.
Teraz, gdy mamy tabelę, dodajmy do niej kilka rekordów:
-- Wstawienie wielu rekordów do tabeli zadań
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
('Zadanie 1', '2023-08-10', 'Pending', 'Jan'),
('Zadanie 2', '2023-08-11', 'In Progress', 'Anna'),
('Zadanie 3', '2023-08-12', 'Completed', 'Piotr'),
('Zadanie 4', '2023-08-13', 'Pending', 'Alicja'),
('Zadanie 5', '2023-08-14', 'In Progress', 'Robert'),
('Zadanie 6', '2023-08-15', 'Completed', 'Emilia'),
('Zadanie 7', '2023-08-16', 'Pending', 'Dawid'),
('Zadanie 8', '2023-08-17', 'In Progress', 'Oliwia'),
('Zadanie 9', '2023-08-18', 'Pending', 'Daniel'),
('Zadanie 10', '2023-08-19', 'Completed', 'Zofia'),
('Zadanie 11', '2023-08-20', 'Pending', 'Mateusz'),
('Zadanie 12', '2023-08-21', 'In Progress', 'Ewa'),
('Zadanie 13', '2023-08-22', 'Completed', 'Wiktor'),
('Zadanie 14', '2023-08-23', 'Pending', 'Eliza'),
('Zadanie 15', '2023-08-24', 'In Progress', 'Jakub'),
('Zadanie 16', '2023-08-25', 'Completed', 'Lidia'),
('Zadanie 17', '2023-08-26', 'Pending', 'Beniamin'),
('Zadanie 18', '2023-08-27', 'In Progress', 'Maja'),
('Zadanie 19', '2023-08-28', 'Pending', 'Henryk'),
('Zadanie 20', '2023-08-29', 'Completed', 'Izabela');
Po wykonaniu tej instrukcji powinieneś zobaczyć podobny komunikat:
Query OK, 20 rows affected (0.18 sec) Records: 20 Duplicates: 0 Warnings: 0
Teraz, za pomocą TRUNCATE, usuńmy wszystkie rekordy z tabeli zadań:
TRUNCATE TABLE tasks; Query OK, 0 rows affected (0.72 sec)
Spowoduje to usunięcie wszystkich danych, ale nie samej tabeli. Możemy to zweryfikować poleceniem SHOW TABLES:
SHOW TABLES;
+---------------+ | Tables_in_db1 | +---------------+ | tasks | +---------------+ 1 row in set (0.00 sec)
A zapytanie SELECT, które próbuje pobrać dane z tabeli zadań, zwróci pusty wynik:
SELECT * FROM tasks; Empty set (0.00 sec)
Jak używać instrukcji SQL DELETE
Składnia instrukcji SQL DELETE
Ogólna postać instrukcji SQL DELETE wygląda następująco:
DELETE FROM nazwa_tabeli WHERE warunek;
Warunek w klauzuli WHERE to predykat, który określa, które wiersze mają zostać usunięte. DELETE usuwa te rekordy, dla których ten warunek jest prawdziwy.
Dzięki temu DELETE daje nam większą kontrolę nad tym, jakie dane zostaną usunięte.
A co się stanie, gdy użyjemy DELETE bez klauzuli WHERE?🤔
DELETE FROM nazwa_tabeli;
W takim przypadku, instrukcja DELETE usunie wszystkie wiersze z danej tabeli.
Jeśli operacja DELETE (lub kilka operacji) jest częścią niezakończonej transakcji, możemy cofnąć zmiany. Zawsze jednak zaleca się wykonanie kopii zapasowej danych.
Przykłady użycia SQL DELETE
Zobaczmy teraz DELETE w akcji.
Wcześniej wyczyściliśmy tabelę zadań. Możemy więc ponownie wykonać INSERT, aby dodać rekordy:
-- Wstawienie rekordów do tabeli zadań
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
('Zadanie 1', '2023-08-10', 'Pending', 'Jan'),
('Zadanie 2', '2023-08-11', 'In Progress', 'Anna'),
('Zadanie 3', '2023-08-12', 'Completed', 'Piotr'),
...
('Zadanie 18', '2023-08-27', 'In Progress', 'Maja'),
('Zadanie 19', '2023-08-28', 'Pending', 'Henryk'),
('Zadanie 20', '2023-08-29', 'Completed', 'Izabela');
Na początek użyjmy DELETE z klauzulą WHERE. Poniższe zapytanie usunie wszystkie wiersze, których status to „Completed”:
DELETE FROM tasks WHERE status="Completed"; Query OK, 6 rows affected (0.14 sec)
Teraz sprawdźmy, co pozostało, za pomocą SELECT:
SELECT * FROM tasks;
Powinno nam zostać 14 rekordów:
+---------+-----------+------------+-------------+----------+ | task_id | title | due_date | status | assignee | +---------+-----------+------------+-------------+----------+ | 1 | Zadanie 1 | 2023-08-10 | Pending | Jan | | 2 | Zadanie 2 | 2023-08-11 | In Progress | Anna | | 4 | Zadanie 4 | 2023-08-13 | Pending | Alicja | | 5 | Zadanie 5 | 2023-08-14 | In Progress | Robert | | 7 | Zadanie 7 | 2023-08-16 | Pending | Dawid | | 8 | Zadanie 8 | 2023-08-17 | In Progress | Oliwia | | 9 | Zadanie 9 | 2023-08-18 | Pending | Daniel | | 11 | Zadanie 11| 2023-08-20 | Pending | Mateusz | | 12 | Zadanie 12| 2023-08-21 | In Progress | Ewa | | 14 | Zadanie 14| 2023-08-23 | Pending | Eliza | | 15 | Zadanie 15| 2023-08-24 | In Progress | Jakub | | 17 | Zadanie 17| 2023-08-26 | Pending | Beniamin | | 18 | Zadanie 18| 2023-08-27 | In Progress | Maja | | 19 | Zadanie 19| 2023-08-28 | Pending | Henryk | +---------+-----------+------------+-------------+----------+ 14 rows in set (0.00 sec)
Uruchomienie teraz poniższego zapytania usunie pozostałe 14 rekordów z tabeli:
DELETE FROM tasks; Query OK, 14 rows affected (0.20 sec)
Tabela zadań jest pusta:
SELECT * FROM tasks; Empty set (0.00 sec)
Instrukcja SQL DROP
Dotychczas dowiedzieliśmy się, że:
- TRUNCATE usuwa wszystkie wiersze z tabeli.
- DELETE bez klauzuli WHERE również usuwa wszystkie rekordy.
Żadne z tych poleceń nie usuwa samej tabeli. Aby usunąć tabelę z bazy danych, możemy użyć DROP TABLE:
DROP TABLE nazwa_tabeli;
Usuńmy teraz tabelę zadań z bazy danych:
mysql> DROP TABLE tasks; Query OK, 0 rows affected (0.43 sec)
Polecenie SHOW TABLES zwróci pusty zestaw (bo usunęliśmy jedyną tabelę w bazie):
mysql> SHOW TABLES; Empty set (0.00 sec)
Kiedy używać TRUNCATE a kiedy DELETE w SQL
| Cecha | TRUNCATE | DELETE |
|—|—|—|
| Składnia | `TRUNCATE TABLE nazwa_tabeli;` | Z `WHERE`: `DELETE FROM nazwa_tabeli WHERE warunek;`
Bez `WHERE`: `DELETE TABLE nazwa_tabeli;` |
| Podzbiór SQL | Język definicji danych (DDL) | Język manipulacji danymi (DML) |
| Efekt | Usuwa wszystkie wiersze z tabeli. | Usuwa wszystkie rekordy (bez WHERE) lub wybrane wiersze. |
| Wydajność | Bardziej wydajne dla dużych tabel | Mniej wydajne niż TRUNCATE |
Podsumowując:
- Użyj TRUNCATE, gdy chcesz szybko usunąć wszystkie dane z dużej tabeli.
- Użyj DELETE, gdy potrzebujesz usunąć konkretne wiersze na podstawie określonego warunku.
Podsumowanie
Podsumujmy nasze rozważania:
- Podczas pracy z bazami danych często pojawia się potrzeba usunięcia części lub całości danych z tabeli. Możemy to zrobić za pomocą TRUNCATE lub DELETE.
- TRUNCATE ma składnię: `TRUNCATE TABLE nazwa_tabeli;`. Usuwa wszystkie wiersze z danej tabeli, ale nie usuwa samej tabeli.
- DELETE ma składnię: `DELETE FROM nazwa_tabeli WHERE warunek;`. Usuwa wiersze spełniające warunek.
- DELETE bez klauzuli WHERE usuwa wszystkie rekordy z tabeli, podobnie jak TRUNCATE.
- TRUNCATE jest szybsze w przypadku dużych tabel, ponieważ nie skanuje całej tabeli. Jest więc bardziej efektywne, gdy potrzebujemy usunąć wszystkie dane z dużej tabeli.
- Gdy potrzebujemy usunąć tylko wybrane rekordy na podstawie określonego warunku, powinniśmy skorzystać z DELETE.
Aby mieć szybki dostęp do często używanych komend SQL, sprawdź ten ściągawkę SQL.