Mysql笔记：第05章_排序与分页

第05章_排序与分页

1. 排序数据

1.1 排序规则

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

1.2 单列排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
/*部分输出：
+-------------+------------+---------------+------------+
| last_name   | job_id     | department_id | hire_date  |
+-------------+------------+---------------+------------+
| King        | AD_PRES    |            90 | 1987-06-17 |
| Whalen      | AD_ASST    |            10 | 1987-09-17 |
| Kochhar     | AD_VP      |            90 | 1989-09-21 |
| Hunold      | IT_PROG    |            60 | 1990-01-03 |
| Ernst       | IT_PROG    |            60 | 1991-05-21 |
| De Haan     | AD_VP      |            90 | 1993-01-13 |            
*/

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
/*部分输出：
+-------------+------------+---------------+------------+
| last_name   | job_id     | department_id | hire_date  |
+-------------+------------+---------------+------------+
| Banda       | SA_REP     |            80 | 2000-04-21 |
| Kumar       | SA_REP     |            80 | 2000-04-21 |
| Ande        | SA_REP     |            80 | 2000-03-24 |
| Markle      | ST_CLERK   |            50 | 2000-03-08 |
| Lee         | SA_REP     |            80 | 2000-02-23 |
| Philtanker  | ST_CLERK   |            50 | 2000-02-06 |
*/
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;
/*部分输出：
+-------------+-------------+-----------+
| employee_id | last_name   | annsal    |
+-------------+-------------+-----------+
|         132 | Olson       |  25200.00 |
|         128 | Markle      |  26400.00 |
|         136 | Philtanker  |  26400.00 |
|         127 | Landry      |  28800.00 |
|         135 | Gee         |  28800.00 |
|         119 | Colmenares  |  30000.00 |
*/
1.3 多列排序
  • 可以使用不在SELECT列表中的列排序。
  • 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 |
*/
排序演示代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
#1. 排序

# 如果没有使用排序操作，默认情况下查询返回的数据是按照添加数据的顺序显示的。
SELECT * FROM employees;


# 1.1 基本使用
# 使用 ORDER BY 对查询到的数据进行排序操作。
# 升序：ASC (ascend)
# 降序：DESC (descend)

# 练习：按照salary从高到低的顺序显示员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;
/*部分输出：
+-------------+-------------+----------+
| employee_id | last_name   | salary   |
+-------------+-------------+----------+
|         100 | King        | 24000.00 |
|         101 | Kochhar     | 17000.00 |
|         102 | De Haan     | 17000.00 |
|         145 | Russell     | 14000.00 |
|         146 | Partners    | 13500.00 |
*/


# 练习：按照salary从低到高的顺序显示员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;
/*部分输出：
+-------------+-------------+----------+
| employee_id | last_name   | salary   |
+-------------+-------------+----------+
|         132 | Olson       |  2100.00 |
|         128 | Markle      |  2200.00 |
|         136 | Philtanker  |  2200.00 |
|         127 | Landry      |  2400.00 |
|         135 | Gee         |  2400.00 |
|         119 | Colmenares  |  2500.00 |
|         131 | Marlow      |  2500.00 |
*/


# 如果在ORDER BY 后没有显式指名排序的方式的话，则默认按照升序排列。
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary; 
/*部分输出
+-------------+-------------+----------+
| employee_id | last_name   | salary   |
+-------------+-------------+----------+
|         132 | Olson       |  2100.00 |
|         128 | Markle      |  2200.00 |
|         136 | Philtanker  |  2200.00 |
|         127 | Landry      |  2400.00 |
|         135 | Gee         |  2400.00 |
|         119 | Colmenares  |  2500.00 |
|         131 | Marlow      |  2500.00 |
*/


#2. 我们可以使用列的别名，进行排序
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
/*部分输出
+-------------+----------+------------+
| employee_id | salary   | annual_sal |
+-------------+----------+------------+
|         132 |  2100.00 |   25200.00 |
|         128 |  2200.00 |   26400.00 |
|         136 |  2200.00 |   26400.00 |
|         127 |  2400.00 |   28800.00 |
|         135 |  2400.00 |   28800.00 |
*/

