拼接字段

MySQL 的不同之处：多数 DBMS 使用+或||来实现拼接，MySQL 使用 Concat()函数来实现。

拼接两个列

将两列拼接成一个展示形式：vend_name (vend_country)

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)                          |
+--------------------------------------------+

删除多余的空格 RTRIM

SELECT CONCAT(RTRIM(vend_name), ' (', vend_country, ')') FROM vendors ORDER BY vend_name;

显然 MySQL 还支持LTRIM和TRIM分别删除左边的空格和两边的空格。

使用别名AS

签名拼接两列的结果可以看到，拼接后的列名是CONCAT(vend_name, ' (', vend_country, ')')，这显然很不友好，所以我们可以使用别名。

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

执行算术计算

执行算术运算并扩充字段显示：

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 |
+---------+----------+------------+----------------+

MySQL 支持的加减乘除（+ - * 、）算术运算符，并用括号区分优先级

now()函数返回当前日期和时间：

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-06-30 16:33:47 |
+---------------------+
1 row in set (0.00 sec)

数据处理函数

函数我们前面已经接触了，比如 trim()，rtrim()，ltrim()。

文本处理函数

upper()：

mysql> SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+

常用的文本处理函数：

日期和时间处理函数

基本日期比较：

SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01';

上面的匹配显然不合理，如果订单日期精确到时间的话，就无法匹配上了。

SELECT cust_id, order_num FROM orders WHERE DATE(order_date) = '2005-09-01';

匹配一个时间段内的数据：

SELECT cust_id, order_num FROM orders WHERE DATE(order_date) BETWEEN '2005-09-01' and '2005-09-30';
优化后
SELECT cust_id, order_num FROM orders WHERE YEAR(order_date) = 2005 and MONTH(order_date) = 9;

数值处理函数