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