?
一、建表工作
# 学生表 Student：
create table Student(SId varchar(10) ,Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男'),('02' , '钱电' , '1990-12-21' , '男'),('03' , '孙风' , '1990-05-20' , '男'),('04' , '李云' , '1990-08-06' , '男'),('05' , '周梅' , '1991-12-01' , '女'),('06' , '吴兰' , '1992-03-01' , '女'),('07' , '郑竹' , '1989-07-01' , '女'),('09' , '张三' , '2017-12-20' , '女'),('10' , '李四' , '2017-12-25' , '女'),('11' , '李四' , '2017-12-30' , '女'),('12' , '赵六' , '2017-01-01' , '女'),('13' , '孙七' , '2018-01-01' , '女');
# 教师表 Teacher:
create table Teacher(TId varchar(10),Tname varchar(10));?
insert into Teacher values('01' , '张三'),('02' , '李四'),('03' , '王五');
# 科目表 Course:
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));?
insert into Course values('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03');
# 成绩表 SC:
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));?
insert into SC values('01' , '01' , 80),('01' , '02' , 90),('01' , '03' , 99),('02' , '01' , 70),('02' , '02' , 60),('02' , '03' , 80),('03' , '01' , 80),('03' , '02' , 80),('03' , '03' , 80),('04' , '01' , 50),('04' , '02' , 30), ('04' , '03' , 20),('05' , '01' , 76), ('05' , '02' , 87),('06' , '01' , 31),('06' , '03' , 34),('07' , '02' , 89),('07' , '03' , 98);
二、题目准备及解答
-----1、查询"01"课程成绩比"02"课程成绩高的学生的信息及分数
思路：【关键词】学生信息及分数
需要联表查询，先进行01课程比02课程大的比较，然后再拿出SID相等的数据
select s.*,sc.score from student s
left join sc on s.SId = sc.SId
left join sc a on a.SId = sc.SId
and sc.CId='01' and a.CId='02'
where sc.score>a.score
?-----2、查询平均成绩>=60分同学的学生编号、姓名和平均成绩
思路：
主要考察group by聚合函数，where和having的区别以及Sql的执行顺序。
HAVING AVG(score)>=60是在select前执行的，需要写完整
SELECT s.SId,s.Sname,AVG(sc.score)
FROM student s
LEFT JOIN sc
ON s.SId = sc.SId
GROUP BY s.SId
HAVING AVG(score)>=60
?-----3、查询在SC表中有成绩的学生信息
SELECT *
FROM sc
LEFT JOIN student s
ON s.SId = sc.SId
GROUP BY sc.SId;
-----4、查询所有同学的学生编号、姓名、选课总数、所有课程的总成绩（没成绩显示null）
SELECT s.SId,s.Sname,SUM(sc.CId),SUM(sc.score)
FROM student s
LEFT JOIN sc
ON s.SId = sc.SId
GROUP BY sc.SId;
?-----5、查询姓李老师的数量
思路：COUNT函数
SELECT COUNT(Tname)
FROM teacher
WHERE Tname LIKE "李%";
-----6、查询学过【张三】老师授课的同学的信息
SELECT s.*
FROM student s
INNER JOIN
(
SELECT SId
FROM sc
LEFT JOIN course c
ON sc.CId = c.CId
LEFT JOIN teacher t
ON course.TId = t.TId
WHERE t.Tname = '张三'
) AS a
ON s.SId = a.SId;
?-----7、查询没有学全所有课程的同学信息
思路：没有学全=课程数量<3
SELECT s.*
FROM student s
LEFT JOIN sc
ON s.SId=sc.SId
GROUP BY s.SId
HAVING COUNT(CId)<3;
?-----8、查询至少有一门课与学号为"01"的同学所学课程相同的同学信息
SELECT s.*
FROM student s
LEFT JOIN sc
ON s.SId=sc.SId
WHERE sc.CId IN (SELECT sc.CId FROM sc WHERE sc.SId = '01')
GROUP BY s.SId
HAVING COUNT(sc.CId)>=1
?-----9、查询与学号为"01"的同学所学课程完全相同的同学信息
思路：01同学的全部课程；其他同学
提取一个表不含01同学的，然后在里面过滤课程信息（科目和数量）一样的同学出来
SELECT s.*
FROM student s
INNER JOIN sc
ON s.SId = sc.SId
WHERE s.SId != '01' AND sc.CId IN (SELECT sc.CId FROM sc WHERE sc.SId = '01')
GROUP BY s.SId
HAVING COUNT(CId) = (SELECT COUNT(CId) FROM sc WHERE SId = '01')
?-----10、查询没学过"张三"老师讲授的任一门课的学生姓名
SELECT s.Sname
FROM student s
WHERE s.SId NOT IN
(SELECT sc.SId
FROM sc
INNER JOIN course c
ON sc.CId=c.CId
INNER JOIN teacher t
ON c.TId=t.TId
WHERE Tname="张三");
?-----11、查询两门及以上不合格课程的同学的学号，姓名和平均成绩
思路：score小于60，count(CId)大于2
SELECT s.SId,s.Sname,AVG(sc.score)
FROM student s
LEFT JOIN sc
ON s.SId = sc.SId
WHERE sc.score<60
GROUP BY s.SId
HAVING COUNT(sc.CId)>2
?-----12、查询"01"课程分数小于60的学生信息，按分数降序排列
思路：学过01课程学生拉一个表，然后过滤score<60的学生，加order by DESC
SELECT s.*
FROM student s
LEFT JOIN sc
ON s.SId=sc.SId
WHERE sc.CId='01' AND sc.score>60
ORDER BY sc.score DESC
?-----13、按平均成绩从高到低显示所有学生的所有课程成绩及平均成绩
思路：平均值的表因为涉及到聚合，要另外新建，然后联合。
SELECT a.SId,a.Sname,a.CId,a.score,b.avg_score
FROM
(SELECT s.SId,s.Sname,sc.CId,sc.score
FROM student s
LEFT JOIN sc
ON s.SId=sc.SId) AS a
LEFT JOIN
(SELECT sc.SId,AVG(score) avg_score
FROM sc
GROUP BY sc.SId) AS b
ON a.SId=b.SId
ORDER BY b.avg_score DESC;
?------14、查询各科成绩的最高分、平均分、最低分、选修人数、合格率、中等率、优良率、优秀率，按人数降序，课程升序
思路：最高分MAX等聚合函数，CASE WHEN的简单函数和搜索函数的区别
SELECT sc.CId,c.Cname,MAX(sc.score) AS 最高分,AVG(sc.score) AS 平均分,
MIN(sc.score) AS 最低分,COUNT(*) AS 选修人数,
(SUM(CASE WHEN sc.score>=60 THEN 1 ELSE 0 END)/COUNT(*)) AS 合格率,
(SUM(CASE WHEN sc.score>=70 AND sc.score<80 THEN 1 ELSE 0 END)/COUNT(*)) AS 中等率,
(SUM(CASE WHEN sc.score>=80 AND sc.score<90 THEN 1 ELSE 0 END)/COUNT(*)) AS 优良率,
(SUM(CASE WHEN sc.score>=90 THEN 1 ELSE 0 END)/COUNT(*)) AS 优秀率
FROM sc
INNER JOIN course c
ON sc.CId = c.CId
GROUP BY sc.CId
ORDER BY 选修人数 DESC,CId ASC
-----15、按各科成绩进行排序，显示排名，分数重复继续排名?
思路：窗口函数row_number(),rank(),dense_rank()的区别，以及row_number()使用方法；over确定其细节，partition by等价于分类
SELECT *,
row_number() over (PARTITION BY CId ORDER BY score DESC) AS 排名
FROM sc 
?-----16、查询学生的总成绩并进行排名，总成绩重复时保留名额空缺
SELECT SId,SUM(score) sum_score,rank() over (ORDER BY SUM(score)) AS Rank_
FROM sc
GROUP BY SId;
-----17、查询各科成绩前三名的记录
思路：各科成绩分类进行排序列一个表，然后从这个表里取前三名的出来。（）表要起别名
SELECT *
FROM
(SELECT *,dense_rank() over(PARTITION BY CId ORDER BY score) AS cid_rank
FROM sc) a
WHERE cid_rank<=3
?-----18、统计各科成绩各分数段人数：课程编号、课程名称、[0-60](60-70](70-85](85-100]所占百分比
思路：先拿出各个科目总人数（count（） group by），然后进行case when分类，然后再取百分数
SELECT CId,
CONCAT(SUM(CASE WHEN score<=60 THEN 1 ELSE 0 END)/COUNT(SId)*100,'%') AS '[0-60]所占百分比',
CONCAT(SUM(CASE WHEN score>60 AND score<=70 THEN 1 ELSE 0 END)/COUNT(SId),'%') AS '(60-70]所占百分比',
CONCAT(SUM(CASE WHEN score>70 AND score<=85 THEN 1 ELSE 0 END)/COUNT(SId),'%') AS '(70-85]所占百分比',
CONCAT(SUM(CASE WHEN score>85 THEN 1 ELSE 0 END)/COUNT(SId),'%') AS '(85-100]所占百分比'
FROM sc
GROUP BY CId
-----19、查询每门课程被选修的学生数
?
SELECT CId,COUNT(CId) AS '选修的学生数'
FROM sc
GROUP BY CId
?-----20、查询只选修两门课程的学生学号和姓名
SELECT s.Sname,sc.SId
FROM student s
INNER JOIN sc
ON s.SId=sc.SId
GROUP BY SId
HAVING COUNT(CId)=2
?-----21、查询男生、女生人数
SELECT Ssex,COUNT(*)
FROM student
GROUP BY Ssex
?-----22、查询名字中含有"风"字的学生信息
SELECT *
FROM student
WHERE Sname LIKE "%风%"
?-----23、查询同名同性学生名单，并统计同名同姓人数
思路：重复一个表，过滤ID不同但名字相同的人
select * ,count(*)
from student a
inner join student b
on a.SId!=b.SId
and a.Sname=b.Sname
and a.Ssex=b.Ssex
-----24、查询1990年出生的学生信息
select *
from student
where year(Sage)=1990
?-----25、查询每门课程平均成绩，并降序排列，平均成绩相同，则按课程编号升序排列
select CId,avg(score)
from sc
group by CId
order by AVG(score) desc
?----26、查询平均成绩大于等于85分学生的学号、姓名、平均成绩
select s.SId,s.Sname,avg(sc.`score`)
from student s
left join sc
on s.SId=sc.SId
group by SId
having AVG(sc.`score`)>85
?----27、查询课程为数学，且分数低于60的学生姓名和分数
select s.`Sname`,sc.`score`
from student s
left join sc
on s.`SId`=sc.`SId`
left join course c
on sc.`CId`=c.`CId`
where sc.`score`<60 and c.`Cname` like "数学"
----28、查询所有学生的课程及分数情况（存在学生没成绩、没选课情况）
SELECT s.`Sname`,c.`Cname`,sc.`score`
FROM student s
LEFT JOIN sc
ON s.`SId`=sc.`SId`
LEFT JOIN course c
ON sc.`CId`=c.`CId`?
?----29、查询任意一门课程成绩在70分以上的学生姓名、所选课程名称和分数
select s.`Sname`,c.`Cname`,sc.`score`
from student s
left join sc
on s.`SId`=sc.`SId`
left join course c
on sc.`CId`=c.`CId`
where score>70
?----30、查询存在不及格的课程
select c.`Cname`
from sc
left join course c
on sc.`CId`=c.`CId`
where score<60
group by c.`Cname`
?----31、查询课程编号为01 且成绩在80分以上的学生学号和姓名
SELECT s.`SId`,s.`Sname`,sc.`CId`,sc.`score`
FROM student s
LEFT JOIN sc
ON s.`SId`=sc.`SId`
WHERE sc.`score`>=80 AND sc.`CId`=1
?----32、求每门课程的学生人数
select CId,count(*)
from sc
group by CId;----33、假设成绩不重复，查询选修"张三"老师课程的学生中，成绩最高学生信息及成绩
?
select s.*,sc.`score`
from student s
left join sc
on s.`SId`=sc.`SId`
left join course c
on c.`CId`=sc.`CId`
left join teacher t
on t.`TId`=c.`TId`
where t.`Tname`="张三"
having max(sc.`score`)
?----34、假设成绩重复，查询选修"张三"老师课程的学生中，成绩最高学生信息及成绩
思路：成绩重复要考虑同样成绩的学生，需要进行排序表整理
SELECT a.SId,a.Sname,a.Sage,a.Ssex,a.score
FROM
(SELECT s.*,sc.`score`,rank() over(ORDER BY sc.`score`) AS rank_
FROM student s
LEFT JOIN sc
ON s.`SId`=sc.`SId`
LEFT JOIN course c
ON c.`CId`=sc.`CId`
LEFT JOIN teacher t
ON t.`TId`=c.`TId`
WHERE t.`Tname`="张三") AS a
WHERE rank_<=1
---35、查询不同课程成绩相同学生的学生编号、课程编号、学生成绩
SELECT a.*
FROM
(SELECT sc.`SId`,sc.`CId`,sc.`score`
FROM sc) AS a
LEFT JOIN sc
ON sc.`SId`=a.SId
WHERE sc.`score`=a.score AND sc.`CId`!=a.CId
GROUP BY sc.`CId`
?---36、查询每门课程成绩最好的前两名
SELECT a.SId,a.CId,s.`Sname`
FROM
(SELECT row_number() over(PARTITION BY sc.`CId` ORDER BY sc.`score` DESC) AS rank_,sc.*
FROM sc) AS a
LEFT JOIN student s
ON s.`SId`=a.SId
WHERE rank_<=2
?---37、统计每门课程的学生选修人数（超过5人的课程才统计）
SELECT COUNT(sc.`SId`) AS 选修人数
FROM sc
GROUP BY sc.`CId`
HAVING COUNT(sc.`SId`)>=5?
?---38、检索至少选修两门课程的学生学号
select SId
from sc
group by SId
having count(*)>=2;---39、查询选修全部课程的学生信息
SELECT s.*
FROM student s
LEFT JOIN sc
ON s.`SId`=sc.`SId`
GROUP BY s.`SId`
HAVING COUNT(sc.`CId`)=3
?---40、查询各学生的年龄，只按年份来算
SELECT s.`Sname`,(YEAR(NOW())-YEAR(s.`Sage`)) AS 年龄
FROM student s
?---41、按照出生日期来算（当前月日<出生年月的月日则年龄减一）
SELECT s.SId,s.Sname,
TIMESTAMPDIFF(YEAR,s.Sage,NOW()) AS 年龄
FROM student s 
?---42、查询本周过生日的同学信息
SELECT s.SId,s.Sname,s.Sage
FROM student s
WHERE WEEK(NOW())=WEEK(s.`Sage`)
?---43、查询下周过生日的学生信息
SELECT s.SId,s.Sname,s.Sage
FROM student s
WHERE WEEK(NOW())-WEEK(s.`Sage`)=-1---44、查询本月过生日的同学信息
SELECT s.*
FROM student s
WHERE MONTH(s.`Sage`)=MONTH(NOW())
?45、查询下月过生日的学生信息
SELECT s.*
FROM student s
WHERE MONTH(s.`Sage`)-MONTH(NOW())=1