Вредоносное ПО (malware) - это назойливые или опасные программы,...
ВЫЧИСЛЕНИЯ
Итоговые функции
В выражениях SQL-запросов нередко требуется выполнить предварительную обработку данных. С этой целью используются специальные функции и выражения.
Довольно часто требуется узнать, сколько записей соответствует тому или иному запросу, какова сумма значений некоторого числового столбца, его максимальное, минимальное и среднее значения. Для этого служат так называемые итоговые (статистические, агрегатные) функции. Итоговые функции обрабатывают наборы записей, заданные, например, выражением WHERE. Если их включить в список столбцов, следующий за оператором SELECT, то результатная таблица будет содержать не только столбцы таблицы базы данных, но и значения, вычисленные с помощью этих функций. Далее приведен список итоговых функций .
- COUNT (параметр ) возвращает количество записей, указанных в параметре. Если требуется получить количество всех записей, то в качестве параметра следует указать символ звездочки (*). Если в качестве параметра указать имя столбца, то функция вернет количество записей, в которых этот столбец имеет значения, отличные от NULL. Чтобы узнать, сколько различных значений содержит столбец, перед его именем следует указать ключевое слово DISTINCT. Например:
SELECT COUNT(*) FROM Клиенты;
SELECT COUNT(Сумма_заказа) FROM Клиенты;
SELECT COUNT(DISTINCT Сумма_заказа) FROM Клиенты;
Попытка выполнить следующий запрос приведет к сообщению об ошибке:
SELECT Регион , COUNT(*) FROM Клиенты ;
- SUM (параметр ) возвращает сумму значений указанного в параметре столбца. Параметр может представлять собой и выражение, содержащее имя столбца. Например:
SELECT SUM (Сумма_заказа) FROM Клиенты;
Данное SQL-выражение возвращает таблицу, состоящую из одного столбца и одной записи и содержащую сумму всех определенных значений столбца Сумма_заказа из таблицы Клиенты.
Допустим, что в исходной таблице значения столбца Сумма_заказа выражены в рублях, а нам требуется вычислить общую сумму в долларах. Если текущий обменный курс равен, например, 27,8, то получить требуемый результат можно с помощью выражения:
SELECT SUM (Сумма_заказа*27.8) FROM Клиенты;
- AVG (параметр ) возвращает среднее арифметическое всех значений указанного в параметре столбца. Параметр может представлять собой выражение, содержащее имя столбца. Например:
SELECT AVG (Сумма_заказа) FROM Клиенты;
SELECT AVG (Сумма_заказа*27.8) FROM Клиенты
WHERE Регион <> "Северо_3апад";
- МАХ (параметр ) возвращает максимальное значение в столбце, указанном в параметре. Параметр может также представлять собой выражение, содержащее имя столбца. Например:
SELECT МАХ(Сумма__заказа) FROM Клиенты;
SELECT МАХ(Сумма_заказа*27.8) FROM Клиенты
W HERE Регион <> "Северо_3апад";
- MIN (параметр ) возвращает минимальное значение в столбце, указанном в параметре. Параметр может представлять собой выражение, содержащее имя столбца. Например:
SELECT MIN(Сумма_заказа) FROM Клиенты;
SELECT MIN (Сумма__заказа*27 . 8) FROM Клиенты
W HERE Регион <> "Северо_3апад";
На практике нередко требуется получить итоговую таблицу, содержащую суммарные, усредненные, максимальные и минимальные значения числовых столбцов. Для этого следует использовать группировку (GROUP BY) и итоговые функции.
SELECT Регион, SUM (Сумма_заказа) FROM Клиенты
GROUP BY Регион;
Результатная таблица для данного запроса содержит имена регионов и итоговые (общие) суммы заказов всех клиентов из соответствующих регионов (рис. 5).
Теперь рассмотрим запрос на получение всех итоговых данных по регионам:
SELECT Регион, SUM (Сумма_заказа), AVG (Сумма_заказа), МАХ(Сумма_заказа), MIN (Сумма_заказа)
FROM Клиенты
GROUP BY Регион;
Исходная и результатная таблицы показаны на рис. 8. В примере только Северо-Западный регион представлен в исходной таблице более чем одной записью. Поэтому в результатной таблице для него различные итоговые функции дают различные значения.
Рис. 8. Итоговая таблица сумм заказов по регионам
При использовании итоговых функций в списке столбцов в операторе SELECT заголовки соответствующих им столбцов в результатной таблице имеют вид Expr1001, Expr1002 и т.д. (или что-нибудь аналогичное, в зависимости от реализации SQL). Однако заголовки для значений итоговых функций и других столбцов вы можете задавать по своему усмотрению. Для этого достаточно после столбца в операторе SELECT указать выражение вида:
AS заголовок_столбца
Ключевое слово AS (как) означает, что в результатной таблице соответствующий столбец должен иметь заголовок, указанный после AS. Назначаемый заголовок еще называют псевдонимом. В следующем примере (рис. 9) задаются псевдонимы для всех вычисляемых столбцов:
SELECT Регион,
SUM (Сумма_заказа) AS [Общая сумма заказа],
AVG (Сумма_заказа) AS [Средняя сумма заказа],
МАХ(Сумма_заказа) AS Максимум,
MIN (Сумма_заказа) AS Минимум,
FROM Клиенты
GROUP BY Регион;
Рис. 9. Итоговая таблица сумм заказов по регионам с применением псевдонимов столбца
Псевдонимы, состоящие из нескольких слов, разделенных пробелами, заключаются в квадратные скобки.
Итоговые функции можно использовать в выражениях SELECT и HAVING, но их нельзя применять в выражении WHERE. Oneратор HAVING аналогичен оператору WHERE, но в отличие от WHERE он отбирает записи в группах.
Допустим, требуется определить, в каких регионах более одного клиента. С этой целью можно воспользоваться таким запросом:
SELECT Регион , Count(*)
FROM Клиенты
GROUP BY Регион HAVING COUNT(*) > 1;
Функции обработки значений
При работе с данными часто приходится их обрабатывать (преобразовывать к нужному виду): выделить в строке некоторую подстроку, удалить ведущие и заключительные пробелы, округлить число, вычислить квадратный корень, определить текущее время и т. п. В SQL имеются следующие три типа функций:
- строковые функции;
- числовые функции;
- функции даты-времени.
Строковые функции
Строковые функции принимают в качестве параметра строку и возвращают после ее обработки строку или NULL.
- SUBSTRING (строка FROM начало ) возвращает подстроку, получающуюся из строки, которая указана в качестве параметра строка . Подстрока начинается с символа, порядковый номер которого указан в параметре начало, и имеет длину, указанную в параметре длина. Нумерация символов строки ведется слева направо, начиная с 1. Квадратные скобки здесь указывают лишь на то, что заключенное в них выражение не является обязательным. Если выражение FOR длина не используется, то возвращается подстрока от начало и до конца исходной строки. Значения параметров начало и длина должны выбираться так, чтобы искомая подстрока действительно находилась внутри исходной строки. В противном случае функция SUBSTRING вернет NULL.
Например:
SUBSTRING ("Дорогая Маша!" FROM 9 FOR 4) возвращает "Маша";
SUBSTRING ("Дорогая Маша! " FROM 9) возвращает "Маша! ";
SUBSTRING("Дорогая Маша! " FROM 15) возвращает NULL.
Использовать эту функцию в SQL-выражении можно, например, так:
SELECT * FROM Клиенты
WHERE SUBSTRING(Регион FROM 1 FOR 5) = "Север";
- UPPER (строка ) переводит все символы указанной в параметре строки в верхний регистр.
- LOWER (строка ) переводит все символы указанной в параметре строки в нижний регистр.
- TRIM (LEADING | TRAILING | BOTH ["символ"] FROM строка ) удаляет ведущие (LEADING), заключительные (TRAILING) или те и другие (BOTH) символы из строки. По умолчанию удаляемым символом является пробел (" "), поэтому его можно не указывать. Чаще всего эта функция используется именно для удаления пробелов.
Например:
TRIM (LEADING " " FROM "город Санкт-Петербург") вращает " город Санкт-Петербург ";
TRIM(TRALING " " FROM "город Санкт-Петербург") возвращает "город Санкт-Петербург";
TRIM (BOTH " " FROM " город Санкт-Петербург ") возвращает "город Санкт-Петербург";
TRIM(BOTH FROM " город Санкт-Петербург ") возвращает "город Санкт-Петербург";
TRIM(BOTH "г" FROM "город Санкт-Петербург") возвращает "ород Санкт-Петербур".
Среди этих функций наиболее часто используемые - SUBSTRING() И TRIM().
Числовые функции
Числовые функции в качестве параметра могут принимать данные не только числового типа, но возвращают всегда число или NULL (неопределенное значение).
- POSITION (целеваяСтрока IN строка ) ищет вхождение целевой строки в указанную строку. В случае успешного поиска возвращает номер положения ее первого символа, иначе 0. Если целевая строка имеет нулевую длину (например, строка " "), то функция возвращает 1. Если хотя бы один из параметров имеет значение NULL, то возвращается NULL. Нумерация символов строки ведется слева направо, начиная с 1.
Например:
POSITION ("e" IN "Привет всем") возвращает 5;
POSITION ("всeм" IN "Привет всем") возвращает 8;
POSITION (" " Привет всем") возвращает 1;
POSITION("Привет!" IN "Привет всем") возвращает 0.
В таблице Клиенты (см. рис. 1) столбец Адрес содержит, кроме названия города, почтовый индекс, название улицы и другие данные. Возможно, вам потребуется выбрать записи о клиентах, проживающих в определенном городе. Так, если требуется выбрать записи, относящиеся к клиентам, проживающим в Санкт-Петербурге, то можно воспользоваться следующим выражением SQL-запроса:
SELECT * FROM Клиенты
WHERE POSITION (" Санкт - Петербург " IN Адрес ) > 0;
Заметим, что этот простой запрос на выборку данных можно сформулировать иначе:
SELECT * FROM Клиенты
WHERE Адрес LIKE " %Петербург% ";
- EXTRACT (параметр ) извлекает элемент из значения типа дата-время или из интервала. Например:
EXTRACT (MONTH FROM DATE "2005-10-25") возвращает 10.
- CHARACTER_LENGTH (строка ) возвращает количество символов в строке.
Например:
CHARACTER_LENGTH("Привет всем") возвращает 11.
- OCTET_LENGTH (строка ) возвращает количество октетов (байтов) в строке. Каждый символ латиницы или кириллицы представляется одним байтом, а символ китайского алфавита двумя байтами.
- CARDINALITY (параметр ) принимает в качестве параметра коллекцию элементов и возвращает количество элементов в коллекции (кардинальное число). Коллекция может быть, например, массивом или мультимножеством, содержащим элементы различных типов.
- ABS (число ) возвращает абсолютное значение числа. Например:
ABS (-123) возвращает 123;
ABS (2 - 5) возвращает 3.
- МО D (число1, число2 ) возвращает остаток от целочисленного деления первого числа на второе. Например:
MOD (5, з) возвращает 2;
MOD (2, з) возвращает 0.
- LN (число ) возвращает натуральный логарифм числа.
- ЕХР (число ) возвращает е число (основание натурального логарифма в степени число).
- POWER (число1, число2 ) возвращает число1 число2 (число1 в степени число2).
- SQRT (число ) возвращает квадратный корень из числа.
- FLOOR (число ) возвращает наибольшее целое число, не превышающее заданное параметром (округление в меньшую сторону). Например:
FLOOR (5.123) возвращает 5.0.
- CEIL (число ) или CEILING (число ) возвращает наименьшее целое число, которое не меньше заданного параметром округление в большую сторону). Например:
CEIL (5.123) возвращает 6. 0.
- WIDTH_BUCKET (число1, число2, числоЗ, число4) возвращает целое число в диапазоне между 0 и число4 + 1. Параметры число2 и числоЗ задают числовой отрезок, разделенный на равновеликие интервалы, количество которых задается параметром число 4. Функция определяет номер интервала, в который попадает значение число1. Если число1 находится за пределами заданного диапазона, то функция возвращает 0 или число 4 + 1. Например:
WIDTH_BUCKET(3.14, 0, 9, 5) возвращает 2.
Функции даты-времени
В языке SQL имеются три функции, которые возвращают текущие дату и время.
- CURRENT_DATE возвращает текущую дату (тип DATE).
Например: 2005-06-18.
- CURRENT_TIME (число ) возвращает текущее время (тип TIME). Целочисленный параметр указывает точность представления секунд. Например, при значении 2 секунды будут представлены с точностью до сотых (две цифры в дробной части):
12:39:45.27.
- CURRENT_TIMESTAMP (число ) возвращает дату и время (тип TIMESTAMP). Например, 2005-06-18 12:39:45.27. Целочисленный параметр указывает точность представления секунд.
Обратите внимание, что дата и время, возвращаемые этими функциями, имеют не символьный тип. Если требуется представить их в виде символьных строк, то для этого следует использовать функцию преобразования типа CAST ().
Функции даты-времени обычно применяются в запросах на вставку, обновление и удаление данных. Например, при записи сведений о продажах в специально предусмотренный для этого столбец вносятся текущие дата и время. После подведения итогов за месяц или квартал, данные о продажах за отчетный период можно удалить.
Вычисляемые выражения
Вычисляемые выражения строятся из констант (числовых, строковых, логических), функций, имен полей и данных других типов путем соединения их арифметическими, строковыми, логическими и другими операторами. В свою очередь, выражения могут быть объединены посредством операторов в более сложные (составные) выражения. Для управления порядком вычисления выражений используются круглые скобки.
Логические операторы AND, OR и NOT и функции были рассмотрены ранее.
Арифметические операторы:
- + сложение;
- - вычитание;
- * умножение;
- / деление.
Строковый оператор только один оператор конкатенации или склейки строк (| |). В некоторых реализациях SQL (например, Microsoft Access) вместо (| |) используется символ (+). Оператор конкатенации приписывает вторую строку к концу первой пример, выражение:
"Саша" | | "любит" | | " Машу"
вернет в качестве результата строку " Сашалюбит Машу".
При составлении выражений необходимо следить, чтобы операнды операторов имели допустимые типы. Например, выражение: 123 + "Саша" недопустимо, поскольку арифметический оператор сложения применяется к строковому операнду.
Вычисляемые выражения могут находиться после оператора SELECT, а также в выражениях условий операторов WHERE и HAVI NG .
Рассмотрим несколько примеров.
Пусть таблица Продажи содержит столбцы Тип_товара, Количество и Цена, а нам требуется знать выручку для каждого типа товара. Для этого достаточно в список столбцов после оператора SELECT включить выражение Количество*Цена:
SELECT Тип_товара, Количество, Цена, Количество*Цена AS
Итого FROM Продажи;
Здесь используется ключевое слово AS (как) для задания псевдонима столбца с вычисляемыми данными.
На рис. 10 показаны исходная таблица Продажи и результатная таблица запроса.
Рис. 10. Результат запроса с вычислением выручки по каждому типу товара
Если требуется узнать общую выручку от продажи всех товаров, то достаточно применить следующий запрос:
SELECT SUM (Количество*Цена) FROM Продажи;
Следующий запрос содержит вычисляемые выражения и в списке столбцов, и в условии оператора WHERE. Он выбирает из таблицы продажи те товары, выручка от продажи которых больше 1000:
SELECT Тип_товара, Количество*Цена AS Итого
FROM Продажи
WHERE Количество*Цена > 1000;
Предположим, что требуется получить таблицу, в которой два столбца:
Товар, содержащий тип товара и цену;
Итого, содержащий выручку.
Поскольку предполагается, что в исходной таблице продажи столбец Тип_товара является символьным (тип CHAR), а столбец Цена числовой, то при объединении (склейке) данных из этих столбцов необходимо выполнить приведение числового типа к символьному с помощью функции CAST (). Запрос, выполняющий это задание, выглядит так (рис. 11):
SELECT Тип_товара | | " (Цена: " | | CAST(Цена AS CHAR(5)) | | ")" AS Товар, Количество*Цена AS Итого
FROM Продажи;
Рис. 11. Результат запроса с объединением разнотипных данных в одном столбце
Примечание. В Microsoft Access аналогичный запрос будет иметь следующий вид:
SELECT Тип_товара + " (Цена: " + C Str (Цена) + ")" AS Товар,
Количество*Цена AS Итого
FROM Продажи;
Условные выражения с оператором CASE
В обычных языках программирования имеются операторы условного перехода, которые позволяют управлять вычислительным процессом в зависимости от того, выполняется или нет некоторое условие. В языке SQL таким оператором является CASE (случай, обстоятельство, экземпляр ). В SQL:2003 этот оператор возвращает значение и, следовательно, может использоваться в выражениях. Он имеет две основные формы, которые мы рассмотрим в данном разделе.
Оператор CASE со значениями
Оператор CASE со значениями имеет следующий синтаксис:
CASE проверяемое_значение
WHEN значение1 THEN результат1
WHEN значение2 THEN резулътат2
. . .
WHEN значением N THEN результат N
ELSE результатХ
В случае, когда проверяемое_значение равно значение1 , оператор CASE возвращает значение результат1 , указанное после ключевого слова THEN (то). В противном случае проверяемое_значение сравнивается с значение2 , и если они равны, то возвращается значение результат2. В противном случае проверяемое значение сравнивается со следующим значением, указанным после ключевого слова WHEN (когда) и т. д. Если проверяемое_значение не равно ни одному из таких значений, то возвращается значение результат X , указанное после ключевого слова ELSE (иначе).
Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из значений, подлежащих сравнению, не равно проверяемому значению, то оператор CASE возвращает NULL.
Допустим, на основе таблицы Клиенты (см. рис. 1) требуется получить таблицу, в которой названия регионов заменены их кодовыми номерами. Если в исходной таблице различных регионов не слишком много, то для решения данной задачи удобно воспользоваться запросом с оператором CASE:
SELECT Имя , Адрес ,
CASE Регион
WHEN " Москва " THEN "77"
WHEN "Тверская область" THEN "69"
. . .
ELSE Регион
AS Код региона
FROM Клиенты;
Оператор CASE с условиями поиска
Вторая форма оператора CASE предполагает его использование при поиске в таблице тех записей, которые удовлетворяют определенному условию:
CASE
WHEN условие1 THEN результат1
WHEN уоловие2 THEN результат2
. . .
WHEN условие N THEN результат N
ELSE результатХ
Оператор CASE проверяет, истинно ли условие1 для первой записи в наборе, определенном оператором WHERE, или во всей таблице, если WHERE отсутствует. Если да, то CASE возвращает значение результат1. В противном случае для данной записи проверяется условие2. Если оно истинно, то возвращается значение результат2 и т. д. Если ни одно из условий не выполняется, то возвращается значение результат X , указанное после ключ го слова ELSE.
Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из условий не выполняется, оператор CASE вращает NULL. После того как оператор, содержащий CASE, выполнится для первой записи, происходит переход к следующей записи. Так продолжается до тех пор, пока не будет обработан весь набор записей.
Предположим, в таблице книги (Название, Цена) столбец имеет значение NULL, если соответствующей книги нет в наличии. Следующий запрос возвращает таблицу, в которой вместо NULL отображается текст "Нет в наличии":
SELECT Название,
CASE
WHEN Цена IS NULL THEN " Нет в наличии "
ELSE CAST(Цена AS CHAR(8))
AS Цена
FROM Книги;
Все значения одного и того же столбца должны иметь одинаковые типы. Поэтому в данном запросе используется функция преобразования типов CAST для приведения числовых значений столбца Цена к символьному типу.
Обратите внимание, что вместо первой формы оператора CASE всегда можно использовать вторую:
CASE
WHEN проверяемое_значение = значение1 THEN результат1
WHEN проверяемое_значение = значение2 THEN результат2
. . .
WHEN проверяемое_значение = значение N THEN peзyльтaтN
ELSE резулътатХ
Функции NULLIF и COALESCE
В ряде случаев, особенно в запросах на обновление данных (оператор UPDATE), удобно использовать вместо громоздкого оператора CASE более компактные функции NULLIF () (NULL, если) и COALESCE() (объединять).
Функция NULLIF (значение1, значение2 ) возвращает NULL, если значение первого параметра соответствует значению второго параметра, в случае несоответствия возвращается значение первого параметра без изменений. То есть если равенство значение1 = значение2 выполняется, то функция возвращает NULL, иначе значение значение1.
Данная функция эквивалентна оператору CASE в следующих двух формах:
- CASE значение1
WHEN значение2 THEN NULL
ELSE значение1
- CASE
WHEN значение1 = значение2 THEN NULL
ELSE значение1
Функция COALESCE(значение1, значение2, ... , значение N ) принимает список значений, которые могут быть как определенными, так и неопределенными (NULL). Функция возвращает определенное значение из списка или NULL, если все значения не определены.
Данная функция эквивалентна следующему оператору CASE:
CASE
WHEN значение 1 IS NOT NULL THEN значение 1
WHEN значение 2 IS NOT NULL THEN значение 2
. . .
WHEN значение N IS NOT NULL THEN значение N
ELSE NULL
Предположим, что в таблице Книги (Название, Цена) столбец Цена имеет значение NULL, если соответствующей книги нет в наличии. Следующий запрос возвращает таблицу, в которой вместо NULL отображается текст "Нет в наличии":
SELECT Название, COALESCE (CAST(Цена AS CHAR(8)),
"Нет в наличии") AS Цена
FROM Книги;
SQL - Урок 11. Итоговые функции, вычисляемые столбцы и представления
Итоговые функции еще называют статистическими, агрегатными или суммирующими. Эти функции обрабатывают набор строк для подсчета и возвращения одного значения. Таких функций всего пять:- AVG() Функция возвращает среднее значение столбца.
- COUNT() Функция возвращает число строк в столбце.
- MAX() Функция возвращает самое большое значение в столбце.
- MIN() Функция возвращает самое маленькое значение в столбце.
- SUM() Функция возвращает сумму значений столбца.
SELECT MIN(price), MAX(price), AVG(price) FROM prices;
Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик "Дом печати" (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:
1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком "Дом печати" (id=2):
2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:
3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:
4. В получившейся таблице явно не хватает столбца Сумма, то есть вычисляемого столбца . Возможность создания таких столбцов предусмотрена в MySQL. Для этого надо лишь указать в запросе имя вычисляемого столбца и что он должен вычислять. В нашем примере такой столбец будет называться summa, а вычислять он будет произведение столбцов quantity и price. Название нового столбца отделяется словом AS:
SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик "Дом печати". Синтаксис для использования функции SUM() следущий:
SELECT SUM(имя_столбца) FROM имя_таблицы;
Имя столбца нам известно - summa, а вот имени таблицы у нас нет, так как она является результатом запроса. Что же делать? Для таких случаев в MySQL существуют Представления . Представление - это запрос на выборку, которому присваивается уникальное имя и который можно сохранять в базе данных, для последующего использования.
Синтаксис создания представления следующий:
CREATE VIEW имя_представления AS запрос;
Давайте сохраним наш запрос, как представление с именем report_vendor:
CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
6. Вот теперь можно использовать итоговую функцию SUM() :
SELECT SUM(summa) FROM report_vendor;
Вот мы и достигли результата, правда для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. Да, иногда для получения результата приходится подумать, без этого никуда. Зато мы коснулись двух очень важных тем - вычисляемые столбцы и представления. Давайте поговорим о них поподробнее.
Вычисляемые поля (столбцы)
На примере мы рассмотрели сегодня математическое вычисляемое поле. Здесь хотелось бы добавить, что использовать можно не только операцию умножения (*), но и вычитание (-), и сложение (+), и деление (/). Синтаксис следующий:SELECT имя_столбца_1, имя_столбца_2, имя_столбца_1*имя_столбца_2 AS имя_вычисляемого_столбца FROM имя_таблицы;
Второй нюанс - ключевое слово AS, мы его использовали для задания имени вычисляемого столбца. На самом деле с помощью этого ключевого слова задаются псевдонимы для любых столбцов. Зачем это нужно? Для сокращения и читаемости кода. Например, наше представление могло бы выглядеть так:
CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, prices AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
Согласитесь, что так гораздо короче и понятнее.
Представления
Синтаксис создания представлений мы уже рассматривали. После создания представлений, их можно использовать так же, как таблицы. То есть выполнять запросы к ним, фильтровать и сортировать данные, объединять одни представления с другими. С одной стороны это очень удобный способ хранения частоприменяемых сложных запросов (как в нашем примере).Но следует помнить, что представления - это не таблицы, то есть они не хранят данные, а лишь извлекают их из других таблиц. Отсюда, во-первых, при изменении данных в таблицах, результаты представления так же будут меняться. А во-вторых, при запросе к представлению происходит поиск необходимых данных, то есть производительность СУБД снижается. Поэтому злоупотреблять ими не стоит.
В этом учебном пособии вы узнаете, как использовать MySQL функцию SUM
с синтаксисом и примерами.
Описание
MySQL функция SUM возвращает суммарное значение выражения.
Синтаксис
Синтаксис MySQL функции SUM:
ИЛИ Синтаксис MySQL функции SUM при группировке результатов по одному или нескольким столбцам:
Параметры или аргументы
expression1 , expression2 , … expression_n — выражения, которые не инкапсулированы в функции SUM и должны быть включены в предложение GROUP BY в конце SQL оператора.
aggregate_expression — это столбец или выражение, которое будет суммироваться.
tables — таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, указанная в предложении FROM.
WHERE conditions — необязательный. Это условия, которые должны быть выполнены для выбранных записей.
Применение
Функция SUM может использоваться в следующих версиях MySQL:
- MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
Пример с одним выражением
Рассмотрим примеры MySQL функции SUM, чтобы понять, как использовать функцию SUM в MySQL.
Например, вам необходимо узнать какая совокупная общая заработная плата всех сотрудников, чья зарплата превышает 20 000 долларов в год.
В этом примере функции SUM, выражению SUM (salary ) присвоили псевдонима «Total Salary». В результате при возврате набора результатов в качестве имени поля будет отображаться «Total Salary».
Пример с использование DISTINCT
Вы можете использовать предложение DISTINCT в функции SUM. Например, приведенная ниже инструкция SQL возвращает комбинированный общий оклад уникальных значений заработной платы, где зарплата выше 20 000 долларов в год.
Если бы две зарплаты составляли 10 000 долларов в год, в функции SUM использовалось бы только одно из этих значений.
Пример с использованием формулы
Выражение, содержащееся в функции SUM, не обязательно должно быть одним полем. Вы также можете использовать формулу. Например, вы можете рассчитать общую комиссию.
MySQL
SELECT SUM(sales * 0.05) AS "Total Commission" FROM orders;
SELECT SUM (sales*0.05)AS "Total Commission" FROM orders; |
Пример с использованием GROUP BY
В некоторых случаях вам потребуется использовать предложение GROUP BY с функцией SUM.
Например, вы можете использовать функцию SUM, чтобы вернуть department (название отдела) и общий объем sales (продаж в соответствующем отделе).
В этом учебном пособии вы узнаете, как использовать функцию SUM
в SQL Server (Transact-SQL) с синтаксисом и примерами.
Описание
В SQL Server (Transact-SQL) функция SUM возвращает суммарное значение выражения.
Синтаксис
Синтаксис функции SUM в SQL Server (Transact-SQL):
ИЛИ синтаксис функции SUM при группировке результатов по одному или нескольким столбцам:
Параметры или аргументы
expression1
, expression2
, … expression_n
— выражения, которые не включены в функцию SUM и должны быть включены в оператор GROUP BY в конце SQL-предложения.
aggregate_expression
— это столбец или выражение, которое будет суммировано.
tables
— таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в операторе FROM.
WHERE conditions
— необязательный. Это условия, которые должны выполняться для выбранных записей.
Применение
Функция SUM может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Пример с одним полем
Рассмотрим некоторые примеры SQL Server функции SUM, чтобы понять, как использовать функцию SUM в SQL Server (Transact-SQL).
Например, вы можете узнать, как общее количество всех products , количество которых больше 10.
В этом примере функции SUM мы выражению SUM(quantity ) установили псевдоним «Total Quantity». При возврате результирующего набора — «Total Quantity» будет отображаться как имя поля.
Пример использования DISTINCT
Вы можете использовать оператор DISTINCT в функции SUM. Например, приведенный ниже оператор SQL возвращает общую сумму salary с уникальными значениями salary , где salary ниже 29 000 долларов в год.
Если бы две salary составляли 24 000 долл. в год, в функции SUM использовалось только одно из этих значений.
Пример использования формулы
Выражение, содержащееся в функции SUM, не обязательно должно быть одним полем. Вы также можете использовать формулу. Например, вы можете рассчитать общую комиссию.
Transact-SQL
SELECT SUM(sales * 0.03) AS "Total Commission" FROM orders;
SELECT SUM (sales * 0.03 ) AS "Total Commission " FROM orders ; |
Пример использования GROUP BY
В некоторых случаях вам потребуется использовать оператор GROUP BY с функцией SUM.
Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.
Функция SQL SUM
Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:
SELECT SUM (ИМЯ_СТОЛБЦА) ...
После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).
Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):
Для получения суммы размеров всех заработных плат используем следующий запрос:
SELECT SUM (Salary) FROM Staff
Этот запрос вернёт значение 287664,63.
А теперь . В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.
Функция SQL MIN
Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.
Пример 3. База данных и таблица - те же, что и в примере 1.
Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Запрос вернёт значение 10505,90.
И вновь упражнение для самостоятельного решения . В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:
Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.
Функция SQL MAX
Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.
Пример 5.
Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Запрос вернёт значение 18352,80
Пришло время упражнения для самостоятельного решения .
Пример 6. Вновь работаем с двумя таблицами - Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц) .
Функция SQL AVG
Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.
Пример 7. База данных и таблица - те же, что и в предыдущих примерах.
Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Результатом будет значение 6,33
Пример 8. Работаем с одной таблицей - Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.
Функция SQL COUNT
Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) ..., то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) ..., то результатом будет количество всех записей (строк) таблицы.
Пример 9. База данных и таблица - те же, что и в предыдущих примерах.
Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm - не NULL, вернёт следующий запрос:
SELECT COUNT (Comm) FROM Staff
Результатом будет значение 11.
Пример 10. База данных и таблица - те же, что и в предыдущих примерах.
Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT:
SELECT COUNT (*) FROM Staff
Результатом будет значение 17.
В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.
Пример 11. Работаем с одной таблицей - Staff. Вывести число сотрудников в отделе планирования (Plains).
Агрегатные функции вместе с SQL GROUP BY (группировкой)
Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору .
Пример 12. Есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts - о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money - о денежных суммах, вырученных за подачу объявлений.
Category | Part | Units | Money |
Транспорт | Автомашины | 110 | 17600 |
Недвижимость | Квартиры | 89 | 18690 |
Недвижимость | Дачи | 57 | 11970 |
Транспорт | Мотоциклы | 131 | 20960 |
Стройматериалы | Доски | 68 | 7140 |
Электротехника | Телевизоры | 127 | 8255 |
Электротехника | Холодильники | 137 | 8905 |
Стройматериалы | Регипс | 112 | 11760 |
Досуг | Книги | 96 | 6240 |
Недвижимость | Дома | 47 | 9870 |
Досуг | Музыка | 117 | 7605 |
Досуг | Игры | 41 | 2665 |
Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос:
SELECT Category, SUM (Money) AS Money FROM Ads GROUP BY Category
Пример 13. База данных и таблица - та же, что в предыдущем примере.
Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было подано наибольшее число объявлений. Пишем следующий запрос:
SELECT Category, Part, MAX (Units) AS Maximum FROM Ads GROUP BY Category
Результатом будет следующая таблица:
Итоговые и индивидуальные значения в одной таблице можно получить объединением результатов запросов с помощью оператора UNION .
Реляционные базы данных и язык SQL