使用SELECT从一个表中获取信息,命令的一般形式为:

1
2
3
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

what_to_select表明你想看到什么,可以是列的组合,或者*表示所有列;which_table表明你想从哪个表检索数据;WHERE项是可选的,如果存在,conditions_to_satisfy指定一个或多个为了实现检索,行必须满足的条件.

Selecting All Data

从表中检索出所有信息:

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

如果你想回顾整个表,这种形式的SELECT是很有用的,比如在你刚刚向表中导入数据之后.你可能会发现Bowser的出生日期不正确,应该是1989,而不是1979.

至少有两种方法可以修正错误:

  • 重新编辑pet.txt,然后使用DELETE清空整个表,LOAD DATA重新导入数据:
    1
    2
    mysql> DELETE FROM pet;
    mysql> LOAD DATA LOCAL INFILE ‘pet.txt’ INTO TABLE pet;

但是这样做的话,你需要重新输入Puffball的记录.

  • 可以使用UPDATE修正发生错误的记录:
    1
    mysql> UPDATE pet SET birth = ‘1989-08-31’ WHERE name = ‘Bowser’;

UPDATE只改变有错误的记录,不需要重新导入数据.

Selecting Particular Rows

如果你不想看到整个表的内容,尤其是当表的内容十分庞大时,在这种情况下,在你想看到的信息上指定一些约束条件.

可以只选择表中的特定一行,例如,你想确认一下对Bowser出生日期的更改,像这样选择Bowser的记录:

1
2
3
4
5
6
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.

字符串比较时对大小写不敏感,所以你可以指定namebowser,BOWSER等等,查询结果是相同的.

你可以对任何列指定条件,不仅仅是name,例如,你想知道那个动物是在1998年后出生的,可以测试birth列:

1
2
3
4
5
6
7
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 |
+----------+-------+---------+------+------------+-------+

你可以结合约束条件,例如,确定母狗:

1
2
3
4
5
6
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,两者可以混合使用.尽管ANDOR拥有更高的优先级,如果你同时使用两个运算符,使用括号来指定条件的分组情况是更好的选择.

1
2
3
4
5
6
7
8
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 |
+-------+--------+---------+------+------------+-------+

Selecting Particular Columns

如果你不想看到表的全部列,仅仅需要指出你感兴趣的列,用逗号分隔.例如,你想知道你的宠物是什么时候出生的,选择namebirth两列:

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

为了确定谁拥有宠物,使用这个查询:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+

注意到这个查询只是简单地从每个记录中检索owner列,他们中的一部分多次出现,为了最小化输出,通过添加关键字DISTINCT可以只检索每个独一无二的记录一次:

1
2
3
4
5
6
7
8
9
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+

你可以使用WHERE来结合行选择和列选择,例如,只得到猫和狗的出生日期:

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

英文原文:

Selecting All Data

Selecting Particular Rows

Selecting Particular Columns