MYSQL培训经典教程第四章

1、.第4章MYSQL高级特性本章要点：v 集合函数与时间函数v 字符串的模式匹配v 如何创建索引v 检索数据中的一些技巧第三章向你初步介绍了SQL。你学会了如何用SELECT语句进行查询，你还学会了如何建立自己的表以及如何录入数据等。在这一章里，你将加深你SQL语言知识。你将学习如何建立索引来加快查询速度。你还将学会如果用更多的SQL语句和函数来操作表中的数据。尤其是对检索语句SELECT的使用，其中技巧繁多，可以说这是SQL语言中最重要的语句，也是使用者最常使用的语句。本章将详细介绍SELECT语句的用法。无论如何，这里的介绍都不能包括SQL语言的所有技巧，读者应该在平时的不断使用中积累经验。

2、4.1 集合函数到现在为止，你只学习了如何根据特定的条件从表中取出一条或多条记录。但是，假如你想对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计，都需要使用集合函数。你可以统计记录数目，平均值，最小值，最大值，或者求和。当你使用一个集合函数时，它只返回一个数，该数值代表这几个统计值之一。这些函数的最大特点就是经常和GROUP BY语句配合使用，需要注意的是集合函数不能和非分组的列混合使用。4.1.1 行列计数l 计算查询语句返回的记录行数直接计算函数COUNT(*)的值，例

3、如，计算pet表中猫的只数：mysql>SELECT count(*) FROM pet WHERE species=cat;+-+| count(*) |+-+| 2 |+-+4.1.2统计字段值的数目例如，计算pet表中species列的数目：mysql> SELECT count(species) FROM pet;+-+| count(species) |+-+| 9 |+-+如果相同的种类出现了不止一次，该种类将会被计算多次。如果你想知道种类为某个特定值的宠物有多少个，你可以使用WHERE子句，如下例所示： mysql> SELECT COUNT(species)

4、FROM pet WHERE species='cat' ;注意这条语句的结果：+-+| COUNT(species) |+-+| 2 |+-+这个例子返回种类为'cat'的作者的数目。如果这个名字在表pet中出现了两次，则次函数的返回值是2。 而且它和上面提到过的语句的结果是一致的：SELECT count(*) FROM pet WHERE species=cat实际上，这两条语句是等价的。假如你想知道有多少不同种类的的宠物数目。你可以通过使用关键字DISTINCT来得到该数目。如下例所示： mysql> SELECT COUNT(DISTINCT s

5、pecies) FROM pet;+-+| COUNT(DISTINCT species) |+-+| 5 |+-+如果种类'cat'出现了不止一次，它将只被计算一次。关键字DISTINCT 决定了只有互不相同的值才被计算。 通常，当你使用COUNT（）时，字段中的空值将被忽略。另外，COUNT()函数通常和GROUP BY子句配合使用，例如可以这样返回每种宠物的数目：mysql> SELECT species,count(*) FROM pet GROUP BY species;+-+-+| species | count(*) |+-+-+| bird | 2 | c

6、at | 2 | dog | 3 | hamster | 1 | snake | 1 |+-+-+4.1.3 计算字段的平均值需要计算这些值的平均值。使用函数AVG（），你可以返回一个字段中所有值的平均值。 假如你对你的站点进行一次较为复杂的民意调查。访问者可以在1到10之间投票，表示他们喜欢你站点的程度。你把投票结果保存在名为vote的INT型字段中。要计算你的用户投票的平均值，你需要使用函数AVG（）： SELECT AVG(vote) FROM opinion 这个SELECT语句的返回值代表用户对你站点的平均喜欢程度。函数AVG（）只能对数值型字段使用。这个函数在计算平均值时也忽略空值

