#--学生表
create table student(
s_id vaechar(20),
s_name vaechar(20) not NULL,
s_birth vaechar(20) not NULL,
s_sex vaechar(10) not NULL,
constraint pk_student PRIMARY KEY (s_id))
#--课程表
create table course(
c_id vaechar(20),
c_name vaechar(20) not NULL DEFAULT '',
t_id vaechar(20) not NULL,
PRIMARY KEY(c_id))
#--教师表
create table teacher(
t_id vaechar(20),
t_name vaechar(20) not NULL DEFAULT '',
PRIMARY KEY(t_id))
#--成绩表
create table score(
s_id varchar(20),
c_id varchar(20),
s_score int(3),
PRIMARY KEY(s_id,c_id))
alter table teacher convert to character set utf8
#--插入学生表测试数据
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' , '女');
#--课程表测试数据
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
#--教师表测试数据
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
#--成绩表测试数据
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);
#-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select *
from student st inner join score sc
on st.s_id = sc.s_id and sc.c_id = '01'
left outer join score s
on st.s_id = s.s_id and s.c_id = '02'
where sc.s_score > s.s_score;
#-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select *
from student st inner join score sc
on st.s_id = sc.s_id and sc.c_id = '01'
left join score s
on st.s_id = s.s_id and s.c_id = '02'
where sc.s_score < s.s_score;
#-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.s_id, st.s_name, round(sum(s.s_score)/count(*),2) avg_score
from
score s left join student st
on s.s_id = st.s_id
group by s.s_id
having sum(s.s_score)/count(*) >= 60
#-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select st.s_id, st.s_name, count(s.c_id), sum(s.s_score)
from
student st left join score s
on st.s_id = s.s_id
group by st.s_id;
#-- 6、查询"李"姓老师的数量
select count(*)
from teacher
where t_name like '李%'
#-- 7、查询学过"张三"老师授课的同学的信息
select t.t_name, st.*
from score s left join course c
on s.c_id = c.c_id
left join teacher t
on c.t_id = t.t_id
left join student st
on s.s_id = st.s_id
where t.t_id = '01'
group by st.s_name
order by s.s_id
#-- 8、查询没学过"张三"老师授课的同学的信息
select *
from student
where s_id not in
(select s.s_id
from score s left join course c
on s.c_id = c.c_id
left join teacher t
on c.t_id = t.t_id
left join student st
on s.s_id = st.s_id
where t.t_id = '01'
group by st.s_name
order by s.s_id)
#-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select *
from score s inner join student st
on s.s_id = st.s_id
where s.c_id = '01' or s.c_id = '02'
group by s.s_id
having count(*)>1
#-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
#--方法一
select *
from(
select s.s_id, s.c_id, st.s_name, st.s_birth
from score s inner join student st
on s.s_id = st.s_id
where s.c_id = '01' or s.c_id = '02'
group by s.s_id
having count(*) = 1
) a
where a.c_id = '01'
#--方法二
select *
from student st
where st.s_id in (select s_id from score where c_id = '01')
and st.s_id not in (select s_id from score where c_id = '02');
#--11、查询没有学全所有课程的同学的信息
select *
from student st
where st.s_id not in (
select s_id from score group by s_id having count(*) =3)
#-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student
where s_id in
(select b.s_id from
(select s_id, c_id from score where s_id = '01') a
cross join
(select s_id, c_id from score where s_id != '01') b
where a.c_id = b.c_id
group by b.s_id)
#-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student
where s_id in
(select b.s_id from
(select s_id, c_id from score where s_id = '01') a
cross join
(select s_id, c_id from score where s_id != '01') b
where a.c_id = b.c_id
group by b.s_id
having count(*) = 3)
#-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student
where s_id not in
(select s.s_id
from score s inner join course c
on s.c_id = c.c_id
inner join teacher t
on c.t_id = t.t_id
where t.t_name = '张三')
#-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select st.* , a.avg_score
from student st inner join
(select s_id, sum(s_score)/count(*) avg_score from score
where s_score < 60
group by s_id
having count(*) >= 2) a
where st.s_id = a.s_id
#-- 16、检索"01"课程分数小于60，按分数升序排列的学生信息
select * from student st
inner join
(select s_id, s_score from score
where c_id = '01' and s_score < 60) s
on st.s_id = s.s_id
order by s.s_score asc
#-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--形式一
select st.s_id, st.s_name, s.s_score, sc.sum_score from student st
left join score s
on st.s_id = s.s_id
left join
(select s_id, sum(s_score) sum_score from score
group by s_id) sc
on st.s_id = sc.s_id
order by sc.sum_score desc
--形式二
select s.s_id,
(select s_score from score where s_id = s.s_id and c_id = '01') languages,
(select s_score from score where s_id = s.s_id and c_id = '02') maths,
(select s_score from score where s_id = s.s_id and c_id = '03') english,
avg(s.s_score) avg_score
from score s
group by s.s_id
order by avg_score desc
#-- 18.查询各科成绩最高分、最低分和平均分：
#-- 以如下形式显示：
#-- 课程ID，课程name，最高分，最低分，平均分，
#-- 及格率，中等率，优良率，优秀率
#-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select s.c_id,c.c_name, max(s.s_score), min(s.s_score), avg(s.s_score),
sum(case when s.s_score >= 60 then 1 else 0 end)/
sum(case when s.s_score then 1 else 0 end) a,
sum(case when s.s_score between 70 and 80 then 1 else 0 end)/
sum(case when s.s_score then 1 else 0 end) b,
sum(case when s.s_score between 80 and 90 then 1 else 0 end)/
sum(case when s.s_score then 1 else 0 end) c,
sum(case when s.s_score >= 90 then 1 else 0 end)/
sum(case when s.s_score then 1 else 0 end) d
from score s
left join
(select *
from course) c
on s.c_id = c.c_id
group by s.c_id
#--注意标注代码的异同点
select s.c_id,c.c_name, max(s.s_score), min(s.s_score), avg(s.s_score),
sum(case when s.s_score >= 60 then 1 else 0 end)/
sum(case when s.s_score then 1 else 0 end) a,
sum(case when s.s_score between 70 and 80 then 1 else 0 end)/
sum(case when s.s_score then 1 else 0 end) b,
sum(case when s.s_score between 80 and 90 then 1 else 0 end)/
sum(case when s.s_score then 1 else 0 end) c,
sum(case when s.s_score >= 90 then 1 else 0 end)/
sum(case when s.s_score then 1 else 0 end) d
from score s
left join
course c
on s.c_id = c.c_id
group by s.c_id
#-- 19、按各科成绩进行排序，并显示排名
set @rang := 0;
select *, @rang := @rang + 1
from score
order by s_score desc
#-- 20、查询学生的总成绩并进行排名
set @rang := 0;
select a.s_id, a.sum_score, @rang := @rang + 1
from
(select s_id, sum(s_score) sum_score from score
group by s_id
order by sum_score desc) a
#-- 21、查询不同老师所教不同课程平均分从高到低显示
select s.s_id, t.t_name, s.c_id, avg(s.s_score) avg_score
from score s left outer join course c
on s.c_id = c.c_id
left outer join teacher t
on c.t_id = t.t_id
group by s.c_id
order by avg(s.s_score) desc
#-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
set @one := 0;
set @two := 0;
set @three := 0;
select s.s_id, st.s_name, st.s_birth, st.s_sex, s.rang, s.s_score
from
(select s_id, c_id, s_score, @one :=@one + 1 rang
from score
where c_id = '01'
order by s_score desc) s
join student st
on s.s_id = st.s_id
where s.rang = 2 or s.rang = 3
union all
select s.s_id, st.s_name, st.s_birth, st.s_sex, s.rang, s.s_score
from
(select s_id, c_id, s_score, @two :=@two + 1 rang
from score
where c_id = '02'
order by s_score desc) s
join student st
on s.s_id = st.s_id
where s.rang = 2 or s.rang = 3
union all
select s.s_id, st.s_name, st.s_birth, st.s_sex, s.rang, s.s_score
from
(select s_id, c_id, s_score, @three :=@three + 1 rang
from score
where c_id = '03'
order by s_score desc) s
join student st
on s.s_id = st.s_id
where s.rang = 2 or s.rang = 3
#-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select a.c_id, co.c_name, a.A, a.p,b.B, b.p,c.C,c.p,d.D,d.p from
(select c_id, sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) A,
100 * sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*) p
from score
group by c_id) a
left join
(select c_id, sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) B,
100 * sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*) p
from score
group by c_id) b
on a.c_id = b.c_id
left join
(select c_id, sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) C,
100 * sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*) p
from score
group by c_id) c
on a.c_id = c.c_id
left join
(select c_id, sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) D,
100 * sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*) p
from score
group by c_id) d
on a.c_id = d.c_id
left join
(select * from course) co
on a.c_id = co.c_id
#-- 24、查询学生平均成绩及其名次
#--一、
set @i = 0;
set @j = 0;
set @avg_s = 0;
select s.s_id,
@i := @i + 1 '不保留空缺排名',
@j := (case when @avg_s = s.avg_score then @j else @i end) '保留空缺排名',
@avg_s := s.avg_score '平均分',
s.avg_score
from
(select s_id, avg(s_score) avg_score from score
group by s_id
order by avg_score desc)s
#--二、
select a.s_id,
@i:=@i+1 as '不保留空缺排名',
@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名',
@avg_score:=avg_s as '平均分'
from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC)a,(select @avg_score:=0,@i:=10,@k:=0)b;
#-- 25、查询各科成绩前三名的记录
#-- 1.选出sc表比s表成绩大的所有组
#-- 2.选出比当前id成绩大的 小于三个的
select * from
score s left join score sc
on s.c_id = sc.c_id and s.s_score < sc.s_score
group by s.s_id, s.c_id having count(sc.s_id) < 3
order by s.c_id,s.s_score desc
