一、表结构要求:
-- 1.学生表-t_student (sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别)
-- 2.教师表-t_teacher (tid 教师编号,tname 教师名称)
-- 3.课程表-t_course (cid 课程编号,cname 课程名称,tid 教师名称)
-- 4.成绩表-t_score (sid 学生编号,cid 课程编号,score 成绩)
二、题目:
01)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)
分析:
count group by 选课总数
sum 课程总成绩
外连接:左外连接(left join),从表与null值关联
select s.sid,s.sname,count(sc.cid) cn,sum(sc.score) sm
from t_student s left join t_score sc
on s.sid=sc.sid
group by s.sid,s.sname
02)查询「李」姓老师的数量
分析:t_teacher
#count group by 分组
# like'李%'
select count(t.tname) from t_teacher t
group by t.tname having t.tname like '李%'
03)查询学过「张三」老师授课的同学的信息
分析:四表连接查询 (教师表==》课程表 ==》成绩表 ==》学生表)
#多表联查:永远视为两表之间的连接查询
select s.* from t_teacher t,t_course c,t_score sc,t_student s
where t.tid=c.tid and c.cid=sc.cid and sc.sid=s.sid and t.tname='张三';
04)查询没有学全所有课程的同学的信息
分析:having 筛选
#t_course/t_student
#1、先找到学全了所有课程的学生
select sid from t_score group by sid having count(cid)=(select count(cid) from t_course)
#2、再排除学全的学生
#计算总的课程数量:select count(cid) from t_course;
select * from t_student where sid not in(
select sid from t_score
group by sid having count(cid)=(select count(cid) from t_course));
05)查询没学过"张三"老师讲授的任一门课程的学生姓名
分析:t_teacher/t_course/t_score/t_student
select * from t_student where sid not in(
select sc.sid from t_teacher t,t_course c,t_score sc
where t.tid=c.cid and t.tname='张三' and c.cid=sc.cid);
06)查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
分析:t_score/t_student
#group by 分组 having 过滤#score<60 不及格
#having count(cid)>1 两门及其以上
select s.sid,s.sname,round(avg(sc.score),2) sc
from t_score sc,t_student s
where sc.sid=s.sid and sc.score<60
group by s.sid,s.sname
having count(sc.cid)>1;
07)检索" 01 "课程分数小于 60,按分数降序排列的学生信息
分析:order by score desc
#连表查询 t_score/t_studentcid='01' 课程为01
score<60 分数小于 60
order by score desc 降序
select s.*,sc.score from t_student s left join t_score sc
on s.sid=sc.sid where sc.cid='01' and sc.score<60
order by sc.score desc;
08)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
分析:avg
#1、order by avg(score) desc 平均成绩降序排序
#2、所有课程的成绩(行列转换:if 或者 case when)
select * from t_course;
select sc.sid,round(avg(sc.score),2) as '平均成绩',
sum(if(sc.cid='01',sc.score,0)) as '语文',
sum(if(sc.cid='02',sc.score,0)) as '数学',
sum(if(sc.cid='03',sc.score,0)) as '英语'
from t_score sc group by sc.sid
order by avg(sc.score) desc;
09)查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
concat:连接函数
#选修人数:select cid,count(sid) from t_score1 group by cid
select c.cid,c.cname,
max(sc.score) as '最高分',
min(sc.score) as '最低分',
round(avg(sc.score),2) as '平均分',
concat(round(sum(if(sc.score>=60,1,0))/count(s.sid)*100,2),'%') as '及格率',
concat(round(sum(if(sc.score>=70 and sc.score<=80,1,0))/count(s.sid)*100,2),'%') as '中等率',
concat(round(sum(if(sc.score>=80 and sc.score<=90,1,0))/count(s.sid)*100,2),'%') as '优良率',
concat(round(sum(if(sc.score>=90,1,0))/count(s.sid)*100,2),'%') as '优秀率'
from t_score sc,t_course c,t_student s
where sc.cid=c.cid and sc.sid=s.sid
group by c.cid,c.cname