MySQL - Create Calculated Field

Definition: Use permutations to combine existing fields into new fields as needed

---

splice field

For example, if school and location are two fields, but now need to be combined into the format of school (address), you can use the Concat() function to achieve:

 SELECT Concat(university_name, '(',university_address,')') FROM universities ORDER BY university_name;

In addition, in order to remove extra spaces in fields, MySQL supports:

• A function to remove spaces from the right of a string: RTrim()
• A function to remove spaces from the left of a string: LTrim()
• Remove the spaces on the left and right sides of the string function: Trim()

For example, to remove extra spaces on the right side of the data:

 SELECT Concat(RTrim(university_name), '(',RTrim(university_address),')') FROM universities ORDER BY university_name;

---

use an alias

After the splicing is completed, there is only one value, no name, and the client cannot refer to it. We can use the AS keyword to assign an alias:

 SELECT Concat(university_name, '(',university_address,')') AS university_fullname FROM universities ORDER BY university_name;

perform calculations

In addition to concatenating fields, another common use of calculated fields is to perform data calculations on retrieved data. For example, we have the quantity and unit price of commodities, and want to query the total price and related information of different commodities that meet the requirements:

 SELECT p_id, p_num,p_price, p_num * p_price AS total_price FROM customer_order WHERE order_num=1005

---

Reference: Forta B. MySQL crash course[M]. Pearson Education India, 2006.