SQL

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

Запрос CREATE TABLE

CREATE TABLE [IF NOT EXISTS] tbl_name 
    ( create_definition [, create_definition] ... )
 
create_definition:
    col_name data_type 
      [NOT NULL | NULL] [DEFAULT literal]
      [COMMENT 'string']
    | [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
);

Если ни у одного поля не указан спецификатор PRIMARY KEY, CREATE TABLE автоматически добавит в таблицу являющееся первичным ключом поле id. Такое поведение обусловлено соображениями мультимодельности. Модели, отличные от реляционной, предполагают наличие уникального идентификатора у каждого узла графа или JSON-документа.

FOREIGN KEY может ссылаться только на поле, являющееся первичным ключом.

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' 

То же самое может быть сделано и более простым запросом (см. описание операции JOIN):

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:
  table_options
  | ADD [COLUMN] col_name column_definition,...
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (col_name) REFERENCES tbl_name
  | {ALTER|MODIFY} [COLUMN] col_name column_definition,...
  | DROP [COLUMN] col_name


column_definition:
    data_type [NOT NULL | NULL] [DEFAULT literal]

Пример

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;

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');

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 table_name_or_alias FROM join_expression
    [WHERE where_condition]

Пример

DELETE FROM person WHERE name = 'Livia'

Замечания

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

BACKUP

BACKUP DATABASE TO path

Пример

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

SELECT

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

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 countofpersons from person
    
  • Простые выражения и функции:

    select upper(name) as uname, age*2 as dbl from person
    
  • Агрегирующие функции:

    select count(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
    

Выражение WHERE

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

    select * from person where age > 20 and name = 'Lamar'
    
  • Оператор IN:

    select name, age from person 
    where age in (20, 22, 24) and name in ('Lamar', 'Susan')
    
  • Проверка на NULL:

    select * from person WHERE age IS NULL
    select * from person WHERE age IS NOT NULL
    
  • Простые выражения и функции (список операторов и функций см. далее):

    select * from person where age*2 - income/20 > 0 and upper(name) = 'LAMAR'
    

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

select name, lastname, city, model from person p join car c on c.owner = p.id where index name = 'Lamar'

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

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

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

    select name, model from person p join car c on p.id = c.owner
    
  • JOIN между несколькими таблицами:

    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 между двумя таблицами через промежуточную таблицу (в данном примере через таблицу 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'
    

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

    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'
    

Особенности реализации

NitrosBase существенно ускоряет обработку JOIN, так как является мультимодельной и для хранения связей использует возможности графовой подсистемы.

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

Выражение 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.6)
    

В агрегирующих функциях возможно использовать различные выражения и 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
    

Выражения 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
    
  • логические операторы:

    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
    

Функции

Функция Описание
ABS Возвращает абсолюьное значение (модуль) числа
ACOS Возвращает арккосинус действительного числа
ASCII Возвращает ASCII-код символа
ASIN Возвращает арксинус действительного числа
ATAN Возвращает арктангенс действительного числа
CAST Выполняет преобразование типов
CEIL Наименьшее целое, большее переданного аргумента или равное ему
CHR Возвращает символ с соответствующим ASCII-кодом
CONCAT Соединение (конкатенация) двух или более строк
COS Возвращает косинус числа
COT Возвращает котангенс числа
DIV Целочисленное деление, возвращается целое — результат деления с остатком первого аргумента на второй
EXP Возвращает основание натуральных логарифмов e, возведенное в степень, переданную как аргумент
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.
Значения в круглых скобках игнорируются
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
CHAR(size) VARCHAR(size), TEXT Строка; значения в круглых скобках игнорируются