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

Что такое базы данных и зачем они нужны?

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

  • удобство, даваемое унификацией
  • эффективный (быстрый) доступ к записям
  • надёжность

Надёжность означает, что если даже компьютер в процессе записи данных внезапно выключится, ранее записанные данные не будут потеряны. А данные, которые были записаны лишь наполовину, будут отброшены как некорректные. База данных должна оставаться в консистентном состоянии.

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

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

Самый популярный и простой тип баз данных — реляционные базы данных. Каждая такая база данных представляет из себя набор табличек. Таблица — это и есть та структура, ограничивающая формат данных. Идея таблицы в том, что каждая запись представляет из себя одну строку, при этом набор колонок фиксирован. Таким образом в каждой строке приведён один и тот же набор данных, ни больше, ни меньше. Например, в профиле пользователя всегда есть: имя, фамилия, дата рождения, информация о себе, email, и URL-адрес аватарки. Даже если информация о себе или аватар отсутствуют, в таблице они будут приведены в виде пустой ячейки. Структура таблиц и связей между ними называется схемой (schema).

Расположение данных в памяти

Каждая ячейка имеет фиксированный тип и ширину (в байтах). К примеру, мы можем потребовать, чтобы каждая дата занимала 8 байт, каждое имя — 40 символов (80 байтов в кодировке UTF-16), информация о себе — 1024 байта, email и URL — по 256 байт.

Тогда строка «имя + фамилия + год рождения + BIO + email + URL» занимает 80 + 80 + 8 + 1024 + 256 + 256 = 1704 байта. Если мы хотим найти в таблице 1001-ю строку, нам не нужно тысячу раз искать переход к следующей записи, достаточно отступить от начала файла 1000 × 1704 байта, и мы сразу попадём на нужную строку.

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

Теперь поймём, какие проблемы могут нас поджидать. Предположим, что нам нужно записать короткое имя «Иван» в текстовое поле длины 40 символов. В таком случае мы всё равно должны занять все 40 байт, и мы просто дозаполняем лишнее пространство «пустыми» символами, padding bytes.

Ситуация сложнее, если текстовое поле потенциально может быть очень большим. Например, если разрешено вводить текст комментария до миллиона символов, то каждая строка будет занимать более чем по 2 мегабайта, даже если большая часть комментариев в действительности влезает в 140 символов. Чтобы не допустить такого расточительства, длинные строки не пишут прямо в таблицу. Вместо этого длинные тексты записывают в отдельное место базы данных, а в таблицу кладут лишь указатель (адрес) на место расположения полного текста. Указатель занимает фиксированные 8 байт.

Типы значений

Набор базовых типов данных в раличных продуктах различаются (и эти типы по-разному называются). Все детали имеет смысл изучать на странице документации конкретной БД. Однако можно выделить такой набор основных типов:

  • BOOLEAN — логический тип
  • INTEGER — целые числа. Нумерация строк обыкновенно тоже хранится в целых числах
  • FLOAT/REAL — вещественные числа
  • DECIMAL — десятичные числа, чтобы из-за округления не терялась точность, например, в финансовых БД. Иллюстрация проблемы:
    float('0.1') * 3 == 0.30000000000000004 != 0.3
    decimal('0.1') * 3 == decimal('0.3')
  • VARCHAR(42) — строка длины 42 символа
  • TEXT — строка произвольной длины
  • DATE/TIME/DATETIME — дата и/или время
  • BLOB — набор байтов, не имеющий специального смысла для базы данных. Например, содержимое небольшого файла, которое вы решили записать вместо файла непосредственно в БД.

Стоит помнить, что в ячейке любого типа может быть записан NULL (если это явно не запрещено). Поэтому, например, логический тип имеет три значения: TRUE / FALSE / NULL.

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

OLTP vs OLAP

Базы данных делятся по применению на два класса: OLTP (Online Transaction Processing) и OLAP (Online Analytical Processing).

  • OLTP — это применение базы для обработки транзакций. Например, записывать все покупки в магазине и вести учёт товара в режиме реального времени. Или добавлять фотки в инстаграм. Или… да почти всё, что видит пользователь на современном веб-сайте или в мобильном приложении — это вотчина OLTP.
  • OLAP — это базы данных, предназначенные для аналитических запросов. Обыкновенно информация в такие базы записывается редко и сразу огромными партиями — ну, например, можно представить себе задачу по переносу из OLTP базы в OLAP всех транзакций, обработанных за неделю. OLAP базы эффективны для задач сбора агрегированной статистики. Если в OLTP вы обычно работаете со считанным числом строк, то в OLAP считается нормальной обработка всех строк таблицы. Иногда такие базы даже записывают данные не построчно, а поколоночно. Один и тот же софт может применяться и для первого, и для второго, но не всегда это будет эффективно.

