Первая таблица: CREATE, INSERT, SELECT

В прошлых уроках таблицы появлялись мимоходом: надо было показать утилиту или типы. Сегодня таблица главная героиня. Мы соберём меню маленькой кофейни с нуля: создадим таблицу, заполним её, прочитаем и в конце удалим. Это три команды SQL, на которых держится всё остальное.

Разлинованный лист

Представь, что ты открываешь кофейню и заводишь тетрадь для меню. Сначала разлиновываешь страницу: «название», «цена», «объём». Это шапка, она не меняется. Потом построчно вписываешь напитки. Каждая строка это один напиток, каждая колонка один признак.

Таблица в базе устроена так же. Разлиновать лист значит выполнить CREATE TABLE. Вписать строку значит INSERT. Прочитать тетрадь значит SELECT. В теории баз данных строку называют кортежем, а столбец атрибутом, об этом есть урок про отношения и кортежи.

CREATE TABLE: шапка таблицы

Команда CREATE TABLE задаёт имя таблицы и список столбцов. У каждого столбца имя и тип:

sqlite> CREATE TABLE menu (
(x1...>   name TEXT,
(x1...>   price INTEGER,
(x1...>   volume INTEGER
(x1...> );

Утилита ничего не ответила, и это хороший знак: молчание значит успех. Столбцы перечисляют через запятую, после последнего запятой нет. Тип в SQLite это пожелание к значению, как мы выяснили в прошлом уроке, но писать его стоит всегда.

Имена таблиц и столбцов в этом разделе латиницей: так их не придётся брать в кавычки, и они одинаково читаются в любом языке программирования. Русские слова живут в данных.

INSERT: вписываем строки

INSERT INTO добавляет строки. Надёжнее всего перечислить столбцы, а потом значения в том же порядке:

sqlite> INSERT INTO menu (name, price, volume) VALUES ('Эспрессо', 120, 40);
sqlite> INSERT INTO menu VALUES
   ...>   ('Капучино', 190, 300),
   ...>   ('Латте', 210, 400);
sqlite> SELECT * FROM menu;
╭──────────┬───────┬────────╮
│   name   │ price │ volume │
╞══════════╪═══════╪════════╡
│ Эспрессо │   120 │     40 │
│ Капучино │   190 │    300 │
│ Латте    │   210 │    400 │
╰──────────┴───────┴────────╯

Во втором INSERT столбцы не перечислены. Тогда значения идут в порядке столбцов из CREATE TABLE, и их должно быть ровно столько, сколько столбцов. Там же видно, что одной командой можно вставить несколько строк: скобки со значениями перечисляют через запятую. Это быстрее, чем много отдельных INSERT.

Текст берут в одинарные кавычки, числа пишут без них. Если в тексте нужен апостроф, его удваивают: 'Кофе ''Мокко'''.

SELECT: читаем таблицу

SELECT * FROM menu выше читает всю таблицу. Звёздочка значит «все столбцы». Выбирать отдельные столбцы, фильтровать и сортировать научимся в уроках кластера про выборку, начиная с урока про SELECT и псевдонимы.

Обрати внимание на порядок строк. Сейчас они вышли в порядке вставки, но SQL этого не обещает. Без явной сортировки база вправе вернуть строки как ей удобнее. Когда порядок важен, его задают словом ORDER BY, о нём урок про сортировку.

Скрытый номер строки

Вставим напиток, не указав объём, и попросим ещё один столбец, которого мы не создавали:

sqlite> INSERT INTO menu (name, price) VALUES ('Раф', 240);
sqlite> SELECT rowid, name, volume FROM menu;
╭───────┬──────────┬────────╮
│ rowid │   name   │ volume │
╞═══════╪══════════╪════════╡
│     1 │ Эспрессо │     40 │
│     2 │ Капучино │    300 │
│     3 │ Латте    │    400 │
│     4 │ Раф      │ NULL   │
╰───────┴──────────┴────────╯

Два открытия. Первое: столбец, которому не дали значения, получил NULL, то есть «значения нет». Разговор про NULL впереди, ему посвящён отдельный урок.

Второе: у каждой строки есть rowid, скрытый номер. SQLite заводит его сама в каждой обычной таблице, нумерует строки по порядку вставки и хранит строки, отсортированными по этому номеру. В SELECT * он не попадает, но его можно запросить по имени. Обычно вместо голого rowid заводят свой столбец id INTEGER PRIMARY KEY, и SQLite делает его синонимом rowid. Это тема урока про первичный ключ.

Ошибки, которые встретятся

Повторный CREATE TABLE с тем же именем падает: таблица уже есть. Если не уверен, существует ли таблица, добавь IF NOT EXISTS, и команда молча ничего не сделает:

sqlite> CREATE TABLE menu (name TEXT);
Parse error near line 1: table menu already exists
  CREATE TABLE menu (name TEXT);
               ^--- error here
sqlite> CREATE TABLE IF NOT EXISTS menu (name TEXT);

Parse error значит, что база отказалась ещё до выполнения, на этапе разбора команды. Строка с ^--- error here показывает место, где она споткнулась. Учти, что IF NOT EXISTS не сравнивает столбцы: старая таблица останется со своими тремя столбцами, а не с одним name.

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

sqlite> INSERT INTO menu VALUES ('Какао', 180);
Parse error near line 1: table menu has 3 columns but 2 values were supplied

Лекарство: перечислить столбцы, как в INSERT с «Рафом».

DROP TABLE: удаляем таблицу

Таблица больше не нужна? DROP TABLE удаляет её вместе со всеми строками:

sqlite> DROP TABLE menu;
sqlite> SELECT * FROM menu;
Parse error near line 2: no such table: menu

База не переспрашивает и корзины у неё нет. На настоящих данных перед DROP TABLE делают копию файла базы. Есть и вариант DROP TABLE IF EXISTS, он не ругается, если таблицы уже нет. Вместе с CREATE TABLE IF NOT EXISTS он помогает писать скрипты, которые можно запускать много раз.

Попробуй сам

Песочница начинает с пустой базы. Собери своё меню, посмотри на скрытые номера строк и намеренно повтори создание таблицы:

Добавь в меню свои напитки, в том числе с апострофом в названии. Потом удали таблицу через DROP TABLE и создай её заново с другим набором столбцов.

Проверь себя

Задание

Дальше

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