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

MySQL learning footprint record 08 -- create a calculated field -- Concat (), AS bitsCN.com

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

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. splice the field Concat ()

* Splicing: concatenates values to form a single value.

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. delete unnecessary spaces RTrim () on the right ()

Delete extra spaces on the left LTrim ()

Delete unnecessary space Trim () on both sides ()

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. execute arithmetic computation

First retrieve all items in order 20005

   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 + ,-,*,/

BitsCN.com