-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 (需要查询学生的全部信息,以及01课程成绩 02 课程成绩)
select st.*,sc.s_score as '01score',sc2.s_score as '02score'
from student st
LEFT JOIN score sc on st.s_id = sc.s_id and sc.c_id = 01
LEFT JOIN score sc2 on st.s_id = sc2.s_id and sc2.c_id = 02
加上条件限制(where之中不能使用别名比较大小)
select st.*,sc.s_score as '01score',sc2.s_score as '02score'
from student st
LEFT JOIN score sc on st.s_id = sc.s_id and sc.c_id = 01
LEFT JOIN score sc2 on st.s_id = sc2.s_id and sc2.c_id = 02
where sc.s_score > sc2.s_score
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数(同1)
select st.*,sc.s_score as '01score',sc2.s_score as '02score'
from student st
LEFT JOIN score sc on st.s_id = sc.s_id and sc.c_id = 01
LEFT JOIN score sc2 on st.s_id = sc2.s_id and sc2.c_id = 02
where sc.s_score < sc2.s_score
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩(需要分组 having一般跟在group by之后)
select st.s_id,st.s_name,avg(sc.s_score)
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
GROUP BY st.s_id
HAVING avg(sc.s_score) > 60
-- round(x,d) 四舍五入取值; x是需要取舍的数值,d是取舍位置
select st.s_id,st.s_name,ROUND(avg(sc.s_score),2) as avgScore
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
GROUP BY st.s_id
HAVING avg(sc.s_score) > 60
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
不加限制条件结果如下,会包含没有成绩的
select st.s_id,st.s_name,ROUND(avg(sc.s_score),2) as avgScore
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
GROUP BY st.s_id
这样只能查询出有成绩的
select st.s_id,st.s_name,ROUND(avg(sc.s_score),2) as avgScore
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
GROUP BY st.s_id
HAVING avg(sc.s_score) < 60
这样才能显示空的
select st.s_id,st.s_name,ROUND(avg(sc.s_score),2) as avgScore
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
GROUP BY st.s_id
HAVING avg(sc.s_score) < 60 or avg(sc.s_score) is NULL
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select st.s_id,st.s_name,IFNULL(count(sc.c_id),0),IFNULL(sum(sc.s_score),0)
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
GROUP BY st.s_id
-- 6、查询"李"姓老师的数量
select count(*) as cnt_name_li
from teacher
where t_name like '李%';
-- 7、查询学过"张三"老师授课的同学的信息
select st.*,sc.*, te.*
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
LEFT JOIN course co on co.c_id = sc.c_id
LEFT JOIN teacher te on te.t_id = co.t_id
WHERE t_name = "张三"
-- 8、查询没学过"张三"老师授课的同学的信息
-- 张三老师教的课
select c_id from course co
LEFT JOIN teacher te
on te.t_id = co.t_id
WHERE t_name = "张三"
-- 有张三老师课成绩的st.s_id
select sc.s_id from
score sc where sc.c_id in
(select c.c_id from course c left join teacher t on
t.t_id=c.t_id where t.t_name="张三")
-- 不在上面查到的st.s_id的学生信息,即没学过张三老师授课的同学信息
select student.* from student
WHERE student.s_id not IN (
select sc.s_id from
score sc where sc.c_id in
(select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name="张三")
)
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
先在分数表查询学生id 并按照学生id分组,条件是课程id 在01 ,02 里面,如果每个分组的记录数是2,就说明这个学生学过编号为"01"并且也学过编号为"02"
select st.*
from student st
where st.s_id in (
select s_id
from score
where c_id in (01,02)
group by s_id
having count(*) = 2)
自己想的别的解法,使用inner join 需要完全匹配,结果同上
select st.*
from student st
INNER JOIN score sc on st.s_id = sc.s_id and sc.c_id = 01
INNER JOIN score sc2 on st.s_id = sc2.s_id and sc2.c_id = 02
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select st.*,sc.*
from student st
INNER JOIN score sc on st.s_id = sc.s_id and sc.c_id = 01
where st.s_id not in(
select s_id
from score
WHERE score.c_id = 02
)
-- 11、查询没有学全所有课程的同学的信息
SELECT st.*
from student st
INNER JOIN score sc on st.s_id = sc.s_id
GROUP BY st.s_id
HAVING COUNT(*) < (SELECT count(*)
from course)
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select c_id
from score sc
where sc.s_id = 01
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
-- 里面查询的意义就是先查询出01学生学习的课程号为 01 02 03
-- 然后成绩表和者3条记录内连接,记录数和原来分数标的记录一样的,代表对接学习的课程一样
-- 按照学号分组,记录数和课程数一样,代表学习的课程数一样
-- 记得过滤掉01学生自己
SELECT student.*
from student
where s_id in (
SELECT s_id
from score sc
INNER JOIN (
select c_id from score sc where sc.s_id = 01
) t on sc.c_id = t.c_id
where s_id != 01
GROUP BY s_id
HAVING COUNT(*) = (select COUNT(*) from score sc where sc.s_id = 01)
)
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name
from student
where s_id not in (
SELECT s_id
from score sc
INNER JOIN (
SELECT c_id
from course co
inner join teacher te on te.t_id = co.t_id
where te.t_name = '张三'
) tt on sc.c_id = tt.c_id
)
-- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select st.s_id,st.s_name,avgscore
from student st
INNER JOIN (
SELECT s_id,avg(s_score) as avgscore
from score
GROUP BY s_id
)tt on st.s_id = tt.s_id
where st.s_id in (
SELECT s_id
from score
where s_score < 60
GROUP BY s_id
HAVING COUNT(*) >= 2
)
-- 16、检索"01"课程分数小于60,按分数降序排列的学生信息
select st.*
from student st
INNER JOIN (
SELECT s_id,s_score
from score
where c_id = 01 and s_score < 60
)tt on st.s_id = tt.s_id
ORDER BY s_score DESC;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 可加round,case when then else end 使显示更完美
select s.s_id as 学号,s.s_name as 姓名,
sum(CASE c_id WHEN 01 THEN s_score ELSE 0 END ) as 语文,
sum(case c_id when 02 then s_score else 0 end) as 数学,
sum(case c_id when 03 then s_score else 0 end) as 英语,
IFNULL(ROUND(AVG(s_score),2),0) as 平均成绩
from student s
LEFT JOIN score sc on s.s_id = sc.s_id
GROUP BY s.s_id
ORDER BY 平均成绩 DESC
-- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
select
c.c_id as 课程ID,c.c_name as 课程name,
max(s_score) as 最高分,
min(s_score) as 最低分,
round(avg(s_score), 2) as 平均分,
concat(round(sum(case when s_score >= 60 then 1 else 0 end) / count(*) * 100, 2), '%') as 及格率,
concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end) / count(*) * 100, 2), '%') as 中等率,
concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优良率,
concat(round(sum(case when s_score >= 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优秀率
from course c
inner join score s on c.c_id = s.c_id
group by c.c_id;
-- 19、按各科成绩进行排序,并显示排名(实现不完全)
-- mysql没有rank函数
-- 加@score是为了防止用union all 后打乱了顺序
-- 20、查询学生的总成绩并进行排名
-- 21、查询不同老师所教不同课程平均分从高到低显示
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
-- 24、查询学生平均成绩及其名次
查不出来??
-- 然后加上一个排序
-- (@i:=@i+1)代表定义一个变量,每次叠加1
-- from (select @i := 0)部分允许我们进行变量初始化
SELECT(@i := @i + 1) as rank,t2.*
from (SELECT @i := 0) var
CROSS JOIN(
select s.s_id,s.s_name,avgscore
from student s
inner join (
select s_id,avg(s_score) as avgscore
from score
GROUP BY s_id
) tt on s.s_id = tt.s_id
ORDER BY avgscore DESC
) as t2;
-- 25、查询各科成绩前三名的记录
-- 26、查询每门课程被选修的学生数
法1
SELECT co.c_id, co.c_name ,COUNT(1)
from course co
inner join score sc on co.c_id = sc.c_id
GROUP BY co.c_id
法2
select c.c_id,c.c_name,cnt_student
from course c
inner join (
select c_id,count(*) as cnt_student
from score
group by c_id
) t1
on c.c_id = t1.c_id;
-- 27、查询出只有两门课程的全部学生的学号和姓名
select s_id, s_name
from student st
where s_id in (
SELECT s_id from score sc
GROUP BY s_id
HAVING count(*) = 2
)
-- 28、查询男生、女生人数
SELECT s_sex, COUNT(1)
FROM student
GROUP BY s_sex
-- 29、查询名字中含有"风"字的学生信息
select st.* FROM student st WHERE st.s_name LIKE "%风%"
-- 30、查询同名同性学生名单,并统计同名人数
SELECT s_name,count(1)
from student
GROUP BY s_name,s_sex
HAVING count(1) > 1
-- 31、查询1990年出生的学生名单
SELECT student.*
FROM student
where YEAR(s_birth) = 1990
-- 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
法1 先连接,然后进行分组
SELECT co.c_id,co.c_name,avg(sc.s_score) as avgScore
FROM course co
left join score sc on sc.c_id = co.c_id
GROUP BY co.c_id
ORDER BY avgScore desc,co.c_id
法2 子查询先分好租,然后进行连接
SELECT co.c_id,co.c_name,avgScore
FROM course co
inner join (
SELECT c_id, avg(sc.s_score) as avgScore
from score sc
GROUP BY sc.c_id
)sco on sco.c_id = co.c_id
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
法1先连接后条件控制
select st.s_id,st.s_name,avg(sc.s_score) from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id having avg(sc.s_score)>=85
法2 先条件控制 后连接
SELECT st.s_id,st.s_name,avgScore
from student st
INNER JOIN (
select s_id,avg(s_score) as avgScore
FROM score
GROUP BY s_id
HAVING avgScore >= 85
) sco on sco.s_id = st.s_id
-- 34、查询课程名称为"数学",且分数低于60的学生姓名和分数
select s_name,s_score
FROM student
INNER JOIN (
select s_id,s_score
from score
WHERE c_id = (
select c_id
from course
WHERE course.c_name = '数学'
) and s_score < 60
) scc on scc.s_id = student.s_id
-- 35、查询所有学生的课程及分数情况
SELECT st.s_name,scc.s_score,c_name
FROM student st
INNER JOIN (
SELECT sc.s_id,sc.s_score,c_name
from score sc
left JOIN course co on co.c_id = sc.c_id
) scc on scc.s_id = st.s_id
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT s_name, c_name, s_score
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
LEFT JOIN course co on co.c_id = sc.c_id
where st.s_id in (
SELECT scc.s_id from (
select s_id,max(s_score)
FROM score sc
LEFT JOIN course co on co.c_id = sc.c_id
GROUP BY s_id
HAVING min(s_score) > 70
) scc
)
-- 37、查询不及格的学生id,姓名,课程id,课程名称,分数
SELECT st.s_id,st.s_name, co.c_name, sc.s_score
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
LEFT JOIN course co on co.c_id = sc.c_id
where sc.s_score < 60
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT DISTINCT(st.s_id),st.s_name
from student st
LEFT JOIN score sc on st.s_id = sc.s_id
where sc.c_id = 01 and sc.s_score >= 80
-- 39、求每门课程的学生人数
SELECT sc.c_id, co.c_name,COUNT(*) as num
from score sc
LEFT JOIN course co on co.c_id = sc.c_id
GROUP BY sc.c_id, co.c_name
-- 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT student.*, score.s_score from student
INNER JOIN score on student.s_id = score.s_id
WHERE score.s_score = (
SELECT MAX(s_score) as maxscore from (
SELECT sc.s_id,sc.s_score
from score sc
LEFT JOIN course co on co.c_id = sc.c_id
LEFT JOIN teacher te on co.t_id = te.t_id
where te.t_name = '张三'
) bb
)
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 ??
法1在这里用了inner join后会有概念是重复的记录:“01 课与 03课不同”=“03 课与 01 课不同是重复记录”
select a.s_id,a.c_id,a.s_score,b.*
from score as a
INNER JOIN score as b
on a.s_id = b.s_id
and a.c_id != b.c_id
and a.s_score = b.s_score
所以这里取唯一可以直接用group by
GROUP BY a.c_id 报错 GROUP BY a.c_id,a.s_id 不报错???
select a.s_id,a.c_id,a.s_score
from score as a
INNER JOIN score as b
on a.s_id = b.s_id
and a.c_id != b.c_id
and a.s_score = b.s_score
GROUP BY a.c_id,a.s_id
-- 42、查询每门功成绩最好的前两名 ??
-- 借鉴(更准确,漂亮):
-- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,
-- 若人数相同,按课程号升序排列
SELECT c.c_id,cnt_student
FROM course c
INNER JOIN(
select c_id, COUNT(*) as cnt_student
from score sc
GROUP BY c_id
HAVING cnt_student > 5
) t1
on c.c_id = t1.c_id
ORDER BY cnt_student DESC, c.c_id;
-- 44、检索至少选修两门课程的学生学号
select s_id
from score
group by s_id
having count(*) >= 2;
-- 45、查询选修了全部课程的学生信息
SELECT student.* FROM student where student.s_id in (
SELECT s_id
from score
GROUP BY s_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM course)
);