Учебник по алготрейдингуРазделы
Программирование на MQL5 для трейдеровРасширенные средства языкаБаза данных SQLiteОсновы SQL 
Скачать одним файлом:
Книга по алготрейдингу в PDF
Книга по алготрейдингу в CHM

Основы SQL

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

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

  • создание и удаление таблиц, а также модификация их схем, то есть описаний столбцов, с указанием типов, имен и ограничений;
  • создание (добавление), чтение, редактирование и удаление записей в таблицах — данные операции часто обозначают общей аббревиатурой CRUD (Create, Read, Update, Delete);
  • построение запросов для выборки записей из одной или комбинации нескольких таблиц по сложным условиям;
  • оптимизация алгоритмов за счет построения индексов по избранным столбцам, использования представлений (view), заключения пакетных действий в транзакции, объявления триггеров обработки событий и других инструментов продвинутого уровня.

В базах данных SQL все эти действия выполняются с помощью зарезервированных SQL-команд (или иначе операторов). В силу специфики интеграции с MQL5, часть действий выполняется встроенными функциями MQL5. Например, открытие, применение или отмена транзакции выполняется тройкой DatabaseTransaction-функций, хотя в стандарте SQL (и в публичной реализации SQLite) имеются соответствующие SQL-команды (BEGIN TRANSACTION, COMMIT, ROLLBACK).

Большинство SQL-команд доступно и в MQL-программах: они передаются в исполняющий "движок" SQLite как строковые параметры функций DatabaseExecute или DatabasePrepare. Разница между этими двумя вариантами заключается в нескольких нюансах.

DatabasePrepare позволяет подготовить запрос для его последующего массового циклического исполнения, причем на каждой итерации — с различными значениями параметров (сами параметры, то есть их имена в запросе, — одни и те же). Кроме того, подобные подготовленные запросы предоставляют механизм чтения результатов с помощью DatabaseRead и DatabaseReadBind. То есть с их помощью можно "пролистать" набор отобранных записей.

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

В последующем нам часто придется оперировать несколькими базовыми понятиями.

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

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

Индекс — служебная структура данных (так называемое сбалансированное дерево, B-tree) для быстрого поиска записей по значениям предопределенных полей (свойств) или их комбинаций.

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

Приведем краткий список наиболее востребованных SQL-операторов и выполняемые ими действия:

  • CREATE — создаёт объект базы данных (таблицу, представление, индекс, триггер);
  • ALTER — изменяет объект (таблицу);
  • DROP — удаляет объект (таблицу, представление, индекс, триггер);
  • SELECT — выбирает записи или вычисляет значения, удовлетворяющие заданным условиям;
  • INSERT — добавляет новые данные (одну или набор записей);
  • UPDATE — изменяет существующие записи;
  • DELETE — удаляет записи из таблицы;

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

Каждый оператор может располагаться на нескольких строках (переводы строк, так же как и лишние пробелы игнорируются). При необходимости можно отправить в SQLite сразу несколько команд — тогда после каждой команды следует использовать символ завершения команды ';' (точка с запятой).

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

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

CREATE TABLE имя_таблицы
   ( имя_столбца тип [ ограничения ...] [, имя_столбца тип [ ограничения ...] ...] );

Что такое ограничения, и какие типы бывают в SQL, мы разберем в следующем разделе. А пока наглядный пример (с разными типами и опциями):

CREATE TABLE IF NOT EXISTS example_table
   (id INTEGER PRIMARY KEY,
    name TEXT,
    timestamp INTEGER DEFAULT CURRENT_STAMP,
    income REAL,
    data BLOB);

Синтаксис создания индекса таков:

CREATE [ UNIQUE ] INDEX имя_индекса
   ON имя_таблицы ( имя_столбца [, имя_столбца ...] );

Имеющиеся индексы автоматически используются при запросах с условиями отбора по соответствующим столбцам. Без индексов процесс происходит медленнее.

Удаление таблицы (вместе с данными, если в неё что-то было записано) довольно просто:

DROP TABLE имя_таблицы;

Вставить данные в таблицу можно следующим образом:

INSERT INTO имя_таблицы [ ( имя_столбца [, имя_столбца...] ) ]
   VALUES ( значение [, значение ...] );

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

INSERT INTO example_table (name, income) VALUES ('Morning Flat Breakout', 1000);

Обратите внимание, строковые литералы помещаются в SQL в одинарные кавычки.

Если имена столбцов в INSERT-инструкции опущены, предполагается, что после ключевого слова VALUES указаны значения для всех столбцов таблицы, причем именно в том порядке, в котором они описаны в таблице.

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

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

SELECT имя_столбца [, имя_столбца ...] FROM имя_таблицы [ WHERE условие ];

Если нужно вернуть каждую подходящую запись целиком (все столбцы), используем нотацию со звездочкой:

SELECT * FROM имя_таблицы [ WHERE условие ];

Когда условия нет, система возвращает все записи таблицы.

