ORDER BY 与 LIMIT

1. ORDER BY

我们希望查询课程表courses时，能对结果按照学生人数student_count进行排序，此时该如何操作呢？

SQL有ORDER BY关键字可以指定结果集按照一个列或者多个列的顺序进行排序

1.1 ORDER BY 实例 Ⅰ

查询课程表courses，对结果按照学生人数student_count排序

SELECT *
FROM courses
ORDER BY student_count;

执行输出结果

mysql> SELECT *
    -> FROM courses
    -> ORDER BY student_count;
+----+-------------------------+-------------------+-------------+------------+
| id | name                    | student_count | created_at | teacher_id |
+----+-------------------------+-------------------+-------------+------------+
| 10 | Object Oriented Design  |               300 | 2020-08-08  |          4 |
|  4 | Web                     |               340 | 2020-04-22  |          4 |
|  8 | Data Analysis           |               500 | 2019-07-12  |          1 |
|  5 | Big Data                |               700 | 2020-09-11  |          1 |
|  3 | Django                  |               780 | 2020-02-29  |          3 |
|  7 | Java P6+                |               780 | 2019-01-19  |          3 |
|  1 | Advanced Algorithms     |               880 | 2020-06-01  |          4 |
|  2 | System Design           |              1350 | 2020-07-18  |          3 |
|  6 | Artificial Intelligence |              1660 | 2018-05-13  |          3 |
| 12 | Dynamic Programming     |              2000 | 2018-08-18  |          1 |
+----+-------------------------+-------------------+-------------+------------+
10 rows in set (0.01 sec)

可以看出结果已经按照学生上课人数进行了从小到大排序，如果希望按学生人数从大到小排列，我们只需要在student_count后面加上DESC关键字，即

SELECT *
FROM courses
ORDER BY student_count DESC;

执行输出结果

mysql> SELECT *
    -> FROM courses
    -> ORDER BY student_count DESC;
+----+-------------------------+-------------------+-------------+------------+
| id | name                    | student_count | created_at | teacher_id |
+----+-------------------------+-------------------+-------------+------------+
| 12 | Dynamic Programming     |              2000 | 2018-08-18  |          1 |
|  6 | Artificial Intelligence |              1660 | 2018-05-13  |          3 |
|  2 | System Design           |              1350 | 2020-07-18  |          3 |
|  1 | Advanced Algorithms     |               880 | 2020-06-01  |          4 |
|  3 | Django                  |               780 | 2020-02-29  |          3 |
|  7 | Java P6+                |               780 | 2019-01-19  |          3 |
|  5 | Big Data                |               700 | 2020-09-11  |          1 |
|  8 | Data Analysis           |               500 | 2019-07-12  |          1 |
|  4 | Web                     |               340 | 2020-04-22  |          4 |
| 10 | Object Oriented Design  |               300 | 2020-08-08  |          4 |
+----+-------------------------+-------------------+-------------+------------+
10 rows in set (0.00 sec)

1.2 ORDER BY 实例 Ⅱ

查询课程表courses中的课程信息中teacher_id为1、2或3的课程的名称、教师id和创建时间，结果按照教师id排序，如果教师id相同，则按照创建课程时间排序

SELECT name,teacher_id,created_at
FROM courses
WHERE teacher_id in (1,2,3)
ORDER BY teacher_id,created_at;

注意：

这里ORDER BY要写在WHERE后面，先条件后排序，不然会报错

执行输出结果

mysql> SELECT name,teacher_id,created_at
    -> FROM courses
    -> WHERE teacher_id in (1,2,3)
    -> ORDER BY teacher_id,created_at;
+-------------------------+------------+-------------+
| name                    | teacher_id | created_at |
+-------------------------+------------+-------------+
| Dynamic Programming     |          1 | 2018-08-18  |
| Data Analysis           |          1 | 2019-07-12  |
| Big Data                |          1 | 2020-09-11  |
| Artificial Intelligence |          3 | 2018-05-13  |
| Java P6+                |          3 | 2019-01-19  |
| Django                  |          3 | 2020-02-29  |
| System Design           |          3 | 2020-07-18  |
+-------------------------+------------+-------------+
7 rows in set (0.00 sec)

可以发现结果先按照teacher_id进行排序，如果teacher_id相同则按照创建时间排序

1.3 ORDER BY 总结

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

ORDER BY 关键字默认按照升序对记录进行排序，关键字ASC可以省去不写。

如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

语法

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

练习题：ORDER BY

题目描述：查询教师表中，国籍为中国的教师信息，结果按照年龄排序

SELECT *
FROM teachers
WHERE country = 'CN'
ORDER BY age;

目标输出结果

mysql> SELECT *
    -> FROM teachers
    -> WHERE country = 'CN'
    -> ORDER BY age;
+----+-----------------+------------------------+-----+---------+
| id | name            | email                  | age | country |
+----+-----------------+------------------------+-----+---------+
|  5 | Linghu Chong    | NULL                   |  18 | CN      |
|  2 | Northern Beggar | [e-mail] |  21 | CN      |
+----+-----------------+------------------------+-----+---------+
2 rows in set (0.00 sec)

2. LIMIT

之前我们的学习都是对列的条件约束，可是当我们数据非常多时，如果只希望返回有限个数的数据，应该如何操作呢？

SQL中的LIMIT可以帮我们实现该功能

2.1 LIMIT 实例

查询课程表course中学生人数student_count最少的三门课程信息

SELECT *
FROM courses
ORDER BY student_count
LIMIT 3;

注意

这里LIMIT的位置，在ORDER BY后面，否则会报错

执行输出结果

mysql> SELECT *
    -> FROM courses
    -> ORDER BY student_count
    -> LIMIT 3;
+----+------------------------+-------------------+-------------+------------+
| id | name                   | student_count | created_at | teacher_id |
+----+------------------------+-------------------+-------------+------------+
| 10 | Object Oriented Design |               300 | 2020-08-08  |          4 |
|  4 | Web                    |               340 | 2020-04-22  |          4 |
|  8 | Data Analysis          |               500 | 2019-07-12  |          1 |
+----+------------------------+-------------------+-------------+------------+
3 rows in set (0.01 sec)

练习题：LIMIT

题目描述：查询教师国籍为中国的教师中，年龄最大的教师

SELECT *
FROM teachers
WHERE country = 'CN'
ORDER BY age desc
LIMIT 1;

目标输出结果

mysql> SELECT *
    -> FROM teachers
    -> WHERE country = 'CN'
    -> ORDER BY age desc
    -> LIMIT 1;
+----+-----------------+------------------------+-----+---------+
| id | name            | email                  | age | country |
+----+-----------------+------------------------+-----+---------+
|  2 | Northern Beggar | [e-mail] |  21 | CN      |
+----+-----------------+------------------------+-----+---------+
1 row in set (0.01 sec)