Создание запросов и фильтров. Вычисление при помощи оператора SELECT. Встроенные функции

Выполнение вычислений при помощи оператора SELECT. Встроенные функции

Кроме связывания таблиц и отбора данных оператор SELECT может использоваться для вычислений. В этом случае он имеет синтаксис:

SELECT <Выражение>

где <выражение> - какое-то математическое выражение или функция. Выражение имеет стандартный вид (как в Visual Basic), оно может включать в себя встроенные функции сервера.

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

В SQL Server существуют следующие встроенные функции, разбитые на группы.

Математические функции

Замечание: В качестве параметров функции будем указывать соответствующий им тип данных.

• ABS (numeric) - модуль числа;
• ACOS/ASIN/ATAN (Float) - арккосинус, арксинус, арктангенс в радианах;
• COS/SIN/TAN/COT (Float) - косинус, синус, тангенс, котангенс;
• CEILING (Numeric) - наименьшее целое, большее или равное параметру в скобках;
• DEGREES (Numeric) - преобразует радианы в градусы;
• EXP(Float) - экспонента, е^х;
• FLOOR(Numeric) - наибольшее целое меньшее или равное выражению numeric ;
• LOG(Float) - натуральный логарифм ln;
• LOG10(Float) - десятичный логарифм log₁₀;
• PI () - число пи;
• POWER (Numeric,y) - возводит выражение Numeric в степень у ;
• RADIANS (Numeric) - преобразует градусы в радианы;
• RAND () - генерирует случайное число типа данных Float, расположенное между нулем и единицей;
• ROUND (Numeric, Длина) - округляет выражение Numeric до заданной Длины (количество знаков после запятой);
• SIGN (Numeric) - выводит знак числа +/- или ноль;
• SQUARE (Float) - вычисляет квадрат числа Float ;
• SQRT (Float) - вычисляет квадратный корень числа Float.

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

• SELECT ABS(-10) результат 10
• SELECT SQRT (16) результат 4
• SELECT ROUND (125.85,0) результат 126
• SELECT POWER (2,4) результат 16

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

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

• 'Строка1'+ 'Строка2' присоединяет Строку1 к Строке2 ;
• ASCII(Char) - возвращает ASCII код с самого левого символа выражения Char ;
• CHAR(Int) - выводит символ соответствующий ASCII коду в выражении Int ;
• CHARINDEX(Образец, Выражение) - выводит позицию Образца выражения, то есть где находится Образец в Выражении ;
• DIFFERENCE(Выражение1, Выражение2) - сравнивает два выражения, выводит числа от 0 до 4: 0 - выражения абсолютно различны; 4 - выражения абсолютно идентичны. Оба выражения типа данных Char ;
• LEFT(Char, Int) - выводит из строки Char Int символов слева;
• RIGHT(Char, Int) - выводит из строки Char Int символов справа;
• LTRIM(Char) - удаляет из строки Char пробелы слева;
• RTRIM(Char) - удаляет из строки Char пробелы справа;
• WCHAR(Int) - выводит выражение Int в формате Unicode;
• REPLACE(Строка1, Строка2, Строка3) - меняет в Строке1 все элементы Строка2 на элементы Строка3 ;
• REPLICATE(Char, Int) - повторяет строку Char Int раз;
• REVERSE(Сhar) - производит инверсию строки Char, то есть располагает символы в обратном порядке;
• SPACE(Int) - выводит Int пробелов;
• STR(Float) - переводит число Float в строку;
• STUFF(Выражение1, Начало, Длина, Выражение2) - удаляет из Выражения1 начиная с позиции символа Начало количество символов равное параметру Длина, вместо них подставляет Выражение2 ;
• SUBSTRING(Выражение, Начало, Длина) - из Выражения выводится строка заданной Длины начиная с позиции Начало ;
• UNICODE(Char) - выводит код в формате Unicode первого символа в строке Char ;
• LOWER(Char) - переводит строку Char в маленькие буквы;
• UPPER(Char) - переводит строку Char в заглавные буквы.

Примеры применения строковых функций:

