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

ゝ一纸荒年。 2022-01-11 00:15 223阅读 0赞

***本文所用到的表格数据  
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支持的基本算术操作符+,-,\*,/**