拼接字段

MySQL 的不同之處：多數(shù) DBMS 使用+或||來(lái)實(shí)現(xiàn)拼接蚕脏，MySQL 使用 Concat()函數(shù)來(lái)實(shí)現(xiàn)拉背。

拼接兩個(gè)列

將兩列拼接成一個(gè)展示形式：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

簽名拼接兩列的結(jié)果可以看到，拼接后的列名是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)       |
+-------------------------+

執(zhí)行算術(shù)計(jì)算

執(zhí)行算術(shù)運(yùn)算并擴(kuò)充字段顯示：

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 支持的加減乘除（+ - * 值戳、）算術(shù)運(yùn)算符议谷，并用括號(hào)區(qū)分優(yōu)先級(jí)

now()函數(shù)返回當(dāng)前日期和時(shí)間：

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

數(shù)據(jù)處理函數(shù)

函數(shù)我們前面已經(jīng)接觸了，比如 trim()堕虹，rtrim()卧晓，ltrim()。

文本處理函數(shù)

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

常用的文本處理函數(shù)：

日期和時(shí)間處理函數(shù)

基本日期比較：

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

上面的匹配顯然不合理赦政，如果訂單日期精確到時(shí)間的話(huà)胜宇，就無(wú)法匹配上了。

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

匹配一個(gè)時(shí)間段內(nèi)的數(shù)據(jù)：

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

數(shù)值處理函數(shù)