MySQL基础——计算字段及数据处理函数

计算字段

字段（field） 基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。
拼接（concatenate） 将值联结到一起构成单个值。
可以用Concat()函数来实现两个列的拼接。（多数DBMS使用+或||来实现拼接，当把SQL语句转换成MySQL语句时一定要把这个区别铭记在心。）

# 生成一个供应商报表，格式：名字（地址）,SELECT语句返回包含4个列（计算字段）
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)                       |
+----------------------------------------+

Trim函数：支持RTrim()和LTrim()，分别是去掉数据右侧和左侧的空格，可以用来整理数据。
别名（alias） 一个字段或值得替换名。
可以看到，新计算列的名字没有意义，为了让客户机引用查询结果，SQL支持列别名。别名用AS关键字赋予。

mysql> SELECT Concat(vend_name,'(',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)       |
+------------------------+

算数计算 对检索出的数据进行算数计算

# 检索订单号20005中的所有物品并汇总价格
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 |
+---------+----------+------------+----------------+

此外，圆括号可用来区分优先顺序。

数据处理函数

函数没有SQL的可移植性强，如果决定使用函数，应该保证做好代码注释。大多数SQL实现支持以下类型的函数：

• 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算数操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
• 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

文本处理函数

其中SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。举例：顾客中有一个顾客Coyote Inc.，其联系名为Y.Lee。但如果这是记录错误，这个联系名实际应该是Y.Lie，怎么办？

mysql> SELECT cust_name, cust_contact
    -> FROM customers
    -> WHERE cust_contact = 'Y.Lie';
Empty set (0.00 sec)

mysql> SELECT cust_name, cust_contact
    -> FROM customers
    -> WHERE Soundex(cust_contact) = Soundex('Y.Lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

日期和时间处理函数

日期和时间采用响应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

MySQL使用的日期格式必须为yyyy-mm-dd，因此2020年3月30日应该为2020-03-30。

# 基本的日期比较很简单,检索一个2005年3月1日的订单记录。
mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE order_date = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+

但这样有时并不可靠，因为表中存储日期的列的数据类型为datetime，同时存储了日期和时间值，

mysql> SELECT order_date FROM orders;
+---------------------+
| order_date          |
+---------------------+
| 2005-09-01 00:00:00 |
| 2005-09-12 00:00:00 |
| 2005-09-30 00:00:00 |
| 2005-10-03 00:00:00 |
| 2005-10-08 00:00:00 |
+---------------------+

若时间不为0，则不会匹配，此时使用Date()函数更可靠：

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+

如果想检索2015年9月的所有订单的话，需要BETWEEN操作符，或者AND操作符：

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |

数值处理函数