MySQL学习足迹记录09--常用文本，日期，数值处理函数(二)
即代表了它们的功能，无需刻意记忆.
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(): 返回一个数的指数值