Mysql习题

1、查询所有的课程的名称以及对应的任课老师姓名

select coname as '课程名称',tname as '教师姓名' from course join teacher on course.teacher_id = teacher.tid;


2、查询学生表中男女生各有多少人

select gender as '性别',count(gender) as '人数' from student group by gender;


3、查询物理成绩等于100的学生的姓名

版本1，失败
select sname from score join student on score.student_id=student.stid join course on score.course_id=course.coid where coname='物理' and score=100;
版本2：成功
select sname as '学生姓名',coname as '课程',score as '分数' from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid where score=100 and coname='物理';


4、查询平均成绩大于八十分的同学的姓名和平均成绩

select sname as '姓名',avg(score) as '平均成绩' from score join student on score.student_id=student.sid group by sid having avg(score)>80;


5、查询所有学生的学号，姓名，选课数，总成绩

select sid as '学号',sname as '姓名',count(course_id) as '选课数',sum(score) as '总成绩' from score join student on score.student_id=student.sid group by sname;


6、 查询姓李老师的个数

select count(tname) from teacher where tname REGEXP '^李.*';


7、 查询没有报李平老师课的学生姓名

select * from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid join teacher tea on co.teacher_id=tea.tid where not tname='李平老师' group by sname; 


8、 查询物理课程比生物课程高的学生的学号

版本1：失败
select group_concat(student_id,score,coname) from score join course on score.course_id=course.coid where coname in ('物理','生物') group by student_id;
版本2：
物理表：
select sid,sname,course_id,score as '物理分数' from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid where coname='物理';
生物表：
select sid,sname,course_id,score as '生物分数' from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid where coname='生物';
联表：成功！
select * from student s join(select sid,sname,course_id,score as '物理分数' from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid where coname='物理') wl on s.sid=wl.sid join (select sid,sname,course_id,score as '生物分数' from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid where coname='生物') sw on s.sid=sw.sid where 物理分数>80;


9、 查询没有同时选修物理课程和体育课程的学生姓名

版本1：失败
select sname,coname from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid group by sname; 
版本2：成功
select sname,group_concat(coname) from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid where coname in ('物理','体育') group by sname having count(sname)=2 order by sname;


10、查询挂科超过两门(包括两门)的学生姓名和班级

select sname,count(score) from score join student on score.student_id= student.sid join class on student.class_id=class.cid where score<60 group by sname having not count(score)<2;


11、查询选修了所有课程的学生姓名

select * from score join student on score.student_id=student.sid group by student_id having count(score)=4 order by sname;


12、查询李平老师教的课程的所有成绩记录

select * from score join course on score.course_id=course.coid join teacher on course.teacher_id=teacher.tid where tname='李平老师';


13、查询全部学生都选修了的课程号和课程名

select count(student_id) from score join course on score.course_id=course.coid group by coname order by course_id;

empty

14、查询每门课程被选修的次数

select coname,count(score) from score join course on score.course_id=course.coid group by coname;


15、查询只选修了一门课程的学生姓名和学号

select * from score join student on score.student_id=student.sid group by sid having count(score)=1;


16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

select distinct score from score join student on score.student_id=student.sid order by score desc;


17、查询平均成绩大于85的学生姓名和平均成绩

select sname,avg(score) from score join student on score.student_id=student.sid group by sname having avg(score)>85;


18、查询生物成绩不及格的学生姓名和对应生物分数

select sname,score from score join student on score.student_id=student.sid join course on score.course_id=course.coid where coname='生物' group by sname having score<60;


19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

代码：
select sname,avg(score) from score join student on score.student_id=student.sid join course on score.course_id=course.coid join teacher on course.teacher_id=teacher.tid where tname='李平老师' group by sname order by avg(score) desc limit 1;
看细节：
select group_concat(sname,coname,score) from score join student on score.student_id=student.sid join course on score.course_id=course.coid join teacher on course.teacher_id=teacher.tid where tname='李平老师' group by sname;


20、查询每门课程成绩最好的前两名学生姓名

失败1：
select * from score join student on score.student_id=student.sid join course on score.course_id=course.coid order by score desc limit 2 where course_id in (1,2,3,4);
成功：
(select * from score join student on score.student_id=student.sid join course on score.course_id=course.coid where course_id=1 order by score desc limit 2)
union all
(select * from score join student on score.student_id=student.sid join course on score.course_id=course.coid where course_id=2 order by score desc limit 2)
union all
(select * from score join student on score.student_id=student.sid join course on score.course_id=course.coid where course_id=3 order by score desc limit 2)
union all
(select * from score join student on score.student_id=student.sid join course on score.course_id=course.coid where course_id=4 order by score desc limit 2)


21、查询不同课程但成绩相同的学号，课程号，成绩

以分数为组？？？？
不对
select group_concat(' 学号：',student_id,' 课程号：',course_id),score as '分数' from score group by score having count(student_id)>1;

2：
select * from score group by score;
select * from


22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；

select * from score sc join student st on sc.student_id=st.sid join course co on sc.course_id=co.coid join teacher tea on co.teacher_id=tea.tid where not tname='李平老师' group by sname;


23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

select * from score join student on score.student_id=student.sid join course on score.course_id=course.coid where course_id in(select course_id from score where student_id=1);


24、任课最多的老师中学生单科成绩最高的学生姓名

找到任课最多的老师课程：
select teacher_id from course group by teacher_id order by count(teacher_id) desc limit 1; 
找到任课最多的老师的课程：
select coid from course where teacher_id=(select teacher_id from course group by teacher_id order by count(teacher_id) desc limit 1);
找单科成绩最高的学生:
select sname,max(score),course_id from score join student on score.student_id=student.sid group by course_id having course_id in (select coid from course where teacher_id=(select teacher_id from course group by teacher_id order by count(teacher_id) desc limit 1));