Создание вычисляемых полей
7. Создание вычисляемых полей
Данные, хранимые в таблицах базы данных. Обычно бывают представлены не в таком виде, который необходим для ваших приложений. Например.
1) Необходимо отразить поле, содержащее имя компании с ее адресом, но эта информация расположена в разных столбцах таблицы.
2) Город, штат и ZIP-код хранятся в отдельных столюцах, но для программы печати почтовых наклеек необходима эта информиация в одном, корректно сформированном поле.
3) В таблице с предметами заказа хранятся цены продуктов и их количество, но не полная цена каждого продукта. Чтобы распечатать счет , необходимы полные цены.
4) Необходимы общая сумма, среднее значение или результаты других расчетов, основанных на данных, имеющихся в таблице.
Вместо того чтобы извлекать эти данные, а затем изменять их форму при помощи клиентского приложения или отчета, лучше извлекать уже преобразованные, подсчитанные или отформатированные данные прямо из базы данных. Термин поле означает то же самое, что и столбец. Хотя поле относят к вычисляемым столбцам.
База данных знает, какие столбцы в операторе SELECT являются реальными столбцами, а какие – вычисляемыми полями. С точки зрения клиента (приложения), данные вычисляемого поля возвращаются точно так же, как и данные из любого другого столбца.
Многие преобразования могут выполнить и клиентские приложения, но эти операции гораздо быстрее выполняются на сервере базы данных.
Рекомендуемые материалы
7.1. Конкатенация полей
Конкатенация значений – это присоединение их друг к другу для получения одного длинного значения.
В качестве примера создадим заголовок, состоящий из значений двух столбцов. Необходимо создать отчет поставщику и указать его адрес как часть его имени в виде имя – адрес. В отчете должно быть одно значение, а данные в таблице хранятся в двух столбцах. В SQL-выражении SELECT можно выполнить конкатенацию двух столбцов при помощи специального оператора. В зависимости от СУБД это может быть знак + или две вертикальные черточки ||. В MS Access для конкатенации используется знак +.
SELECT vend_name + ‘(‘ + vend_country + ‘)’ формирование столбца
FROM Vendors из таблицы
ORDER BY vend_name; упорядочить по столбцу
--------------------------
Expr1000
Bear Emporium (USA )
Beras R Us (USA )
Doll House Inc. (USA )
Fun and Games (England )
Furball Inc. (USA )
Jouets et ours (France )
Как видно из результата, выражение SELECT возвращает один столбец (вычисляемое поле), содержащий все четыре элемента как одно целое.
Примечание. В MySQL конкатенация не поддерживается при помощи оператора + или ||. Там используется функция CONCAT (), в которой указывается список элементов, по отношению к которым выполняется конкатенация. Пример имел бы следующий вид.
SELECT CONCAT(vend_name, ‘(‘, vend_country, ‘)’ формирование столбца
FROM Vendors из таблицы
ORDER BY vend_name; упорядочить по столбцу
Для того, чтобы убрать ненужные пробелы необходимо использовать функцию RTRIM(). Эта функция отбрасывает все пробелы справа от указанного значения.
SELECT RTRIM(vend_name) + ‘(‘ + RTRIM(vend_country) + ‘)’
FROM Vendors
ORDER BY vend_name;
--------------------------
Expr1000
Bear Emporium (USA)
Beras R Us(USA)
Doll House Inc.(USA)
Fun and Games(England)
Furball Inc.(USA)
Jouets et ours(France)
В большинстве СУБД поддерживается как функция RTRIM(), которая обрезает правую часть строки, так и LTRIM(), которая удаляет левую часть строки, а также TRIM(), которая обрезает строку слева и справа.
7.2. Использование псевдонимов
Псевдоним – это альтернативное имя для поля или значения. Псевдоним применяется для именования вычисляемых полей. Псевдонимы присваиваются при помощи ключевого слова AS.
SELECT RTRIM(vend_name) + ‘(‘ + RTRIM(vend_country) + ‘)’ AS vend_title
FROM Vendors
ORDER BY vend_name;
--------------------------
vend_title
Bear Emporium (USA)
Beras R Us(USA)
Doll House Inc.(USA)
Fun and Games(England)
Furball Inc.(USA)
Jouets et ours(France)
К созданному имени столбца vend_title может обращаться любое клиентское приложение.
Примечание. 1) Псевдонимы можно использовать также для переименования столбца, если в реальном названии присутствуют недопустимые символы, например, пробелы или если название сложное и трудночитаемое.
2) Псевдонимом может служить как одно слово, так и целая строка. Если используется строка, она должна быть заключена в кавычки. Таким образом, псевдонимы используются для переименования многословных названий столбцов в однословные.
7.3. Выполнение математических вычислений
Другой способ использования вычисляемых полей – это выполнение математических операций над выбранными данными.
В таблице Orders хранятся все полученные заказы, а в таблице OrderItems содержатся наименования продуктов для каждого заказа. Требуется осуществить выборку всех продуктов в заказе номер 20008.
SELECT prod_id, quantity, item_price выбор столбцов
FROM OrderItems из таблицы
WHERE order_num = 20008; условие выборки
--------------------------
prod_id quantity item_price
RGAN01 5 4,99р.
BR03 5 11,99р.
BNBG01 10 3,49р.
BNBG02 10 3,49р.
BNBG03 10 3,49р.
В столбце item_price содержится цена на продукт для каждой записи, имеющейся в заказе. Необходимо узнать полную цену (цена за один продукт, умноженная на количество продуктов в заказе).
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
--------------------------
prod_id quantity item_price expanded_price
RGAN01 5 4,99р. 24.95
BR03 5 11,99р. 59.95
BNBG01 10 3,49р. 34.90
BNBG02 10 3,49р. 34.90
Рекомендуем посмотреть лекцию "13 Коммуникационная сеть".
BNBG03 10 3,49р. 34.90
Столбец expanded_price является вычисляемым полем. В ЫЙД поддерживаются основные математические операции:
+ сложение
- вычитание
* умножение
/ деление
