MySQL学习 - 排序与分页

排序 ORDER BY

使用 ORDER BY 语句，写在 WHERE 句子之后，默认升序排序

mysql> SELECT last_name,salary FROM employees ORDER BY salary;
+-------------+----------+
| last_name   | salary   |
+-------------+----------+
| Olson       |  2100.00 |
| Markle      |  2200.00 |
| Philtanker  |  2200.00 |
-- ... ...

ORDER BY 可以使用之前学习过的列的别名，而 WHERE 中无法使用列的别名，因为 WHERE 早于 SELECT 处理。

mysql> SELECT last_name,salary*12 annual_salary FROM employees ORDER BY annual_salary;
+-------------+---------------+
| last_name   | annual_salary |
+-------------+---------------+
| Olson       |      25200.00 |
| Markle      |      26400.00 |
| Philtanker  |      26400.00 |
| Landry      |      28800.00 |
| Gee         |      28800.00 |
| Colmenares  |      30000.00 |
-- ... ...

排序规则

• ASC 升序排序 默认

  mysql> SELECT last_name,salary FROM employees ORDER BY salary ASC;
  +-------------+----------+
  | last_name   | salary   |
  +-------------+----------+
  | Olson       |  2100.00 |
  | Markle      |  2200.00 |
  | Philtanker  |  2200.00 |
  | Landry      |  2400.00 |
  | Gee         |  2400.00 |
  | Colmenares  |  2500.00 |
  -- ... ...

• DESC 降序排序

  mysql> SELECT last_name,salary FROM employees ORDER BY salary DESC;
  +-------------+----------+
  | last_name   | salary   |
  +-------------+----------+
  | King        | 24000.00 |
  | Kochhar     | 17000.00 |
  | De Haan     | 17000.00 |
  | Russell     | 14000.00 |
  | Partners    | 13500.00 |
  | Hartstein   | 13000.00 |
  | Greenberg   | 12000.00 |

多列排序

ORDER BY
-- 一级排序
col_name ASC,
-- 二级排序
col_name_2 DESC,
-- 三级排序
col_name_3 ASC,
-- ...

mysql> SELECT last_name,salary,department_id FROM employees ORDER BY salary DESC,department_id ASC;
+-------------+----------+---------------+
| last_name   | salary   | department_id |
+-------------+----------+---------------+
| King        | 24000.00 |            90 |
| Kochhar     | 17000.00 |            90 |
| De Haan     | 17000.00 |            90 |
| Russell     | 14000.00 |            80 |
| Partners    | 13500.00 |            80 |
| Hartstein   | 13000.00 |            20 |
| Errazuriz   | 12000.00 |            80 |
| Greenberg   | 12000.00 |           100 |
-- ... ...

分页 LIMIT

使用 LIMIT 语句实现，写在 ORDER BY 之后

格式：LIMIT 偏移量,数据条目数

当偏移量为0时，可以省略：LIMIT 条目数

MySQL8.0 增加了一个新的特性，可以使用 OFFSET 来代替偏移量

LIMIT 数据条目数 OFFSET 偏移量

示例

取第1~5条

mysql> SELECT last_name,salary,department_id FROM employees ORDER BY salary DESC,department_id ASC LIMIT 0,5;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| King      | 24000.00 |            90 |
| Kochhar   | 17000.00 |            90 |
| De Haan   | 17000.00 |            90 |
| Russell   | 14000.00 |            80 |
| Partners  | 13500.00 |            80 |
+-----------+----------+---------------+
5 rows in set (0.00 sec)

mysql> SELECT last_name,salary,department_id FROM employees ORDER BY salary DESC,department_id ASC LIMIT 5;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| King      | 24000.00 |            90 |
| Kochhar   | 17000.00 |            90 |
| De Haan   | 17000.00 |            90 |
| Russell   | 14000.00 |            80 |
| Partners  | 13500.00 |            80 |
+-----------+----------+---------------+
5 rows in set (0.00 sec)

取第4~8条

