數(shù)據(jù)表介紹

--1.學(xué)生表
Student(SId,Sname,Sage,Ssex)
--SId 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別
--2.課程表
Course(CId,Cname,TId)
--CId 課程編號(hào),Cname 課程名稱,TId 教師編號(hào)
--3.教師表
Teacher(TId,Tname)
--TId 教師編號(hào),Tname 教師姓名
--4.成績(jī)表
SC(SId,CId,score)
--SId 學(xué)生編號(hào),CId 課程編號(hào),score 分?jǐn)?shù)

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

# 1:查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
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. 查詢同時(shí)存在" 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 "課程的情況(不存在時(shí)顯示為 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. 查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

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. 查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)

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. 查詢?cè)?SC 表存在成績(jī)的學(xué)生信息

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

#5. 查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)(沒(méi)成績(jī)的顯示為 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. 查詢「李」姓老師的數(shù)量
select count(tname) from teacher where tname like '李%'


#7 查詢學(xué)過(guò)「張三」老師授課的同學(xué)的信息
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 查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息

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 查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息

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 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

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. 查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門課程的學(xué)生姓名
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 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

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 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息
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 按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
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 按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)保留名次空缺

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 查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺

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 統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱，[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查詢各科成績(jī)前三名的記錄
select * from 
(select cid, score, rank () over(partition by cid order by score ) mm from sc) as a 
where a.mm <= 3

#20 查詢男生、女生人數(shù)
select ssex, count(ssex) from Student group by ssex


#21 查詢名字中含有「風(fēng)」字的學(xué)生信息
select * from student where sname like '%風(fēng)%'


#22 查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
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 年出生的學(xué)生名單
select * from student where sage like '%1990%' ;
select * from student where year(sage) = 1990;


# 24 查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列

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

# 25 查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
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 查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)

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 = '數(shù)學(xué)'and score <= 60


# 27 查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒(méi)成績(jī)，沒(méi)選課的情況）
select Student.Sname, SC.score,sc.cid from SC right join Student 
on Student.SId = SC.SId

# 28 查詢?nèi)魏我婚T課程成績(jī)?cè)?70 分以上的姓名、課程名稱和分?jǐn)?shù)
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 成績(jī)不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
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 成績(jī)有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
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 統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）
select count(sc.cid) as count from sc left join student 
on sc.sid = student.sid
group by cid
having count > 5


#32 查詢選修了全部課程的學(xué)生信息
select count(distinct(cid))as count, sid from sc 
group by sid
having count = (select count(cid) from course)