# SQL

В документе описан поддерживаемый NitrosBase диалект SQL. Развитие поддержки SQL см. раздел SQL Roadmap. В примерах используется демонстрационная база данных, описанная в разделе «Демонстрационная база данных».

# CREATE TABLE

Синтаксис

CREATE TABLE [IF NOT EXISTS] tbl_name 
  ( create_definition [, create_definition] ... )
 
create_definition:
  col_name data_type 
    [NOT NULL | NULL] [DEFAULT literal]
    [PRIMARY KEY]
  | [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name) REFERENCES tbl_name 

Пример

CREATE TABLE person (
  p_id varchar PRIMARY KEY,
  name varchar, 
  lastname varchar, 
  age bigint, 
  city varchar, 
  income int, 
  dbl double, 
  dtime datetime
);

Если имя таблицы начинается со знака '#', то создается временная таблица

Пример

CREATE TABLE #tempt1 (
  p_id varchar PRIMARY KEY,
  name varchar, 
  lastname varchar, 
  age bigint, 
  city varchar, 
  income int, 
  dbl double, 
  dtime datetime
);

Ограничения текущей версии NitrosBase:

  • первичный ключ не может быть составным;

# CREATE TABLE AS EDGE

Создание специальной таблицы для представления графовых ссылок (связей типа «многие-ко-многим»). Может интерпретироваться как промежуточная таблица в обычной реляционной базе данных, имеющая два стандартных поля: fromid и toid.

Синтаксис

CREATE TABLE link_name AS EDGE from_tablename to_tablename; 

Пример

CREATE TABLE owner AS EDGE car person;

Пример использования таблицы в запросе SELECT:

SELECT p.id, name, lastname, age, c.id, c.model 
FROM person p 
  JOIN owner o ON p.id = o.toid 
  JOIN car c ON c.id = o.fromid 
WHERE age > 20 AND model = 'Toyota';

То же самое может быть сделано и более простым запросом (подробнее см. раздел «Работа с edge-таблицами»):

SELECT p.id, name, lastname, age, c.id, c.model 
FROM person p 
  JOIN car c ON p.id = c.owner 
WHERE age > 20 AND model = 'Toyota';

# ALTER TABLE

Синтаксис

ALTER TABLE tbl_name
  [alter_specification [, alter_specification] ...]

alter_specification:
  | ADD [COLUMN] column_definition,...
  | ADD [COLUMN] (column_definition,...)
  | {ALTER|MODIFY} [COLUMN] column_definition,...
  | DROP [COLUMN [if exists]] col_name

column_definition:
  col_name data_type 
    [NOT NULL | NULL] [DEFAULT literal]
  | [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name) REFERENCES tbl_name 

Пример

ALTER TABLE person ADD birthday DATE;

# CREATE INDEX

Синтаксис

CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name,...)

Пример

CREATE INDEX p_ndx_age ON person (age);

# DROP TABLE

Синтаксис

DROP TABLE [IF EXISTS] tbl_name

Пример

DROP TABLE person;

tbl_name - имя таблицы или edge таблицы

# DROP INDEX

Синтаксис

DROP INDEX index_name [ON tbl_name]

Пример

DROP INDEX p_ndx_age;

# INSERT

Синтаксис

INSERT INTO tbl_name
  [(col_name,...)]
  VALUES (value,...)

Пример

INSERT INTO person
  (id, name, lastname, age, city, dbl, dtime)
VALUES
  ('person5000', 'John', 'Tester', 30, 
  'Lissabon', 1.11, '2018-07-03 01:52:02.65');

# INSERT INTO ... SELECT ...

Синтаксис

INSERT INTO tbl_name
  [(col_name,...)]
  SELECT ...

Пример

INSERT INTO table2
  (id, name, lastname, age, city, dbl, dtime)
SELECT 
  id, name, lastname, age, city, dbl, dtime 
FROM person
WHERE age < 30

# BULK INSERT

Синтаксис

BULK INSERT table_name (col_name,...) 
  FROM 'file_path' 
  [WITH ([FIRSTROW = number,] [FIELDTERMINATOR = 'character'])]

Пример

