Dowiedz się, jak działają instrukcje TRUNCATE i DELETE w języku SQL, jakie są między nimi różnice oraz kiedy lepiej używać jednej z nich.
Podczas pracy z tabelami bazy danych może być konieczne usunięcie podzbioru wierszy lub wszystkich wierszy. Aby usunąć wiersze z tabeli bazy danych, możesz użyć instrukcji SQL TRUNCATE lub DELETE, w zależności od przypadku użycia.
W tym samouczku przyjrzymy się bliżej każdej z instrukcji, zrozumiemy, jak działają, i zdecydujemy, kiedy preferować użycie TRUNCATE zamiast DELETE i odwrotnie.
Zanim przejdziemy dalej, warto przejrzeć następujące podzbiory SQL:
- Instrukcje języka definicji danych (DDL) służą do tworzenia obiektów bazy danych, takich jak tabele, i zarządzania nimi. Instrukcje SQL CREATE, DROP i TRUNCATE są przykładami instrukcji DDL.
- Instrukcje Data Manipulation Language (DML) służą do manipulowania danymi w obiektach bazy danych. Instrukcje DML służą do wykonywania operacji tworzenia, odczytywania, aktualizowania i usuwania rekordów.
- Instrukcje Data Query Language (DQL) służą do pobierania danych z tabel bazy danych. Wszystkie instrukcje SELECT należą do podzbioru DQL.
Spis treści:
Jak korzystać z instrukcji SQL TRUNCATE
Składnia instrukcji SQL TRUNCATE
Składnia instrukcji SQL TRUNCATE jest następująca:
TRUNCATE TABLE table_name;
Uruchomienie powyższego polecenia TRUNCATE usuwa wszystkie wiersze w tabeli określonej przez nazwa_tabeli — i nie usuwa tabeli.
Operacja obcięcia nie skanuje wszystkich rekordów w tabeli. Jest więc stosunkowo szybszy podczas pracy z dużymi tabelami bazy danych.
Przykład użycia SQL TRUNCATE
📑 Uwaga: Jeśli masz MySQL zainstalowany na twoim komputerze, możesz kodować razem z klientem wiersza poleceń MySQL. Możesz także śledzić w innym wybranym systemie DBMS, takim jak PostgreSQL.
Najpierw utwórzmy bazę danych do pracy z:
mysql> CREATE DATABASE db1; Query OK, 1 row affected (1.50 sec)
Następnie wybierz bazę danych, którą właśnie utworzyliśmy:
mysql> use db1; Database changed
Następnym krokiem jest utworzenie tabeli bazy danych. Uruchom następującą instrukcję CREATE TABLE, aby utworzyć prostą tabelę zadań:
-- 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)
);
W tym przykładzie tabela zadań zawiera następujące kolumny:
- task_id: Automatycznie zwiększający się unikalny identyfikator dla każdego zadania.
- tytuł: Tytuł lub nazwa zadania, ograniczona do 255 znaków.
- termin_terminu: Termin wykonania zadania, reprezentowany jako data.
- status: Status zadania, który może być „Oczekujący”, „W toku” lub „Ukończony”. Wartość domyślna to „Oczekujące”.
- cesjonariusz: cesjonariusz do określonego zadania.
Teraz, gdy mamy już utworzoną tabelę zadań, wstawmy do niej rekordy:
-- 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');
Po uruchomieniu instrukcji wstawiania powinieneś zobaczyć podobny wynik:
Query OK, 20 rows affected (0.18 sec) Records: 20 Duplicates: 0 Warnings: 0
Teraz uruchom komendę TRUNCATE table, aby usunąć wszystkie rekordy z tabeli zadań:
TRUNCATE TABLE tasks; Query OK, 0 rows affected (0.72 sec)
Spowoduje to usunięcie wszystkich rekordów, a nie tabeli. Możesz to sprawdzić, uruchamiając SHOW TABLES; jak tak:
SHOW TABLES;
+---------------+ | Tables_in_db1 | +---------------+ | tasks | +---------------+ 1 row in set (0.00 sec)
A zapytanie SELECT w celu pobrania danych z tabeli zadań zwraca pusty zestaw:
SELECT * FROM tasks; Empty set (0.00 sec)
Jak korzystać z instrukcji SQL DELETE
Składnia instrukcji SQL DELETE
Ogólna składnia instrukcji SQL DELETE jest następująca:
DELETE FROM table_name WHERE condition;
Warunek w klauzuli WHERE jest predykatem, który określa, który z wierszy powinien zostać usunięty. Instrukcja DELETE usuwa wszystkie wiersze, dla których predykat ma wartość True.
Instrukcja DELETE umożliwia zatem większą kontrolę nad tym, które rekordy mają zostać usunięte.
Ale co się stanie, gdy użyjesz instrukcji DELETE bez klauzuli WHERE?🤔
DELETE FROM table_name;
Uruchomienie instrukcji DELETE w pokazany sposób powoduje usunięcie wszystkich wierszy z tabeli bazy danych.
Jeśli instrukcja DELETE lub zestaw instrukcji DELETE są częścią niezatwierdzonej transakcji, można cofnąć zmiany. Zaleca się jednak wykonanie kopii zapasowej danych w innym miejscu.
SQL DELETE Przykład użycia
Zobaczmy teraz instrukcję usuwania SQL w działaniu.
Usunęliśmy wszystkie rekordy z tabeli zadań. Możesz więc ponownie uruchomić instrukcję INSERT (którą uruchomiliśmy wcześniej), aby wstawić rekordy:
-- 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');
Najpierw użyjmy instrukcji DELETE z klauzulą WHERE. Poniższe zapytanie usuwa wszystkie wiersze, których stan to „Completed”:
DELETE FROM tasks WHERE status="Completed"; Query OK, 6 rows affected (0.14 sec)
Teraz uruchom to zapytanie SELECT:
SELECT * FROM tasks;
Zobaczysz, że obecnie jest 14 wierszy:
+---------+---------+------------+-------------+----------+ | 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)
Uruchomienie następującej instrukcji DELETE powoduje usunięcie wszystkich pozostałych 14 rekordów w tabeli:
DELETE FROM tasks; Query OK, 14 rows affected (0.20 sec)
A tabela zadań jest teraz pusta:
SELECT * FROM tasks; Empty set (0.00 sec)
Instrukcja SQL DROP
Do tej pory nauczyliśmy się:
- Instrukcja TRUNCATE usuwa wszystkie wiersze z tabeli.
- Instrukcja DELETE — bez klauzuli WHERE — usuwa wszystkie rekordy z tabeli.
Jednak instrukcje TRUNCATE i DELETE nie usuwają tabeli. Jeśli chcesz usunąć tabelę z bazy danych, możesz użyć polecenia DROP TABLE w następujący sposób:
DROP TABLE table_name;
Teraz usuńmy tabelę zadań z bazy danych:
mysql> DROP TABLE tasks; Query OK, 0 rows affected (0.43 sec)
Zobaczysz, że POKAŻ TABELE; zwraca pusty zestaw (ponieważ usunęliśmy jedyną tabelę, która była obecna w bazie danych):
mysql> SHOW TABLES; Empty set (0.00 sec)
Kiedy używać TRUNCATE a DELETE w SQL
FeatureTRUNCATEDELETESyntaxTRUNCATE TABLE nazwa_tabeli;Z klauzulą WHERE: DELETE FROM nazwa_tabeli WHERE warunek;
Bez klauzuli WHERE: DELETE TABLE nazwa_tabeli; SQL Podzbiór Język definicji danych (DDL) Język manipulacji danymi (DML) Efekt Usuwa wszystkie wiersze w tabeli bazy danych. Po uruchomieniu bez klauzuli WHERE instrukcja DELETE usuwa wszystkie rekordy w tabeli bazy danych. WydajnośćWięcej wydajniejsza niż instrukcja DELETE podczas pracy z dużymi tabelami. Mniej wydajna niż instrukcja TRUNCATE.
Podsumowując:
- Gdy musisz usunąć wszystkie wiersze z dużej tabeli bazy danych, użyj instrukcji TRUNCATE.
- Aby usunąć podzbiór wierszy na podstawie określonych warunków, użyj instrukcji DELETE.
Podsumowując
Zakończmy naszą dyskusję podsumowaniem:
- Podczas pracy z tabelami bazy danych możesz chcieć usunąć podzbiór wierszy lub wszystkie wiersze w określonej tabeli. Aby to zrobić, możesz użyć instrukcji TRUNCATE lub DELETE.
- Instrukcja TRUNCATE przyjmuje następującą składnię: TRUNCATE TABLE nazwa_tabeli;. Usuwa wszystkie wiersze w tabeli określonej przez nazwa_tabeli, ale nie usuwa samej tabeli.
- Instrukcja DELETE przyjmuje następującą składnię: DELETE FROM nazwa_tabeli WHERE warunek;. Spowoduje to usunięcie wierszy, dla których warunek predykatu jest prawdziwy.
- Uruchomienie instrukcji SQL DELETE bez klauzuli WHERE powoduje usunięcie wszystkich wierszy w tabeli. Tak więc funkcjonalnie osiąga to ten sam wynik, co instrukcja SQL TRUNCATE.
- Uruchamianie TRUNCATE jest szczególnie szybsze podczas pracy z większymi tabelami, ponieważ nie skanuje całej tabeli. Więc kiedy musisz usunąć wszystkie wiersze w dużej tabeli bazy danych, uruchomienie truncate może być bardziej wydajne.
- Gdy musisz usunąć podzbiór wierszy — na podstawie określonego warunku — możesz użyć instrukcji SQL DELETE.
Aby uzyskać szybki przegląd często używanych poleceń SQL, sprawdź tę ściągawkę SQL.