📥 1. INSERT — добавляем строки

Команда INSERT добавляет новые строки в таблицу. Есть два основных способа: вставка без указания столбцов и вставка с явным перечислением столбцов.

1.1 INSERT INTO table VALUES — простейшая вставка

Первый способ — перечислить значения для ВСЕХ столбцов таблицы в том порядке, в котором они объявлены:

-- Сначала создадим таблицу
CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, city TEXT, registered_at TEXT
); -- Вставка строки: указываем значения для ВСЕХ столбцов по порядку
INSERT INTO customers VALUES (1, 'Иван Петров', 'ivan@mail.com', 'Москва', '2024-01-15'); -- Можно вставить несколько строк одной командой
INSERT INTO customers VALUES (2, 'Анна Смирнова', 'anna@mail.com', 'СПб', '2024-02-10'), (3, 'Олег Кузнецов', 'oleg@mail.com', 'Казань', '2024-03-05'), (4, 'Мария Васильева', 'maria@mail.com', 'Москва', '2024-03-20');

1.2 INSERT INTO table (col1, col2) VALUES — вставка с указанием столбцов

Второй способ — явно перечислить, в какие столбцы вы вставляете данные. Это безопаснее: порядок значений соответствует вашему списку, а не порядку в таблице.

-- Указываем только нужные столбцы
INSERT INTO customers (name, email, city) VALUES ('Дмитрий Фёдоров', 'dmitry@mail.com', 'Новосибирск'); -- Столбец registered_at получит NULL (мы его не указали)
-- А id получит NULL, но если есть AUTOINCREMENT — значение подставится автоматически -- Можно указать столбцы в любом порядке
INSERT INTO customers (email, name, city, registered_at, id) VALUES ('sveta@mail.com', 'Светлана Козлова', 'Екатеринбург', '2024-04-01', 6); -- Множественная вставка с указанием столбцов
INSERT INTO customers (name, email, city, registered_at) VALUES ('Павел Соколов', 'pavel@mail.com', 'Москва', '2024-04-10'), ('Елена Новикова', 'elena@mail.com', 'СПб', '2024-04-12');

1.3 Вставка результата запроса (INSERT INTO ... SELECT)

Можно вставить в таблицу результат любого SELECT-запроса. Количество и типы столбцов должны совпадать:

-- Создадим таблицу для архива
CREATE TABLE customers_archive ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, city TEXT, archived_at TEXT
); -- Копируем всех клиентов из Москвы в архив
INSERT INTO customers_archive (id, name, email, city, archived_at)
SELECT id, name, email, city, date('now')
FROM customers
WHERE city = 'Москва'; -- Можно использовать любые выражения и функции в SELECT
INSERT INTO customers_archive (id, name, email, city, archived_at)
SELECT id, name, email, city, '2024-12-31'
FROM customers
WHERE city = 'СПб';

✏️ 2. UPDATE — изменяем строки

Команда UPDATE изменяет существующие строки. Синтаксис: UPDATE table SET col1 = val1, col2 = val2 WHERE условие.

2.1 Простое обновление одного столбца

-- Создадим таблицу товаров
CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL, category TEXT, stock INTEGER DEFAULT 0
); INSERT INTO products VALUES (1, 'Ноутбук', 75000.0, 'Электроника', 10), (2, 'Мышь', 2500.0, 'Электроника', 50), (3, 'Книга "SQL для начинающих"', 1500.0, 'Книги', 100), (4, 'Наушники', 5000.0, 'Электроника', 25), (5, 'Футболка', 1500.0, 'Одежда', 200); -- Изменить название товара
UPDATE products SET name = 'Мышь беспроводная' WHERE id = 2; -- Поднять цену на конкретный товар
UPDATE products SET price = 79000.0 WHERE id = 1; -- Посмотрим результат
SELECT * FROM products;

2.2 Обновление нескольких столбцов

