最新MYSQL培训经典教程第四章.doc-淘文阁

资源描述

《最新MYSQL培训经典教程第四章.doc》由会员分享，可在线阅读，更多相关《最新MYSQL培训经典教程第四章.doc（179页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateMYSQL培训经典教程第四章MYSQL培训经典教程第四章第4章MYSQL高级特性本章要点：v 集合函数与时间函数v 字符串的模式匹配v 如何创建索引v 检索数据中的一些技巧第三章向你初步介绍了SQL。你学会了如何用SELECT语句进行查询，你还学会了如何建立自己的表以及如何录入数据等。在这一章里，你将加深你SQL语言知识。你将学习如何建立索引来加快查询速度。你还将学会

2、如果用更多的SQL语句和函数来操作表中的数据。尤其是对检索语句SELECT的使用，其中技巧繁多，可以说这是SQL语言中最重要的语句，也是使用者最常使用的语句。本章将详细介绍SELECT语句的用法。无论如何，这里的介绍都不能包括SQL语言的所有技巧，读者应该在平时的不断使用中积累经验。4.1 集合函数到现在为止，你只学习了如何根据特定的条件从表中取出一条或多条记录。但是，假如你想对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计，都需要使用集合函数。你可以统计记录数目，平均值，

3、最小值，最大值，或者求和。当你使用一个集合函数时，它只返回一个数，该数值代表这几个统计值之一。这些函数的最大特点就是经常和GROUP BY语句配合使用，需要注意的是集合函数不能和非分组的列混合使用。4.1.1 行列计数l 计算查询语句返回的记录行数直接计算函数COUNT(*)的值，例如，计算pet表中猫的只数：mysqlSELECT count(*) FROM pet WHERE species=cat;+-+| count(*) |+-+| 2 |+-+4.1.2统计字段值的数目例如，计算pet表中species列的数目：mysql SELECT count(species) FROM pe

4、t;+-+| count(species) |+-+| 9 |+-+如果相同的种类出现了不止一次，该种类将会被计算多次。如果你想知道种类为某个特定值的宠物有多少个，你可以使用WHERE子句，如下例所示： mysql SELECT COUNT(species) FROM pet WHERE species=cat ;注意这条语句的结果：+-+| COUNT(species) |+-+| 2 |+-+这个例子返回种类为cat的作者的数目。如果这个名字在表pet中出现了两次，则次函数的返回值是2。而且它和上面提到过的语句的结果是一致的：SELECT count(*) FROM pet WHERE

5、species=cat实际上，这两条语句是等价的。假如你想知道有多少不同种类的的宠物数目。你可以通过使用关键字DISTINCT来得到该数目。如下例所示： mysql SELECT COUNT(DISTINCT species) FROM pet;+-+| COUNT(DISTINCT species) |+-+| 5 |+-+如果种类cat出现了不止一次，它将只被计算一次。关键字DISTINCT 决定了只有互不相同的值才被计算。通常，当你使用COUNT（）时，字段中的空值将被忽略。另外，COUNT()函数通常和GROUP BY子句配合使用，例如可以这样返回每种宠物的数目：mysql SELE

6、CT species,count(*) FROM pet GROUP BY species;+-+-+| species | count(*) |+-+-+| bird | 2 | cat | 2 | dog | 3 | hamster | 1 | snake | 1 |+-+-+4.1.3 计算字段的平均值需要计算这些值的平均值。使用函数AVG（），你可以返回一个字段中所有值的平均值。假如你对你的站点进行一次较为复杂的民意调查。访问者可以在1到10之间投票，表示他们喜欢你站点的程度。你把投票结果保存在名为vote的INT型字段中。要计算你的用户投票的平均值，你需要使用函数AVG（）： SE

7、LECT AVG(vote) FROM opinion 这个SELECT语句的返回值代表用户对你站点的平均喜欢程度。函数AVG（）只能对数值型字段使用。这个函数在计算平均值时也忽略空值。再给出一个实际例子，例如我们要计算pet表中每种动物年龄的平均值，那么使用AVG()函数和GROUP BY子句：mysql SELECT species,AVG(CURDATE()-birth) FROM pet GROUP BY species;返回的结果为：+-+-+| species | AVG(CURDATE()-birth) |+-+-+| bird | 34160 | cat | 74959.5

8、| dog | 112829.66666667 | hamster | 19890 | snake | 49791 |+-+-+4.1.4 计算字段值的和假设你的站点被用来出售某种商品，已经运行了两个月，是该计算赚了多少钱的时候了。假设有一个名为orders的表用来记录所有访问者的定购信息。要计算所有定购量的总和，你可以使用函数SUM（）： SELECT SUM(purchase_amount) FROM orders 函数SUM（）的返回值代表字段purchase_amount中所有值的总和。字段purchase_amount的数据类型也许是DECIMAL类型，但你也可以对其它数值型字段使用

