Руководство по SQL

Это руководство демонстрирует поддержку SQL в Tarantool. В нем рассматривается функциональность, с которой можно ознакомиться на базовом курсе по SQL.

Перед началом этого руководства:

1. Установите утилиту tt CLI.

2. Запустите экземпляр Tarantool в интерактивном режиме с помощью команды tt run -i:

    
   $ tt run -iTarantool 3.0.0-0-g6ba34da7f8type 'help' for interactive helptarantool>

3. Инициализируйте экземпляр и переключите язык ввода на SQL:

    
   tarantool> box.cfg{}tarantool> \set language sqltarantool>  \set delimiter ;

Теперь у вас запущен экземпляр Tarantool, принимающий ввод на SQL.

Для начала введите следующие SQL-запросы:

 
CREATE TABLE table1 (column1 INTEGER PRIMARY KEY, column2 VARCHAR(100));INSERT INTO table1 VALUES (1, 'A');UPDATE table1 SET column2 = 'B';SELECT * FROM table1 WHERE column1 = 1;

Результат выполнения оператора SELECT выглядит так:

 
sql_tutorial:instance001> SELECT * FROM table1 WHERE column1 = 1;---- metadata:  - name: COLUMN1    type: integer  - name: COLUMN2    type: string  rows:  - [1, 'B']...

Результат включает:

• метаданные: имена и типы данных каждого столбца
• строки результата

Для краткости в результатах запросов в этом руководстве метаданные опускаются. Показаны только строки результата.

Ниже приведены дополнительные сведения о CREATE TABLE:

• Создается несколько столбцов с разными типами данных.
• Для двух столбцов задан PRIMARY KEY (уникальный и не допускающий значения NULL).

Создадим еще одну таблицу:

 
CREATE TABLE table2 (column1 INTEGER,                     column2 VARCHAR(100),                     column3 SCALAR,                     column4 DOUBLE,                     PRIMARY KEY (column1, column2));

Результат: row_count: 1.

Добавим четыре строки в таблицу (table2):

• В столбцы типа INTEGER и DOUBLE записываются числа
• В столбцы типа VARCHAR и SCALAR записываются строки (строки SCALAR представлены в шестнадцатеричном формате)

 
INSERT INTO table2 VALUES (1, 'AB', X'4142', 5.5);INSERT INTO table2 VALUES (1, 'CD', X'2020', 1E4);INSERT INTO table2 VALUES (2, 'AB', X'2020', 12.34567);INSERT INTO table2 VALUES (-1000, '', X'', 0.0);

Затем попробуем добавить еще одну строку:

 
INSERT INTO table2 VALUES (1, 'AB', X'A5', -5.5);

Этот INSERT завершается ошибкой из-за нарушения ограничения первичного ключа: строка с первичным ключом 1, 'AB' уже существует.

Последовательное сканирование — это просмотр всех строк таблицы вместо использования индексов. В Tarantool SQL-запросы SELECT, выполняющие последовательное сканирование, по умолчанию запрещены. Например, следующий запрос приводит к ошибке Scanning is not allowed for 'table2':

 
SELECT * FROM table2;

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

 
SELECT * FROM SEQSCAN table2;

Попробуйте выполнить следующие запросы, использующие проиндексированный столбец column1 в фильтрах:

 
SELECT * FROM table2 WHERE column1 = 1;SELECT * FROM table2 WHERE column1 + 1 = 2;

Результат:

• Первый запрос возвращает строки:

   
  - [1, 'AB', 'AB', 10.5]- [1, 'CD', '  ', 10005]

• Второй запрос завершается ошибкой Scanning is not allowed for 'TABLE2'. Хотя столбец column1 проиндексирован, выражение column1 + 1 не вычисляется из индекса, поэтому данный SELECT является запросом на сканирование.

Подробнее об использовании SEQSCAN см. в описании предложения SQL FROM.

Получим 4 строки из таблицы, отсортированные по убыванию column2, а затем (при совпадении значений column2) по возрастанию column4.

* — сокращение для «все столбцы».

 
SELECT * FROM SEQSCAN table2 ORDER BY column2 DESC, column4 ASC;

Результат:

 
- - [1, 'CD', '  ', 10000]  - [1, 'AB', 'AB', 5.5]  - [2, 'AB', '  ', 12.34567]  - [-1000, '', '', 0]

Выберем часть вставленных данных:

• Первый оператор использует оператор сравнения LIKE, требующий, чтобы «первый символ был 'A', а последующие — любыми».

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

 
SELECT column1, column2, column1 * column4 FROM SEQSCAN table2 WHERE column2LIKE 'A%';SELECT column1, column2, column3, column4 FROM SEQSCAN table2    WHERE (column1 < 2 AND column4 < 10)    OR column3 = X'2020';

