MySQL参考手册译文【中篇】

3、创建和使用数据库

学会了如何键入SQL语句,你准备好开始学习进入数据可了咩?

假设在你的家中(动物园)养了几只宠物,你希望将他们成长的过程中产生的各种各样的信息作为足迹保存下来。你可以创建一个表格存放他们的数据,也可以当你想要获取他们的信息的时候能随时获取。请跟随以下步骤完成你的学习任务:

  • 创建一个数据库
  • 创建一张表
  • 将信息存入表中
  • 用各种各样的方法检索表中的数据
  • 使用多张表

这个动物园的数据库创建起来很简单(有意为之),但是也许在实际的动物园管理中创建类似的数据库也不会太难。举个栗子,一位农场主希望用数据库记录牲畜的各种信息,或者一名兽医想要观察记录生病的动物的情况。

使用 SHOW 命令查找在当前服务中存在的所有数据库:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql      |
| test          |
| tmp         |
+----------+

名为 mysql 的数据库记录了用户进入的权限。名为 text 的数据库通常作为可使用的工作区或者开发者用来测试的地方。

列出数据库的语句在你的机器上也许有所不同,如果你没有权限使用 SHOW DATABASES 命令那么这条语句不会返回任何内容。获取帮助前往 Section 13.7.5.14, “SHOW DATABASES Syntax”.

如果 test 数据库存在,尝试进入该数据库:

mysql> USE test
Database changed

USE 命令和 QUIT 命令一样,都不需要分号作为结尾。(当然你也可以在其后写上分号,也不会有什么危害)。 USE 是一个特殊的语句,它必须在单行上书写。

你可以使用 test数据库做以下的训练,但是你创建的任何数据都有可能被能进入该数据库的开发者删除,所以你有必要询问你的MySQL管理员,征求一个属于自己的数据库,如果你想创建自己的 menagerie ,管理员需要执行类似以下的命令:

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

3.1、创建和选择数据库

如果管理员创建了仅有你可以使用的数据库,你可以开始使用它。否则你需要亲自创建:

MySQL数据库参考手册中文

在Unix系统之下,数据库名称是大小写敏感的(SQL语句是不敏感的),所以你必须总是输入 menagerie,而 MenageriaMENAGERIE或其他变量名均不可以。除了数据库名,创建表名也要注意大小写敏感这件事,因此请保持良好的命名习惯,注意区分大小写。

创建一个数据库而不改变为当前数据库。你必须明确这件事,让 menageria 变成当前数据库,使用这个语句:

MySQL数据库参考手册中文

你的数据库只需要创建一次,但是你必须在每次开始一次mysql会话之前先选择一个数据库。你可以使用 USE 语句转换数据库。或者你也可以在进入mysql的同时告诉mysql你想使用哪一个数据库。只需要在连接参数后制定数据库名称。举个例子:

shell> mysql -h host -u user -p menagerie
Enter password: ********

3.2、创建一张表

创建数据库是比较普通的部分,但是目前为止还是空空如也,不信可以 SHOW TABLES 语句:

MySQL数据库参考手册中文

最艰难的部分是决定数据库的表结构应该是怎么样的:需要哪些表,这些表都有那些列标签。

你想要创建一个记录每一个宠物的表。所以这个表可以叫做 pet,至少需要包含每一个动物的名字。因为表中仅仅存在名字信息显得太无趣,这张表还应当包含信息。举个例子,如果你的家中有多个照顾宠物的人,那么还需要记录宠物的饲养人。你也许还想要记录一些基础的描述信息,比如物种和性别。

