转载

[mysql基础文档]-27-order by&limit排序

本文介绍MySQL中最常用的两种排序order by以及limit。

文章目录

[1].order by排序实例
[2].limit排序实例

[1].order by排序实例

本文所使用的数据表为“[mysql基础文档]-24-select查询基础”第一部分所创建的cellphone表，请参考：练习数据表

order by能对查询结果进行排序，可用参数如下

desc 降序
asc 升序

默认使用asc升序排列，请看下面的实例

//order by后面接用来排序的列名称，省略参数默认就是asc升序排列 mysql> select goods_id,goods_name from cellphone where goods_id between 1 and 5 order by goods_id; +----------+-------------+ | goods_id | goods_name  | +----------+-------------+ |        1 | hongMI2a    | |        2 | MInote      | |        3 | hongMI2     | |        4 | MI4         | |        5 | GalaxyN9200 | +----------+-------------+  //使用降序排列 mysql> select goods_id,goods_name from cellphone where goods_id between 1 and 5 order by goods_id desc; +----------+-------------+ | goods_id | goods_name  | +----------+-------------+ |        5 | GalaxyN9200 | |        4 | MI4         | |        3 | hongMI2     | |        2 | MInote      | |        1 | hongMI2a    | +----------+-------------+  //如果在排列时遇到两个相同值，并且如果设定了次级比较列，则再对次级比较列大小进行排序 //order by会首先根据第一个sale_price desc，对sale_price进行降序排列，遇到价格相同的值时，再使用次级goods_id desc，让goods_id比较大的排在前面，以此类推，如果goods_id也具有相同的值，再添加次级 mysql> select goods_id,goods_name,sale_price from cellphone order by sale_price desc,goods_id desc; +----------+--------------+------------+ | goods_id | goods_name   | sale_price | +----------+--------------+------------+ |        5 | GalaxyN9200  |    5388.00 | |       17 | iPhone6s     |    5288.00 | |        9 | GalaxyG9250  |    5288.00 | |       19 | GalaxyS6     |    4499.00 | |        6 | iPhone6A1586 |    4288.00 | |       12 | iPhone5s     |    3188.00 | |       13 | GalaxyN9109W |    2698.00 | |       20 | Huawei7      |    2499.00 | |       11 | MX5          |    1899.00 | |        2 | MInote       |    1799.00 | |       18 | MX4Pro       |    1599.00 | |       16 | Huawei6      |    1499.00 | |        4 | MI4          |    1499.00 | |       14 | MX4          |    1399.00 | |       15 | iPhone4s     |    1398.00 | |        8 | Huawei4X     |     999.00 | |        7 | MeizuNote2   |     899.00 | |       10 | Huawei4A     |     699.00 | |        3 | hongMI2      |     699.00 | |        1 | hongMI2a     |     549.00 | +----------+--------------+------------+

[2].limit排序实例

limit用法：limit 从第几行开始取,取几行

请看下面的实例:

//使用打折价格列排序，limit从排序后的结果集中第0行开始，向下取出三行显示（结果集中的第1行对应limit的第0行） mysql> select goods_id,goods_name,sale_price from cellphone order by sale_price limit 0,3; +----------+------------+------------+ | goods_id | goods_name | sale_price | +----------+------------+------------+ |        1 | hongMI2a   |     549.00 | |       10 | Huawei4A   |     699.00 | |        3 | hongMI2    |     699.00 | +----------+------------+------------+  //取出最贵的三行商品 mysql> select goods_id,goods_name,sale_price from cellphone order by sale_price desc,goods_id desc limit 0,3; +----------+-------------+------------+ | goods_id | goods_name  | sale_price | +----------+-------------+------------+ |        5 | GalaxyN9200 |    5388.00 | |       17 | iPhone6s    |    5288.00 | |        9 | GalaxyG9250 |    5288.00 | +----------+-------------+------------+  //使用goods_id排序，并且从结果第3行开始取，取4行显示（还记得limit和实际结果集的那1行偏移量吧，limit是从0开始的） mysql> select goods_id,goods_name from cellphone order by goods_id limit 2,4; +----------+--------------+ | goods_id | goods_name   | +----------+--------------+ |        3 | hongMI2      | |        4 | MI4          | |        5 | GalaxyN9200  | |        6 | iPhone6A1586 | +----------+--------------+

P.s：limit只能在MySQL下使用，Oracle没有这个命令。

[**] 注:如文中未特别声明转载请注明出自:QingSword.COM

正文到此结束