Первый результат:

 
- - [1, 'AB', 5.5]  - [2, 'AB', 24.69134]

Второй результат:

 
- - [-1000, '', '', 0]  - [1, 'AB', 'AB', 5.5]  - [1, 'CD', '  ', 10000]  - [2, 'AB', '  ', 12.34567]

Выборка с группировкой.

Строки с одинаковыми значениями column2 группируются, и для column4 вычисляются агрегатные значения — сумма, количество, среднее.

 
SELECT column2, SUM(column4), COUNT(column4), AVG(column4)FROM SEQSCAN table2GROUP BY column2;

Результат:

 
- - ['', 0, 1, 0]  - ['AB', 17.84567, 2, 8.922835]  - ['CD', 10000, 1, 10000]

Вставьте строки, содержащие значения NULL.

NULL не то же самое, что nil в Lua; в SQL это значение обычно используется для неизвестных или неприменимых данных.

 
INSERT INTO table2 VALUES (1, NULL, X'4142', 5.5);INSERT INTO table2 VALUES (0, '!!@', NULL, NULL);INSERT INTO table2 VALUES (0, '!!!', X'00', NULL);

Результаты:

• Первый INSERT завершается ошибкой, так как NULL не допускается для столбца, определенного с предложением PRIMARY KEY.
• Остальные операторы INSERT выполняются успешно.

Создайте новый индекс для column4.

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

 
CREATE UNIQUE INDEX i ON table2 (column4);

Результат: rowcount: 1.

Создайте таблицу table3, содержащую подмножество столбцов table2 и подмножество строк table2.

Это можно сделать, объединив INSERT с SELECT. Затем выберите все данные из полученной таблицы.

 
CREATE TABLE table3 (column1 INTEGER, column2 VARCHAR(100), PRIMARY KEY(column2));INSERT INTO table3 SELECT column1, column2 FROM SEQSCAN table2 WHERE column1 <> 2;SELECT * FROM SEQSCAN table3;

Результат:

 
- - [-1000, '']  - [0, '!!!']  - [0, '!!@']  - [1, 'AB']  - [1, 'CD']

Подзапрос — это запрос внутри запроса.

Найдите все строки в table2, значения (column1, column2) которых отсутствуют в table3.

 
SELECT * FROM SEQSCAN table2 WHERE (column1, column2) NOT IN (SELECT column1,column2 FROM SEQSCAN table3);

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

 
- - [2, 'AB', '  ', 12.34567]

Соединение (join) — это комбинация двух таблиц. В Tarantool существует несколько способов их выполнения, например, "декартовы соединения" или "левые внешние соединения".

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

 
SELECT * FROM SEQSCAN table2, table3    WHERE table2.column1 = table3.column1 AND table2.column2 = table3.column2    ORDER BY table2.column4;

Результат:

 
- - [0, '!!!', "\0", null, 0, '!!!']  - [0, '!!@', null, null, 0, '!!@']  - [-1000, '', '', 0, -1000, '']  - [1, 'AB', 'AB', 5.5, 1, 'AB']  - [1, 'CD', ' ', 10000, 1, 'CD']

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

 
CREATE TABLE table4 (column1 INTEGER PRIMARY KEY, column2 INTEGER, CHECK(column2 <> 13));INSERT INTO table4 VALUES (12, 13);

Результат: вставка завершается ошибкой, как и ожидалось, с сообщением Check constraint 'ck_unnamed_TABLE4_1' failed for tuple.

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

 
CREATE TABLE table5 (column1 INTEGER, column2 VARCHAR(100),    PRIMARY KEY (column1),    FOREIGN KEY (column1, column2) REFERENCES table2 (column1, column2));INSERT INTO table5 VALUES (2,'AB');INSERT INTO table5 VALUES (3,'AB');

Результат:

• Первый оператор INSERT выполняется успешно, так как в table3 есть строка [2, 'AB', ' ', 12.34567].
• Второй оператор INSERT ожидаемо завершается ошибкой с сообщением Foreign key constraint ''fk_unnamed_TABLE5_1'' failed: foreign tuple was not found.

В результате предыдущих операторов INSERT в column4 таблицы table2 содержатся следующие значения: {0, NULL, NULL, 5.5, 10000, 12.34567}. Прибавьте 5 ко всем этим значениям, кроме 0. Прибавление 5 к NULL дает NULL, как того требуют правила арифметики SQL. Используйте SELECT, чтобы увидеть, что произошло с column4.

 
UPDATE table2 SET column4 = column4 + 5 WHERE column4 <> 0;SELECT column4 FROM SEQSCAN table2 ORDER BY column4;

