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)