Sorting Rows

你可能注意到,前面的例子中,结果行的排列没有特定的顺序,当行按一定有意义的方法排序时,更容易去检查查询结果.使用ORDER BY来排序一个结果.

这是鸟儿的生日按日期排序:

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

在字符类型的列中,排序和其他比较操作一样,正常情况下对大小写不敏感,这意味着,列的顺序除了大小写外是唯一的,你可以使用BINARY来强制排序对大小写敏感,像这样:ORDER BY BINARY col_name

默认情况下,排序按升序排列,给被排列的列名添加关键字DESC可以使其按降序排列:

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

你可以排序多个列,对不同的列实行不同的排序方法,例如,先按宠物类型的升序排序,再按宠物出生日期的降序排序,使用下面的查询:

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

关键字DESC只应用于它前面,紧接着它的列名(birth),而不影响其他列(species)的排序.

Counting Rows

数据库经常用来回答这样的问题,一个表中某种类型的数据的出现频率.例如,你可能想知道你有多少宠物,每个主人有多少宠物,或者你想实现其他方式的统计.

计算宠物的总数,等价于,pet表有多少行,因为每个宠物只有一条记录.用COUNT(*)统计行的数量,所以像这样统计你的宠物数量:

1
2
3
4
5
6
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

前面,你检索了拥有宠物的主人的名字,如果你想知道每个主人有多少宠物,你也可以使用COUNT(*):

1
2
3
4
5
6
7
8
9
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+

这个查询使用了GROUP BY为每个owner分组所有的记录,结合使用COUNT(*)GROUP BY对于描述不同分组下的数据是很有用的,下面的例子,展示了其他方式的统计:

每个性别的动物数量:

1
2
3
4
5
6
7
8
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+

查询结果中的NULL表明性别未知.

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+

当你使用COUNT(*)时,也不需要检索整个表,例如,当你仅仅统计dog,cat时:

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = 'dog' OR species = 'cat'
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+

英文原文:

Sorting Rows

Counting Rows