概述
数据库中的数据直接呈现出来一般不是我们想要的,所以我们上两节演示了如何对数据进行过滤的方法。除了对数据进行过滤,
我们可能还需要对数据进行排序,比如想从列表中了解消费最高的项,就可能需要对金额字段做降序排序,想看年龄从小到大的分布情况,就可能需要对user表的age字段进行升序排序。
也可能需要对数据进行限制,比如我们需要对付款的1~10,11~20,21~30 名的用户分别赠予不同的礼品,这时候对数据的限制就很有用了。
备注:下面脚本中[]包含的表示可选,| 分隔符表示可选其一。
数据排序 order by
语法格式如下:
1、需要排序的字段跟在order by之后;
2、asc 和 desc表示排序的规则,asc:升序,desc:降序,默认为升序 asc;
3、排序可以指定多次字段,多字段排序之间用逗号隔开。
4、多字段排序中,越靠前优先级越高,下面中cname1优先排序,当cname1等值的时候,cname2开始排序,直至所有字段都排序完。
select cname from tname order by cname1 [asc|desc],cname2 [asc|desc]...;
单个字段排序
举个例子,在销售额中通按照交易的订单进行金额额度降序的方式显示:
mysql> select * from t_order; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 8 | brand | 52.2 | 2 | | 9 | hen | 1752.02 | 7 | | 10 | helyn | 88.5 | 4 | | 11 | sol | 1007.9 | 11 | | 12 | diny | 12 | 1 | | 13 | weng | 52.2 | 5 | | 14 | sally | 99.71 | 9 | +---------+---------+---------+-------+ 7 rows in set mysql> select * from t_order order by amount desc; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 9 | hen | 1752.02 | 7 | | 11 | sol | 1007.9 | 11 | | 14 | sally | 99.71 | 9 | | 10 | helyn | 88.5 | 4 | | 8 | brand | 52.2 | 2 | | 13 | weng | 52.2 | 5 | | 12 | diny | 12 | 1 | +---------+---------+---------+-------+ 7 rows in set
多个字段排序
多个字段排序用逗号隔开,优先级从左到右逐次递减,如下图,如果金额一致,则按照购买商品数量从多到少排序:
mysql> select * from t_order order by amount desc,goods desc; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 9 | hen | 1752.02 | 7 | | 11 | sol | 1007.9 | 11 | | 14 | sally | 99.71 | 9 | | 10 | helyn | 88.5 | 4 | | 13 | weng | 52.2 | 5 | | 8 | brand | 52.2 | 2 | | 12 | diny | 12 | 1 | +---------+---------+---------+-------+ 7 rows in set
按alias排序
按照别名排序或者做条件查询的目的都是为了简化代码,方便使用,别名可以是英文,也可以是中文:
mysql> select account as ac,amount as am,goods as gd from t_order order by am,gd desc; +-------+---------+----+ | ac | am | gd | +-------+---------+----+ | diny | 12 | 1 | | weng | 52.2 | 5 | | brand | 52.2 | 2 | | helyn | 88.5 | 4 | | sally | 99.71 | 9 | | sol | 1007.9 | 11 | | hen | 1752.02 | 7 | +-------+---------+----+ 7 rows in set
字段排序中使用函数
下面使用了abs取绝对值函数,所以在 am字段降序排序中,-99.99 排在 99.71之上。
mysql> select * from t_order; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 8 | brand | 52.2 | 2 | | 9 | hen | 1752.02 | 7 | | 10 | helyn | 88.5 | 4 | | 11 | sol | 1007.9 | 11 | | 12 | diny | 12 | 1 | | 13 | weng | 52.2 | 5 | | 14 | sally | 99.71 | 9 | | 15 | brand1 | -99.99 | 5 | +---------+---------+---------+-------+ 8 rows in set mysql> select account as ac,amount as am,goods as gd from t_order order by abs(am) desc; +--------+---------+----+ | ac | am | gd | +--------+---------+----+ | hen | 1752.02 | 7 | | sol | 1007.9 | 11 | | brand1 | -99.99 | 5 | | sally | 99.71 | 9 | | helyn | 88.5 | 4 | | brand | 52.2 | 2 | | weng | 52.2 | 5 | | diny | 12 | 1 | +--------+---------+----+ 8 rows in set
与Where条件结合使用
order 在 where 条件之后,根据where已经过滤好的数据再进行排序。下面是过滤出购买金额>80 且 购买数量>5的数据,并且按照价格降序排序。
mysql> select * from t_order; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 8 | brand | 52.2 | 2 | | 9 | hen | 1752.02 | 7 | | 10 | helyn | 88.5 | 4 | | 11 | sol | 1007.9 | 11 | | 12 | diny | 12 | 1 | | 13 | weng | 52.2 | 5 | | 14 | sally | 99.71 | 9 | | 15 | brand1 | -99.99 | 5 | +---------+---------+---------+-------+ 8 rows in set mysql> select * from t_order where amount>80 and goods>5 order by amount desc; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 9 | hen | 1752.02 | 7 | | 11 | sol | 1007.9 | 11 | | 14 | sally | 99.71 | 9 | +---------+---------+---------+-------+
数据limit
很多时候我们过滤出符合要求的数据之后,还需要得到这些数据中的某一个具体区间,比如对付款超过1000的用户的第1~10,11~20,21~30 名分别赠予不同的礼品,这时候就要使用limit操作了。
limit用来限制select查询返回的数据,常用于数据排行或者分页等情况。
语法格式如下:
select cname from tname limit [offset,] count;
1、offset表示偏移量,就是指跳过的行数,可以省略不写,默认为0,表示跳过0行,如 limit 8 等同于 limit 0,8。
2、count:跳过偏移量offset之后开始取的数据行数,有count行。
3、limit中offset和count的值不能用表达式。
获取前n条记录
如下图,limit n 和 limit 0,n 是一致的:
mysql> select * from t_order; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 8 | brand | 52.2 | 2 | | 9 | hen | 1752.02 | 7 | | 10 | helyn | 88.5 | 4 | | 11 | sol | 1007.9 | 11 | | 12 | diny | 12 | 1 | | 13 | weng | 52.2 | 5 | | 14 | sally | 99.71 | 9 | | 15 | brand1 | -99.99 | 5 | +---------+---------+---------+-------+ 8 rows in set mysql> select * from t_order limit 2 ; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 8 | brand | 52.2 | 2 | | 9 | hen | 1752.02 | 7 | +---------+---------+---------+-------+ 2 rows in set mysql> select * from t_order limit 0,2; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 8 | brand | 52.2 | 2 | | 9 | hen | 1752.02 | 7 | +---------+---------+---------+-------+ 2 rows in set
limit限制单条记录
这边我们获取支付金额中最大和最小的的一条记录。可以先使用 order 条件进行排序,然后limit 第1条记录即可:
mysql> select * from t_order; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 8 | brand | 52.2 | 2 | | 9 | hen | 1752.02 | 7 | | 10 | helyn | 88.5 | 4 | | 11 | sol | 1007.9 | 11 | | 12 | diny | 12 | 1 | | 13 | weng | 52.2 | 5 | | 14 | sally | 99.71 | 9 | | 15 | brand1 | -99.99 | 5 | +---------+---------+---------+-------+ 8 rows in set mysql> select * from t_order where amount>0 order by amount desc limit 1; +---------+---------+---------+-------+ | orderid | account | amount | goods | +---------+---------+---------+-------+ | 9 | hen | 1752.02 | 7 | +---------+---------+---------+-------+ 1 row in set mysql> select * from t_order where amount>0 order by amount asc limit 1; +---------+---------+--------+-------+ | orderid | account | amount | goods | +---------+---------+--------+-------+ | 12 | diny | 12 | 1 | +---------+---------+--------+-------+ 1 row in set
以上就是MySQL 查询的排序、分页相关的详细内容,更多关于MySQL 查询的资料请关注其它相关文章!
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]