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

来源:互联网 时间:1970-01-01

本文介绍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


相关阅读:
Top