排序
排序:升序ASC
,降序DESC
ORDER BY
通过哪个字段排序,怎么排
查询结果根据成绩降序排序
-- 排序:升序ASC,降序DESC
-- ORDER BY通过哪个字段排序,怎么排
-- 查询结果根据成绩降序排序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = '高等数学-2'
ORDER BY `StudentResult` DESC
分页
好处 : (用户体验,网络传输,查询压力)
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
…
第N页 :limit (pageNo-1)*pageSzie,pageSzie
[pageNo
:页码,pageSize
:单页面显示条数]
-- 分页,语法;limit 当前页,页面大小
-- 排序:升序ASC,降序DESC
-- ORDER BY通过哪个字段排序,怎么排
-- 查询结果根据成绩降序排序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = '高等数学-2'
ORDER BY `StudentResult` ASC
LIMIT 0,5
思考题:查询高等数学-2课程成绩排名前三的学生,并且分数要大于70的学生信息(学号,姓名,课程名称,分数)
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = '高等数学-2' AND `StudentResult`>'70'
ORDER BY `StudentResult` DESC
LIMIT 0,3