分页查询和聚合查询

一、分页查询

1. 为什么要使用分页

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

查询可以通过LIMIT OFFSET 子句实现

2. 年轻人的第一个分页查询

SELECT
	id,
	NAME,
	score
FROM
	students
ORDER BY
	score DESC,
	id DESC
LIMIT 3 offset 6
1
2
3
4
5
6
7
8
9
10

结果如下：查询到的是第6-8的三条记录

7	小林	85
5	小白	81
4	小米	73

1
2
3
4

注意SQL记录集的索引从0开始！！！！

当查询的offset大于实际的记录集的时候

当offset过大的时候，查询到的结果并不会报错，而是返回一个空的结果集

3. 参数的意义

• LIMIT总是设定为pageSize；
• OFFSET计算公式为pageSize * (pageIndex - 1)。
• PAGEINDEX表示的是当前页的索引

千万要注意，SQL中记录集的索引是从0开始的。根据上述的条件就就能正确查询出第N页的记录集。

4. 分页查询的关键

分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值。

---

二、聚合查询

1.1 聚合查询（聚合函数）

如果我们想统计出来一张表的数据有多少。不需要人工去数，只需要使用SQL内置的聚合函数来帮助我们实现这些功能。

SELECT COUNT(*) FROM students

10

1
2
3
4

COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

对于此种现象，我们应该给查询结果的设置一个别名，让我们更好的识别出查询的结果。事实和实验也表明，设置了别名的查询结果更通俗易懂。

深刻认识到count(*)是查询所有列的行数，一般情况下和查询指定列的行数的结果是一致的。

在大部分情况下（指没有NULL值得情况下），COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：

1.2 常用的聚合函数

1.3 注意一些特殊的结果

SELECT COUNT(id) FROM students WHERE gender='M' AND score>=100

要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

东西是死的，人是活的呀，可以根据实际需要设置不同的查询条件来获得自己想要的结果。

2.1 分组聚合查询

【实验】统计一般学生的数量

# 传统的SQL语句
SELECT COUNT(*) FROM students WHERE class_id =1

# 现在改进的SQL语句
SELECT class_id ,COUNT(*) num FROM students  GROUP BY class_id

1
2
3
4
5
6

使用上述语句可以很好的查询出来每个班级的成员数。

2.2 可以使用多个列进行分组

理解：count(*)只是查询结果的一个列而已。

# 我们想统计各班的男生和女生人数：
SELECT
	class_id,
	gender,
	COUNT(*)
FROM
	students
GROUP BY
	class_id,
	gender
1
2
3
4
5
6
7
8
9
10

2.3 个人实验

• 实验1

#查询每个班级的平均分
SELECT class_id,AVG(score) from students GROUP BY class_id
1
2

结果如下：

1	86.5000
2	73.6667
3	88.3333

1
2
3
4

• 实验2

#请使用一条SELECT查询查出每个班级男生和女生的平均
SELECT class_id,gender,AVG( score) from students GROUP BY class_id,gender
1
2

结果如下：

1	F	84.0000
1	M	89.0000
2	F	81.0000
2	M	70.0000
3	F	88.0000
3	M	89.0000

1
2
3
4
5
6
7

---

个人理解

• select查询语句是从后往前执行的，先执行后面的group by子句，再返回头将select的结果输出。
• 在写select语句中，不知道能不能得到自己想要的东西的情况下，可以先试着用*来将所有东西输出，然后需要啥改啥。
• 按谁分组，给你显示出来的东西就会显示出来他的第一条东西，应该再查询结果的地方加上分组的列明和其他数据，这样会更加好看。