# 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
orNOT 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
через несколько полей с условием andSELECT 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 года).Поддерживаемые строковые форматы значений:
|
DATETIME | — | Дата и время суток, где дата — в том же диапазоне, что и в DATE .Поддерживаемые строковые форматы значений:
|
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")