Чем отличаются БД, СУБД, драйвер СУБД

Вообще «база данных» возникает в трёх контекстах, которые стоит держать в голове.

БД

БД — это сами данные. Каждый проект хранится в какой-то пачке таблиц, которые записаны в один или несколько файлов на диске.

СУБД

Часто словом БД называют «систему управления базами данных (СУБД)» / «Relational Database Management System (RDBMS)». На самом деле это скорее «сервер базы данных», то есть программа, которая принимает запросы к базе данных, выполняет их и возвращает клиенту ответ. MySQL, PostgreSQL, Oracle, MSSQL итд — это всё СУБД.

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

Важно понимать, что СУБД и основная программа работают по-отдельности друг от друга. Одна СУБД может одновременно работать с несколькими базами данных и к ней может подключаться несколько программ. Это такая единая точка входа. В крупных проектах СУБД нередко «живёт» на отдельном мощном компьютере, к которому подключается много других компьютеров, на которых развёрнуто приложение или приложения.

Сейчас стали распространены SAAS (software as a service) решения вида «managed database». Эти страшные слова обозначают всего лишь, что СУБД установлена и обслуживается не на вашем, а на чужом компьютере, и вы просто покупаете возможность хранить и обрабатывать там свои данные.

Драйвер СУБД

Когда программисты говорят, что надо подключить БД, они имеют в виду «воспользоваться драйвером системы управления базами данных», то есть специальной библиотекой, которая умеет подключаться к СУБД, отправлять ей запросы и получать ответы в том виде, который пригоден для вашего языка программирования.

Встраиваемые БД = СУБД + драйвер, 2-в-1

Есть отдельный класс баз данных — встраиваемые (embedded) БД. Например, к ним относится sqlite, с которым мы будем работать. Этот тип баз данных полезен для небольших программок, которые живут на одном устройстве и пишут/читают понемногу. Например записная книжка в телефоне может иметь встраиваемую БД. Такие базы объединяют драйвер и СУБД воедино. В итоге вам не нужно держать отдельную программу СУБД, всё необходимое содержится в вашей программе.

Это менее гибко и гораздо медленнее работает, зато работать с такой БД проще. Поэтому начнём мы именно с такой БД. Каждая база в sqlite хранится в виде единого файла, который можно спокойно хранить в любом месте на жёстком диске.

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

Практическая часть

Что вам понадобится

Дальше небольшая практическая часть. Для неё вам понадобится скачать и установить несколько программ и файлов:

  • SQLite можно скачать с официального сайта. Он, как и всё, с чем мы будем работать, абсолютно бесплатный.
  • Также скачайте SQLite Browser — это программка, чтобы можно было быстро посмотреть содержимое базы данных и выполнить пару запросов.

  • Тестовая база данных видеопроката, с которой мы экспериментируем. Зайдите на гитхабе в папку sqlite-sakila-db и скачайте файлы sakila.db (это сама БД в формате sqlite), recreate_sakila_sqlite.sh (bash-скрипт, которым эта БД создана), sqlite-sakila-schema.sql и sqlite-sakila-insert-data.sql — SQL-скрипты, описывающие структуру таблиц и данные, которые туда вносятся.

  • Графическую схему базы данных видеопроката можно посмотреть здесь.

Скрипт создания БД

Откройте recreate_sakila_sqlite.sh в блокноте. В скрипте команды написаны на bash (который встроен в Linux и MacOS). Их можно выполнить в терминале. Но аналогичные команды можно выполнить в командной строке Windows (чтобы её открыть, нажмите Win+R, напечатайте cmd и запустите). Так как БД уже создана, запускать скрипт нет необходимости, но полезно понять, что там происходит:

rm sakila.db
sqlite3 sakila.db < ./sqlite-sakila-schema.sql
sqlite3 sakila.db < ./sqlite-sakila-insert-data.sql

— Сначала старый файл с БД sakila.db удаляется командой rm — Затем запускается программа sqlite3, которой в качестве параметра передана база данных sakila.db. На первой команде файл БД отсутствует, поэтому будет создан с нуля, на второй команде откроется только что созданная БД и будет модифицирована.