9、函数SUM（）。用一个不太恰当的例子说明，我们计算pet表中同种宠物的年龄的总和：mysql SELECT species,SUM(CURDATE()-birth) FROM pet GROUP BY species;你可以查看结果，与前一个例子对照：+-+-+| species | SUM(CURDATE()-birth) |+-+-+| bird | 68320 | cat | 149919 | dog | 338489 | hamster | 19890 | snake | 49791 |+-+-+4.1.5 计算字段值的极值求字段的极值，涉及两个函数MAX()和MIN()。例如，还是p

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

11、sql SELECT species,MIN(birth) FROM pet GROUP BY species;下面是令人满意的结果：+-+-+| 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 SELECT species,MAX(birth) FROM pet GR

12、OUP BY species; +-+-+| 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语言中非常常用的函数。这些函数之所以称之为集合函数，是因为它们应用在多条记录中，所以集合函数最常见的用法就是与GROUP BY子句配合使用，最重要的是集合函数不能同未分组的列混合使用。

13、4.2 操作日期和时间日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在秒级跟踪一个表的改变。日期和时间类型是DATETIME、DATE、TIMESTAMP、TIME和YEAR。这些的每一个都有合法值的一个范围，而“零”当你指定确实不合法的值时被使用。注意，MySQL允许你存储某个“不严格地”合法的日期值，例如1999-11-31，原因我们认为它是应用程序的责任来处理日期检查，而不是SQL服务器。为了使日期检查更“快”，MySQL仅检查月份在0-12的范围，天在0-31的范围。上述范围这样被定义是因为MySQL允许你在一个DA

14、TE或DATETIME列中存储日期，这里的天或月是零。这对存储你不知道准确的日期的一个生日的应用程序来说是极其有用的，在这种情况下，你简单地存储日期象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();+

15、-+| CURDATE() |+-+| 2001-02-20 |+-+mysql select CURDATE() + 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() 返回当前时期

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

17、-+| name | CURDATE()-birth |+-+-+| 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年，精度为一秒，其值作为数字显示。你可以使用它自动地用当前的日期和时间标记INSE

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

19、创建如下的表：mysql CREATE 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;+-+-+-+-+-+-+| id | name | engl

20、ish | 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 | history | time |+-+-+-+-+-+-+| 11

21、 | 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 | name | english | chinese | history |

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

23、可以把原有的值付给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 |+-+-+-+-+-+-+另一方面，你可能发现，当你想要实现上面这个效果时，很容易用一个你用NOW()初始化的DATETIM

24、E列然后不再改变它，这样也许直接些。但是，TIMESTAMP列的以后好处是存储要求比较小，节省空间。TIMESTAMP的存储需求是4字节，而DATETIME列的存储需求是8字节。4.2.3 返回日期和时间范围当你分析表中的数据时，你也许希望取出某个特定时间的数据。我们用下面一个表来模仿一个web站点的记录。mysql CREATE TABLE weblog- (- data float,- entrydate datetime- );然后随机的增加几个数据：mysql INSERT weblog VALUES(rand(),now();rand()函数返回一个随机的浮点值，now()函数返回

25、当前时间。多执行上面语句几次，得到一个作为测试的表。最为测试你还可以增加一个值：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 | 2001-02-08 00:00:00 | 0.437768 | 2001-02-08 13:57:06 | 0.327279 | 2001-02-08

26、 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 不要这样做。这个SELECT语句不会返回正确的记录它将只返回值为2000-02-08 00:00:00的记录，换句话说，只返回当天零点零时的记录。上面语句的结果为： +-+-+| da

27、ta | entrydate |+-+-+| 0.973723 | 2001-02-08 00:00:00 |+-+-+要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。1、使用关系运算符和逻辑运算符来限制时间范围例如，下面的这个SELECT 语句将能返回正确的记录： mysql SELECT * FROM weblog - WHEREentrydate=2001-02-08 AND entrydate SELECT * FROM weblog WHERE entrydate LIKE 2001-02-08% ;这个语句可以匹配正确的记录。因为通配符“”代表了任何时间。

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

29、OM weblog WHERE entrydate=2001-02-08;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运算符和模式匹配，是通过比较串值进行的，因此必须使用标准的时间书写格式，YYYY-MM-DD HH-MM

30、-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);+-+| TO_DAYS(1997-07-01) |+-+| 729571 |+-+例如：返回2个时间相差的天数(21世纪

31、已经过去了多少天)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模式匹配SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式

32、时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。例如，在表pet中，为了找出以“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”

展开阅读全文