Изменение данных: INSERT, UPDATE, DELETE и транзакции

Этот урок меняет учебные данные. Когда закончите, запустите скрипт из урока 1 ещё раз — он вернёт базу в исходное состояние.

INSERT: добавить строку

До сих пор мы только читали данные. Новую строку добавляет INSERT:

INSERT INTO books (title, author, genre, price)
VALUES ('Мартин Иден', 'Лондон', 'роман', 520);

В скобках после названия таблицы — столбцы, после VALUES — значения в том же порядке. Столбец id мы не указали: в нашей таблице он объявлен как INTEGER PRIMARY KEY, и SQLite сам назначил следующий свободный номер. В PostgreSQL и MySQL для этого служат свои слова — GENERATED … AS IDENTITY и AUTO_INCREMENT, — но смысл тот же.

SELECT id, title, price FROM books WHERE id = 9;

id | title       | price
9  | Мартин Иден | 520

UPDATE: изменить значения

UPDATE меняет значения в строках, которые прошли условие WHERE. Поднимем цены на фэнтези на 50 рублей. Но сначала — проверка: тот же WHERE в SELECT покажет, что именно изменится.

SELECT title, price FROM books WHERE genre = 'фэнтези';

title                             | price
Гарри Поттер и философский камень | 650
Хоббит                            | 540

Две строки, те самые. Теперь правка — в SET можно ссылаться на старое значение:

UPDATE books SET price = price + 50 WHERE genre = 'фэнтези';

SELECT title, price FROM books WHERE genre = 'фэнтези';

title                             | price
Гарри Поттер и философский камень | 700
Хоббит                            | 590

Самая опасная ошибка — забыть WHERE. Запрос UPDATE books SET price = price + 50; без условия поднимет цены всем книгам, и база не переспросит. Отсюда правило: сначала SELECT с условием, потом UPDATE с тем же условием.

DELETE: удалить строки

DELETE удаляет строки, подходящие под условие. Правило то же: сначала посмотреть.

SELECT id, status FROM orders WHERE status = 'отменён';

id | status
5  | отменён

DELETE FROM orders WHERE status = 'отменён';

SELECT COUNT(*) FROM orders;

COUNT(*)
8

Было девять заказов, стало восемь. DELETE без WHERE очистил бы таблицу целиком. Саму таблицу он не удаляет — для этого есть отдельная команда DROP TABLE, которую в рабочих базах выполняют ещё осторожнее.

В настоящих системах заказы часто вообще не удаляют, а помечают статусом: история нужна для отчётов и разбирательств.

Транзакции: право на ошибку

Транзакция объединяет несколько изменений в одно целое: либо сохраняется всё, либо ничего.

• BEGIN — открыть транзакцию;
• COMMIT — сохранить все изменения;
• ROLLBACK — отменить всё, что сделано после BEGIN.

Поднимем цены на научные книги на 10% внутри транзакции и посмотрим на результат:

BEGIN;
UPDATE books SET price = ROUND(price * 1.1) WHERE genre = 'наука';
SELECT title, price FROM books WHERE genre = 'наука';

title                   | price
Краткая история времени | 792
Sapiens                 | 979

Допустим, решили, что поднимать рано. ROLLBACK возвращает всё к состоянию до BEGIN:

ROLLBACK;
SELECT title, price FROM books WHERE genre = 'наука';

title                   | price
Краткая история времени | 720
Sapiens                 | 890

Если бы результат устроил, вместо ROLLBACK мы написали бы COMMIT, и изменения стали бы постоянными. После COMMIT отменить их можно только новой правкой или из резервной копии. Поэтому в рабочих базах изменения данных согласуют с командой, а перед крупной правкой делают копию.

Что дальше

Вы прошли основу, на которой держится ежедневная работа с данными. Дальше стоит двигаться так:

• Практика. Решайте задачи на тренажёрах — sql-ex.ru, бесплатные курсы-тренажёры на Stepik — по две-три в день.
• Свои данные. Возьмите открытый набор — например, с портала data.mos.ru, — загрузите в таблицу и задайте ему вопросы, которые интересны вам.
• WITH. Конструкция WITH даёт подзапросу имя и выносит его в начало, и сложный запрос читается по шагам.
• Оконные функции. Считают итоги, не сворачивая строки: место товара в рейтинге, накопленная сумма, доля в общем итоге.
• Индексы. Тема скорости: почему один запрос отвечает мгновенно, а другой — минуту.

SQL встречается в вакансиях аналитика данных, тестировщика, бэкенд-разработчика, продакт-менеджера и маркетолога. Если хотите примерить одну из этих профессий, загляните в мини-курс «Тестировщик» на этом сайте, а для отчётов и графиков пригодится курс «Excel и Google Таблицы».

SELECT id, status FROM orders WHERE status = 'в пути';

id | status
7  | в пути
8  | в пути

BEGIN;
DELETE FROM orders WHERE status = 'в пути';
SELECT COUNT(*) FROM orders;

COUNT(*)
7

ROLLBACK;
SELECT COUNT(*) FROM orders;

COUNT(*)
9