Mysql Study Notes 005 and mysql Study Notes

Mysql Study Notes 005 and mysql Study Notes
Date Calculation

MySQL provides several functions for date calculation. Balara ....

For example, I want to determine the size of the pets, and calculate the difference between the current date and the date of birth.

The procedure is as follows:

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 | 18 |
| Fluffy | 1992-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 () is the YEAR, no problem.

Insert a millennium bug

Tip: it is very likely that you will encounter a classic problem called the millennium bug.

Select year ('89-01-01 '); the result is 1989.

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

Mysql>

Select year ('03-01-01 '); the result is 2003.

Mysql> select year ('03-01-01 ');
+ ------------------ +
| YEAR ('03-01-01 ') |
+ ------------------ +
| 1, 2003 |
+ ------------------ +
1 row in set (0.00 sec)

Mysql>

There are also ancient programmers telling me that 69 70 is a hurdle. I tried it.

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

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

Mysql>

Really. My idea is that the first day of the computer world is. So there is such a hurdle. Now Java can reach the number of days before January 1.

 

 

Well, the book is now in the beginning. We calculated the age for our pets just now, but a customer who understands Mysql was very dissatisfied. The age of a pet is counted based on its birthday ).

The procedure is as follows:

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 | 18 |
| Fluffy | 24 |
| Claws | 23 |
| Buffy | 1989-05-13 | 2017-05-26 | 28 |
| Fang | 1990-08-27 | 2017-05-26 | 26 |
| Bowser | 27 |
| Chirpy | 18 |
| Slim | 21 |
+ ---------- + ------------ + ------ +
9 rows in set (0.00 sec)

Mysql>

RIGHT (birth, 5) is the date of birth.

I have another bold idea here: Mysql is developed in C/C ++. I guess bool type can be computed with int type in Mysql operations.

 

Another example: there is actually another vulnerability that you didn't notice. One pet is dead. How can we still have age when we die? First, let's look at the dead pet.

The procedure is as follows:

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 |
+ -------- + ------------ + ------ +
| Bo5-0| | 5 |
+ -------- + ------------ + ------ +
1 row in set (0.00 sec)

Mysql>

Note: NULL is a special value and cannot be compared with common comparison characters.

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>

 

There are many date extraction functions, such as MONTH () and DAYOFMONTH;

The procedure is as follows:

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 | 3 | 30 |
| Buffy | 1989-05-13 | 5 | 13 |
| Fang | 1990-08-27 | 8 | 27 |
| Bowser | 1989-08-31 | 8 | 31 |
| Fluffy | 1992-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>

 

Next, let's play with it: assume that the current month is July 22, April (in fact, July 22, May). I want to give my pet a birthday one month in advance. So, I should look for pets for my birthday in February.

The procedure is as follows:

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>

At this time, you will certainly know what you like. The problem is difficult to upgrade.

 

I don't know how many months it is now. I just want a small month to celebrate my birthday for pets. What shoshould I do?

The procedure is as follows:

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

However, the old drivers of daily code know it! The above operations must be false. This is a problem!

When I want to prepare a birthday for pets in February, can I use the following statement to check it? No, it's not 0 months!

 

So experienced programmers must have a bold idea in mind: Is there '%' in Mysql?

Yes, yes.

The procedure is as follows:

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 () |
+ --------------------- +
| 16:01:09 |
+ --------------------- +
1 row in set (0.04 sec)

Mysql>

... This TM is embarrassing, and there is no pet for the birthday in February.

It doesn't matter. Our routine continues (the routine in the book continues ). The following is another method in the book.

The procedure is as follows:

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>

Here we use a DATE_ADD () function, which provides a portal for you. If the portal fails, go to Baidu to cool.

 

To be continued...