还有年龄的记录呢?那样事情就有趣多了,但是将他存放在数据库中并不是很好,因为年龄会随着时间的推移而发生变化,也就是说我们还去要经常的更新表中宠物的年龄的数据,因此如果能将动物的出生日期作为固定值保存下来会更好些。随后,无论何时你需要宠物的年龄数据,你可以通过当前时间和宠物的出生日期计算出宠物的年龄,MySQL提供了操作日期的算法,所以实现它并不十分困难。保存出生日期而不保存年龄还有其他的优势:

  • 你可以使用数据库来提醒你宠物的生日(也许你会觉得提醒动物的生日这件事有些愚蠢,但是别忘了,这只是一个例子,也许在其他使用场景中,你确实会需要数据库来提醒公司里大家的生日信息)。
  • 你可以通过和当前日期作比较来计算年龄。举个例子,如果你存储了死亡日期在数据库了,你可以很容易的计算出宠物去世时的年龄。

你也许还想在 pet 表中存入其他类型的信息。但是目前这些字段足够了:nameownerspeciessexbirthdeath

使用 CREATE TABLE 语句去定制表的布局:

MySQL数据库参考手册中文

VARCHAR 数据类型对与name,ownerspecies来说是最合适不过的了。因为这些字段的长度的不固定的可变的。这些字段的长度不是也不相同,也不能固定为20。你可以保存1到65535长度的长度的数据,这取决于你自己。如果你无法现在做出抉择(抉择数据的长度),你可以使用MySQL提供的 ALTER TABLE 语句来改变字段信息。

有几种类型的值可以代表宠物的性别,比如说mf,或者malefemale。最简单的是使用mf来记录宠物的性别。

death字段的数据类型毋庸置疑的为DATE

一旦你创建一张表,SHOW TABLES 会输出所有表:

MySQL数据库参考手册中文

为了能验证你创建的表是期待的样子,可以使用 DESCRIBE 语句:

MySQL数据库参考手册中文

你可以随时使用 DESCRIBE 语句。举个例子,如果你忘记了表中列的名字或者字段的数据类型你可以通过这种方法查看表结构。

3.3、向表中添加数据

创建完一张表之后,你需要向其中添加数据,LOAD DATAINSERT 语句是很实用的命令。

假设你的宠物记录如下:

nameownerspeciessexbirthdeath
FluffyHaroldcatf1993-02-04 
ClawsGwencatm1994-03-17 
BuffyHarolddogf1989-05-13 
FangBennydogm1990-08-27 
BowserDianedogm1979-08-311995-07-29
ChirpyGwenbirdf1998-09-11 
WhistlerGwenbird 1997-12-09 
SlimBennysnakem1996-04-29 

你可以创建一个叫做 pet.txt 的文本文件,这个文本文件包含一条宠物信息的记录。使用制表符(TAB键)将值分隔开,值的顺序就是 CREATE TABLE 时字段的顺序,如果要忽略某个值(比如不知道宠物的的性别或者去世日期),你可以使用NULL值,在文本文件中,使用 \N (反斜杠加一个大写的N),举个例子,一直鸟的信息看起来就像这个样子(空白处是一个TAG符):

Whistler        Gwen    bird    \N      1997-12-09      \N

将文本文件的一条数据插入到pet表中,使用以下语句:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

如果你在window创建的文件,则是以 \r\n 作为一行的结束,你应该使用如下的语句:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';

(在运行OS X系统的苹果电脑中,你应该使用 LINES TERMINATED BY ‘\r’)。

如果你想,你可以指定列值的分割符,明确指定 LOAD DATA 的结束标志,但是默认的使用TAB符和换行符来分割。

如果遇到问题,获取帮助前往Section 6.1.6, “Security Issues with LOAD DATA LOCAL”

当你想要插入数据时, INSERT 语句是很实用的。

MySQL数据库参考手册中文

3.4、检索数据

SELECT 语句通常用于从表中拉数据,通常的语句是:

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

what_to_select 代表你想要查询的字段,这可以是列标签或者是 * 通配符(全部列)。which_table 代表你想要从那个表中获取数据。WHERE 子句是可选的,如果出现了该子句,conditions_to_satisfy 表示一个或更多个检索数据的条件。

选取所有的数据

