MySQL_05：排序和分页

排序

排序规则

使用 ORDER BY 子句排序

• ASC（ascend）: 升序
• DESC（descend）:降序
• ORDER BY 子句在SELECT语句的结尾
• 如果没有使用排序操作。默认情况下查询返回的数据是按照当此添加数据的顺序显示的
• 如果在order by 后面没有显示的指明排序方式的话，默认按照升序

练习

1.单列排序

# 1.1:基本使用
# 使用order by对查询的数据进行排序操作
# 升序：asc（ascend）
# 降序：desc（descend）
# 练习：按照salary从高到低的顺序显示员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;
# 练习：按照salary从低到高的顺序显示员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;

练习2：列的别名进行排序：

# 2 . 列的别名进行排序
SELECT employee_id,salary,salary*12 annual_sal
FROM employees
ORDER BY annual_sal DESC;

# 列的别名只能在order by 中使用不能在where中使用
# 下面这里会报错，其实原因就是sql语句的执行顺序并不是从上往下，而是从from开始，where语句在select语句的前面，此时还没有别名
SELECT employee_id,salary,salary*12 annual_sal
FROM employees
WHERE annual_sal>8000;

练习3

# 3. 强调格式：where需要声明在from的后面，order by 的前面
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;

2.二级排序以及多列排序

# 4. 存在order by 选中的排序存在相同数据的情况时候，考虑二级排序
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC;
# 练习：显示员工信息，按照department_id降序排序，salary的升序排序
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

二级之前

二级之后

多列（多级排序）：可以使用不在SELECT列表中的列排序。 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第 一列数据中所有值都是唯一的，将不再对第二列进行排序。

---

---

分页

背景

• 背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
• 背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？

原理

• 分页原理

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

  MySQL中使用 LIMIT 实现分页

• 格式：

  LIMIT [位置偏移量,] 行数
  

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

操作代码

# 1.mysql使用limit实现数据的分页显示

# 需求：每页显示20条记录，此时显示第1页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;
# 需求：每页显示20条记录，此时显示第2页
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;
# 需求：每页显示20条记录，此时显示第3页
SELECT employee_id,last_name
FROM employees
LIMIT 30,20;
# 需求：每页显示pageSize条记录，此时显示pageNo页
# 公式：limit (paggeSize-1)*pageSize,pageSize;


# 2.where...order by...limit 声明顺序如下；

# LIMIT的格式：LIMIT 位置偏移量，条目数
#结果limit 0，条目数等价于limit条目数
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>5000
ORDER BY salary DESC
LIMIT 0,10;
# limit 10;

# 练习：* 表里有 107条数据，我们只想要显示第 32、33 条数据怎么办呢？
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

# 3.mysql 8.0新特性：LIMIT...OFFSET...(后面的变成偏移量，前面的是条目数)
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;

# 练习：差选员工表中工资最高的员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
LIMIT 0,1;
# limit 1;

拓展

在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关 键字，而且需要放到 SELECT 语句的最后面。

• 如果是 SQL Server 和 Access，需要使用 TOP 关键字，比如：

  SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC
  

• 如果是 DB2，使用 FETCH FIRST 5 ROWS ONLY 这样的关键字：

  SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
  

• 如果是 Oracle，你需要基于 ROWNUM 来统计行数：

  SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;
  

---

练习题

# 第一题
SELECT employee_id '姓名',salary*12 '年薪'
FROM employees
ORDER BY 年薪 DESC, employee_id ASC;
# 第二题
SELECT employee_id 姓名,salary '工资'
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;
# 第三题
SELECT last_name,email,department_id
FROM employees
#where email like '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id ASC;

注意：MySQL中列别名为中文时，order by 子句中使用别名时不要加引号

错误示范

SELECT employee_id '姓名',salary*12 '年薪'
FROM employees
ORDER BY '年薪' DESC, employee_id ASC;