MySQL题目组一（5.1版本MySQL）

题目组一（MySQL5.1版本）
建表语句
学生表Student
create table Student(Sid varchar(6), 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(‘08’,‘王菊’,‘1990-01-20’,‘女’);

成绩表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)

课程表Cource
create table Course(Cid varchar(10),Cname varchar(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’ , ‘王五’)

题目与答案
1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
（1）01课程的学生信息和课程分数
select Sname,Sage,score,Course.Cid from Student,SC,Course
where Student.Sid=SC.Sid and SC.Cid=Course.Cid and Course.Cid=01;
（2）02课程的学生信息和课程分数
select Sname,Sage,score,Course.Cid from Student,SC,Course
where Student.Sid=SC.Sid and SC.Cid=Course.Cid and Course.Cid=02;

表1：s1
表2：s2
select
s1.Sname as ‘姓名’,s1.Sage as ‘年龄’,s1.score as ‘01课程成绩’,s2.score as ‘02课程成绩’
from
(select Sname,Sage,score,Course.Cid from Student,SC,Course
where Student.Sid=SC.Sid and SC.Cid=Course.Cid and Course.Cid=01) as s1
inner join
(select Sname,Sage,score,Course.Cid from Student,SC,Course
where Student.Sid=SC.Sid and SC.Cid=Course.Cid and Course.Cid=02) as s2
on s1.Sname=s2.Sname where s1.score>s2.score;

2.查询平均成绩大于等于60的同学的学生编号和学生姓名和平均成绩
查询：学生编号(Student)、学生姓名(Student)、平均成绩(SC)
判断条件：平均成绩大于等于60
select Student.Sid,Sname,avg(score) as avg_score
from Student
left join
SC
on Student.Sid=SC.Sid
group by Student.Sid
having avg_score>=60;

3.查询在SC表存在成绩的学生信息
select
Student.Sid,Sname,Sage
from SC
left join
Student
on SC.Sid=Student.Sid
where score != 0 group by Sname;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩显示为null）
查询：学生编号(Sid)、学生姓名(Sname)、选课总数(Cid)、所有课程总成绩(score)
表：Student、Course、SC
select Student.Sid ‘学生编号’,Student.Sname ‘学生姓名’,sum(SC.score) ‘总成绩’,count(SC.Cid) ‘选课总数’
from Student left join SC
on Student.Sid=SC.Sid
left join Course
on SC.Cid=Course.Cid
group by Student.Sid;

5.查有成绩的学生信息
select
*
from Student
where Sid in (select Sid from SC where score is not null)

6.查询“李”姓老师的数量
select Tname,count(*) as ‘人数’ from Teacher where Tname like ‘李%’;

7.查询学过“张三”老师授课的学生的信息
查询：学生信息（Sname、Sage）
条件：“张三”授课
表：Student、SC
select Sname,Sage,Course.Cname,Tname from
Student
left join
SC
on Student.Sid=SC.Sid
left join
Course
on Course.Cid=SC.Cid
left join
Teacher
on Course.Tid=Teacher.Tid
where Tname like ‘张三’;

8.查询没有学全所有课程的学生信息
（1）查看有多少课程
课程数小于总课程数的信息输出
select
Student.Sid,Sname,Sage
from
Student left join SC
on Student.Sid=SC.Sid
group by SC.Sid having count(Cid)<
(select count(Cid) from Course);

9.查询和“01”号学生学习课程完全相同的其他学生的信息
01号同学学习的课程数量
select
a.,b.,count(Cid)
from
Student as a
left join SC as b
on a.Sid=b.Sid
group by b.Sid
having count(Cid)=(
select count(Cid) from SC where Sid=01
);

查询学号为01的学生的选课信息
select Cid from SC where Sid=01
10.查询至少有一门课与学号为01的同学所学相同的学生信息
select
Student.,SC.
from Student left join SC
on Student.Sid=SC.Sid
group by Student.Sid
having SC.Cid in (select Cid from SC where Sid=01)

11.查询没学过“张三”老师讲授的任意一门课程的学生姓名
（1）学过张三课程的学生id
select
a.Sid
from SC a
left join Course b
on a.Cid=b.Cid
left join Teacher c
on b.Tid=c.Tid
left join Student d
on a.Sid=d.Sid
where Tname =‘张三’
（2）没学过张三课程的学生姓名
select Sname from Student
where Sid not in
(select
a.Sid
from SC a
left join Course b
on a.Cid=b.Cid
left join Teacher c
on b.Tid=c.Tid
left join Student d
on a.Sid=d.Sid
where Tname =‘张三’)

12.查询两门及以上不及格课程的学生学号、姓名、平均成绩
select
a.Sid,Sname,score,avg(score)
from SC a
left join Course b
on a.Cid=b.Cid
left join Teacher c
on b.Tid=c.Tid
left join Student d
on a.Sid=d.Sid
where score<60
group by d.Sid
having count(b.Cid)>=2;

13.检索“01”课程分数小于60，按分数降序排列的学生信息
select
Sname,Sage,d.Sid,score
from SC a
left join Course b
on a.Cid=b.Cid
left join Teacher c
on b.Tid=c.Tid
left join Student d
on a.Sid=d.Sid
where score < 60
and b.Cid=01
order by score desc;

14.按平均成绩从高到低显示所有学生的所有课程以及平均成绩
查询：平均成绩，课程总成绩1，课程总成绩2，课程总成绩3
select
avg(score) avg_score,a.Sid,
sum(case when a.Cid=01 then score else null end) as sum_score1,
sum(case when a.Cid=02 then score else null end) as sum_score2,
sum(case when a.Cid=03 then score else null end) as sum_score3
from SC a
left join Course b
on a.Cid=b.Cid
left join Teacher c
on b.Tid=c.Tid
left join Student d
on a.Sid=d.Sid
group by Sname
order by avg_score desc;

这里要特别注意分数能不能取到（between and是都可以取到）
15.查询各科成绩最高分、最低分和平均分
以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
（及格为>=60，中等为70-80，优良为80-90，优秀为>=90）
select
b.Cid,Cname,max(score),min(score),avg(score),
sum(case when score>=60 then 1 else 0 end)/count(score) as ‘及格率’,
sum(case when score >= 70 and score <80 then 1 else 0 end)/count(*) as ‘中等率’,
sum(case when score >=80 and score <90 then 1 else 0 end)/count(score) as ‘优良率’,
sum(case when score>=90 then 1 else 0 end)/count(score) as ‘优秀率’
from SC a
left join Course b
on a.Cid=b.Cid
left join Teacher c
on b.Tid=c.Tid
left join Student d
on a.Sid=d.Sid
group by Cname
order by a.Cid;

16.按照各科进行排序，显示排名，score重复时继续排序
select
Sid,Cid,score,
@rank:=@rank+1 as ‘排名’
from SC,(select @rank:=0) as t order by score desc;

17.按照各科进行排序，显示排名，score重复时合并排序
select
Sid,
Cid,
score,
case when @sco=score then @rank else @rank:=@rank+1 end as rn,
@sco:=score
from SC,(select @rank:=0,@score:=null) as t
order by score desc;

18.统计各科成绩分数段人数：课程编号，课程名称，
[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
select
a.Cid as ‘课程编号’,Cname as ‘课程名称’,
sum(case when score>=85 and score<=100 then 1 else 0 end)/count(score) as ‘100-85’,
sum(case when score>=70 and score<85 then 1 else 0 end)/count(score) as ‘85-70’,
sum(case when score>=60 and score<70 then 1 else 0 end)/count(score) as ‘70-60’,
sum(case when score<60 then 1 else 0 end)/count(score) as ‘60-0’
from SC a
left join Course b
on a.Cid=b.Cid
left join Teacher c
on b.Tid=c.Tid
left join Student d
on a.Sid=d.Sid
group by Cname
order by a.Cid;

19.查询各科成绩前三名
这里给出三种方式
解法一
select * from SC as s1
where 3>(select count(*) from SC as s2 where s1.Cid=s2.Cid and s1.score>s2.score)
order by Cid;

解法二
select * from
(select Sid,Cid,score,@rank:=@rank+1 rn from SC,(select @rank:=0) as t
where Cid=01 order by score desc limit 3) s1
union
select * from
(select Sid,Cid,score,@rank:=@rank+1 rn from SC,(select @rank:=0) as t
where Cid=02 order by score desc limit 3) s2
union
select * from
(select Sid,Cid,score,@rank:=@rank+1 rn from SC,(select @rank:=0) as t
where Cid=03 order by score desc limit 3) s3

解法三
select s1.score “科目一成绩”,s1.Sid as sid_01,s2.score “科目二成绩”,s2.Sid as sid_02,s1.rn “排名” from
(select Sid,score,@rank:=@rank+1 rn from SC,(select @rank:=0) as t where Cid=01 order by score desc limit 0,3) s1
join
(select Sid,score,@rank1:=@rank1+1 rn from SC,(select @rank1:=0) as t where Cid=02 order by score desc limit 0,3) s2
on s1.rn=s2.rn

20.查询只选修两门课程的学生学号和姓名
select s1.Sid,Sname,count(Cid) from
Student as s1
left join SC as s2
on s1.Sid=s2.Sid
group by s1.Sid
having count(Cid)=2;

21.查询名字中含有“风”字的学生信息
select * from Student where Sname like ‘%风%’;

22.查询1990年出生的学生名单
select * from Student where Sage like’1990%’;

23.成绩不重复，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息和成绩
select
a.Sid,sum(score) as sum_score,Sname
from SC a
left join Course b
on a.Cid=b.Cid
left join Teacher c
on b.Tid=c.Tid
left join Student d
on a.Sid=d.Sid
where Tname=‘张三’
group by a.Sid
order by sum_score desc
limit 0,1;

24.成绩有重复的情况下，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息和成绩

25.查询各学生的年龄，只按年份来算
写法一
select Sid,Sname,
date_format(now(),’%Y’)-year(Sage) as year,
Ssex from Student;
写法二
select Sid,Sname,year(now())-year(Sage) as ‘年龄’
from Student;

26.按照出生日期来算，当前月日<出生年月的月日，则，年龄减一

（1）当前月日
date_format(now(),’%m-%d’)
（2）出生年月的月日
select if(
date_format(now(),’%m-%d’)-date_format(Sage,’%m-%d’)<0,
year(Sage)-1,year(Sage)
) as year
from Student;
（3）求年龄，两个年份相减
现在的年份：select date_format(now(),’%Y’) as year from Student;
出生的年份：
select if(
date_format(now(),’%m-%d’)-date_format(Sage,’%m-%d’)<0,
year(Sage)-1,year(Sage)
) as year
from Student;