这是最简单的使用SELECT从表中检索数据的方法。

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name       | owner  | species  | sex     | birth          | death         |
+----------+--------+---------+------+------------+------------+
| Fluffy       | Harold | cat         | f         | 1993-02-04 | NULL       |
| Claws      | Gwen   | cat          | m      | 1994-03-17 | NULL       |
| Buffy       | Harold | dog        | f        | 1989-05-13 | NULL       |
| Fang       | Benny  | dog         | m     | 1990-08-27 | NULL         |
| Bowser   | Diane   | dog        | m      | 1979-08-31 | 1995-07-29 |
| Chirpy    | Gwen   | bird         | f       | 1998-09-11 | NULL         |
| Whistler | Gwen   | bird         | NULL| 1997-12-09 | NULL       |
| Slim        | Benny  | snake     | m      | 1996-04-29 | NULL       |
| Puffball   | Diane  | hamster | f        | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

如果你想要浏览全部数据,是有此方法很实用,有时候你也许会觉得有些数据不准确,你发现正确的生日日期应该是1989而不是1979年。

这有至少两种方法修正这一错误:

  • 编辑 pet.txt 文件修正错误,然后清空该表,最后使用 DELETELOADDATA 重新导入数据。
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
  • 仅仅修复错误的记录,你可以使用 UPDATE 语句。
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

选取特殊的行

正如上一张介绍的,检索表的全部内容很容易。忽略了 SELECT 语句的 WHERE 子句。但是也许你不想要检索到整张表的数据

mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name    | owner | species  | sex    | birth          | death         |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog        | m      | 1989-08-31| 1995-07-29|
+--------+-------+---------+------+------------+------------+

还可以选取一个范围

mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name       | owner | species  | sex    | birth           | death |
+----------+-------+---------+------+------------+-------+
| Chirpy     | Gwen  | bird        | f        |1998-09-11 | NULL |
| Puffball   | Diane  | hamster | f        |1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+

你还可以联合多个条件:

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species   | sex    | birth           | death |
+-------+--------+---------+------+------------+-------+
| Buffy   | Harold | dog        | f        | 1989-05-13| NULL  |
+-------+--------+---------+------+------------+-------+

除了使用 AND 表示与,还可以使用 OR 表示或。

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name      | owner  | species  | sex    | birth           | death |
+----------+-------+---------+------+------------+-------+
| Chirpy     | Gwen  | bird       | f         | 1998-09-11 | NULL |
| Whistler  | Gwen  | bird       | NULL  | 1997-12-09 | NULL |
| Slim        | Benny  | snake    | m       | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+

ANDOR 可以混合使用,尽管 ANDOR 拥有更高的优先级。如果你需要两种操作符,使用括号运算符准确的包裹条件将其分组是一个不错的选择。

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
    -> OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species   | sex    | birth           | death |
+-------+--------+---------+------+------------+-------+
| Claws  | Gwen   | cat          | m      |1994-03-17 | NULL  |
| Buffy   | Harold | dog        | f        |1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

选取指定的列(字段)

如果你不想查询表的全部行,而是只关心感兴趣的列,使用 逗号 将字段分隔开,举个例子,如果你想要知道你的动物们是何时出生的,选择 namebirth 列即可:

mysql> SELECT name, birth FROM pet;
+----------+------------+
| name       | birth          |
+----------+------------+
| Fluffy       | 1993-02-04 |
| Claws       | 1994-03-17 |
| Buffy        | 1989-05-13 |
| Fang        | 1990-08-27 |
| Bowser    | 1989-08-31 |
| Chirpy      | 1998-09-11 |
| Whistler   | 1997-12-09 |
| Slim         | 1996-04-29 |
| Puffball    | 1999-03-30 |
+----------+------------+

如果你只关心宠物的拥有者,可以这样查询:

mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

值得注意的是,查询结果返回了每一条记录的 owner(宠物的饲养员) 字段,你可以看到,这些饲养员出现多次,为了去除重复的查询结果,压缩查询结果,只需要添加 DISTINCT 关键字便可以只出现一次。

mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane   |
| Gwen   |
| Harold |
+--------+

你还可使用 ** WHERE** 子句联合行与列的查询,举个例子,只获取 狗和猫 的出生日期、姓名和物种:

mysql> SELECT name, species, birth FROM pet
    -> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name    | species  | birth           |
+--------+---------+------------+
| Fluffy    | cat         | 1993-02-04 |
| Claws    | cat         | 1994-03-17 |
| Buffy     | dog       | 1989-05-13 |
| Fang     | dog        | 1990-08-27 |
| Bowser | dog        | 1989-08-31 |
+--------+---------+------------+

将查询结果排序

将动物们的出生日期信息按日期排序:

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name       | birth          |
+----------+------------+
| Buffy        | 1989-05-13 |
| Bowser    | 1989-08-31 |
| Fang        | 1990-08-27 |
| Fluffy       | 1993-02-04 |
| Claws       | 1994-03-17 |
| Slim         | 1996-04-29 |
| Whistler   | 1997-12-09 |
| Chirpy      | 1998-09-11 |
| Puffball    | 1999-03-30 |
+----------+------------+

默认的排序为正向排序,也就是最小的值在前,为了使用逆向排序,只需添加 DESC 关键字:

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name       | birth           |
+----------+------------+
| Puffball    | 1999-03-30 |
| Chirpy      | 1998-09-11 |
| Whistler   | 1997-12-09 |
| Slim         | 1996-04-29 |
| Claws       | 1994-03-17 |
| Fluffy       | 1993-02-04 |
| Fang        | 1990-08-27 |
| Bowser    | 1989-08-31 |
| Buffy       | 1989-05-13 |
+----------+------------+

还可以按多个字段排序,如此一来,当第一个排序有重复,则会按第二个排序:

mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;
+----------+---------+------------+
| name       | species  | birth          |
+----------+---------+------------+
| Chirpy     | bird       | 1998-09-11 |
| Whistler  | bird       | 1997-12-09 |
| Claws      | cat         | 1994-03-17 |
| Fluffy      | cat         | 1993-02-04 |
| Fang       | dog        | 1990-08-27 |
| Bowser   | dog        | 1989-08-31 |
| Buffy       | dog       | 1989-05-13 |
| Puffball   | hamster | 1999-03-30 |
| Slim        | snake     | 1996-04-29 |
+----------+---------+------------+

可以发现,DESC (逆向排序) 只对 birth字段起作用,对 species 仍使用正向排序。

计算日期

MySQL提供了一些计算日期的功能函数,举个例子,计算年龄或者日期的额外部分。

为了确定你的宠物的年龄,使用 TIMESTAMPDIFF() 函数。该函数有三个参数,第一个为计算结果的单位,另外两个参数为两个日期。

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet;
+----------+------------+------------+------+
| name       | birth           | CURDATE() | age  |
+----------+------------+------------+------+
| Fluffy       | 1993-02-04 | 2003-08-19 |  10 |
| Claws       | 1994-03-17 | 2003-08-19 |    9 |
| Buffy        | 1989-05-13 | 2003-08-19 |  14 |
| Fang        | 1990-08-27 | 2003-08-19 |  12 |
| Bowser    | 1989-08-31 | 2003-08-19 |  13 |
| Chirpy     | 1998-09-11 | 2003-08-19 |    4 |
| Whistler  | 1997-12-09 | 2003-08-19 |    5 |
| Slim        | 1996-04-29 | 2003-08-19 |    7 |
| Puffball   | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

