MySQL中文参考手册-创造并使用数据库（5）

来自：

　　4.5 日期计算
　　MySQL提供几个函数，你能用来执行在日期上的计算，例如，计算年龄或提取日期的部分。
　　
　　为了决定你的每个宠物有多大，用出生日期和当前日期之间的差别计算年龄。通过变换2个日期到天数，取差值，并且用365除(在一年里的天数)：
　　
　　mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 FROM pet;
　　+----------+-------------------------------------+
　　| name　　 | (TO_DAYS(NOW())-TO_DAYS(birth))/365 |
　　+----------+-------------------------------------+
　　| Fluffy　 |　　　　　　　　　　　　　　　　6.15 |
　　| Claws　　|　　　　　　　　　　　　　　　　5.04 |
　　| Buffy　　|　　　　　　　　　　　　　　　　9.88 |
　　| Fang　　 |　　　　　　　　　　　　　　　　8.59 |
　　| Bowser　 |　　　　　　　　　　　　　　　　9.58 |
　　| Chirpy　 |　　　　　　　　　　　　　　　　0.55 |
　　| Whistler |　　　　　　　　　　　　　　　　1.30 |
　　| Slim　　 |　　　　　　　　　　　　　　　　2.92 |
　　| Puffball |　　　　　　　　　　　　　　　　0.00 |
　　+----------+-------------------------------------+
　　
　　尽管查询可行，关于它还有能被改进的一些事情。首先，如果行以某个次序表示，其结果能更容易被扫描。第二，年龄列的标题不是很有意义的。
　　
　　第一个问题通过增加一个ORDER BY name子句按名字排序输出来解决。为了处理列标题，为列提供一个名字以便一个不同的标签出现在输出中(这被称为一个列别名)：
　　
　　mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age
　　　　-> FROM pet ORDER BY name;
　　+----------+------+
　　| name　　 | age　|
　　+----------+------+
　　| Bowser　 | 9.58 |
　　| Buffy　　| 9.88 |
　　| Chirpy　 | 0.55 |
　　| Claws　　| 5.04 |
　　| Fang　　 | 8.59 |
　　| Fluffy　 | 6.15 |
　　| Puffball | 0.00 |
　　| Slim　　 | 2.92 |
　　| Whistler | 1.30 |
　　+----------+------+
　　
　　为了按age而非name排序输出，只要使用一个不同ORDER BY子句：
　　
　　mysql>　SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age
　　　　->　FROM pet ORDER BY age;
　　+----------+------+
　　| name　　 | age　|
　　+----------+------+
　　| Puffball | 0.00 |
　　| Chirpy　 | 0.55 |
　　| Whistler | 1.30 |
　　| Slim　　 | 2.92 |
　　| Claws　　| 5.04 |
　　| Fluffy　 | 6.15 |
　　| Fang　　 | 8.59 |
　　| Bowser　 | 9.58 |
　　| Buffy　　| 9.88 |
　　+----------+------+
　　
　　一个类似的查询可以被用来确定已经死亡动物的死亡年龄。你通过检查death值是否是NULL来决定那些是哪些动物，然后，对于那些有非NULL值，计算在death和birth值之间的差别：
　　
　　mysql>　SELECT name, birth, death, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age
　　　　->　FROM pet WHERE death IS NOT NULL ORDER BY age;
　　+--------+------------+------------+------+
　　| name　 | birth　　　| death　　　| age　|
　　+--------+------------+------------+------+
　　| Bowser | 1989-08-31 | 1995-07-29 | 5.91 |
　　+--------+------------+------------+------+
　　
　　差询使用death IS NOT NULL而非death != NULL，因为NULL是特殊的值，这以后会解释。见4.6 用NULL值工作。
　　
　　如果你想要知道哪个动物下个月过生日，怎么办？对于这类计算，年和天是无关的，你简单地想要提取birth列的月份部分。MySQL提供几个日期部分的提取函数，例如YEAR()、MONTH()和DAYOFMONTH()。在这里MONTH()是适合的函数。为了看它怎样工作，运行一个简单的查询，显示birth和MONTH(birth)的值：
　　
　　mysql> SELECT name, birth, MONTH(birth) FROM pet;
　　+----------+------------+--------------+
　　| name　　 | birth　　　| MONTH(birth) |
　　+----------+------------+--------------+
　　| Fluffy　 | 1993-02-04 |　　　　　　2 |
　　| Claws　　| 1994-03-17 |　　　　　　3 |
　　| Buffy　　| 1989-05-13 |　　　　　　5 |
　　| Fang　　 | 1990-08-27 |　　　　　　8 |
　　| Bowser　 | 1989-08-31 |　　　　　　8 |
　　| Chirpy　 | 1998-09-11 |　　　　　　9 |
　　| Whistler | 1997-12-09 |　　　　　 12 |
　　| Slim　　 | 1996-04-29 |　　　　　　4 |
　　| Puffball | 1999-03-30 |　　　　　　3 |
　　+----------+------------+--------------+
　　
　　用下个月的生日找出动物也是容易的。假定当前月是4月，那么月值是4并且你寻找在5月出生的动物 (5月)， 象这样：
　　
　　mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
　　+-------+------------+
　　| name　| birth　　　|
　　+-------+------------+
　　| Buffy | 1989-05-13 |
　　+-------+------------+
　　
　　当然如果当前月份是12月，就有点复杂了。你不是只把加1到月份数(12)上并且寻找在13月出生的动物，因为没有这样的月份。相反，你寻找在1月出生的动物(1月) 。
　　
　　你甚至可以编写查询以便不管当前月份是什么它都能工作。这种方法你不必在查询中使用一个特定的月份数字，DATE_ADD()允许你把时间间隔加到一个给定的日期。如果你把一个月加到NOW()值上，然后用MONTH()提取月份部分，结果产生寻找生日的月份：
　　
　　mysql> SELECT name, birth FROM pet
　　　　-> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
　　
　　完成同样任务的一个不同方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如果它当前是12，则“绕回”月份到值0)：
　　
　　mysql> SELECT name, birth FROM pet
　　　　-> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;
　　
　　注意，MONTH返回在1和12之间的一个数字，且MOD(something,12)返回在0和11之间的一个数字，因此必须在MOD()以后加1，否则我们将从11月( 11 )跳到1月(1)。
　　
　　4.6 NULL值操作
　　NULL值可能很奇怪直到你习惯于它。概念上，NULL意味着“没有值”或“未知值”，且它被看作有点与众不同的值。为了测试NULL，你不能使用算术比较运算符例如=、<或!=。为了说明它，试试下列查询：
　　
　　mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL;
　　+----------+-----------+----------+----------+
　　| 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL |
　　+----------+-----------+----------+----------+
　　|　　 NULL |　　　NULL |　　 NULL |　　 NULL |
　　+----------+-----------+----------+----------+
　　
　　很清楚你从这些比较中得到毫无意义的结果。相反使用IS NULL和IS NOT NULL操作符：
　　
　　mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
　　+-----------+---------------+
　　| 1 IS NULL | 1 IS NOT NULL |
　　+-----------+---------------+
　　|　　　　 0 |　　　　　　 1 |
　　+-----------+---------------+
　　
　　在MySQL中，0意味着假而1意味着真。
　　
　　NULL这样特殊的处理是为什么，在前面的章节中，为了决定哪个动物不再是活着的，使用death IS NOT NULL而不是death != NULL是必要的。
　　
　　4.7 模式匹配
　　MySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。
　　
　　SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。
　　
　　为了找出以“b”开头的名字：
　　
　　mysql> SELECT * FROM pet WHERE name LIKE "b%";
　　+--------+--------+---------+------+------------+------------+
　　| name　 | owner　| species | sex　| birth　　　| death　　　|
　　+--------+--------+---------+------+------------+------------+
　　| Buffy　| Harold | dog　　 | f　　| 1989-05-13 | NULL　　　 |
　　| Bowser | Diane　| dog　　 | m　　| 1989-08-31 | 1995-07-29 |
　　+--------+--------+---------+------+------------+------------+
　　
　　为了找出以“fy”结尾的名字：
　　
　　mysql> SELECT * FROM pet WHERE name LIKE "%fy";
　　+--------+--------+---------+------+------------+-------+
　　| name　 | owner　| species | sex　| birth　　　| death |
　　+--------+--------+---------+------+------------+-------+
　　| Fluffy | Harold | cat　　 | f　　| 1993-02-04 | NULL　|
　　| Buffy　| Harold | dog　　 | f　　| 1989-05-13 | NULL　|
　　+--------+--------+---------+------+------------+-------+
　　
　　为了找出包含一个“w”的名字：
　　
　　mysql> SELECT * FROM pet WHERE name LIKE "%w%";
　　+----------+-------+---------+------+------------+------------+
　　| name　　 | owner | species | sex　| birth　　　| death　　　|
　　+----------+-------+---------+------+------------+------------+
　　| Claws　　| Gwen　| cat　　 | m　　| 1994-03-17 | NULL　　　 |
　　| Bowser　 | Diane | dog　　 | m　　| 1989-08-31 | 1995-07-29 |
　　| Whistler | Gwen　| bird　　| NULL | 1997-12-09 | NULL　　　 |
　　+----------+-------+---------+------+------------+------------+
　　
　　为了找出包含正好5个字符的名字，使用“_”模式字符：
　　
　　mysql> SELECT * FROM pet WHERE name LIKE "_____";
　　+-------+--------+---------+------+------------+-------+
　　| name　| owner　| species | sex　| birth　　　| death |
　　+-------+--------+---------+------+------------+-------+
　　| Claws | Gwen　 | cat　　 | m　　| 1994-03-17 | NULL　|
　　| Buffy | Harold | dog　　 | f　　| 1989-05-13 | NULL　|
　　+-------+--------+---------+------+------------+-------+
　　
　　由MySQL提供的模式匹配的其他类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。
　　
　　扩展正则表达式的一些字符是：
　　
　　“.”匹配任何单个的字符。
　　一个字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的一个范围，使用一个“-”。“[a-z]”匹配任何小写字母，而“[0-9]”匹配任何数字。
　　“ * ”匹配零个或多个在它前面的东西。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配的任何数量的数字，而“.*”匹配任何数量的任何东西。
　　正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，“[aA]”匹配小写或大写的“a”而“[a-zA-Z]”匹配两种写法的任何字母。
　　如果它出现在被测试值的任何地方，模式就匹配(只要他们匹配整个值，SQL模式匹配)。
　　为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。
　　为了说明扩展正则表达式如何工作，上面所示的LIKE查询在下面使用REGEXP重写：
　　
　　为了找出以“b”开头的名字，使用“^”匹配名字的开始并且“[bB]”匹配小写或大写的“b”：
　　
　　mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";
　　+--------+--------+---------+------+------------+------------+
　　| name　 | owner　| species | sex　| birth　　　| death　　　|
　　+--------+--------+---------+------+------------+------------+
　　| Buffy　| Harold | dog　　 | f　　| 1989-05-13 | NULL　　　 |
　　| Bowser | Diane　| dog　　 | m　　| 1989-08-31 | 1995-07-29 |
　　+--------+--------+---------+------+------------+------------+
　　
　　为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：
　　
　　mysql> SELECT * FROM pet WHERE name REGEXP "fy$";
　　+--------+--------+---------+------+------------+-------+
　　| name　 | owner　| species | sex　| birth　　　| death |
　　+--------+--------+---------+------+------------+-------+
　　| Fluffy | Harold | cat　　 | f　　| 1993-02-04 | NULL　|
　　| Buffy　| Harold | dog　　 | f　　| 1989-05-13 | NULL　|
　　+--------+--------+---------+------+------------+-------+
　　
　　为了找出包含一个“w”的名字，使用“[wW]”匹配小写或大写的“w”：
　　
　　mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";
　　+----------+-------+---------+------+------------+------------+
　　| name　　 | owner | species | sex　| birth　　　| death　　　|
　　+----------+-------+---------+------+------------+------------+
　　| Claws　　| Gwen　| cat　　 | m　　| 1994-03-17 | NULL　　　 |
　　| Bowser　 | Diane | dog　　 | m　　| 1989-08-31 | 1995-07-29 |
　　| Whistler | Gwen　| bird　　| NULL | 1997-12-09 | NULL　　　 |
　　+----------+-------+---------+------+------------+------------+
　　
　　既然如果一个正规表达式出现在值的任何地方，其模式匹配了，就不必再先前的查询中在模式的两方面放置一个通配符以使得它匹配整个值，就像如果你使用了一个SQL模式那样。
　　
　　为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：
　　
　　mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";
　　+-------+--------+---------+------+------------+-------+
　　| name　| owner　| species | sex　| birth　　　| death |
　　+-------+--------+---------+------+------------+-------+
　　| Claws | Gwen　 | cat　　 | m　　| 1994-03-17 | NULL　|
　　| Buffy | Harold | dog　　 | f　　| 1989-05-13 | NULL　|
　　+-------+--------+---------+------+------------+-------+
　　
　　你也可以使用“{n}”“重复n次”操作符重写先前的查询：
　　
　　mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";
　　+-------+--------+---------+------+------------+-------+
　　| name　| owner　| species | sex　| birth　　　| death |
　　+-------+--------+---------+------+------------+-------+
　　| Claws | Gwen　 | cat　　 | m　　| 1994-03-17 | NULL　|
　　| Buffy | Harold | dog　　 | f　　| 1989-05-13 | NULL　|
　　+-------+--------+---------+------+------------+-------+
　　
　　4.8 行计数
　　数据库经常用于回答这个问题，“某个类型的数据在一张表中出现的频度?”例如，你可能想要知道你有多少宠物，或每位主人有多少宠物，或你可能想要在你的动物上施行各种类型的普查。
　　
　　计算你拥有动物的总数字与“在pet表中有多少行?”是同样的问题，因为每个宠物有一个记录。COUNT()函数计数非NULL结果的数目，所以数你的动物的查询看起来像这样：
　　
　　mysql> SELECT COUNT(*) FROM pet;
　　+----------+
　　| COUNT(*) |
　　+----------+
　　|　　　　9 |
　　+----------+
　　
　　在前面，你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你可以使用COUNT()函数：
　　
　　mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
　　+--------+----------+
　　| owner　| COUNT(*) |
　　+--------+----------+
　　| Benny　|　　　　2 |
　　| Diane　|　　　　2 |
　　| Gwen　 |　　　　3 |
　　| Harold |　　　　2 |
　　+--------+----------+
　　
　　注意，使用GROUP BY对每个owner分组所有记录，没有它，你得到的一切是一条错误消息：
　　
　　mysql> SELECT owner, COUNT(owner) FROM pet;
　　ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
　　with no GROUP columns is illegal if there is no GROUP BY clause
　　
　　COUNT()和GROUP BY对以各种方式分类你的数据很有用。下列例子显示出实施动物普查操作的不同方式。
　　
　　每种动物数量：
　　
　　mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
　　+---------+----------+
　　| species | COUNT(*) |
　　+---------+----------+
　　| bird　　|　　　　2 |
　　| cat　　 |　　　　2 |
　　| dog　　 |　　　　3 |
　　| hamster |　　　　1 |
　　| snake　 |　　　　1 |
　　+---------+----------+
　　
　　每中性别的动物数量：
　　
　　mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
　　+------+----------+
　　| sex　| COUNT(*) |
　　+------+----------+
　　| NULL |　　　　1 |
　　| f　　|　　　　4 |
　　| m　　|　　　　4 |
　　+------+----------+
　　
　　（在这个输出中，NULL表示“未知性别”。）
　　
　　按种类和性别组合的动物数量：
　　
　　mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
　　+---------+------+----------+
　　| species | sex　| COUNT(*) |
　　+---------+------+----------+
　　| bird　　| NULL |　　　　1 |
　　| bird　　| f　　|　　　　1 |
　　| cat　　 | f　　|　　　　1 |
　　| cat　　 | m　　|　　　　1 |
　　| dog　　 | f　　|　　　　1 |
　　| dog　　 | m　　|　　　　2 |
　　| hamster | f　　|　　　　1 |
　　| snake　 | m　　|　　　　1 |
　　+---------+------+----------+
　　
　　当你使用COUNT()时，你不必检索整个一张表。例如, 先前的查询，当只在狗和猫上施行时，看起来像这样：
　　
　　mysql> SELECT species, sex, COUNT(*) FROM pet
　　　　-> WHERE species = "dog" OR species = "cat"
　　　　-> GROUP BY species, sex;
　　+---------+------+----------+
　　| species | sex　| COUNT(*) |
　　+---------+------+----------+
　　| cat　　 | f　　|　　　　1 |
　　| cat　　 | m　　|　　　　1 |
　　| dog　　 | f　　|　　　　1 |
　　| dog　　 | m　　|　　　　2 |
　　+---------+------+----------+
　　
　　或，如果你仅需要知道已知性别的按性别的动物数目：
　　
　　mysql> SELECT species, sex, COUNT(*) FROM pet
　　　　-> WHERE sex IS NOT NULL
　　　　-> GROUP BY species, sex;
　　+---------+------+----------+
　　| species | sex　| COUNT(*) |
　　+---------+------+----------+
　　| bird　　| f　　|　　　　1 |
　　| cat　　 | f　　|　　　　1 |
　　| cat　　 | m　　|　　　　1 |
　　| dog　　 | f　　|　　　　1 |
　　| dog　　 | m　　|　　　　2 |
　　| hamster | f　　|　　　　1 |
　　| snake　 | m　　|　　　　1 |
　　+---------+------+----------+