文章目录

按一列排序按多列排序指定排序方向：desclimit和order by组合总结

按一列排序

mysql> select prod_name -> from products -> order by prod_name; +---------------------+ | prod_name | +---------------------+ | 12 inch teddy bear | | 18 inch teddy bear | | 8 inch teddy bear | | Bird bean bag toy | | Fish bean bag toy | | King doll | | Queen doll | | Rabbit bean bag toy | | Raggedy Ann | +---------------------+ 9 rows in set (0.00 sec)

按多列排序

mysql> select prod_id, prod_price, prod_name -> from products -> order by prod_price, prod_name; +---------+------------+---------------------+ | prod_id | prod_price | prod_name | +---------+------------+---------------------+ | BNBG02 | 3.49 | Bird bean bag toy | | BNBG01 | 3.49 | Fish bean bag toy | | BNBG03 | 3.49 | Rabbit bean bag toy | | RGAN01 | 4.99 | Raggedy Ann | | BR01 | 5.99 | 8 inch teddy bear | | BR02 | 8.99 | 12 inch teddy bear | | RYL01 | 9.49 | King doll | | RYL02 | 9.49 | Queen doll | | BR03 | 11.99 | 18 inch teddy bear | +---------+------------+---------------------+ 9 rows in set (0.00 sec)

指定排序方向：desc

mysql> select prod_id, prod_price, prod_name -> from products -> order by prod_price desc; +---------+------------+---------------------+ | prod_id | prod_price | prod_name | +---------+------------+---------------------+ | BR03 | 11.99 | 18 inch teddy bear | | RYL01 | 9.49 | King doll | | RYL02 | 9.49 | Queen doll | | BR02 | 8.99 | 12 inch teddy bear | | BR01 | 5.99 | 8 inch teddy bear | | RGAN01 | 4.99 | Raggedy Ann | | BNBG01 | 3.49 | Fish bean bag toy | | BNBG02 | 3.49 | Bird bean bag toy | | BNBG03 | 3.49 | Rabbit bean bag toy | +---------+------------+---------------------+ 9 rows in set (0.00 sec) mysql> select prod_id, prod_price, prod_name -> from products -> order by prod_price desc, prod_name; +---------+------------+---------------------+ | prod_id | prod_price | prod_name | +---------+------------+---------------------+ | BR03 | 11.99 | 18 inch teddy bear | | RYL01 | 9.49 | King doll | | RYL02 | 9.49 | Queen doll | | BR02 | 8.99 | 12 inch teddy bear | | BR01 | 5.99 | 8 inch teddy bear | | RGAN01 | 4.99 | Raggedy Ann | | BNBG02 | 3.49 | Bird bean bag toy | | BNBG01 | 3.49 | Fish bean bag toy | | BNBG03 | 3.49 | Rabbit bean bag toy | +---------+------------+---------------------+ 9 rows in set (0.00 sec)

可见desc只作用于其前面的1列，而之前的distinct会作用于其后的所有列

limit和order by组合

选出最大值

mysql> select prod_price -> from products -> order by prod_price desc -> limit 1; +------------+ | prod_price | +------------+ | 11.99 | +------------+ 1 row in set (0.00 sec)

返回最大的前4个

mysql> select prod_price -> from products -> order by prod_price desc -> limit 4; +------------+ | prod_price | +------------+ | 11.99 | | 9.49 | | 9.49 | | 8.99 | +------------+

总结

order by子句必须在from子句后面；limit子句必须在order by子句后面