如果将查询结果进行排序,将提升查询结果的可读性。如下,将按宠物名将查询结果进行排序。

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet ORDER BY name;
+----------+------------+------------+------+
| name       | birth           | CURDATE()| age   |
+----------+------------+------------+------+
| Bowser    | 1989-08-31 | 2003-08-19 |   13 |
| Buffy        | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy      | 1998-09-11 | 2003-08-19 |    4 |
| Claws       | 1994-03-17 | 2003-08-19 |    9 |
| Fang        | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy       | 1993-02-04 | 2003-08-19 |   10 |
| Puffball    | 1999-03-30 | 2003-08-19 |    4 |
| Slim         | 1996-04-29 | 2003-08-19 |    7 |
| Whistler  | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

如果想要将查询结果按年龄排序,只需要将 name 替换成 age

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet ORDER BY age;
+----------+------------+------------+------+
| name       | birth           | CURDATE() | age  |
+----------+------------+------------+------+
| Chirpy     | 1998-09-11 | 2003-08-19 |    4 |
| Puffball    | 1999-03-30 | 2003-08-19 |    4 |
| Whistler   | 1997-12-09 | 2003-08-19 |    5 |
| Slim         | 1996-04-29 | 2003-08-19 |    7 |
| Claws       | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy        | 1993-02-04 | 2003-08-19 |   10 |
| Fang         | 1990-08-27 | 2003-08-19 |   12 |
| Bowser     | 1989-08-31 | 2003-08-19 |   13 |
| Buffy        | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

如果想查询已经去世的宠物的信息,只需要将死亡日期不为NULL的列表查询出来即可。

mysql> SELECT name, birth, death,
    -> TIMESTAMPDIFF(YEAR,birth,death) AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name    | birth          | death          | age   |
+--------+------------+------------+------+
| Bowser |1989-08-31| 1995-07-29 |    5    |
+--------+------------+------------+------+

细心的你也许已经注意到了,查询去世日期使用的是 ISNOT NULL 而不是 <> NULL 因为 NULL是一个特殊的值,不能使用普通的操作符来比较 NULL

如果你想要知道哪一个动物下一个月生日?MySQL提供了几个函数处理日期。比如 YEAR(), MONTH()DAYOFMONTH().MONTH()

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 |
+----------+------------+--------------+

假设现在是四月,你想要查询5月出生的动物:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name   | birth          |
+-------+------------+
| Buffy   | 1989-05-13|
+-------+------------+

如果当前的月份是12月则会出现一个小小的麻烦,你不能单纯的在12月加上1来获取13月生日的动物。因为没有13月,而是应该查询1月生日的动物。

那如何才能不需要关心当前月份而获取下一个月份的数字呢? DATE_ADD() 可以将你给定的月份的基础上增加一个月,如果你使用 CURDATE() 来获取当前月份并加1:

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

完成这项任务的另一种方法:

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

NULL值

NULL值是一个神奇的值,理论上讲,NULL值是指a missing unknown value,NULL值和其他所有值都不相等。

为了测试NULL值的用法,使用 IS NULLIS NOT NULL 操作符:

MySQL数据库参考手册中文

你不能使用像 = , < 或者 <> 的算数比较运算符。为了证明这一点,请看如下例子:

MySQL数据库参考手册中文

因为所有和NULL进行算数比较的结果还是NULL,你不可能获取任何有意义的结果。

在MySQL中,0 和 NULL 意味着 false。

MySQL数据库参考手册中文

模式匹配

MySQL提供标准的SQL模式匹配,其基于正则表达式。

SQL模式匹配允许你使用 _ 匹配任何单个字符串,使用 % 匹配任意数字(包括0字符)。在MySQL中,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的另一种类型的模式匹配是借鉴了正则表达式。当你想要测试这种类型的模式时,使用 REGEXPNOT REGEXP 操作符(或者 RLIKENOT RLIKE,他们都是同意义的。)

