Спонсор: Microsoft
Опубликован: 22.11.2010 | Доступ: свободный | Студентов: 4106 / 1007 | Оценка: 4.44 / 4.34 | Длительность: 09:23:00
Лекция 4:

Создание запросов и фильтров. Вычисление при помощи оператора 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) - десятичный логарифм log10;
  • 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" .

Татьяна Казакова
Татьяна Казакова
Олег Андриевский
Олег Андриевский

Также весьма интересен фильтр данных, вводимый вручную:

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        If Trim(TextBox1.Text) = "" Then
            Me.СтудентыBindingSource.RemoveFilter()
        Else
            Me.СтудентыBindingSource.Filter = String.Format("{0} LIKE '{1}%'", "ФИО", Trim(TextBox1.Text))
        End If
    End Sub

Санжар Жумабаев
Санжар Жумабаев
Казахстан, Астана
Евгений Попов
Евгений Попов
Россия, Москва, МИПК при МГТУ им. Баумана, 2003