以下是四张表，1.学生表-t_student，2.教师表-t_teacher，3.课程表-t_course， 4.成绩表-t_score
下面是表设计：
-- 1.学生表-t_student -- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别 create table t_student( sid varchar(10) primary key comment '学生编号', sname varchar(20) not null comment '学生姓名', sage varchar(10) not null comment '学生年龄', sex varchar(20) not null comment '学生性别' )comment '学生信息表'; -- 2.教师表-t_teacher -- tid 教师编号,tname 教师名称 create table t_teacher( tid varchar(10) primary key comment '教师编号', tname varchar(20) not null comment '教师名称' )comment '教师信息表'; -- 3.课程表-t_course -- cid 课程编号,cname 课程名称,tid 教师名称 create table t_course( cid varchar(10) primary key comment '课程编号', cname varchar(20) not null comment '课程名称', tid varchar(10) not null comment '教师名称', foreign key(tid) references t_teacher(tid) )comment '课程信息表'; -- 4.成绩表-t_score -- sid 学生编号,cid 课程编号,score 成绩 create table t_score( sid varchar(10) not null comment '学生编号', cid varchar(10) not null comment '课程编号', score float default 0 comment '成绩', foreign key(sid) references t_student(sid), foreign key(cid) references t_course(cid) )comment '成绩信息表'; select * from t_student; select * from t_teacher; select * from t_course; select * from t_score;
下面是面试题+解题结果（解题的方式不是只有这一种，我写的而只是其中之一）
#01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select s.*,temp.s1,temp.s2 from( select t1.sid,t1.s1,t2.s2 from (select sid,score as s1 from t_score where cid='01')t1, (select sid,score as s2 from t_score where cid='02')t2 where t1.sid=t2.sid and t1.s1>t2.s2)temp,t_student s where temp.sid=s.sid
#02）查询同时存在" 01 "课程和" 02 "课程的情况
select* from (select sid,score as s1 from t_score where cid='01') t1, (select sid,score as s2 from t_score where cid='02') t2 where t1.sid=t2.sid;
#03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from (select sid,score from t_score where cid='01')t1 left join (select sid,score from t_score where cid='02')t2 on t1.sid=t2.sid
#04）查询不存在" 01 "课程但存在" 02 "课程的情况
select * from t_score where sid not in ( select sid from t_score where cid='01') and cid='02'; select * from t_score where sid='07';
#05）查询平均成绩(分组 group by,筛选：having avg)大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.sname, round(avg(sc.score),2) score from t_student s,t_score sc where s.sid=sc.sid group by s.sid,s.sname having avg(sc.score)>=60;
#06）查询在t_score表存在成绩的学生信息
select distinct s.* from t_score sc,t_student s where sc.sid=s.sid
#07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select s.sid,s.sname, count(sc.cid) cn, sum(sc.cid) sm from t_student s left join t_score sc on sc.sid=s.sid group by s.sid,s.sname
#08）查询「李」姓老师的数量
select count(tid) from t_teacher where tname like '李%';
#09）查询学过「张三」老师授课的同学的信息
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='张三'
#10）查询没有学全所有课程的同学的信息
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))
#11）查询没学过"张三"老师讲授的任一门课程的学生姓名
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.tid and t.tname='张三' and c.cid=sc.cid)
#12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
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
#13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select * from t_student a, t_score b where a.sid = b.sid and b.cid = '01' and score < 60 order by score desc;
#14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
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,sc.cid order by avg(sc.score) desc
#15）查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c.cid, c.cname, count(c.cid) as '选修人数', 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
