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(): 返回一个数的指数值