Дізнайтеся, як оператори TRUNCATE і DELETE працюють у SQL, які відмінності між ними та коли вам слід віддати перевагу використанню одного над іншим.
Під час роботи з таблицями бази даних вам, можливо, доведеться видалити підмножину рядків або всі рядки. Щоб видалити рядки з таблиці бази даних, ви можете використовувати оператори SQL TRUNCATE або DELETE залежно від варіанту використання.
У цьому підручнику ми докладніше розглянемо кожен із операторів, зрозуміємо, як вони працюють, і вирішимо, коли віддати перевагу використанню TRUNCATE над DELETE і навпаки.
Перш ніж йти далі, корисно переглянути такі підмножини SQL:
- Інструкції мови визначення даних (DDL) використовуються для створення та керування об’єктами бази даних, наприклад таблицями. Оператори SQL CREATE, DROP і TRUNCATE є прикладами операторів DDL.
- Інструкції мови обробки даних (DML) використовуються для маніпулювання даними в об’єктах бази даних. Інструкції DML використовуються для виконання операцій створення, читання, оновлення та видалення записів.
- Інструкції мови запитів даних (DQL) використовуються для отримання даних із таблиць бази даних. Усі оператори SELECT належать до підмножини DQL.
Як використовувати інструкцію SQL TRUNCATE
Синтаксис оператора SQL TRUNCATE
Синтаксис використання оператора SQL TRUNCATE такий:
TRUNCATE TABLE table_name;
Виконання наведеної вище команди TRUNCATE видаляє всі рядки в таблиці, визначені table_name, але не видаляє таблицю.
Операція скорочення не сканує всі записи в таблиці. Тому це значно швидше під час роботи з великими таблицями бази даних.
Приклад використання SQL TRUNCATE
📑 Примітка: якщо у вас є MySQL встановлений на вашій машині, ви можете кодувати за допомогою клієнта командного рядка MySQL. Ви також можете слідувати в іншій СУБД на ваш вибір, наприклад PostgreSQL.
Давайте спочатку створимо базу даних для роботи:
mysql> CREATE DATABASE db1; Query OK, 1 row affected (1.50 sec)
Потім виберіть базу даних, яку ми щойно створили:
mysql> use db1; Database changed
Наступним кроком є створення таблиці бази даних. Виконайте наступний оператор CREATE TABLE, щоб створити просту таблицю завдань:
-- 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)
);
У цьому прикладі таблиця завдань має такі стовпці:
- task_id: автоматично збільшується унікальний ідентифікатор для кожного завдання.
- title: назва або назва завдання, обмежена 255 символами.
- due_date: Термін виконання завдання, представлений у вигляді дати.
- статус: статус завдання, яке може бути «Очікує», «Виконується» або «Виконано». За замовчуванням установлено значення «Очікує на розгляд».
- assignee: особа, яка виконує певне завдання.
Тепер, коли ми створили таблицю завдань, давайте вставимо в неї записи:
-- 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');
Після виконання оператора вставки ви повинні побачити подібний результат:
Query OK, 20 rows affected (0.18 sec) Records: 20 Duplicates: 0 Warnings: 0
Тепер запустіть команду TRUNCATE table, щоб видалити всі записи з таблиці завдань:
TRUNCATE TABLE tasks; Query OK, 0 rows affected (0.72 sec)
Це видалить усі записи, а не таблицю. Ви можете перевірити це, запустивши SHOW TABLES; так:
SHOW TABLES;
+---------------+ | Tables_in_db1 | +---------------+ | tasks | +---------------+ 1 row in set (0.00 sec)
А запит SELECT для отримання даних із таблиці завдань повертає порожній набір:
SELECT * FROM tasks; Empty set (0.00 sec)
Як використовувати інструкцію SQL DELETE
Синтаксис оператора SQL DELETE
Загальний синтаксис використання оператора SQL DELETE такий:
DELETE FROM table_name WHERE condition;
Умова в реченні WHERE є предикатом, який визначає, який із рядків слід видалити. Інструкція DELETE видаляє всі рядки, для яких предикат має значення True.
Таким чином, оператор DELETE дає змогу краще контролювати, які записи буде видалено.
Але що відбувається, коли ви використовуєте оператор DELETE без пропозиції WHERE?🤔
DELETE FROM table_name;
Виконання оператора DELETE, як показано, видаляє всі рядки в таблиці бази даних.
Якщо оператор DELETE або набір операторів DELETE є частиною незафіксованої транзакції, ви можете відкотити зміни. Однак рекомендується створити резервну копію даних в іншому місці.
Приклад використання SQL DELETE
Тепер давайте подивимося на дію оператора видалення SQL.
Ми видалили всі записи з таблиці завдань. Отже, ви можете повторно запустити оператор INSERT (який ми запускали раніше), щоб вставити записи:
-- 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');
Спочатку скористаємося оператором DELETE з пропозицією WHERE. Наступний запит видаляє всі рядки зі статусом “Виконано”:
DELETE FROM tasks WHERE status="Completed"; Query OK, 6 rows affected (0.14 sec)
Тепер виконайте цей запит SELECT:
SELECT * FROM tasks;
Ви побачите, що наразі є 14 рядків:
+---------+---------+------------+-------------+----------+ | 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)
Виконання наступного оператора DELETE видаляє всі решту 14 записів у таблиці:
DELETE FROM tasks; Query OK, 14 rows affected (0.20 sec)
І таблиця завдань тепер порожня:
SELECT * FROM tasks; Empty set (0.00 sec)
Інструкція SQL DROP
Наразі ми дізналися:
- Інструкція TRUNCATE видаляє всі рядки з таблиці.
- Оператор DELETE без речення WHERE видаляє всі записи з таблиці.
Однак оператори TRUNCATE і DELETE не видаляють таблицю. Якщо ви хочете видалити таблицю з бази даних, ви можете скористатися командою DROP TABLE так:
DROP TABLE table_name;
Тепер давайте видалимо таблицю завдань з бази даних:
mysql> DROP TABLE tasks; Query OK, 0 rows affected (0.43 sec)
Ви побачите, що SHOW TABLES; повертає порожній набір (оскільки ми видалили єдину таблицю, яка була присутня в базі даних):
mysql> SHOW TABLES; Empty set (0.00 sec)
Коли використовувати TRUNCATE чи DELETE у SQL
FeatureTRUNCATEDELETESсинтаксисTRUNCATE TABLE table_name;З реченням WHERE: DELETE FROM table_name WHERE умова;
Без пропозиції WHERE: DELETE TABLE table_name; SQL SubsetData definition language (DDL) Data manipulation language (DML) EffectDeletes all the rows in the database table.When run without the WHERE clause, DELETE statement deletes all records in the database table.PerformanceMore ефективніше ніж оператор DELETE під час роботи з великими таблицями. Менш ефективний ніж оператор TRUNCATE.
Підсумовуючи:
- Якщо вам потрібно видалити всі рядки з великої таблиці бази даних, використовуйте інструкцію TRUNCATE.
- Щоб видалити підмножину рядків на основі певних умов, скористайтеся інструкцією DELETE.
Підводячи підсумки
Підсумуємо нашу дискусію:
- Працюючи з таблицями бази даних, ви можете видалити підмножину рядків або всі рядки в певній таблиці. Для цього ви можете використовувати оператори TRUNCATE або DELETE.
- Оператор TRUNCATE приймає синтаксис: TRUNCATE TABLE ім’я_таблиці;. Він видаляє всі рядки в таблиці, визначеній table_name, але не видаляє саму таблицю.
- Оператор DELETE приймає синтаксис: DELETE FROM table_name WHERE умова;. Це видаляє рядки, для яких умова предикату є істинною.
- Виконання оператора SQL DELETE без пропозиції WHERE видаляє всі рядки в таблиці. Таким чином, функціонально це досягає того самого результату, що і оператор SQL TRUNCATE.
- Запуск TRUNCATE особливо швидший під час роботи з великими таблицями, оскільки він не сканує всю таблицю. Отже, коли вам потрібно видалити всі рядки у великій таблиці бази даних, запуск усечення може бути ефективнішим.
- Якщо вам потрібно видалити підмножину рядків (на основі певної умови), ви можете скористатися інструкцією SQL DELETE.
Для швидкого огляду часто використовуваних команд SQL перегляньте цю шпаргалку SQL.