• SELECT ASCII('G') результат 71.
• SELECT LOWER('ABC') результат abc.
• SELECT RIGHT('ABCDE',3) результат CDE
• SELECT REVERSE('МИР') результат РИМ.

Замечание. Во всех строковых функциях значения выражения типа Char заключаются в одинарные кавычки.

Функции дат

Замечание: в некоторых функциях дат используется так называемая часть дат, которая кодируется специальными символами:

• dd - число дат (от 1 до 31);
• dy - день года (число от 1 до 366);
• hh - значение часа (0-23)
• ms - значение милисекунд (от 0 до 999)
• mi - значение минут (0-59)
• qq - значение (1-4)
• mm - значение месяцев (1-12)
• ss - значение секунд (0-59)
• wk - значение номеров недель в году
• dw - значение дней недели, неделя начинается с воскресенья (1-7).
• yy - значение лет (1753 -999)

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

• DATEADD(часть, число, date) - добавляет к дате date часть даты увеличенное на число;
• DATEDIFF(часть, date1, date2) - выводит количество частей даты между date1 и date2 ;
• DATENAME(часть, date) - выводит символьное значение частей даты к заданной дате (название дней недели);
• DATEPART(часть, date) - выводит числовое значение части даты из заданной даты (номер месяца);
• DAY(date) - выводит количество дней в заданной дате;
• MONTH (date) - выводит количество месяцев в заданной дате;
• YEAR(date) - выводит количество лет в заданной дате;
• GETDATE() - выводит текущую дату установленную на компьютере;

Замечание: Даты выводятся в Американском формате: месяц/день/год.

Примеры функции работ с датами:

• SELECT DATEADD(dd,5,11/20/07) результат Nov/25/2007.
• SELECT DATEDIFF(dd,11/20/07, 11/25/07) результат 5 дней.
• SELECT DATENAME(mm, 11/20/07) результат November.
• SELECT DATEPART(mm, 11/20/07) результат 11.

Замечание: В выражениях оператора SELECT можно использовать операции сравнения. В результате будет либо истина TRUE, либо ложь FALSE. Можно использовать следующие операторы: =, <, >, >=, <=, <>, !<(не меньше), !>(не больше), !=(не равно). Приоритет операции задается круглыми скобками.

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

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

• COL_LENGTH(таблица, поле) - выводит ширину поля;
• DATALENGTH(выражение) - выводит длину выражения;
• GETANSINULL(имя БД) - выводит допустимо или недопустимо использовать в БД значение NULL ;
• IDENT_INCR(таблица) - выводит шаг увеличения поля счетчика в таблице;
• IDENT_SEED(таблица) - выводит начальное значение счетчиков в таблице;
• ISDATE(выражение) - выводит единицу, если выражение является датой и ноль, если не является;
• ISNUMERIC(выражение) - выводит единицу, если выражение является числовым и ноль, если не числовым;
• NULIFF(выражение1, выражение2) - выводит NULL если выражение1 равно выражению 2.

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

Агрегатные функции - позволяют вычислять итоговые значения по полям таблицы.

• AVG(поле) - выводит среднее значение поля;
• COUNT(*) - выводит количество записей в таблице;
• COUNT(поле) - выводит количество всех значений поля;
• MAX(поле) - выводит максимальное значение поля;
• MIN(поле) - выводит минимальное значение поля;
• STDEV(поле) - выводит среднеквадратичное отклонение всех значений поля;
• STDEVP(поле) - выводит среднеквадратичное отклонение различных значений поля;
• SUM(поле) - суммирует все значения поля;
• TOP n [Percent] - выводит n первых записей из таблицы, либо n% записей из таблицы;
• VAR(поле) - выводит дисперсию всех значений поля;
• VARP(поле) - выводит дисперсию всех различных значений поля.

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

• SELECT AVG(возраст) FROM Студенты - выводит средний возраст студента из таблицы "Студенты".
• SELECT COUNT(ФИО) FROM Студенты - выводит количество различных ФИО из таблицы "Студенты".
• SELECT Top 100 * FROM Студенты - выводит первые 100 студентов из таблицы "Студенты".

На этом мы заканчиваем рассмотрение запросов и фильтров. Дополнительную информацию можно найти в "лабораторной работе №4" .