一. 排序order by
示例:
SELECT goods_id,goods_name,cat_id FROM goods ORDER BY cat_id;
降序(从上往下递减):desc;
3 |
2 |
1 |
升序(从上往下递增);asc;(未指明排序顺序,默认为升序)
1 |
2 |
3 |
多重排序:
在数据库查询时,常常需要排序,而有时排序条件可能有多个
ORDER BY 语句可以接多个字段名,ORDER BY fieldA[],fieldB[],fieldC[],… 其顺序就是排序优先级,[] 可加ASC或DESC
举例说明:对下表查询,先 cat_id 升序排列,然后以 shop_price 降序排列
SELECT goods_id,goods_name,cat_id,shop_price FROM goods ORDER BY cat_id asc,shop_price desc;
二. limit用法
语法:select c1,c2 from table limit offset,count;
offset:偏移量,即要返回的第一行的距离,可以理解为需要跳过的行数。
count:要返回的最大行数;
常见用法:
1.返回前 N 条记录
SELECT * FROM result ORDER BY c limit N;
SELECT * FROM result ORDER BY c limit 0,N;
"limit N" 等价于" limit 0,N" 用法,标识检索前N行;
2.返回最前的那条记录
SELECT * FROM result ORDER BY c limit 1;
只返回第一条记录;
3.返回最后的一条记录
SELECT * FROM result ORDER BY c limit -1;
SELECT * FROM result ORDER BY c ASC limit 1;
4.返回6~15行记录
SELECT * FROM result ORDER BY c limit 5,10;
5.返回第10行到最末尾的记录
SELECT * FROM result ORDER BY c limit 9,-1;