Таблица 8.2. Математические функции SQL

Математическая функция	Описание
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 .

Таблица 8.3. Функции SQL для обработки строк

Функция	Описание
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 (см.

Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье.

Агрегатные функции

Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций:

AVG

Вычисляет среднее арифметическое значение данных, содержащихся в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.

MIN и MAX

Определяют максимальное и минимальное значение из всех значений данных, содержащихся в столбце. Значения могут быть числовыми, строковыми или временными (дата/время).

SUM

Вычисляет общую сумму значений в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.

COUNT

Подсчитывает количество значений, отличных от null в столбце. Функция count(*) является единственной агрегатной функцией, которая не выполняет вычисления над столбцами. Эта функция возвращает количество строк (независимо от того, содержат ли отдельные столбцы значения null).

COUNT_BIG

Аналогична функции count, с той разницей, что возвращает значение данных типа BIGINT.

Использование обычных агрегатных функций в инструкции SELECT будет рассматриваться в одной из следующих статей.

Скалярные функции

Скалярные функции Transact-SQL используются в создании скалярных выражений. (Скалярная функция выполняет вычисления над одним значением или списком значений, тогда как агрегатная функция выполняет вычисления над группой значений из нескольких строк.) Скалярные функции можно разбить на следующие категории:

числовые функции;

функции даты;

строковые функции;

системные функции;

функции метаданных.

Эти типы функций рассматриваются в последующих разделах.

Числовые функции

Числовые функции языка 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 функции даты и их краткое описание приводятся в таблице ниже:

Функции даты 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 строковые функции и их краткое описание приводятся в таблице ниже:

Строковые функции 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), который присваивается каждому объекту базы данных при его создании. Посредством этого идентификатора система может однозначно идентифицировать каждый объект базы данных.

В следующей таблице приводятся некоторые из наиболее важных системных функций вместе с их кратким описанием:

Системные функции Transact-SQL

Функция	Синтаксис	Описание	Пример использования
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. Если идентификатор не указан, то возвращается имя текущего пользователя.

Функции метаданных

По большому счету, функции метаданных возвращают информацию об указанной базе данных и объектах базы данных. В таблице ниже приводятся некоторые из наиболее важных функций метаданных вместе с их кратким описанием:

Функции метаданных Transact-SQL

Функция	Синтаксис	Описание	Пример использования
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. Функции могут быть агрегатными или скалярными. Начнем…

COUNT()

COUNT() - функция возвращающая количество записей (строк) таблицы. Запись функции с указанием столбца вернет количество записей конкретного столбца за исключением NULL записей. Синтаксис функции:

COUNT(column_name);

Запись функции с указанием маски «*» вернет количество всех записей в таблице. Синтаксис:

COUNT(*);

Рассмотрим примеры. Имеется следующая таблица Universities:

ID	UniversityName	Students	Faculties	Professores	Location	Site
1	Perm State National Research University	12400	12	1229	Perm	psu.ru
2	Saint Petersburg State University	21300	24	13126	Saint-Petersburg	spbu.ru
3	Novosibirsk State University	7200	13	1527	Novosibirsk	nsu.ru
4	Moscow State University	35100	39	14358	Moscow	msu.ru
5	Higher School of Economics	20335	12	1615	Moscow	hse.ru
6	Ural Federal University	57000	19	5640	Yekaterinburg	urfu.ru
7	National Research Nuclear University	8600	10	936	Moscow	mephi.ru

Пример 1. Вывести число записей таблицы, используя функцию COUNT:

SELECT COUNT(*)
FROM Universities; // выведит 7

Пример 2. Найти количество университетов расположенных в Москве, используя функцию COUNT:

SELECT COUNT(*) FROM Universities WHERE Location = "Moscow"; // выведит 3

AVG()

AVG() - функция возвращающая среднее значение столбца. Данная функция применима только для числовых столбцов. Синтаксис:

AVG(column_name);

Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию AVG найти среднее число студентов (Students) всех университетов:

SELECT AVG(Students) FROM Universities; // выведит 23133

MIN()

MIN() - функция возвращающая минимальное значение столбца. Функция имеет следующий синтаксис:

