3.查询数据

基本查询

-- 关键字不区分大小写
-- SELECT * FROM <表名>
SELECT * FROM students;
--SELECT 100+200;
--上述查询会直接计算出表达式的结果。虽然SELECT可以用作计算，但它并不是SQL的强项。但是，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

小结

使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。

SELECT查询的结果是一个二维表。

 

条件查询

-- 在一张表有数百万记录的情况下，获取所有记录不仅费时，还费内存和网络带宽。
--SELECT * FROM <表名> WHERE <条件表达式>
-- SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，写成WHERE条件就是SELECT * FROM students WHERE score >= 80。
SELECT * FROM students WHERE score >= 80;
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
SELECT * FROM students WHERE NOT class_id = 2;
--要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

小结

通过WHERE条件查询，可以筛选出符合指定条件的记录，而不是整个表的所有记录。

 

 

投影查询

--从students表中返回id、score和name这三列：
SELECT id, score, name FROM students;
--这样返回的结果集就只包含了我们指定的列，并且，结果集的列的顺序和原表可以不一样。
--使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。
SELECT id, score points, name FROM students;
SELECT id, score points, name FROM students WHERE gender = 'M';

 

小结

使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。

SELECT语句可以对结果集的列进行重命名。

 

排序

 

--如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。
SELECT id, name, gender, score FROM students ORDER BY score;
--如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”：
SELECT id, name, gender, score FROM students ORDER 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, score FROM students WHERE class_id = 1 ORDER BY score DESC;

 

 

小结

使用ORDER BY可以对结果集进行排序；

可以对多列进行升序、倒序排序。

 

分页查询

--分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现。
--我们先把所有学生按照成绩从高到低进行排序，再我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
--上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

--如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

 

由于第4页只有1条记录，因此最终结果集按实际数量1显示。LIMIT 3表示的意思是“最多3条记录”。

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

LIMIT总是设定为pageSize；
OFFSET计算公式为pageSize * (pageIndex - 1)。
这样就能正确查询出第N页的记录集。

如果原本记录集一共就10条记录，但我们把OFFSET设置为20，会得到什么结果呢？

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 20;
--Empty result set，OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

注意

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

 

小结

使用LIMIT <M> OFFSET <N>可以对结果集进行分页，每次查询返回结果集的一部分；

分页查询需要先确定每页的数量和当前页数（返回的第一个记录的“下标”），然后确定LIMIT和OFFSET的值。