Раздел 35 · SQLite с нуля

Представления (VIEW)

junior~20 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

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

Ярлык на рабочем столе

Представь папку с документами глубоко на диске: «Работа», внутри «Кофейня», внутри «Отчёты», внутри «2026». Чтобы не проходить этот путь каждый день, на рабочий стол кладут ярлык. Ярлык сам не хранит документы, это ссылка. Открываешь ярлык, а видишь содержимое настоящей папки, со всеми вчерашними изменениями.

Представление работает так же. Это запрос SELECT, которому дали имя. Данных в нём нет: когда ты читаешь представление, SQLite выполняет сохранённый запрос над настоящими таблицами.

Без представления

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

sqlite> SELECT o.id, c.name AS guest, s.name AS barista
   ...> FROM orders o
   ...> LEFT JOIN customers c ON c.id = o.customer_id
   ...> JOIN staff s ON s.id = o.staff_id
   ...> WHERE o.id <= 3;
╭────┬───────┬─────────╮
│ id │ guest │ barista │
╞════╪═══════╪═════════╡
│  1 │ Маша  │ Аня     │
│  2 │ NULL  │ Аня     │
│  3 │ Пётр  │ Гоша    │
╰────┴───────┴─────────╯

LEFT JOIN с гостями нужен, чтобы не потерять заказы без карты: у второго заказа гостя нет. Забудешь LEFT, и такие заказы молча исчезнут из отчёта. Именно такие ошибки представление помогает не повторять.

CREATE VIEW

Представление создают командой CREATE VIEW имя AS и дальше обычный SELECT:

sqlite> CREATE VIEW order_list AS
   ...> SELECT o.id, o.ordered_at, c.name AS guest, s.name AS barista
   ...> FROM orders o
   ...> LEFT JOIN customers c ON c.id = o.customer_id
   ...> JOIN staff s ON s.id = o.staff_id;
sqlite> SELECT id, guest, barista FROM order_list WHERE barista = 'Аня';
╭────┬───────┬─────────╮
│ id │ guest │ barista │
╞════╪═══════╪═════════╡
│  1 │ Маша  │ Аня     │
│  2 │ NULL  │ Аня     │
│  7 │ Дима  │ Аня     │
│  8 │ Маша  │ Аня     │
╰────┴───────┴─────────╯

Обрати внимание на второй запрос. Он короткий, в нём нет ни одного JOIN, а к представлению можно добавить WHERE, ORDER BY и всё остальное, как к таблице. Столбцы представления получили имена из псевдонимов: guest и barista.

Где представление в базе

Представление живёт в базе рядом с таблицами. .tables показывает его в одном списке с таблицами, а .schema печатает исходный запрос:

sqlite> .tables
customers     drinks     order_items     order_list     orders     staff
sqlite> .schema order_list
CREATE VIEW order_list AS
SELECT o.id, o.ordered_at, c.name AS guest, s.name AS barista
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
JOIN staff s ON s.id = o.staff_id
/* order_list(id,ordered_at,guest,barista) */;

Последняя строка в комментарии /* ... */ дописана утилитой sqlite3, в базе её нет. Это подсказка: какие столбцы видны снаружи у представления.

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

Представление поверх представления

Представление можно соединять с таблицами и с другими представлениями. Сделаем второе, с суммой каждого заказа. Агрегаты и GROUP BY были в уроке про группировку:

sqlite> CREATE VIEW order_totals AS
   ...> SELECT i.order_id, sum(i.qty * d.price) AS total
   ...> FROM order_items i JOIN drinks d ON d.id = i.drink_id
   ...> GROUP BY i.order_id;
sqlite> SELECT l.id, l.guest, t.total FROM order_list l JOIN order_totals t ON t.order_id = l.id WHERE t.total >= 400;
╭────┬───────┬───────╮
│ id │ guest │ total │
╞════╪═══════╪═══════╡
│  5 │ Лена  │   660 │
│  7 │ Дима  │   410 │
│ 10 │ Дима  │   510 │
│ 11 │ NULL  │   400 │
│ 12 │ Лена  │   430 │
╰────┴───────┴───────╯

Большой отчёт собрался из двух понятных кусков. Так представления помогают разбить сложный запрос на части с говорящими именами.

Данные всегда свежие

Представление не копия данных, поэтому оно не устаревает. Раф подорожал, и суммы заказов с рафом сразу другие:

sqlite> UPDATE drinks SET price = 260 WHERE name = 'Раф';
sqlite> SELECT order_id, total FROM order_totals WHERE order_id IN (5, 12);
╭──────────┬───────╮
│ order_id │ total │
╞══════════╪═══════╡
│        5 │   700 │
│       12 │   450 │
╰──────────┴───────╯

Было 660 и 430, стало 700 и 450. Никто представление не обновлял: SQLite выполнил запрос заново над изменённой таблицей drinks. Оборотная сторона та же: тяжёлый запрос внутри представления выполняется при каждом чтении. Представление не ускоряет запрос, оно его прячет за именем.

Только для чтения

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

sqlite> INSERT INTO order_list (id, guest) VALUES (13, 'Маша');
Parse error near line 1: cannot modify order_list because it is a view
sqlite> CREATE VIEW order_list AS SELECT 1;
Parse error near line 2: view order_list already exists
  CREATE VIEW order_list AS SELECT 1;
              ^--- error here
sqlite> CREATE VIEW IF NOT EXISTS order_list AS SELECT 1;

CREATE VIEW IF NOT EXISTS молча ничего не делает, если имя занято. Так пишут скрипты, которые можно запускать много раз. Но учти: старое представление при этом остаётся старым, новый запрос не применится. Изменить представление в SQLite можно только так: удалить и создать заново. Удаляет его DROP VIEW, таблицы при этом не страдают:

sqlite> DROP VIEW order_totals;
sqlite> .tables
customers     drinks     order_items     order_list     orders     staff

В PostgreSQL у представлений больше возможностей, например CREATE OR REPLACE VIEW и материализованные представления. Про них рассказывает урок о представлениях в PostgreSQL.

Попробуй сам

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

Задай цену матча-латте через UPDATE drinks и запусти SELECT из представления ещё раз: матча-латте должна появиться сама.

Проверь себя

Задание

Дальше

Представления читают данные, а менять их приходится обычными INSERT и UPDATE. Что делать, если изменений несколько и они должны пройти вместе или не пройти вовсе? В следующем уроке разбираем транзакции.

домашка

Домашка