MIN(column_name);

Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию MIN найти минимальное значение столбца Professores:

SELECT MIN(Professores) FROM Universities; // выведит 936

MAX()

MAX() - функция возвращающая максимальное значение столбца таблицы. Функция имеет следующий синтаксис:

MAX(column_name);

Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию MAX найти максимальное значение колонки Students:

SELECT MAX(Students) FROM Univerities; // выведит 57000

SUM()

SUM() - функция, возвращающая сумму значений столбца таблицы. Используется только для числовых столбцов. Синтаксис функции:

SUM ( expression);

Параметр ALL - является параметром по умолчанию. Считается сумма всех строк. При указании параметра DISTINCT - происходит подсчет только уникальных значений.

Рассмотрим примеры. Возьмем нашу таблицу Universities. Пример 1. Используя функцию SUM найти количество студентов (Students) всех университетов в таблице:

SELECT SUM(Students) FROM Universities; // выведит 161935

Пример 2. Используя функцию SUM произвести подсчет уникальных значений столбца Faculties:

SELECT SUM(DISTINCT Faculties) FROM Universities; // выведит 117 (в столбце Faculties присутствует 2 одинаковых записи, под ID 1 и 5. Значение столбца под ID 5 не суммируется.)

ROUND()

ROUND() - функция для округления десятичных чисел. Работает только с числовыми столбцами или произвольными вещественными числами. Синтаксис функции:

ROUND(expression, length);

expression - название столбца или столбцов, а так же вещественное число. length - указывает точность округления для числа.

Рассмотрим пример. Возьмём таблицу Planets:

ID	PlanetName	Radius	SunSeason	OpeningYear	HavingRings	Opener
1	Mars	3396	687.58	1659	No	Christiaan Huygens
2	Saturn	60268	10759.22	-	Yes	-
3	Neptune	24764	60190.91	1846	Yes	John Couch Adams
4	Mercury	2439	115.88	1631	No	Nicolaus Copernicus
5	Venus	6051	243.56	1610	No	Galileo Galilei

Пример. Используя функцию ROUND округлить столбец SunSeason с точностью до одного знака после запятой:

SELECT ROUND(SunSeason, 1) FROM Planets;

UCASE()

UCASE() - функция, возвращающая значения столбца или столбцов в верхнем регистре букв. В СУБД MS SQL Server аналогом UCASE() является функция UPPER с тем же синтаксисом:

UCASE(column_name)

Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. Вывести названия планет в верхнем регистре, у которых нет колец, используя функцию UCASE:

SELECT UCASE(PlanetName) FROM Planets WHERE HavingRings = "No";

LCASE()

LCASE() - функция, возвращающая значения столбца или столбцов в нижнем регистре букв. В СУБД MS SQL Server аналогом оператора SQL LCASE() является функция LOWER с тем же синтаксисом:

LCASE(column_name);

Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. С помощью функции LCASE вывести в нижнем регистре названия планет, у которых есть кольца:

SELECT LCASE(PlanetName) FROM Planets WHERE HavingRings = "Yes";

LEN()

LEN() - функция, возвращающая длину значения в поле записи. Функция исключает из подсчета конечные пробелы. Синтаксис:

LEN(column_name);

Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. Вывести первооткрывателя планеты (Opener) и длину названия открытым им планеты, с помощью функции LEN:

SELECT Opener, LEN(PlanetName) FROM Planets;

MID()

MID() - функция, выводящая определенное количество символов текстового поля таблицы. Синтаксис функции:

MID(colunm_name,start [,length]);

Параметр start задает позицию начального символа. Параметр length устанавливает количество символов для вывода начиная с позиции, указанной в параметре start.

Рассмотрим пример. Возьмём нашу старую таблицу Universities. Пример. Вывести первые 3 символа названия города (Location) с помощью функции MID:

SELECT MID(Location, 1, 3) FROM Universities;

NOW()

NOW() - функция, возвращающая системное время и дату. Синтаксис функции:

Рассмотрим пример. Возьмём нашу старую таблицу Universities. Пример. Вывести сколько на текущий момент студентов обучается в каждом университете. используя функцию NOW:

SELECT UniversityName, Students, NOW() AS CurDate FROM Universities;

