Retrieving Information from a Table
207
Finding animals with birthdays in the upcoming month is also simple. Suppose that the current month is
April. Then the month value is
4
and you can look for animals born in May (month
5
) like this:
mysql>
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
There is a small complication if the current month is December. You cannot merely add one to the
month number (
12
) and look for animals born in month
13
, because there is no such month. Instead,
you look for animals born in January (month
1
).
You can write the query so that it works no matter what the current month is, so that you do not have
to use the number for a particular month.
DATE_ADD()
[919]
enables you to add a time interval to
a given date. If you add a month to the value of
CURDATE()
[918]
, then extract the month part with
MONTH()
[925]
, the result produces the month in which to look for birthdays:
mysql>
SELECT name, birth FROM pet
->
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
A different way to accomplish the same task is to add
1
to get the next month after the current one after
using the modulo function (
MOD
) to wrap the month value to
0
if it is currently
12
:
mysql>
SELECT name, birth FROM pet
->
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
MONTH()
[925]
returns a number between
1
and
12
. And
MOD(something,12)
[911]
returns a
number between
0
and
11
. So the addition has to be after the
MOD()
[911]
, otherwise we would go
from November (
11
) to January (
1
).
3.3.4.6. Working with
NULL
Values
The
NULL
value can be surprising until you get used to it. Conceptually,
NULL
means “a missing
unknown value” and it is treated somewhat differently from other values.
To test for
NULL
, use the
IS NULL
[877]
and
IS NOT NULL
[877]
operators, as shown here:
mysql>
SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
You cannot use arithmetic comparison operators such as
=
[875]
,
<
[876]
, or
<>
[876]
to test for
NULL
. To demonstrate this for yourself, try the following query:
mysql>
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
Because the result of any arithmetic comparison with
NULL
is also
NULL
, you cannot obtain any
meaningful results from such comparisons.
In MySQL,
0
or
NULL
means false and anything else means true. The default truth value from a
boolean operation is
1
.
This special treatment of
NULL
is why, in the previous section, it was necessary to determine which
animals are no longer alive using
death IS NOT NULL
instead of
death <> NULL
.
Two
NULL
values are regarded as equal in a
GROUP BY
.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...