MySQL学习足迹记录08--创建计算字段--Concat(),AS_MySQL

bitsCN.com

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

bitsCN.com