Для закрепления материала выполните пример каждой функцию самостоятельно. Если будут вопросы пишите в комментариях и я обязательно на них отвечу.

Шаги этого этапа изучения SQL запросов призваны продемонстрировать тот факт, что SQL умеет не только делать сложные выборки и сортировать данные, но и вычислять результаты математических функций, выполнять преобразование текста, группировать записи и т.п. Точнее все это умеет не SQL, а , его поддерживающие. SQL своими стандартами только формулирует требования к этим самым СУБД.

Шаг 15. Функции SUM, AVG, MIN, MAX, COUNT…

На этом шаге будут показаны варианты использования простейших функций в SQL, таких как сумма, минимальное и максимальное значения, среднее значение и т.п. Сразу же начнем с примера вывода среднего значения стажа всех сотрудников.

SELECT AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНИЙ СТАЖ СОТРУДНИКОВ] FROM D_STAFF

SQL функция AVG.

Аналогично можно вычислить минимальное и максимальное значения (MIN, MAX), общую сумму (SUM) и т.д. Советую попробовать это выполнить с использованием обучающей программы. Стоит попробовать определить дополнительные критерии отбора записей, участвующих в определении итогового значения функции с использованием оператора WHERE.

Перечисленные выше функции для определения своего значения используют результат запроса целиком. Такие функции называются агрегатными . Также, есть ряд функций, аргументом которых являются не все значения определенной в запросе колонки, а каждое отдельное значение каждой отдельной строки результата. Примером такой функции является SQL функция вычисления длины текстового поля LEN:

SELECT S_NAME, LEN(D_STAFF.S_NAME) AS [ДЛИНА] FROM D_STAFF

Можно использовать суперпозицию SQL функций , как показано ниже, и вычислить максимальное значение длины поля S_NAME.

SELECT MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

SQL функция MAX.

Ну и в заключении все вместе.

SELECT SUM(D_STAFF.S_EXPERIENCE) AS [СУММА], AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНЕЕ], MIN(D_STAFF.S_EXPERIENCE) AS [МИНИМУМ], MAX(D_STAFF.S_EXPERIENCE) AS [МАКСИМУМ], COUNT(*) AS [КОЛИЧЕСТВО ЗАПИСЕЙ], MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

Пример использования агрегатных SQL функций.

Обратите внимание на аргумент функции COUNT. Я указал в качестве аргумента (*), поскольку хочу получиться именно общее число записей. Если указать, например COUNT(S_NAME), то результатом будет число непустых значений S_NAME (S_NAME IS NOT NULL). Можно было бы написать COUNT(DISTINCT S_NAME) и получить количество уникальных значений S_NAME, но MS Access такой вариант, к сожалению, не поддерживает. В нашем примере COUNT(S_NAME) и COUNT(*) дают абсолютно одинаковый результат.

Шаг 16. Преобразование текста

Часто, текстовые значения заполняются пользователями программного обеспечения по-разному: кто пишет Ф.И.О. с заглавной буквы, кто нет; кто-то пишет все заглавными буквами. Многие отчетные формы требуют унифицированного подхода, да и не только отчетные формы. Для решения этой задачи в SQL есть две функции UCASE и LCASE. Пример запроса и результат его обработки приведены ниже:

SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF

SQL функции UCASE и LCASE.

Шаг 17. SQL и работа со строками

Есть еще такая замечательная функция MID, которая поможет вам решить задачу выделения части строки из всего значения текстового поля. Здесь также лучшим комментарием будет пример – пример "издевательств" над наименованиями профилей пользователей.

SELECT UCASE(MID(P_NAME,3,5)) FROM D_PROFILE

Суперпозиция SQL функций UCASE и MID.

Мы “вырезали” из значений наименований профилей по 5 символов, начиная с 3-го, и получили кучу повторяющегося “мусора”. Для того чтобы оставить только уникальные значения будем использовать ключевое слово DISTINCT.

SELECT DISTINCT UCASE(MID(P_NAME,3,5)) AS FROM D_PROFILE

Выбор уникальных значений агрегатной функции.

Иногда приходится в качестве аргументов функции MID использовать выражения с функцией LEN. В следующем примере мы уже выводим последние 5 символов в наименованиях профилей.

SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE

Использование SQL функции LEN.

Шаг 18. Использование SQL функций в критерии отбора записей. Оператор HAVING

Разобравшись с функциями, практически сразу возникает вопрос, как их можно использовать в критериях отбора записей? Некоторые функции, а именно те, которые не являются агрегатными - использовать достаточно легко. Вот, например, список сотрудников, чье Ф.И.О. более 25 символов.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

Использование неагрегатной функции LEN в условиях SQL запроса.

Ну а если вам, к примеру, необходимо вывести идентификаторы всех должностей, которые занимают более одного сотрудника в компании, то такой подход не подойдет. Я имею в виду то, что следующий запрос, может быть, и не лишен какого-то смысла, но он неверный с точки зрения структурированного запроса. Это связано с тем, что для корректной обработки подобных SQL запросов с использованием агрегатных функций одного линейного прохода по записям сотрудников будет мало.

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

Для таких случаев в SQL ввели ключевое слово HAVING, которое поможет нам решить проблему с должностями и сотрудниками.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION HAVING COUNT(S_POSITION)>1

Использование агрегатных функций в условиях SQL запроса.

Шаг 19. Группировка данных в результатах SQL запроса оператором GROUP BY

Оператор GROUP BY необходим для группировки значений агрегатных функций по значениям связанных с ними полей. Он необходим, когда мы хотим использовать значение агрегатной функции в критерии отбора записей (предыдущий шаг). Он также необходим, когда мы хотим включить в результат запроса значение агрегатной функции. Но в самом простом варианте группировка эквивалента выделению уникальных значений колонки. Смотрим пример запроса.

SELECT S_POSITION FROM D_STAFF

А это два варианта, позволяющие вывести только уникальные значения S_POSITION.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION

SELECT DISTINCT S_POSITION FROM D_STAFF

Ну а теперь вернемся к группировке значений функций по значениям связанных с ними полей. Выведем для каждого профиля пользователя количество ассоциированных с ним записей в таблице D_STAFF_PROFILE.

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ] FROM D_STAFF_PROFILE GROUP BY PROFILE_ID

Использование агрегатной SQL функции вместе с группировкой.

Оператор GROUP BY позволяет также группировать результат запроса более чем по одному полю, перечисляя их через запятую. Я надеюсь, что после всего вышесказанного дополнительные комментарии к результату последнего запроса не нужны.

SELECT S.S_POSITION AS , S.S_NAME AS [СОТРУДНИК], COUNT(SP.STAFF_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ В ТАБЛИЦЕ D_STAFF_PROFILE] FROM D_STAFF S, D_STAFF_PROFILE SP WHERE S.XD_IID=SP.STAFF_ID GROUP BY S.S_POSITION, S.S_NAME

Группировка строк результата SQL запроса по нескольким полям.

Основные команды SQL, которые должен знать каждый программист

Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных (RDBMS). В этой статье будет рассказано о часто используемых командах SQL, с которыми должен быть знаком каждый программист. Этот материал идеально подойдёт для тех, кто хочет освежить свои знания об SQL перед собеседованием на работу. Для этого разберите приведённые в статье примеры и вспомните, что проходили на парах по базам данных.

Обратите внимание, что в некоторых системах баз данных требуется указывать точку с запятой в конце каждого оператора. Точка с запятой является стандартным указателем на конец каждого оператора в SQL. В примерах используется MySQL, поэтому точка с запятой требуется.

Настройка базы данных для примеров

Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql . После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):

Mysql -u root -p

Затем введите пароль.

Выполните следующую команду. Назовём базу данных «university»:

CREATE DATABASE university; USE university; SOURCE ; SOURCE

Команды для работы с базами данных

1. Просмотр доступных баз данных

SHOW DATABASES;

2. Создание новой базы данных

CREATE DATABASE;

3. Выбор базы данных для использования

USE ;

4. Импорт SQL-команд из файла.sql

SOURCE ;

5. Удаление базы данных

DROP DATABASE ;

Работа с таблицами

6. Просмотр таблиц, доступных в базе данных

SHOW TABLES;

7. Создание новой таблицы