Sqlite3 установлена на предыдущем этапе. Если команда пишет, что не может найти программу sqlite3, то либо пропишите полный путь к программе sqlite3, либо добавьте путь к папке с этой программой в переменные окружения и перезапустите терминал (командную строку).

Стрелочка < ./sqlite-sakila-schema.sql говорит, что команды, которые должны быть выполнены будут введены не с клавиатуры, а взяты из указанного файла. Если бы этой стрелочки не было, открылась бы командная строка sqlite, в которой вы могли бы руками вводить команды.

Схема БД

Создание таблицы

Первый файл, sqlite-sakila-schema.sql описывает схему БД. Если вы его откроете, вы увидите множество команд (каждая команда может занимать сколько угодно строк, заканчивается команда точкой с запятой). Главными командами в этом файле являются CREATE TABLE. Например,

CREATE TABLE actor (
  actor_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

говорит, что мы создаём таблицу actor с полями (колонками):

  • actor_id типа INTEGER
  • first_name и last_name типа VARCHAR(45)
  • last_update типа TIMESTAMP (как видите, тип DATETIME здесь называется по-другому, да и вообще в sqlite нет специального типа для дат — см. 2.2. Date and Time Datatype в документации про типы).

Внимание! Ключевое правило чтения программ.
Если вы не понимаете какой-то кусок кода, не паникуйте. Просто пропустите непонятные слова и прочтите то, что осталось.
Код написан почти на английском, так к нему и относитесь. Читайте программу как предложение на человеческом языке.
Если вы не понимаете 80% написанного, ничего страшного. Возможно, что основные мысли заключены в остальных 20% — тех, что проще.
Если вы прочитали кусочек кода, но всё равно не знаете, что он обозначает, попытайтесь угадать его смысл. Зачастую вы угадаете правильно.
Полезно взять код, запустить его и посмотреть на результат. Немного поменять, посмотреть как изменился результат. Полезно бывает удалить части, которые кажутся лишними и оставить только «скелет» программы.
Не бойтесь экспериментировать!

Модификаторы колонок

Помимо типов тут указаны модификаторы и ограничения колонок. Например, NOT NULL говорит, что в этих колонках не может быть пустоты. Что логично: актёр без имени — это явно какая-то недоработка.

Модификатор DEFAULT CURRENT_TIMESTAMP говорит, что если last_update не указан, то следует просто записать текущее время.

PRIMARY KEY — говорит, что колонка является первичным ключом. Это означает, грубо говоря, что эта колонка используется, чтобы перенумеровать строки уникальными значениями.

А AUTOINCREMENT говорит, что если вы попытались добавить новую строку в таблицу актёров, но не указали actor_id, то будет автоматически выбран ещё незанятый идентификатор. Как правило, следующий по счёту за последним заполненным. Отсюда и слово «инкремент», обозначающее «увеличить на 1».

Можно взять как правило: в каждой таблице должна быть колонка id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL. Технически это не обязательно, но почти всегда полезно. Не стоит экономить на спичках. Чаще всего колонка называется просто id (от identifier, идентификатор), хотя иногда к ней добавляют в качестве префикса имя таблицы, как сделано в нашем примере: author_id.

Обратите ещё внимание на то, что названия таблиц и полей (actor, last_name) пишутся строчными буквами, а ключевые слова типа CREATE TABLE или INTEGER пишутся заглавными. Это не строгое правило, но рекомендуется делать так, чтобы в запросе было проще увидеть важные части.

Добавление данных

Второй файл, sqlite-sakila-insert-data.sql, выглядит более однотипно. Сначала в нём идёт несколько команд вида DELETE FROM actor; — это означает удалить все строки из таблицы actor. Сама таблица в базе данных при этом остаётся, с теми же колонками. Просто теперь она пустая, без строк.

Дальше идут тысячи команд вида:

INSERT INTO actor
  (actor_id,first_name,last_name,last_update)
VALUES
  ('1','PENELOPE','GUINESS','2006-02-15 04:34:33.000') ;

Эта команда означает, что в таблицу actors надо добавить строку с actor_id = '1', first_name = 'PENELOPE' итд.

Вместо сотен одинаковых команд мы могли бы воспользоваться «пакетным добавлением» (bulk insert), которое позволяет добавить сразу несколько строк в рамках одной команды:

INSERT INTO actor (actor_id,first_name,last_name,last_update)
VALUES
  ('1','PENELOPE','GUINESS','2006-02-15 04:34:33.000'),
  ('2','NICK','WAHLBERG','2006-02-15 04:34:33.000'),
  ('3','ED','CHASE','2006-02-15 04:34:33.000') ;

Указание порядка колонок

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

Технически, скобку с порядком полей (actor_id,first_name,last_name,last_update) можно было бы вовсе опустить:

INSERT INTO actor VALUES ('1','PENELOPE','GUINESS','2006-02-15 04:34:33.000') ;

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

  • Во-первых, это приведёт к ошибкам, если схема таблицы поменяется. Например, будет добавлена колонка в середину таблицы. Защита от дурака это хорошо и правильно, все программисты время от времени ошибаются. И лучше пусть скрипт упадёт с ошибкой, чем случайно перезапишет все данные в вашей базе ;-)

  • Во-вторых, такой многословный синтаксис позволяет указывать не все поля. Поля, у которых значение по умолчанию не задано, будут равны значению по-умолчанию (указанному в схеме таблицы) или NULL, если дефолтное значение не задано.

