还有部分题目做不出来，第18、36题还存在报错，需要再研究下
练习数据
数据表
--1.学生表 Student(SId,Sname,Sage,Ssex)
SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表 Course(CId,Cname,TId)
CId 课程编号,Cname 课程名称,TId 教师编号
--3.教师表 Teacher(TId,Tname)
TId 教师编号,Tname 教师姓名
--4.成绩表 SC(SId,CId,score)
SId 学生编号,CId 课程编号,score 分数
创建测试数据
学生表 Student：
导入数据方法：将以下 mysql 语句，完整复制到 workbench 语句窗口（或者是 mysql 的黑窗口），然后运行即可导入，不需要另外创建表，下面表的操作一样。这些语句第一条是创建表（create table），后面都是插入数据到表中（insert into table ）。
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' , '女');
科目表 Course
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');
教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成绩表 SC
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 "课程成绩高的学生的信息及课程分数
A1
select *
from student a
inner join sc b
on a.sid=b.sid and b.cid=01
inner join sc c
on a.sid=c.sid and c.cid=02
where b.score>c.score;
A2
select a.*,c.*
from
(select *
from sc
where cid='01') a
inner join
(select *
from sc
where cid='02') b
on a.sid=b.sid
inner join student c
on a.sid=c.sid
where a.score>b.score ;
1.1
查询同时存在" 01 "课程和" 02 "课程的情况
提示：左表时01课程记录，右表是02课程记录，如果用sid能关联上左表和油表的，说明01、02课程记录都有，用 子查询+inner join
A1
select *
from (select * from sc where cid=01) a
inner join (select * from sc where cid=02) b
on a.sid=b.sid;
A2
select *
from sc a
inner join sc b
on a.sid=b.sid
where a.cid='01' and b.cid='02';
A3
select *
from sc a
inner join sc b
where a.cid=01 and b.cid=02 and a.sid=b.sid;
1.2 -no
查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
提示：不管课程02存在与否，都筛选出来，先找出存在01的课程记录，然后自己的其它课程做关联。如果是02就关联上，如果不是02就关联不上，用left join
A1
select *
from (select * from sc where cid='01') a
left join sc b
on a.sid=b.sid and b.cid='02';
A2
select *
from sc a
left join sc b
on a.sid=b.sid and b.cid='02'
where a.cid='01' ;
1.3 -no
查询不存在" 01 "课程但存在" 02 "课程的情况
提示：用子查询筛选出不存在01课程的记录后，再进行关联找存在02课程，可以用子查询筛选+inner join
A1
select *
from (select * from sc where sid not in (select sid from sc where cid='01')) a
inner join sc b
on a.sid=b.sid and b.cid='02';
提示：找出不存在01课程的学生，在这些学生里找学习过02课程的
A2
select *
from
(select * from sc where sid not in (select sid from sc where cid=01)) a
inner join
(select * from sc where cid=02) b
on a.sid=b.sid;
2
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
A1
注：group by只能查询分组字段，其它字段要以聚合函数的形式被查询出来
即select后面有的列名，必须在group by后面有
select
b.sid,b.sname,avg(score)
from sc a
inner join student b
on a.sid=b.sid
group by b.sid，b.sname
having avg(score)>=60;
A2
注：不起别名则报错
select
a.sid,a.sname,b.avg
from student a
inner join
(select
sid,avg(score) as avg
from sc
group by sid
having avg(score)>=60) b
on a.sid=b.sid;
3-no
查询在 SC 表存在成绩的学生信息
select
b.*
from
(select sid from sc
group by sid) a
left join student b
on a.sid=b.sid;
4
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
A1
select
b.sid,b.sname,a.c,a.s
from
(select sid,count(cid) as c ,sum(score) as s
from sc
group by sid) a
right join student b
on a.sid=b.sid;
A2
select
a.sid,
a.sname,
count(b.cid) as c,
sum(b.score) as s
from student a
left join sc b
on a.sid=b.sid
group by a.sid,a.sname;
4.1
查有成绩的学生信息
A1
select
*
from
(select sid
from sc
group by sid) a
inner join student b
on a.sid=b.sid;
A2
select a.sid,b.sname,b.sage,b.ssex
from sc a
inner join student b
on a.sid=b.sid
group by a.sid,b.sname,b.sage,b.ssex;
5-no
查询「李」姓老师的数量
select
count(1)
from
teacher
where tname like '李%';
6
查询学过「张三」老师授课的同学的信息
A1
select *
from student
where sid in
(select
sid
from sc
where cid in
(select
cid
from course a
inner join teacher b
on a.tid=b.tid
where tname='张三'));
A2
select *
from
(select a.sid
from sc a
inner join course b
on a.cid=b.cid
inner join teacher c
on b.tid=c.tid
where tname ='张三') d
inner join student e
on d.sid=e.sid;
7
查询没有学全所有课程的同学的信息
提示：同学的课程数<所有课程数
select a.*
from student a
inner join
(select
sid,count(cid) as c
from sc
group by sid
having c<
(select count(1) from course)
) b
on a.sid=b.sid;
8-no
A1
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select distinct a.*
from student a
inner join sc b
on a.sid=b.sid
where b.cid in
(select cid from sc where sid='01')
A2
9-no
查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
提示：可以从两方面来保证：1、没有01号同学学习的课程外的课程记录。2、课程数量一致
①1号同学学习了哪些课程
select cid from sc where sid='01'
②哪些同学学习了1号同学学习的课程外的其他课程
select sid from sc where cid not in (select cid from sc where sid='01')
③排除这些学习了其它课程的同学，并且选出其中学习课程数等于01同学课程数的
select sid from sc where sid not in (select sid from sc where cid not in (select cid from sc where sid='01'))
group by sid
having count(sid)=(select count(cid) from sc where sid='01')
④上面只查了sid。查出其它信息
select b.*
from
(select a.sid
from sc a
where a.sid not in (select sid from sc where cid not in (select cid from sc where sid='01'))
and a.sid !='01'
group by a.sid
having count(1)=(select count(cid) from sc where sid='01')) c
inner join student b
on c.sid=b.sid;
10
查询没学过"张三"老师讲授的任一门课程的学生姓名
A1我的答案
提示：找出学习过的，再用not in判断
select sname
from student
where sid not in
(select a.sid
from sc a
where cid in
(select cid
from teacher a
inner join course b
on a.tid=b.tid
where a.tname='张三'));
A2标准答案
select a.*
from student a
where a.sid not in
(select a.sid
from student a
inner join sc b
on a.sid=b.sid
inner join course c
on b.cid=c.cid
inner join teacher d
on c.tid=d.tid
where tname='张三');
11
查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
A1我的答案
select a.*,b.sname
from
(select sid,avg(score)
from sc
where score<60
group by sid
having count(cid)>=2) a
inner join student b
on a.sid=b.sid;
A2标准答案
12
检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select a.*
from student a
inner join
(select *
from sc
where cid='01' and score<'60') b
on a.sid=b.sid
order by score desc;
13
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
A1
select *
from student a
inner join sc b
on a.sid=b.sid
inner join (select sid,avg(score) as avg from sc group by sid) c
on a.sid=c.sid
order by c.avg desc;
A2
select b.*,a.*
from
(select sid,avg(score) as avg_score
from sc
group by sid) a
inner join student b
on a.sid=b.sid
inner join sc c
on b.sid=c.sid
order by avg_score desc;
14-no
查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
A1
select a.*,b.cname
from
(select
cid,
max(score) as 最高分,
min(score) as 最低分,
avg(score) as 平均分,
count(1) as 选修人数,
sum(case when score>=60 then 1 else 0 end)/count(1) as 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(1) as 中等率,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(1) as 优良率,
sum(case when score>=90 then 1 else 0 end)/count(1) as 优秀率
from sc a
group by cid
order by count(1) desc,cid asc
) a
inner join course b
on a.cid=b.cid;
A2
select
a.cid as '课程 ID',
cname as '课程 name',
max(score) as '最高分',
min(score) as '最低分',
avg(score) as '平均分',
count(a.sid) as'选修人数',
sum(case when score>=60 then 1 else 0 end)/count(a.sid) as '及格率',
sum(case when score>=70 and score<80 then 1 else 0 end)/count(a.sid) as '中等率',
sum(case when score>=80 and score<90 then 1 else 0 end)/count(a.sid) as '优良率',
sum(case when score>=90 then 1 else 0 end)/count(a.sid) as '优秀率'
from sc a
inner join course b
on a.cid=b.cid
group by a.cid,cname
order by count(a.sid) desc,a.cid asc;
15-no
按各科成绩进行排序，并显示排名， Score 重复时继续排序
运用sql变量
select
sid,cid,score,
@rank:=@rank+1 as rn
from sc, #逗号代表inner join
(select @rank:=0) as t #给变量赋初值，没有指定关联条件代表笛卡尔积（每一行都会关联这个值）
order by score desc;
15.1-no
按各科成绩进行排序，并显示排名， Score 重复时合并名次
A1我的答案
select
sid,
cid,
score,
@rank:=if(@sco=score,@rank,@rank+1) as r,
@sco:=score
from sc a,
(select @rank:=0,@sco:=null) b
order by score desc;
A2标准答案1
A2标准答案2
由于标准答案1会存在列@sco:=score列，但不写则不会执行，方法2规避掉这种情况
注：case when 第一行@sco和score值相等，执行@rank=@rank，但初始null和score肯定不相等，则执行第二行，将score赋值给@sco，执行@rank:=@rank+1
如果as t 不写，会报错Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）
select
sid,
cid,
score,
case when @sco=score then @rank
when @sco:=score then @rank:=@rank+1
end as r
from sc,
(select @rank:=0,@sco:=null) as t
order by score desc;
15.2
按各科成绩进行排序，并显示排名， Score 重复时保留空缺
select
sid,
cid,
score,
case when @sco=score then ''
when @sco:=score then @rank:=@rank+1
end as r
from sc,
(select @rank:=0,@sco:=null) as t
order by score desc;
16-no
查询学生的总成绩，并进行排名，总分重复时保留名次空缺
自定义变量
定义方法1：set @a:=2;
定义方法2：select @b:=4;
A1
提示:
①先学生总成绩降序
select
sid,
sum(score) as scos
from sc
group by sid
order by scos desc;
②再名次排序（需要两个变量：判断分数是不是与上一名相等、记录名次）
select @sco:=null,@rank:=0
注：@sco先赋值null，然后每次与scos进行比较if(@sco=scos,'',@rank+1) ，如果与scos相同，rank就返回空，不相同rank就加1。@sco每次与scos比较完成后，赋值成scos，@sco:=scos（要不然就会一直用初值null与scos比较）。由于b是两个常量，所以与a中间的inner join也可以改成逗号，代表笛卡尔积。
select
a.*,
@rank:=if(@sco=scos,'',@rank+1) as r,
@sco:=scos
from
(select
sid,
sum(score) as scos
from sc
group by sid
order by scos desc) a
inner join
(select @sco:=null,@rank:=0) b;
A2
select a.*,
case when @sco=s then ''
when @sco:=s then @rank:=@rank+1
end as r
from
(select
sid,sum(score) as s
from sc
group by sid
order by s desc) a,
(select @rank:=0,@sco:=null) as t;
16.1
查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
A1
select
a.*,
@rank:=if(@sco=scos,@rank,@rank+1) as r,
@sco:=scos
from
(select
sid,
sum(score) as scos
from sc
group by sid
order by scos desc) a
inner join
(select @sco:=null,@rank:=0) b;
A2
select a.*,
case when @sco=s then @rank
when @sco:=s then @rank:=@rank+1
end as r
from
(select
sid,sum(score) as s
from sc
group by sid
order by s desc) a,
(select @rank:=0,@sco:=null) as t;
17-no
统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select
cid,
concat(sum(case when 0<=score and score<60 then 1 else 0 end)/count(1)*100,'%') as '(0-60]',
concat(sum(case when 60<=score and score<70 then 1 else 0 end)/count(1)*100,'%') as '(60-70]',
concat(sum(case when 70<=score and score<85 then 1 else 0 end)/count(1)*100,'%') as '(70-85]',
concat(sum(case when 85<=score and score<100 then 1 else 0 end)/count(1)*100,'%') as '(85-100]'
from sc
group by cid;
18-no
查询各科成绩前三名的记录
A2
提示：前三名转化为若大于此成绩的数量小于3，则为前3名
select
*
from sc a
where (select count(cid) from sc b where a.cid=b.cid and b.score>a.score )<3
order by cid desc,score desc;
19
查询每门课程被选修的学生数
select cid,count(sid)
from sc
group by cid;
20
查询出只选修两门课程的学生学号和姓名
select b.sid,b.sname,count(cid) as count_cid
from sc a
inner join student b
on a.sid=b.sid
group by b.sid,b.sname
having count_cid='2';
21
查询男生、女生人数
select ssex,count(sid)
from student
group by ssex;
22
查询名字中含有「风」字的学生信息
select *
from student
where sname like '%风%';
23-no
查询同名同性学生名单，并统计同名人数
A1我的答案
select sname,ssex,count(1)
from student
group by sname,ssex
having count(1)>1;
A1标准答案
select
a.sname,
a.ssex,
count(1) as con
from student a
inner join student b
on a.sname=b.sname and a.ssex=b.ssex and a.sid!=b.sid
group by a.sname,a.ssex;
24
查询 1990 年出生的学生名单
select
*
from student
where year(sage) ='1990' ;
25
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select
cid,
avg(score) as avg
from sc
group by cid
order by avg desc,cid asc;
26
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
A1我的答案
select
a.sid,
a.sname,
avg(score) as avg
from student a
inner join sc b
on a.sid=b.sid
group by a.sid,a.sname
having avg>85;
A2标准答案
select
a.sid,
b.sname,
a.avg
from
(select sid,avg(score) as avg
from sc group by sid
having avg>85) a
inner join student b
on a.sid=b.sid;
27
查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
A1我的答案
select
a.sname,
b.score
from student a
inner join sc b
on a.sid=b.sid
inner join course c
on b.cid=c.cid
where score<60 and cname='数学';
A2标准答案
28
查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select
*
from student a
left join sc b
on a.sid=b.sid;
29
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select
a.sname,
c.cname,
b.score
from student a
left join sc b
on a.sid=b.sid
left join course c
on b.cid=c.cid
where score>70;
30
查询不及格的课程
select *
from course
where cid in
(select
cid
from sc
where score<60);
31
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select *
from student a
inner join sc b
on a.sid=b.sid
where cid='01' and score>80;
32
求每门课程的学生人数
select
cid,
count(1)
from sc
group by cid;
33
假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select
*
from teacher a
inner join course b
on a.tid=b.tid
inner join sc c
on b.cid=c.cid
inner join student d
on c.sid=d.sid
where a.tname='张三'
order by score desc
limit 1;
34-no
假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select *from
(select
a.*,
case when @sco=score then @rank
when @sco:=score then @rank:=@rank+1
end as rn
from
(select
a.sid,
a.score,
b.sname,
c.cid,
d.tname
from sc a
inner join student b
on a.sid=b.sid
inner join course c
on a.cid=c.cid
inner join teacher d
on c.tid=d.tid
where d.tname='张三')a,
(select @rank:=0,@sco:=null) t ) s
where rn=1;
35-no
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select
a.sid,a.cid,a.score
from sc a
inner join sc b
on a.sid=b.sid
where a.score=b.score and a.cid!=b.cid
group by a.sid,a.cid,a.score ;
36（与18相似)-no
查询每门功成绩最好的前两名
A1-报错——不知道错在哪
select
sid,cid,score,rank
from
(select
sc.*,
@rank:= if (@c_cid=cid,if(@sco=score,@rank,@rank+1),1) as rank,
@sco:=score,
@c_cid:=cid
from sc,
(select @sco:=null,@rank:=0,@c_cid:=null) t
order by cid,score desc) a
where a.rank<3;
A2
提示：成绩最好的前两名，转换成若大于此成绩的数量小于2，则为前2名
select
*
from sc a
where (select count(cid) from sc b where a.cid=b.cid and b.score>a.score )<2
order by cid desc,score desc;
37
统计每门课程的学生选修人数（超过 5 人的课程才统计）
select
cid,
count(sid) as cou
from sc
group by cid
having cou >5;
38
检索至少选修两门课程的学生学号
select
sid,
count(cid) as cou
from sc
group by sid
having cou >=2;
39
查询选修了全部课程的学生信息
A1我的答案
select b.*
from(
select
sid,count(cid) as co
from sc
group by sid
having co=( select count(cid) from course )) a
inner join student b
on a.sid=b.sid;
A2标准答案
提示：这个学生的成绩数量和课程数量一致，就保留下来
select
a.*
from student a
where (select count(1) from sc b where a.sid=b.sid)
=(select count(1) from course);
40
查询各学生的年龄，只按年份来算
select
*,year(now())-year(sage) as age
from student ;
41-no
查询各学生的年龄，按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select
*,timestampdiff(year,date(sage),curdate()) as age
from student ;
42
查询本周过生日的学生
select
*,week(sage),week(now())
from student
where week(sage)=week(now());
43
查询下周过生日的学生
select
*,week(sage),week(now())
from student
where week(sage)=week(now())+1;
44
查询本月过生日的学生
select
*,month(sage),month(now())
from student
where month(sage)=month(now());
45
查询下月过生日的学生
select
*,month(sage),month(now())
from student
where month(sage)=month(now())+1;
