mysql杂记二~~日期计算

计算动物年龄：
  select name,birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age from pet;

+----------+------------+------------+------+
| name     | birth      | curdate()  | age  |
+----------+------------+------------+------+
| name     | 1992-08-13 | 2012-08-28 |   20 |
| Fluffy   | 1997-12-09 | 2012-08-28 |   14 |
| Fluffy   | 1997-12-09 | 2012-08-28 |   14 |
| Claws    | 1994-03-17 | 2012-08-28 |   18 |
| Buffy    | 1989-05-13 | 2012-08-28 |   23 |
| Fang     | 1979-08-31 | 2012-08-28 |   32 |
| Bowser   | 1979-11-14 | 2012-08-28 |   32 |
| puffball | 1999-03-30 | 2012-08-28 |   13 |
+----------+------------+------------+------+
 year()提取日期的年部分，right（）提取日期的MM-DD（日历年）部分的最右面5个字符。比较MM-DD值表达式部分的值一般为1或0，如果curdate（）的年比birth的年早，则年份应减去1.

  select name,birth,curdate(),(year(death)-year(birth))-(right(death,5)<right(birth,5))
 as age from pet where death is not NULL order by age;死亡值不为Null的。

MySQL提供几个日期部分的提取函数，例如YEAR( )、MONTH( )和DAYOFMONTH( )。在这里MONTH()是适合的函数。为了看它怎样工作，运行一个简单的查询，显示birth和MONTH(birth)的值：
  SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| name     | 1992-08-13 |            8 |
| Fluffy   | 1997-12-09 |           12 |
| Fluffy   | 1997-12-09 |           12 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1979-08-31 |            8 |
| Bowser   | 1979-11-14 |           11 |
| puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

找出5月份生日的动物：
   select name,birth from pet where MONTH(birth)=5;