Дефолтные значения и автоинкремент

То, что вы можете указывать не все поля, не просто экономит силы, а иногда ещё и необходимо. Представьте, что вы хотите добавить в БД нового актёра. Если вы будете указывать все поля, вам придётся указать actor_id. Но вы ведь заранее не знаете, какой actor_id свободен. И даже если вы сможете вычислить ближайший свободный номер, может получиться так, что его займут раньше вас. Но если вы вообще не будете указывать actor_id, автоинкремент всё сделает за вас!

Все поля, у которых указано значение по дефолту, также будут заполнены. Так команда INSERT INTO actor (first_name, last_name) VALUES ('Oleg', 'Yankovskiy'); заполнит не только actor_id, но и в last_update запишет текущую временну́ю метку.

Ограничение NOT NULL

Если у колонки есть ограничение NOT NULL, но вы не указываете её значение, и дефолт тоже не указан, то команда упадёт с ошибкой «NOT NULL constraint failed» и укажет, в каком поле произошла ошибка. СУБД не позволит вам внести в таблицу некорректные данные.

Подчеркну ещё раз: ограничения — это важно! Не полагайтесь на себя, запрещайте некорректные данные прямо в схеме. Это сэкономит вам много нервных клеток, а вашему работодателю — много денег. Если поле не должно быть пустым, пишите NOT NULL. Если в колонке значения должны быть уникальными, пишите UNIQUE. Чуть позднее мы ещё поговорим про ограничение FOREIGN KEY.

SQLite Browser

Наконец запустим SQLite Browser и посмотрим на нашу базу в чуть более человекочитаемом виде. Откройте файл базы данных sakila.db. У вас появятся вкладки:

  • Database Structure. Здесь вы можете посмотреть, какие таблицы есть в базе и как устроена схема.
  • Browse Data. Все данные отображаются здесь. Вы выбираете название таблицы и видите строки. При необходимости можете их отфильтровать по какому-то критерию.
  • Edit Pragmas. Просто забудьте, вам это не нужно.
  • Execute SQL. В этой вкладке вы можете ввести команду на языке SQL и выполнить её.

Посмотрите теперь на таблицу film во вкладке Browse Data. Введите фильтр на длительность фильма, чтобы найти все фильмы длиной ровно 120 минут.

Научимся теперь то же самое делать при помощи SQL.

SQL

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

SELECT-запросы

Самый простой запрос, который может быть — запросить все фильмы в базе:

SELECT * FROM film;

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

Теперь чуть усложним запрос и выцепим из таблицы только две колонки: название и длительность фильма:

SELECT title, length FROM film;

Следующим пунктом мы собирались найти двухчасовые фильмы. Для этого нам придётся добавить блок условий (WHERE clause):

SELECT title, length FROM film WHERE length = 120;

Обратите внимание, что знак равенства в условии одиночный, хотя в большинстве языков программирования принято писать сравнение при помощи двойного равенства. В языках программирования обычно приходится отделять присваивание от сравнения, но в SQL такой дилеммы нет. В контексте блока условий знак равенства никогда не присваивает значения, только сравнивает.

Теперь посмотрим на комбинированные условия. Оставим среди этих фильмов только те, что с рейтингом PG-13 или R:

SELECT * FROM film WHERE (length = 120) AND ((rating = 'PG-13') OR (rating = 'R'));

