MySQL中的排序与分页
MySQL中的排序与分页
排序查询 (ORDER BY)
ORDER BY 用于对查询结果进行排序。
SELECT 字段名 FROM 表名 ORDER BY 字段1 [ASC|DESC], 字段2 [ASC|DESC];- ASC 表示升序，DESC 表示降序，默认为 ASC。
- 可以指定多个字段进行排序，多个字段之间用逗号分隔。
单字段排序
CREATE TABLE example1 (id INT, name VARCHAR(10));
INSERT INTO example1 VALUES (10, '张三'), (8, '李四'), (5, '王五'), (100, '赵六');
SELECT * FROM example1 ORDER BY id;
SELECT * FROM example1 ORDER BY id DESC;多字段排序
例如，按年龄降序排列学生，如果年龄相同则按学号升序排列：
CREATE TABLE students (id INT PRIMARY KEY, age TINYINT, name VARCHAR(16));
INSERT INTO students (id, age, name) VALUES (1001, 18, '张三'), (1005, 20, '李四'), (1003, 18, '王五'), (1004, 20, '赵六'), (1010, 19, '孙七');
SELECT * FROM students ORDER BY age DESC, id ASC;按别名排序
SELECT age AS '年龄', id AS '学号' FROM students ORDER BY `年龄` ASC, `学号` DESC;按函数排序
假设有一个学生表包含学生的出生日期，现在按出生年份升序、学号升序排序：
CREATE TABLE students (id INT, birth DATE, name VARCHAR(16));
INSERT INTO students (id, birth, name) VALUES (1001, '1990-10-10', '张三'), (1005, '1960-03-01', '李四'), (1003, '1960-08-16', '王五'), (1004, '1968-07-01', '赵六'), (1010, '1962-05-16', '孙七');
SELECT id AS '学号', birth AS '出生日期', YEAR(birth) AS '出生年份', name AS '姓名' FROM students ORDER BY `出生年份` ASC, `学号` ASC;WHERE 之后排序
查询订单金额大于等于100的订单，并按订单金额降序排列：
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, amount DECIMAL(10,2));
INSERT INTO orders (amount) VALUES (88.95), (100.68), (500), (300), (20.88), (200.5);
SELECT id AS '订单编号', amount AS '订单金额' FROM orders WHERE amount >= 100 ORDER BY amount DESC;限制查询结果 (LIMIT)
LIMIT 用于限制查询结果返回的行数，常用于分页。
SELECT 列 FROM 表 LIMIT [偏移量,] 行数;- 偏移量表示跳过的行数，默认为0。
- 行数表示从偏移量后开始取的行数。
获取前N行记录
SELECT id AS '订单编号', amount AS '订单金额' FROM orders LIMIT 2;
SELECT id AS '订单编号', amount AS '订单金额' FROM orders LIMIT 1, 2;获取最大或最小的记录
SELECT id AS '订单编号', amount AS '订单金额' FROM orders ORDER BY amount DESC LIMIT 1;
SELECT id AS '订单编号', amount AS '订单金额' FROM orders ORDER BY amount ASC LIMIT 1;获取第N到第M的记录
例如，获取订单金额最高第3到第5名的订单：
SELECT id AS '订单编号', amount AS '订单金额' FROM orders ORDER BY amount DESC LIMIT 2, 3;分页查询
假设每页显示2条记录，按订单金额降序排列，分别查询第1页、第2页、第3页的数据：
SELECT id AS '订单编号', amount AS '订单金额' FROM orders ORDER BY amount DESC LIMIT 0, 2;
SELECT id AS '订单编号', amount AS '订单金额' FROM orders ORDER BY amount DESC LIMIT 2, 2;
SELECT id AS '订单编号', amount AS '订单金额' FROM orders ORDER BY amount DESC LIMIT 4, 2;解决分页排序数据重复的问题
例如，增加主键排序以避免分页结果乱序：
SELECT * FROM example1 ORDER BY b ASC, a ASC LIMIT 0, 2;
SELECT * FROM example1 ORDER BY b ASC, a ASC LIMIT 2, 2;
SELECT * FROM example1 ORDER BY b ASC, a ASC LIMIT 4, 2;
SELECT * FROM example1 ORDER BY b ASC, a ASC LIMIT 6, 2;