MySQL學習足跡記錄08--創建計算字段--Concat(),AS

MySQL學習足跡記錄08--創建計算字段--Concat(),AS       本文所用到的表格數據

 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.計算字段並不實際存在於數據庫表中，計算字段是在運行時在SELECT語句內創建的。     2.拼接字段Concat()   *拼接：將值連結到一起構成單個值    拼接兩個列

  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.刪除右側多余的空格RTrim()   刪除左側多余的空格LTrim()   刪除兩側多余的空格Trim()   以RTrim()為例：

  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.使用別名（alias),關鍵字AS

  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.執行算術計算   先檢索訂單號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)

 

   再匯總物品的價格（單價*數量）

  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.MySQL支持的基本算術操作符+,-,*,/