Функции Transact-SQL
78Работа с базами данных в .NET Framework --- SQL Server 2012 --- Функции Transact-SQL
Исходники баз данныхФункции языка 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. |
|
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 соответственно наоборот. |
|
CEILING | CEILING(n) | Округляет число до большего целого значения. |
|
ROUND | ROUND(n, p, [t]) | Округляет значение n с точностью до p. Когда аргумент p положительное число, округляется дробная часть числа n, а когда отрицательное - целая часть. При использовании необязательного аргумента t, число n не округляется, а усекается (т.е. округляется в меньшую сторону). |
|
FLOOR | FLOOR(n) | Округляет до меньшего целого значения. |
|
EXP | EXP(n) | Вычисляет значение en. |
|
LOG, LOG10 | LOG(n), LOG10(n) | LOG(n) - вычисляет натуральный логарифм (т.е. с основанием e) числа n, LOG10(n) - вычисляет десятичный (с основанием 10) логарифм числа n. |
|
PI | PI() | Возвращает значение π (3,1415). |
|
POWER | POWER(x, y) | Вычисляет значение xy. |
|
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() | Возвращает текущую системную дату и время. |
|
DATEPART | DATEPART (item, date) | Возвращает указанную в параметре item часть даты date в виде целого числа. |
|
DATENAME | DATENAME (item, date) | Возвращает указанную в параметре item часть даты date в виде строки символов. |
|
DATEDIFF | DATEDIFF (item, dat1, dat2) | Вычисляет разницу между двумя частями дат dat1 и dat2 и возвращает целочисленный результат в единицах, указанных в аргументе item. |
|
DATEADD | DATEADD (item, n, date) | Прибавляет n-е количество единиц, указанных в аргументе item к указанной дате date. (Значение аргумента n также может быть отрицательным.) |
|
Строковые функции
Строковые функции манипулируют значениями столбцов, которые обычно имеют символьный тип данных. Поддерживаемые в Transact-SQL строковые функции и их краткое описание приводятся в таблице ниже:
Функция | Синтаксис | Описание | Пример использования |
---|---|---|---|
ASCII, UNICODE | ASCII(char), UNICODE(char) | Преобразовывает указанный символ в соответствующее целое число кода ASCII. |
|
CHAR, NCHAR | CHAR(int), NCHAR(int) | Преобразовывает код ASCII (или Unicode если NCHAR) в соответствующий символ. |
|
CHARINDEX | CHARINDEX (str1, str2) | Возвращает начальную позицию вхождения подстроки str1 в строку str2. Если строка str2 не содержит подстроки str1, возвращается значение 0 |
|
DIFFERENCE | DIFFERENCE (str1, str2) | Возвращает целое число от 0 до 4, которое является разницей между значениями SOUNDEX двух строк str1 и str2. Метод SOUNDEX возвращает число, которое характеризует звучание строки. С помощью этого метода можно определить подобно звучащие строки. Работает только для символов ASCII. |
|
LEFT, RIGHT | LEFT (str, length), RIGHT (str, length) | Возвращает количество первых символов строки str, заданное параметром length для LEFT и последние length символов строки str для функции RIGHT. |
|
LEN | LEN(str) | Возвращает количество символов (не количество байт) строки str, указанной в аргументе, включая конечные пробелы. |
|
LOWER, UPPER | LOWER(str), UPPER(str) | Функция LOWER преобразовывает все прописные буквы строки str1 в строчные. Входящие в строку строчные буквы и иные символы не затрагиваются. Функция UPPER преобразовывает все строчные буквы строки str в прописные. |
|
LTRIM, RTRIM | LTRIM(str), RTRIM(str) | Функция LTRIM удаляет начальные пробелы в строке str, RTRIM соответственно удаляет пробелы в конце строки. |
|
QUOTENAME | QUOTENAME (char_string) | Возвращает строку в кодировке Unicode с добавленными ограничителями, чтобы преобразовать строку ввода в действительный идентификатор с ограничителями. |
|
PATINDEX | PATINDEX (%p%, expr) | Возвращает начальную позицию первого вхождения шаблона p в заданное выражение expr, или ноль, если данный шаблон не обнаружен. |
|
REPLACE | REPLACE (str1, str2, str3) | Заменяет все вхождения подстроки str2 в строке str1 подстрокой str3. |
|
REPLICATE | REPLICATE (str, i) | Повторяет i раз строку str. |
|
REVERSE | REVERSE (str) | Выводит строку str в обратном порядке. |
|
SOUNDEX | SOUNDEX (str) | Возвращает четырехсимвольный код soundex, используемый для определения похожести двух строк. Работает только для символов ASCII. |
|
SPACE | SPACE (length) | Возвращает строку пробелов длиной, указанной в параметре length. Аналог REPLICATE(' ', length). |
|
STR | STR (f[, len[, d]]) | Преобразовывает заданное выражение с плавающей точкой f в строку, где len - длина строки, включая десятичную точку, знак, цифры и пробелы (по умолчанию равно 10), а d - число разрядов дробной части, которые нужно возвратить. |
|
STUFF | STUFF (str1, a, length, str2) | Удаляет из строки str1 length-символов, начиная с позиции a, и вставляет на их место строку str2. |
|
SUBSTRING | SUBSTRING (str1, a, length) | Извлекает из строки str, начиная с позиции a, подстроку длиной length. |
Системные функции
Системные функции языка Transact-SQL предоставляют обширную информацию об объектах базы данных. Большинство системных функций использует внутренний числовой идентификатор (ID), который присваивается каждому объекту базы данных при его создании. Посредством этого идентификатора система может однозначно идентифицировать каждый объект базы данных.
В следующей таблице приводятся некоторые из наиболее важных системных функций вместе с их кратким описанием:
Функция | Синтаксис | Описание | Пример использования |
---|---|---|---|
CAST | CAST (w AS type [(length)] | Преобразовывает выражение w в указанный тип данных type (если это возможно). Аргумент w может быть любым действительным выражением. |
|
COALESCE | COALESCE (a1, a2) | Возвращает первое значение выражения из списка выражений a1, a2, ..., которое не является значением null. |
|
COL_LENGTH | COL_LENGTH (obj, col) | Возвращает длину столбца col объекта базы данных (таблицы или представления) obj. |
|
CONVERT | CONVERT (type[(length)], w) | Эквивалент функции CAST, но аргументы указываются по-иному. Может применяться с любым типом данных. |
|
CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | Возвращает текущие дату и время. |
|
CURRENT_USER | CURRENT_USER | Возвращает имя текущего пользователя. |
|
DATALENGTH | DATALENGTH (z) | Возвращает число байтов, которые занимает выражение z. |
|
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 одинаковые. |
|
SERVERPROPERTY | SERVERPROPERTY (propertyname) | Возвращает информацию о свойствах сервера базы данных. |
|
SYSTEM_USER | SYSTEM_USER | Возвращает ID текущего пользователя. |
|
USER_ID | USER_ID ([username]) | Возвращает идентификатор пользователя username. Если пользователь не указан, то возвращается идентификатор текущего пользователя. |
|
USER_NAME | USER_NAME ([id]) | Возвращает имя пользователя с указанным идентификатором id. Если идентификатор не указан, то возвращается имя текущего пользователя. |
Функции метаданных
По большому счету, функции метаданных возвращают информацию об указанной базе данных и объектах базы данных. В таблице ниже приводятся некоторые из наиболее важных функций метаданных вместе с их кратким описанием:
Функция | Синтаксис | Описание | Пример использования |
---|---|---|---|
COL_NAME | COL_NAME (tab_id, col_id) | Возвращает имя столбца с указанным идентификатором col_id таблицы с идентификатором tab_id. |
|
COLUMNPROPERTY | COLUMNPROPERTY (id, col, property) | Возвращает информацию об указанном столбце. |
|
DATABASEPROPERTY | DATABASEPROPERTY (database, property) | Возвращает значение свойства property базы данных database. |
|
DB_ID | DB_ID ([db_name]) | Возвращает идентификатор базы данных db_name. Если имя базы данных не указано, то возвращается идентификатор текущей базы данных. |
|
DB_NAME | DB_NAME ([db_id]) | Возвращает имя базы данных, имеющей идентификатор 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. |
|
OBJECT_ID | OBJECT_ID (obj_name) | Возвращает идентификатор объекта obj_name базы данных. |
|
OBJECTPROPERTY | OBJECTPROPERTY (obj_id, property) | Возвращает информацию об объектах из текущей базы данных. |