MySQL 50题解析

Link： https://zhuanlan.zhihu.com/p/32137597

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');


create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');


create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');


create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

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

首先找到所有选修01课程和02课程的学生：

select sid, score from sc where sc.cid='01' as t1

select sid, score from sc where sc.cid='02' as t2

然后在从两个表里找到，sid相同（说明是同一个学生），并且t1的分比t2高的

select * from t1,t2 where t1.sid=t2.sid and t1.score>t2.score



select * 
from (select sid, score from sc where sc.cid='01') as t1,
(select sid, score from sc where sc.cid='02') as t2 
where t1.sid=t2.sid 
and t1.score>t2.score;

1.1 查询同时存在" 01 "课程和" 02 "课程的学生情况

select * from 选修01的学生 as t1，修02的学生 as t2 where t1.sid==t2.sid
select * 
from (select sid from sc where sc.cid='01') as t1,
(select sid from sc where sc.cid='02') as t2
where t1.sid=t2.sid;

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

这个显然就是left join 的情况了

select * 
from (select sid from sc where sc.cid='01') as t1
left join (select sid from sc where sc.cid='02') as t2
on t1.sid=t2.sid;

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

如果直接用right join的特殊情况，会出现null

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

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

1. 首先求出每个人的平均分
select sc.sid, avg(sc.score) as av
from sc
group by sc.sid;

2. 选出平均分大于60分的人
select sc.sid, avg(sc.score) as av
from sc
group by sc.sid
having avg(sc.score)>=60;

3. 和student表联立，得到平均分大于等于60分的同学信息
select student.*, t1.av 
from student 
inner join
(select sc.sid, avg(sc.score) as av
from sc
group by sc.sid
having avg(sc.score)>=60) as t1
on student.sid=t1.sid;

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

1. 找出同时存在于两个表中的sid

select student.*
from student,sc
where student.sid=sc.sid;

2.过滤到相同的学生
select distinct student.*
from student,sc
where student.sid=sc.sid;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩不显示)

1. 求出学生总分和选修课数目
select sc.sid,sum(sc.score) as sumscore,count(sc.cid) as countscore
from sc
group by sc.sid

2.
select student.sid, student.sname, t1.sumscore, t1.countscore
from student,(
select sc.sid,sum(sc.score) as sumscore,count(sc.cid) as countscore
from sc
group by sc.sid
) as t1
where student.sid=t1.sid;

5. 查询「李」姓老师的数量

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

6. 查询学过「张三」老师授课的同学的信息

1. 张三老师授过的课
select teacher.tname, course.cname
from course, teacher
where course.tid=teacher.tid
and teacher.tname='张三';

2.上过张三老师的课同学的信息

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

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

选完所有课程需要多少
select count(*) from course

select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId;

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

select distinct student.*
from student,sc 
where sc.cid in (select cid from sc where sc.cid='01')
and student.sid=sc.sid

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

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

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select student.sname, sc.score
from student, sc
where student.sid=sc.sid
and sc.score<60
and sc.cid='01'
order by sc.score desc