Create Calculated Field

The data stored in the database table is not in the format required by general applications. At this time, we need to perform calculations to obtain the desired data format.

Example:

 If you want to display both the company name and the company address in one field, but the two information are contained in separate table columns.

1. In the above example, the data stored in the table is not required by the application, we need to retrieve the transformed, calculated or formatted data directly from the database, rather than retrieving the data, and then inReformatting in the client application or reporter.

2. This is where calculated fields come into play.Calculated fields do not exist directly in the database table.Calculated fields are created within a SELECT statement at runtime.

Term Explanation (Field)

A field basically means the same thing as a column and is often used interchangeably, although database columns are generally referred to as columns, and the term field is often used in conjunction with computed fields.

Note:

Only the database knows which columns in the SELECT statement are actual table columns and which columns are computed fields.From the perspective of a client (such as an application), the data for a calculated field is returned in the same way as data for other columns.

First, splicing fields

The so-called concatenation is the connection, which connects two or more fields to form a single value.MySQL uses the concat() function to implement, and most other DBMSs use + or || to implement.The connection fields in Oracle can be implemented using concat() or ||.

select concat(company_name,',',company_address) from company_info

Analysis:

What this statement needs to query is the connection field in the company information table. The connection field is: company name + comma + company address.

Note:

1. If you want to remove the spaces on both sides of the table column and connect, you can use the trim() function.If you only want to remove the spaces on the right, use the function Rtrim(), and if you only want to remove the spaces on the left, use the function Ltrim().

2. What is the name of the concatenated field?In fact, he does not have a name. At this time, if we want to obtain it in the application, it will be time-consuming. At this time, we can assign an alias to him.

   For example, assign an alias to the concatenation fields of the company name and company address above: companyInfo

   select concat(company_name,',',company_address) as companyInfo from company_infoselect concat(company_name,',',company_address) companyInfo from company_info

   The above two SQL statements achieve the same effect, except that the second statement omits the keyword as.This way, any client application can use companyInfo to refer to this column, just like an actual table column.

Second, arithmetic calculation

Another common use of calculation fields is to perform arithmetic calculations on retrieved data, that is, addition, subtraction, multiplication and division.

Example:

​ For example, a product shopping record table, the columns of the table contain the order number, product name, product unit price, and product quantity. Now you want to query the product purchased with the order number (201542) and the amount spent on the product.

select item_name,item_number*item_price as totalPricefrom order_infowhere order_number= '201542'

MySQL supports basic arithmetic operators, namely addition, subtraction, multiplication and division (±*/). In addition, parentheses can be used to distinguish the order of precedence.Generally in application development, if the priority of basic operations is involved, it is recommended to use parentheses for programming, so that it is clear and the error rate is small.