BULK INSERT person (id, name, lastname, age, city, income, dbl, dtime) 
FROM 'sntest1/person.csv' 
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',');

Замечания

Параметр FIRSTROW указывает, с какой строки файлов начинать импорт (нумерация строк начинаются с 1). Например, для того чтобы пропустить заголовок (пропустить одну первую строку), нужно указать FIRSTROW = 2.

Путь к файлу должен быть абсолютным, возможно также указывать путь относительно файла запущенного сервера. Например, если сервер nbserver.exe лежит в каталоге c:/nitrosbase/bin, то относительный путь ../data/csv/person.csv будет раскрыт как c:/nitrosbase/data/csv/person.csv.

# UPDATE

Синтаксис

UPDATE table_reference
  SET col_name = value,...
  [WHERE where_condition]

UPDATE table_name_or_alias
  SET col_name = value,...
  FROM tablename [alias] | join_expression
  [WHERE where_condition]

Пример

UPDATE person SET name = 'LAMAR' WHERE name = 'Lamar';

Замечания

Синтаксис выражений FROM и WHERE тот же, что и в запросе SELECT.

# DELETE

Синтаксис

DELETE FROM tbl_name
  [WHERE where_condition]
 
DELETE FROM table_name_or_alias FROM join_expression
  [WHERE where_condition]

Пример

DELETE FROM person WHERE name = 'Livia';

удаление с условием на связанные таблицы

DELETE FROM p 
FROM person p join car c ON p.id = c.owner 
WHERE c.model = 'Toyota';

Замечания

Синтаксис выражений FROM и WHERE тот же, что и в запросе SELECT.

При удалении записи будут автоматически удалены все ссылки на нее, а также будут удалены записи из edge-table, в которых удаляемая запись была в поле fromid или toid

# TRUNCATE TABLE

Синтаксис

TRUNCATE TABLE tbl_name

Пример

TRUNCATE TABLE #temptable1;

# BACKUP

Синтаксис

BACKUP DATABASE TO path

Пример

BACKUP DATABASE TO 'c:/data/sntest1/backup'

# SET DATEFORMAT

Синтаксис

SET DATEFORMAT mdy | dmy | ymd | ydm | myd | dym

Пример

SET DATEFORMAT ymd

# SELECT

Синтаксис

SELECT
  [ DISTINCT ]
  select_expression [, select_expression ...]
  FROM tablename | join_expression
  [WHERE where_condition]
  [WHERE INDEX where_condition]
  [GROUP BY column_definition1 [, column_definition1 ...]]
  [HAVING where_condition]
  [ORDER BY column_definition2 [ASC | DESC] [, column_definition2 …] ]
  [LIMIT row_count] 
  [OFFSET offset]

column_definition1:
  column_name | expression

column_definition2:
  column_name | column_number | expression

# Select expression

Часть, идущая непосрественно после SELECT и непосредственно перед FROM, может содержать:

  • Простой список полей:

    SELECT name, lastname FROM person;
    
  • Все поля:

    SELECT * FROM person
    SELECT p.*, c.model FROM person p JOIN car c ON p.id = c.owner;
    
  • AS:

    SELECT COUNT(*) AS c FROM person;
    
  • Простые выражения и функции:

    SELECT upper(name) AS uname, age*2 AS dbl FROM person;
    
  • Агрегирующие функции:

    SELECT COUNT(age), COUNT_BIG(age), MIN(age), MAX(age), AVG(age) FROM person;
    
  • DISTINCT:

    SELECT distinct name, lastname FROM person;
    
  • вложенный SELECT:

    SELECT 
      name, 
      (SELECT model FROM car WHERE p.id = owner) 
        AS modelname 
    FROM person p;
    
  • CASE...WHEN:

    SELECT name, 
      CASE name 
        WHEN 'Lamar' THEN 'L' 
        WHEN 'Mercedez' THEN 'M' 
        ELSE 'U' END AS n
    FROM person;
    
    SELECT id, 
      CASE WHEN age < 30 THEN 1 
      ELSE 2 END as age_category, 
      name, lastname, income
    from person
    

