MySQL learning footprint record 08 -- create a calculated field -- Concat (),

MySQL learning footprint record 08 -- create a calculated field -- Concat (), AS the table data used in this article

 mysql> SELECT * FROM vendors;+---------+----------------+-----------------+-------------+------------+----------+--------------------------------------------+| vend_id | vend_name      | vend_address    | vend_city   | vend_state | vend_zip | vend_country |+---------+----------------+-----------------+-------------+------------+----------+---------------------------------------------+|    1001 | Anvils R Us    | 123 Main Street | Southfield  | MI         | 48075    | USA          ||    1002 | LT Supplies    | 500 Park Street | Anytown     | OH         | 44333    | USA          ||    1003 | ACME           | 555 High Street | Los Angeles | CA         | 90046    | USA          ||    1004 | Furball Inc.   | 1000 5th Avenue | New York    | NY         | 11111    | USA          ||    1005 | Jet Set        | 42 Galaxy Road  | London      | NULL       | N16 6PS  | England      ||    1006 | Jouets Et Ours | 1 Rue Amusement | Paris       | NULL       | 45678    | France       |+---------+----------------+-----------------+-------------+------------+----------+-----------------------------------------------+6 rows in set (0.00 sec)

 

1. The calculated field does not actually exist in the database table. The calculated field is created in the SELECT statement at run time. 2. Join field Concat () * join: concatenate values to form a single value and splice two columns

  eg:   mysql> SELECT Concat(vend_name,'(',vend_country,')') FROM vendors            -> ORDER BY vend_name;+-------------------------------------------------------+| Concat(vend_name,'(',vend_country,')') |+--------------------------------------------------------+| ACME(USA)                                                || Anvils R Us(USA)                                       || Furball Inc.(USA)                                        || Jet Set(England)                                         || Jouets Et Ours(France)                              || LT Supplies(USA)                                       |+---------------------------------------------------------+6 rows in set (0.00 sec)

 

3. Remove unnecessary spaces on the right. RTrim (): delete unnecessary spaces on the left. LTrim (): delete unnecessary spaces on both sides. Trim (). Take RTrim () as an example:

  eg:    mysql> SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')             -> FROM vendors             -> ORDER BY vend_name;+------------------------------------------------------+| Concat(RTrim(vend_name),'(',RTrim(vend_country),')') |+------------------------------------------------------+| ACME(USA)                                            || Anvils R Us(USA)                                     || Furball Inc.(USA)                                    || Jet Set(England)                                     || Jouets Et Ours(France)                               || LT Supplies(USA)                                     |+------------------------------------------------------+6 rows in set (0.00 sec)

 

4. Use the alias (alias), keyword

  eg:  mysql> SELECT Concat(Trim(vend_name),'(',Trim(vend_country),')')           -> AS vend_title           -> FROM vendors           -> ORDER BY vend_name;+------------------------+| vend_title             |+------------------------+| ACME(USA)              || Anvils R Us(USA)       || Furball Inc.(USA)      || Jet Set(England)       || Jouets Et Ours(France) || LT Supplies(USA)       |+------------------------+6 rows in set (0.00 sec)

 

5. perform arithmetic calculations to retrieve all items in order number 20005 first

   eg:  mysql> SELECT prod_id,quantity,item_price           -> FROM orderitems           -> WHERE order_num = 20005;+---------+----------+------------+| prod_id | quantity | item_price |+---------+----------+------------+| ANV01   |       10 |       5.99 || ANV02   |        3 |       9.99 || TNT2    |        5 |      10.00 || FB      |        1 |      10.00 |+---------+----------+------------+4 rows in set (0.00 sec)

 

Then summarize the price of the item (unit price * quantity)

  eg: mysql> SELECT prod_id,quantity,item_price,          -> quantity*item_price AS expanded_price          -> FROM orderitems          -> WHERE order_num = 20005;+---------+----------+------------+----------------+| prod_id | quantity | item_price | expanded_price |+---------+----------+------------+----------------+| ANV01   |       10 |       5.99 |          59.90 || ANV02   |        3 |       9.99 |          29.97 || TNT2    |        5 |      10.00 |          50.00 || FB      |        1 |      10.00 |          10.00 |+---------+----------+------------+----------------+4 rows in set (0.00 sec)

 

6. Basic arithmetic operators supported by MySQL + ,-,*,/