Часть скобок в условиях лишняя, но если вы, как я, боитесь запутаться с приоритетом и ассоциативностью операторов, лучше писать скобки явно.

Наконец найдём среди этих фильмов два самых дорогих (с наибольшей rental_rate). Для этого нам нужно упорядочить строки по этой колонке (ORDER BY) и ограничиться (LIMIT) первыми двумя строками. По-умолчанию сортировка идёт в порядке возрастания (ASC — ascending), но чтобы первые строки были с максимальной, а не минимальной стоимостью, нужно отсортировать строки по убыванию (DESC - descending).

SELECT * FROM film
WHERE (length = 120) AND ((rating = 'PG-13') OR (rating = 'R'))
ORDER BY rental_rate DESC
LIMIT 2;

Вот мы и разобрались с основами SQL-запросов. Да, всё настолько просто.

CRUD и четыре основных типа запросов

В мире веб-программирования распространена аббревиатура CRUD (Create-Read-Update-Delete). Она символизирует, что типичное веб-приложение с каждым ресурсом умеет производить четыре действия: создать ресурс, посмотреть на него, обновить или удалить. И эти же действия отображаются в базе данных.

Например, когда вы отправляете комментарий, вы создаёте новую строку таблицы. Когда редактируете комментарий, вы обновляете строку, удаление комментария — это удаление строки из таблицы, а просмотр комментария — это запрос соответствующей строки в таблице.

Соответственно в SQL помимо запросов, изменяющих схему (типа CREATE TABLE) также есть четыре основных типа запросов:

  • CREATE — INSERT INTO table (field_1, field_2) VALUES (value_1, value_2);
  • READ — SELECT fields FROM table WHERE condition;
  • UPDATE — UPDATE table SET field = new_value WHERE condition;
  • DELETE — DELETE FROM table WHERE condition;

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

UPDATE

Например, повысим рейтинги всех фильмов с G до PG-13:

UPDATE film SET rating="PG-13" WHERE rating="G";

Если вы хотите обновить одну конкретную строку, придётся написать условие, которое отфильтрует все строки, кроме целевой. Для этого идеально подходит фильтр по первичному ключу. Например, обновим рейтинг фильма под номером 42:

UPDATE film SET rating="R" WHERE film_id = 42;

Обратите внимание, что знак = в блоке условий обозначает проверку условия, а в SET-блоке отвечает за присваивание новых значений.

DELETE

Запросы на удаление пишутся по тем же правилам. Например, удалим недавно добавленного Олега Янковского из таблицы актёров:

DELETE FROM actor WHERE first_name = 'Oleg' and last_name = 'Yankovskiy';

Ограничения, мешающие выполнению запросов

Попробуем теперь удалить актрису под номером 1.

DELETE FROM actor WHERE actor_id = 1;

… и у нас ничего не получится. При выполнении запроса случится ошибка «FOREIGN KEY constraint failed». Что произошло? Оказывается, что мы не можем удалить актёра, на которого ссылается другая запись.

Вновь заглянем в схему и увидим, что есть таблица film_actor, которая говорит, что у неё есть ограничение (CONSTRAINT) на внешний ключ (FOREIGN KEY), ссылающийся (REFERENCES) на поле actor_id таблицы actor:

CREATE TABLE film_actor (
  actor_id INT NOT NULL,
  film_id  INT NOT NULL,
  last_update TIMESTAMP NOT NULL,
  PRIMARY KEY  (actor_id,film_id),
  CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE
);

Внешний ключ — это просто ссылка на другую строку (как правило в другой таблице). Таблица film_actor хранит сразу две внешние ссылки: на фильмы и на актёров. В таблице film_actor приведено почти два десятка фильмов, в которых играла актриса PENELOPE GUINESS (id = 1), проверим их:

SELECT * FROM film_actor WHERE actor_id = 1;

Представим теперь, что было бы, если бы БД позволила удалить эту актрису. Тогда оказалось бы, что таблица film_actor целых 19 раз ссылается на строку, которой нет. Это аналогично тому, что вы кликаете на ссылку в сети, а вам говорят, что такой страницы нет. Или вовсе перекидывают на какую-то другую страницу, которая заняла место старой. То же самое могло бы приключиться и в базе данных.

Ограничение на внешние ключи помогает поддерживать ссылочную целостность. Если удаляется актёр, в норме вслед за ним каскадом должны быть удалены и все другие записи, которые на него прямо или косвенно ссылались.

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