5 MySQL-排序和分页

5 MySQL-排序和分页

排序

排序规则

1. 使用ORDER BY子句排序

2. ASC ascend 升序

   DESC descend 降序

3. ORDER BY 子句在SELECT语句的结尾

-- 1.1 默认排序方式是添加的顺序
SELECT *
FROM employees;

单列排序

-- 1.2 单列排序
-- 1.2.1 单列排序的降序
SELECT *
FROM employees
ORDER BY salary DESC;

-- 1.2.2 单列排序的升序 -- 默认按照升序排列
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary ASC;

-- 1.2.3 使用别名  只能使用在order by中
--                不能使用在where中使用
--            解释： 在底层执行过程中，下列SQL语句执行顺序是：
--                  1. From语句
--                  2. Where语句 因此在where中不能使用别名，因为Select还未执行别名操作
--                  3. SELECT语句
--                  4. Order By语句
-- 注意： ORDER BY 放在结尾处
SELECT employee_id, first_name, last_name, salary AS sal
FROM employees
WHERE salary BETWEEN 4000 AND 8000
ORDER BY sal;

多列排序

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

-- 1.3 多列排序
-- 以部门号降序，薪水升序的原则
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC;

分页

实现规则

1. 分页原理

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

2. MySQL中使用 LIMIT 实现分页

3. 格式：LIMIT [位置偏移量postion, 行数rows]

4. MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT4,3;”返回的结果相同。

5. 分页显式公式：（当前页数-1）*每页条数，每页条数

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

7. 使用 LIMIT 的好处：

   约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有1 条，就可以使用LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

-- 2. 分页
-- ---------------------- ---------------------- ----------------------
-- 1.1 Limit 实现 分页显示
-- 第一个参数是 位置position 从0开始
-- 第二个参数是 显示行数rows
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
ORDER BY department_id, salary DESC
LIMIT 0, 10;

-- where     order by     limit
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE salary BETWEEN 4000 AND 8000
ORDER BY department_id, salary DESC
LIMIT 0, 5;

-- 不使用函数，显示最高工资
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 0,1;

拓展

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

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

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

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

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

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

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

Exercise

-- 3. Exercise
-- ---------------------- ---------------------- ----------------------
# 3.1 查询员工姓名、部门号、薪资，按照年薪降序，姓名升序
SELECT last_name, department_id, salary * 12 AS "annual_salary"
FROM employees
ORDER BY annual_salary DESC, last_name ASC;

# 3.2 查询工资不再8000到17000的员工姓名和薪资，按薪资降序，显示第21到40位置的数据
SELECT last_name, salary
FROM employees
WHERE NOT salary BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20, 20;

# 3.3 查询邮箱中包含e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT employee_id, last_name, email, department_id
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id;