7、。 再给出一个实际例子，例如我们要计算pet表中每种动物年龄的平均值，那么使用AVG()函数和GROUP BY子句：mysql> SELECT species,AVG(CURDATE()-birth) FROM pet GROUP BY species;返回的结果为：+-+-+| species | AVG(CURDATE()-birth) |+-+-+| bird | 34160 | cat | 74959.5 | dog | 112829.66666667 | hamster | 19890 | snake | 49791 |+-+-+4.1.4 计算字段值的和假设你的站点被用来出售

8、某种商品，已经运行了两个月，是该计算赚了多少钱的时候了。假设有一个名为orders的表用来记录所有访问者的定购信息。要计算所有定购量的总和，你可以使用函数SUM（）： SELECT SUM(purchase_amount) FROM orders 函数SUM（）的返回值代表字段purchase_amount中所有值的总和。字段purchase_amount的数据类型也许是DECIMAL类型，但你也可以对其它数值型字段使用函数SUM（）。用一个不太恰当的例子说明，我们计算pet表中同种宠物的年龄的总和：mysql> SELECT species,SUM(CURDATE()-birth) F

9、ROM pet GROUP BY species;你可以查看结果，与前一个例子对照：+-+-+| species | SUM(CURDATE()-birth) |+-+-+| bird | 68320 | cat | 149919 | dog | 338489 | hamster | 19890 | snake | 49791 |+-+-+4.1.5 计算字段值的极值求字段的极值，涉及两个函数MAX()和MIN()。例如，还是pet表，你想知道最早的动物出生日期，由于日期最早就是最小，所以可以使用MIN()函数：mysql> SELECT MIN(birth) FROM pet;+-+|

10、 MIN(birth) |+-+| 1989-05-13 |+-+但是，你只知道了日期，还是无法知道是哪只宠物，你可能想到这样做：SELECT name,MIN(birth) FROM pet;但是，这是一个错误的SQL语句，因为集合函数不能和非分组的列混合使用，这里name列是没有分组的。所以，你无法同时得到name列的值和birth的极值。MIN()函数同样可以与GROUP BY子句配合使用，例如，找出每种宠物中最早的出生日期：mysql> SELECT species,MIN(birth) FROM pet GROUP BY species;下面是令人满意的结果：+-+-+| sp

11、ecies | MIN(birth) |+-+-+| bird | 1997-12-09 | cat | 1993-02-04 | dog | 1989-05-13 | hamster | 1999-03-30 | snake | 1996-04-29 |+-+-+另一方面，如果你想知道最近的出生日期，就是日期的最大值，你可以使用MAX()函数，如下例所示： mysql> SELECT species,MAX(birth) FROM pet GROUP BY species; +-+-+| species | MAX(birth) |+-+-+| bird | 1998-09-11 |

12、cat | 1994-03-17 | dog | 1990-08-31 | hamster | 1999-03-30 | snake | 1996-04-29 |+-+-+4.1.6 总结在本节中，介绍了一些典型的集合函数的用法，包括计数、均值、极值和总和，这些都是SQL语言中非常常用的函数。这些函数之所以称之为集合函数，是因为它们应用在多条记录中，所以集合函数最常见的用法就是与GROUP BY子句配合使用，最重要的是集合函数不能同未分组的列混合使用。4.2 操作日期和时间日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在秒级

13、跟踪一个表的改变。日期和时间类型是DATETIME、DATE、TIMESTAMP、TIME和YEAR。这些的每一个都有合法值的一个范围，而“零”当你指定确实不合法的值时被使用。注意，MySQL允许你存储某个“不严格地”合法的日期值，例如1999-11-31，原因我们认为它是应用程序的责任来处理日期检查，而不是SQL服务器。为了使日期检查更“快”，MySQL仅检查月份在0-12的范围，天在0-31的范围。上述范围这样被定义是因为MySQL允许你在一个DATE或DATETIME列中存储日期，这里的天或月是零。这对存储你不知道准确的日期的一个生日的应用程序来说是极其有用的，在这种情况下，你简单地存储

