MYSQL金典培训教程MySQL高级特性.doc-道客多多

资源描述

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

2、L 高高高级级级特特特性性性第第 4章章M ySQL 金典培训教程82 http:/4.1 集合函数到现在为止，你只学习了如何根据特定的条件从表中取出一条或多条记录。但是，假如你想对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计，都需要使用集合函数。你可以统计记录数目，平均值，最小值，最大值，或者求和。当你使用一个集合函数时，它只返回一个数，该数值代表这几个统计值之一。这些函数的最大特点就是经常和 GROUP BY 语句配合使用，需要注意的是集合函数不能和非

3、分组的列混合使用。4.1.1 行列计数计算查询语句返回的记录行数直接计算函数 COUNT(*)的值，例如，计算 pet 表中猫的只数：mysqlSELECT count(*) FROM pet WHERE species=cat;+-+| count(*) |+-+| 2 |+-+4.1.2 统计字段值的数目例如，计算 pet 表中 species 列的数目：mysql SELECT count(species) FROM pet;+-+| count(species) |+-+| 9 |+-+如果相同的种类出现了不止一次，该种类将会被计算多次。如果你想知道种类为某个特定值的宠物有多少个，你

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

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

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

7、的返回值代表用户对你站点的平均喜欢程度。函数 AVG（）只M ySQL 金典培训教程84 http:/能对数值型字段使用。这个函数在计算平均值时也忽略空值。再给出一个实际例子，例如我们要计算 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