#列的别名只能在 ORDER BY 中使用，不能在WHERE中使用。
#如下操作报错！
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600;


#3. 强调格式：WHERE 需要声明在FROM后，ORDER BY之前。
#先用where过滤，再用ORDER BY排序
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;
/*部分输出：
+-------------+----------+
| employee_id | salary   |
+-------------+----------+
|         204 | 10000.00 |
|         103 |  9000.00 |
|         104 |  6000.00 |
|         105 |  4800.00 |
|         106 |  4800.00 |
|         107 |  4200.00 |
*/



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


#练习：显示员工信息，先按照department_id的降序排列，
#再salary的升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;
/*部分输出：
+-------------+----------+---------------+
| employee_id | salary   | department_id |
+-------------+----------+---------------+
|         206 |  8300.00 |           110 |
|         205 | 12000.00 |           110 |
|         113 |  6900.00 |           100 |
|         111 |  7700.00 |           100 |
|         112 |  7800.00 |           100 |
|         110 |  8200.00 |           100 |
|         109 |  9000.00 |           100 |
*/

2. 分页

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

分页原理
所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
MySQL中使用 LIMIT 实现分页
格式：
LIMIT 位置偏移量, 行数
第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。
举例

–前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
–第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
–第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT4,3;”返回的结果相同。

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

SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;

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

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

  • 如果是 SQL Server 和 Access，需要使用 TOP 关键字，比如：
1
2
SELECT TOP 5 name, hp_max FROM heros 
ORDER BY hp_max DESC
  • 如果是 DB2，使用 FETCH FIRST 5 ROWS ONLY 这样的关键字：
1
2
3
SELECT name, hp_max 
FROM heros 
ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
  • 如果是 Oracle，你需要基于 ROWNUM 来统计行数：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT rownum,last_name,salary 
FROM employees 
WHERE rownum < 5 ORDER BY salary DESC;
#需要说明的是，这条语句是先取出来前 5 条数据行，然后再按照 hp_max 
#从高到低的顺序进行排序。但这样产生的结果和上述方法的并不一样。
#在后面讲到子查询，你可以使用

SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE rownum < 10;
#得到与上述方法一致的结果。
分页演示代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
#2. 分页
#2.1 mysql使用limit实现数据的分页显示

# 需求1：每页显示20条记录，此时显示第1页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;#0是偏移量，20是显示的条目数
/*输出：
+-------------+------------+
| employee_id | last_name  |
+-------------+------------+
|         100 | King       |
|         101 | Kochhar    |
|         102 | De Haan    |
|         103 | Hunold     |
|         104 | Ernst      |
|         105 | Austin     |
|         106 | Pataballa  |
|         107 | Lorentz    |
|         108 | Greenberg  |
|         109 | Faviet     |
|         110 | Chen       |
|         111 | Sciarra    |
|         112 | Urman      |
|         113 | Popp       |
|         114 | Raphaely   |
|         115 | Khoo       |
|         116 | Baida      |
|         117 | Tobias     |
|         118 | Himuro     |
|         119 | Colmenares |
+-------------+------------+
20 rows in set (0.00 sec)
*/


# 需求2：每页显示20条记录，此时显示第2页
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;#偏移量是20，显示条目数是20
/*输出
+-------------+-------------+
| employee_id | last_name   |
+-------------+-------------+
|         120 | Weiss       |
|         121 | Fripp       |
|         122 | Kaufling    |
|         123 | Vollman     |
|         124 | Mourgos     |
|         125 | Nayer       |
|         126 | Mikkilineni |
|         127 | Landry      |
|         128 | Markle      |
|         129 | Bissot      |
|         130 | Atkinson    |
|         131 | Marlow      |
|         132 | Olson       |
|         133 | Mallin      |
|         134 | Rogers      |
|         135 | Gee         |
|         136 | Philtanker  |
|         137 | Ladwig      |
|         138 | Stiles      |
|         139 | Seo         |
+-------------+-------------+
20 rows in set (0.00 sec)
*/


# 需求3：每页显示20条记录，此时显示第3页
SELECT employee_id,last_name
FROM employees
LIMIT 40,20;

#需求：每页显示pageSize条记录，此时显示第pageNo页：
#公式：LIMIT (pageNo-1) * pageSize,pageSize;



