分页与排序
排序 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