MYSQL经典培训课程第三章

本章要点：集合函数与时间函数字符串的模式匹配如何创建索引检索数据中的一些技巧第三章向你初步介绍了SQL。你学会了如何用SELECT语句进行查询，你还学会了如何建立自己的表以及如何录入数据等。在这一章里，你将加深你SQL语言知识。你将学习如何建立索引来加快查询速度。你还将学会如果用更多的SQL语句和函数来操作表中的数据。尤其是对检索语句SELECT的使用，其中技巧繁多，可以说这是SQL语言中最重要的语句，也是使用者最常使用的语句。本章将详细介绍SELECT语句的用法。无论如何，这里的介绍都不能包括SQL语言的所有技巧，读者应该在平时的不断使用中积累经验。MMMYYYSSSQQQLLL高高高级级级特特特性性性第第四四章章MySQL金典培训教程82集合函数到现在为止，你只学习了如何根据特定的条件从表中取出一条或多条记录。但是，假如你想对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计，都需要使用集合函数。你可以统计记录数目，平均值，最小值，最大值，或者求和。当你使用一个集合函数时，它只返回一个数，该数值代表这几个统计值之一。这些函数的最大特点就是经常和GROUPBY语句配合使用，需要注意的是集合函数不能和非分组的列混合使用。4.1.1行列计数计算查询昆山空调维修(*)的值，例如，计算pet表中猫的只数：mysqlSELECTcount(*)FROMpetWHEREspecies=’cat’;+----------+|count(*)|+----------+|2|+----------+4.1.2统计字段值的数目例如，计算pet表中species列的数目：mysqlSELECTcount(species)FROMpet;+----------------+|count(species)|+----------------+|9|+----------------+如果相同的种类出现了不止一次，该种类将会被计算多次。如果你想知道种类为某个特定值的宠物有多少个，你可以使用WHERE子句，如下例所示：mysqlSELECTCOUNT(species)FROMpetWHEREspecies='cat';注意这条语句的结果：4MySQL高级特性|COUNT(species)|+----------------+|2|+----------------+这个例子返回种类为'cat'的作者的数目。如果这个名字在表pet中出现了两次，则次函数的返回值是2。而且它和上面提到过的语句的结果是一致的：SELECTcount(*)FROMpetWHEREspecies=’cat’实际上，这两条语句是等价的。假如你想知道有多少不同种类的的宠物数目。你可以通过使用关键字DISTINCT来得到该数目。如下例所示：mysqlSELECTCOUNT(DISTINCTspecies)FROMpet;+-------------------------+|COUNT(DISTINCTspecies)|+-------------------------+|5|+-------------------------+如果种类'cat'出现了不止一次，它将只被计算一次。关键字DISTINCT决定了只有互不相同的值才被计算。通常，当你使用COUNT（）时，字段中的空值将被忽略。另外，COUNT()函数通常子句配合使用，例如可以这样返回每种宠物的数目：mysqlSELECTspecies,count(*)FROMpetGROUPBYspecies;+---------+----------+|species|count(*)|+---------+----------+|bird|2||cat|2||dog|3||hamster|1||snake|1|+---------+----------+4.1.3计算字段的平均值需要计算这些值的平均值。使用函数AVG（），你可以返回一个字段中所有值的平均值。MySQL金典培训教程84假如你对你的站点进行一次较为复杂的民意调查。访问者可以在1到10之间投票，表示他们喜欢你站点的程度。你把投票结果保存在名为vote的INT型字段中。要计算你的用户投票的平均值，你需要使用函数AVG（）：SELECTAVG(vote)FROMopinion这个SELECT语句的返回值代表用户对你站点的平均喜欢程度。函数AVG（）只能对数值型字段使用。这个函数在计算平均值时也忽略空值。再给出一个实际例子，例如我们要计算pet表中每种动物年龄的平均值，那么使用AVG()函数和GROUPBY子句：mysqlSELECTspecies,AVG(CURDATE()-birth)FROMpetGROUPBYspecies;返回的结果为：+---------+----------------------+|species|AVG(CURDATE()-birth)|+---------+----------------------+|bird|34160||cat|74959.5||dog|112829.66666667||hamster|19890||snake|49791|+---------+----------------------+4.1.4计算字段值的和假设你的站点被用来出售某种商品，已经运行了两个月，是该计算赚了多少钱的时候了。假设有一个名为orders的表用来记录所有访问者的定购信息。要计算所有定购量的总和，你可以使用函数SUM（）：SELECTSUM(purchase_amount)FROMorders函数SUM（）的返回值代表字段purchase_amount中所有值的总和。字段purchase_amount的数据类型也许是DECIMAL类型，但你也可以对其它数值型字段使用函数SUM（）。用一个不太恰当的例子说明，我们计算pet表中同种宠物的年龄的总和：mysqlSELECTspecies,SUM(CURDATE()-birth)FROMpetGROUPBYspecies;你可以查看结果，与前一个例子对照：+---------+----------------------+|species|SUM(CURDATE()-birth)|+---------+----------------------+4MySQL高级特性|bird|68320||cat|149919||dog|338489||hamster|19890||snake|49791|+---------+----------------------+4.1.5计算字段值的极值求字段的极值，涉及两个函数MAX()和MIN()。例如，还是pet表，你想知道最早的动物出生日期，由于日期最早就是最小，所以可以使用MIN()函数：mysqlSELECTMIN(birth)FROMpet;+------------+|MIN(birth)|+------------+|1989-05-13|+------------+但是，你只知道了日期，还是无法知道是哪只宠物，你可能想到这样做：SELECTname,MIN(birth)FROMpet;但是，这是一个错误的SQL语句，因为集合函数不能和非分组的列混合使用，这里name列是没有分组的。所以，你无法同时得到name列的值和birth的极值。MIN()函数同样可以与GROUPBY子句配合使用，例如，找出每种宠物中最早的出生日期：mysqlSELECTspecies,MIN(birth)FROMpetGROUPBYspecies;下面是令人满意的结果：+---------+------------+|species|MIN(birth)|+---------+------------+|bird|1997-12-09||cat|1993-02-04||dog|1989-05-13||hamster|1999-03-30||snake|1996-04-29|+---------+------------+另一方面，如果你想知道最近的出生日期，就是日期的最大值，你可以使用MAX()MySQL金典培训教程86函数，如下例所示：mysqlSELECTspecies,MAX(birth)FROMpetGROUPBYspecies;+---------+------------+|species|MAX(birth)|+---------+------------+|bird|1998-09-11||cat|1994-03-17||dog|1990-08-31||hamster|1999-03-30||snake|1996-04-29|+---------+------------+4.1.6总结在本节中，介绍了一些典型的集合函数的用法，包括计数、均值、极值和总和，这些都是SQL语言中非常常用的函数。这些函数之所以称之为集合函数，是因为它们应用在多条记录中，所以集合函数最常见的用法就是与GROUPBY子句配合使用，最重要的是集合函数不能同未分组的列混合使用。4.2操作日期和时间日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在秒级跟踪一个表的改变。日期和时间类型是DATETIME、DATE、TIMESTAMP、TIME和YEAR。这些的每一个都有合法值的一个范围，而“零”当你指定确实不合法的值时被使用。注意，MySQL允许你存储某个“不严格地”合法的日期值，例如1999-11-31，原因我们认为它是应用程序的责任来处理日期检查，而不是SQL服务器。为了使日期检查更“快”，MySQL仅检查月份在0-12的范围，天在0-31的范围。上述范围这样被定义是因为MySQL允许你在一个DATE或DATETIME列中存储日期，这里的天或月是零。这对存储你不知道准确的日期的一个生日的应用程序来说是极其有用的，在这种情况下，你简单地存储日期象1999-00-00或1999-01-00。（当然你不能期望从函数如DATE_SUB()或DATE_ADD()得到类似以这些日期的正确值）。4.2.1返回当前日期和时间通过函数GETDATE（），你可以获得当前的日期和时间。例如，CURDATE()返回当前日期CURRENT_DATE以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。4MySQL高级特性();+------------+|CURDATE()|+-----