数据表介绍

--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 分数

select * from  Student;
select * from  Course;
select * from `Teacher`;
select * from `SC`;

# 1:查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select a.SId,  a.Cid, b.Cid, a.score, b.score from 
(select * from SC where Cid = '01') as a 
join 
(select * from SC where Cid = '02') as b 
on a.SId= b.SId
where a.score < b.score)  as c
ON C.SId = Stdent.SId

select * from `Student` where SId in ('01','05')

# 1.1. 查询同时存在" 01 "课程和" 02 "课程的情况
select * from SC ;

select * from SC left join Student on  Student.SId = SC.SId

select * FROM SC LEFT JOIN Student on Student.SId = SC.SId
where SC.CId in ('01', '02')


#1.2. 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select SC.CId, Student.Sname FROM SC left JOIN Student on Student.SId = SC.SId 
where SC.CId  in ('01','02')

#1.3. 查询不存在" 01 "课程但存在" 02 "课程的情况
select SC.CId, Student.Sname FROM SC left JOIN Student on Student.SId = SC.SId 
where SC.CId  in ('03','02')

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

select avg(score) as avge,Sname from 
(select SC.CId, Student.Sname,SC.score FROM SC left JOIN Student on Student.SId = SC.SId ) as a
group by Sname
having avge>= 60
order by avge 


#3. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select sum(score) as sum ,Sname, count(CId) as course from 
(select SC.CId, Student.Sname,SC.score FROM SC left JOIN Student on Student.SId = SC.SId ) as a
group by Sname

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

select Student.SId,SC.CId,Student.Sname, SC.score from SC left join Student 
on Student.SId = SC.SId

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

select student.SID, student.SNAME, a.total, a.count from student left join (select SId, sum(score) as total ,count(CId) as count from SC 
group by  SId) as a on student.sid = a.sid


#6. 查询「李」姓老师的数量
select count(tname) from teacher where tname like '李%'


#7 查询学过「张三」老师授课的同学的信息
select student.sid, student.sname, b.tname from student  right join 
(select distinct a.sid, a.tid, teacher. tname from (
select sc.sid, sc.cid, course.tid from sc left join course
on sc.cid=course.cid) as a left join teacher on a.tid = teacher.tid
where tname = '张三') as b
on student.sid = b.sid


#8 查询没有学全所有课程的同学的信息

select sname,count(cid) as count from 
(select  student.sid, student.sname, student.sage, student.ssex, student.sc.cid
from student right join sc 
on student.sid = sc.sid) as c
group by sname
having count !=3

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

select student.sname, a.* from student right join 
select * from sc where cid in (select cid from sc where sid = '01')
 on student.sid = a.sid

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

select distinct c.sname, count(c.cid) as count  from 
(select  student.sname, a.* from student right join 
(select * from sc where cid in (select cid from sc where sid = '01') )as a
 on student.sid = a.sid)as c
group by c.sname
having count = 3


#11. 查询没学过"张三"老师讲授的任一门课程的学生姓名
select student.sname from student join 
(select * from sc where cid != (select cid from course where  Tid in  (select  tid from teacher where tname like '张%')))as a
on a.sid= student.sid


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

select sname, sid, avg (score), sum(score)from 
(select sc.*, student.sname from student right join sc
on student.sid = sc.sid where score < '60') as a
group by sid, sname


#13 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select a.*, student.sname from 
(select sid,score from sc where cid ='01' and score <60
order by score desc) as a
left join student 
on a.sid= student.sid


#14 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select student.sid, student.sname,  sum(sc.score) as sum, avg(sc.score) as avg
from student right join sc on student.sid = sc.sid
group by student.sid, student.sname
order by avg desc


#15 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select case score when min(score) then 'a' else 'null' end seq, score, sid, avg (score) from 
(select sid, avg (score), sum(score) as score from sc group by sid) as a
group by score,sid


# 16 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

select student.sname, a.* from student right join
(select sum(score)as sum ,sid from sc group by sid ) as a 
on student.sid=a.sid
order by sum desc

# 17 rank 排名
set @currank :=0
select score,  @currank := @currank + 1  as a from sc order by score desc
SELECT sid,score, rank() over(partition by sid ORDER BY score )mm from sc


# 18 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select sid,
count(if( score>85, 1, null)) as "[100-85]",
count(if( score<60, 1, null)) as "[0-60]" ,
count(if( score<85 and score > 70, 1, null)) as "[85-70]",
count(if( score<70 and score > 60, 1, null)) as "[70-60]"
from sc group by sid


#19查询各科成绩前三名的记录
select * from 
(select cid, score, rank () over(partition by cid order by score ) mm from sc) as a 
where a.mm <= 3

#20 查询男生、女生人数
select ssex, count(ssex) from Student group by ssex


#21 查询名字中含有「风」字的学生信息
select * from student where sname like '%风%'


#22 查询同名同性学生名单，并统计同名人数
select sname, ssex from student where sname in 
(select sname from student group by snameF
having count(sname) >= 2 and count(ssex) >=2)

#23 查询 1990 年出生的学生名单
select * from student where sage like '%1990%' ;
select * from student where year(sage) = 1990;


# 24 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select cid, avg(score) as avg from sc group by cid
order by avg desc, cid

# 25 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select Student.SId,Student.Sname, avg(SC.score) from SC left join Student 
on Student.SId = SC.SId
group by sc.sid,Student.Sname
having avg(SC.score) >= 85


# 26 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select course.cname, a.* from course right join 
(select Student.Sname, SC.score,sc.cid from SC left join Student 
on Student.SId = SC.SId)  as a
on course.cid = a.cid
where course.cname = '数学'and score <= 60


# 27 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select Student.Sname, SC.score,sc.cid from SC right join Student 
on Student.SId = SC.SId

# 28 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select course.cname, a.* from course right join 
(select Student.Sname, SC.score,sc.cid from SC left join Student 
on Student.SId = SC.SId)  as a
on course.cid = a.cid
where score >70 

# 29 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select max(score)
from sc , student, course, teacher
where sc.sid = student.sid
and sc.cid = course.cid
and course.tid = teacher.tid
and tname = '张三'


# 30 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select *, rank () over (order by score) m  from 
(select distinct (sc.sid), score
from sc, student, course, teacher
where sc.sid = student.sid
and sc.cid = course.cid
and course.tid = teacher.tid
and tname = '张三') as c
where c.m = 1


# 31 统计每门课程的学生选修人数（超过 5 人的课程才统计）
select count(sc.cid) as count from sc left join student 
on sc.sid = student.sid
group by cid
having count > 5


#32 查询选修了全部课程的学生信息
select count(distinct(cid))as count, sid from sc 
group by sid
having count = (select count(cid) from course)