--------------------------------表结构私发----------------------------------
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT ss.*, 语文.s_score, 数学.s_score
FROM score 语文, score 数学, student ss
WHERE ss.s_id = 语文.s_id AND -- 找同一个学生
ss.s_id = 数学.s_id AND
语文.c_id = '01' AND
数学.c_id = '02' AND
语文.s_score > 数学.s_score ;
SELECT ss.*, 语文.s_score as yuwen, 数学.s_score as math
FROM student ss
LEFT JOIN score 语文 ON ss.s_id = 语文.s_id AND 语文.c_id = '01'
LEFT JOIN score 数学 ON ss.s_id = 数学.s_id AND 数学.c_id = '02'
WHERE 语文.s_score > 数学.s_score ;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT ss.*, 语文.s_score as yuwen, 数学.s_score as math
FROM student ss
LEFT JOIN score 语文 ON ss.s_id = 语文.s_id AND 语文.c_id = '01'
LEFT JOIN score 数学 ON ss.s_id = 数学.s_id AND 数学.c_id = '02'
WHERE 语文.s_score < 数学.s_score ;
-- 3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT ss.s_id, ss.s_name, count(score.c_id), SUM(score.s_score)
FROM student ss
LEFT JOIN score ON ss.s_id = score.s_id -- 将学生id一样的课程统计
GROUP BY ss.s_id, ss.s_name ;
-- 4、查询学过"张三"老师授课的同学的信息
SELECT ss.*
FROM student ss
LEFT JOIN score on ss.s_id = score.s_id
LEFT JOIN course on score.c_id = course.c_id
LEFT JOIN teacher on teacher.t_id = course.t_id
where teacher.t_name = '张三' ;
-- 1.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
SELECT ss.s_id, ss.s_name, AVG(score.s_score) as avgscore
FROM student ss
LEFT JOIN score ON ss.s_id = score.s_id
GROUP BY ss.s_id, ss.s_name
HAVING avgscore < 60 OR avgscore IS null ;
-- 2.查询"李"姓老师的数量
SELECT COUNT(tt.t_name)
FROM teacher tt
WHERE tt.t_name LIKE '李%' ;
-- 3.查询没学过"张三"老师授课的同学的信息
SELECT ss.*
FROM student ss
where ss.s_id not in(
SELECT score.s_id
FROM score, course, teacher
where score.c_id = course.c_id AND teacher.t_id = course.t_id
AND teacher.t_name = '张三'
) ;
-- 4.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT ss.*
FROM student ss
INNER JOIN score sc1 ON sc1.s_id = ss.s_id AND sc1.c_id='01'
INNER JOIN score sc2 ON sc2.s_id = ss.s_id AND sc2.c_id='02' ;
# 1.按平均成绩从高到低显示所有学生的所有课程成绩以及平均成绩
SELECT a.s_name, 语文.s_score as yuwen, 数学.s_score as math, 英语.s_score as english, AVG(s4.s_score) as avgScore
FROM student a
LEFT JOIN score 语文 ON a.s_id = 语文.s_id AND 语文.c_id = '01'
LEFT JOIN score 数学 ON a.s_id = 数学.s_id AND 数学.c_id = '02'
LEFT JOIN score 英语 ON a.s_id = 英语.s_id AND 英语.c_id = '03'
LEFT JOIN score s4 ON a.s_id = s4.s_id
GROUP BY a.s_name
ORDER BY avgScore DESC ;
# 总成绩2,3名
-- SELECT a.*, 语文.s_score as yuwen, 数学.s_score as math, 英语.s_score as english, SUM(s4.s_score) as sumScore
-- FROM student a
-- LEFT JOIN score 语文 ON a.s_id = 语文.s_id AND 语文.c_id = '01'
-- LEFT JOIN score 数学 ON a.s_id = 数学.s_id AND 数学.c_id = '02'
-- LEFT JOIN score 英语 ON a.s_id = 英语.s_id AND 英语.c_id = '03'
-- LEFT JOIN score s4 ON a.s_id = s4.s_id
-- GROUP BY a.s_name
-- ORDER BY sumScore DESC
-- LIMIT 1,2 ;
# 2.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
(SELECT s.*, sc.c_id as '课程ID', sc.s_score as "成绩"
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
WHERE sc.c_id='01' ORDER BY sc.s_score desc LIMIT 1,2)
UNION ALL
(SELECT s.*, sc.c_id, sc.s_score
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
WHERE sc.c_id='02' ORDER BY sc.s_score desc LIMIT 1,2)
UNION ALL
(SELECT s.*, sc.c_id, sc.s_score
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
WHERE sc.c_id='03' ORDER BY sc.s_score desc LIMIT 1,2) ;
#方法二:
-- 先把课程里面的id和名称挑出来
SELECT kc.c_id, kc.c_name, "第二名" as "名次",
(SELECT s_name from student
LEFT JOIN score ON score.s_id = student.s_id
WHERE score.c_id = kc.c_id ORDER BY score.s_score desc LIMIT 2,1
) as 姓名,
(SELECT s_score from score
where score.c_id = kc.c_id
ORDER BY score.s_score DESC LIMIT 2,1
) as 成绩,
"第三名" as "名次",
(SELECT s_name from student
LEFT JOIN score ON score.s_id = student.s_id
WHERE score.c_id = kc.c_id ORDER BY score.s_score desc LIMIT 3,1
) as 姓名,
(SELECT s_score from score
where score.c_id = kc.c_id
ORDER BY score.s_score DESC LIMIT 3,1
) as 成绩
FROM course kc GROUP BY kc.c_id;
# 3.查询学生平均成绩及其名次
set @rankScore:=0;
SELECT @rankScore:= @rankScore+1 as '排名',tab.* FROM
(SELECT student.s_id, student.s_name, IFNULL(AVG(score.s_score),0) as avgScore
FROM student
LEFT JOIN score ON score.s_id = student.s_id
GROUP BY student.s_id
ORDER BY avgScore desc) tab ;
# 4.查询各科成绩前三名的记录
(SELECT s.*, sc.c_id as '课程ID', sc.s_score as "成绩"
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
WHERE sc.c_id='01' ORDER BY sc.s_score desc LIMIT 3)
UNION ALL
(SELECT s.*, sc.c_id, sc.s_score
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
WHERE sc.c_id='02' ORDER BY sc.s_score desc LIMIT 3)
UNION ALL
(SELECT s.*, sc.c_id, sc.s_score
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
WHERE sc.c_id='03' ORDER BY sc.s_score desc LIMIT 3)
# 5.查询本月过生日的学生
SELECT s.s_name, s.s_birth
FROM student s
WHERE MONTH(s.s_birth) = MONTH(CURDATE()) ;
# 6.查询下周过生日的学生
SELECT s.s_name, s.s_birth
FROM student s
WHERE WEEK(s.s_birth) = WEEK(NOW())+1 ;