小小程知识库
xxcheng
2023-07-20
目录

排序与分页

# 排序 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 |
-- ... ...
1
2
3
4
5
6
7
8

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 |
-- ... ...
1
2
3
4
5
6
7
8
9
10
11

# 排序规则

  • 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 |
-- ... ...
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

  • 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 |
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

# 多列排序

ORDER BY
-- 一级排序
col_name ASC,
-- 二级排序
col_name_2 DESC,
-- 三级排序
col_name_3 ASC,
-- ...
1
2
3
4
5
6
7
8
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 |
-- ... ...
1
2
3
4
5
6
7
8
9
10
11
12
13

# 分页 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 取第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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 取第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)
1
2
3
4
5
6
7
8
9
10
11
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)
1
2
3
4
5
6
7
8
9
10
11

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

LIMIT (pageNo-1)*pageSize,pageSize;
1

# 不同 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 选择工资不在 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 查询邮箱中包含 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
上次更新: 2023/07/20, 16:47:14
运算符的使用
多表查询

← 多表查询→

最近更新
01
Go：GMP模型深入理解
01-10
02
rpc学习：进阶到gRPC
01-04
03
配置
12-12
更多文章>
Theme by Vdoing | Copyright © 2019-2024 xxcheng | 浙ICP备19024050号