1. 排序数据

1.1 排序规则

  • 使用 ORDER BY 子句排序
    • ASC（ascend）: 升序
    • DESC（descend）:降序
  • ORDER BY 子句在SELECT语句的结尾

1.2 单列排序

mysql> SELECT * FROM job_history ORDER BY department_id DESC;
+-------------+------------+------------+------------+---------------+
| employee_id | start_date | end_date   | job_id     | department_id |
+-------------+------------+------------+------------+---------------+
|         101 | 1989-09-21 | 1993-10-27 | AC_ACCOUNT |           110 |
|         101 | 1993-10-28 | 1997-03-15 | AC_MGR     |           110 |
|         200 | 1987-09-17 | 1993-06-17 | AD_ASST    |            90 |
|         200 | 1994-07-01 | 1998-12-31 | AC_ACCOUNT |            90 |
|         176 | 1998-03-24 | 1998-12-31 | SA_REP     |            80 |
|         176 | 1999-01-01 | 1999-12-31 | SA_MAN     |            80 |
|         102 | 1993-01-13 | 1998-07-24 | IT_PROG    |            60 |
|         114 | 1998-03-24 | 1999-12-31 | ST_CLERK   |            50 |
|         122 | 1999-01-01 | 1999-12-31 | ST_CLERK   |            50 |
|         201 | 1996-02-17 | 1999-12-19 | MK_REP     |            20 |
+-------------+------------+------------+------------+---------------+
10 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM job_history ORDER BY department_id ASC;
+-------------+------------+------------+------------+---------------+
| employee_id | start_date | end_date   | job_id     | department_id |
+-------------+------------+------------+------------+---------------+
|         201 | 1996-02-17 | 1999-12-19 | MK_REP     |            20 |
|         114 | 1998-03-24 | 1999-12-31 | ST_CLERK   |            50 |
|         122 | 1999-01-01 | 1999-12-31 | ST_CLERK   |            50 |
|         102 | 1993-01-13 | 1998-07-24 | IT_PROG    |            60 |
|         176 | 1998-03-24 | 1998-12-31 | SA_REP     |            80 |
|         176 | 1999-01-01 | 1999-12-31 | SA_MAN     |            80 |
|         200 | 1987-09-17 | 1993-06-17 | AD_ASST    |            90 |
|         200 | 1994-07-01 | 1998-12-31 | AC_ACCOUNT |            90 |
|         101 | 1989-09-21 | 1993-10-27 | AC_ACCOUNT |           110 |
|         101 | 1993-10-28 | 1997-03-15 | AC_MGR     |           110 |
+-------------+------------+------------+------------+---------------+
10 rows in set (0.00 sec)

mysql>

1.3 多列排序