# Выражение WHERE

  • Простые операторы сравнения и логические операторы:

    SELECT * FROM person WHERE age > 20 AND name = 'Lamar';
    
  • Оператор LIKE, выполняющий сопоставление с образцом для строковых типов:

    SELECT * FROM person WHERE name LIKE '%amar%';
    SELECT * FROM person WHERE name LIKE 'L[ai]%';
    
  • Оператор IN или NOT IN:

    SELECT name, age FROM person 
    WHERE age IN (20, 22, 24) AND name IN ('Lamar', 'Susan');
    
    SELECT * FROM customers 
    WHERE ctype IN ( 
        SELECT distinct otype 
        FROM orders 
        WHERE odate >= '2020-03-03' 
      );
    
  • Проверка на NULL:

    SELECT * FROM person WHERE age IS NULL;
    SELECT * FROM person WHERE age IS NOT NULL;
    
  • Subqueries with EXISTS or NOT EXISTS

    SELECT * FROM person WHERE age > 40 
      AND EXISTS( SELECT * FROM car WHERE car.owner = person.id );
    
  • Другие операторы и функции (список операторов и функций см. далее):

    SELECT * FROM person WHERE age*2 - income/20 > 0 AND UPPER(name) = 'LAMAR';
    

# Выражения FROM и JOIN

Запрос к одной таблице:

SELECT * FROM person;

JOIN операции через поле FOREIGN-KEY или через промежуточную таблицу тип EDGE являются быстрыми операциями. Мы рекомендуем при разработке стараться максимально использовать такие простые JOIN операции

NitrosBase существенно ускоряет обработку таких JOIN операций, так как является мультимодельной и для хранения связей использует возможности графовой подсистемы. В реляционном представлении связи между объектами представляются как ссылки через поля с FOREIGN KEY на id записей в этой же или другой таблице. Условие ON может содержать только оператор =.

  • JOIN между двумя таблицами через поле с FOREIGN KEY:

    SELECT name, model FROM person p JOIN car c ON p.id = c.owner;
    
  • JOIN между двумя таблицами через промежуточную таблицу (в данном примере через таблицу owner, имеющую тип EDGE, предназначенный для описания ребер графа):

    SELECT p.id, name, lastname, age, c.id, c.model
    FROM person p 
      JOIN owner o ON p.id = o.toid 
      JOIN car c ON c.id = o.fromid
    WHERE age > 20 AND model = 'Toyota';
    

JOIN операции через простые поля (не FOREIGN-KEY) являются достаточно медленными. Мы рекомендуем по возможности перестроить структуру базы данных чтобы использовать FOREIGN-KEY или хотя бы создать индекс(ы) по полям, используемым в JOIN выражении.

  • JOIN через обычное поле

    SELECT p1.id, p2.id 
    FROM person p1 
      JOIN person2 p2 
      ON p1.name = p2.name
    

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

    CREATE INDEX p_name ON person (name)
    

    и/или

    CREATE INDEX p2_name ON person2 (name)
    
  • JOIN через несколько полей с условием and

    SELECT p1.id, p2.id 
    FROM person p1 
      JOIN person2 p2 
      ON p1.name = p2.name AND p1.lastname = p2.lastname
    

    Для выполнения такого запроса желательно создать индекс по двум полям

    CREATE INDEX p_name_lastname ON person (name, lastname)
    

    или (для таблицы person2)

    CREATE INDEX p2_name_lastname ON person2 (name, lastname)
    

    Но достаточно иметь один из простых индексов по одному полю name или lastname для таблицы person или person2

    CREATE INDEX p_name ON person (name)  
    CREATE INDEX p_name ON person (lastname)  
    CREATE INDEX p_name ON person2 (name)  
    CREATE INDEX p_name ON person2 (lastname)
    
  • OUTER JOIN
    В данной версии outer join работает только для связей через foreign-key поля

    SELECT p.*, c.model 
    FROM person p 
      LEFT OUTER JOIN car c 
      ON p.id = c.owner AND c.model = 'Honda'
    
    SELECT p.*, c.model 
    FROM person p 
      RIGHT OUTER JOIN car c 
      ON p.id = c.owner AND p.age = 45
    
  • JOIN с результатом вложенного запроса SELECT

    SELECT p.age, p2.age 
    FROM person p 
      JOIN ( SELECT name, lastname, age FROM person ) p2 
      ON p.name = p2.name and p.lastname = p2.lastname
    