#2.2 WHERE ... ORDER BY ...LIMIT 声明顺序如下：
# LIMIT的格式： 严格来说：LIMIT 位置偏移量,条目数
# 结构"LIMIT 0,条目数" 等价于 "LIMIT 条目数"
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;
/*输出：
+-------------+-----------+----------+
| employee_id | last_name | salary   |
+-------------+-----------+----------+
|         100 | King      | 24000.00 |
|         101 | Kochhar   | 17000.00 |
|         102 | De Haan   | 17000.00 |
|         145 | Russell   | 14000.00 |
|         146 | Partners  | 13500.00 |
|         201 | Hartstein | 13000.00 |
|         108 | Greenberg | 12000.00 |
|         147 | Errazuriz | 12000.00 |
|         205 | Higgins   | 12000.00 |
|         168 | Ozer      | 11500.00 |
+-------------+-----------+----------+
10 rows in set (0.00 sec)
*/



#练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;
/*输出：
+-------------+-----------+
| employee_id | last_name |
+-------------+-----------+
|         131 | Marlow    |
|         132 | Olson     |
+-------------+-----------+
2 rows in set (0.00 sec)
*/


#2.3 MySQL8.0新特性：LIMIT ... OFFSET ...

#练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;#偏移量是31，显示条目为2条
/*输出
+-------------+-----------+
| employee_id | last_name |
+-------------+-----------+
|         131 | Marlow    |
|         132 | Olson     |
+-------------+-----------+
*/


#练习：查询员工表中工资最高的员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;
/*输出
+-------------+-----------+----------+
| employee_id | last_name | salary   |
+-------------+-----------+----------+
|         100 | King      | 24000.00 |
+-------------+-----------+----------+
*/

#2.4 LIMIT 可以使用在MySQL、PGSQL、MariaDB、SQLite 等数据库中使用，表示分页。
# 不能使用在SQL Server、DB2、Oracle！

3.课后练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
#第05章_排序与分页的课后练习


#1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示 

SELECT last_name,department_id,salary * 12 annual_salary
FROM employees
ORDER BY annual_salary DESC,last_name ASC;
/*部分输出
+-------------+---------------+---------------+
| last_name   | department_id | annual_salary |
+-------------+---------------+---------------+
| King        |            90 |     288000.00 |
| De Haan     |            90 |     204000.00 |
| Kochhar     |            90 |     204000.00 |
| Russell     |            80 |     168000.00 |
| Partners    |            80 |     162000.00 |
| Hartstein   |            20 |     156000.00 |
| Errazuriz   |            80 |     144000.00 |
*/

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

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;
/*输出
+-----------+---------+
| last_name | salary  |
+-----------+---------+
| Ernst     | 6000.00 |
| Fay       | 6000.00 |
| Mourgos   | 5800.00 |
| Austin    | 4800.00 |
| Pataballa | 4800.00 |
| Whalen    | 4400.00 |
| Lorentz   | 4200.00 |
| Sarchand  | 4200.00 |
| Bull      | 4100.00 |
| Bell      | 4000.00 |
| Everett   | 3900.00 |
| Chung     | 3800.00 |
| Dilly     | 3600.00 |
| Ladwig    | 3600.00 |
| Rajs      | 3500.00 |
| Dellinger | 3400.00 |
| Mallin    | 3300.00 |
| Bissot    | 3300.00 |
| Taylor    | 3200.00 |
| Stiles    | 3200.00 |
+-----------+---------+
*/


#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT employee_id,last_name,email,department_id
FROM employees
#where email like '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id;
/*部分输出
+-------------+------------+----------+---------------+
| employee_id | last_name  | email    | department_id |
+-------------+------------+----------+---------------+
|         201 | Hartstein  | MHARTSTE |            20 |
|         114 | Raphaely   | DRAPHEAL |            30 |
|         119 | Colmenares | KCOLMENA |            30 |
|         186 | Dellinger  | JDELLING |            50 |
|         191 | Perkins    | RPERKINS |            50 |
|         193 | Everett    | BEVERETT |            50 |
|         198 | OConnell   | DOCONNEL |            50 |
*/