检索数据

    what_to_select 表示你想看到的数据，可以是具体列，也可以是*(代表所有列)。

    which_table 表示你要从哪个表中检索数据。

    WHERE 是可选的，如果有的话，conditions_to_satisfy表示指定一个或多个行应该满足的条件。

    查询所有数据

    2. +----------+--------+---------+------+------------+------------+
    3. | name     | owner  | species | sex  | birth      | death      |
    4. +----------+--------+---------+------+------------+------------+
    5. | Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
    6. | Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
    7. | Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
    8. | Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
    9. | Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
    10. | Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
    11. | Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
    12. | Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
    13. | Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
    14. +----------+--------+---------+------+------------+------------+
    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. +--------+-------+---------+------+------------+------------+
    1. mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
    2. +----------+-------+---------+------+------------+-------+
    3. | name     | owner | species | sex  | birth      | death |
    4. +----------+-------+---------+------+------------+-------+
    5. | Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
    6. | Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
    7. +----------+-------+---------+------+------------+-------+
    1. mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
    2. +----------+-------+---------+------+------------+-------+
    3. | name     | owner | species | sex  | birth      | death |
    4. +----------+-------+---------+------+------------+-------+
    5. | Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
    6. | Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
    7. | Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |

    查询指定列

    2.     -> OR (species = 'dog' AND sex = 'f');
    3. +-------+--------+---------+------+------------+-------+
    4. | name  | owner  | species | sex  | birth      | death |
    5. +-------+--------+---------+------+------------+-------+
    6. | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
    7. | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
    8. +-------+--------+---------+------+------------+-------+

    查询宠物表中的宠物拥有者（不重复）。使用DISTINCT关键詞：

    1. mysql> SELECT DISTINCT owner FROM pet;
    2. +--------+
    3. | owner  |
    4. +--------+
    5. | Benny  |
    6. | Diane  |
    7. | Gwen   |
    8. | Harold |
    9. +--------+
    1. mysql> SELECT name, species, birth FROM pet
    2.     -> WHERE species = 'dog' OR species = 'cat';
    3. +--------+---------+------------+
    4. | name   | species | birth      |
    5. +--------+---------+------------+
    6. | Fluffy | cat     | 1993-02-04 |
    7. | Claws  | cat     | 1994-03-17 |
    8. | Buffy  | dog     | 1989-05-13 |
    9. | Fang   | dog     | 1990-08-27 |
    10. | Bowser | dog     | 1989-08-31 |
    11. +--------+---------+------------+

    行排序

    1. mysql> SELECT name, species, birth FROM pet
    2.     -> WHERE species = 'dog' OR species = 'cat';
    3. +--------+---------+------------+
    4. | name   | species | birth      |
    5. +--------+---------+------------+
    6. | Fluffy | cat     | 1993-02-04 |
    8. | Fang   | dog     | 1990-08-27 |
    9. | Bowser | dog     | 1989-08-31 |
    10. +--------+---------+------------+

    默认的排序是升序（ASC），以下是按降序排列：

    使用TIMESTAMPDIFF查询宠物年龄：

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

    查询death不为NULL，按年龄升序排列：

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

    查询使用death IS NOT NULL，而不是death <> NULL,因为NULL是一个特殊的值，不能使用常规的方法来比较。

    查询出年日期是5月份的宠物：

    1. mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
    2. +-------+------------+
    3. | name  | birth      |
    4. +-------+------------+
    5. | Buffy | 1989-05-13 |
    6. +-------+------------+