Я чувствую себя как функция арктангенса, которая приближается к асимптоте.
“Теория большого взрыва”
Функции, которые доступны в СУБД SQLite, делятся на несколько категорий: строковые, для работы с датой и временем, числовые и математические, логические, агрегатные и прочие.
Строковые функции
Функция | Описание |
---|---|
CHAR | Возвращает строку, заданных кодами unicode. |
INSTR | Возвращает позицию подстроки в строке. |
LENGTH | Возвращает длину указанной строки. |
LOWER | Преобразует все символы в указанной строке в нижний регистр. |
LTRIM | Удаляет все указанные символы из левой части строки. |
REPLACE | Заменяет все вхождения указанной строки. |
RTRIM | Удаляет все указанные символы из правой части строки. |
SUBSTR | Позволяет извлечь подстроку из строки. |
TRIM | Удаляет все указанные символы с обеих сторон строки. |
UPPER | Преобразует все символы в указанной строке в верхний регистр. |
HEX | Интерпретирует аргумент как BLOB и возвращает строку, которая содержит шестнадцатеричное представление содержимого аргумента в верхнем регистре. |
QUOTE | Возвращает текст литерала SQL, который является значением его аргумента, подходящего для включения в инструкцию SQL. |
CHAR
CHAR( x1, x2, …, xn)
Возвращает строку символов, заданных кодами Unicode. В качестве параметров передаётся список числовых значений Unicode ( https://unicode-table.com/ru/ ).
SELECT CHAR(5125,32) || word FROM word
Результат
ᐅ дубина
ᐅ дракон
ᐅ дерево
☝ В справочниках числовые значения Unicode обычно приводятся как шестнадцатеричные числа, для использования в SQL-запросе необходимо их перевести в десятичные.
INSTR
INSTR( <строка>, <подстрока> )
Возвращает позицию подстроки в строке
SELECT word, INSTR( word, 'а') FROM word
Code language: JavaScript (javascript)
Результат
дубина 6
дракон 3
дерево 0
дятел 0
LENGTH
LENGTH( <строка> )
Возвращает длину строки
SELECT word, LENGTH( word ) FROM word
Результат
дерево 6
дятел 5
дуб 3
добро 5
☝ Строго говоря, функция возвращает количество символов в строке до первого символа NULL, но обычно строки не содержат этот символ и результат совпадает с общим числом символов.
📝 Для аргумента данной функции предусмотрена автоматическая конвертация типов, поэтому, если передать в качестве аргумента число, функция вернёт длину строкового представления данного числа. А для BLOB аргумента функция вернёт количество байт.
LOWER
LOWER( <строка> )
Преобразует символы строки в нижний регистр.
SELECT word, LOWER( word ) FROM word
Результат
дерево дерево
Дмитрий дмитрий
Донецк донецк
дым дым
☝ Обычно функция LOWER обрабатывает только ASCII-символы, что значительно сужает область её применения, но в MVDB реализована поддержка для правильной конвертации кириллицы.
LTRIM
LTRIM( <строка>, [<символы>] )
Функция ltrim(X,Y) возвращает строку, сформированную путем удаления всех символов Y, которые есть слева в X. Если аргумент Y опущен, ltrim(X) удаляет пробелы слева в X.
SELECT word, LTRIM( word, 'аэро' ) FROM word
Code language: JavaScript (javascript)
Результат
арбуз буз
аспид спид
аспирин спирин
аэробус бус
бабуин бабуин
REPLACE
REPLACE( <строка>, <старое значение>, <новое значение> )
Функция replace (A,B,C) возвращает строку, образованную заменой в строке A всех вхождений B на C.
SELECT word, REPLACE( word, 'а', 'у' ) FROM word
Code language: JavaScript (javascript)
Результат
аист уист
ананас унунус
апорт упорт
арбуз урбуз
RTRIM
RTRIM( <строка>, [<символы>] )
Функция rtrim(X,Y) возвращает строку, сформированную путем удаления всех символов Y, которые есть справа в X. Если аргумент Y опущен, rtrim(X) удаляет пробелы слева в X.
SELECT word, RTRIM( word, 'а' ) FROM word
Code language: JavaScript (javascript)
Результат
кебаб кебаб
кета кет
килька кильк
киска киск
кит кит
SUBSTR
SUBSTR( <строка>, <начало>, <длина> )
Функция substr (X,Y,Z) возвращает подстроку входной строки X, которая начинается с Y-го символа и имеет длину Z символов. Если Z опущен, то substr (X,Y) возвращает все символы до конца строки X, начиная с Y-го символа. Самый левый символ X имеет позицию 1. Если Y отрицательно, то первый символ подстроки находится путем подсчета справа, а не слева. Если Z отрицательный, то возвращаются символы abs(Z), предшествующие Y-му символу. Если X является строкой, то индексы символов ссылаются на фактические символы UTF-8. Если x – это blob, тогда индексы относятся к байтам.
SELECT word, SUBSTR( word, 1,2 ), SUBSTR( word, 3 ) FROM word
Результат
абак аб ак
абрикос аб рикос
автобус ав тобус
автомат ав томат
агава аг ава
азалия аз алия
TRIM
TRIM( <строка>, [<символы>] )
Функция trim(X,Y) возвращает строку, сформированную путем удаления всех символов Y, которые есть слева и справа в X. Если аргумент Y опущен, trim(X) удаляет пробелы слева и справа в X.
SELECT word, TRIM( word, 'а') FROM word
Code language: JavaScript (javascript)
Результат
автобус втобус
автомат втомат
агава гав
азалия залия
UPPER
UPPER ( <строка> )
Преобразует символы строки в верхний регистр.
SELECT word, UPPER( word ) FROM word
Результат
дерево ДЕРЕВО
Дмитрий ДМИТРИЙ
Донецк ДОНЕЦК
дым ДЫМ
☝ Обычно функция LOWER обрабатывает только ASCII-символы, что значительно сужает область её применения, но в MVDB реализована поддержка для правильной конвертации кириллицы.
HEX
HEX( <строка> )
Функция hex() интерпретирует аргумент как BLOB и возвращает строку, которая содержит шестнадцатеричное представление содержимого аргумента в верхнем регистре.
SELECT word, HEX( word) FROM word
Результат
лоб 3B043E043104
ложка 3B043E0436043A043004
лом 3B043E043C04
лопата 3B043E043F04300442043004
лоск 3B043E0441043A04
PRINTF
PRINTF( [<формат>,]<данные> {,<данные>} )
Функция printf(FORMAT,…) – sql-функция форматированного вывода. Первый аргумент – это строка формата, указывающая, как построить выходную строку, используя значения, взятые из последующих аргументов.
Строка формата определяет выходную последовательность, в которую могут входить как обычные символы, так и символы замещения, которые определяют место отображения данных, передаваемых в качестве остальных аргументов функции PRINTF()..
Формат символов замещения имеет определенную структуру: начинается с символа % и заканчивается одним из символов задания типа:
%[<флаги>][<ширина>][.<точность>][<длина>]<тип>
Как видите, только символ % и тип являются обязательными параметрами, остальные – опциональные.
Чтобы добавить в формат символ %, напишите его дважды.
Если аргумент FORMAT отсутствует или равен NULL, то результатом будет NULL.
Тип подстановки
Таблица 1. Типы подстановок
Тип | Описание |
---|---|
% | Два символа % подряд интерпретируются на выходе как один символ % без подстановки значения. |
d, i | Целое число со знаком |
u | Целое число без знака |
f | Число с плавающей запятой |
e, E | Число с плавающей запятой, отображаемое в экспоненциальной записи. |
g, G | Число с плавающей запятой, отображаемое либо в обычной десятичной записи, либо, если показатель степени не близок к нулю, в экспоненциальной записи. |
x, X | Целое число, отображаемое в шестнадцатеричном формате; x – в нижнем регистре, X – в верхнем регистре. |
o | Целое число, отображаемое в восьмеричном формате. |
s, z | Строка с нулевым символом в конце. |
c | Единичный символ. Если аргументом является строка, то из нее извлекается и отображается только первый символ. |
p | Для SQLite аналогично %x |
n | Ничего не отображается. Оставлен для совместимости с реализацией printf на языке С. |
q, Q | Cтрока с нулевым символом в конце. Строка печатается со всеми одинарными кавычками (‘), удвоенными, чтобы строка могла безопасно появляться внутри строкового литерала SQL. Тип подстановки% Q также помещает одинарные кавычки на обоих концах замещаемой строки. Если аргумент %Q является нулевым указателем, тогда выводом является NULL без кавычек. Другими словами, нулевой указатель генерирует SQL NULL, а ненулевой указатель генерирует допустимый строковый литерал SQL. Если аргумент %q является нулевым указателем, то выходные данные не генерируются. Таким образом, нулевой указатель на %q совпадает с пустой строкой. Для этих подстановок точность – это количество байтов или символов, взятых из аргумента, а не количество байтов или символов, записанных в вывод. Подстановки% q и% Q являются улучшениями SQLite, которых нет в большинстве других реализаций printf (). |
w | Эта подстановка работает как %q, за исключением того, что она удваивает все символы двойных кавычек (“) вместо одинарных кавычек, что делает результат пригодным для использования с именем идентификатора в двойных кавычках в выражении SQL. Подстановка% w – это усовершенствование SQLite, которого нет в большинстве других реализаций printf (). |
Длина
Длина значения аргумента может быть указана одной или несколькими буквами l, которые встречаются непосредственно перед буквой типа замещения. В SQLite длина имеет значение только для целочисленных типов, но на практике редко используется.
Длина | Действие |
---|---|
<не указана> | используются 32-битные значения |
l | используются 32-битные значения |
ll | используются 64-битные значения |
Ширина
Поле <ширина> определяет минимальную ширину подставляемого значения в выходных данных. Если строка или число, записанные в вывод, короче ширины, то значение дополняется. Заполнение слева (значение выровнено по правому краю) по умолчанию. Если используется флаг «-», то заполнение находится справа, а значение выравнивается по левому краю.
Ширина измеряется в байтах по умолчанию. Однако, если “!” флаг присутствует, тогда ширина в символах. Это имеет значение только для многобайтовых символов utf-8, которые встречаются только при замене строк.
Если ширина представляет собой один символ «*» вместо числа, то фактическое значение ширины читается как целое число из списка аргументов. Если считываемое значение является отрицательным, то для ширины используется абсолютное значение, а значение выравнивается по левому краю, как если бы присутствовал флаг «-».
Если подставляемое значение больше ширины, тогда к выходу добавляется полное значение. Другими словами, ширина – это минимальная ширина значения, которое отображается на выходе.
Точность
Поле точности, если оно присутствует, должно следовать после ширины и отделяться одинарным символом «.». Если ширины нет, то укажите “.” сразу же после флагов (если они есть) или начального “%”.
Для подстановок строк (% s,% z,% q,% Q или% w) точность – это число байтов или символов, используемых в аргументе. По умолчанию это число байтов, но, если “!” флаг присутствует., то это число символов, Если нет точности, то подставляется вся строка. Примеры: «% .3s» заменяет первые 3 байта строки аргумента. «% !. 3s» заменяет первые три символа строки аргумента.
Для целочисленных замен (% d,% i,% x,% X,% o и% p) точность определяет минимальное количество отображаемых цифр. При необходимости добавляются начальные нули, чтобы расширить вывод до минимального количества цифр.
Для подстановок с плавающей точкой (% e,% E,% f,% g,% G) точность задает количество цифр, отображаемых справа от десятичной точки.
Для замены символа (% c) точность N больше 1 приводит к тому, что символ повторяется N раз. Это нестандартное расширение, встречающееся только в SQLite.
Флаги
Флаги состоят из нуля или более символов, которые следуют сразу за «%», который определяет подстановку. Различные флаги и их значения следующие:
Флаг | Значение |
---|---|
– | Выровнять по левому краю значение на выходе. По умолчанию используется выравнивание по правому краю. Если ширина равна нулю или иным образом меньше длины подставляемого значения, то заполнение отсутствует, а флаг “-” не используется. |
+ | Для числовых замен со знаком поставьте знак «+» перед положительными числами. Знак «-» всегда появляется перед отрицательными числами независимо от настроек флага. |
<пробел> | Для числовых замен со знаком подставляет один пробел перед положительными числами. |
0 | Добавьте столько цифр «0» к числовым подстановкам, сколько необходимо, чтобы расширить значение до указанной ширины. Если поле ширины опущено, то этот флаг не используется. |
# | Это флаг «альтернативная форма 1». Для замен% g и% G это приводит к удалению конечных нулей. Этот флаг заставляет десятичную точку появляться для всех подстановок с плавающей точкой. Для подстановок% o,% x и% X флаг “альтернативная форма 1” приводит к добавлению значения к «0», «0x» или «0X» соответственно. |
, | Этот флаг приводит к добавлению разделителей-запятых к выводу подстановок% d и% i между каждыми 3 цифрами слева. Это может помочь людям легче определить величину больших целочисленных значений. Например, значение 2147483647 будет отображаться как «2147483647» с использованием «% d», но будет отображаться как «2 147 483 647» с «%, d». Этот флаг является нестандартным расширением. |
! | Это флаг “альтернативная форма 2”. Для подстановок строк этот флаг заставляет понимать ширину и точность в терминах символов, а не байтов. Для подстановок с плавающей запятой флаг “альтернативная форма 2” увеличивает максимальное число значащие цифры, отображаемые от 16 до 26, заставляя отображать десятичную точку и приводит к появлению хотя бы одной цифры после десятичной точки. Флаг “альтернативная форма 2” – это нестандартное расширение, которое отсутствует в других реализациях printf (). |
📝 Обычно за формат отображения данных в таблице отвечает My Visual Database, который либо делает это автоматически, либо формат можно настроить с помощью скриптов. Но в некоторых случаях функция printf может пригодиться.
SELECT id*4, PRINTF('%4.4X',id*4 ) FROM word
Code language: JavaScript (javascript)
Результат
4 0004
8 0008
12 000C
16 0010
20 0014
QUOTE
QUOTE( <аргумент>)
Функция quote(X) возвращает текст литерала SQL, который является значением его аргумента, подходящего для включения в инструкцию SQL. Строки окружены одинарными кавычками с экранированием внутренних кавычек по мере необходимости. Большие двоичные объекты кодируются как шестнадцатеричные литералы. Строки со встроенными символами NULL не могут быть представлены в SQL как строковые литералы, поэтому возвращаемый строковый литерал усекается до первого NUL.
SELECT QUOTE("Генри О'Хара")
Code language: JavaScript (javascript)
Результат
'Генри О''Хара'
Code language: JavaScript (javascript)
📝 Данная функция может быть очень полезной, если текст запроса формируется вручную (с помощью скриптов), а данные для вставки вводятся пользователем и могут содержать одинарные кавычки.
Работа с датой/временем
Время представлено в SQLite как текстовая строка определенного формата. Особенность функции SQLite по работе с датой – отсутствие функции, возвращающей текущую дату. Вместо этого используют специальную константу в качестве аргумента нижеперечисленных функций – NOW
Функция | Описание |
---|---|
DATE | Вычисляет1 дату и возвращает в строковом формате ‘YYYY-MM-DD’. |
DATETIME | Вычисляет дату и время, возвращает в строковом формате ‘YYYY-MM-DD HH:MM:SS’. |
JULIANDAY | Возвращает дату как юлианский день2 – в виде числа с плавающей запятой. |
STRFTIME | Возвращает дату/время в строковом формате с заданным представлением. |
TIME | Вычисляет время, возвращает в строковом формате ‘HH-MM-SS’.. |
Примечания:
1. Каждая из описанных функций позволяет выполнять различные операции с датой (прибавлять/отнимать дни, месяцы, годы, часы и т.д.).
2. Юлианский день — это количество дней с 12:00 24 ноября 4714 г. до н.э. по гринвичскому времени по григорианскому календарю.
Первый аргумент всех функций – строка времени, которая может иметь следующий формат:
Формат | Пример |
---|---|
now1 | now |
YYYY-MM-DD | 2020-12-30 |
YYYY-MM-DD HH:MM | 2020-12-30 12:55 |
YYYY-MM-DD HH:MM:SS | 2020-12-30 12:55:23 |
YYYY-MM-DD HH:MM:SS.SSS | 2020-12-30 12:55:23.032 |
YYYY-MM-DDTHH:MM | 2020-12-30T12:55 |
YYYY-MM-DDTHH:MM:SS | 2020-12-30T12:55:23 |
YYYY-MM-DDTHH:MM:SS.SSS | 2020-12-30T12:55:23.032 |
HH:MM | 12:55 |
HH:MM:SS | 12:55:23 |
HH:MM:SS.SSS | 12:55:23.032 |
AAA.BBB2 | 2456572.84952685 |
Примечания:
1. Литерал, используемый для возврата текущей даты.
2. Юлианская дата, выраженная дробным числом – количество дней с полудня 24 ноября 4714 года до н. э. григорианского календаря по Гринвичу.
Модификаторы – это необязательные параметры, разделяемые запятой. Применяются последовательно слева-направо.
Модификатор | Действие |
---|---|
NNN days | Добавляет NNN дней |
NNN hours | Добавляет NNN часов |
NNN minutes | Добавляет NNN минут |
NNN.NNN seconds | Добавляет NNN.NNN секунд. |
NNN months | Добавляет NNN месяцев |
NNN years | Добавляет NNN годов |
start of month | Перенос даты обратно на начало месяца |
start of year | Перенос даты обратно на начало года |
start of day | Перенос времени на начало дня |
weekday N | Перемещает дату вперед на следующую дату, где номер дня недели равен N (0 = воскресенье, 1 = понедельник, 2 = вторник, 3 = среда, 4 = четверг, 5 = пятница, 6 = суббота) |
unixepoch | Модификатор работает только если строка времени в формате DDDDDDDDDD. Модификатор заставляет функции интерпретировать DDDDDDDDDD не как Юлианскую дату, как это должно быть в обычной ситуации, а как UNIX-время — количество секунд с 1970 года. Если модификатор ‘unixepoch’ не следует после строки времени в формате DDDDDDDDDD, выражающей количество секунд с 1970 года, или если другой модификатор стоит между модификатором ‘unixepoch’ и строкой времени, то результат будет неопределенным. Из-за ограничений, налагаемых использованием 64-разрядных целых чисел, модификатор ‘unixepoch’ работает только для дат между 0000-01-01 00:00:00 и 5352-11-01 10:52:47 (UNIX-время от −62167219200 до 10675199167 секунд включительно). |
localtime | Предполагает, что строка времени соответствует всемирному координированному времени (UTC), и приводит время к локальному. |
utc | противоположен ‘localtime’, предполагает, что время соответствует локальному, и приводит время к UTC. |
📝 Для модификаторов 1-6 NNN может быть отрицательным, тогда дата будет уменьшаться. После вычислений результат нормализуется. Например, для даты 2001-03-31 указан модификатор ‘+1 month’, это дает 2001-04-31, но в апреле только 30 дней, поэтому дата нормализуется в 2001-05-01. Аналогичный эффект возникает, когда в первоначальной дате указано 29 февраля високосного года и применяется модификатор ‘±N years’, где N не кратно четырем.
DATE
DATE( <строка времени>, [ <модификатор 1>, <модификатор 1>, … <модификатор N> ] )
Возвращает дату в формате ‘YYYY-MM-DD’. Производит различные модификации исходной даты, которая задается строкой времени.
DATETIME
DATE( <строка времени>, [ <модификатор 1>, <модификатор 1>, … <модификатор N> ] )
Возвращает дату в формате ‘YYYY-MM-DD HH:MM:SS’. Производит различные модификации исходной даты, которая задается строкой времени.
JULIANDAY
JULIANDAY( <строка времени>, [ <модификатор 1>, <модификатор 1>, … <модификатор N> ] )
Возвращает Юлианскую дату — количество дней с полудня 24 ноября 4714 года до н. э. григорианского календаря по Гринвичу.
STRFTIME
STRFTIME( <формат>, <строка времени>, [ <модификатор 1>, <модификатор 1>, … <модификатор N> ] )
Возвращает дату в заданном формате. Производит различные модификации исходной даты, которая задается строкой времени.
Формат может включать в себя следующие шаблоны:
Шаблон | Описание |
---|---|
%d | день месяца: 01…31 |
%f | доли секунд: SS.SSS |
%H | час: 00…23 |
%j | день года: 001…366 |
%J | Юлианская дата |
%m | месяц: 01…12 |
%M | минута: 00…59 |
%s | количество секунд с 1 января 1970 года |
%S | секунда: 00…59 |
%w | день недели: 0…6 (0 — воскресенье) |
%W | неделя года: 00…53 |
%Y | год: 0000…9999 |
%% | % |
Остальные символы, заданные в строке формата, отображаются в результате без изменений.
📝 Функцией STRFTIME можно заменить все остальные функции работы с датой/временем, которые созданы исключительно для удобства.
TIME
TIME( <строка времени>, [ <модификатор 1>, <модификатор 1>, … <модификатор N> ] )
Возвращает время в формате ‘HH:MM:SS’. Производит различные модификации исходного времени, которое задается строкой времени.
Несколько примеров использования функций даты и времени.
Требуемый результат | Пример |
---|---|
Текущая дата | SELECT date(‘now’) |
Последний день текущего месяца | SELECT date(‘now’,’start of month’,’+1 month’,’-1 day’) |
Количество дней, прошедших с подписания Декларации независимости США. | SELECT julianday(‘now’) – julianday(‘1776-07-04’) |
Количество секунд с некоторого момента в 2020 году | SELECT strftime(‘%s’,’now’) – strftime(‘%s’,’2020-06-07 02:34:56′) |
Дата первого вторника октября текущего года | SELECT date(‘now’,’start of year’,’+9 months’,’weekday 2′) |
Сложить три поля со временем, результат отобразить как время | time( strftime(‘%s’,time(fimefield_1)) + strftime(‘%s’,time(fimefield_2)) + strftime(‘%s’,time(fimefield_3)) ,’unixepoch’ ) |
Числовые/математические функции
Функция | Описание |
---|---|
ABS | Возвращает абсолютное значение (модуль) числа. |
RANDOM | Возвращает псевдо-случайное целое число между -9223372036854775808 и +9223372036854775807. |
RANDOMBLOB | Функция randomblob(N) возвращает N псевдо-случайных байт. Если N меньше 1, тогда будет возвращен 1 псевдо-случайный байт. |
ROUND | Возвращает число, округленное до определенного количества десятичных знаков. |
MAX | Возвращает максимальное значение из переданных аргументов (не меньше двух) |
MIN | Возвращает минимальное значение из переданных аргументов (не меньше двух) |
ABS
ABS( <число> )
Функция возвращает абсолютное значение (модуль) числа.
SELECT ABS(5), ABS(-5), ABS(0)
Результат
5 5 0
RANDOM
RANDOM()
Возвращает случайное число в диапазоне от -9223372036854775808 до +9223372036854775807
SELECT RANDOM(), RANDOM()
Результат
-8143724900744948159 -1081441681096557270
☝ Функция возвращает целое число, но из-за ограничений компонента на длину отображаемой мантиссы, в таблице MVD это число может отображаться, как число с плавающей запятой.
На практике данную функцию используют для того, чтобы задать случайный порядок следования данных в выборке. Для этого нужно прописать эту функцию в секции ORDER BY
SELECT * FROM item ORDER BY RANDOM()
RANDOMBLOB
RANDOMBLOB( <целое число> )
Функция randomblob(N) возвращает N псевдослучайных байт. Если N меньше 1, тогда будет возвращен 1 псевдослучайный байт.
📝 С помощью данной функции можно генерировать уникальные идентификаторы, используя функцию вместе с hex() и/или lower() например, так:
SELECT LOWER(HEX(RANDOMBLOB(16)))
Результат
86c58c62119658747da04c124b915892
ROUND
ROUND( <число> [, <точность> ] )
Функция round (X,Y) возвращает значение с плавающей запятой X, округленное до Y цифр справа от десятичной запятой. Если аргумент Y опущен, предполагается, что он равен 0.
SELECT ROUND(3.1415), ROUND(3.1415,2)
Code language: CSS (css)
Результат
3 3.14
Code language: CSS (css)
MAX
MAX( <число 1>, <число 2> { <число N> } )
Возвращает максимальное число из всех числовых значений, переданных в качестве аргументов функции.
SELECT MAX( 45, 456, 83, 21)
Результат
456
📝 Использование MAX в качестве агрегатной функции рассматривается далее в главе “Агрегатные функции”
MIN
MIN( <число 1>, <число 2> { <число N> } )
Возвращает минимальное число из всех числовых значений, переданных в качестве аргументов функции.
SELECT MIN( 45, 456, 83, 21)
Результат
21
📝 Использование MIN в качестве агрегатной функции рассматривается далее в главе “Агрегатные функции”
Логические функции
Функция | Описание |
---|---|
CASE | Процедура. Организует выполнение запроса или его части по условию. Аналог if-then-else. |
COALESCE | Возвращает первое ненулевое выражение в списке. |
IFNULL | Возвращает копию первого не NULL аргумента, или NULL если оба аргумента являются NULL. В Ifnull() должно передаваться именно 2 аргумента. Функция эквивалентна функции coalesce() с двумя аргументами. |
NULLIF | Возвращает NULL, если два выражения эквивалентны. В противном случае он возвращает первое выражение. |
LIKE | Функция like() используется для реализации выражения” Y LIKE X [ESCAPE Z]”. |
CASE
CASE [<выражение>]
WHEN <условие 1> THEN <результат 1>
WHEN <условие 2> THEN <результат 1>
…
WHEN <условие N> THEN <результат N>
[ ELSE <результат N+1> ]
END
Функция CASE оценивает условия и возвращает значение при выполнении одного из условий. Если никакое условие не выполнено, то результат берётся из секции ELSE. Если секция ELSE отсутствует, то функция вернёт значение NULL.
Если задано <выражение>, то оно сравнивается с каждым условием:
SELECT *,
CASE name
WHEN 'Шайба' then 'Без резьбы'
ELSE 'С резьбой'
END
FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
1 Шайба 20 Без резьбы
2 Винт 50 С резьбой
3 Гайка 30 С резьбой
Code language: SQL (Structured Query Language) (sql)
Если <выражение> не используется, то условия могут быть произвольными:
SELECT *,
CASE
WHEN price > 30 then 'Дорогое'
WHEN name = 'Шайба' THEN 'Особое'
END
FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
1 Шайба 20 Особое
2 Винт 50 Дорогое
3 Гайка 30
Code language: SQL (Structured Query Language) (sql)
📝 Строго говоря, CASE – это не функция, а конструкция языка SQL, которая может быть использована в запросах или сохраненных процедурах. Является аналогом конструкции IF THEN ELSE в обычных языках программирования.
COALESCE
COALESCE( <значение 1>, <значение 2> { <значение N> } )
Функция coalesce() возвращает копию первого не NULL аргумента или NULL, если все аргументы являются NULL. Coalesce() должна использовать как минимум 2 аргумента.
SELECT name, price, COALESCE( price, 0 ) FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
Шайба 20 20
Винn 50 50
Гайка 30 30
Пружина <NULL> 0
Code language: SQL (Structured Query Language) (sql)
📝 Данная функция обычно используется, если целевой аргумент может быть NULL, а результат обязательно должен иметь конкретное значение.
IFNULL
IFNULL( <значение 1>, <значение 2> )
Функция ifnull() возвращает копию первого не NULL аргумента, или NULL если оба аргумента являются NULL. В Ifnull() должно передаваться именно 2 аргумента. Функция эквивалентна функции coalesce() с двумя аргументами.
SELECT name, price, IFNULL( price, 0 ) FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
Шайба 20 20
Винт 50 50
Гайка 30 30
Пружина <NULL> 0
Code language: SQL (Structured Query Language) (sql)
NULLIF
NULLIF( <значение 1>, <значение 2> )
Возвращает NULL, если два выражения эквивалентны. В противном случае возвращает первое выражение.
Можно использовать для исключения определённых значений из выборки. Например, этот запрос покажет количество всех деталей, которые не называются “Гайка”:
SELECT count( NULLIF(name,'Гайка') ) FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
3
Code language: SQL (Structured Query Language) (sql)
LIKE
LIKE( <шаблон>,<источник данных> [,<символ исключения>] )
Функция like() используется для реализации выражения” Y LIKE X [ESCAPE Z]”. Если присутствует необязательное ESCAPE-предложение, то функция like() вызывается с тремя аргументами. В противном случае он вызывается только с двумя аргументами.
☝ Параметры X и Y реверсируются в функции like() относительно оператора LIKE.
SELECT name FROM item WHERE LIKE('%а%',name)
Code language: SQL (Structured Query Language) (sql)
Результат
Шайба
Гайка
Пружина
Code language: SQL (Structured Query Language) (sql)
Агрегатные функции
Функция | Описание |
---|---|
AVG | Возвращает среднее значение поля (выражения). |
COUNT | Возвращает количество поля (выражения) |
MAX | Возвращает максимальное значение поля (выражения) |
MIN | Возвращает минимальное значение поля (выражения) |
SUM | Возвращает суммированное значение поля (выражения) |
GROUP_CONCAT | возвращает строку, которая представляет собой объединение всех значений входного выражения, отличных от NULL, разделенных разделителем. |
Агрегатные функции работают не с отдельным аргументом, а с набором данных, возвращаемым SQL-запросом.
<Функция> [<Префикс> ] <Набор данных>
Перед выражением, определяющим набор данных, можно использовать префикс:
- ALL – вычислять для всех значений. Используется по умолчанию
- DISTINCT – вычислять только для уникальных значений.
☝ Если в запросе присутствуют как обычные поля, так и агрегатные функции, то необходимо применять группировку ( см. главу “Группировка данных” ).
AVG
AVG( [ALL | DISTINCT] <выражение>)
Вычисляет среднее значение для элементов, которые не равны NULL.
SELECT AVG(price) FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
33.33
Code language: SQL (Structured Query Language) (sql)
☝ Функция вернёт значение NULL только в случае, если все значения в наборе данных будут NULL
COUNT
COUNT( [ALL | DISTINCT] <выражение>)
Вычисляет количество элементов.
SELECT COUNT(*) FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
4
Code language: SQL (Structured Query Language) (sql)
📝 Для подсчета количества записей в запросе обычно используется Count(*) как универсальная функция.
MAX
MAX( [ALL | DISTINCT] <выражение>)
Вычисляет максимальное значение в выборке.
Пример использования функции для поиска записи с максимальным значением в одном из полей.
SELECT name, price FROM item
WHERE price = (SELECT MAX(price) FROM item)
Code language: SQL (Structured Query Language) (sql)
Результат
Винт 50
Code language: SQL (Structured Query Language) (sql)
📝 Использование префикса не меняет смысла запроса, а потому бесполезно.
MIN
MIN( [ALL | DISTINCT] <выражение>)
Вычисляет максимальное значение в выборке.
SELECT name, price FROM item
WHERE price = (SELECT MAX(price) FROM item)
Code language: SQL (Structured Query Language) (sql)
Результат
Шайба 20
Code language: SQL (Structured Query Language) (sql)
📝 Использование префикса не меняет смысла запроса, а потому бесполезно.
SUM
SUM( [ALL | DISTINCT] <выражение>)
Вычисляет сумму значений в выборке.
SELECT SUM(price) FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
100
Code language: SQL (Structured Query Language) (sql)
📝 Если все значения в выборке NULL, то результат – NULL. Если все значения в выборке, отличные от NULL, целочисленные, то результат будет целочисленный. Во всех остальных случаях результат будет с плавающей запятой.
GROUP_CONCAT
GROUP_CONCAT( <выражение>,<разделитель>)
Если разделитель не указать, то значения будут разделены запятыми.
SELECT GROUP_CONCAT(name) FROM item
Code language: SQL (Structured Query Language) (sql)
Результат
Шайба,Винт,Гайка,Пружина
Code language: SQL (Structured Query Language) (sql)
Прочие функции
Функция | Описание |
---|---|
CAST | Преобразование аргумента к указанному типу. см. Правила описания типов. |
CHANGES | Возвращает количество строк базы данных, которые были изменены, вставлены или удалены последним завершенным INSERT, DELETE, или UPDATE операторами, исключая операторы в низкоуровневых триггерах. |
LAST_INSERT_ROWID | Возвращает значение уникального идентификатора записи, добавленной последней командой INSERT в текущем сеансе работы с базой. |
TOTAL_CHANGES | Функция total_changes () возвращает количество изменений строк, вызванных инструкциями INSERT, UPDATE или DELETE с момента открытия текущего соединения с базой данных. |
TYPEOF | Функция typeof(X) возвращает строку, указывающую тип данных выражения X: “null”, “integer”, “real”, “text”, или “blob”. |
SQLITE_VERSION | Возвращает версию SQLite, которую вы используете. |
UNICODE | Функция unicode(X) возвращает числовую позицию unicode, соответствующую первому символу строки X. Если аргумент unicode (X) не является строкой, то результат не определен. |
CAST
CAST( <выражение> AS <тип>)
Выражение CAST(X AS Y) позволяет преобразовать (конвертировать) данные X в данные другого типа (точнее, класса хранения, которые описаны в гл. 1.4) Y. Если значение X равно NULL, результат тоже будет NULL. Преобразование возможно в следующие типы данных:
Тип | Преобразование |
---|---|
NONE | Преобразование значения без указания типа приводит к преобразованию в тип BLOB. Преобразование в BLOB состоит из первого приведения значения к TEXT в кодировке соединения с базой данных, а затем интерпретации результирующей последовательности байтов в виде BLOB-объекта, а не в виде текста. |
TEXT | Преобразование BLOB к тексту происходит так что последовательности байтов, составляющих BLOB объект, интерпретируется как текст, закодированный с использованием кодировки базы данных. Преобразование INTEGER или REAL в TEXT приводит к отображению результата как если бы применялась функция PRINTF(), за исключением того что TEXT будет в кодировке соединения с базой данных. |
REAL | Если преобразуемое значение BLOB, тогда оно сначала преобразуется в TEXT, а затем происходит дальнейшая конвертация. Если TEXT преобразовывается в REAL, используется максимальное количество символов в начале тексте, способных быть представленными как число. Остальные символы игнорируются. Начальные пробелы также игнорируются. Если в начале текста нет символов, которые можно представить как число, тогда возвращается значение 0.0 |
INTEGER | Если преобразуемое значение BLOB, тогда оно сначала преобразуется в TEXT, а затем происходит дальнейшая конвертация. Если TEXT преобразовывается в INTEGER, используется максимальное количество символов в начале тексте, способных быть представленными как число. Остальные символы игнорируются. Начальные пробелы также игнорируются. Если в начале текста нет символов, которые можно представить как число, тогда возвращается значение 0. Если значение превышает +9223372036854775807, тогда возвращается 9223372036854775807 и соответственно, если меньше чем -9223372036854775808, тогда возвращается -9223372036854775808.Если преобразуемое значение выглядит как число с плавающей точкой с показателем степени, тогда степень игнорируется. Например, CAST ‘123e+5’ AS INTEGER вернет 123, а не 12300000. |
NUMERIC | Преобразование TEXT или BLOB значения в NUMERIC приводит сначала к преобразованию в REAL, а затем преобразованию в INTEGER, если только преобразование из REAL в INTEGER происходит без потерь и обратимо. В этом и есть отличие от преобразования в INTEGER – преобразование в NUMERIC происходит только в случае если нет потерь при преобразовании. |
SELECT CAST('1+1' as TEXT)
UNION
SELECT CAST(1+1 as TEXT)
UNION
SELECT CAST(1/0.1 as TEXT)
Code language: SQL (Structured Query Language) (sql)
Результат
1+1
2
10.0
Code language: SQL (Structured Query Language) (sql)
📝 Функция CAST часто используется для приведения данных к одному типу для объединения их в запросах, использующих команду UNION
CHANGES
CHANGES()
Функция changes() function возвращает количество строк базы данных, которые были изменены, вставлены или удалены последним завершенным INSERT, DELETE, или UPDATE операторами, исключая операторы в низкоуровневых триггерах.
SELECT CHANGES()
Code language: SQL (Structured Query Language) (sql)
Результат
1
Code language: SQL (Structured Query Language) (sql)
📝 Данная функция представляет интерес при анализе работы запросов.
LAST_INSERT_ROWID
LAST_INSERT_ROWID()
Возвращает идентификатор последней записи, вставленной в базу данных, если таблица, в которую была вставлена запись, содержит поле с автоинкрементом.
SELECT LAST_INSERT_ROWID()
Code language: SQL (Structured Query Language) (sql)
Результат
4
Code language: SQL (Structured Query Language) (sql)
📝 В My Visual Database все таблицы содержат ключевое поле с автоинкрементом.
Можно использовать в последовательности SQL-команд, например, если в последующем запросе необходимо сослаться на только что созданную запись.
Если добавлений не было, функция возвращает нулевое значение.
В скриптах имеется аналогичная функция Last_Insert_ID().
TOTAL_CHANGES
TOTAL_CHANGES()
Функция total_changes () возвращает количество изменений строк, вызванных инструкциями INSERT, UPDATE или DELETE с момента открытия текущего соединения с базой данных.
SELECT TOTAL_CHANGES()
Code language: SQL (Structured Query Language) (sql)
Результат
21
Code language: SQL (Structured Query Language) (sql)
📝 На практике эту функцию можно использовать для определения, вносились ли в базу какие-либо изменения.
TYPEOF
TYPEOF( <выражение> )
Функция typeof(X) возвращает строку, указывающую тип данных выражения X: “null”, “integer”, “real”, “text”, или “blob”.
SELECT TYPEOF('Что это?')
Code language: SQL (Structured Query Language) (sql)
Результат
text
Code language: SQL (Structured Query Language) (sql)
SQLITE_VERSION
SQLITE_VERSION()
Функция sqlite_version() возвращает строку версии для запущенной библиотеки SQLite.
SELECT SQLITE_VERSION()
Code language: SQL (Structured Query Language) (sql)
Результат
3.8.3
Code language: SQL (Structured Query Language) (sql)
UNICODE
UNICODE( <строка> )
Функция unicode(X) возвращает числовую позицию unicode, соответствующую первому символу строки X. Если аргумент unicode (X) не является строкой, то результат не определен.
SELECT UNICODE('My Visual Dataaabase')
Code language: SQL (Structured Query Language) (sql)
Результат
77
Code language: SQL (Structured Query Language) (sql)
📝 За рамками данного описания остались несколько функций ( likelihood(), likely(), load_extension(), unlikely(), zeroblob() ) о которых вы можете почитать в документации по SQLite.
А теперь немного практики.