вычисляемое поле в таблице mysql
Базовый курс SQL. Вычисляемые поля
Primary tabs
Forums:
Иногда нам нужно извлечь данные не в том формате, в котором они хранятся в таблицах. Например:
Следует знать, что в таком случае SQL предоставляет возможность произвести некоторые преобразования с данными прямо в процессе запроса. Отформатировать полученные «сырые» данные могла бы и клиентская сторона, но как правило, на сервере базы данных это происходит гораздо быстрее.
Конкатенация полей
Допустим, мы создаём список студентов участвующих в творческом конкурсе, и нам требуется указать их возраст в виде Фамилия (возраст). В PostgreSQL соединить значения двух столбцов и добавить скобки мы можем с помощью оператора ||:
Как вы можете увидеть, в результате мы имеем все 4 части, склеенные в одну строку. Но мешают пробелы, которыми было заполнено поле. Чтобы их убрать, воспользуемся функцией RTRIM(), которая удаляет все пробелы, справа от значения. Также, в случае необходимости, можете использовать LTRIM() и TRIM(), удаляющие соответственно пробелы слева от строки или пробелы и слева, и справа.
В некоторых других СУБД для конкатенации вместо «||» используется «+».
В MySQL конкатенацию можно осуществить с помощью функции CONCAT().
Псевдонимы вычисляемых полей
Наверное вы заметили, что новый столбец, который мы получили «на лету», не имеет имени. В таком случае мы не сможем обратиться к нему на стороне клиентского приложения. Чтобы решить эту проблему, дадим столбцу псевдоним. Для этого используется ключевое слово AS:
Теперь мы сможем обращаться к результату данного запроса по имени, так, как если бы это был реальный столбец.
Псевдонимы могут быть использованы и для переименования существующих столбцов таблицы. Обычно это делают для сокращения длинных неудобочитаемых заголовков, но причина может быть и любая другая. Важно помнить, что если вы хотите дать столбцу сложный псевдоним из нескольких слов, его надо будет заключить в кавычки.
Математические операции
Теперь нам нужно определить победителей конкурса. Для этого сложим результаты двух туров и отсортируем список по убыванию:
Получим практически готовую турнирную таблицу:
В данном случае столбец final_points является вычисляемым полем. В SQL на ряду со сложением (+) могут быть использованы вычитание (-), умножение (*) и деление (/). Для управления порядком вычислений используйте скобки.
Есть и другие способы расчёта суммы значений в SQL, например, с помощью функции SUM().
Подробнее функции мы рассмотрим в следующем разделе.
Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик «Дом печати» (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:
1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком «Дом печати» (id=2):
2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:
3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:
5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик «Дом печати». Синтаксис для использования функции SUM() следущий:
Синтаксис создания представления следующий:
Вычисляемые поля (столбцы)
Представления
Синтаксис создания представлений мы уже рассматривали. После создания представлений, их можно использовать так же, как таблицы. То есть выполнять запросы к ним, фильтровать и сортировать данные, объединять одни представления с другими. С одной стороны это очень удобный способ хранения частоприменяемых сложных запросов (как в нашем примере).
Видеоуроки php + mysql
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Учебник 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 была выполнена конкатенация следующих элементов:
Как видно из приведенного выше результата, выражение SELECT возвращает один столбец (вычисляемое поле), содержащий все четыре элемента как одно целое.
Конкатенация в MySQL
В MySQL не поддерживается конкатенация при помощи оператора + или ||. Здесь необходимо использовать функцию CONCAT(), в которой указывается список элементов, по отношению к которым необходимо выполнить конкатенацию. При использовании функции CONCAT() первая строка примера выглядела бы так:
В MySQL поддерживается использование оператора ||, но не для конкатенации. В MySQL || является эквивалентом логического оператора OR, a && — эквивалентом логического оператора AND.
Взгляните еще раз на результат, полученный после применения оператора SELECT. Два столбца, объединенные в вычисляемое поле, заполнены пробелами. Во многих базах данных (но не во всех) сохраненный текст дополняется пробелами до ширины столбца. Чтобы выбрать правильно отформатированные данные, необходимо убрать добавленные пробелы. Это можно сделать при помощи SQL-функции RTRIM() следующим образом:
Ниже приведено это же выражение, но с использованием оператора ||:
Функция RTRIM() отбрасывает все пробелы справа от указанного значения. При использовании функции RTRIM() каждый отдельный столбец обрабатывается корректно. Город, штат указываются через запятую и пробел, а штат и ZIP-код – через пробел.
Функции TRIM
В большинстве СУБД поддерживаются как функция RTRIM () (которая, как мы увидели, «обрезает» правую часть строки), так и LTRIM() (которая удаляет левую часть строки), а также TRIM() (которая «обрезает» строку слева и справа).
Использование псевдонимов
Оператор SELECT, который использовался для конкатенации полей имени и адреса, как видите, справился со своей задачей. Но как же называется новый вычисляемый столбец? По правде говоря – никак, это просто значение. Этого может быть достаточно, если вы просматриваете результаты в программе тестирования SQL-запросов, однако столбец без названия нельзя использовать в клиентском приложении, так как клиент не сможет к нему обратиться.
Для решения этой проблемы в SQL была включена поддержка псевдонимов. Псевдоним – это альтернативное имя для поля или значения. Псевдонимы присваиваются при помощи ключевого слова AS. Взгляните на следующий оператор SELECT:
Ниже приведена эта же инструкция, но с использованием оператора ||:
Сам по себе этот оператор SELECT ничем не отличается от предыдущего, за исключением того, что вычисляемое поле указывается после текста AS vend_title. Таким образом, SQL создает вычисляемое поле, содержащее результат вычислений, под названием vend_title. Как видите, результат остается тем же, но столбец теперь носит имя vend_title и любое клиентское приложение может обращаться к нему по имени, как если бы это был реальный столбец таблицы.
Другое использование псевдонимов
Псевдонимы можно использовать и по-другому. Часто псевдонимы используются для переименования столбца, если в реальном названии присутствуют недопустимые символы (например, пробелы) или если название сложное и трудночитаемое.
Имена псевдонимов
Псевдонимом может служить как одно слово, так и целая строка. Если используется строка, она должна быть заключена в кавычки. В принципе, так делать можно, хотя и не рекомендуется. Многословные имена, несомненно, удобнее читать, но они создают множество проблем для многих клиентских приложений. Таким образом, наиболее часто псевдонимы используются для переименования многословных названий столбцов в однословные.
Производные столбцы
Псевдонимы иногда называют «производные столбцы», но, независимо оттого, какой термин вы будете использовать, означают они одно и то же.
Выполнение математических вычислений
Еще одним способом использования вычисляемых полей является выполнение математических операций над выбранными данными. Рассмотрим пример. В таблице Orders хранятся все полученные заказы, а в таблице Order Items содержатся наименования продуктов для каждого заказа. Следующий SQL-оператор осуществляет выборку всех продуктов в заказе номер 20008:
В столбце item_price содержится цена на продукт для каждой записи, имеющейся в заказе. Чтобы узнать полную цену (цена за один продукт, умноженная на количество продуктов в заказе), необходимо сделать следующее:
Столбец expanded_price, показанный в предыдущем результате, является вычисляемым полем; вычисление было простым: quantity*item_price. Теперь клиентское приложение может использовать этот новый вычисляемый столбец, как и любой другой в таблице.
В SQL поддерживаются основные математические операции, перечисленные в табл. 7.1. Кроме того, для управления порядком обработки можно использовать круглые скобки. В уроке 5, «Расширенная фильтрация данных», рассказывается о порядке обработки.
Таблица 7.1. Математические операции в SQL
| Операция | Описание |
|---|---|
| + | Сложение |
| – | Вычитание |
| * | Умножение |
| / | Деление |
Резюме
В этом уроке вы узнали, что такое вычисляемые поля и как их можно создавать. Были рассмотрены примеры использования вычисляемых полей для конкатенации строк и выполнения математических операций. Кроме того, вы узнали, как следует создавать и использовать псевдонимы так, чтобы ваше приложение могло обращаться к вычисляемым полям.
SQL-Урок 6. Вычисляемые поля
1. Выполнение математических операций
Одним из способов использования расчетных полей является выполнение математических операций над выбранными данными. Давайте на примере рассмотрим как это происходит, использовав снова нашу таблицу Sumproduct. Предположим, нам нужно вычислить среднюю цену приобретения каждого товара. Для этого нужно переделить колонку Amount (сумма) на Quantity (количество):
SELECT DISTINCT Product, Amount/Quantity FROM Sumproduct
Как видим, СУБД отобрала все наименования товаров и отобразила их среднюю стоимость в отдельном столбце, который был создан во время выполнения запроса. Также можно заметить, что мы использовали дополнительный оператор DISTINCT, который нам нужен для отображения уникальных названий товаров (без него мы бы получили дублирование записей).
2. Использование псевдонимов
В предыдущем примере мы рассчитывали среднюю стоимость покупки каждого товара и отобразили значение в расчетном столбце. Однако в дальнейшем, нам неудобно обращаться к этому полю, так как его название является неинформативным для нас (СУБД дала название полю — Expr1001 ). Однако мы можем назвать поле самостоятельно, заранее указав его название в запросе, то есть дать псевдоним. Давайте перепишем предыдущий пример и укажем псевдонима для расчетного поля:
SELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct
Видим, наше расчетное поле получило собственное название AvgPrice. Для этого мы использовали оператор AS, после которого указали необходимое нам название. Стоит отметить, что в SQL поддерживаются только основные математические операции: сложение (+), вычитание (-), умножение (*), деление (/). Также для изменения очередности выполнения операции можно использовать круглые скобки.
Часто псевдонимы используют не только чтобы называть расчетные поля, но и для переименования действующих. Это может быть необходимым, если действующее поле имеет длинное название или название не достаточно информативным.
3. Соединение полей (конкатенация)
Кроме математических операций мы можем объединять текст и выводить его в отдельном поле. Давайте рассмотрим, каким образом можно осуществить склеивание (конкатенацию) текста. Имеем такой пример:
SELECT Month + ‘ ‘ + Product AS NewField, Quantity FROM Sumproduct
В этом примере мы соединили значение в двух столбцах и вывели результат в новое поле NewField.
SQL-Урок 6. Вычисляемые поля
1. Выполнение математических операций
Одним из способов использования расчетных полей является выполнение математических операций над выбранными данными. Давайте на примере рассмотрим как это происходит, использовав снова нашу таблицу Sumproduct. Предположим, нам нужно вычислить среднюю цену приобретения каждого товара. Для этого нужно переделить колонку Amount (сумма) на Quantity (количество):
SELECT DISTINCT Product, Amount/Quantity FROM Sumproduct
Как видим, СУБД отобрала все наименования товаров и отобразила их среднюю стоимость в отдельном столбце, который был создан во время выполнения запроса. Также можно заметить, что мы использовали дополнительный оператор DISTINCT, который нам нужен для отображения уникальных названий товаров (без него мы бы получили дублирование записей).
2. Использование псевдонимов
SELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct
Видим, наше расчетное поле получило собственное название AvgPrice. Для этого мы использовали оператор AS, после которого указали необходимое нам название. Стоит отметить, что в SQL поддерживаются только основные математические операции: сложение (+), вычитание (-), умножение (*), деление (/). Также для изменения очередности выполнения операции можно использовать круглые скобки.
Часто псевдонимы используют не только чтобы называть расчетные поля, но и для переименования действующих. Это может быть необходимым, если действующее поле имеет длинное название или название не достаточно информативным.
3. Соединение полей (конкатенация)
Кроме математических операций мы можем объединять текст и выводить его в отдельном поле. Давайте рассмотрим, каким образом можно осуществить склеивание (конкатенацию) текста. Имеем такой пример:
SELECT Month + ‘ ‘ + Product AS NewField, Quantity FROM Sumproduct
В этом примере мы соединили значение в двух столбцах и вывели результат в новое поле NewField.