В качестве условия можно подставить логическое выражение, включающее имена столбцов и различные операторы сравнения, а также встроенные функции SQL и результаты вложенного запроса SELECT (подобные запросы записывается в круглых скобках). Операторы сравнения включают:

  • AND — логическое И;
  • OR — логическое ИЛИ;
  • IN — значение из списка;
  • NOT IN — значение вне списка;
  • BETWEEN — значение в диапазоне;
  • LIKE — похожее по написанию на шаблон со специальными символами подстановки ('%', '_');
  • EXISTS — проверка на непустоту результатов вложенного запроса.

Например, выборка имен записей с доходом не меньше 1000 и не старше одного года (с предварительным округлением до месяца):

SELECT name FROM example_table
  WHERE income >= 1000 AND timestamp > datetime('now', 'start of month', '-1 year');

Дополнительно выборку можно отсортировать по возрастанию или убыванию (ORDER BY), сгруппировать по признакам (GROUP BY) и отфильтровать группы (HAVING), а также ограничить в ней количество записей (LIMIT, OFFSET). Для каждой группы можно вернуть значение какой-либо агрегатной функции, в частности, COUNT, SUM, MIN, MAX, AVG, посчитанной на всех записях группы.

SELECT [ DISTINCT ] имя_столбца [, имя_столбца ...] FROM имя_таблицы
   [ WHERE условие ]
   [ ORDER BY имя_столбца [ ASC | DESC ]
      [ LIMIT количество OFFSET смещение_от_начала ] ]
   [ GROUP BY имя_столбца [ HAVING условие ] ];

Необязательное ключевое слово DISTINCT позволяет убрать дубликаты (если они обнаружатся в результатах по текущим условиям отбора). Оно имеет смысл только в отсутствии группировки.

LIMIT даст воспроизводимые результаты только при наличии сортировки.

При необходимости выборку SELECT можно делать не из одной таблицы, а из нескольких, комбинируя их по требуемому сочетанию полей. Для этого используется ключевое словое JOIN.

SELECT [...] FROM имя_таблицы_1
   [ INNER | OUTER | CROSS ] JOIN имя_таблицы_2
   ON логическое_условие

или

SELECT [...] FROM имя_таблицы_1
   [ INNER | OUTER | CROSS ]opt JOIN имя_таблицы_2
   USING ( имя_общего_столбца [, имя_общего_столбца ...] )

В принципе, SQLite поддерживает три разновидности JOIN — INNER JOIN, OUTER JOIN и CROSS JOIN, — но подробности мы оставим для самостоятельного изучения: в книге общее представление о них можно будет составить из примеров.

С помощью JOIN можно, в частности, построить все сочетания записей одной таблицы с записями из другой или сопоставить сделки из таблицы сделок (допустим, под названием deals) со сделками этой же таблицы по принципу совпадения идентификаторов позиции, но чтобы направление сделок (вход в рынок/выход из рынка) было противоположным, что в результате даст виртуальную таблицу трейдов.

SELECT         // перечисляем столбцы таблицы результатов с алиасами (после 'as')
   d1.time as time_in, d1.position_id as position, d1.type as type, // таблица d1
   d1.volume as volume, d1.symbol as symbol, d1.price as price_in,
   d2.time as time_out, d2.price as price_out,                      // таблица d2
   d2.swap as swap, d2.profit as profit,
   d1.commission + d2.commission as commission                      // комбинация
   FROM deals d1 INNER JOIN deals d2      // d1 и d2 - алиасы одной таблицы deals
   ON d1.position_id = d2.position_id     // условие слияния по позиции
   WHERE d1.entry = 0 AND d2.entry = 1    // условие отбора "вход/выход"

Это SQL-запрос из справки по MQL5, где примеры JOIN имеются в описаниях функций DatabaseExecute, DatabasePrepare.

Основополагающее свойство SELECT в том, что он всегда возвращает результаты в вызывающую программу, в отличие от других запросов типа CREATE, INSERT и т.д. Правда, с версии SQLite 3.35 для операторов INSERT, UPDATE и DELETE также появилась возможность, при необходимости, вернуть значения с помощью дополнительного ключевого слова RETURNING. Например,

INSERT INTO example_table (name, income) VALUES ('Morning Flat Breakout', 1000)
   RETURNING id;

В любом случае, в MQL5 результаты запроса доступы через группу DatabaseColumn-функций, DatabaseRead, DatabaseReadBind.  

Кроме того, SELECT позволяет рассчитывать результаты выражений и возвращать их сами по себе или комбинировать с результатами из таблиц. Выражения могут включать большинство операторов, знакомых нам по выражениям MQL5, а также встроенные функции SQL. С их полным перечнем следует ознакомиться в документации SQLite. Вот, например, как можно узнать текущую версию сборки SQLite в вашем экземпляре терминала и редактора, что может быть важно для выяснения того, какие опции доступны.

SELECT sqlite_version();

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

Среди функций имеется несколько востребованных статистических и математических.

Редактировать записи следует оператором UPDATE.

UPDATE имя_таблицы SET имя_столбца = значение [, имя_столбца = значение ...] 
  WHERE условие;

Синтаксис команды удаления записей таков:

DELETE FROM имя_таблицы WHERE условие;