Лекция № 4
Тема: «Стандартные функции в разных СУБД»
План
1. Функции Microsoft SQL Server
2. Функции MySQL
3. Функции SQLite
1. Функции Microsoft SQL Server
Числовые функции
Для работы с числовыми данными SQL Server предоставляет ряд функций. Рассмотрим некоторые из них:
– Round(n,m) - округляет число n до m знаков.
В качестве первого параметра передается число. Второй параметр указывает на длину. Если длина представляет положительное число, то оно указывает, до какой цифры после запятой идет округление. Если длина представляет отрицательное число, то оно указывает, до какой цифры с конца числа до запятой идет округление
Select Round(1342.345, 2) -- 1342.350
Select Round(1342.345, -2) -- 1300.000
– IsNumeric(выражение) - определяет, является ли значение числом.
В качестве параметра функция принимает выражение. Если выражение является числом, то функция возвращает 1. Если не является, то возвращается 0.
Select IsNumeric(1342.345) -- 1
Select IsNumeric('1342.345') -- 1
Select IsNumeric('sql') -- 0
Select IsNumeric('13-04-2017') -- 0
– ABS(число) возвращает абсолютное значение (модуль) числа.
Select Abs(-123) -- 123
– Ceiling(число) - возвращает наименьшее целое число, которое больше или равно текущему значению.
Select Ceiling(-123.45) -- -123
Select Ceiling(123.45) -- 124
– Floor(число) - возвращает наибольшее целое число, которое меньше или равно текущему значению.
Select Floor(-123.45) -- -124
Select Floor(123.45) -- 123
– Square(число) - возводит число в квадрат.
Select Square(5) -- 25
– Sqrt(число) - получает квадратный корень числа.
Select Sqrt(225) -- 15
– Rand() - генерирует случайное число с плавающей точкой в диапазоне от 0 до 1.
Select Rand() -- 0.707365088352935
Select Rand() -- 0.173808327956812
– Cos(радианы) - возвращает косинус угла, выраженного в радианах
Select Cos(1.0472) -- 0.5 - 60 градусов
– Sin(радианы) - возвращает синус угла, выраженного в радианах
Select Sin(1.5708) -- 1 - 90 градусов
– Tan(радианы) - возвращает тангенс угла, выраженного в радианах
Select Tan(0.7854) -- 1 - 45 градусов
Функции для преобразования данных
Когда мы присваиваем значение одного типа столбцу, который хранит данные другого типа, либо выполняем операции, которые извлекают данные разных типов, SQL Server пытается выполнить преобразование и привести используемое значение к нужному типу. Но не все преобразования SQL Server может выполнить автоматически.
В тех случаях, когда необходимо выполнить преобразования типов, надо выполнять явное преобразование. Для этого в T-SQL определены две функции:
– Cast(выражение AS тип_данных) - преобразует выражение одного типа к другому. В качестве выражения может выступать имя поля или формула.
Например, выведем информацию о дате, количестве и сумме продаж товара в одну строку:
Select Id, Cast(DataProdag AS nvarchar) + ' продано: ' +
Cast(Kol AS nvarchar) +' на сумму: '+ Cast(Kol*Price AS nvarchar) From Orders
– Convert(тип_данных, выражение [, стиль])
Большую часть преобразований охватывает функция Cast. Если же необходимо какое-то дополнительное форматирование, то можно использовать функцию Convert.
Третий необязательный параметр задает стиль форматирования данных. Этот параметр представляет числовое значение, которое для разных типов данных имеет разную интерпретацию. Например, некоторые значения для форматирования дат и времени:
0 или 100 - формат даты "Mon dd yyyy hh:mi AM/PM" (значение по умолчанию)
1 или 101 - формат даты "mm/dd/yyyy"
3 или 103 - формат даты "dd/mm/yyyy"
7 или 107 - формат даты "Mon dd, yyyy hh:mi AM/PM"
8 или 108 - формат даты "hh:mi:ss"
10 или 110 - формат даты "mm-dd-yyyy"
14 или 114 - формат даты "hh:mi:ss:mmmm" (24-часовой формат времени)
Некоторые значения для форматирования данных типа money в строку:
0 - в дробной части числа остаются только две цифры (по умолчанию)
1 - в дробной части числа остаются только две цифры, а для разделения разрядов применяется запятая
2 - в дробной части числа остаются только четыре цифры
Например, выведем дату и стоимость заказов с форматированием:
Select Convert(nvarchar, DateZakaz, 3),
Convert(nvarchar, Price * ProductCount, 1)
From Orders
– Try_Convert(тип_данных, выражение [, стиль])
При использовании функций Cast и Convert SQL Server выбрасывает исключение, если данные нельзя привести к определенному типу. Например:
Select Convert(int, 'Sql') –-нельзя преобразовать текст целому типу
Чтобы избежать генерации исключения можно использовать функцию Try_Convert. Ее использование аналогично функции Convert за тем исключением, что если выражение не удается преобразовать к нужному типу, то функция возвращает Null:
Select Try_Convert(int, 'Sql') -- Null
Select Try_Convert(int, '22') -- 22
Дополнительные функции
– Str(выражение [, длина [,точность]]) - преобразует число в строку.
Второй параметр указывает на длину строки, а третий - сколько знаков в дробной части числа надо оставлять.
– Char(int) - преобразует числовой код ASCII в символ. Нередко используется для тех ситуаций, когда необходим символ, который нельзя ввести с клавиатуры.
– Ascii(char) - преобразует символ в числовой код ASCII.
– NChar(int) - преобразует числовой код Unicode в символ.
– Unicode(char) - преобразует символ в числовой код Unicode.
Функции NewID, IsNull, Coalesce, NullIf
– NewId() - используется для генерации некоторого уникального значения. Например, мы можем определить для столбца первичного ключа тип UniqueIdentifier и по умолчанию присваивать ему значение функции NewId:
Create Table Clients
(
id UniqueIdentifier Primary Key Default Newid(),
fam nvarchar(20) not Null,
imya nvarchar(20) not Null,
phone nvarchar(20) Null,
email nvarchar(20) Null
)
- IsNull(выражение, значение) - проверяет значение некоторого выражения. Если оно равно Null, то функция возвращает значение, которое передается в качестве второго параметра.
Например, возьмем выше созданную таблицу. Поля phone и email могут содержать значение Null. Однако, для пользователя эти значения можно не показывать, а заменить их более содержательным тестом. Например, в поле phone вместо Null выведем «не определено», а для поля email вместо Null выведем «неизвестно»:
Select fam, imya,
IsNull(phone, 'не определено') as phone,
IsNull(email, 'неизвестно') as email
From Clients
- Coalesce(выражение1, выражение2, выражениеN) - принимает список значений и возвращает первое из них, которое не равно Null.
Например, выберем из таблицы Clients пользователей и в контактах у них определим либо телефон, либо электронный адрес, если они не равны Null:
Select fam, imya,
Coalesce(phone, email, 'не определено') as contacts
From Clients
То есть в данном случае возвращается телефон, если он определен. Если он не определен, то возвращается электронный адрес. Если и электронный адрес не определен, то возвращается строка "не определено".
– NullIf(выражение1, выражение2) - возвращает Null, если её аргументы равны, или первый аргумент в противном случае.
Например, в таблице Trip посчитаем сколько рейсов из Ростова (town_from) не едут в Москву (town_to).
Select Count(NullIf(town_to, 'Москва')) as non_moscow
From Trip
Where town_from='Ростов';
В данном примере функция Count() считаем количество значений не равных Null. Если поле town_to = 'Москва', то функция NullIf() вернет Null, и данная запись функцией Count подсчитан не будет.
2. Функции MySQL
Математические функции
Все математические функции в случае ошибки возвращают NULL.
– Abs(X) - возвращает абсолютное значение (модуль) величины X.
– Sign(X) - возвращает -1, 0 или 1, в зависимости от того, является ли X отрицательным, нулем или положительным.
– Mod(N,M) или % - возвращает остаток от деления N на M.
Select Mod(234, 10) или Select 234 % 10
– Floor(X) - возвращает наибольшее целое число, не превышающее X.
– Ceiling(X) - возвращает наименьшее целое число, не меньшее, чем X:
– Round(X) - возвращает аргумент X, округленный до ближайшего целого числа.
Следует учитывать, что поведение функции Round() при значении аргумента, равном середине между двумя целыми числами, зависит от конкретной реализации библиотеки C. Округление может выполняться: к ближайшему четному числу, всегда к ближайшему большему, всегда к ближайшему меньшему, всегда быть направленным к нулю. Чтобы округление всегда происходило только в одном направлении, необходимо использовать вместо данной хорошо определенные функции, такие как Truncate() или Floor().
Round(X,D) - возвращает аргумент X, округленный до числа с D десятичными знаками. Если D равно 0, результат будет представлен без десятичного знака или дробной части:
– Exp(X) - возвращает значение e (основа натуральных логарифмов), возведенное в степень X.
– Log(X) - возвращает натуральный логарифм числа X.
Чтобы получить логарифм числа X для произвольной основы логарифмов B, следует использовать формулу Log(X)/Log(B).
– Log10(X) - возвращает десятичный логарифм числа X:
– Pow(X,Y) или Power(X,Y) - возвращает значение аргумента X, возведенное в степень Y.
– Sqrt(X) - возвращает неотрицательный квадратный корень числа X:
– Pi() - возвращает значение числа "пи". По умолчанию представлено 5 десятичных знаков, но в MySQL для представления числа "пи" при внутренних вычислениях используется полная двойная точность.
– Cos(X) - возвращает косинус числа X, где X задается в радианах.
– Sin(X) - возвращает синус числа X, где X задается в радианах.
– Tan(X) - возвращает тангенс числа X, где X задается в радианах.
– Cot(X) - возвращает котангенс числа X.
– Rand() или Rand(N) - возвращает случайную величину с плавающей точкой в диапазоне от 0 до 1. Если целочисленный аргумент N указан, то он используется как начальное значение этой величины:
– Least(X,Y,...) - если задано два или более аргументов, возвращает наименьший (с минимальным значением) аргумент.
– Greatest(X,Y,...) - возвращает наибольший (с максимальным значением) аргумент.
– Degrees(X) - возвращает аргумент X, преобразованный из радианов в градусы
– Radians(X) - возвращает аргумент X, преобразованный из градусов в радианы.
– Truncate(X,D) - возвращает число X, усеченное до D десятичных знаков. Если D равно 0, результат будет представлен без десятичного знака или дробной части.
Функции потока управления программой
– IfNull(expr1,expr2) - если expr1 не равно Null, то функция IfNull() возвращает значение expr1, в противном случае - expr2.
– NullIf(expr1,expr2) - если выражение expr1 = expr2 истинно, то возвращает Null, в противном случае - expr1.
– If(expr1,expr2,expr3) - если expr1 равно значению true, то функция If() возвращает expr2, в противном случае - expr3.
Функции приведения типов
– Cast(expression AS type)
или
– Convert(expression, type)
Выполняет приведение значения expression к заданному типу.
Разные функции
– Database() - возвращает имя текущей базы данных.
– User() - возвращает имя текущего активного пользователя MySQL.
– Password(str) - создает строку "пароля" из простого текста в аргументе str. Именно эта функция используется в целях шифрования паролей MySQL для хранения в столбце Password в таблице привилегий user:
Select Password('badpwd'); ## '7f84554057dd964b'
Шифрование, которое выполняет функция PASSWORD(), необратимо.
– MD5(string) - вычисляет 128-битовую контрольную сумму MD5 для аргумента string. Возвращаемая величина представляет собой 32-разрядное шестнадцатеричное число, которое может быть использовано, например, в качестве хеш-ключа.
– SHA1(string) или SHA(string) - вычисляет 160-битовую контрольную сумму SHA1 для аргумента string, как описано в RFC 3174 (Secure Hash Algorithm). Возвращаемая величина представляет собой 40-разрядное шестнадцатеричное число или NULL (в том случае, если входной аргумент был равен NULL). Одно из возможных применений для этой функции - в качестве хеш-ключа. Можно ее использовать и как криптографически безопасную функцию для сохранения паролей.
Функция SHA1() может рассматриваться как более защищенный криптографически эквивалент функции MD5(). SHA() является синонимом для функции SHA1().
– Version() - возвращает строку с номером версии сервера MySQL.
3. Функции SQLite
– Abs(X) - возвращает абсолютное значение (модуль) числового аргумента X.
– Cast(X as тип) - позволяет преобразовать (конвертировать) данные X в данные указанного типа.
– Char(X1,X2,...,XN) - возвращает строку, состоящую из символов, имеющих unicode значения с X1 до XN.
– Coalesce(X,Y,...) - возвращает копию первого не Null аргумента или Null, если все аргументы являются Null. Coalesce() должна использовать как минимум 2 аргумента.
– IfNull(X,Y) - возвращает копию первого не Null аргумента, или Null, если оба аргумента являются NULL. В IfNull() должно передаваться именно 2 аргумента. Функция эквивалентна функции Coalesce() с двумя аргументами.
– Max(X,Y,...) - возвращает аргумент с максимальным значением или Null, если все аргументы равны Null.
– Min(X,Y,...) - возвращает аргумент с минимальным значением или Null, если все аргументы равны Null.
– NullIf(X,Y) - возвращает свой первый аргумент, если аргументы отличаются, и Null, если аргументы совпадают.
– Random() - возвращает псевдо-случайное целое число между -9223372036854775808 и +9223372036854775807.
– Round(X) или Round(X,Y) - возвращает значение с плавающей запятой X, округленное до Y цифр справа от десятичной запятой. Если аргумент Y опущен, предполагается, что он равен 0.
– Sqlite_Version() - возвращает строку версии для запущенной библиотеки SQLite.