[MySQL] 排序与分页

文章目录

• • 图
  • 资料
  • 1 排序数据
  • 2 多级排序
  • 3 分页
  • • 3.1 每页显示pageSize条记录，此时显示第pageNo页：
    • 3.2 WHERe ... ORDER BY ...LIMIT 声明顺序如下：
    • 3.3 MySQL8.0新特性：LIMIT ... OFFSET ...
    • 3.4 拓展
  • 4 排序与分页练习
  • • 题目：
    • 解答：

---

图

资料

根据【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！】 整理
资料链接：
百度网盘：
链接：https://pan.baidu.com/s/1KboU_3EZJxrezMWZ2klP6g
提取码：1234
阿里云盘
【MySQL】

1 排序数据

如果没有使用排序操作，默认情况下查询返回的数据是按照添加数据的顺序显示的。

使用 ORDER BY 子句排序。

• ASC（ascend）: 升序
• DESC（descend）:降序

ORDER BY 子句在SELECt语句的结尾。

按照salary从高到低的顺序显示员工信息

# 按照salary从高到低的顺序显示员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

按照salary从低到高的顺序显示员工信息

# 按照salary从低到高的顺序显示员工信息
SELECt employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;

如果在ORDER BY 后没有显式指名排序的方式的话，则默认按照升序排列。

# 按照salary从低到高的顺序显示员工信息
SELECt employee_id,last_name,salary
FROM employees
ORDER BY salary;

我们可以使用列的别名，进行排序

SELECt employee_id,last_name,salary*12 AS annual_sal
FROM employees
ORDER BY annual_sal ASC;

列的别名不能在WHERe中使用

SELECt employee_id,last_name,salary*12 AS annual_sal
FROM employees
WHERe annual_sal >20000
ORDER BY annual_sal ASC;

[42S22][1054] Unknown column ‘annual_sal’ in ‘where clause’

与语句的执行顺序有关
查询语句会先执行 from 子句
然后执行 where 子句
再执行 select 子句，字段的别名在select子句定义声明
所以在where子句中列还未有别名，列的别名不能再where中使用
然后执行其他子句

别名的使用只能在别名声明之后

强调格式：WHERe 需要声明在FROM后，ORDER BY之前。

排序的依据字段可以不是查询的字段

SELECt employee_id,salary
FROM employees
WHERe department_id IN (50,60,70)
ORDER BY department_id DESC;

2 多级排序

显示员工信息，按照department_id的降序排列，salary的升序排列

# 显示员工信息，按照department_id的降序排列，salary的升序排列
SELECt employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC, salary ASC;

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

3 分页

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

MySQL中使用 LIMIT 实现分页

语法：

SELECt 字段
FROM 表名
[ WHERe 条件 ]
[ ORDER BY 排序条件 ]
LIMIT [位置偏移量, ] 行数 

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。

每页显示20条记录，此时显示第1页：

SELECt employee_id, last_name
FROM employees
LIMIT 0, 20;
# 或
# 结构"LIMIT 0,条目数" 等价于 "LIMIT 条目数"
SELECt employee_id, last_name
FROM employees
LIMIT 20;

每页显示20条记录，此时显示第2页：
第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，每页显示20条，第二页第一条为第21条，所以要显示第二页偏移量为 (21-1)=20。

SELECt employee_id, last_name
FROM employees
LIMIT 20, 20;

每页显示20条记录，此时显示第3页：
第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，每页显示20条，第三页第一条为第41条，所以要显示第三页偏移量为 (41-1)=40。

SELECt employee_id, last_name
FROM employees
LIMIT 40, 20;

3.1 每页显示pageSize条记录，此时显示第pageNo页：

由上面的例子：
第一页偏移量：( 1 - 1 ) * 20
第二页偏移量：( 2 - 1 ) * 20
第三页偏移量：( 3 - 1 ) * 20
可以得出，每页显示pageSize条记录，此时显示第pageNo页的偏移量计算公式为： ( pageNo - 1 ) * pageSize

3.2 WHERe … ORDER BY …LIMIT 声明顺序如下：

SELECt employee_id,last_name,salary
FROM employees
WHERe salary > 6000
ORDER BY salary DESC
LIMIT 0,10;

注意：LIMIT 子句必须放在整个SELECt语句的最后！

表里有107条数据，我们只想要显示第 32、33 条数据：

# 表里有107条数据，我们只想要显示第 32、33 条数据
SELECT employee_id, last_name
FROM employees
# 偏移量为 要显示的第一条数据的行数减一
LIMIT 31, 2;

3.3 MySQL8.0新特性：LIMIT … OFFSET …

例如，“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。

LIMIT … OFFSET … ：
从查询出来的结果集拿去 N 条数据 ( LIMIT … ) ，偏移 M 个位置 ( OFFSET … )

表里有107条数据，我们只想要显示第 32、33 条数据：

# 表里有107条数据，我们只想要显示第 32、33 条数据
SELECt employee_id, last_name
FROM employees
# 获取 32 33 条数据 => 获取两条数据
# 从 32 条开始 => 偏移量为 (32-1)
LIMIT 2 OFFSET 31;

查询员工表中工资最高的员工信息：

# 查询员工表中工资最高的员工信息
SELECt employee_id, last_name, salary
FROM employees
# 查询工资最高，倒序排序，选出第一个
ORDER BY salary DESC
LIMIT 1 OFFSET 0;
# 或
SELECt employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 0, 1;

3.4 拓展

LIMIT 可以使用在MySQL、PGSQL、MariaDB、SQLite 等数据库中使用，表示分页，需要放到 SELECt 语句的最后面。

不能使用在SQL Server、DB2、Oracle！

4 排序与分页练习 题目：

# 1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示
# 2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据 
# 3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

解答：

• #1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

  	 #1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示
  	SELECT last_name, department_id, (salary*12) AS annual_sal
  	FROM employees
  	ORDER BY annual_sal DESC, last_name ASC;
  

• #2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据

  #2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
  SELECt last_name, salary
  FROM employees
  WHERe salary>17000 OR salary<8000
  ORDER BY salary DESC
  LIMIT 20,20;
  # 或
  SELECt last_name, salary
  FROM employees
  WHERe salary NOT BETWEEN 8000 AND 17000
  ORDER BY salary DESC
  LIMIT 20,20;
  

• #3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

  #3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
  SELECt last_name, email, department_id
  FROM employees
  WHERe email LIKE '%e%'
  # 字节数 邮箱的长度
  # length() 求长度
  ORDER BY length(email) DESC, department_id ASC;
  # 或
  SELECt last_name, email, department_id
  FROM employees
  WHERe email REGEXP '[e]'
  ORDER BY length(email) DESC, department_id ASC;