Результат: {NULL, NULL, 0, 10.5, 17.34567, 10005}.

В результате предыдущих операторов INSERT в table2 содержится 6 строк:

 
- - [-1000, '', '', 0]  - [0, '!!!', "\0", null]  - [0, '!!@', null, null]  - [1, 'AB', 'AB', 10.5]  - [1, 'CD', '  ', 10005]  - [2, 'AB', '  ', 17.34567]

Попробуйте удалить последнюю и первую из этих строк:

 
DELETE FROM table2 WHERE column1 = 2;DELETE FROM table2 WHERE column1 = -1000;SELECT COUNT(column1) FROM SEQSCAN table2;

Результат:

• Первый оператор DELETE вызывает ошибку, так как существует ограничение внешнего ключа.
• Второй оператор DELETE выполняется успешно.
• Оператор SELECT показывает, что осталось 5 строк.

Создайте еще одно ограничение: в table1 не должно быть строк, содержащих значения, отсутствующие в table5. Это было невозможно при создании table1, так как на тот момент table5 еще не существовало. Добавить ограничения к существующим таблицам можно с помощью оператора ALTER TABLE.

 
ALTER TABLE table1 ADD CONSTRAINT c    FOREIGN KEY (column1) REFERENCES table5 (column1);DELETE FROM table1;ALTER TABLE table1 ADD CONSTRAINT c    FOREIGN KEY (column1) REFERENCES table5 (column1);

Результат: оператор ALTER TABLE завершается ошибкой при первом выполнении, так как в table1 есть строка, а для ADD CONSTRAINT требуется, чтобы таблица была пустой. После удаления строки оператор ALTER TABLE выполняется успешно. Теперь существует цепочка ссылок: от table1 к table5 и от table5 к table2.

Суть триггера: при изменении (INSERT, UPDATE или DELETE) выполняется дополнительное действие — возможно, еще один INSERT, UPDATE или DELETE.

Настройте следующий триггер: при обновлении table3 выполнять обновление table2. Укажите FOR EACH ROW, чтобы триггер сработал 5 раз (так как в table3 5 строк).

 
SELECT column4 FROM table2 WHERE column1 = 2;CREATE TRIGGER tr AFTER UPDATE ON table3 FOR EACH ROWBEGIN UPDATE table2 SET column4 = column4 + 1 WHERE column1 = 2; END;UPDATE table3 SET column2 = column2;SELECT column4 FROM table2 WHERE column1 = 2;

Результат:

• Первый SELECT показывает, что исходное значение column4 в table2, где column1 = 2, было: 17.34567.
• Второй SELECT возвращает:

 
- - [22.34567] ..

Строковые данные (обычно определяемые типами данных CHAR или VARCHAR) можно обрабатывать разными способами. Например:

• объединять строки с помощью оператора ||
• извлекать подстроки с помощью функции SUBSTR

 
SELECT column2, column2 \|\| column2, SUBSTR(column2, 2, 1) FROM SEQSCAN table2;

Результат:

 
- - ['!!!', '!!!!!!', '!']  - ['!!@', '!!@!!@', '!']  - ['AB', 'ABAB', 'B']  - ['CD', 'CDCD', 'D']  - ['AB', 'ABAB', 'B']

Числовые данные (обычно определяемые типами данных INTEGER или DOUBLE) также можно обрабатывать разными способами. Например:

• сдвиг влево с помощью оператора <<
• получение остатка от деления с помощью оператора %

 
SELECT column1, column1 << 1, column1 << 2, column1 % 2 FROM SEQSCAN table2;

Результат:

 
- - [0, 0, 0, 0]  - [0, 0, 0, 0]  - [1, 2, 4, 1]  - [1, 2, 4, 1]  - [2, 4, 8, 0]

Tarantool может обрабатывать:

• целые числа в любом месте диапазона 4-байтовых целых чисел
• числа с плавающей точкой в любом месте 8-байтового диапазона IEEE
• любые символы Unicode с кодировкой UTF-8 и выбором параметров сортировки

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

 
CREATE TABLE t6 (column1 INTEGER, column2 VARCHAR(10), column4 DOUBLE,PRIMARY KEY (column1));INSERT INTO t6 VALUES (-1234567890, 'АБВГД', 123456.123456);INSERT INTO t6 VALUES (+1234567890, 'GD', 1e30);INSERT INTO t6 VALUES (10, 'FADEW?', 0.000001);INSERT INTO t6 VALUES (5, 'ABCDEFG', NULL);SELECT column1 + 1, column2, column4 * 2 FROM SEQSCAN t6 ORDER BY column2;

