СУБД SQLite

Термины СУБД и БД

Системой управления базами данных (СУБД) называют программное средство, позволяющее хранить информацию и извлекать хранимую информацию. В каком-то смысле, даже обычный массив в языке Си вместе с операциями индексации и индексированного присваивания можно считать примером очень примитивной СУБД. Тем не менее, обычно к СУБД подобные элементарные вещи не относят.

Обычно СУБД поддерживают возможность изолировать подмножества хранимых данных друг от друга. Единицы изоляции называют базами данных (БД). Например, если в качестве СУБД рассматривать только вышеупомянутые операции индексации и индексированного присваивания языка Си, то отдельными БД для такой СУБД будут массивы.

По факту понятия СУБД и БД часто используют как синонимы: ни к какой особой путанице это не приводит.

Реляционные БД

В этой главе мы обсудим т.н. реляционную модель СУБД/БД. Реляционная БД (именно БД как единица изоляции) представляет собой набор именованных таблиц. Каждая таблица имеет некоторое фиксированное (на момент создания таблицы) множество именованных колонок.

Единица хранения (т.н. запись) — строка таблицы. В таблицу в любой момент можно добавлять новые строки или удалять из неё уже имеющиеся там. Обычно разрешаются «неполные» строки: некоторые колонки в такой строке могут быть пустыми.

Основа реляционной модели — язык SQL, на котором строятся запросы к реляционной БД. Изначально (в 70х годах) этот язык не отождествлялся с реляционной моделью. В настоящее время, говоря о реляционных БД/СУБД, подразумевают именно ту или иную реализацию языка SQL.

СУБД SQLite

В качестве реализации языка SQL мы будем использовать СУБД SQLite. Для Windows-систем можно скачать с официального сайта архив sqlite-tools, содержащий интерпретатор SQL-команд sqlite3.

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

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

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

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

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

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

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

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

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

DROP TABLE foobar;

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

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

INSERT INTO foobar VALUES(значение1, значение2, ...);

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

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

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

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

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

На последнем пункте остановимся отдельно: если подать NULL в качестве значения колонки с типом INTEGER PRIMARY KEY, значение ячейки будет сгенерировано автоматически. Это очень удобно! (Подобное поведение специфично для 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 такой подзапрос трактуется естественным образом — как таблица, состоящая из результатов такого запроса. Внутри 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. Составьте запрос, который выдаёт имена и возраста всех людей, являющихся внуками тех, у кого более двух детей.