8、| 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(

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

11、数同样可以与 GROUP BY 子句配合使用，例如，找出每种宠物中最早的出生日期：mysql 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()函数，如下例所示：

12、 mysql SELECT species,MAX(birth) FROM pet GROUP 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 |M ySQL 金典培训教程86 http:/+-+-+4.1.6 总结在本节中，介绍了一些典型的集合函数的用法，包括计数、均值、极值和总和，这些都是 SQL 语言中非常常用的函数。这些函数之所以称

13、之为集合函数，是因为它们应用在多条记录中，所以集合函数最常见的用法就是与 GROUP BY 子句配合使用，最重要的是集合函数不能同未分组的列混合使用。4.2 操作日期和时间日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在秒级跟踪一个表的改变。日期和时间类型是 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。这些的每一个都有合法值的一个范围，而“零”当你指定确实不合法的值时被使用。注意，MySQL 允许你存储某个“不严格地”合法的日期值，例如 1999-11-31，原因我们认为它是应用程序的责任来处理日

14、期检查，而不是 SQL 服务器。为了使日期检查更“快”，MySQL 仅检查月份在 0-12 的范围，天在 0-31 的范围。上述范围这样被定义是因为MySQL 允许你在一个 DATE 或 DATETIME 列中存储日期，这里的天或月是零。这对存储你不知道准确的日期的一个生日的应用程序来说是极其有用的，在这种情况下，你简单地存储日期象 1999-00-00 或 1999-01-00。（当然你不能期望从函数如 DATE_SUB()或 DATE_ADD()得到类似以这些日期的正确值）。4.2.1 返回当前日期和时间通过函数 GETDATE（），你可以获得当前的日期和时间。例如， CURDATE()

16、ect CURTIME();+-+| CURTIME() |+-+| 10:42:38 |+-+mysql select CURTIME() + 0;+-+| CURTIME()+0 |+-+| 104525 |+-+ NOW() 返回当前时期和时间NOW()以 YYYY-MM-DD HH:MM:SS 的格式或者 YYYYMMDDHHMMSS 的格式返回日期和时间值，取决于上下文。mysqlselect now();+-+| now() |+-+| 2001-02-20 10:45:57 |+-+mysqlselect now()+0;+-+| now()+0 |+-+| 2001022010

18、| 39011 | Slim | 49791 | Puffball | 19890 |+-+-+4.2.2 自动记录数据的改变时间TIMESTAMP 列类型提供一种类型，TIMESTAMP 值可以从 1970 的某时的开始一直到 2037 年，精度为一秒，其值作为数字显示。你可以使用它自动地用当前的日期和时间标记 INSERT 或 UPDATE 的操作。如果你有多个 TIMESTAMP 列，只有第一个自动更新。自动更新第一个 TIMESTAMP 列在下列任何条件下发生：列没有明确地在一个 INSERT 或 LOAD DATA INFILE 语句中指定。列没有明确地在一个 UPDATE 语

19、句中指定且一些另外的列改变值。（注意一个UPDATE 设置一个列为它已经有的值，这将不引起 TIMESTAMP 列被更新，因为如果你设置一个列为它当前的值，MySQL 为了效率而忽略更改。）你明确地设定 TIMESTAMP 列为 NULL. 除第一个以外的 TIMESTAMP 列也可以设置到当前的日期和时间，只要将列设为NULL，或 NOW()。例如，创建如下的表：mysql CREATE TABLE student- (- id int,- name char(16),- english tinyint,- chinese tinyint,- history tinyint,- time

20、 timestamp- );向表中插入记录，可以查看效果：mysql INSERT student(id,name,englisht,Chinese,history) VALUES(11,”Tom”,66,93,67);查看记录的存储情况：mysql SELECT * FROM student;4 MySQL 高级特性 http:/ 89+-+-+-+-+-+-+| id | name | english | chinese | history | time |+-+-+-+-+-+-+| 11 | Tom | 66 | 93 | 67 | 20010220123335 |+-+-+-+-+-

21、+-+你可以看到 time 列纪录下了数据录入时的时间值。如果你更新改记录，在查看操作的结果：mysql UPDATE student SET english=76 WHERE id=11;mysql SELECT * FROM student;+-+-+-+-+-+-+| id | name | english | chinese | history | time |+-+-+-+-+-+-+| 11 | Tom | 76 | 93 | 67 | 20010220125736 |+-+-+-+-+-+-+可以清楚的看到，time 列的时间被自动更改为修改记录的时间。有时候你希望不更改任何值，

22、也能打到修改 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 | time |+-+-+-+-+-+-+| 11 | Tom | 76 | 93 | 67 | 20010220130517 |+-+-+-+-+-+-+通过明确地设置希望的值，你可

23、以设置任何 TIMESTAMP 列为不同于当前日期和时间的值，即使对第一个 TIMESTAMP 列也是这样。例如，如果，当你创建一个行时，你想要一个 TIMESTAMP 被设置到当前的日期和时间，但在以后无论何时行被更新时都不改变，你可以使用这样使用：让 MySQL 在行被创建时设置列，这将初始化它为当前的日期和时间。当你执行随后的对该行中其他列的更改时，明确设定 TIMESTAMP 列为它的当前值。例如，当你在修改列时，可以把原有的值付给 TIMESTAMP 列：mysql UPDATE student SET english=66,time=time WHERE id=11;mysq

24、l select * from student where id=11;+-+-+-+-+-+-+| id | name | english | chinese | history | time |M ySQL 金典培训教程90 http:/+-+-+-+-+-+-+| 11 | Tom | 66 | 93 | 67 | 20010220130517 |+-+-+-+-+-+-+另一方面，你可能发现，当你想要实现上面这个效果时，很容易用一个你用 NOW()初始化的 DATETIME 列然后不再改变它，这样也许直接些。但是，TIMESTAMP 列的以后好处是存储要求比较小，节省空间。

25、TIMESTAMP 的存储需求是 4 字节，而DATETIME 列的存储需求是 8 字节。4.2.3 返回日期和时间范围当你分析表中的数据时，你也许希望取出某个特定时间的数据。我们用下面一个表来模仿一个 web 站点的记录。mysql CREATE TABLE weblog- (- data float,- entrydate datetime- );然后随机的增加几个数据：mysql INSERT weblog VALUES(rand(),now();rand()函数返回一个随机的浮点值，now()函数返回当前时间。多执行上面语句几次，得到一个作为测试的表。最为测试你还可以增加一个值：mys

26、ql 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 13:57:09 | 0.0931809 | 2001-02-08 1

27、3: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-4 MySQL 高级特性 http:/ 9108 00:00:00 的记录，换句话说，只返回当天零点零时的记录。上面语句的结果为： +-+-+| da

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

29、何时间。 +-+-+| 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 |+-+-+M ySQL 金典培训教程92 http:/3、上面两种方法的异同由于使用关系运算符进行的是比较过程，时转换成内部的存储格式后进行的，因此，因此时间的书写可以不是那么严格要求。

30、例如，下面几种写法是等价的：mysql SELECT * FROM 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 运算符和模式匹配，

31、是通过比较串值进行的，因此必须使用标准的时间书写格式，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);+-+| TO_DAYS(1

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

34、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 | 19

35、89-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 | 19

36、97-12-09 | NULL |+-+-+-+-+-+-+M ySQL 金典培训教程94 http:/为了找出包含正好 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 |+-+

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

38、匹配的任何数量的数字，而“.*”匹配任何数量的任何东西。正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，“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 | 1

展开阅读全文