Практикум / Практическое задание на SQL
Практикум / Практическое задание на SQL

Практическое задание на SQL

Практическое задание на SQL

В данном практическом задании демонстрируется поддержка SQL, добавленная в серии Tarantool’а 2.x. Задание можно выполнять двумя способами:

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

You will encounter all the functionality that you’d encounter in an «SQL-101» course.

Начнем с первой таблицы и запросов типа SELECT

Initialize

Tarantool выполняет запросы в качестве клиента. Запустите Tarantool и при желании введите запрос конфигурации, например.

tarantool> box.cfg{}

До версии Tarantool’а 2.0 перед выполнением любой операции с базой данных нужно было вызвать box.cfg{...}. Теперь можно сразу же начинать работу с базой данных. В таком случае Tarantool запускает модуль базы данных и применяет настройки, используемые по умолчанию.

set

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

Здесь упомянем: язык по умолчанию – SQL, а операторы заканчиваются точкой с запятой.

tarantool> \set language sql
tarantool> \set delimiter ;

CREATE, INSERT, UPDATE, SELECT

Начните с простых операторов 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 будет следующим:

tarantool> SELECT * FROM table1 WHERE column1 = 1;
---
- - [1, 'B']
...

Reality check: actually the result will include include initial fields called «metadata», the names and data types of each column. For all SELECT examples we show only the result rows without showing the metadata.

CREATE TABLE

Here is CREATE TABLE with more details:

  • Есть несколько столбцов с разными типами данных.
  • Есть первичный ключ PRIMARY KEY (уникальный и ненулевой) для двух столбцов.
CREATE TABLE table2 (column1 INTEGER,
                     column2 VARCHAR(100),
                     column3 SCALAR,
                     column4 FLOAT,
                     PRIMARY KEY (column1, column2));

The result will be: «rowcount: 1» (no error).

INSERT

Попытайтесь вставить 5 строк в таблицу:

  • В столбцах INTEGER и FLOAT будут числа.
  • В столбцах 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 (1, 'AB', X'A5', -5.5);
INSERT INTO table2 VALUES (2, 'AB', X'2020', 12.34567);
INSERT INTO table2 VALUES (-1000, '', X'', 0.0);

The result will be:

  • Третья операция вставки INSERT завершится ошибкой по причине нарушения ограничений по первичному ключу (1, 'AB' – повтор).
  • Остальные четыре операции вставки INSERT будут выполнены.

SELECT с условием ORDER BY

Осуществите выборку 4 строк в таблице в порядке убывания по column2, затем в порядке возрастания по column4 (где значения column2 одинаковы).

«*» расшифровывается как «все столбцы».

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

The result will be:

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

SELECT с условиями WHERE

Осуществите выборку некоторых вставленных данных:

  • В первом операторе используется оператор сравнения LIKE, который требует, чтобы «первый символ был „A“, следующие символы могут быть чем угодно».
  • Во втором операторе используются логические операторы и круглые скобки, поэтому выражения с AND должны быть правдой, или выражение OR должно быть правдой. Обратите внимание, что столбцы не должны быть проиндексированы.
SELECT column1, column2, column1 * column4 FROM table2 WHERE column2
LIKE 'A%';
SELECT column1, column2, column3, column4 FROM table2
    WHERE (column1 < 2 AND column4 < 10)
    OR column3 = X'2020';

Получим следующие результаты:

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

and

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

SELECT с GROUP BY и агрегация

Осуществите выборку с группировкой данных.

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

SELECT column2, SUM(column4), COUNT(column4), AVG(column4)
FROM table2
GROUP BY column2;

The result will be:

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

Усложненные действия и сложные операции выборки SELECT

NULL

Вставьте еще строки с нулевыми значениями 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);

The result will be:

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

Индексы

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

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

CREATE UNIQUE INDEX i ON table2 (column4);

The result will be: «rowcount: 1» (no error).

Создание таблицы с собственным подмножеством значений

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

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

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

The result will be:

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

SELECT с подзапросом

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

Здесь мы найдем все строки из таблицы table2, где значения в столбцах (column1, column2) не обнаруживаются в таблице table3.

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

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

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

SELECT с объединением

Объединение – это сочетание двух таблиц. В Tarantool’е есть несколько способов объединений: «декартово произведение», «левое внешнее соединение» и т.д.

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

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

The result will be:

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

Ограничения, влияющие на обновления

CREATE TABLE с условием CHECK

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

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

Результат: как и должно быть, вставка завершается ошибкой с сообщением «error: 'CHECK constraint failed: TABLE4'» (ошибка по ограничению).

CREATE TABLE с условием FOREIGN KEY

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

Когда мы создавали таблицу table2, то указали, что столбцы с первичным ключом «primary key» – (column1, column2).

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, как и должно быть, завершается ошибкой с сообщением «error: FOREIGN KEY constraint failed» (ошибка по ограничению).

