Mysql学习笔记005

日期计算

MySQL提供了几个函数， 可以用来计算日期。巴拉拉拉。。。。

举个例子：我想要确定pet们有多大， 可以计算当前日期的年和出生日期之间的差。

操作如下：

SELECT name, birth, YEAR(CURRENT_DATE()) - YEAR(birth) AS age FROM pet;

mysql> SELECT name, birth, YEAR(CURRENT_DATE()) - YEAR(birth) AS age FROM pet;
+----------+------------+------+
| name | birth | age |
+----------+------------+------+
| Whistler | 1997-12-09 | 20 |
| Puffball | 1999-03-30 | 18 |
| Fluffy | 1993-02-04 | 24 |
| Claws | 1994-03-17 | 23 |
| Buffy | 1989-05-13 | 28 |
| Fang | 1990-08-27 | 27 |
| Bowser | 1989-08-31 | 28 |
| Chirpy | 1998-09-11 | 19 |
| Slim | 1996-04-29 | 21 |
+----------+------------+------+
9 rows in set (0.00 sec)

mysql>

YEAR() 取得是年份，没毛病。

插一个千年虫问题

温馨提示：这里很有可能遇到一个很经典的问题，叫千年虫问题。

SELECT YEAR('89-01-01');这个处理得到的是1989

mysql> select year('89-01-01');
+------------------+
| year('89-01-01') |
+------------------+
| 1989 |
+------------------+
1 row in set (0.00 sec)

mysql>

 SELECT YEAR('03-01-01');这个得到的是2003

mysql> SELECT YEAR('03-01-01');
+------------------+
| YEAR('03-01-01') |
+------------------+
| 2003 |
+------------------+
1 row in set (0.00 sec)

mysql>

 还有上古程序员跟我说个69 70 是个坎我试了一下

mysql> select year('69-01-01');
+------------------+
| year('69-01-01') |
+------------------+
| 2069 |
+------------------+
1 row in set (0.00 sec)

mysql> select year('70-01-01');
+------------------+
| year('70-01-01') |
+------------------+
| 1970 |
+------------------+
1 row in set (0.00 sec)

mysql>

 还真是。我的想法是计算机世界的第一天是：1970-01-01嘛。所以就有了这么一个坎。现在Java是可以到达1970-01-01之前的天数的，在这里不多说了。

 

 

好，书归正传。刚才我们给宠物们计算出了age ，然而一个懂Mysql的客户看到了很不满意。宠物的年龄应该按照过了生日计算（生下来算0岁）。

操作如下：

SELECT name, birth, CURDATE(), (YEAR(CURDATE()) - YEAR(birth)) - (RIGHT(CURDATE(), 5) < RIGHT(birth, 5)) AS age FROM pet;

mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE()) - YEAR(birth)) -
-> (RIGHT(CURDATE(), 5) < RIGHT(birth, 5)) AS age
-> FROM pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Whistler | 1997-12-09 | 2017-05-26 | 19 |
| Puffball | 1999-03-30 | 2017-05-26 | 18 |
| Fluffy | 1993-02-04 | 2017-05-26 | 24 |
| Claws | 1994-03-17 | 2017-05-26 | 23 |
| Buffy | 1989-05-13 | 2017-05-26 | 28 |
| Fang | 1990-08-27 | 2017-05-26 | 26 |
| Bowser | 1989-08-31 | 2017-05-26 | 27 |
| Chirpy | 1998-09-11 | 2017-05-26 | 18 |
| Slim | 1996-04-29 | 2017-05-26 | 21 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

mysql>

 RIGHT(birth, 5)就是取到了生日的日期。

我这里又有一个大胆的想法:Mysql是C/C++开发的嘛 我就猜Mysql的运算中bool类型也是可以和int运算的。

 

又是一个栗子：其实还有一个漏洞，你没有注意到。有一个pet已经死了。死了怎么可能还有age呢？我们先查一下死去的pet有哪些。

操作如下：

SELECT name, birth, death,(YEAR(death)-YEAR(birth)) - (RIGHT(death,5) < RIGHT(birth,5)) AS age FROM pet WHERE death IS NOT NULL;

mysql> SELECT name,birth,death,
-> (YEAR(death)-YEAR(birth)) -
-> (RIGHT(death,5)<RIGHT(birth,5)) AS age
-> FROM pet
-> WHERE death IS NOT NULL;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+
1 row in set (0.00 sec)

mysql>

注意：NULL是特殊的值， 不能用普通的比较符比较

mysql> SELECT name,birth,death,
-> (YEAR(death)-YEAR(birth)) -
-> (RIGHT(death,5)<RIGHT(birth,5)) AS age
-> FROM pet
-> WHERE death != NULL;
Empty set (0.00 sec)

mysql>

 

刚才说了那么多关于YEAR()的日期提取函数， 其实还有很多 比如说 MONTH() 和 DAYOFMONTH() 这两个；

操作如下：

SELECT name, birth, MONTH(birth), DAYOFMONTH(birth) FROM pet ORDER BY species DESC;

mysql> SELECT name, birth, MONTH(birth), DAYOFMONTH(birth)
-> FROM pet ORDER BY species DESC;
+----------+------------+--------------+-------------------+
| name | birth | MONTH(birth) | DAYOFMONTH(birth) |
+----------+------------+--------------+-------------------+
| Slim | 1996-04-29 | 4 | 29 |
| Puffball | 1999-03-30 | 3 | 30 |
| Buffy | 1989-05-13 | 5 | 13 |
| Fang | 1990-08-27 | 8 | 27 |
| Bowser | 1989-08-31 | 8 | 31 |
| Fluffy | 1993-02-04 | 2 | 4 |
| Claws | 1994-03-17 | 3 | 17 |
| Whistler | 1997-12-09 | 12 | 9 |
| Chirpy | 1998-09-11 | 9 | 11 |
+----------+------------+--------------+-------------------+
9 rows in set (0.00 sec)

mysql>

 

接着往后面玩：假定当前月是4月（其实是五月）,我要提前一个月准备给pet们过生日。So,我应该查找5月份过生日的pets。

操作如下：

SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

mysql> SELECT name, birth
-> FROM pet
-> WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.22 sec)

mysql>

这时候，看喜欢套路的你肯定会知道。问题难度要小小升级一下。

 

现在我不知道现在是几月份，我就是想要小个月准备给pets过生日。What should I do ?

操作如下：

SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(CURDATE()) + 1;

然而天天日代码的老司机们都知道！上面的操作一定是假的。这是有问题的！

当我要给一月份的pets准备生日我用上面的语句能查到吗？查不到，根本就没有0月！

 

那经验丰富的程序员们一定在心中有一个大胆的想法：Mysql中是不是有'%'啊?

没错，是有的。

操作如下：

SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

mysql> SELECT name,birth
-> FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
Empty set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2017-05-26 16:01:09 |
+---------------------+
1 row in set (0.04 sec)

mysql>

 ...这TM就尴尬了,没有在6月份过生日的pet.

没关系，我们的套路继续（书上的套路继续）。下面给出书上的另一种方法。

操作如下：

SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));
Empty set (0.10 sec)

mysql>

这里用到了一个DATE_ADD()函数，具体的讲解给大家一个传送门。如果传送门失效了，那就去百度吧骚年。

 

to be continued...