• 检索数据

    检索数据

    SELECT语句用于从表中检索数据，通常格式如下：

    1. SELECT what_to_select
    2. FROM which_table
    3. WHERE conditions_to_satisfy;

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

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

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

    查询所有数据

    1. mysql> SELECT * FROM pet;
    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  |
    8. +----------+-------+---------+------+------------+-------+
    1. mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
    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. +-------+--------+---------+------+------------+-------+

    查询指定列

    1. mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
    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 |
    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. +--------+---------+------------+

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

    1. mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
    2. +----------+------------+
    3. | name     | birth      |
    4. +----------+------------+
    5. | Puffball | 1999-03-30 |
    6. | Chirpy   | 1998-09-11 |
    7. | Whistler | 1997-12-09 |
    8. | Slim     | 1996-04-29 |
    9. | Claws    | 1994-03-17 |
    10. | Fluffy   | 1993-02-04 |
    11. | Fang     | 1990-08-27 |
    12. | Bowser   | 1989-08-31 |
    13. | Buffy    | 1989-05-13 |
    14. +----------+------------+

    日期计算

    使用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. +-------+------------+

    查询下一个月过生日的宠物名称和生日，其中DATE_ADD表示日期加，在这里表示，在当前日期上再加一个月的间隔，也就是，如果当前日期为2016-7-30,则加一个月间隔就是8月份。为什么要这么加呢？大家知道，如果直接加的话，到了12月就变成13月了，明显这不合理，当然，我们也可以通过MOD方法来做到12月到1月的过渡。

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

    原文: https://strongyoung.gitbooks.io/mysql-reference-manual/content/tutorial/creating_using_database/retrieving_information_from_table.html