14、日期象1999-00-00或1999-01-00。（当然你不能期望从函数如DATE_SUB()或DATE_ADD()得到类似以这些日期的正确值）。4.2.1 返回当前日期和时间通过函数GETDATE（），你可以获得当前的日期和时间。例如，l CURDATE() 返回当前日期CURRENT_DATE 以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。 mysql> select CURDATE();+-+| CURDATE() |+-+| 2001-02-20 |+-+mysql> select CURDAT

15、E() + 0;+-+| CURDATE()+0 |+-+| 20010220 |+-+l CURTIME() 返回当前时间以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。 mysql> select CURTIME();+-+| CURTIME() |+-+| 10:42:38 |+-+mysql> select CURTIME() + 0;+-+| CURTIME()+0 |+-+| 104525 |+-+l NOW() 返回当前时期和时间NOW()以YYYY-MM-DD HH:MM:SS的格式或者YYY

16、YMMDDHHMMSS的格式返回日期和时间值，取决于上下文。mysql>select now();+-+| now() |+-+| 2001-02-20 10:45:57 |+-+mysql>select now()+0;+-+| now()+0 |+-+| 20010220105635 |+-+这些得到当前日期和时间的函数，对于日期和时间的计算很方便，尤其是计算一个时间到现在的时间差。例如，在pet表中，我们以天为单位计算宠物的年龄：mysql> SELECT name,CURDATE()-birth FROM pet;+-+-+| name | CURDATE()-bir

17、th |+-+-+| Fluffy | 80016 | Claws | 69903 | Buffy | 119707 | Chirpy | 29309 | Fang | 109393 | Bowser | 109389 | Whistler | 39011 | Slim | 49791 | Puffball | 19890 |+-+-+4.2.2 自动记录数据的改变时间TIMESTAMP列类型提供一种类型，TIMESTAMP值可以从1970的某时的开始一直到2037年，精度为一秒，其值作为数字显示。你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMES

18、TAMP列，只有第一个自动更新。 自动更新第一个TIMESTAMP列在下列任何条件下发生： l 列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。 l 列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。（注意一个UPDATE设置一个列为它已经有的值，这将不引起TIMESTAMP列被更新，因为如果你设置一个列为它当前的值，MySQL为了效率而忽略更改。） l 你明确地设定TIMESTAMP列为NULL. 除第一个以外的TIMESTAMP列也可以设置到当前的日期和时间，只要将列设为NULL，或NOW()。 例如，创建如下的表：mysql> CREATE

19、TABLE student-> (-> id int,-> name char(16),-> english tinyint,-> chinese tinyint,-> history tinyint,-> time timestamp-> );向表中插入记录，可以查看效果：mysql> INSERT student(id,name,englisht,Chinese,history) VALUES(11,”Tom”,66,93,67);查看记录的存储情况：mysql> SELECT * FROM student;+-+-+-+-+-+

20、-+| id | name | english | chinese | history | time |+-+-+-+-+-+-+| 11 | Tom | 66 | 93 | 67 | 20010220123335 |+-+-+-+-+-+-+你可以看到time列纪录下了数据录入时的时间值。如果你更新改记录，在查看操作的结果：mysql> UPDATE student SET english=76 WHERE id=11;mysql> SELECT * FROM student;+-+-+-+-+-+-+| id | name | english | chinese | histo

21、ry | time |+-+-+-+-+-+-+| 11 | Tom | 76 | 93 | 67 | 20010220125736 |+-+-+-+-+-+-+可以清楚的看到，time列的时间被自动更改为修改记录的时间。有时候你希望不更改任何值，也能打到修改TIMESTAMP列的值，这时只要设置该列的值为NULL，MySQL就可以自动更新TIMESTAMP列的值：mysql> UPDATE student SET time=NULL WHERE id=11;mysql> select * from student where id=11;+-+-+-+-+-+-+| id | n

