数据库中的数据直接呈现出来一般不是我们想要的,所以我们上两节演示了如何对数据进行过滤的方法。除了对数据进行过滤,
我们可能还需要对数据进行排序,比如想从列表中了解消费最高的项,就可能需要对金额字段做降序排序,想看年龄从小到大的分布情况,就可能需要对user表的age字段进行升序排序。
也可能需要对数据进行限制,比如我们需要对付款的1~10,11~20,21~30 名的用户分别赠予不同的礼品,这时候对数据的限制就很有用了。
备注:下面脚本中[]包含的表示可选,| 分隔符表示可选其一。
语法格式如下:
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]...;
举个例子,在销售额中通按照交易的订单进行金额额度降序的方式显示:
1 mysql> select * from t_order; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount | goods | 4 +---------+---------+---------+-------+ 5 | 8 | brand | 52.2 | 2 | 6 | 9 | hen | 1752.02 | 7 | 7 | 10 | helyn | 88.5 | 4 | 8 | 11 | sol | 1007.9 | 11 | 9 | 12 | diny | 12 | 1 |10 | 13 | weng | 52.2 | 5 |11 | 14 | sally | 99.71 | 9 |12 +---------+---------+---------+-------+13 7 rows in set14 15 mysql> select * from t_order order by amount desc;16 +---------+---------+---------+-------+17 | orderid | account | amount | goods |18 +---------+---------+---------+-------+19 | 9 | hen | 1752.02 | 7 |20 | 11 | sol | 1007.9 | 11 |21 | 14 | sally | 99.71 | 9 |22 | 10 | helyn | 88.5 | 4 |23 | 8 | brand | 52.2 | 2 |24 | 13 | weng | 52.2 | 5 |25 | 12 | diny | 12 | 1 |26 +---------+---------+---------+-------+27 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之上。
1 mysql> select * from t_order; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount | goods | 4 +---------+---------+---------+-------+ 5 | 8 | brand | 52.2 | 2 | 6 | 9 | hen | 1752.02 | 7 | 7 | 10 | helyn | 88.5 | 4 | 8 | 11 | sol | 1007.9 | 11 | 9 | 12 | diny | 12 | 1 |10 | 13 | weng | 52.2 | 5 |11 | 14 | sally | 99.71 | 9 |12 | 15 | brand1 | -99.99 | 5 |13 +---------+---------+---------+-------+14 8 rows in set15 16 mysql> select account as ac,amount as am,goods as gd from t_order order by abs(am) desc;17 18 +--------+---------+----+19 | ac | am | gd |20 +--------+---------+----+21 | hen | 1752.02 | 7 |22 | sol | 1007.9 | 11 |23 | brand1 | -99.99 | 5 |24 | sally | 99.71 | 9 |25 | helyn | 88.5 | 4 |26 | brand | 52.2 | 2 |27 | weng | 52.2 | 5 |28 | diny | 12 | 1 |29 +--------+---------+----+30 8 rows in set
order 在 where 条件之后,根据where已经过滤好的数据再进行排序。下面是过滤出购买金额>80 且 购买数量>5的数据,并且按照价格降序排序。
1 mysql> select * from t_order; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount | goods | 4 +---------+---------+---------+-------+ 5 | 8 | brand | 52.2 | 2 | 6 | 9 | hen | 1752.02 | 7 | 7 | 10 | helyn | 88.5 | 4 | 8 | 11 | sol | 1007.9 | 11 | 9 | 12 | diny | 12 | 1 |10 | 13 | weng | 52.2 | 5 |11 | 14 | sally | 99.71 | 9 |12 | 15 | brand1 | -99.99 | 5 |13 +---------+---------+---------+-------+14 8 rows in set15 16 mysql> select * from t_order where amount>80 and goods>5 order by amount desc;17 +---------+---------+---------+-------+18 | orderid | account | amount | goods |19 +---------+---------+---------+-------+20 | 9 | hen | 1752.02 | 7 |21 | 11 | sol | 1007.9 | 11 |22 | 14 | sally | 99.71 | 9 |23 +---------+---------+---------+-------+
很多时候我们过滤出符合要求的数据之后,还需要得到这些数据中的某一个具体区间,比如对付款超过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的值不能用表达式。
如下图,limit n 和 limit 0,n 是一致的:
1 mysql> select * from t_order; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount | goods | 4 +---------+---------+---------+-------+ 5 | 8 | brand | 52.2 | 2 | 6 | 9 | hen | 1752.02 | 7 | 7 | 10 | helyn | 88.5 | 4 | 8 | 11 | sol | 1007.9 | 11 | 9 | 12 | diny | 12 | 1 |10 | 13 | weng | 52.2 | 5 |11 | 14 | sally | 99.71 | 9 |12 | 15 | brand1 | -99.99 | 5 |13 +---------+---------+---------+-------+14 8 rows in set15 16 mysql> select * from t_order limit 217 ;18 +---------+---------+---------+-------+19 | orderid | account | amount | goods |20 +---------+---------+---------+-------+21 | 8 | brand | 52.2 | 2 |22 | 9 | hen | 1752.02 | 7 |23 +---------+---------+---------+-------+24 2 rows in set25 26 mysql> select * from t_order limit 0,2;27 +---------+---------+---------+-------+28 | orderid | account | amount | goods |29 +---------+---------+---------+-------+30 | 8 | brand | 52.2 | 2 |31 | 9 | hen | 1752.02 | 7 |32 +---------+---------+---------+-------+33 2 rows in set
这边我们获取支付金额中最大和最小的的一条记录。可以先使用 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
即跳过m条,获取n条,示例如下,我们跳过两条,从第三条开始,连取四条的操作:
1 mysql> select * from t_order order by amount; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount | goods | 4 +---------+---------+---------+-------+ 5 | 15 | brand1 | -99.99 | 5 | 6 | 12 | diny | 12 | 1 | 7 | 8 | brand | 52.2 | 2 | 8 | 13 | weng | 52.2 | 5 | 9 | 10 | helyn | 88.5 | 4 |10 | 14 | sally | 99.71 | 9 |11 | 11 | sol | 1007.9 | 11 |12 | 9 | hen | 1752.02 | 7 |13 +---------+---------+---------+-------+14 8 rows in set15 16 mysql> select * from t_order order by amount limit 2,4;17 +---------+---------+--------+-------+18 | orderid | account | amount | goods |19 +---------+---------+--------+-------+20 | 8 | brand | 52.2 | 2 |21 | 13 | weng | 52.2 | 5 |22 | 10 | helyn | 88.5 | 4 |23 | 14 | sally | 99.71 | 9 |24 +---------+---------+--------+-------+25 4 rows in set
分页的做法与这个类似,我们程序业务上看到的分页一般有 pageIndex,pageSize等参数,我们通常的做法是 limit pageIndex*pageSize,pageSize。
这边假设有31条数据,每页数量pageSize=10,页面索引pageIndex默认0,则第一页就是 limit 0,10,第二页 limit10,10,第三页 limit 20,10,第四页 limit 30,10。
注意点:
1、limit 后面不能使用表达式,只能使用明确的数值,否则会爆出异常,比如 limit 0*10,10,是不对的,这个上面提过了。
2、limit后续的数值只能是正整数和0,也就是说,不能是负数,否则同样会报错。
3、排序字段的值相同情况下,排序后分页会出现混乱重复的情况。
第3点详细说明下:假如根据age排序,但是有多个age都是20岁的同学,这时候我们3条记录一页,就会出现分页混乱数据重复。因为年龄相同的人有多个,这是几个人的排序在每次查询的时候会有不确定性。
举个例子,下面的分页,混乱了
1 mysql> select * from user3; 2 +----+------+-------+ 3 | id | age | name | 4 +----+------+-------+ 5 | 1 | 20 | brand | 6 | 2 | 22 | sol | 7 | 3 | 20 | helen | 8 | 4 | 19.5 | diny | 9 | 6 | 19 | a |10 | 7 | 20 | b |11 | 8 | 20 | c |12 | 9 | 20 | d |13 | 10 | 20 | e |14 | 11 | 23 | f |15 +----+------+-------+16 10 rows in set17 18 mysql> select * from user3 order by age limit 0,3; 19 +----+------+-------+20 | id | age | name |21 +----+------+-------+22 | 6 | 19 | a |23 | 4 | 19.5 | diny |24 | 3 | 20 | helen |25 +----+------+-------+26 3 rows in set27 28 mysql> select * from user3 order by age limit 3,3;29 30 +----+-----+-------+31 | id | age | name |32 +----+-----+-------+33 | 3 | 20 | helen |34 | 7 | 20 | b |35 | 8 | 20 | c |36 +----+-----+-------+37 3 rows in set38 39 mysql> select * from user3 order by age limit 6,3;40 41 +----+-----+-------+42 | id | age | name |43 +----+-----+-------+44 | 7 | 20 | b |45 | 3 | 20 | helen |46 | 2 | 22 | sol |47 +----+-----+-------+48 3 rows in set
我们的做法是使用重复值字段做排序的时候再加个唯一依据(一般可以设主键),就不会混乱了。
如下示例,正常了:
1 mysql> select * from user3; 2 +----+------+-------+ 3 | id | age | name | 4 +----+------+-------+ 5 | 1 | 20 | brand | 6 | 2 | 22 | sol | 7 | 3 | 20 | helen | 8 | 4 | 19.5 | diny | 9 | 6 | 19 | a |10 | 7 | 20 | b |11 | 8 | 20 | c |12 | 9 | 20 | d |13 | 10 | 20 | e |14 | 11 | 23 | f |15 +----+------+-------+16 10 rows in set17 18 mysql> select * from user3 order by age,id limit 0,3;19 20 +----+------+-------+21 | id | age | name |22 +----+------+-------+23 | 6 | 19 | a |24 | 4 | 19.5 | diny |25 | 1 | 20 | brand |26 +----+------+-------+27 3 rows in set28 29 mysql> select * from user3 order by age,id limit 3,3;30 31 +----+-----+-------+32 | id | age | name |33 +----+-----+-------+34 | 3 | 20 | helen |35 | 7 | 20 | b |36 | 8 | 20 | c |37 +----+-----+-------+38 3 rows in set39 40 mysql> select * from user3 order by age,id limit 6,3;41 42 +----+-----+------+43 | id | age | name |44 +----+-----+------+45 | 9 | 20 | d |46 | 10 | 20 | e |47 | 2 | 22 | sol |48 +----+-----+------+49 3 rows in set
1、order by cname [asc|desc] 用于对查询结果排序,asc为升序,desc为降序,可以省略,省略情况下默认为asc。
2、limit用来限制查询结果返回的行数,包含2个参数(offset,count),offset:表示跳过多少行,count:表示跳过offset行之后取的行数。limit中offset可以省略,默认值为0;limit中offset 和 count都必须大于等于0;limit中offset和count的值不能用表达式。
3、分页排序时,排序字段不要有重复值,重复值情况下可能会导致分页结果混乱,建议在后面加一个主键排序。
为帮助开发者们提升面试技能、有机会入职BATJ等大厂公司,特别制作了这个专辑——这一次整体放出。
大致内容包括了: Java 集合、JVM、多线程、并发编程、设计模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大厂面试题等、等技术栈!
欢迎大家关注公众号【Java烂猪皮】,回复【666】,获取以上最新Java后端架构VIP学习资料以及视频学习教程,然后一起学习,一文在手,面试我有。
每一个专栏都是大家非常关心,和非常有价值的话题,如果我的文章对你有所帮助,还请帮忙点赞、好评、转发一下,你的支持会激励我输出更高质量的文章,非常感谢!