Результат:

 
- - [6, 'ABCDEFG', null]  - [11, 'FADEW?', 2e-06]  - [1234567891, 'GD', 2e+30]  - [-1234567889, 'АБВГД', 246912.246912]

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

Создайте представление v3 на основе table3 и выполните из него выборку:

 
CREATE VIEW v3 AS SELECT SUBSTR(column2,1,2), column4 FROM SEQSCAN t6WHERE column4 >= 0;SELECT * FROM v3;

Результат:

 
- - ['АБ', 123456.123456]  - ['FA', 1e-06]  - ['GD', 1e+30]

Поместив WITH + SELECT перед SELECT, можно создать временное представление, которое существует на время выполнения запроса.

Создайте такое представление и выполните из него выборку:

 
WITH cte AS (             SELECT SUBSTR(column2,1,2), column4 FROM SEQSCAN t6             WHERE column4 >= 0)SELECT * FROM cte;

Результат такой же, как и при выполнении CREATE VIEW:

 
- - ['АБ', 123456.123456]  - ['FA', 1e-06]  - ['GD', 1e+30]

Tarantool, как и некоторые другие популярные СУБД, может обрабатывать запросы вида SELECT 55; (выборка без FROM). Tarantool также поддерживает более стандартный запрос VALUES (expression [, expression ...]);.

 
SELECT 55 * 55, 'The rain in Spain';VALUES (55 * 55, 'The rain in Spain');

Результат обоих запросов:

 
- - [3025, 'The rain in Spain']

Чтобы узнать внутреннюю структуру базы данных Tarantool с помощью SQL, выполните выборку из системных таблиц Tarantool _space, _index и _trigger:

 
SELECT * FROM SEQSCAN "_space";SELECT * FROM SEQSCAN "_index";SELECT * FROM SEQSCAN "_trigger";

На самом деле эти запросы выполняют выборку из "системных спейсов" NoSQL.

Выполните выборку из _space по имени таблицы:

 
SELECT "id", "name", "owner", "engine" FROM "_space" WHERE "name"='TABLE3';

Результат:

 
- - [517, 'TABLE3', 1, 'memtx']

SQL-запросы можно выполнять напрямую из кода Lua без переключения в режим ввода SQL.

Измените настройки так, чтобы консоль принимала инструкции на Lua, а не на SQL:

 
sql_tutorial:instance001> \set language lua

Вызывать SQL-запросы можно с помощью Lua-функции box.execute(string).

 
sql_tutorial:instance001> box.execute([[SELECT * FROM SEQSCAN table3;]]);

Результат:

 
- - [-1000, '']  - [0, '!!!']  - [0, '!!@']  - [1, 'AB']  - [1, 'CD']...

Чтобы оценить масштабируемость SQL в Tarantool, создайте таблицу большего размера.

Приведенный ниже код на Lua генерирует миллион строк со случайными данными и вставляет их в таблицу. Скопируйте этот код в консоль Tarantool и немного подождите:

 
box.execute("CREATE TABLE tester (s1 INT PRIMARY KEY, s2 VARCHAR(10))");function string_function()    local random_number    local random_string    random_string = ""    for x = 1, 10, 1 do        random_number = math.random(65, 90)        random_string = random_string .. string.char(random_number)    end    return random_stringend;function main_function()    local string_value, t, sql_statement    for i = 1, 1000000, 1 do        string_value = string_function()        sql_statement = "INSERT INTO tester VALUES (" .. i .. ",'" .. string_value .. "')"        box.execute(sql_statement)    endend;start_time = os.clock();main_function();end_time = os.clock();print('insert done in ' .. end_time - start_time .. ' seconds');

Результат: теперь у вас есть таблица с миллионом строк и сообщение insert done in 88.570578 seconds.

Проверим, как работает SELECT для таблицы с миллионом строк:

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

 
box.execute([[SELECT * FROM tester WHERE s1 = 73446;]]);box.execute([[SELECT * FROM SEQSCAN tester WHERE s2 LIKE'QFML%';]]);

Результат:

• первый запрос выполняется мгновенно
• второй запрос выполняется заметно медленнее

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

Эти инструкции нужно вводить по отдельности.

 
sql_tutorial:instance001> \set language sqlsql_tutorial:instance001> DROP TABLE tester;sql_tutorial:instance001> DROP TABLE table1;sql_tutorial:instance001> DROP VIEW v3;sql_tutorial:instance001> DROP TRIGGER tr;sql_tutorial:instance001> DROP TABLE table5;sql_tutorial:instance001> DROP TABLE table4;sql_tutorial:instance001> DROP TABLE table3;sql_tutorial:instance001> DROP TABLE table2;sql_tutorial:instance001> DROP TABLE t6;sql_tutorial:instance001> \set language luasql_tutorial:instance001> os.exit();