Четыре урока подряд мы только читали базу «Пенки»: выбирали столбцы, фильтровали, сортировали и ловили NULL. Но меню кофейни живёт: появляются новые напитки, цены растут, что-то убирают навсегда. В этом уроке учимся менять данные тремя командами: INSERT добавляет строки, UPDATE меняет их, DELETE удаляет.
Меловая доска у кассы
Представь меню на меловой доске за стойкой. Бариста может сделать с ним три вещи: дописать новую строку, стереть и переписать цену в существующей строке, стереть строку целиком. Чтобы переписать цену, он сначала находит нужную строку глазами: «вот капучино». Если он сотрёт цены, не посмотрев, у какого напитка, пострадает вся доска.
С таблицей то же самое. INSERT дописывает строку. UPDATE и DELETE сначала находят строки условием WHERE, как в уроке про фильтры, и только потом меняют или удаляют их. Вместе эти команды называют DML, языком изменения данных.
INSERT: новая строка
В уроке про первую таблицу мы писали INSERT со всеми значениями подряд. В живой базе так делают редко: проще перечислить столбцы, в которые кладёшь значения. Добавим в меню флэт уайт:
sqlite> INSERT INTO drinks (name, category, price, volume) VALUES ('Флэт уайт', 'кофе', 220, 250);
sqlite> SELECT id, name, price FROM drinks WHERE category = 'кофе';
╭────┬───────────┬───────╮
│ id │ name │ price │
╞════╪═══════════╪═══════╡
│ 1 │ Эспрессо │ 120 │
│ 2 │ Американо │ 150 │
│ 3 │ Капучино │ 190 │
│ 4 │ Латте │ 210 │
│ 5 │ Раф │ 240 │
│ 10 │ Флэт уайт │ 220 │
╰────┴───────────┴───────╯Столбец id мы не указали, а у новой строки он есть: 10. SQLite сам выдал следующий номер после самого большого. Почему так происходит, разберём в следующем уроке про первичный ключ.
Перечень столбцов спасает от двух бед. Порядок значений больше не зависит от порядка столбцов в таблице, и запрос не сломается, когда в таблицу добавят новый столбец. Столбцы, которых нет в перечне, получат значение по умолчанию, а если его нет, то NULL.
Одной командой можно вставить сразу несколько строк: наборы значений перечисляют через запятую.
sqlite> INSERT INTO drinks (name, category) VALUES ('Глинтвейн б/а', 'другое'), ('Морс', 'холодное');
sqlite> SELECT id, name, category, price FROM drinks WHERE id > 9;
╭────┬───────────────┬──────────┬───────╮
│ id │ name │ category │ price │
╞════╪═══════════════╪══════════╪═══════╡
│ 10 │ Флэт уайт │ кофе │ 220 │
│ 11 │ Глинтвейн б/а │ другое │ NULL │
│ 12 │ Морс │ холодное │ NULL │
╰────┴───────────────┴──────────┴───────╯Цену мы не назвали, и в обеих строках price стал NULL: напиток есть, цены пока нет.
UPDATE: переписать значение
UPDATE меняет значения в строках, которые уже есть. После SET пишут, какому столбцу какое значение дать, после WHERE пишут, в каких строках:
sqlite> UPDATE drinks SET price = 160 WHERE name = 'Морс';
sqlite> SELECT changes();
╭───────────╮
│ changes() │
╞═══════════╡
│ 1 │
╰───────────╯
sqlite> UPDATE drinks SET price = price + 10 WHERE category = 'кофе';
sqlite> SELECT changes();
╭───────────╮
│ changes() │
╞═══════════╡
│ 6 │
╰───────────╯Справа от = в SET может стоять выражение, в котором участвует старое значение: price + 10 прибавляет десять рублей к текущей цене каждого кофе.
Функция changes() отвечает, сколько строк задела последняя команда. Первая UPDATE нашла один морс, вторая шесть видов кофе. Это быстрая проверка: если ждал одну строку, а changes() говорит 600, пора насторожиться.
DELETE: стереть строку
DELETE удаляет строки, которые подходят под WHERE. Глинтвейн в меню не прижился:
sqlite> DELETE FROM drinks WHERE name = 'Глинтвейн б/а';
sqlite> SELECT changes();
╭───────────╮
│ changes() │
╞═══════════╡
│ 1 │
╰───────────╯Удаляется строка целиком. Стереть одно значение, оставив строку, можно только через UPDATE ... SET price = NULL.
Без WHERE страдают все
У UPDATE и DELETE условие WHERE необязательное. Без него команда работает со всеми строками таблицы, и SQLite не переспросит. Проверим на служебной таблице, которую не жалко. Создадим её и заполним строками из запроса: INSERT умеет брать значения не только из VALUES, но и из SELECT.
sqlite> CREATE TABLE price_log (drink TEXT, old_price INTEGER);
sqlite> INSERT INTO price_log SELECT name, price FROM drinks WHERE category = 'чай';
sqlite> SELECT * FROM price_log;
╭────────────────┬───────────╮
│ drink │ old_price │
╞════════════════╪═══════════╡
│ Чай с чабрецом │ 130 │
│ Матча-латте │ NULL │
╰────────────────┴───────────╯А теперь DELETE без условия:
sqlite> DELETE FROM price_log;
sqlite> SELECT changes();
╭───────────╮
│ changes() │
╞═══════════╡
│ 2 │
╰───────────╯
sqlite> SELECT count(*) FROM price_log;
╭──────────╮
│ count(*) │
╞══════════╡
│ 0 │
╰──────────╯Таблица на месте, строк в ней нет. Корзины, как в файловом менеджере, у базы нет. Привычка, которая спасает данные: сначала напиши SELECT с тем же WHERE, посмотри, какие строки он находит, и только потом замени SELECT * на DELETE. Отменять изменения целиком умеют транзакции, до них дойдём в уроке 23.
RETURNING: что именно изменилось
changes() говорит, сколько строк задела команда, но не какие. Если дописать в конец UPDATE, INSERT или DELETE слово RETURNING и перечень столбцов, команда вернёт изменённые строки, как обычный SELECT:
sqlite> UPDATE drinks SET price = 190 WHERE name = 'Матча-латте' RETURNING id, name, price;
╭────┬─────────────┬───────╮
│ id │ name │ price │
╞════╪═════════════╪═══════╡
│ 8 │ Матча-латте │ 190 │
╰────┴─────────────┴───────╯
sqlite> DELETE FROM drinks WHERE id = 12 RETURNING name, price;
╭──────┬───────╮
│ name │ price │
╞══════╪═══════╡
│ Морс │ 160 │
╰──────┴───────╯У матча-латте наконец появилась цена, а удалённый морс показал, какой он был перед удалением. RETURNING особенно полезен после INSERT: так программа сразу узнаёт номер, который база выдала новой строке. Та же команда есть в PostgreSQL, её разбирает урок про изменение данных.
Попробуй сам
Песочница работает на свежей базе «Пенки». Добавь напиток, подними ему цену и посмотри, что вернёт RETURNING, а потом удали его:
Попробуй убрать WHERE у UPDATE и посмотри на changes(). Кнопка «сбросить базу» вернёт меню к исходному.
Проверь себя
Задание
Дальше
Пока SQLite принимает в таблицу почти что угодно: напиток без категории, два одинаковых названия, отрицательную цену. В следующем уроке ставим таблице правила: первичный ключ, NOT NULL, UNIQUE и CHECK.
домашка