Учебник SQLУрок 7. Создание вычисляемых полейВ этом уроке вы узнаете, что такое вычисляемые поля, как их создавать и как использовать псевдонимы для ссылки на такие поля из вашего приложения. Что такое вычисляемые поляДанные, хранимые в таблицах базы данных, обычно бывают представлены не в таком виде, который необходим для ваших приложений. Вот несколько примеров.
В каждом из этих примеров данные хранятся не в том виде, в котором их необходимо предоставить приложению. Вместо того чтобы извлекать эти данные, а затем изменять их форму при помощи клиентского приложения или отчета, лучше извлекать уже преобразованные, подсчитанные или отформатированные данные прямо из базы данных. Именно здесь помогут вычисляемые поля. В отличие от всех выбранных нами ранее столбцов, вычисляемых полей на самом деле в таблице базы данных нет. Они создаются "на лету" SQL-оператором SELECT. ПолеИзначально термин поле означал то же самое, что и столбец, и в основном эти понятия взаимозаменяемы, хотя столбцы базы данных обычно называют столбцами, а термин поля обычно используется по отношению к вычисляемым полям. Важно отметить, что только база данных "знает", какие столбцы в операторе SELECT являются реальными столбцами таблицы, а какие – вычисляемыми полями. С точки зрения клиента (например, вашего приложения), данные вычисляемого поля возвращаются точно так же, как и данные из любого другого столбца. Клиентское или серверное форматирование?Многие преобразования и изменения форматов, которые могут быть выполнены посредством SQL-операторов, могут быть также выполнены и клиентским приложением. Однако, как правило, эти операции гораздо быстрее выполняются на сервере базы данных, чем у клиента, так как СУБД предназначена, кроме всего, для быстрого и эффективного выполнения операций такого типа. Конкатенация полейЧтобы продемонстрировать работу вычисляемых полей, рассмотрим простой пример – создание заголовка, состоящего из двух столбцов. В таблице Vendors содержится название поставщика и его адрес. Предположим, что вам необходимо создать отчет по поставщику и указать его адрес как часть его имени в виде имя (адрес). В отчете должно быть одно значение, а данные в таблице хранятся в двух столбцах: vend_name и vend_country. Кроме того, значение vend_country необходимо заключить в скобки, которых нет в таблице базы данных. Выражение SELECT, которое возвращает имена поставщиков и адреса, довольно простое, но как создать комбинированное значение? КонкатенацияКомбинирование значений (путем присоединения их друг к другу) для получения одного "длинного" значения. Для этого необходимо соединить два значения. В SQL-выражении SELECT можно выполнить конкатенацию двух столбцов при помощи специального оператора. В зависимости от СУБД это может быть знак "плюс" (+) или две вертикальные черточки (||). Оператор + или ||В СУБД Access, SQL Server и Sybase для конкатенации используется знак +. В СУБД DB2, Oracle, PostgreSQL и Sybase используется знак ||. Более подробную информацию ищите в документации по вашей СУБД. Вообще-то || – более предпочтительный оператор конкатенации, так что он поддерживается все большим и большим количеством СУБД. Ниже приведен пример использования знака "плюс" (применяется синтаксис, принятый в большинстве СУБД). SELECT vend_name + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name; Вывод: ------------------------- Bear Emporium (USA) Beras R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France) Ниже приведена та же инструкция, но с использованием оператора || SELECT vend_name || ' (' || vend_country || ')' FROM Vendors ORDER BY vend_name; Вывод: ------------------------- Bear Emporium (USA) Beras R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France) В предыдущих операторах SELECT была выполнена конкатенация следующих элементов:
Как видно из приведенного выше результата, выражение SELECT возвращает один столбец (вычисляемое поле), содержащий все четыре элемента как одно целое. Конкатенация в MySQL
В MySQL не поддерживается конкатенация при помощи оператора + или ||. Здесь необходимо использовать функцию CONCAT(), в которой указывается список элементов, по отношению к которым необходимо выполнить конкатенацию. При использовании функции CONCAT() первая строка примера выглядела бы так: Взгляните еще раз на результат, полученный после применения оператора SELECT. Два столбца, объединенные в вычисляемое поле, заполнены пробелами. Во многих базах данных (но не во всех) сохраненный текст дополняется пробелами до ширины столбца. Чтобы выбрать правильно отформатированные данные, необходимо убрать добавленные пробелы. Это можно сделать при помощи SQL-функции RTRIM() следующим образом: SELECT RTRIM( vend_name ) + ' (' + RTRIM( vend_country ) + ') ' FROM Vendors ORDER BY vend_name; Вывод: ----------------------- Bear Emporium (USA) Beras R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France) Ниже приведено это же выражение, но с использованием оператора ||: SELECT RTRIM( vend_name ) || ' (' || RTRIM( vend_country ) "" ') ' FROM Vendors ORDER BY vend_name; Вывод: ----------------------- Bear Emporium (USA) Beras R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France) Функция RTRIM() отбрасывает все пробелы справа от указанного значения. При использовании функции RTRIM() каждый отдельный столбец обрабатывается корректно. Город, штат указываются через запятую и пробел, а штат и ZIP-код – через пробел. Функции TRIMВ большинстве СУБД поддерживаются как функция RTRIM () (которая, как мы увидели, "обрезает" правую часть строки), так и LTRIM() (которая удаляет левую часть строки), а также TRIM() (которая "обрезает" строку слева и справа). Использование псевдонимовОператор SELECT, который использовался для конкатенации полей имени и адреса, как видите, справился со своей задачей. Но как же называется новый вычисляемый столбец? По правде говоря – никак, это просто значение. Этого может быть достаточно, если вы просматриваете результаты в программе тестирования SQL-запросов, однако столбец без названия нельзя использовать в клиентском приложении, так как клиент не сможет к нему обратиться. Для решения этой проблемы в SQL была включена поддержка псевдонимов. Псевдоним – это альтернативное имя для поля или значения. Псевдонимы присваиваются при помощи ключевого слова AS. Взгляните на следующий оператор SELECT: 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) Ниже приведена эта же инструкция, но с использованием оператора ||: 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) Сам по себе этот оператор SELECT ничем не отличается от предыдущего, за исключением того, что вычисляемое поле указывается после текста AS vend_title. Таким образом, SQL создает вычисляемое поле, содержащее результат вычислений, под названием vend_title. Как видите, результат остается тем же, но столбец теперь носит имя vend_title и любое клиентское приложение может обращаться к нему по имени, как если бы это был реальный столбец таблицы. Другое использование псевдонимовПсевдонимы можно использовать и по-другому. Часто псевдонимы используются для переименования столбца, если в реальном названии присутствуют недопустимые символы (например, пробелы) или если название сложное и трудночитаемое. Имена псевдонимовПсевдонимом может служить как одно слово, так и целая строка. Если используется строка, она должна быть заключена в кавычки. В принципе, так делать можно, хотя и не рекомендуется. Многословные имена, несомненно, удобнее читать, но они создают множество проблем для многих клиентских приложений. Таким образом, наиболее часто псевдонимы используются для переименования многословных названий столбцов в однословные. Производные столбцыПсевдонимы иногда называют "производные столбцы", но, независимо оттого, какой термин вы будете использовать, означают они одно и то же. Выполнение математических вычисленийЕще одним способом использования вычисляемых полей является выполнение математических операций над выбранными данными. Рассмотрим пример. В таблице Orders хранятся все полученные заказы, а в таблице Order Items содержатся наименования продуктов для каждого заказа. Следующий SQL-оператор осуществляет выборку всех продуктов в заказе номер 20008: SELECT prod_id, quantity, item_price FROM OrderItems WHERE order_nam = 20008; Вывод: prod_id quantity item_price ----------------------------- RGAN01 5 4.9900 BR03 5 11.9900 BNBG01 10 3.4900 BNBG02 10 3.4900 BNBG03 10 3.4900 В столбце item_price содержится цена на продукт для каждой записи, имеющейся в заказе. Чтобы узнать полную цену (цена за один продукт, умноженная на количество продуктов в заказе), необходимо сделать следующее: SELECT prod_id, quantity, item_price quantity*item_price AS expanded_price FROM OrderItems WHERE order_nam = 20008; Вывод: prod_id quantity item_price expanded_price --------------------------------------------- RGAN01 5 4.9900 24.9500 BR03 5 11.9900 59.9500 BNBG01 10 3.4900 34.9000 BNBG02 10 3.4900 34.9000 BNBG03 10 3.4900 34.9000 Столбец expanded_price, показанный в предыдущем результате, является вычисляемым полем; вычисление было простым: quantity*item_price. Теперь клиентское приложение может использовать этот новый вычисляемый столбец, как и любой другой в таблице. В SQL поддерживаются основные математические операции, перечисленные в табл. 7.1. Кроме того, для управления порядком обработки можно использовать круглые скобки. В уроке 5, "Расширенная фильтрация данных", рассказывается о порядке обработки. Таблица 7.1. Математические операции в SQL
РезюмеВ этом уроке вы узнали, что такое вычисляемые поля и как их можно создавать. Были рассмотрены примеры использования вычисляемых полей для конкатенации строк и выполнения математических операций. Кроме того, вы узнали, как следует создавать и использовать псевдонимы так, чтобы ваше приложение могло обращаться к вычисляемым полям. |
||||||||||||