查询结果集通常是按照id
排序的,也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办?可以加上ORDER BY
子句。
例如按照成绩从低到高进行排序:
SELECT id, name, gender, score FROM students ORDER BY score;
如果要反过来,按照成绩从高到底排序,我们可以加上DESC
表示“倒序”:
SELECT id, name, gender, score FROM studentsORDER BY score DESC;
如果score
列有相同的数据,要进一步排序,可以继续添加列名。
例如,使用ORDER BY score DESC, gender
表示先按score
列倒序,如果有相同分数的,再按gender
列排序:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
默认的排序规则是ASC
:“升序”,即从小到大。ASC
可以省略,即ORDER BY score ASC
和ORDER BY score
效果一样。
如果有WHERE
子句,那么ORDER BY
子句要放到WHERE
子句后面。例如,查询一班的学生成绩,并按照倒序排序:
SELECT id, name, gender, scoreFROM studentsWHERE class_id = 1ORDER BY score DESC;
这样,结果集仅包含符合WHERE
条件的记录,并按照ORDER BY
的设定排序。
使用ORDER BY
可以对结果集进行排序;
可以对多列进行升序、倒序排序。
使用SELECT查询时,如果结果集数据量很大,比如几万行数据,放在一个页面显示的话数据量太大,不如分页显示,每次显示100条。
要实现分页功能,实际上就是从结果集中显示第1100条记录作为第1页,显示第101200条记录作为第2页,以此类推。
因此,分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>
子句实现。我们先把所有学生按照成绩从高到低进行排序:
现在,我们把结果集分页,每页3条记录。要获取第1页的记录,可以使用LIMIT 3 OFFSET 0
:
SELECT id, name, gender, scoreFROM studentsORDER BY score DESCLIMIT 3 OFFSET 0;
上述查询LIMIT 3 OFFSET 0
表示,对结果集从0号记录开始,最多取3条。注意SQL记录集的索引从0开始。
如果要查询第2页,那么我们只需要“跳过”头3条记录,也就是对结果集从3号记录开始查询,把OFFSET
设定为3:
SELECT id, name, gender, scoreFROM studentsORDER BY score DESCLIMIT 3 OFFSET 3;
类似的,查询第3页的时候,OFFSET
应该设定为6:
SELECT id, name, gender, scoreFROM studentsORDER BY score DESCLIMIT 3 OFFSET 6;
OFFSET
是可选的,如果只写LIMIT 15
,那么相当于LIMIT 15 OFFSET 0
。
在MySQL中,LIMIT 15 OFFSET 30
还可以简写成LIMIT 30, 15
。
使用LIMIT OFFSET
分页时,随着N
越来越大,查询效率也会越来越低。
使用LIMIT OFFSET
可以对结果集进行分页,每次查询返回结果集的一部分;
分页查询需要先确定每页的数量和当前页数,然后确定LIMIT
和OFFSET
的值。
如果我们要统计一张表的数据量,例如,想查询students
表一共有多少条记录,难道必须用SELECT * FROM students
查出来然后再数一数有多少行吗?
这个方法当然可以,但是比较弱智。对于统计总数、平均数这类计算,SQL提供了专门的聚合函数,使用聚合函数进行查询,就是聚合查询,它可以快速获得结果。
仍然以查询students
表一共有多少条记录为例,我们可以使用SQL内置的COUNT()
函数查询:
SELECT COUNT(*) FROM students;
COUNT(*)
表示查询所有列的行数,要注意聚合的计算结果虽然是一个数字,但查询的结果仍然是一个二维表,只是这个二维表只有一行一列,并且列名是COUNT(*)
。
通常,使用聚合查询时,我们应该给列名设置一个别名,便于处理结果:
SELECT COUNT(*) num FROM students;-- 使用聚合查询并设置结果集的列名为num:
COUNT(*)
和COUNT(id)
实际上是一样的效果。另外注意,聚合查询同样可以使用WHERE
条件,因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等:
SELECT COUNT(*) boys FROM students WHERE gender = ‘M‘;
除了COUNT()
函数外,SQL还提供了如下聚合函数:
函数 | 说明 |
---|---|
SUM | 计算某一列的合计值,该列必须为数值类型 |
AVG | 计算某一列的平均值,该列必须为数值类型 |
MAX | 计算某一列的最大值 |
MIN | 计算某一列的最小值 |
注意,MAX()
和MIN()
函数并不限于数值类型。如果是字符类型,MAX()
和MIN()
会返回排序最后和排序最前的字符。
要统计男生的平均成绩,我们用下面的聚合查询:
SELECT AVG(score) average FROM students WHERE gender = ‘M‘;-- 使用聚合查询计算男生平均成绩:
要特别注意:如果聚合查询的WHERE
条件没有匹配到任何行,COUNT()
会返回0,而SUM()
、AVG()
、MAX()
和MIN()
会返回NULL
:
如果我们要统计一班的学生数量,我们知道,可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;
。如果要继续统计二班、三班的学生数量,难道必须不断修改WHERE
条件来执行SELECT
语句吗?
对于聚合查询,SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询:
SELECT COUNT(*) num FROM students GROUP BY class_id;-- 按class_id分组:
执行这个查询,COUNT()
的结果不再是一个,而是3个,这是因为,GROUP BY
子句指定了按class_id
分组,因此,执行该SELECT
语句时,会把class_id
相同的列先分组,再分别计算,因此,得到了3行结果。
但是这3行结果分别是哪三个班级的,不好看出来,所以我们可以把class_id
列也放入结果集中
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
使用SQL提供的聚合查询,我们可以方便地计算总数、合计值、平均值、最大值和最小值;
聚合查询也可以添加WHERE
条件。