计算动物年龄:
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;