检索数据

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