5.5.3. Вычисления внутри оператора select

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

Выражения над полями

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

К символьным операциям относится конкатенация двух строк и выделение подстроки. Операция конкатенации записывается в виде двух вертикальных черт: '||'. Операция выделения подстроки записывается с помощью квадратных скобок, в которых через запятую указан номер первого и последнего символа в подстроке. Рассмотрим примеры:

SELECT lname[4,9] FROM companies

"Рога и Ко" " Добро по" "Т Сделай "

SELECT lname || " " || fname FROM persons

"Антонов Сергей" "Шапокляк Алексей" "Антонов Антон" "Бендер Остап"

Обратите внимание, что в последнем примере результат - это четыре ряда, каждый из которых состоит из одной строки. Если бы мы выполнили запрос

SELECT lname, fname FROM persons

то получили бы четыре ряда по два строковых значения в каждом.

В последнем примере мы также использовали и текстовую константу - строку из одного пробела, заключенную в кавычки. Можно использовать строковые константы любой длины и состоящие из любых символов. Если внутри константы надо использовать символ "кавычки", то следует написать его подряд дважды. В символьных выражениях Вы можете использовать и не символьные выражения ­операция преобразования типов (например, числа 1234 в строку "1234") будет произведена автоматически.

К арифметическим операциям относятся операции сложения ('+'), вычитания ('-'), умножения ('*') и деления ('/'). Пример:

SELECT price*1.2 FROM items

SELECT tax*quantity/1.2 FROM orders

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

Функция определения длины: LENGTH(<имя поля>) - вычисляет длину строки, которая LENGTH(<текстовая строка>) содержится в указанном поле записи или указанной текстовой констант;

Временные функции: DATE (<выражение не типа DATE>) - преобразует выражение типа CHAR, INTEGER или DATETIMEв значение типа DATE; DAY(<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер дня в месяце; MDY(<месяц>, <день>, <год>) - по заданным трем целым значением, определяющим номер месяца, дня, года строится значение типа DATE; MONTH (<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер месяца в годе; WEEKDAY (<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер дня недели, причем восвкресенью соответсвует значение 0, понедельнику - 1 и т.д.; YEAR (<<выражение типа DATE или DATETIME>) - по заданному в качестве аргумента значению вычисляет номер года, состоящий из 4-х цифр;

Функции преобразования HEX (<целое выражение>) - преобразует целое, заданное в качестве аргумента, в строку, представляющую шестнадцатеричную запись данного целого; ROUND (<числовое выражение, точность>) - округляет с указанной точностью числовое выражение, заданное в качестве аргумента; точность определяет степень одной десятой, до которой надо округлять. TRUNC (<числовое выражение, точность>) - обрезает незначащие цифры у заданного в качестве аргумента выражения, причем точность определяет смещение влево от запятой последней значащей цифры;

Математические функции: ACOS(<числовое выражение>) - арккосинус от заданного значения; ASIN(<числовое выражение>) - арксинус от заданного значения; ATAN (<числовое выражение>) - арктангенс от заданного значения; COS(<числовое выражение>) - косинус от заданного числа радиан; SIN(<числовое выражение>) - синус от заданного числа радиан; TAN (<числовое выражение>) - тангенс от заданного числа радиан; ABS (<числовое выражение>) - абсолютное значение; EXP (<степень>) - вычисление заданной экспоненты; LOGN (<выражение>) - вычисление натурального логарифма; LOG10 (<выражение>) - вычисление десятичного логарифма; MOD (<делимое>, <делитель>) - остаток от деления; POW (<значение>, <степень>) - возведение в степень; ROOT (<значение>, <степень>) - вычисление корня заданной степени; SQRT (<числовое выражение>) - квадратный корень;

Функции – псевдополя CURRENT <диапазон> - возвращает точный момент времени (тип DATETIME), когда происхoдит обработка данного запроса; параметр <диапазон> задает (как и при описании типа DATETIME) требуемое точность - от года до тысячных секунды (например, CURRENT DAY TO SECOND); TODAY - возвращает дату (тип DATE) выполнения даного запроса; USER - возвращает имя пользователя (как он зарегестрировался в операционной системе), который выполняет данный запрос.

Следует напомнить про специальное значение NULL - "не определено". Если это значение встретится в выражении, то и все выражение будет иметь значение NULL. Даже умножив нуль на NULL, Вы все равно получите NULL. Конкатенация любой строки с неопределенной строкой (то есть строкой, имеющей значение NULL) тоже в результате даст NULL.

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

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

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

SELECT MAX(price) FROM items

В этом запросе будут просмотрены все записи из таблицы items и будет определено максимальное значение среди всех полей price.

Если мы хотим просто узнать, сколько записей получается в результирующей выборке, то надо использовать агрегатную функцию COUNT(*). Например, для того, чтобы получить число предложений поставок джинсов (сколько раз в таблице items имеется записей с названием товара "джинсы"), надо выполнить запрос

SELECT COUNT(*) FROM items WHERE name="джинсы"

А если мы хотим получить пару значений - минимальная и максимальная цены на кеды, то это достигается следующим запросом:

SELECT MIN(price), MAX(price) FROM items WHERE name="кеды"

Некоторые агрегатные функции перечислены в таблице:

AVG(<имя поля>) - среднее по всем значениям данного поля; COUNT(*) - количество записей; MAX(<имя поля>) - максимум из всех значений данного поля; MIN(<имя поля>) - минимум по всем значениям данного поля; SUM(<имя поля>) - сумма всех значений данного поля.

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

SELECT name, COUNT(*), MAX(price) FROM items GROUP BY name

Полученные результаты могли бы быть, например, такими:

кеды 2 3000.00 джинсы 4 46000.00 рога 1 999999.85

В списке выбираемых полей при использовании группирования могут указываться кроме агрегатных функций только те поля, по которым производится группировка. Да это и понятно - ведь одному значению поля из GROUP BY может соответсвовать несколько записей и непонятно, какое значение несгруппированного поля использовать в результирующей выборке.

Можно наложить дополнительное условие на группирование данных с помощью ключевого слова HAVING, указываемого после раздела GROUP BY. В разделе HAVING указываются условие на группу, то есть группы, не удовлетворяющие условию, будут отсутствовать в результирующей выборке. Например, мы хотим выполнить предыдущий запрос, но нам не нужна информация о товарах, поставляемых только одним поставщиком. Для этого построим следующий запрос:

SELECT name, COUNT(*), MAX(price) FROM items GROUP BY name HAVING COUNT(*) > 1

и получим следующие результаты:

кеды 2 3000.00 джинсы 4 46000.00