MySQL学习足迹记录09--常用文本，日期，数值处理函数

刺骨的言语ヽ痛彻心扉 2022-01-11 01:11 288阅读 0赞

**1. 文本处理函数  
这里只介绍Soundex(str)函数，其它的函数无需记忆，只需大概记得函数名就OK了，**

**要用时再用HELP命令查看一下用法。**

**eg:HELP Upper;**

**\*Soundex(str)：对字符串进行发音比较而不是字母比较  
先列出所以cust\_contact的数据**

**mysql> SELECT cust\_contact FROM customers;**

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