MySQL讲义第23讲——select 查询之ORDER BY

讲义第23讲——select 查询之ORDER BY"/>

MySQL讲义第23讲——select 查询之ORDER BY

MySQL讲义第23讲——select 查询之ORDER BY

文章目录

• MySQL讲义第23讲——select 查询之ORDER BY
• • 一、按单个字段排序
  • • • 1、查询所有学生信息，按姓名排序
      • 2、查询系编号为【D01】的学生信息，查询结果按 height 降序排列
      • 3、查询所有学生的信息，并且按学生的年龄排序
  • 二、按多个字段排序
  • • • 1、查询所有的学生信息，查询结果按 dept_id 和 height 排序
      • 2、查询所有的学生信息，查询结果先按 dept_id 升序排列，再按 stu_id 降序排列

ORDER BY 语句用于对查询结果进行排序。默认按照升序对记录进行排序，如果希望按照降序对记录进行排序，可以使用 DESC 关键字。语法格式如下：

ORDER BY
字段名或表达式 [DESC] [,...]

一、按单个字段排序

1、查询所有学生信息，按姓名排序

mysql> SELECT-> *-> FROM-> stu-> ORDER BY-> convert(stu_name using gbk);
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801103 | 巩莉      | 女     | 2000-06-18 00:00:00 |  170.0 | 15937320456 | D02     |
| 201901004 | 李刚      | 男     | 2000-12-25 00:00:00 |  178.0 | 15937320321 | D01     |
| 201801102 | 刘国强    | 男     | 2000-08-14 00:00:00 |  174.0 | 15937320789 | D02     |
| 201901005 | 刘鹏      | 男     | 2001-12-18 00:00:00 |  176.0 | NULL        | D01     |
| 201801104 | 宋丹风    | 女     | 1999-11-20 00:00:00 |  165.0 | 15937320444 | D02     |
| 201901002 | 王宏伟    | 男     | 2001-02-15 00:00:00 |  180.0 | 15937320255 | D01     |
| 201801203 | 王鹏飞    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
| 201801201 | 王艳艳    | 女     | 1999-09-30 00:00:00 |  162.0 | NULL        | NULL    |
| 201801101 | 王占峰    | 男     | 1999-12-30 00:00:00 |  177.0 | 15937320987 | D02     |
| 201901003 | 张静静    | 女     | 2001-08-17 00:00:00 |  167.0 | 15937320123 | D01     |
| 201801202 | 赵牡丹    | 女     | 2001-08-10 00:00:00 |  160.0 | 15937320666 | D03     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
11 rows in set (0.05 sec)

2、查询系编号为【D01】的学生信息，查询结果按 height 降序排列

mysql> SELECT-> *-> FROM-> stu-> WHERE-> dept_id = 'D01'-> ORDER BY-> height DESC;
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201901002 | 王宏伟    | 男     | 2001-02-15 00:00:00 |  180.0 | 15937320255 | D01     |
| 201901004 | 李刚      | 男     | 2000-12-25 00:00:00 |  178.0 | 15937320321 | D01     |
| 201901005 | 刘鹏      | 男     | 2001-12-18 00:00:00 |  176.0 | NULL        | D01     |
| 201901003 | 张静静    | 女     | 2001-08-17 00:00:00 |  167.0 | 15937320123 | D01     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
4 rows in set (0.02 sec)

3、查询所有学生的信息，并且按学生的年龄排序

