MySQL學(xué)習(xí)足跡記錄09--常用文本，日期，數(shù)值處理函數(shù)_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) 假設(shè)你只記得顧客的實(shí)際名是Y. Lie，現(xiàn)在要找 Y Lee的數(shù)據(jù) mysql> SELECT cust_name,cust_contact FROM customers -> WHERE cust_contact = 'Y. Lie'; #查找失敗Empty set (0.00 sec) 現(xiàn)在用Soundex()對(duì)字符串進(jìn)行發(fā)音比較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():將文本轉(zhuǎn)換為大寫 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)

 大部分都比較簡(jiǎn)單，函數(shù)名即代表了它們的功能，無需刻意記憶. AddDate(),AddTime(),CurDate,CurTime(),Date(). DateDiff():計(jì)算兩個(gè)日期之差 Date_Add(),Date_Format(),Day(),DayOfWeek(),Hour(),Month(),Now(),Second(),Time(),Year() *MySQL使用的日期格式y(tǒng)yyy-mm-dd Examples: 先列出orders所有的日期數(shù)據(jù) 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)