MySQL學習足跡記錄09--常用文本，日期，數值處理函數 1. 文本處理函數 這裡只介紹Soundex(str)函數，其它的函數無需記憶，只需大概記得函數名就OK了， 要用時再用HELP命令查看一下用法。 eg:HELP Upper; *Soundex(str)：對字符串進行發音比較而不是字母比較 先列出所以cust_contact的數據
mysql> SELECT cust_contact FROM customers;
+--------------+
| cust_contact |
+--------------+
| Y Lee |
| Jerry Mouse |
| Jim Jones |
| Y Sam |
| E Fudd |
+--------------+
5 rows in set (0.00 sec)
假設你只記得顧客的實際名是Y. Lie，現在要找 Y Lee的數據
mysql> SELECT cust_name,cust_contact FROM customers
-> WHERE cust_contact = 'Y. Lie'; #查找失敗
Empty set (0.00 sec)
現在用Soundex()對字符串進行發音比較
mysql> SELECT cust_contact FROM customers
-> WHERE Soundex(cust_contact) = Soundex('Y. Lie');
+--------------+
| cust_contact | #ok,查找成功
+--------------+
| Y Lee |
+--------------+
1 row in set (0.00 sec)
常用函數參考（可跳過）：
*Upper():將文本轉換為大寫
eg:
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 |
+----------------+------------------+
6 rows in set (0.00 sec)
*LEFT(str,len):
Returns the leftmost len characters from the string str,
or NULL if any argument is NULL.
Examples:
mysql> SELECT LEFT('Hello',3);
+-----------------+
| LEFT('Hello',3) |
+-----------------+
| Hel |
+-----------------+
1 row in set (0.00 sec)
*LENGTH(str):
Returns the length of the string str, measured in bytes.
Examples:
mysql> SELECT LENGTH('Hello');
+-----------------+
| LENGTH('Hello') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
*LOCATE(substr,str), LOCATE(substr,str,pos):
The first syntax returns the position of the first occurrence of
substring substr in string str. The second syntax returns the position
of the first occurrence of substring substr in string str, starting at
position pos. Returns 0 if substr is not in str.
Examples:
mysql> SELECT LOCATE('es','chinese');
+------------------------+
| LOCATE('es','chinese') |
+------------------------+
| 5 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT LOCATE('ue','queue',3);
+------------------------+
| LOCATE('ue','queue',3) |
+------------------------+
| 4 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT LOCATE('al','hello');
+----------------------+
| LOCATE('al','hello') |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)
*LOWER(str):
Returns the string str with all characters changed to lowercase
Examples:
mysql> SELECT LOWER('HELLO');
+----------------+
| LOWER('HELLO') |
+----------------+
| hello |
+----------------+
1 row in set (0.00 sec)
*RIGHT(str,len)
Returns the rightmost len characters from the string str,
or NULL if any argument is NULL.
Examples:
mysql> SELECT RIGHT('queue',3);
+------------------+
| RIGHT('queue',3) |
+------------------+
| eue |
+------------------+
1 row in set (0.00 sec)
2.常用日期和時間處理函數
大部分都比較簡單，函數名即代表了它們的功能，無需刻意記憶.
AddDate(),AddTime(),CurDate,CurTime(),Date().
DateDiff():計算兩個日期之差
Date_Add(),Date_Format(),Day(),DayOfWeek(),Hour(),Month(),Now(),Second(),Time(),Year()
*MySQL使用的日期格式yyyy-mm-dd
Examples:
先列出orders所有的日期數據
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 |
+---------------------+
5 rows in set (0.00 sec)
mysql> SELECT cust_id,order_num
-> FROM orders
-> WHERE order_date = '2005-09-01'; # WHERE order_date = '2005-09-01'並不可靠
+---------+-----------+ #假如order_date的值為‘2005-09-01 11：30：05’則檢索失敗
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
+---------+-----------+
1 row in set (0.00 sec)
解決辦法，用Date（）函數
mysql> SELECT cust_id,order_num
-> FROM orders
-> WHERE Date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
+---------+-----------+
1 row in set (0.00 sec)
練習：檢索2005年9月的所有訂單 法一：
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 |
+---------+-----------+
3 rows in set (0.00 sec)
法二：(無需記住每月有多少天，而且不需要操心閏年2月）
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 |
+---------+-----------+
3 rows in set (0.00 sec)
3.數值處理函數 Abs(),Cos(),Sin(),Sqrt(),Tan(),Pi() Mod():返回除操作的余數 Exp(): 返回一個數的指數值
