MySQL 知识量:16 - 40 - 165
如果需要对查询结果进行排序,可以使用order by子句,子句后面跟查询依据的列名即可。
select * from student order by age;
查询表student的所有信息,并按照年龄age进行排序。结果为:
+----+-------+-----+--------+ | id | name | age | sex | +----+-------+-----+--------+ | 4 | Robot | 10 | male | | 6 | Toney | 10 | male | | 1 | Susan | 11 | female | | 3 | Bob | 11 | male | | 5 | Jen | 11 | female | | 2 | Jame | 12 | male | +----+-------+-----+--------+
以上结果中包含了排序的列age,实际上用非查询的列排序完全可以。例如:
select name from student order by age;
查询表student的姓名name列信息,并按照年龄age进行排序。结果为:
+-------+ | name | +-------+ | Robot | | Toney | | Susan | | Bob | | Jen | | Jame | +-------+
如果要对查询结果按照多个列进行排序,只需要在order by后面加上多个排序列的列名即可。
select * from student order by age,sex;
查询表student的所有信息,并按照age和sex进行排序。结果为:
+----+-------+-----+--------+ | id | name | age | sex | +----+-------+-----+--------+ | 4 | Robot | 10 | male | | 6 | Toney | 10 | male | | 1 | Susan | 11 | female | | 5 | Jen | 11 | female | | 3 | Bob | 11 | male | | 2 | Jame | 12 | male | +----+-------+-----+--------+
MySQL会先按照第一个列进行排序,在此基础上,对第一个列值相同的再按照第二个列进行排序。以上代码中,先按照age进行排序,对age的值相同的,再按照sex进行排序。
MySQL的order by子句查询结果的排序默认是升序(asc),即省略升降序标识时就按升序处理。如果要设置为降序,就要显示设置为desc。
select * from student order by age desc;
查询表student的所有信息,并按照年龄age从大到小进行排序。结果为:
+----+-------+-----+--------+ | id | name | age | sex | +----+-------+-----+--------+ | 2 | Jame | 12 | male | | 1 | Susan | 11 | female | | 3 | Bob | 11 | male | | 5 | Jen | 11 | female | | 4 | Robot | 10 | male | | 6 | Toney | 10 | male | +----+-------+-----+--------+
升降序标识紧跟在排序列名的后面。如果要对多个排序列进行设置,就要在每个列名的后面加上升降序标识。
select * from student order by age desc,id desc;
查询表student的所有信息,并先按照年龄age从大到小进行排序,在此基础上,对age相同的再按照id从大到小进行排序。结果为:
+----+-------+-----+--------+ | id | name | age | sex | +----+-------+-----+--------+ | 2 | Jame | 12 | male | | 5 | Jen | 11 | female | | 3 | Bob | 11 | male | | 1 | Susan | 11 | female | | 6 | Toney | 10 | male | | 4 | Robot | 10 | male | +----+-------+-----+--------+
使用order by和limit的组合,如何查询年龄最大的学生信息?
select * from student order by age desc limit 1;
结果为:
+----+------+-----+------+ | id | name | age | sex | +----+------+-----+------+ | 2 | Jame | 12 | male | +----+------+-----+------+
要特别注意order by子句与limit子句的放置顺序,即:limit子句要在order by子句的后面,否则会出错。
Copyright © 2017-Now pnotes.cn. All Rights Reserved.
编程学习笔记 保留所有权利
MARK:3.0.0.20240214.P35
From 2017.2.6