MySQL學習足跡記錄09--常用文本，日期，數值處理函數_MySQL

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)

 大部分都比較簡單，函數名即代表了它們的功能，無需刻意記憶. 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)

 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)

 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)

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)