mysql> SELECT last_name,salary,department_id FROM employees ORDER BY salary DESC,department_id ASC LIMIT 3,5;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| Russell   | 14000.00 |            80 |
| Partners  | 13500.00 |            80 |
| Hartstein | 13000.00 |            20 |
| Errazuriz | 12000.00 |            80 |
| Greenberg | 12000.00 |           100 |
+-----------+----------+---------------+
5 rows in set (0.00 sec)

mysql> SELECT last_name,salary,department_id FROM employees ORDER BY salary DESC,department_id ASC LIMIT 5 OFFSET 3;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| Russell   | 14000.00 |            80 |
| Partners  | 13500.00 |            80 |
| Hartstein | 13000.00 |            20 |
| Errazuriz | 12000.00 |            80 |
| Greenberg | 12000.00 |           100 |
+-----------+----------+---------------+
5 rows in set (0.00 sec)

取第6~10条和第11~15条

mysql> SELECT last_name,salary,department_id FROM employees ORDER BY salary DESC,department_id ASC LIMIT 5,5;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| Hartstein | 13000.00 |            20 |
| Errazuriz | 12000.00 |            80 |
| Greenberg | 12000.00 |           100 |
| Higgins   | 12000.00 |           110 |
| Ozer      | 11500.00 |            80 |
+-----------+----------+---------------+
5 rows in set (0.00 sec)

mysql> SELECT last_name,salary,department_id FROM employees ORDER BY salary DESC,department_id ASC LIMIT 10,5;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| Raphaely  | 11000.00 |            30 |
| Cambrault | 11000.00 |            80 |
| Abel      | 11000.00 |            80 |
| Vishney   | 10500.00 |            80 |
| Zlotkey   | 10500.00 |            80 |
+-----------+----------+---------------+
5 rows in set (0.00 sec)

通过示例，可以推导出一个公式：

LIMIT (pageNo-1)*pageSize,pageSize;

不同 DBMS 实现分页之间的差异

在不同的 DBMS 之间它们实现的关键字和方法是不同的。

• MySQL、PostgreSQL、MariaDB、SQLite 使用 LIMIT 关键字；
• SQL Server、Access 使用 TOP 关键字；
• ...

练习

查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序显示（取前5位）

mysql> SELECT first_name,last_name,department_id,salary*12 as salary_annual
    -> FROM employees
    -> ORDER BY salary_annual DESC,
    -> first_name ASC
    -> LIMIT 5;
+------------+-----------+---------------+---------------+
| first_name | last_name | department_id | salary_annual |
+------------+-----------+---------------+---------------+
| Steven     | King      |            90 |     288000.00 |
| Lex        | De Haan   |            90 |     204000.00 |
| Neena      | Kochhar   |            90 |     204000.00 |
| John       | Russell   |            80 |     168000.00 |
| Karen      | Partners  |            80 |     162000.00 |
+------------+-----------+---------------+---------------+
5 rows in set (0.00 sec)

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

mysql> SELECT last_name,salary
    -> FROM employees
    -> WHERE salary NOT BETWEEN 8000 AND 17000
    -> ORDER BY salary DESC
    -> LIMIT 20,5;
+-----------+---------+
| last_name | salary  |
+-----------+---------+
| Ernst     | 6000.00 |
| Fay       | 6000.00 |
| Mourgos   | 5800.00 |
| Austin    | 4800.00 |
| Pataballa | 4800.00 |
+-----------+---------+
5 rows in set (0.00 sec)

查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序（取前5位）

mysql> SELECT department_id,last_name,email FROM employees
    -> WHERE email LIKE '%e%'
    -> -- WHERE email REGEXP 'e'
    -> ORDER BY LENGTH(email) DESC,
    -> department_id ASC
    -> LIMIT 5;
+---------------+------------+----------+
| department_id | last_name  | email    |
+---------------+------------+----------+
|            20 | Hartstein  | MHARTSTE |
|            30 | Colmenares | KCOLMENA |
|            30 | Raphaely   | DRAPHEAL |
|            50 | Everett    | BEVERETT |
|            50 | Dellinger  | JDELLING |
+---------------+------------+----------+
5 rows in set (0.00 sec)