一、排序
1.1 排序查询语法
排序查询语法:select * from 表名 order by 列1 asc|desc
先按照列1进行排序,如果列1的值相同时,则按照 列2 排序,以此类推
asc从小到大排列,即升序
desc从大到小排序,即降序
#年龄从大到小, 当年龄一样时,身高从低到高
select * from personinfo order by age desc,height asc;
mysql> select * from personinfo order by age desc,height asc;
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
| id | name | age | gender | height | city | addr | personId | is_del |
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
| 6 | xiaozhao | 30 | 女 | NULL | NULL | NULL | 510131198505101236 | |
| 4 | xiaozhao | 30 | 女 | 160.00 | 成都 | 双流区空港4路128号 | 510131198905101234 | |
| 5 | xiaoliao | 25 | 男 | NULL | 重庆 | NULL | 510131198505101235 | |
| 3 | xiaoliao | 25 | 男 | 172.00 | 成都 | 高新区合作路1112号 | 510131198605106578 | |
| 2 | xiaozhang | 20 | 男 | 172.00 | NULL | NULL | 510131198605171237 | |
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
5 rows in set (0.00 sec)
二、分页查询
有时候数据太多,需要一页一页查询查出,比如京东、淘宝这些数据 都是分页展示的
分页查询的语法格式:
select * from 表名 limit start,count
假如每条页面展示m条,第N页就可以写成
select * from 表名 limit (N-1)*m,m
例:
mysql> select * from personinfo limit 0,3;
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
| id | name | age | gender | height | city | addr | personId | is_del |
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
| 2 | xiaozhang | 20 | 男 | 172.00 | NULL | NULL | 510131198605171237 | |
| 3 | xiaoliao | 25 | 男 | 172.00 | 成都 | 高新区合作路1112号 | 510131198605106578 | |
| 4 | xiaozhao | 30 | 女 | 160.00 | 成都 | 双流区空港4路128号 | 510131198905101234 | |
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
3 rows in set (0.00 sec)