排序 ORDER BY与分页 limit的使用（补充）

分页与排序

排序 ORDER BY

语法：ORDER BY 要排列的字段 ASC | DESC (升序还是降序)

演示

-- 查询参加“高等数学-1”考试的同学信息（学号，姓名，科目名，分数）按升序排列
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult ASC 

分页 limit

语法：limit 起始位置，页面大小

演示

SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
where subjectname = '高等数学-1'
ORDER BY studentresult ASC 
LIMIT 1,5
-- LIMIT 5 offset 1   -- 1表示页面大小（返回值），5表示起始位置

子查询

在where语句中嵌套select语句

使用对比

-- 查询 高等数学-1 分数不小于80的学生和姓名
-- 连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1' AND studentresult >= 80

- 子查询 嵌套查询
SELECT studentno,studentname FROM student WHERE studentno IN(
	SELECT studentno FROM result WHERE studentresult >= 80 AND subjectno = (
		SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
	)
)


-- 查询c语言-1 前5名同学的成绩（学号，姓名，分数）使用子查询
-- 连接查询
SELECT s.studentno,studentname,studentresult 
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC
LIMIT 0,5

-- 连接查询和子查询并用
SELECT s.studentno,studentname,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.studentno=r.studentno
WHERE subjectno=
(SELECT subjectno FROM `subject` WHERE subjectname='高等数学-1')
ORDER BY studentresult DESC
LIMIT 0,5