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

页面导航:首页 > 数据库 > Mysql > MySQL学习足迹记录08--创建计算字段--Concat(),AS

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

来源: 作者: 时间:2016-02-18 10:28 【

MySQL学习足迹记录08--创建计算字段--Concat(),AS 本文所用到的表格数据 mysql SELECT * FROM vendors;+---------+----------------+-----------------+-------------+------------+----------+-------...
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: 
  > 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支持的基本算术操作符+,-,*,/
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<