Database SQL Statements Learning notes (5)-Creating calculated fields

1. The data stored in the table is not always required by the application, for example, we have the unit price of the goods, there are two columns of the number of items sold, but we need the total price information, that is, the unit price * product for printing the invoice. Therefore, we need to retrieve the converted, computed or formatted data directly from the database, rather than retrieving the data. At this point the unit price and quantity are normal table columns, and the total price is the calculated field.

• Field: Basically the same as the column, often used with each other, but database columns are generally called columns.
• Only the database knows which columns are columns and which columns are fields. The application is not recognized.

 Use sells; SELECT *  from Vendors;

2. Stitching (CONCATENATE) field: Joins values together (attaches one value to another value) to form a single value.

 Use sells; SELECT concat (vend_name,' (', Vend_country,')'    from vendors;

Sometimes in order to correctly return formatted data, some spaces must be removed, which can be done with the RTrim () function.

LTRIM (remove all the blanks on the left), TRIM () strips the left and right sides of the string.

3. When stitching is complete, a new column is created, but there is no name so that it cannot be referenced when it is used. Thus, we need to give it an alias, given by using the AS keyword.

SELECT concat (Trim (vend_name),'  (', Trim (vend_country),'  )' ) as from vendors;  

Results:

4. Perform arithmetic operations

 SELECT from WHERE order_num = 20008;

Now calculate the total price and alias Overall_price

SELECT  prod_id,quantity,item_price,quantity  * as from WHERE order_num=20008;

Overall_price is the alias of the new field, also known as the Export column.

SQL arithmetic operator: +,-,*,/

SELECT 2 * 3;

The result above is 6, which means that select can be used not only as a keyword to retrieve data, but also as a simple way to access and manipulate an expression

SELECT now ();

Show Current Time 2016-06-06 20:22:46

Database SQL Statements Learning notes (5)-Creating calculated fields