CREATE TABLE ( , , PRIMARY KEY (), FOREIGN KEY () REFERENCES ());

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

• ячейка таблицы не может иметь значение NULL;
• первичный ключ - PRIMARY KEY (col_name1, col_name2, …) ;
• внешний ключ - FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.

Пример

Создайте таблицу «instructor»:

CREATE TABLE instructor (ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name));

8. Сведения о таблице

Можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы следующей командой:

DESCRIBE ;

9. Добавление данных в таблицу

INSERT INTO (, , , …) VALUES (, , , …);

При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

INSERT INTO VALUES (, , , …);

10. Обновление данных таблицы

UPDATE SET = , = , ... WHERE ;

11. Удаление всех данных из таблицы

DELETE FROM ;

12. Удаление таблицы

DROP TABLE ;

Команды для создания запросов

13. SELECT

SELECT используется для получения данных из определённой таблицы:

SELECT , , … FROM ;

Следующей командой можно вывести все данные из таблицы:

SELECT * FROM ;

14. SELECT DISTINCT

В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.

SELECT DISTINCT , , … FROM ;

15. WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

SELECT , , … FROM WHERE ;

В запросе можно задавать следующие условия:

• сравнение текста;
• сравнение численных значений;
• логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE:

SELECT * FROM course WHERE dept_name=’Comp. Sci.’; SELECT * FROM course WHERE credits>3; SELECT * FROM course WHERE dept_name="Comp. Sci." AND credits>3;

16. GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.

SELECT , , … FROM GROUP BY ;

Пример

Выведем количество курсов для каждого факультета:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name;

17. HAVING

Ключевое слово HAVING было добавлено в SQL потому, что WHERE не может быть использовано для работы с агрегатными функциями.

SELECT , , ... FROM GROUP BY HAVING

Пример

Выведем список факультетов, у которых более одного курса:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1;

18. ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .

SELECT , , … FROM ORDER BY , , … ASC|DESC;

Пример

Выведем список курсов по возрастанию и убыванию количества кредитов:

SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC;

19. BETWEEN

BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.

SELECT , , … FROM WHERE BETWEEN AND ;

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000;

20. LIKE

Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.

Есть два свободных оператора, которые используются в LIKE:

• % (ни одного, один или несколько символов);
• _ (один символ).

SELECT , , … FROM WHERE LIKE ;

Пример

Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-»:

SELECT * FROM course WHERE title LIKE ‘%to%’; SELECT * FROM course WHERE course_id LIKE "CS-___";

21. IN

С помощью IN можно указать несколько значений для оператора WHERE:

SELECT , , … FROM WHERE IN (, , …);

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

SELECT * FROM student WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);

22. JOIN

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

SELECT , , … FROM JOIN ON = ;

Пример 1

Выведем список всех курсов и соответствующую информацию о факультетах:

SELECT * FROM course JOIN department ON course.dept_name=department.dept_name;

Пример 2

Выведем список всех обязательных курсов и детали о них:

SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id;

Пример 3

Выведем список всех курсов вне зависимости от того, обязательны они или нет:

SELECT course.course_id, title, dept_name, credits, prereq_id FROM prereq RIGHT OUTER JOIN course ON prereq.course_id=course.course_id;

23. View

View - это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. View всегда показывает самую свежую информацию из базы данных.

Создание

CREATE VIEW AS SELECT , , … FROM WHERE ;

Удаление

DROP VIEW ;

Пример

Создадим view , состоящую из курсов с 3 кредитами:

24. Агрегатные функции

Эти функции используются для получения совокупного результата, относящегося к рассматриваемым данным. Ниже приведены общеупотребительные агрегированные функции:

• COUNT (col_name) - возвращает количество строк;
• SUM (col_name) - возвращает сумму значений в данном столбце;
• AVG (col_name) - возвращает среднее значение данного столбца;
• MIN (col_name) - возвращает наименьшее значение данного столбца;
• MAX (col_name) - возвращает наибольшее значение данного столбца.

25. Вложенные подзапросы

Вложенные подзапросы - это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос.

Пример

Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:

SELECT DISTINCT course_id FROM section WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (SELECT course_id FROM section WHERE semester = ‘Spring’ AND year= 2010);