MySQL经典45题

一、 数据准备

1、课程表course

2、成绩表sc（部分数据）

3、学生表student（部分数据）

4、教师表teacher

二、题目讲解

1、查询01课程比02课程成绩高的学生的信息及课程分数

select *
from student as a
right join sc as b
on a.sid=b.sid

inner join sc as c
on a.sid=c.sid and b.cid=01 and c.cid=02
where b.score>c.score;

1.1、查询同时存在01课程和02课程的情况

select * 
from sc as a
inner join sc as b
on a.sid=b.sid and a.cid='01' and b.cid='02';

1.2查询存在01课程但可能不存在02课程的情况（不存在时显示为null）

select * 
from sc as a
left join sc as b
on a.sid=b.sid and b.cid='02' 
where a.cid = '01';

1.3、查询不存在01课程，存在02课程的情况

select * 
from sc
where sid not in (select sid from sc where cid='01')
and cid ='02';

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.sid,a.sname,avg(b.score) as s_avg
from student as a
right join sc as b
on a.sid=b.sid
group by b.sid
having s_avg>60;

3、查询在SC表存在成绩的学生信息

select a.*
from student as a
right join sc as b
on a.sid=b.sid
group by b.sid;

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程总成绩（没成绩的显示为null）

select a.sid,a.sname,count(b.cid) as c_sum,sum(b.score) as s_sum
from student as a
left join sc as b
on a.sid=b.sid
group by a.sid;

4.1、查询有成绩的学生信息

#法1
select * from student
where sid in(select distinct sid from sc);
#法2
select * from student
where sid in(select sid from sc group by sid);
#法3
select a.* 
from student as a
inner join sc as b
on a.sid=b.sid
group by a.sid;

5、查看李老师的数量

select count(*) 
from teacher
where tname like '李%';

6、查询学过张三老师的课的学生信息

select student.* 
from teacher,course,sc,student
where teacher.tname = '张三'
and teacher.tid=course.tid
and course.cid=sc.cid
and sc.sid=student.sid;

7、查询没有学全所有课程的同学的信息

select a.*,count(cid) as c_count
from student as a
inner join sc as b
on a.sid=b.sid
group by a.sid
having c_count<(select count(cid) from course);

8、查询至少有一门课与学号为01的同学所学相同的同学信息

#法1
select cid from sc where sid = '01';
select * from student where sid in(
select distinct sid from sc 
where cid in(select cid from sc where sid = '01'));

#法2
select distinct a.* 
from student as a
inner join sc as b
on a.sid=b.sid
where cid in(select cid from sc where sid = '01');

9、查询与学号为01的同学所学课程完全相同的其他同学信息

select * from student where sid in(
select a.sid from (select sid,group_concat(cid order by cid) as courses 
from sc group by sid) as a
inner join (select sid,group_concat(cid order by cid) as courses 
from sc group by sid having sid='01') as b
on a.sid != 1 and a.courses = b.courses);

10、查询没有学过张三老师讲授的任意一门课程的学生姓名

select distinct sname from student where sid not in(
select sid from sc where cid in(
select a.cid from course as a
inner join teacher as b
on a.tid=b.tid
where b.tname = '张三' ));