# Выражение GROUP BY

  • Простая группировка по одному полю:

    SELECT COUNT(dbl), AVG(dbl), MIN(dbl), MAX(dbl) 
    FROM person 
    GROUP BY city;
    
  • Группировка по нескольким полям:

    SELECT COUNT(dbl), AVG(dbl), MIN(dbl), MAX(dbl) 
    FROM person 
    GROUP BY city, name;
    
  • Фильтрация результатов агрегации с использованием HAVING:

    SELECT city, AVG(dbl) AS avg_dbl
    FROM person
    GROUP BY city
    HAVING (avg_dbl <= 0.5);
    

В агрегирующих функциях возможно использовать различные выражения и DISTINCT:

SELECT AVG(income*dbl) AS avg_expr FROM person
SELECT AVG( 
  CASE WHEN name = 'Lamar' THEN 1 
       WHEN name = 'Mercedez' THEN 2 
       ELSE 0 end
  ) AS avg_expr  
FROM person'
SELECT city, COUNT(distinct id) FROM person GROUP BY city;

Если выражение GROUP BY опущено, но присутствуют агрегирующие функции, весь результат трактуется как одна группа.

# Выражение ORDER BY

  • Простая сортировка по одному полю:

    SELECT name, lastname, age
    FROM person 
    ORDER BY name;
    
  • Сортировка по нескольким полям и с указанием порядка сортировки:

    SELECT name, lastname, age
    FROM person
    ORDER BY name, lastname ASC, age DESC;
    
  • Сортировка по номеру колонки (номер колонки в выражении select начинается с 1):

    SELECT name, lastname, age
    FROM person
    ORDER BY 1, lastname ASC, 3 DESC;
    
  • Сортировка по выражению:

    SELECT name, lastname, age
    FROM person
    ORDER income/age, name, lastname;
    

# Выражения LIMIT и OFFSET

LIMIT и OFFSET служат для ограничения количества записей в результате:

  • Вывести только 100 записей:

    SELECT * FROM person LIMIT 100;
    
  • Вывести 10 записей, начиная с 100-й (нумерация записей начинается с нуля — то есть пропустить первые 100 записей):

    SELECT * FROM person LIMIT 10 OFFSET 100;
    

# Операторы

  • операторы сравнения:

    >, >=, <, <=, =, <>, !=, IS NULL, IS NOT NULL, IN

    SELECT * FROM person WHERE age IS NULL;
    
  • оператор LIKE, выполняющий сопоставление с образцом для строковых типов (аналогичен одноименному оператору в MS SQL Server).

    Наряду с % (любая последовательность символов) и _ (любой один символ) в шаблонах разрешается использовать заключенные в [ и ] перечисления символов. При задании перечислений разрешается использовать ^ (отрицание) и - (диапазон). Для экранирования спецсимволов следует заключать их в квадратные скобки.

    SELECT * FROM person WHERE name LIKE '%amar%';
    SELECT * FROM person WHERE name LIKE 'L[ai]%';
    
  • логические операторы:

    AND (&&), OR (||), NOT

    SELECT * FROM person
    WHERE age > 50 AND (name = 'Lamar' OR lastname = 'Wurdeman');
    
  • арифметические операторы:

    +, -, *, /

    SELECT age*2 FROM person WHERE age*100/income > 3;
    

# UNION

Синтаксис

SELECT ...
UNION [ALL] 
SELECT ...
[UNION [ALL] SELECT ...]

Пример

SELECT id, name, lastname, city FROM person WHERE id = 'person22' 
UNION 
SELECT id, name, lastname, city FROM person WHERE id = 'person33' 
UNION 
SELECT id, name, lastname, city FROM person WHERE id = 'person55

# ФУНКЦИИ