-- Изменить цену и количество на складе одновременно
UPDATE products
SET price = 5500.0, stock = 30
WHERE id = 4; -- Увеличить цену на 10% для всей категории
UPDATE products
SET price = price * 1.10
WHERE category = 'Электроника'; -- Для всех товаров с остатком 0 установить пометку
UPDATE products
SET category = 'Распродажа'
WHERE stock = 0;

2.3 UPDATE с подзапросом

-- Обновить цену товара до средней цены по категории
UPDATE products
SET price = ( SELECT AVG(price) FROM products WHERE category = 'Книги'
)
WHERE id = 3 AND category = 'Книги'; -- Установить остаток на складе равным среднему по всем товарам
UPDATE products
SET stock = (SELECT ROUND(AVG(stock)) FROM products)
WHERE stock = 0;

🗑️ 3. DELETE — удаляем строки

Команда DELETE удаляет строки из таблицы. Синтаксис: DELETE FROM table WHERE условие.

3.1 Простое удаление по условию

-- Сначала посмотрим, что собираемся удалить (всегда проверяйте!)
SELECT * FROM products WHERE stock = 0; -- Удаляем товары, которых нет на складе
DELETE FROM products WHERE stock = 0; -- Удаляем товары дешевле 2000 рублей
DELETE FROM products WHERE price < 2000; -- Удаляем товары определённой категории
DELETE FROM products WHERE category = 'Распродажа';

3.2 DELETE с подзапросом

-- Создадим таблицу заказов
CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, order_date TEXT NOT NULL, status TEXT DEFAULT 'new'
); INSERT INTO orders VALUES (1, 1, '2024-01-10', 'delivered'), (2, 1, '2024-02-15', 'delivered'), (3, 2, '2024-03-01', 'shipped'), (4, 3, '2024-03-10', 'new'), (5, 4, '2024-04-01', 'new'), (6, 1, '2023-05-05', 'cancelled'); -- Удалить заказы клиента с id = 1 за 2023 год
DELETE FROM orders
WHERE customer_id = 1 AND order_date < '2024-01-01'; -- Удалить заказы со статусом 'cancelled' (отменённые)
DELETE FROM orders WHERE status = 'cancelled'; -- Удалить все заказы клиента с именем 'Иван Петров'
DELETE FROM orders
WHERE customer_id = ( SELECT id FROM customers WHERE name = 'Иван Петров'
);

3.3 TRUNCATE — быстрая очистка таблицы

TRUNCATE TABLE удаляет все строки таблицы гораздо быстрее, чем DELETE без WHERE. Но его нельзя откатить (в некоторых СУБД) и он не срабатывает триггеры:

-- Быстро удалить ВСЕ строки из таблицы
TRUNCATE TABLE customers_archive; -- Разница: DELETE можно откатить (ROLLBACK), TRUNCATE — обычно нет
-- TRUNCATE быстрее, но DELETE с WHERE гибче

🔄 4. RETURNING — получаем изменённые данные

Некоторые СУБД (PostgreSQL, SQLite с версии 3.35) поддерживают RETURNING — после INSERT, UPDATE, DELETE можно сразу получить изменённые строки:

-- Вставили и сразу получили данные новой строки
INSERT INTO products (name, price, category, stock)
VALUES ('Планшет', 45000.0, 'Электроника', 15)
RETURNING id, name, price; -- Обновили и увидели старые и новые значения
UPDATE products
SET price = price * 1.15
WHERE category = 'Электроника'
RETURNING id, name, price AS new_price; -- Удалили и посмотрели, что удалили
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING id, customer_id, order_date;

🔒 5. UPSERT — INSERT или UPDATE (SQLite)

UPSERT — комбинация INSERT + UPDATE: если строка уже существует (по PRIMARY KEY или UNIQUE), то выполняется UPDATE, иначе INSERT. В SQLite — INSERT ... ON CONFLICT DO UPDATE:

