Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

5.3.4.2 选择特定行

如前一节所示,检索整个表非常容易。只需从 SELECT 语句中省略 WHERE 子句。 但是,通常您不想看到整个表,特别是当它变得很大时。相反,您通常更感兴趣于回答特定的问题,在这种情况下,您指定了您想要的信息的某些约束。让我们以您宠物的问题来看一些选择查询。

您可以从表中选择特定的行。例如,如果您想验证对 Bowser 出生日期的更改,选择 Bowser 的记录,如下所示:

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

输出确认年份正确记录为 1989,而不是 1979。

字符串比较通常是大小写不敏感的,因此您可以指定名称为 'bowser''BOWSER' 等等。查询结果相同。

您可以在任何列上指定条件,不仅仅是 name。例如,如果您想知道哪些动物出生于 1998 年或之后,测试 birth 列:

Press CTRL+C to copy
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 | +----------+-------+---------+------+------------+-------+

您可以组合条件,例如,定位雌性狗:

Press CTRL+C to copy
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 运算符:

Press CTRL+C to copy
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 可以交叉使用,虽然 AND 的优先级高于 OR。如果您使用这两个运算符,最好使用括号来明确地指示条件应该如何分组:

Press CTRL+C to copy
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 | +-------+--------+---------+------+------------+-------+