(文档有待进一步整理,暂时先做备份~)
# 1. 读取降序后前几行的数据
# limit 取前几行
SELECT *
FROM student
ORDER BY age DESC
LIMIT 3
# 2. 查询某列重复的行
SELECT id, name
FROM student
WHERE name in (
SELECT name
FROM student
GROUP BY name
HAVING(COUNT(*) > 1)
)
ORDER BY `name` DESC; # 想要排序的话
# 3. 平均分不及格的学生id及平均分
SELECT sid, AVG(score) AS avg_score
FROM stu_course
GROUP BY sid
HAVING avg_score < 60;
# 4. 每门课成绩都不低于80分的学生的id
# distinct去除列中重复记录
# 用到反向思想
SELECT DISTINCT sid
FROM stu_course
WHERE sid not in (
SELECT sid
FROM stu_course
WHERE score < 80
)
# 5. 输出学生姓名和总分
# 方法一:会过滤掉没有成绩的人
SELECT student.`name`, SUM(score) as sum_score
FROM stu_course, student
WHERE student.ID = stu_course.sid
GROUP BY sid
#方法二:更保险的写法是左外连接
SELECT ID, student.`name`, SUM(score) as sum_score
FROM student
LEFT JOIN stu_course
ON student.ID = stu_course.sid
GROUP BY sid; # 记得写group by
# 5. 总成绩最高的学生
# 方法一:效率低,会重复计算所有的
SELECT sid, SUM(score) AS sum_score
FROM stu_course
GROUP BY sid
HAVING SUM(score) >= ALL
(SELECT SUM(score) from stu_course GROUP BY sid)
# 方法二:order by 可以使用聚集函数,因此最简单的方法是:
SELECT sid, SUM(score) AS sum_score
FROM stu_course
GROUP BY sid
ORDER BY sum_score DESC
LIMIT 1 # 同理可查前三名
# 6. 查询课程1成绩第2高的学生(查询第N大数的问题)
# 一:先查出第二高的成绩
SELECT score
FROM stu_course
WHERE stu_course.cid = 1
ORDER BY score DESC
LIMIT 1, 1 # 第一个1表示跳过前1行,第二个1表示取之后的1行
# 二:取出改成绩对应的学生
SELECT *
FROM stu_course
WHERE cid = 1 and score = (
SELECT score
FROM stu_course
WHERE stu_course.cid = 1
ORDER BY score DESC
LIMIT 1, 1
)
# 7. 查询各科成绩最高的学生,结果列出学生id、课程id和对应的成绩
# 以下方法是不对的,sid是无效的
SELECT sid, cid, MAX(score)
FROM stu_course
GROUP BY cid
# 正确的解法是使用相关嵌套查询(在进行内层查询时需要用到外层查询):
SELECT *
FROM stu_course as x
WHERE score >= (
SELECT MAX(score)
FROM stu_course as y
WHERE y.cid = x.cid # 不是很理解这里为什么这么写?
)
# 8. 查询每门课的前2名,结果按课程id升序,同一门课按成绩降序 -- 取每组的前N条记录
SELECT *
FROM stu_course as x
WHERE 2 > (SELECT COUNT(DISTINCT score) FROM stu_course as y WHERE x.cid = y.cid and y.score > x.score) # 如果只有0个、1个学生比x的成绩高,那它一定是前2名
ORDER BY cid, score DESC
# 9. 球队组合比赛,可能的组合 -- 表自己与自己连接
SELECT a.name, b.name
FROM team a, team b
WHERE a.name < b.name
# 10. 查询每个人每门课的成绩
# 一:建一个视图
CREATE VIEW temp AS SELECT student.`name` as sname, course.`name` as cname, score
FROM stu_course join(student, course)
ON student.id = stu_course.sid and course.id = stu_course.cid;
# 二:查询
SELECT sname, # 注意标点符号必须英文
sum(case when cname='语文' then score else 0 end) as 语文,
sum(case when cname='数学' then score else 0 end) as 数学,
SUM(case when cname='英语' then score else 0 end) as 英语
FROM temp
GROUP BY sname;