Mysql排序和分页(order by&limit)及存在的坑

排序查询(order by)

电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此时我们可以使用数据库中的排序功能来完成。

排序语法:

select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc];
  • 需要排序的字段跟在order by之后;
  • asc|desc表示排序的规则,asc:升序,desc:降序,默认为asc;
  • 支持多个字段进行排序,多字段排序之间用逗号隔开。

单字段排序

mysql> create table test2(a int,b varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test2 values (10,'jack'),(8,'tom'),(5,'ready'),(100,'javacode');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test2;
+------+----------+
| a  | b    |
+------+----------+
|  10 | jack   |
|  8 | tom   |
|  5 | ready  |
| 100 | javacode |
+------+----------+
4 rows in set (0.00 sec)
mysql> select * from test2 order by a asc;
+------+----------+
| a  | b    |
+------+----------+
|  5 | ready  |
|  8 | tom   |
|  10 | jack   |
| 100 | javacode |
+------+----------+
4 rows in set (0.00 sec)
mysql> select * from test2 order by a desc;
+------+----------+
| a  | b    |
+------+----------+
| 100 | javacode |
|  10 | jack   |
|  8 | tom   |
|  5 | ready  |
+------+----------+
4 rows in set (0.00 sec)
mysql> select * from test2 order by a;
+------+----------+
| a  | b    |
+------+----------+
|  5 | ready  |
|  8 | tom   |
|  10 | jack   |
| 100 | javacode |
+------+----------+
4 rows in set (0.00 sec)

多字段排序

比如学生表,先按学生年龄降序,年龄相同时,再按学号升序,如下:

mysql> create table stu(id int not null comment '学号' primary key,age tinyint not null comment '年龄',name varchar(16) comment '姓名');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into stu (id,age,name) values (1001,18,'路人甲Java'),(1005,20,'刘德华'),(1003,18,'张学友'),(1004,20,'张国荣'),(1010,19,'梁朝伟');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from stu;
+------+-----+---------------+
| id  | age | name     |
+------+-----+---------------+
| 1001 | 18 | 路人甲Java  |
| 1003 | 18 | 张学友    |
| 1004 | 20 | 张国荣    |
| 1005 | 20 | 刘德华    |
| 1010 | 19 | 梁朝伟    |
+------+-----+---------------+
5 rows in set (0.00 sec)
mysql> select * from stu order by age desc,id asc;
+------+-----+---------------+
| id  | age | name     |
+------+-----+---------------+
| 1004 | 20 | 张国荣    |
| 1005 | 20 | 刘德华    |
| 1010 | 19 | 梁朝伟    |
| 1001 | 18 | 路人甲Java  |
| 1003 | 18 | 张学友    |
+------+-----+---------------+
5 rows in set (0.00 sec)

按别名排序

mysql> select * from stu;
+------+-----+---------------+
| id  | age | name     |
+------+-----+---------------+
| 1001 | 18 | 路人甲Java  |
| 1003 | 18 | 张学友    |
| 1004 | 20 | 张国荣    |
| 1005 | 20 | 刘德华    |
| 1010 | 19 | 梁朝伟    |
+------+-----+---------------+
5 rows in set (0.00 sec)
mysql> select age '年龄',id as '学号' from stu order by 年龄 asc,学号 desc;
+--------+--------+
| 年龄  | 学号  |
+--------+--------+
|   18 |  1003 |
|   18 |  1001 |
|   19 |  1010 |
|   20 |  1005 |
|   20 |  1004 |
+--------+--------+

按函数排序

有学生表(id:编号,birth:出生日期,name:姓名),如下:

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE student (
  ->  id int(11) NOT NULL COMMENT '学号',
  ->  birth date NOT NULL COMMENT '出生日期',
  ->  name varchar(16) DEFAULT NULL COMMENT '姓名',
  ->  PRIMARY KEY (id)
  -> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student (id,birth,name) values (1001,'1990-10-10','路人甲Java'),(1005,'1960-03-01','刘德华'),(1003,'1960-08-16','张学友'),(1004,'1968-07-01','张国荣'),(1010,'1962-05-16','梁朝伟');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM student;
+------+------------+---------------+
| id  | birth   | name     |
+------+------------+---------------+
| 1001 | 1990-10-10 | 路人甲Java  |
| 1003 | 1960-08-16 | 张学友    |
| 1004 | 1968-07-01 | 张国荣    |
| 1005 | 1960-03-01 | 刘德华    |
| 1010 | 1962-05-16 | 梁朝伟    |
+------+------------+---------------+
5 rows in set (0.00 sec)

Mysql排序和分页(order by&limit)及存在的坑

扫一扫手机访问