UPDATE

Ранее были вставлены с помощью INSERT следующие значения в столбец 4 таблицы 2 table2 column4: {0, NULL, NULL, 5.5, 10000, 12.34567}. Прибавим 5 к каждому значению, кроме 0. (По правилам SQL-арифметики, при сложении 5 и NULL результатом будет NULL.) Затем осуществим выборку SELECT, чтобы посмотреть, как выглядит столбец column4.

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

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

DELETE

Итак, после запросов вставки INSERT имеем 6 строк в таблице table2:

- - [-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 table2;

The result will be:

  • Первый запрос DELETE вызовет сообщение с ошибкой, поскольку (помните?) есть ограничение по внешнему ключу.
  • Второй запрос DELETE будет выполнен.
  • Запрос выборки SELECT показывает, что осталось лишь 5 строк.

ALTER TABLE с условием FOREIGN KEY

Теперь создадим еще одно «ограничение»: чтобы в таблице 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.

Triggers

Идея триггера такова: если происходит изменение (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 ROW
BEGIN 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 table2;

The result will be:

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

Операции с числами

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

Здесь мы покажем:

  • оператор << для сдвига влево и
  • оператор % для модуля числа.
SELECT column1, column1 << 1, column1 << 2, column1 % 2 FROM table2;

The result will be:

- - [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 FLOAT,
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 t6 ORDER BY column2;

Результат будет:

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

Представления

Представление – это «виртуальная таблица», строки которой не находятся в базе данных физически, их значения вычисляются из других таблиц.

Здесь создадим представление v3 на основе таблицы table3, а затем осуществим выборку из него.

CREATE VIEW v3 AS SELECT SUBSTR(column2,1,2), column4 FROM t6 WHERE
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 t6 WHERE column4
             >= 0)
SELECT * FROM cte;

Результат: тот же, что и ранее по CREATE VIEW:

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

VALUES

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

Здесь используем оба типа запросов.

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

Результат будет следующим:

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

Метаданные

Какие объекты базы данных мы уже создали? Можно узнать о:

  • таблицах с помощью SELECT * FROM "_space";
  • индексах с помощью SELECT * FROM "_index";
  • триггерах с помощью SELECT * FROM "_trigger"; (Эти имена знакомы старым пользователям Tarantool’а, потому что на самом деле мы выбираем из системных спейсов NoSQL.)

Здесь выбираем из _space.

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

В результате (мы знаем, что получим строку, потому что сами создали table3 ранее):

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

Вызов из языка реализации, чтобы создать большую таблицу

box.execute()

Теперь мы изменим настройки так, чтобы консоль принимала операторы, написанные на Lua, вместо операторов, написанных на SQL. (В следующей версии в клиентах Tarantool’а будет больше способов переключения языков.)

This doesn’t mean we have left the SQL world though, because we can invoke SQL statements using a Lua function: box.execute(string).

Здесь мы переключим языки и осуществим выборку содержимого таблицы table3. Эти запросы следует вводить по отдельности.

tarantool> \set language lua
tarantool> box.execute([[SELECT * FROM table3;]]);

Покажем запросы и результаты вместе:

tarantool> \set language lua
---
...
tarantool> box.execute([[SELECT * FROM table3;]]);
---
- - [-1000, '']
  - [0, '!!!']
  - [0, '!!@']
  - [1, 'AB']
  - [1, 'CD']
...

Создание таблицы с миллионом строк

Итак, мы продемонстрировали множество функций SQL, но масштабируется ли он? Чтобы ответить на этот вопрос, создадим большую таблицу.

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

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_string
end;

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)
   end
end;
start_time = os.clock();
main_function();
end_time = os.clock();
'insert done in ' .. end_time - start_time .. ' seconds';

Результат: теперь у вас есть таблица с миллионом строк с сообщением «insert done in 88.570578 seconds» (выполнено за 88,570578 секунд).

Выборка из таблицы с миллионом строк

Теперь можно экспериментировать с большим объемом данных – давайте посмотрим, сколько времени занимает SELECT.

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

Второй сделанный запрос не пойдет через индекс, потому что для s2 мы не сказали CREATE INDEX xxxx ON tester (s2);.

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

Результат будет:

  • первый запрос будет выполнен сразу же,
  • второй запрос будет заметно медленнее, но все равно займет доли секунды.

Очистка и выход

Итак, мы закончили: мы продемонстрировали работу подмножества SQL в Tarantool’е 2.1.

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

tarantool> \set language sql
tarantool> DROP TABLE tester;
tarantool> DROP TABLE table1;
tarantool> DROP VIEW v3;
tarantool> DROP TRIGGER tr;
tarantool> DROP TABLE table5;
tarantool> DROP TABLE table4;
tarantool> DROP TABLE table3;
tarantool> DROP TABLE table2;
tarantool> DROP TABLE t6;
tarantool> \set language lua
tarantool> os.exit();