7-MySQL-DQL语言-分页、排序、子查询、分组和过滤

MySQL -> DQL查询数据 -> 分页、排序、子查询、分组和过滤

1. 分页和排序

• 回顾一下SELECT语法：顺序不能换

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1 [AS alias1][,table.field2][AS alias2]][,...]}
FROM table_name [AS table_alias]
[LEFT | RIGHT | INNER JOIN table_name2]
[WHERE ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...]
[LIMIT {[OFFSET,]ROW_COUNT | ROW_COUNT OFFSET OFFSET}];

排序

-- 排序ORDER BY  升序ASC 降序DESC
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
RIGHT JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等数学-2'
ORDER BY StudentResult DESC

排序

-- 分页 
-- LIMIT 起始页,页面大小
-- 每页只显示5条数据
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
RIGHT JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等数学-2'
ORDER BY StudentResult DESC
LIMIT 3,5

-- 网页应用：当前 当前页面大小 总的页数
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit (N-1)*pageSize, pageSize
-- 总页数=数据总数/页面大小

• 练习题：查询高等数学-2成绩排名前十的学生，并且分数要大于80

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等数学-2' AND `StudentResult` > 80
ORDER BY `StudentResult` DESC
LIMIT 0,10

2. 子查询

• where （这个值是计算出来的）
• 本质：在where语句中嵌套一个子查询语句

-- 查询高等数学-2的所有考试结果 学号、科目名称、成绩，降序排列
-- 方式1：使用连接查询
SELECT `StudentNo`,s.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` s
ON r.`SubjectNo` = s.`SubjectNo`
WHERE `SubjectName` = '高等数学-2'
ORDER BY `StudentResult` DESC


-- 方式2：使用子查询 由里及外
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result` r
WHERE SubjectNo = (
	SELECT `SubjectNo` FROM `subject` 
	WHERE `SubjectName` = '高等数学-2'
)
ORDER BY StudentResult DESC


-- 分数不低于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult` >= 80

-- 在这个基础上增加一个科目 
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult` >= 80 AND `SubjectNo` = (
	SELECT `SubjectNo` FROM `subject`
	WHERE `SubjectName` = '高等数学-2'
)

-- 再改造
SELECT `StudentNo`,`StudentName`
FROM `student`
WHERE `StudentNo` IN (
	SELECT `StudentNo` FROM `result`
	WHERE `StudentResult` >= 80 
	AND `SubjectNo` = (
		SELECT `SubjectNo` FROM `subject`
		WHERE `SubjectName` = '高等数学-2'
	)
)

3. 分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心：根据不同的课程分组
SELECT `SubjectName`,AVG(`StudentResult`),MAX(`StudentResult`),MIN(`StudentResult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
-- where AVG(`StudentResult`) > 80 报错
GROUP BY r.`SubjectNo`  -- 通过什么字段来分组
HAVING AVG(`StudentResult`) > 80 -- 分组条件