数据库分页、排序与子查询_视图升序降序

一、排序

• 关键词为：order by
• 针对于查询出的结果，我们可以进行升序（ASC）和降序（DESC）两种排序
• 如果不特别指出，一般都是直接按照升序排序

-- =============排序=============
-- ASC：升序 DESC：降序
 
-- 对参加考试的同学按数学成绩降序排序
SELECT st.studentNo,studentName,subjectName,studentScore
FROM student AS st
RIGHT JOIN score AS sc
ON st.studentNo = sc.studentNo 
INNER JOIN `subject` AS su
ON sc.subjectNo = su.subjectNo
WHERE subjectName = '数学'
ORDER BY studentScore DESC
 
-- 对参加考试的同学按数学成绩升序排序
SELECT st.studentNo,studentName,subjectName,studentScore
FROM student AS st
RIGHT JOIN score AS sc
ON st.studentNo = sc.studentNo 
INNER JOIN `subject` AS su
ON sc.subjectNo = su.subjectNo
WHERE subjectName = '数学'
ORDER BY studentScore ASC

二、分页

• 关键词为：limit
• 分页的主要目的是缓解数据库的压力
• limit有两个参数，1.结果起始值，2.页面的大小，即就是显示的结果数

-- 对参加考试的同学按数学成绩降序排序后进行分页
SELECT st.studentNo,studentName,subjectName,studentScore
FROM student AS st
RIGHT JOIN score AS sc
ON st.studentNo = sc.studentNo 
INNER JOIN `subject` AS su
ON sc.subjectNo = su.subjectNo
WHERE subjectName = '数学'
ORDER BY studentScore DESC
LIMIT 0,5

总结规律如下：

-- 第一页 limit0,5
-- 第二页 limit5,5
-- 第三页 limit10,5
-- 第n页  limit(n-1)*5,5

-- limit (n-1)*size,size
-- limit (当前页-1)*页面大小，页面大小 

数据起始值 = (当前页-1)*页面大小

三、子查询

• 实质为：在where中嵌套一个子查询

-- ==========子查询==========
 
-- 查询数据库的考试结果（学号，科目名称，成绩）降序排序
 
-- 方法一：使用多表连接
SELECT studentNo,subjectNo,StudentScore
FROM `subject` AS su
JOIN score AS sc
ON su.subjectNo = sc.subjectNo
WHERE subjectName = '数据库'
ORDER BY StudentScore DESC 
 
-- 方法二：使用子查询
SELECT studentNo,subjectNo,StudentScore
FROM score 
WHERE subjectNo = (
	SELECT subjectNo 
	FROM `subject` 
	WHERE subjectName = '数据库'
)
ORDER BY StudentScore DESC