数据表介绍
https://www.jianshu.com/p/476b52ee4f1b
--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
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-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-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 "课程成绩高的学生的信息及课程分数
select * from student s1 join
(select t1.SId,class1,class2 from (select SId, score class1 from sc where CId = '01') as t1 ,
(select SId, score class2 from sc where CId = '02') as t2
where t1.SId = t2.SId and t1.class1 > t2.class2) r
on r.SId = s1.SId;
2 查询同时存在" 01 "课程和" 02 "课程的情况
select * from (select * from sc where CId = '01') as t1
join (select * from sc where CId = '02') as t2
on t1.SId = t2.SId;
3 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from (select * from sc where CId = '01') as t1
left join (select * from sc where CId = '02') as t2
on t1.SId = t2.SId;
4 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from (select * from sc where CId = '02') as t1
where t1.SId not in (select SId from sc where CId = '01');
5 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select t2.SId, s1.Sname, t2.average from
(select * from (select SId, avg(score) average from sc group by SId)
as t1 where t1.average >= 60) as t2
join student s1 on t2.SId = s1.SId;
select t1.SId, s1.Sname, t1.aver from (select SId, avg(score) aver from sc
group by SId having aver >= 60) as t1 join student s1
on t1.SId = s1.SId;
6 查询在 SC 表存在成绩的学生信息
select * from student as s1 where s1.SId in (select distinct SId from sc);
select s1.SId, s1.Sname,s1.Sage, s1.Ssex from student as s1
join (select distinct SId from sc) as t1 on s1.SId = t1.SId;
7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)
select s1.SId,s1.Sname,t1.course_num,t1.grade from student s1 left join
(select SId,count(*) course_num, sum(score) grade from sc
group by SId) as t1 on s1.SId = t1.SId;
8 查有成绩的学生信息
select * from student as s1 where s1.SId in (select distinct SId from sc);
select s1.SId, s1.Sname,s1.Sage, s1.Ssex from student as s1
join (select distinct SId from sc) as t1 on s1.SId = t1.SId;
9 查询「李」姓老师的数量
select * from teacher where Tname like '李%';
10 查询学过「张三」老师授课的同学的信息
select * from student where SId in
(select distinct SId from sc where CId =
(select CId from course where CId =
(select TId from teacher where Tname = '张三')));
11 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select t3.SId, s1.Sname, t3.aver from
(select t2.SId,avg(score) aver from
(select SId,count(*) from
(select * from sc where score < 60) t1 group by SId having count(*) >=2) t2
left join sc on sc.SId = t2.SId group by t2.SId) t3 left join student s1
on s1.SId = t3.SId ;
考虑到不及格分数的平均不是平均分
12 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select s1.* from
(select * from
(select * from sc where CId = '01' and score < 60) as t1 order by t1.score Desc)
as t2 left join student as s1 on t2.SId = s1.SId;
13 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s1.*,aver from
(select sid,avg(score) aver from sc group by sid)
as t1 right join sc s1 on t1.sid = s1.sid order by aver desc;
14 查询各科成绩最高分、最低分和平均分
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cid,
max(score) as '最高分',
min(score) as '最低分',
avg(score) as '平均分',
count(*) as '选修人数' ,
sum(if(score >= 60,1,0))/count(*) as '及格率',
sum(if(score between 70 and 80,1,0))/count(*) as '中等率',
sum(if(score between 80 and 90,1,0))/count(*) as '优良率',
sum(if(score >= 90,1,0))/count(*) as '优秀率' from sc group by cid
order by count(*) desc, cid asc;
15 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
这一道题有点tricky，可以用变量，但也有更为简单的方法，即自交（左交）
用sc中的score和自己进行对比，来计算“比当前分数高的分数有几个”。
select t1.*,count(t2.score)+1 as rank from sc as t1 left join sc as t2
on t1.score < t2.score group by t1.cid, t1.sid,t1.score order by rank asc;
16 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
这里主要学习一下使用变量。在SQL里面变量用@来标识。
set @rank = 0;
select *, @rank := @rank + 1 as rank from (select * from
(select sid, sum(score) as score from sc group by sid) as t2
order by t2.score desc) t3;
select sc.*, @r := @r + 1 as rank from sc,(select @r := 0) r order by score;
select sc.*,
case when @p =score then @r
when @p:=score then @r :=@r+1 end
rank from sc,(select @r:=0,@p:=null) r order by score desc;
select * from (select sc.*, @c := if(@p=score,@c,@r) as rank, @p:=score, @r:=@r+1 from sc, (select @p:=null,@r:=1,@c:=0) r order by score) c;
17 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select *,sum(if(t1.score between 85 and 100,1,0)) as '[100-85]',
sum(if(t1.score between 85 and 100,1,0))/count(*)*100 as 'rate%',
sum(if(t1.score between 70 and 85,1,0)) as '[70-85]',
sum(if(t1.score between 70 and 85,1,0))/count(*)*100 as 'rate%'
from sc as t1 left join course as t2 on t1.cid = t2.cid group by t1.cid;
select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;
18 查询各科成绩前三名的记录