mysql> SELECT-> stu_id,-> stu_name,-> birth,-> year(now())-year(birth) as age-> FROM -> stu-> ORDER BY 4;
+-----------+-----------+---------------------+------+
| stu_id    | stu_name  | birth               | age  |
+-----------+-----------+---------------------+------+
| 201801202 | 赵牡丹    | 2001-08-10 00:00:00 |   19 |
| 201901002 | 王宏伟    | 2001-02-15 00:00:00 |   19 |
| 201901003 | 张静静    | 2001-08-17 00:00:00 |   19 |
| 201901005 | 刘鹏      | 2001-12-18 00:00:00 |   19 |
| 201801102 | 刘国强    | 2000-08-14 00:00:00 |   20 |
| 201801103 | 巩莉      | 2000-06-18 00:00:00 |   20 |
| 201801203 | 王鹏飞    | 2000-10-19 00:00:00 |   20 |
| 201901004 | 李刚      | 2000-12-25 00:00:00 |   20 |
| 201801101 | 王占峰    | 1999-12-30 00:00:00 |   21 |
| 201801104 | 宋丹风    | 1999-11-20 00:00:00 |   21 |
| 201801201 | 王艳艳    | 1999-09-30 00:00:00 |   21 |
+-----------+-----------+---------------------+------+
11 rows in set (0.00 sec)mysql> SELECT-> stu_id,-> stu_name,-> birth-> FROM -> stu-> ORDER BY year(now())-year(birth);
+-----------+-----------+---------------------+
| stu_id    | stu_name  | birth               |
+-----------+-----------+---------------------+
| 201801202 | 赵牡丹    | 2001-08-10 00:00:00 |
| 201901002 | 王宏伟    | 2001-02-15 00:00:00 |
| 201901003 | 张静静    | 2001-08-17 00:00:00 |
| 201901005 | 刘鹏      | 2001-12-18 00:00:00 |
| 201801102 | 刘国强    | 2000-08-14 00:00:00 |
| 201801103 | 巩莉      | 2000-06-18 00:00:00 |
| 201801203 | 王鹏飞    | 2000-10-19 00:00:00 |
| 201901004 | 李刚      | 2000-12-25 00:00:00 |
| 201801101 | 王占峰    | 1999-12-30 00:00:00 |
| 201801104 | 宋丹风    | 1999-11-20 00:00:00 |
| 201801201 | 王艳艳    | 1999-09-30 00:00:00 |
+-----------+-----------+---------------------+
11 rows in set (0.00 sec)

二、按多个字段排序

如果按多个字段排序，则先按第一个字段排序，然后针对第一个字段的重复记录再按第二个字段排序，以此类推。

1、查询所有的学生信息，查询结果按 dept_id 和 height 排序

mysql> SELECT-> *-> FROM -> stu-> ORDER BY-> dept_id,height;
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801201 | 王艳艳    | 女     | 1999-09-30 00:00:00 |  162.0 | NULL        | NULL    |
| 201901003 | 张静静    | 女     | 2001-08-17 00:00:00 |  167.0 | 15937320123 | D01     |
| 201901005 | 刘鹏      | 男     | 2001-12-18 00:00:00 |  176.0 | NULL        | D01     |
| 201901004 | 李刚      | 男     | 2000-12-25 00:00:00 |  178.0 | 15937320321 | D01     |
| 201901002 | 王宏伟    | 男     | 2001-02-15 00:00:00 |  180.0 | 15937320255 | D01     |
| 201801104 | 宋丹风    | 女     | 1999-11-20 00:00:00 |  165.0 | 15937320444 | D02     |
| 201801103 | 巩莉      | 女     | 2000-06-18 00:00:00 |  170.0 | 15937320456 | D02     |
| 201801102 | 刘国强    | 男     | 2000-08-14 00:00:00 |  174.0 | 15937320789 | D02     |
| 201801101 | 王占峰    | 男     | 1999-12-30 00:00:00 |  177.0 | 15937320987 | D02     |
| 201801202 | 赵牡丹    | 女     | 2001-08-10 00:00:00 |  160.0 | 15937320666 | D03     |
| 201801203 | 王鹏飞    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
11 rows in set (0.00 sec)

2、查询所有的学生信息，查询结果先按 dept_id 升序排列，再按 stu_id 降序排列

mysql> SELECT-> *-> FROM-> stu-> ORDER BY-> dept_id, stu_id DESC;
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801201 | 王艳艳    | 女     | 1999-09-30 00:00:00 |  162.0 | NULL        | NULL    |
| 201901005 | 刘鹏      | 男     | 2001-12-18 00:00:00 |  176.0 | NULL        | D01     |
| 201901004 | 李刚      | 男     | 2000-12-25 00:00:00 |  178.0 | 15937320321 | D01     |
| 201901003 | 张静静    | 女     | 2001-08-17 00:00:00 |  167.0 | 15937320123 | D01     |
| 201901002 | 王宏伟    | 男     | 2001-02-15 00:00:00 |  180.0 | 15937320255 | D01     |
| 201801104 | 宋丹风    | 女     | 1999-11-20 00:00:00 |  165.0 | 15937320444 | D02     |
| 201801103 | 巩莉      | 女     | 2000-06-18 00:00:00 |  170.0 | 15937320456 | D02     |
| 201801102 | 刘国强    | 男     | 2000-08-14 00:00:00 |  174.0 | 15937320789 | D02     |
| 201801101 | 王占峰    | 男     | 1999-12-30 00:00:00 |  177.0 | 15937320987 | D02     |
| 201801203 | 王鹏飞    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
| 201801202 | 赵牡丹    | 女     | 2001-08-10 00:00:00 |  160.0 | 15937320666 | D03     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
11 rows in set (0.00 sec)