mysql> SELECT last_name, department_id,salary FROM employees ORDER BY department_id,salary DESC;
+-------------+---------------+----------+
| last_name   | department_id | salary   |
+-------------+---------------+----------+
| Grant       |          NULL |  7000.00 |
| Whalen      |            10 |  4400.00 |
| Hartstein   |            20 | 13000.00 |
| Fay         |            20 |  6000.00 |
| Raphaely    |            30 | 11000.00 |
| Khoo        |            30 |  3100.00 |
| Baida       |            30 |  2900.00 |
| Tobias      |            30 |  2800.00 |
| Himuro      |            30 |  2600.00 |
| Colmenares  |            30 |  2500.00 |
| Mavris      |            40 |  6500.00 |
| Fripp       |            50 |  8200.00 |
| Weiss       |            50 |  8000.00 |
| Kaufling    |            50 |  7900.00 |
| Vollman     |            50 |  6500.00 |
| Mourgos     |            50 |  5800.00 |
| Sarchand    |            50 |  4200.00 |
| Bull        |            50 |  4100.00 |
| Bell        |            50 |  4000.00 |
| Everett     |            50 |  3900.00 |
| Chung       |            50 |  3800.00 |
| Ladwig      |            50 |  3600.00 |
| Dilly       |            50 |  3600.00 |
| Rajs        |            50 |  3500.00 |
| Dellinger   |            50 |  3400.00 |
| Bissot      |            50 |  3300.00 |
| Mallin      |            50 |  3300.00 |
| Nayer       |            50 |  3200.00 |
| Stiles      |            50 |  3200.00 |
| Taylor      |            50 |  3200.00 |
| McCain      |            50 |  3200.00 |
| Davies      |            50 |  3100.00 |
| Fleaur      |            50 |  3100.00 |
| Walsh       |            50 |  3100.00 |
| Cabrio      |            50 |  3000.00 |
| Feeney      |            50 |  3000.00 |
| Rogers      |            50 |  2900.00 |
| Gates       |            50 |  2900.00 |
| Atkinson    |            50 |  2800.00 |
| Geoni       |            50 |  2800.00 |
| Jones       |            50 |  2800.00 |
| Mikkilineni |            50 |  2700.00 |
| Seo         |            50 |  2700.00 |
| Matos       |            50 |  2600.00 |
| OConnell    |            50 |  2600.00 |
| Grant       |            50 |  2600.00 |
| Marlow      |            50 |  2500.00 |
| Patel       |            50 |  2500.00 |
| Vargas      |            50 |  2500.00 |
| Sullivan    |            50 |  2500.00 |
| Perkins     |            50 |  2500.00 |
| Landry      |            50 |  2400.00 |
| Gee         |            50 |  2400.00 |
| Markle      |            50 |  2200.00 |
| Philtanker  |            50 |  2200.00 |
| Olson       |            50 |  2100.00 |
| Hunold      |            60 |  9000.00 |
| Ernst       |            60 |  6000.00 |
| Austin      |            60 |  4800.00 |
| Pataballa   |            60 |  4800.00 |
| Lorentz     |            60 |  4200.00 |
| Baer        |            70 | 10000.00 |
| Russell     |            80 | 14000.00 |
| Partners    |            80 | 13500.00 |
| Errazuriz   |            80 | 12000.00 |
| Ozer        |            80 | 11500.00 |
| Cambrault   |            80 | 11000.00 |
| Abel        |            80 | 11000.00 |
| Zlotkey     |            80 | 10500.00 |
| Vishney     |            80 | 10500.00 |
| Tucker      |            80 | 10000.00 |
| King        |            80 | 10000.00 |
| Bloom       |            80 | 10000.00 |
| Fox         |            80 |  9600.00 |
| Bernstein   |            80 |  9500.00 |
| Sully       |            80 |  9500.00 |
| Greene      |            80 |  9500.00 |
| Hall        |            80 |  9000.00 |
| McEwen      |            80 |  9000.00 |
| Hutton      |            80 |  8800.00 |
| Taylor      |            80 |  8600.00 |
| Livingston  |            80 |  8400.00 |
| Olsen       |            80 |  8000.00 |
| Smith       |            80 |  8000.00 |
| Cambrault   |            80 |  7500.00 |
| Doran       |            80 |  7500.00 |
| Smith       |            80 |  7400.00 |
| Bates       |            80 |  7300.00 |
| Marvins     |            80 |  7200.00 |
| Tuvault     |            80 |  7000.00 |
| Sewall      |            80 |  7000.00 |
| Lee         |            80 |  6800.00 |
| Ande        |            80 |  6400.00 |
| Banda       |            80 |  6200.00 |
| Johnson     |            80 |  6200.00 |
| Kumar       |            80 |  6100.00 |
| King        |            90 | 24000.00 |
| Kochhar     |            90 | 17000.00 |
| De Haan     |            90 | 17000.00 |
| Greenberg   |           100 | 12000.00 |
| Faviet      |           100 |  9000.00 |
| Chen        |           100 |  8200.00 |
| Urman       |           100 |  7800.00 |
| Sciarra     |           100 |  7700.00 |
| Popp        |           100 |  6900.00 |
| Higgins     |           110 | 12000.00 |
| Gietz       |           110 |  8300.00 |
+-------------+---------------+----------+
107 rows in set (0.00 sec)

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

2. 分页

2.1 背景

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

2.2 实现规则

  • 分页原理
    • 所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件
  • MySQL中使用 LIMIT 实现分页
  • 格式
    • LIMIT [位置偏移量,] 行数