Функция Описание
ABS Возвращает абсолюьное значение (модуль) числа
ACOS Возвращает арккосинус действительного числа
ASCII Возвращает ASCII-код символа
ASIN Возвращает арксинус действительного числа
ATAN Возвращает арктангенс действительного числа
CAST Выполняет преобразование типов
CEIL Наименьшее целое, большее переданного аргумента или равное ему
CHR Возвращает символ с соответствующим ASCII-кодом
CONCAT Соединение (конкатенация) двух или более строк
COS Возвращает косинус числа
COT Возвращает котангенс числа
DATEPART Возвращает целочисленное значение указанной части даты
DATETIME2FROMPARTS Возвращает datetime2, построенное из указанных параметров
DIV Целочисленное деление, возвращается целое — результат деления с остатком первого аргумента на второй
EXP Возвращает основание натуральных логарифмов e, возведенное в степень, переданную как аргумент
ISNULL Проверяет выражение на NULL и возвращает указанное значение если NULL иначе возвращает значение выражения
LEFT, STRLEFT Возвращает начальную подстроку исходной строки (первый аргумент) с определенной длиной (второй аргумент)
LEN, LENGTH Возвращает длину (количество байтов) строки
LN, LOG Возвращает натуральный логарифм числа
LOG10 Возвращает логарифм по основанию 10
LOG2 Возвращает логарифм по основанию 2
LOWER Преобразует символы строки в нижний регистр
LPAD Дополняет слева первую строку второй до достижения определенной длины
MOD Остаток от целочисленного деления первого аргумента на сторой
MONTH Возвращает месяц даты
PI Возвращает значение числа пи
POWER Возводит число (первый аргумент) в степень (второй аргумент)
POS, POSITION Возвращает позицию первого вхождения второй строки в первую
RAND Возвращает случайное число большее 0 и меньшее 1
REPEAT Сцепляет строку саму с собой определенное число раз; строка — первый аргумент, количество повторений — второй
REPLACE Заменяет в исходной строке (первый аргумент) все вхождения одной строки (второй аргумент) другой (третий аргумент)
REVERSE Обращает строку
ROUND Округляет число до указанного количества десятичных разрядов
YEAR Возвращает год переданной даты
STRLEFT Начальная подстрока исходной строки (первый аргумент) определенной длины (второй аргумент)
STRRIGHT Конечная подстрока исходной строки (первый аргумент) определенной длины (второй аргумент)
RPAD Дополняет справа первую строку второй до достижения определенной длины
REGEX Проверка совпадения строки с образцом, задаваемым регулярным выражением
RIGHT, STRRIGHT Возвращает начальную подстроку исходной строки (первый аргумент) с определенной длиной (второй аргумент)
SIGN Возвращает знак числа: -1 для отрицательных, 0 для 0, 1 для положительных
SIN Возвращает синус числа
SQRT Returns the square root of a number
SUBSTR, SUBSTRING Extracts a substring from a string (starting at any position)
TAN Возвращает тангенс числа
TRIM Удаляет начальные и конечные пробелы из строки
TRUNCATE Усекает число до указанного числа знаков после запятой
UPPER Преобразует переданную строку в верхний регистр

# ТИПЫ ДАННЫХ


Тип Псевдонимы Описание
BIT BOOL 0 или 1, аналог булевого типа
INT INTEGER, TINYINT 4-байтовое целочисленное значение от -2 147 483 648 до 2 147 483 647
BIGINT 8-байтовое целочисленное значение от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807
REAL(s,d) DOUBLE(size,d), FLOAT(size,d) 8-байтовое с плавающей точкой, диапазон точности от 1.7E-308 до 1.7E+308.
Значения в круглых скобках игнорируются
DECIMAL(s,d) NUMERIC(size,d) 8-байтовое с плавающей точкой. В данной версии идентично DOUBLE, в следующей версии будет реализован с использование десятичной арифметики.
DATE Даты от 0001-01-01 (1 января 0001 года) до 9999-12-31 (31 декабря 9999 года).
Поддерживаемые строковые форматы значений:
  • YYYY-MM-DD: 2017-07-31
  • YYYY/MM/DD: 2017/07/31
  • YYYY.MM.DD: 2017.07.31