22、ame | english | chinese | history | time |+-+-+-+-+-+-+| 11 | Tom | 76 | 93 | 67 | 20010220130517 |+-+-+-+-+-+-+通过明确地设置希望的值，你可以设置任何TIMESTAMP列为不同于当前日期和时间的值，即使对第一个TIMESTAMP列也是这样。例如，如果，当你创建一个行时，你想要一个TIMESTAMP被设置到当前的日期和时间，但在以后无论何时行被更新时都不改变，你可以使用这样使用： l 让MySQL在行被创建时设置列，这将初始化它为当前的日期和时间。 l 当你执行随后的对该行中其他列的更

23、改时，明确设定TIMESTAMP列为它的当前值。 例如，当你在修改列时，可以把原有的值付给TIMESTAMP列：mysql> UPDATE student SET english=66,time=time WHERE id=11;mysql> select * from student where id=11;+-+-+-+-+-+-+| id | name | english | chinese | history | time |+-+-+-+-+-+-+| 11 | Tom | 66 | 93 | 67 | 20010220130517 |+-+-+-+-+-+-+另一方面，

24、你可能发现，当你想要实现上面这个效果时，很容易用一个你用NOW()初始化的DATETIME列然后不再改变它，这样也许直接些。 但是，TIMESTAMP列的以后好处是存储要求比较小，节省空间。TIMESTAMP的存储需求是4字节，而DATETIME列的存储需求是8字节。4.2.3 返回日期和时间范围当你分析表中的数据时，你也许希望取出某个特定时间的数据。我们用下面一个表来模仿一个web站点的记录。mysql> CREATE TABLE weblog-> (-> data float,-> entrydate datetime-> );然后随机的增加几个数据：mysq