mysql> SELECT * FROM employees LIMIT 0, 10;
+-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
| employee_id | first_name | last_name | email    | phone_number | hire_date  | job_id     | salary   | commission_pct | manager_id | department_id |
+-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven     | King      | SKING    | 515.123.4567 | 1987-06-17 | AD_PRES    | 24000.00 |           NULL |       NULL |            90 |
|         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568 | 1989-09-21 | AD_VP      | 17000.00 |           NULL |        100 |            90 |
|         102 | Lex        | De Haan   | LDEHAAN  | 515.123.4569 | 1993-01-13 | AD_VP      | 17000.00 |           NULL |        100 |            90 |
|         103 | Alexander  | Hunold    | AHUNOLD  | 590.423.4567 | 1990-01-03 | IT_PROG    |  9000.00 |           NULL |        102 |            60 |
|         104 | Bruce      | Ernst     | BERNST   | 590.423.4568 | 1991-05-21 | IT_PROG    |  6000.00 |           NULL |        103 |            60 |
|         105 | David      | Austin    | DAUSTIN  | 590.423.4569 | 1997-06-25 | IT_PROG    |  4800.00 |           NULL |        103 |            60 |
|         106 | Valli      | Pataballa | VPATABAL | 590.423.4560 | 1998-02-05 | IT_PROG    |  4800.00 |           NULL |        103 |            60 |
|         107 | Diana      | Lorentz   | DLORENTZ | 590.423.5567 | 1999-02-07 | IT_PROG    |  4200.00 |           NULL |        103 |            60 |
|         108 | Nancy      | Greenberg | NGREENBE | 515.124.4569 | 1994-08-17 | FI_MGR     | 12000.00 |           NULL |        101 |           100 |
|         109 | Daniel     | Faviet    | DFAVIET  | 515.124.4169 | 1994-08-16 | FI_ACCOUNT |  9000.00 |           NULL |        108 |           100 |
+-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM employees LIMIT 10;
+-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
| employee_id | first_name | last_name | email    | phone_number | hire_date  | job_id     | salary   | commission_pct | manager_id | department_id |
+-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven     | King      | SKING    | 515.123.4567 | 1987-06-17 | AD_PRES    | 24000.00 |           NULL |       NULL |            90 |
|         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568 | 1989-09-21 | AD_VP      | 17000.00 |           NULL |        100 |            90 |
|         102 | Lex        | De Haan   | LDEHAAN  | 515.123.4569 | 1993-01-13 | AD_VP      | 17000.00 |           NULL |        100 |            90 |
|         103 | Alexander  | Hunold    | AHUNOLD  | 590.423.4567 | 1990-01-03 | IT_PROG    |  9000.00 |           NULL |        102 |            60 |
|         104 | Bruce      | Ernst     | BERNST   | 590.423.4568 | 1991-05-21 | IT_PROG    |  6000.00 |           NULL |        103 |            60 |
|         105 | David      | Austin    | DAUSTIN  | 590.423.4569 | 1997-06-25 | IT_PROG    |  4800.00 |           NULL |        103 |            60 |
|         106 | Valli      | Pataballa | VPATABAL | 590.423.4560 | 1998-02-05 | IT_PROG    |  4800.00 |           NULL |        103 |            60 |
|         107 | Diana      | Lorentz   | DLORENTZ | 590.423.5567 | 1999-02-07 | IT_PROG    |  4200.00 |           NULL |        103 |            60 |
|         108 | Nancy      | Greenberg | NGREENBE | 515.124.4569 | 1994-08-17 | FI_MGR     | 12000.00 |           NULL |        101 |           100 |
|         109 | Daniel     | Faviet    | DFAVIET  | 515.124.4169 | 1994-08-16 | FI_ACCOUNT |  9000.00 |           NULL |        108 |           100 |
+-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM employees LIMIT 10, 10;
+-------------+-------------+------------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
| employee_id | first_name  | last_name  | email    | phone_number | hire_date  | job_id     | salary   | commission_pct | manager_id | department_id |
+-------------+-------------+------------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
|         110 | John        | Chen       | JCHEN    | 515.124.4269 | 1997-09-28 | FI_ACCOUNT |  8200.00 |           NULL |        108 |           100 |
|         111 | Ismael      | Sciarra    | ISCIARRA | 515.124.4369 | 1997-09-30 | FI_ACCOUNT |  7700.00 |           NULL |        108 |           100 |
|         112 | Jose Manuel | Urman      | JMURMAN  | 515.124.4469 | 1998-03-07 | FI_ACCOUNT |  7800.00 |           NULL |        108 |           100 |
|         113 | Luis        | Popp       | LPOPP    | 515.124.4567 | 1999-12-07 | FI_ACCOUNT |  6900.00 |           NULL |        108 |           100 |
|         114 | Den         | Raphaely   | DRAPHEAL | 515.127.4561 | 1994-12-07 | PU_MAN     | 11000.00 |           NULL |        100 |            30 |
|         115 | Alexander   | Khoo       | AKHOO    | 515.127.4562 | 1995-05-18 | PU_CLERK   |  3100.00 |           NULL |        114 |            30 |
|         116 | Shelli      | Baida      | SBAIDA   | 515.127.4563 | 1997-12-24 | PU_CLERK   |  2900.00 |           NULL |        114 |            30 |
|         117 | Sigal       | Tobias     | STOBIAS  | 515.127.4564 | 1997-07-24 | PU_CLERK   |  2800.00 |           NULL |        114 |            30 |
|         118 | Guy         | Himuro     | GHIMURO  | 515.127.4565 | 1998-11-15 | PU_CLERK   |  2600.00 |           NULL |        114 |            30 |
|         119 | Karen       | Colmenares | KCOLMENA | 515.127.4566 | 1999-08-10 | PU_CLERK   |  2500.00 |           NULL |        114 |            30 |
+-------------+-------------+------------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
10 rows in set (0.00 sec)

mysql>
  • 分页显式公式：（当前页数-1）*每页条数，每页条数
  • 注意：LIMIT 子句必须放在整个SELECT语句的最后！
  • 使用 LIMIT 的好处
    • 约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率
    • 如果我们知道返回结果只有1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回

2.3 拓展

  • 在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字，而且需要放到 SELECT 语句的最后面
  • 如果是 SQL Server 和 Access，需要使用 TOP 关键字
  • 如果是 DB2，使用 FETCH FIRST 5 ROWS ONLY 这样的关键字
  • 如果是 Oracle，你需要基于 ROWNUM 来统计行数