DATETIME Дата и время суток, где дата — в том же диапазоне, что и в DATE.
Поддерживаемые строковые форматы значений:
  • YYYY-MM-DD hh:mm:ss:nnnnnnn: 2017-07-31 01:21:34.1234567
  • YYYY/MM/DD hh:mm:ss:nnnnnnn: 2017/07/31 01:21:34.1234567
  • YYYY.MM.DD hh:mm:ss:nnnnnnn: 2017.07.12 01:21:34.1234567
DATETIME2 (prec) Практически идентичен DATETIME за исключением указания количества знаков в дробной части секунд
CHAR(size) VARCHAR(size),NVARCHAR(size), TEXT Строка; в данной версии значения в круглых скобках игнорируются. В следующей версии длина строки будет ограничиваться указанным значением
VARBINARY (size) Бинарные данные; значения в круглых скобках игнорируются
Пример строкового представления: 0x000000A4

# ПСЕВДОТАБЛИЦЫ

# Работа с edge-таблицами

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

CREATE TABLE friends AS EDGE person person;

Заполнение таблицы:

INSERT INTO friends VALUES ( 'person22', 'person1022' );
INSERT INTO friends VALUES ( 'person22', 'person1023' );

Получение данных:

SELECT p1.id, p2.id
  FROM person p1
  JOIN friends f ON p1.id = f.fromid
  JOIN person p2 ON f.toid = p2.id
WHERE p1.id = 'person22';

Обновление данных:

UPDATE friends SET fromid = 'person7', toid = 'person67' 
WHERE fromid = 'person7' AND toid = 'person1007'
UPDATE car SET owner = 'person23' WHERE id = 'car4022',

Удаление:

DELETE FROM friends WHERE fromid = 'person22' AND toid = 'person1022';
DELETE FROM owner WHERE toid = 'person22',
DELETE FROM owner WHERE fromid = 'car4021',

# Получение списка таблиц

SELECT * FROM INFORMATION_SCHEMA.TABLES;

Результат выполнения запрос содержит два поля:

  • name — имя таблицы;
  • type — тип таблицы (2 — обычная таблица; 3 — ссылочная таблица, см. CREATE TABLE AS EDGE).

Примечание: C++ API имеет отдельную функцию nb_get_tableslist() для получения списка таблиц.

# Получение списка полей таблицы

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='car';

Результат выполнения запроса содержит пять полей:

  • name — имя поля;
  • type — тип поля:
    1 — string,
    2 — int,
    3 — bigint,
    4 — double,
    5 — datetime,
    6 — bool,
    7 — date,
    8 — является primary key либо foreign key (в текущей версии по сути строка);
  • subtype — уточняет сведения в поле type:
    0 — обычное поле,
    1 — primary key,
    2 — foreign key;
  • linktable — название опорной таблицы для поля foreign key;
  • nullable — в текущей версии всегда TRUE.

Примечание: C++ API имеет отдельную функцию nb_get_tableschema() для получения списка полей таблицы.

# ИСПОЛЬЗОВАНИЕ ИНДЕКСОВ

Создание индекса

CREATE INDEX p_age ON person (age)  
CREATE INDEX p_name ON person (name)  

Удаление индекса

DROP INDEX index_name [ON tbl_name]

Использование индекса в простых запросах

SELECT * FROM person WHERE age = 22  

В данном запросе используется индекс p_age

SELECT * FROM person WHERE age = 22 and name = 'Lamar'

В данном случае используются два индекса p_age и p_name

Запросы по диапазону

SELECT * FROM person WHERE age >= 22 and age < 24

Индексы по нескольким полям

CREATE INDEX p_name_lastname ON person (name, lastname)  
SELECT * FROM person WHERE name = 'Lamar' and lastname = 'Tanon'

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

Для простых сравнений можно явно потребовать использования предварительно созданного индекса. Указание использовать индекс — ключевое слово INDEX после WHERE:

SELECT name, lastname, city, model 
FROM person p JOIN car c ON c.owner = p.id 
WHERE INDEX name = 'Lamar';

Индексы также используются при обработке запросов, содержащих выражение JOIN (см. раздел "Выражения FROM и JOIN")