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

    您只能从表中选择特定行。例如,如果你想验证改变你对Bowser的出生日期,选择Bowser这样的记录:

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

    字符串比较通常是不区分大小写的,所以你可以指定名称为“bowser”、“BOWSER”,等等。查询结果是相同的。


    您可以在任何列上指定条件,而不只是名称。例如,如果你想知道哪些动物在1998或之后出生,就测试出生列:

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

    使用in来进行定位多个值

    mysql> select * from pet where birth in ('1993-02-04','1994-03-17');
    +--------+--------+---------+------+------------+-------+
    | name   | owner  | species | sex  | birth      | death |
    +--------+--------+---------+------+------------+-------+
    | Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
    | Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL  |
    +--------+--------+---------+------+------------+-------+
    2 rows in set (0.00 sec)
    

    例如,你可以复合条件来定位雌性狗:

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

    and与or可能混杂,虽然and具有比or高优先级。如果使用这两个操作符,使用括号显式地说明如何组合条件是个好主意:

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