СУБД SQLite

В качестве реализации языка SQL мы будем использовать СУБД SQLite. Сразу отметим, что она весьма сильно отличается от остальных распространённых реализаций SQL и во многом не соответствует стандарту. Тем не менее, она наиболее проста для использования.

Для Windows-систем можно скачать с официального сайта архив sqlite-tools, содержащий интерпретатор SQL-команд sqlite3. Для POSIX-совместимых систем (в том числе WSL) интерпретатор sqlite3 можно установить при помощи менеджера пакетов.

Работа с SQLite-базами при помощи интерпретатора команд — далеко не основной режим работы. Тем не менее, с точки зрения процесса обучения, он является одним из наиболее удобных.

Если запустить sqlite3 без аргументов, он создаёт БД в памяти. Создать БД в файле можно одним из двух способов:

  • дать интерпретатору команду .open название_файла
  • запустить интерпретатор с названием файла в качестве аргумента командной строки

Также отметим ресурсы SQL Online и sql.js, предоставляющие альтернативные sqlite3 интерпретаторы команд СУБД SQLite.

Основы языка SQL

Заполнение БД

Каждая команда языка SQL, вбитая в интерпретатор, должна завершаться точкой-с-запятой (.open не является командой языка SQL).

Чтобы создать таблицу, используется команда

CREATE TABLE название_таблицы(столбец1,столбец2, ...);

Язык SQL является регистронезависимым (в разумных пределах: нелатинские буквы в названиях таблиц и колонок обычно допускаются, но поведение таких названий не регламентировано). Тем не менее, обычно части SQL-команд пишут заглавными буквами, а названия таблиц и столбцов — маленькими. Если название таблицы или столбца состоит из нескольких слов или использует служебные символы, его экранируют двойными кавычками.

Для того, чтобы удалить таблицу foobar, можно воспользоваться командой

DROP TABLE foobar;

Чтобы посмотреть названия таблиц и их колонок, можно воспользоваться командой .schema (естественно, это команда интерпретатора sqlite3, а не языка SQL; в других СУБД это делается по-другому).

Чтобы добавить строчку в таблицу foobar, можно написать

INSERT INTO foobar(столбец1, столбец2, ...) VALUES(значение1, значение2, ...);

Чтобы оставить ячейку пустой, можно воспользоваться специальным словом NULL или не указывать её в списке столбцов, в которые добавляются данные. Числовые данные записываются цифрами (и десятичной точкой, если нужно). Текстовые данные заключаются в одинарные кавычки.

Довольно распространённая практика — включать в таблицу уникальный идентификатор записи. Обычно это делается так:

CREATE TABLE foobar(id INTEGER PRIMARY KEY, остальные столбцы);

В данном случае INTEGER PRIMARY KEY — указание типа данных столбца с названием id. Такой тип данных обеспечивает:

  • быстрый поиск по значениям этой колонки
  • уникальность значений в этой колонке
  • непустоту ячеек этой колонки

На последнем пункте остановимся отдельно: если не указывать этот столбец при добавлении данных, значение ячейки будет сгенерировано автоматически. Это очень удобно!

Можно (а по стандарту SQL — нужно) указать типы и остальных столбцов. Но конкретно СУБД SQLite является динамически типизированным, при этом пытаясь (не очень простым образом) эмулировать поведение более классических СУБД. Поэтому для простоты договоримся не указывать типы данных столбцов, не являющихся идентификатором записи.

Работа с БД

Напомним, что основной режим работы с БД — извлечение нужной информации. Для этого используется команда SELECT. Наиболее простая её разновидность устроена так:

SELECT формат_вывода FROM таблица WHERE условия_на_запись;

Такая команда достаёт из указанной таблицы все записи, удовлетворяющие заданному условию, и выдаёт их в заданном формате.

Например, рассмотрим БД со схемой:

CREATE TABLE people(id INTEGER PRIMARY KEY, name, age);

Чтобы получить имена всех людей, которым больше 30 лет, можно сделать такой запрос:

SELECT name FROM people WHERE age > 30;

В формате вывода можно перечислить несколько выражений через запятую, например (в предположении наличия таблицы numbers с колонками x и y):

SELECT x, y, x+y FROM numbers;

В том месте команды SELECT, где указывается таблица, разрешаются более сложные выражения, чем одиночное название таблицы. Например, можно использовать запятую как операцию декартова произведения таблиц (декартово произведение двух таблиц — множество всех пар записей этих таблиц).

Например, в предположении следующей схемы БД

CREATE TABLE people(id INTEGER PRIMARY KEY, name);
CREATE TABLE ages  (id INTEGER PRIMARY KEY, people_id, age);