25、l> INSERT weblog VALUES(rand(),now();rand()函数返回一个随机的浮点值，now()函数返回当前时间。多执行上面语句几次，得到一个作为测试的表。最为测试你还可以增加一个值：mysql> INSERT weblog VALUES(rand(),”2001-02-08”);这条语句，插入一个entry为”2001-02-08 00:00:00”的值（假定现在为2001年2月8日），你可以查看这个表的值：mysql> select * from weblog;+-+-+| data | entrydate |+-+-+| 0.973723 |

26、2001-02-08 00:00:00 | 0.437768 | 2001-02-08 13:57:06 | 0.327279 | 2001-02-08 13:57:09 | 0.0931809 | 2001-02-08 13:58:29 | 0.198805 | 2001-02-08 13:57:54 |+-+-+你也许对特定的某一天中比如说2001年2月18日访问者在你站点上的活动感兴趣。要取出这种类型的数据，你也许会试图使用这样的SELECT语句： mysql> SELECT * FROM weblog WHERE entrydate="2001-02-08"

27、不要这样做。这个SELECT语句不会返回正确的记录它将只返回值为2000-02-08 00:00:00的记录，换句话说，只返回当天零点零时的记录。上面语句的结果为： +-+-+| data | entrydate |+-+-+| 0.973723 | 2001-02-08 00:00:00 |+-+-+要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。1、使用关系运算符和逻辑运算符来限制时间范围例如，下面的这个SELECT 语句将能返回正确的记录： mysql> SELECT * FROM weblog -> WHEREentrydate>="

28、;2001-02-08" AND entrydate<"2001-02-09" ;这个语句可以完成任务，因为它选取的是表中的日期和时间大于等于2001-02-08 00:00:00并小于2001-02-09 00:00:00的记录。换句话说，它将正确地返回2000年2月8日这一天输入的每一条记录。 其结果为：+-+-+| data | entrydate |+-+-+| 0.973723 | 2001-02-08 00:00:00 | 0.437768 | 2001-02-08 13:57:06 | 0.327279 | 2001-02-08 13:57:0

29、9 | 0.0931809 | 2001-02-08 13:58:29 | 0.198805 | 2001-02-08 13:57:54 |+-+-+2、另一种方法是，你可以使用LIKE来返回正确的记录。通过在日期表达式中包含通配符“”，你可以匹配一个特定日期的所有时间。这里有一个例子： mysql> SELECT * FROM weblog WHERE entrydate LIKE '2001-02-08%' ;这个语句可以匹配正确的记录。因为通配符“”代表了任何时间。 +-+-+| data | entrydate |+-+-+| 0.973723 | 2001-02

30、-08 00:00:00 | 0.437768 | 2001-02-08 13:57:06 | 0.327279 | 2001-02-08 13:57:09 | 0.0931809 | 2001-02-08 13:58:29 | 0.198805 | 2001-02-08 13:57:54 |+-+-+3、上面两种方法的异同由于使用关系运算符进行的是比较过程，时转换成内部的存储格式后进行的，因此，因此时间的书写可以不是那么严格要求。例如，下面几种写法是等价的：mysql> SELECT * FROM weblog WHERE entrydate>="2001-02-08&

31、quot;mysql> SELECT * FROM weblog WHERE entrydate>="2001-2-8"mysql> SELECT * FROM weblog WHERE entrydate>="2001*02*08"mysql> SELECT * FROM weblog WHERE entrydate>="20010208"SELECT * FROM weblog WHERE entrydate>="2001/2/8"而使用LIKE运算符和模式匹配，是通过

32、比较串值进行的，因此必须使用标准的时间书写格式，YYYY-MM-DD HH-MM-SS。4.2.5 比较日期和时间已知两个日期，比较它们的前后，可以直接求出它们的差和零值比较，也可以利用已知的时间函数：TO_DAYS(date) 给出一个日期date，返回一个天数(从0年的天数)，date可以是一个数字，也可以是一个串值，当然更可以是包含日期的时间类型。 mysql> select TO_DAYS(960501);+-+| TO_DAYS(960501) |+-+| 729145 |+-+mysql> select TO_DAYS('1997-07-01');+-+

33、| TO_DAYS('1997-07-01') |+-+| 729571 |+-+例如：返回2个时间相差的天数(21世纪已经过去了多少天)mysql> select to_days(now()-to_days('20010101');+-+| to_days(now()-00000012000000)-to_days('20010101') |+-+| 38 |+-+4.3 字符串模式匹配MySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。 4.3.1 标准的SQL模式匹

34、配SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。 例如，在表pet中，为了找出以“b”开头的名字： mysql> SELECT * FROM pet WHERE name LIKE "b%"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Buffy | Harold

35、 | 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 | Buf

36、fy | 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-

37、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 |

38、 1989-05-13 | NULL |+-+-+-+-+-+-+4.3.2 扩展正则表达式模式匹配由MySQL提供的模式匹配的其他类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。 扩展正则表达式的一些字符是： “.”匹配任何单个的字符。 一个字符类“.”匹配在方括号内的任何字符。例如，“abc”匹配“a”、“b”或“c”。为了命名字符的一个范围，使用一个“-”。“a-z”匹配任何小写字母，而“0-9”匹配任何数字。 “ * ”匹配零个或多个在它前面的东西。例如，“x*”匹配任何数量的“x”

39、字符，“0-9*”匹配的任何数量的数字，而“.*”匹配任何数量的任何东西。 正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，“aA”匹配小写或大写的“a”而“a-zA-Z”匹配两种写法的任何字母。 如果它出现在被测试值的任何地方，模式就匹配(只要他们匹配整个值，SQL模式匹配)。 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“”或在模式的结尾用“$”。 为了说明扩展正则表达式如何工作，上面所示的LIKE查询在下面使用REGEXP重写： 为了找出以“b”开头的名字，使用“”匹配名字的开始并且“bB”匹配小写或大写的“b”： mysql>

40、; 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 WHE

41、RE 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 "w

42、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 |+-+-+-+-+-+-+既然如果一个正规表达式出现在值的任何地方，其模式匹配了，就不必再先前的查询中在模式的两方面放置一个通配符以使得

43、它匹配整个值，就像如果你使用了一个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