下方的列表描述了一些继承自正则表达式的字符:

  • . 匹配任意的单个字符。
  • […] 表示匹配中括号中的任何字符。举个例子,**[abc]**匹配a,b或者c。如果想要匹配一个字符范围,使用破折号即可,比如 [a-z] 匹配任一字母,同理 [0-9] 匹配任一数字。
  • * 通配符匹配0次或多次,举个例子, x* 匹配任意个数的的 x ,[0-9]匹配任意个数的任意数字。. 匹配任意个数的任何符号。
  • REGEXP 模式匹配如果能检测到任何位置则匹配成功。(与 LIKE 不同的是,LIKE 只有整个字符串符合匹配要求才会成功。)
  • 为了能匹配待匹配字符串的开头或结尾,可是使用 ^ 匹配开始或 $ 匹配结束。

为了证明MYSQL扩展的正则表达式是如何起作用的,前面使用 LIKE 查询出的结果这里将使用 REGEXP 重写:

为了找到所有 names 是以 b 开头的 记录,使用 ^ 来匹配 name 的开始:

mysql> SELECT * FROM pet WHERE name REGEXP '^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 |
+--------+--------+---------+------+------------+------------+

如果你真的想要让匹配度变得大小写敏感,需要使用 BINARY 关键字来让普通字符串变成二进制字符串。这次只匹配 name 以小写的 b 开头的记录:

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

找到所有 names 是以 fy 结尾的记录,使用 $ 匹配所有 name:

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 的记录,可以这样写查询语句:

mysql> SELECT * FROM pet WHERE name REGEXP '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       |
+----------+-------+---------+------+------------+------------+

如果你使用SQL模式,在正则表达式的两边加上通配符(表示匹配前后所有字符)是没有必要的,因为正则表达式将会智能的从任何位置开始匹配待匹配字符串。

为了找到 names 仅仅包含5个字符的全部记录,使用 ^$ 匹配 name 的开始和结束,然后将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  |
+-------+--------+---------+------+------------+-------+

获取更多关于正则表达式的帮助前往Section 12.5.2, “Regular Expressions”,这里提供了关于正则表达式的更多信息。

查询到了多少行记录(计数)

数据库经常需要回答类似这样的问题,“某种确定类型的数据在表中存在多少个?”举个例子,你也许想要知道你有多少宠物。或者每一位饲养员各有多少宠物。或者你想要对你的全部动物们进行各种各样的普查。

获得全部动物的总个数这个问题和“pet这个表中一共有多少条记录?”的问题本质上相同。因为每一个动物对应一条记录。COUNT(*) 函数能对所有行进行计数。因此查询动物的个数的SQL语句就像这样:

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 的记录分组。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      |
+---------+------+----------+

如果你希望将选中列在某一条件下使用 COUNT() 进行计数,则 GROUP BY 子句必须必须出现前面出现过的字段名。否则,将会发生下面的情况:

  • 如果 ONLY_FULL_GROUP_BY SQL模式是激活状态,则会报一个错误:
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by

如果 ONLY_FULL_GROUP_BY 是未被激活状态,则mysql对查询语句的处理方式会将全部行看作一个单一的组,但是不能确定更详尽的计数信息。

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        8       |
+--------+----------+
1 row in set (0.00 sec)

获取更多关于 GROUP BY的处理行为 的帮助前往 Section 12.19.3, “MySQL Handling of GROUP BY”.

访问Section 12.19.1, “Aggregate (GROUP BY) Function Descriptions” ,获取更多关于 COUNT(expr) 行为的最优优化方式的帮助。

使用更多的表

pet 表记录了你拥有的全部宠物,如果你想要记录关于他们的其他信息,比如他们看兽医或者生小宝宝的历史性事件信息。你需要另外的一个表,那么这个表应该是什么样子的呢?他至少应当包含如下信息:

  • 宠物姓名,这样你才能知道这些事件属于哪一个动物的。
  • 日期,你可以知道这些事件发生的时间。
  • 还需要一个字段详细描述这一事件。
  • 还需要事件类型的字段,如果你想要将事件进行分类的话。

有了以上的考量,使用 CREATE TABLE 语句创建 event 表,它看起来就像这样:

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

