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 来统计行数