-- UPSERT: если товар с id=1 существует — обновить цену, иначе вставить
INSERT INTO products (id, name, price, category, stock)
VALUES (1, 'Ноутбук', 80000.0, 'Электроника', 12)
ON CONFLICT(id) DO UPDATE SET price = excluded.price, stock = excluded.stock; -- excluded — это специальная таблица с теми значениями, которые вы пытались вставить
-- То же самое с уникальным email
INSERT INTO customers (name, email, city)
VALUES ('Новый Клиент', 'ivan@mail.com', 'Москва')
ON CONFLICT(email) DO UPDATE SET name = excluded.name, city = excluded.city;

🛡️ 6. Транзакции и безопасность

Транзакции позволяют объединить несколько операций в одно атомарное действие. Либо выполняются все, либо ни одна:

-- Начинаем транзакцию
BEGIN; -- Списываем товар со склада
UPDATE products SET stock = stock - 1 WHERE id = 1; -- Создаём заказ
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, date('now'), 'new'); -- Если всё ок — фиксируем изменения
COMMIT; -- Если что-то пошло не так — откатываем
-- ROLLBACK; -- Транзакция гарантирует: либо товар списался И заказ создался,
-- либо ничего не изменилось

🧪 7. Практические задания

Попробуйте выполнить эти задания самостоятельно. Сначала создайте таблицы, воспользовавшись кодом из урока, затем пишите свои запросы.

Задание 1 — Добавить нового клиента

-- Добавьте нового клиента:
-- Имя: 'Виктория Морозова'
-- Email: 'viktoria@mail.com'
-- Город: 'Краснодар'
-- Зарегистрирована: сегодняшняя дата -- Потом добавьте ещё трёх клиентов одной командой

Задание 2 — Изменить цену товара

-- Поднимите цену на все товары категории 'Одежда' на 20%
-- После этого измените название товара с id = 5 на 'Футболка хлопковая'

Задание 3 — Удалить старые заказы

-- Удалите все заказы, сделанные до 1 января 2024 года
-- (используйте таблицу orders) -- Удалите все заказы клиента с email 'ivan@mail.com'
-- (используйте подзапрос для поиска customer_id)

Задание 4 — Транзакция

-- Поместите удаление старых заказов в транзакцию
-- Сначала сделайте SELECT, чтобы увидеть, что удалится
-- Потом DELETE внутри BEGIN/COMMIT

Задание 5 — UPSERT

-- Напишите UPSERT-запрос: если товар 'Ноутбук' уже есть — обновите его цену,
-- если нет — вставьте новый товар

📋 8. Шпаргалка по INSERT, UPDATE, DELETE

-- === INSERT ===
INSERT INTO table VALUES (val1, val2, val3); -- все столбцы по порядку
INSERT INTO table (col1, col2) VALUES (v1, v2); -- указали столбцы
INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4); -- несколько строк
INSERT INTO table SELECT ...; -- вставка из запроса -- === UPDATE ===
UPDATE table SET col = value WHERE условие; -- изменить один столбец
UPDATE table SET col1 = v1, col2 = v2 WHERE условие; -- изменить несколько
UPDATE table SET col = (SELECT ...) WHERE условие; -- с подзапросом -- === DELETE ===
DELETE FROM table WHERE условие; -- удалить строки по условию
DELETE FROM table; -- удалить ВСЁ (осторожно!)
TRUNCATE TABLE table; -- быстро очистить таблицу -- === ДОПОЛНИТЕЛЬНО ===
INSERT ... ON CONFLICT DO UPDATE SET ...; -- UPSERT
INSERT ... RETURNING ...; -- получить вставленные данные
UPDATE ... RETURNING ...; -- получить обновлённые данные
DELETE ... RETURNING ...; -- получить удалённые данные -- === ВСЕГДА ===
-- 1. Делайте SELECT перед UPDATE/DELETE, чтобы проверить WHERE
-- 2. Используйте транзакции (BEGIN/COMMIT/ROLLBACK)
-- 3. Не забывайте WHERE!!!