和之前的 pet 表类似,使用包含所有记录的文本文件来初始化填充新创建的表是很容易的。这些信息看起来就像这样。

namedatetyperemark
Fluffy1995-05-15litter4 kittens, 3 female, 1 male
Buffy1993-06-23litter5 puppies, 2 female, 3 male
Buffy1994-06-19litter3 puppies, 3 female
Chirpy1999-03-21vetneeded beak straightened
Slim1997-08-03vetbroken rib
Bowser1991-10-12kennel 
Fang1991-10-12kennel 
Fang1998-08-28birthdayGave him a new chew toy
Claws1998-03-17birthdayGave him a new flea collar
Whistler1998-12-09birthdayFirst birthday

将这些信息一并填充到表中:

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

基于你之前创建 pet表的时候习得的知识,你可能需要检索在 *event 表中的记录,原理都是一样的。但是你也许想知道创建这样一个表何时才会用到?

假设你想要查询每一个生过小宝宝的宠物的小宝宝出生起他的年龄。我们早前知道了如何通过两个日期计算年龄,妈妈降生小宝宝时的日期存放在 event 表中,我们还需要知道妈妈的出生日期,便可以知道降生小宝宝时妈妈的年龄了,这一生日数据存在 pet 表中,也就是说我们需要两个表的数据:

mysql> SELECT pet.name,
    -> TIMESTAMPDIFF(YEAR,birth,date) AS age,
    -> remark
    -> FROM pet INNER JOIN event
    ->   ON pet.name = event.name
    -> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name    | age    | remark                               |
+--------+------+-----------------------------+
| Fluffy    |    2     | 4 kittens, 3 female, 1 male |
| Buffy     |    4    | 5 puppies, 2 female, 3 male |
| Buffy     |    5    | 3 puppies, 3 female            |
+--------+------+-----------------------------+

有几点需要注意的查询事项:

  • FORM 子句将两个表联合起来,因为需要同时从两个表中拉取数据。
  • 当从多个表中联合数据,你需要指明两个表中的记录如何一一对应。这很容易,因为他们都有 name 字段。这个查询使用 ON 子句,使得两个表中都存在 name 字段的记录很容易一一对应。查询语句中还是用了 INNER JOIN 来联合两个表,这一关键字表示当且仅当两个表中同时满足包含 name 字段时,这一条记录才符合条件。在这个例子中, ON 子句指明 name 字段同时存在于 pet 表和 event 表中。如果 name 字段仅仅出现在一个表中而没有出现在另一个表中,则这一条将不会出现在查询结果当中,因为不满足 ON 子句的条件。
  • 因为 name 字段同时存在于两个表中,因此当你使用 name 字段值的时候你必须指明是哪一个表中的 name 字段值。比如是 pet.name 还是 event.name

你不需要将两个完全不相同的表联合起来。有些时候一个表联合他自己是很实用的,如果你想要比较一张表中的一组条记录和另一组记录。举个例子,你想要查询 pet 表中一对可以繁殖下一代的宠物,你可以通过如下方法产生出一个雌性与雄性的候选:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1 INNER JOIN pet AS p2
    ->   ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+--------+------+--------+------+---------+
| name    | sex    | name    | sex    | species |
+--------+------+--------+------+---------+
| Fluffy     | f        | Claws   | m      | cat        |
| Buffy     | f         | Fang    | m      | dog       |
| Buffy     | f         | Bowser | m     | dog       |
+--------+------+--------+------+---------+

在这次查询中,我们给表的名字定义了两个别名分别为 p1p2

下一篇《MySQL参考手册译文【下篇】》

上一篇《《乌合之众:大众心理研究》读书便笺》

永久链接 http://www.shuaihua.cc/article/mysql-reference-manual-translate-part-two

快速跳转 心头好文 - storage - 《MySQL参考手册译文【中篇】》

发布日期 2017年11月16日 星期四

版权声明 自由转载-非商用-非衍生-保持署名(创意共享3.0许可证