для получения имён всех людей, которым больше 30 лет, можно использовать запрос

SELECT name FROM people,ages WHERE people.id = people_id AND age > 30;

Обратите внимание на то, что нам пришлось явно указать people.id, поскольку есть ещё колонка ages.id. В том случае, когда в запросе есть две таблицы с одинаковым названием, их можно на время запроса переименовать:

SELECT a.name, b.name FROM people AS a, people AS b;

Ещё бывают полезны агрегатные запросы. Например, чтобы посчитать сумму построчных произведений чисел таблицы numbers со столбцами x, y и z, можно воспользоваться запросом

SELECT sum(x*y*z) FROM numbers;

Для sum(1) есть почти аббревиатура count(*) (на самом деле, функцию count можно использовать и с названием колонки в качестве аргумента; в этом случае вычисляется количество записей, в которых соответствующая ячейка непуста). Единственная существенная разница между sum(1) и count(*) состоит в том, что sum на пустом наборе записей даёт значение NULL, а count — ноль.

Удалить запись из таблицы foobar можно командой

DELETE FROM foobar WHERE условие;

Заметим, что наличие уникальных идентификаторов в таблице очень сильно упрощает удаление конкретных записей.

Изменить запись можно командой UPDATE. Она обычно используется как-то так:

UPDATE foobar SET foo=1, bar=3 WHERE id=5;

Естественно, в WHERE допустимо любое условие.

Более сложные запросы

Как внутри FROM, так и внутри WHERE допускаются подзапросы.

Внутри FROM такой подзапрос трактуется естественным образом — как таблица, состоящая из результатов такого запроса. Часто такие подзапросы отделяют при помощи конструкции WITH:

-- следующие выражения эквивалентны:
SELECT ... FROM (SELECT ... FROM ...);
--              /  /  /  /  /  /  /
--             /  /  /  /  /  /  /
--            /  /  /  /  /  /  /
--           /  /  /  /  /  /  /
--          /  /  /  /  /  /  /
WITH a AS (SELECT ... FROM ...)
SELECT ... FROM a;

Внутри WHERE чаще всего подзапросы встречаются в виде

foo = (SELECT sum(...) FROM ...)
foo IN (SELECT bar FROM ...)

В случае бинарных операторов «равно», «больше», «меньше», IS и т.п. подзапрос должен возвращать одну запись. Для оператора IN подзапрос должен возвращать одну колонку. В этом случае IN проверяет принадлежность значения результату подзапроса. (Также IN допускает правым аргументом список значений наподобие foo IN (1,2,3), а также — имена таблиц.)

Проверить подзапрос на пустоту можно одним из как минимум двух способов:

(SELECT count(*) FROM ...) > 0
-- или же --
EXISTS (SELECT foo FROM ...)

Первый из них более гибок, но одновременно и более громоздок.

Теперь полезные приёмы, не касающиеся подзапросов.

Если требуется проверить значение на пустоту, нужно пользоваться выражением IS NULL, а не = NULL, как может показаться на первый взгляд.

Если требуется упорядочить результаты, можно воспользоваться модификаторами ORDER BY foo или ORDER BY foo DESC.

Если требуется ограничить количество результатов n записями, можно воспользоваться модификатором LIMIT n.

Если требуется для каждого достижимого значения некоторого выражения выдать ровно один результат с таким значением, можно воспользоваться модификатором GROUP BY выражение. Например, 

SELECT foo,bar FROM baz GROUP BY bar;

для таблицы

1|2
3|2
2|3

выдаст (в зависимости от фазы луны) одно из двух: либо (1,2) и (2,3), либо (3,2) и (2,3).

Ещё модификатор GROUP BY выражение можно применять для агрегатных запросов. Для каждого достижимого значения выражения выдаётся ровно один результат, в котором агрегатные функции считаются только по записям с одним и тем же значением выражения.

Упражнения

Все упражнения следует выполнять в предположении следующей схемы БД:

CREATE TABLE people (id INTEGER PRIMARY KEY, name);
CREATE TABLE ages   (id INTEGER PRIMARY KEY, people_id, age);
CREATE TABLE parents(id INTEGER PRIMARY KEY, parent_id, child_id);

  1. Составьте запрос, который выдаёт имена всех людей, у которых есть хотя бы два ребёнка.

  2. Составьте запрос, который выдаёт имена детей всех людей, у которых есть хотя бы два ребёнка. Один и тот же человек не должен находиться в выдаче более одного раза!

  3. Составьте запрос, который выдаёт имена людей вместе с количеством внуков у каждого.

  4. Составьте запрос, который выдаёт имена и возраста всех людей, являющихся внуками тех, у кого более двух детей.