Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье.
Агрегатные функции
Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций:
AVGВычисляет среднее арифметическое значение данных, содержащихся в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.
MIN и MAXОпределяют максимальное и минимальное значение из всех значений данных, содержащихся в столбце. Значения могут быть числовыми, строковыми или временными (дата/время).
SUMВычисляет общую сумму значений в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.
COUNTПодсчитывает количество значений, отличных от null в столбце. Функция count(*) является единственной агрегатной функцией, которая не выполняет вычисления над столбцами. Эта функция возвращает количество строк (независимо от того, содержат ли отдельные столбцы значения null).
COUNT_BIGАналогична функции count, с той разницей, что возвращает значение данных типа BIGINT.
Использование обычных агрегатных функций в инструкции SELECT будет рассматриваться в одной из следующих статей.
Скалярные функции
Скалярные функции Transact-SQL используются в создании скалярных выражений. (Скалярная функция выполняет вычисления над одним значением или списком значений, тогда как агрегатная функция выполняет вычисления над группой значений из нескольких строк.) Скалярные функции можно разбить на следующие категории:
числовые функции;
функции даты;
строковые функции;
системные функции;
функции метаданных.
Эти типы функций рассматриваются в последующих разделах.
Числовые функции
Числовые функции языка Transact-SQL - это математические функции для модифицирования числовых значений. Список числовых функций и их краткое описание приводится в таблице ниже:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| ABS | ABS(n) | Возвращает абсолютное значение (т. е. отрицательные значения возвращаются, как положительные) числового выражения n. |
SELECT ABS(-5.320) -- Вернет 5.320 SELECT ABS(8.90) -- Вернет 8.90 |
| ACOS, ASIN, ATAN, ATN2 | ACOS(n), ASIN(n), ATAN(n), ATN2(n, m) | Обратные тригонометрические функции, вычисляющие арккосинус, арксинус, арктангенс значения n (для ATN2 вычисляется арктангенс n/m). Исходные значения n, m и результат имеют тип данных FLOAT. |
|
| COS, SIN, TAN, COT | COS(n), SIN(n), TAN(n), COT(n) | Тригонометрические функции, вычисляющие косинус, синус, тангенс, котангенс значения n. Результат имеет тип данных FLOAT. |
|
| DEGREES, RADIANS | DEGREES(n), RADIANS(n) | Функция DEGREES преобразует радианы в градусы, RADIANS соответственно наоборот. |
SELECT DEGREES(PI() / 4) -- Вернет 45 SELECT COS(RADIANS(60.0)) -- Вернет 0.5 |
| CEILING | CEILING(n) | Округляет число до большего целого значения. |
SELECT CEILING(-5.320) -- Вернет -5 SELECT CEILING(8.90) -- Вернет 9 |
| ROUND | ROUND(n, p, [t]) | Округляет значение n с точностью до p. Когда аргумент p положительное число, округляется дробная часть числа n, а когда отрицательное - целая часть. При использовании необязательного аргумента t, число n не округляется, а усекается (т.е. округляется в меньшую сторону). |
SELECT ROUND(5.3208, 3) -- Вернет 5.3210 SELECT ROUND(125.384, -1) -- Вернет 130.000 SELECT ROUND(125.384, -1, 1) -- Вернет 120.000 |
| FLOOR | FLOOR(n) | Округляет до меньшего целого значения. |
SELECT FLOOR(5.88) -- Вернет 5 |
| EXP | EXP(n) | Вычисляет значение e n . |
|
| LOG, LOG10 | LOG(n), LOG10(n) | LOG(n) - вычисляет натуральный логарифм (т.е. с основанием e) числа n, LOG10(n) - вычисляет десятичный (с основанием 10) логарифм числа n. |
|
| PI | PI() | Возвращает значение π (3,1415). |
|
| POWER | POWER(x, y) | Вычисляет значение x y . |
|
| RAND | RAND() | Возвращает произвольное число типа FLOAT в диапазоне значений между 0 и 1. |
|
| ROWCOUNT_BIG | ROWCOUNT_BIG() | Возвращает количество строк таблицы, которые были обработаны последней инструкцией Transact-SQL, исполненной системой. Возвращаемое значение имеет тип BIGINT. |
|
| SIGN | SIGN(n) | Возвращает знак значения n в виде числа: +1, если положительное, -1, если отрицательное. |
|
| SQRT, SQUARE | SQRT(n), SQUARE(n) | SQRT(n) - вычисляет квадратный корень числа n, SQUARE(n) - возвращает квадрат аргумента n. |
Функции даты
Функции даты вычисляют соответствующие части даты или времени выражения или возвращают значение временного интервала. Поддерживаемые в Transact-SQL функции даты и их краткое описание приводятся в таблице ниже:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| GETDATE | GETDATE() | Возвращает текущую системную дату и время. |
SELECT GETDATE() |
| DATEPART | DATEPART (item, date) | Возвращает указанную в параметре item часть даты date в виде целого числа. |
Вернет 1 (Январь) SELECT DATEPART(month, "01.01.2012") -- Вернет 4 (Wednesday) SELECT DATEPART(weekday, "02.01.2012") |
| DATENAME | DATENAME (item, date) | Возвращает указанную в параметре item часть даты date в виде строки символов. |
Вернет January SELECT DATENAME(month, "01.01.2012") -- Вернет Wednesday SELECT DATENAME(weekday, "02.01.2012") |
| DATEDIFF | DATEDIFF (item, dat1, dat2) | Вычисляет разницу между двумя частями дат dat1 и dat2 и возвращает целочисленный результат в единицах, указанных в аргументе item. |
Вернет 19 (19 лет промежуток между датами) SELECT DATEDIFF(year, "01.01.1990", "01.01.2010") -- Вернет 7305 (7305 дней промежуток между датами) SELECT DATEDIFF(day, "01.01.1990", "01.01.2010") |
| DATEADD | DATEADD (item, n, date) | Прибавляет n-е количество единиц, указанных в аргументе item к указанной дате date. (Значение аргумента n также может быть отрицательным.) |
Прибавит 3 дня к текущей дате SELECT DATEADD(day, 3, GETDATE()) |
Строковые функции
Строковые функции манипулируют значениями столбцов, которые обычно имеют символьный тип данных. Поддерживаемые в Transact-SQL строковые функции и их краткое описание приводятся в таблице ниже:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| ASCII, UNICODE | ASCII(char), UNICODE(char) | Преобразовывает указанный символ в соответствующее целое число кода ASCII. |
SELECT ASCII("W") -- 87 SELECT UNICODE("ю") -- 1102 |
| CHAR, NCHAR | CHAR(int), NCHAR(int) | Преобразовывает код ASCII (или Unicode если NCHAR) в соответствующий символ. |
SELECT CHAR(87) -- "W" SELECT NCHAR(1102) -- "ю" |
| CHARINDEX | CHARINDEX (str1, str2) | Возвращает начальную позицию вхождения подстроки str1 в строку str2. Если строка str2 не содержит подстроки str1, возвращается значение 0 |
Вернет 5 SELECT CHARINDEX ("морф", "полиморфизм") |
| DIFFERENCE | DIFFERENCE (str1, str2) | Возвращает целое число от 0 до 4, которое является разницей между значениями SOUNDEX двух строк str1 и str2. Метод SOUNDEX возвращает число, которое характеризует звучание строки. С помощью этого метода можно определить подобно звучащие строки. Работает только для символов ASCII. |
Вернет 2 SELECT DIFFERENCE ("spelling", "telling") |
| LEFT, RIGHT | LEFT (str, length), RIGHT (str, length) | Возвращает количество первых символов строки str, заданное параметром length для LEFT и последние length символов строки str для функции RIGHT. |
DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "Синх" SELECT LEFT(@str, 4) -- Вернет "зация" SELECT RIGHT(@str, 5) |
| LEN | LEN(str) | Возвращает количество символов (не количество байт) строки str, указанной в аргументе, включая конечные пробелы. |
|
| LOWER, UPPER | LOWER(str), UPPER(str) | Функция LOWER преобразовывает все прописные буквы строки str1 в строчные. Входящие в строку строчные буквы и иные символы не затрагиваются. Функция UPPER преобразовывает все строчные буквы строки str в прописные. |
DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "СИНХРОНИЗАЦИЯ" SELECT UPPER(@str) -- Вернет "синхронизация" SELECT LOWER(@str) |
| LTRIM, RTRIM | LTRIM(str), RTRIM(str) | Функция LTRIM удаляет начальные пробелы в строке str, RTRIM соответственно удаляет пробелы в конце строки. |
|
| QUOTENAME | QUOTENAME (char_string) | Возвращает строку в кодировке Unicode с добавленными ограничителями, чтобы преобразовать строку ввода в действительный идентификатор с ограничителями. |
DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "[Синхронизация]" SELECT QUOTENAME(@str) |
| PATINDEX | PATINDEX (%p%, expr) | Возвращает начальную позицию первого вхождения шаблона p в заданное выражение expr, или ноль, если данный шаблон не обнаружен. |
Вернет 4 SELECT PATINDEX("%хро%", "Синхронизация") |
| REPLACE | REPLACE (str1, str2, str3) | Заменяет все вхождения подстроки str2 в строке str1 подстрокой str3. |
Вернет "Десинхронизация" SELECT REPLACE("Синхронизация", "Синхр", "Десинхр") |
| REPLICATE | REPLICATE (str, i) | Повторяет i раз строку str. |
Вернет "aBaBaBaBaB" SELECT REPLICATE("aB", 5) |
| REVERSE | REVERSE (str) | Выводит строку str в обратном порядке. |
Вернет "яицазинорхниС" SELECT REVERSE("Синхронизация") |
| SOUNDEX | SOUNDEX (str) | Возвращает четырехсимвольный код soundex, используемый для определения похожести двух строк. Работает только для символов ASCII. |
|
| SPACE | SPACE (length) | Возвращает строку пробелов длиной, указанной в параметре length. Аналог REPLICATE(" ", length). |
|
| STR | STR (f[, len[, d]]) | Преобразовывает заданное выражение с плавающей точкой f в строку, где len - длина строки, включая десятичную точку, знак, цифры и пробелы (по умолчанию равно 10), а d - число разрядов дробной части, которые нужно возвратить. |
Вернет "3.14" SELECT STR (3.1415, 4, 2) |
| STUFF | STUFF (str1, a, length, str2) | Удаляет из строки str1 length-символов, начиная с позиции a, и вставляет на их место строку str2. |
Note in a book SELECT STUFF("Notebook", 5, 0," in a ") -- Handbook SELECT STUFF("Notebook", 1, 4, "Hand") |
| SUBSTRING | SUBSTRING (str1, a, length) | Извлекает из строки str, начиная с позиции a, подстроку длиной length. |
Системные функции
Системные функции языка Transact-SQL предоставляют обширную информацию об объектах базы данных. Большинство системных функций использует внутренний числовой идентификатор (ID), который присваивается каждому объекту базы данных при его создании. Посредством этого идентификатора система может однозначно идентифицировать каждый объект базы данных.
В следующей таблице приводятся некоторые из наиболее важных системных функций вместе с их кратким описанием:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| CAST | CAST (w AS type [(length)] | Преобразовывает выражение w в указанный тип данных type (если это возможно). Аргумент w может быть любым действительным выражением. |
Вернет 3 SELECT CAST (3.1258 AS INT) |
| COALESCE | COALESCE (a1, a2) | Возвращает первое значение выражения из списка выражений a1, a2, ..., которое не является значением null. |
|
| COL_LENGTH | COL_LENGTH (obj, col) | Возвращает длину столбца col объекта базы данных (таблицы или представления) obj. |
Вернет 4 SELECT COL_LENGTH ("Employee", "Id") |
| CONVERT | CONVERT (type[(length)], w) | Эквивалент функции CAST, но аргументы указываются по-иному. Может применяться с любым типом данных. |
|
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | Возвращает текущие дату и время. |
|
| CURRENT_USER | CURRENT_USER | Возвращает имя текущего пользователя. |
|
| DATALENGTH | DATALENGTH (z) | Возвращает число байтов, которые занимает выражение z. |
Этот запрос возвращает длину каждого поля SELECT DATALENGTH(FirstName) FROM Employee |
| GETANSINULL | GETANSINULL ("dbname") | Возвращает 1, если использование значений null в базе данных dbname отвечает требованиям стандарта ANSI SQL. |
|
| ISNULL | ISNULL (expr, value) | Возвращает значение выражения expr, если оно не равно NULL; в противном случае возвращается значение value. |
|
| ISNUMERIC | ISNUMERIC (expr) | Определяет, имеет ли выражение expr действительный числовой тип. |
|
| NEWID | NEWID() | Создает однозначный идентификационный номер ID, состоящий из 16-байтовой двоичной строки, предназначенной для хранения значений типа данных UNIQUEIDENTIFIER. |
|
| NEWSEQUENTIALID | NEWSEQUENTIALID() | Создает идентификатор GUID, больший, чем любой другой идентификатор GUID, созданный ранее этой функцией на указанном компьютере. (Эту функцию можно использовать только как значение по умолчанию для столбца.) |
|
| NULLIF | NULLIF (expr1, expr2) | Возвращает значение null, если значения выражений expr1 и expr2 одинаковые. |
Запрос возвращает NULL для проекта, -- у которого Number = "p1" SELECT NULLIF(Number, "p1") FROM Project |
| SERVERPROPERTY | SERVERPROPERTY (propertyname) | Возвращает информацию о свойствах сервера базы данных. |
|
| SYSTEM_USER | SYSTEM_USER | Возвращает ID текущего пользователя. |
|
| USER_ID | USER_ID () | Возвращает идентификатор пользователя username. Если пользователь не указан, то возвращается идентификатор текущего пользователя. |
|
| USER_NAME | USER_NAME () | Возвращает имя пользователя с указанным идентификатором id. Если идентификатор не указан, то возвращается имя текущего пользователя. |
Функции метаданных
По большому счету, функции метаданных возвращают информацию об указанной базе данных и объектах базы данных. В таблице ниже приводятся некоторые из наиболее важных функций метаданных вместе с их кратким описанием:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| COL_NAME | COL_NAME (tab_id, col_id) | Возвращает имя столбца с указанным идентификатором col_id таблицы с идентификатором tab_id. |
Вернет имя столбца "LastName" SELECT COL_NAME (OBJECT_ID("Employee"), 3) |
| COLUMNPROPERTY | COLUMNPROPERTY (id, col, property) | Возвращает информацию об указанном столбце. |
Вернет значение свойства PRECISION -- для столбца Id таблицы Employee SELECT COLUMNPROPERTY (OBJECT_ID("Employee"), "Id", "precision") |
| DATABASEPROPERTY | DATABASEPROPERTY (database, property) | Возвращает значение свойства property базы данных database. |
Вернет значение свойства IsNullConcat -- для базы данных SampleDb SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat") |
| DB_ID | DB_ID () | Возвращает идентификатор базы данных db_name. Если имя базы данных не указано, то возвращается идентификатор текущей базы данных. |
|
| DB_NAME | DB_NAME () | Возвращает имя базы данных, имеющей идентификатор db_id. Если идентификатор не указан, то возвращается имя текущей базы данных. |
|
| INDEX_COL | INDEX_COL (table, i, no) | Возвращает имя индексированного столбца таблицы table. Столбец указывается идентификатором индекса i и позицией no столбца в этом индексе. |
|
| INDEXPROPERTY | INDEXPROPERTY (obj_id, index_name, property) | Возвращает свойства именованного индекса или статистики для указанного идентификационного номера таблицы, имя индекса или статистики, а также имя свойства. |
|
| OBJECT_NAME | OBJECT_NAME (obj_id) | Возвращает имя объекта базы данных, имеющего идентификатор obj_id. |
SELECT OBJECT_NAME(245575913); |
| OBJECT_ID | OBJECT_ID (obj_name) | Возвращает идентификатор объекта obj_name базы данных. |
Вернет 245575913 - ID таблицы Employee SELECT OBJECT_ID("Employee") |
| OBJECTPROPERTY | OBJECTPROPERTY (obj_id, property) | Возвращает информацию об объектах из текущей базы данных. |
Мы продолжаем изучение языка запросов SQL, и сегодня мы с Вами будем разговаривать о строковых функциях SQL . Мы рассмотрим основные и часто используемые строковые функции, такие как: LOWER, LTRIM, REPLACE и другие, все рассматривать мы будем, конечно же, на примерах.
SELECT name || surname AS FIO FROM table
Или чтобы отделить пробелом введите
SELECT name || " " || surname AS FIO FROM tableт.е. две вертикальные черты объединяют два столбца в один, а чтобы отделить их пробелом я поставил между ними пробел (можно использовать любой символ, например тире или двоеточие ) в апострофах и объединил также двумя вертикальными чертами (в Transact-SQL вместо двух вертикальных черточек используется знак + ).
Функция INITCAP
Дальше идет также очень полезная функция, INITCAP – которая возвращает значение в строке, в которой каждое слово начинается с заглавной буквы, а продолжается маленькими. Это нужно для того, если у Вас в той или иной колонке не соблюдают правила заполнения и для того чтобы вывести все это дело в красивом виде можно использовать данную функцию, например, у Вас в таблице записи в колонке name следующего вида: ИВАН иванов или петр петров, Вы применяете данную функцию.
SELECT INITCAP (name) AS FIO FROM table
И у Вас получится вот так.
Функция UPPER
Похожая функция, только возвращает все символы с заглавной буквы, это UPPER .
SELECT UPPER (name) AS FIO FROM table
- name – название колонки;
- 20 – количество знаков (длина поля );
- ‘-‘ – символ, которым нужно дополнить до необходимого количества знаков.
Функция RPAD
Сразу рассмотрим обратную функцию. RPAD – действие и синтаксис тот же что и у LPAD, только дополняются символы справа (в LPAD слева ).
SELECT RPAD (name, 20, "-") AS name FROM table
| Иван—————- |
| Сергей————— |
Функция LTRIM
Далее идет тоже в некоторых случаях полезная функция, LTRIM – эта функция удаляет крайние левые символы, которые Вы укажите. Например, у Вас в базе есть колонка «город», в которой город указан в виде «г.Москва», а также есть города которые указанны в виде просто «Москва». Но Вам нужно вывести отчет только в виде «Москва» без «г.», но как это сделать, если есть и такие и такие? Вы просто указываете своего рода шаблон «г.» и если крайние левые символы начинаются с «г.», то эти символы просто не будут выводиться.
SELECT LTRIM (city, "г.") AS gorod FROM table
Данная функция просматривает символы слева, если символов по шаблону нет в начале строки, то она возвращает исходное значение ячейки, а если есть, то удаляет их.
Функция RTRIM
Также давайте сразу рассмотрим обратную функцию. RTRIM – то же самое что и LTRIM только символы ищутся справа.
Примечание! В Transact-SQL функции RTRIM и LTRIM удаляют пробелы справа и слева соответственно.
Функция REPLACE
Теперь рассмотрим такую интересную функцию как REPLACE – она возвращает строку, в которой все совпадения символов, заменяются на Ваши символы, которые Вы укажите. Для чего ее можно использовать, например, у Вас в базе есть колонки, в которых встречаются некие разделительные символы, допустим «/». Например, Иван/Иванов, а Вам хотелось бы вывести Иван-Иванов, то напишите
SELECT REPLACE (name, "/", "-") FROM table
и у Вас произойдет замена символов.
Данная функция заменяет только полное совпадение символов, если например Вы укажите «—» т.е. три тире она и будет искать только три тире, а каждое отдельное тире заменять не будет, в отличие от следующей функции.
Функция TRANSLATE
TRANSLATE – строковая функция, которая заменяет все символы в строке, на те символы, которые Вы укажите. Исходя из названия функции, можно догадаться, что это полный перевод строки. Отличие данной функции от REPLACE в том, что она заменяет каждый символ, который Вы укажите, т.е. у Вас есть три символа, допустим абв и с помощью TRANSLATE Вы его можете заменить на abc таким образом у Вас а=a, б=b, в=c и по такому принципу будут заменяться все совпадения символов. А если Вы заменяли с помощью REPLACE, то у Вас искалось только полное совпадение символов абв расположенных подряд.
Функция SUBSTR
SUBSTR – данная функция, возвращает только тот диапазон символов, который Вы укажите. Другими словами, допустим, строка из 10 символов, а Вам все десять не нужны, а допустим, нужны только 3-8 (с третьего по восьмой ). С помощью данной функции Вы легко можете это сделать. Например, у Вас в базе есть какой-нибудь идентификатор, фиксированной длинны (типа: AA-BB-55-66-CC) и каждая комбинация символов что-то означает. И в один прекрасный момент Вам сказали вывести только 2 и 3 комбинацию символов, для этого вы пишите запрос следующего вида.
SELECT SUBSTR (ident, "4", "8") FROM table
т.е. мы выводим все символы, начиная с 4 и заканчивая 8, и после этого запроса у Вас выведется вот это:
Функция LENGTH – длина строки
Следующая функция также может пригодиться, это LENGTH – которая просто на всего считает количество символов в строке. Например, Вам нужно узнать, сколько символов в каждой ячейки столбца допустим «name», таблица следующего вида.
SELECT LENGTH (name) FROM tableпосле этого запроса Вы получите вот это.
| 4 |
| 6 |
| 7 |
Вот мы с Вами и рассмотрели основные строковые функции SQL. В следующих статьях мы продолжим изучение SQL.
Как и в большинстве языков программирования, в SQL существуют функции для обработки данных. Стоит отметить, что в отличие от SQL-операторов, функции не стандартизованы для всех видов СУБД, то есть для выполнения одних и тех же операции над данными, разные СУБД имеют свои собственные имена функций. Это означает, что код запроса написан в одной СУБД может не работать в другой, и это нужно учитывать в дальнейшем. Больше всего это касается функций для обработки текстовых значений, преобразования типов данных и манипуляций над датами.
Обычно СУБД поддерживается стандартный набор типов функций, а именно:
- Текстовые функции, которые используются для обработки текста (выделение части символов в тексте, определение длины текста, перевод символов в верхний или нижний регистр...)
- Числовые функции. Используются для выполнения математических операций над числовыми значениями
- Функции даты и времени (осуществляют манипулирования датой и временем, рассчитывают период между датами, проверяют даты на корректность и т.п.)
- Статистические функции (для вычисления максимальных /минимальных значений, средних значений, подсчет количества и суммы...)
- Системные функции (предоставляют разного рода служебную информацию о СУБД, пользователе и др..).
1. Функции SQL для обработки текста
Реализация SQL в СУБД Access имеет следующие функции для обработки текста:
Переведем названия товаров в верхний регистр с помощью функции UCase() :
SELECT Product, UCase(Product) AS Product_UCase FROM Sumproduct
Выделим первые три символа в тексте с помощью функции LEFT() :
SELECT Product, LEFT(Product, 3) AS Product_LEFT FROM Sumproduct
2. Функции SQL для обработки чисел
Функции обработки чисел предназначены для выполнения математических операций над числовыми данными. Эти функции предназначены для алгебраических и геометрических вычислений, поэтому они используются значительно реже функций обработки даты и времени. Однако числовые функции наиболее стандартизированными для всех версий SQL. Давайте взглянем на перечень числовых функций:
Мы привели лишь несколько основных функций, однако вы всегда можете обратиться к документации вашей СУБД, чтобы увидеть полный перечень функций, которые поддерживаются с их подробным описанием.
Например, напишем запрос для получения корня квадратного для чисел в столбце Amount с помощью функции SQR() :
SELECT Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct
3. Функции SQL для обработки даты и времени
Функции манипулирования датой и временем являются одними из важнейших и часто используемых функций SQL. В базах данных значения дат и времени хранятся в специальном формате, поэтому их невозможно использовать напрямую без дополнительной обработки. Каждая СУБД имеет свой набор функций для обработки дат, что, к сожалению, не позволяет переносить их на другие платформы и реализации SQL.
Список некоторых функций для обработки даты и времени в СУБД Access :
Посмотрим на примере как работает функция DatePart() :
SELECT Date1, DatePart("m", Date1) AS Month1 FROM Sumproduct
Функция DatePart () имеет дополнительный параметр, который нам позволяет отобразить необходимую часть даты. В примере мы использовали параметр "m" , который отображает номер месяца (таким же образом мы можем отразить год - "yyyy" , квартал - "q " , день - " d " , неделю - " w " , час - " h " , минуты - "n" , секунды - "s" и т.д.).
4. Статистические функции SQL
Статистические функции помогают нам получить готовые данные без их выборки. SQL-запросы с этими функциями часто используются для анализа и создания различных отчетов. Примером таких выборок может быть: определение количества строк в таблице, получение суммы значений по определенному полю, поиск наибольшего /наименьшего или среднего значения в указанном столбце таблицы. Также отметим, что статистические функции поддерживаются всеми СУБД без особых изменений в написании.
Список статистических функций в СУБД Access :
COUNT() :
SELECT COUNT(*) AS Count1 FROM Sumproduct - возвращает количество всех строк в таблице
SELECT COUNT(Product) AS Count2 FROM Sumproduct - возвращает количество всех непустых строк в поле Product
Мы намеренно удалили одно значение в столбце Product , чтобы показать разницу в работе двух запросов.
Примеры использования функции SUM() :
SELECT SUM(Quantity) AS Sum1 FROM Sumproduct WHERE Month = "April "
Данным запросу мы отразили общее количество проданного товара в апреле.
SELECT SUM(Quantity*Amount) AS Sum2 FROM Sumproduct
Как видим, в статистических функциях мы можем осуществлять вычисления над несколькими столбцами с использованием стандартных математических операторов.
Пример использования функции MIN() :
SELECT MIN(Amount) AS Min1 FROM Sumproduct
Пример использования функции MAX() :
SELECT MAX(Amount) AS Max1 FROM Sumproduct
Пример использования функции AVG() :
SELECT AVG(Amount) AS Avg1 FROM Sumproduct
В этом учебном пособии вы узнаете, как создавать и удалять функции в SQL Server
(Transact-SQL) с помощью синтаксиса и примеров.
Описание
В SQL Server функция представляет собой хранимую программу, в которую вы можете передавать параметры и возвращать значение.
Create Function
Вы можете создавать свои собственные функции в SQL Server (Transact-SQL). Давайте рассмотрим подробнее.
Синтаксис
Синтаксис Functions в SQL Server (Transact-SQL):
CREATE FUNCTION function_name
([ @parameter [ AS ] datatype
[ = default ] [ READONLY ]
, @parameter [ AS ] datatype
[ = default ] [ READONLY ] ]
RETURNS return_datatype
[ WITH { ENCRYPTION
| SCHEMABINDING
| RETURNS NULL ON NULL INPUT
| CALLED ON NULL INPUT
| EXECUTE AS Clause ]
[ AS ]
BEGIN
executable_section
RETURN return_value
END;
Параметры или аргументы
schema_name
— имя схемы, которой принадлежит эта функция.
function_name
— наименование функции в SQL Server.
@parameter
— один или несколько параметров, которые передаются в функцию.
type_schema_name
— схема, которая владеет типом данных, если это применимо.
datatype
— тип данных для @parameter
.
default
— значение по умолчанию для назначения параметру @parameter
.
READONLY
— это означает, что @parameter
не может быть перезаписана функцией.
return_datatype
— тип данных возвращаемого значения функции.
ENCRYPTION
— это означает, что источник для функции не будет сохранен как обычный текст в системных представлениях SQL Server.
SCHEMABINDING
— это означает, что базовые объекты не могут быть изменены, чтобы влиять на функцию.
RETURNS NULL ON NULL INPUT
— это означает, что функция вернет NULL, если любые параметры имеют значение NULL, без необходимости выполнять функцию.
CALL ON NULL INPUT
— это означает, что функция будет выполняться, даже если любые параметры имеют NULL.
EXECUTE AS
— устанавливает контекст безопасности для выполнения функции.
return_value
— значение, возвращаемое функцией.
Пример
Рассмотрим пример создания функции в SQL Server (Transact-SQL).
Ниже приведен простой пример функции:
Transact-SQL
CREATE FUNCTION ReturnSite (@site_id INT) RETURNS VARCHAR(50) AS BEGIN DECLARE @site_name VARCHAR(50); IF @site_id < 10 SET @site_name = "yandex.com"; ELSE SET @site_name = "google.com"; RETURN @site_name; END;
CREATE FUNCTION ReturnSite (@ site_id INT ) RETURNS VARCHAR (50 ) BEGIN DECLARE @ site_name VARCHAR (50 ) ; IF @ site_id < 10 SET @ site_name = "yandex.com" ; ELSE SET @ site_name = "google.com" ; RETURN @ site_name ; END ; |
Эта функция называется ReturnSite
. Она имеет один параметр, называемый @site_id
, который является типом данных INT. Функция возвращает значение VARCHAR (50), указанное в предложении RETURNS.
Затем вы можете ссылаться на новую функцию ReturnSite
следующим образом.
| Математическая функция | Описание |
|---|---|
| ABS(X) | Возвращает абсолютное значение числа Х |
| ACOS (X) | Возвращает арккосинус числа Х |
| ASIN(X) | Возвращает арксинус числа Х |
| ATAN(X) | Возвращает арктангенс числа Х |
| COS(X) | Возвращает косинус числа Х |
| EXP(X) | Возвращает экспоненту числа Х |
| SIGN(X) | Возвращает -1, если Х<0,0, если Х=0, +1 , если Х>0 |
| LN(X) | Возвращает натуральный логарифм числа Х |
| MOD(X,Y) | Возвращает остаток от деления Х на Y |
| CEIL (X) | Возвращает наименьшее целое, большее или равное Х |
| ROUND(X,n) | Округляет число Х до числа с n знаками после десятичной точки |
| SIN(X) | Возвращает синус числа Х |
| SQRT(X) | Возвращает квадратный корень числа Х |
| TAN(X) | Возвращает тангенс числа Х |
| FLOOR (X) | Возвращает наибольшее целоеб меньшее или равное Х |
| LOG(a,X) | Возвращает логарифм числа Х по основанию А |
| SINH(X) | Возвращает гиперболический синус числа Х |
| COSH(X) | Возвращает гиперболический косинус числа Х |
| TANH(X) | Возвращает гиперболический тангенс числа Х |
| TRANC(X,n) | Усекает число Х до числа с n знаками после десятичной точки |
| POWER(A,X) | Возвращает значение А , возведенное в степень Х |
Набор встроенных функций может изменяться в зависимости от версии СУБД одного производителя и также в СУБД различных производителей. Так, например, в СУБД SQLBase, Centure Inc. есть функция @ATAN2(X,Y) , которая возвращает арктангенс Y/X , но отсутствует функция SIGN(X) .
Арифметические выражения необходимы для получения данных, которые непосредственно не сохраняются в колонках таблиц базы данных, но значения которых необходимы пользователю. Допустим, что вам необходим список служащих, показывающий выплату, которую получил каждый служащий с учетом премий и штрафов.
SELECT ENAME, SAL, COMM, FINE, SAL + COMM - FINE FROM EMPLOYEE ORDER BY DEPNO;
Арифметическое выражение SAL + COMM - FINE выводится как новая колонка в результирующей таблице, которая вычисляется в результате выполнения запроса. Такие колонки называют еще производными (вычисляемыми) атрибутами или полями.
Функции обработки строк
SQL предоставляет вам широкий набор функций для манипулирования со строковыми данными (конкатенация строк, CHR, LENGTH, INSTR и другие). Список основных функций для обработки строковых данных приведен в таблице 8.3 .
| Функция | Описание |
|---|---|
| CHR(N) | Возвращает символ ASCII кода для десятичного кода N |
| ASCII(S) | Возвращает десятичный ASCII код первого символа строки |
| INSTR(S2.S1.pos[,N] | Возвращает позицию строки S1 в строке S2 большую или равную pos.N - число вхождений |
| LENGHT(S) | Возвращает длину строки |
| LOWER(S) | Заменяет все символы строки на прописные символы |
| INITCAP(S) | Устанавливает первый символ каждого слова в строке на заглавный, а остальные символы каждого слова - на прописные |
| SUBSTR(S,pos,[,len]) | Выделяет в строке S подстроку длиной len , начиная с позиции pos |
| UPPER(S) | Преобразует прописные буквы в строке на заглавные буквы |
| LPAD(S,N[,A]) | Возвращает строку S , дополненную слева симолами A до числа символов N . Символ - наполнитель по умолчанию - пробел |
| Rpad(S,N[,A]) | Возвращает строку S , дополненную справа симолами A до числа символов N . Символ - наполнитель по умолчанию - пробел |
| LTRIM(S,) | Возвращает усеченную слева строку S . Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел) |
| RTRIM(S,) | Возвращает усеченную справа строку S . Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел |
| TRANSLATE(S,S1,S2) | Возвращает строку S , в которой все вхождения строки S1 замещены строкой S2 . Если S1 <> S2 , то символы, которым нет соответствия, исключаются из результирующей строки |
| REPLACE(S,S1,[,S2]) | Возвращает строку S , для которой все вхождения строки S1 замещены на подстроку S2 . Если S2 не указано, то все вхождения подстроки S1 удаляются из результирующей строки |
| NVL(X,Y) | Если Х есть NULL , то возвращает в Y либо строку, либо число, либо дату в зависимости от исходного типа Y |
Названия одних и тех же функций могут отличаться в различных СУБД. Так, например, функция СУБД Oracle SUBSTR(S, pos, [, len]) в СУБД SQLBase называется @SUBSTRING(S, pos, len) . В СУБД SQLBase имеются функции, которых нет в СУБД Oracle (см.
