用mysql计算85和32的和_SQL50题：答案与详细分析

网上流传较广的50道SQL经典练习题，提供一些个人的解法，欢迎其他小伙伴进行指正和代码优化~

数据表

1.学生表Student(SId,Sname,Sage,Ssex)

--SId 学生编号, Sname 学生姓名, Sage 出生年月, Ssex 学生性别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' , '女');

2.课程表Course(CId,Cname,TId)

--CId 课程编号, Cname 课程名称, TId 教师编号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')

3.教师表Teacher(TId,Tname)

--TId 教师编号, Tname 教师姓名create table Teacher(TId varchar(10),Tname varchar(10))

insert into Teacher values('01' , '张三')

insert into Teacher values('02' , '李四')

insert into Teacher values('03' , '王五')

4.成绩表SC(SId,CId,score)

--SId 学生编号, CId 课程编号, score 分数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)

练习题目及答案(无答案版在最下方)查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

-- 方法一：SQL99 子查询SELECT

s.*, sc_cid1.score AS score1,sc_cid2.score AS score2

FROM

(SELECT * FROM sc WHERE cid = 1) AS sc_cid1 -- 课程为01的分数和学生编号JOIN

(SELECT * FROM sc WHERE cid = 2) AS sc_cid2 -- 课程为02的分数和学生编号ON

sc_cid1.sid = sc_cid2.sid -- 两表相连JOIN

Student s -- 连接学生表ON s.sid = sc_cid1.sid

WHERE

sc_cid1.score > sc_cid2.score; -- 根据题目设置条件

-- 方法二：SQL92 自连接SELECT

s.*,sc1.score,sc2.score

FROM

sc AS sc1,sc AS sc2,student AS s -- 三表相连WHERE sc1.SId = sc2.SId

AND sc1.CId = '01' -- 成绩表1是课程编号为01的成绩 AND sc2.CId ='02' -- 成绩表1是课程编号为01的成绩 AND sc1.score > sc2.score -- 根据题目设置条件 AND s.SId = sc1.SId

-- 方法三：长型数据转化为宽型数据(较难)/* 思路：利用case when原表为长型数据 ➠ 宽型数据学生编号 课程编号 分数 学生编号 01课程的成绩 02课程的成绩 03课程的成绩01 01 80 01 80 90 9901 02 9001 03 99……*/

SELECT

s.*,t.sc1,t.sc2

FROM

(SELECT

sc.SId,

MAX(CASE WHEN sc.CId= '01' THEN sc.score END) sc1, -- 课程为01的成绩MAX(CASE WHEN sc.CId= '02' THEN sc.score END) sc2 -- 课程为02的成绩FROM sc

GROUP BY sc.SId) t,

student s

WHERE t.sc1 > t.sc2

AND s.sid = t.sid;

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

-- 方法一：sql99 成绩表自连接SELECT *

FROM

(SELECT * FROM sc WHERE sc.CId = '01') sc1, -- 学习课程1的学生信息(SELECT * FROM sc WHERE sc.CId = '02') sc2 -- 学习课程2的学生信息WHERE sc1.sid = sc2.sid

-- 方法二：多层子查询SELECT *

FROM sc

WHERE sc.sid IN

(

SELECT sid

FROM sc

WHERE sc.sid IN (SELECT sid FROM sc WHERE sc.CId = '01') -- 学习课程1的学生编号AND cid = '02'

); -- 学习课程1和2的学生编号

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

-- sql99 左连接 课程01为主表SELECT *

FROM

(SELECT * FROM sc WHERE sc.CId = '01') sc1 -- 学习课程1的学生信息LEFT JOIN

(SELECT * FROM sc WHERE sc.CId = '02') sc2 -- 学习课程2的学生信息ON sc1.sid = sc2.sid

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

-- 方法一： 子查询SELECT *

FROM sc

WHERE sid NOT IN (

SELECT sid FROM sc WHERE sc.CId = '01' -- 学习课程1的学生信息)

AND cid = '02'

-- 方法二： 右连接，课程2为主表SELECT sc2.*

FROM

(SELECT * FROM sc WHERE cid = 01) sc1

RIGHT JOIN

(SELECT * FROM sc WHERE cid = 02) sc2

ON sc1.Sid = sc2.Sid

WHERE sc1.sid IS NULL; -- 条件设置课程1的学生信息为空，可显示出题目所要求情况

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

-- 方法一思路：先查出平均成绩大于60分的sid，再连接学生表SELECT s.sid,s.sname,t.avg_sc

FROM

(

SELECT AVG(score) avg_sc,sid

FROM sc

GROUP BY sid

HAVING avg_sc >= 60

) t,

student s

WHERE t.sid = s.sid;

-- 方法二思路：先连接学生表，再求平均成绩大于60分的学生SELECT

sc.sid,

(SELECT DISTINCT s.sname FROM student s WHERE s.sid = sc.sid) s_name, -- 先连接成绩表AVG(score) avg_s

FROM sc

GROUP BY sc.SId

HAVING avg_s >= 60;

-- 方法三思路：成绩表和学生表先连接，再求平均成绩大于60分的学生SELECT s.sid,s.Sname,AVG(sc.score) avg_s

FROM sc,student s

WHERE sc.sid = s.sid

GROUP BY sid

HAVING avg_s >= 60;

2.1 查询所有课程成绩小于60分学生的学号、姓名

SELECT s.sid,s.sname

FROM student s

WHERE s.sid IN

(SELECT sid

FROM sc

GROUP BY sid

HAVING MAX(score) < 60);

-- 所有成绩都小于60，最大成绩必然小于60

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

-- 方法一思路：先求出存在成绩表中sid，再连接学生表SELECT *

FROM student s

WHERE s.sid IN

(

SELECT DISTINCT sid

FROM sc -- 存在成绩表中sid);

-- 方法二思路： sql92 连接查询SELECT DISTINCT student.*

FROM student,sc

WHERE student.SId=sc.SId;

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

-- 方法一思路：要求没成绩的显示为null，需要外连接，学生表为主表SELECT s.sid,s.sname,COUNT(cid),SUM(score)

FROM sc

RIGHT JOIN student s -- 右连接学生表(主表)ON sc.sid = s.sid

GROUP BY sid

-- 方法二：SELECT

s.sid,

s.sname,

a.count_cid,

a.sum_score

FROM Student s

LEFT JOIN -- 再左连接上学生表，连接条件sid，显示包括null值(SELECT sid,COUNT(cid)AS count_cid,SUM(score) AS sum_score

FROM sc -- 用聚合函数求出题目条件GROUP BY sid) AS a

ON s.`SId`=a.`SId`;

4.1 查有成绩的学生信息

-- 方法一思路：先求出有成绩的学生id，再连接学生表SELECT *

FROM student

WHERE sid IN

(

SELECT DISTINCT sid

FROM sc

);

-- 方法二：SELECT DISTINCT s.*

FROM student s,sc

WHERE s.sid = sc.sid

-- 方法三：existsSELECT *

FROM student

WHERE EXISTS (

SELECT *

FROM sc

WHERE sc.sid = student.sid

);

5. 查询「李」姓老师的数量

SELECT COUNT(*)

FROM teacher

WHERE tname LIKE '李%'

6. 查询学过「张三」老师授课的同学的信息

SELECT s.*

FROM student s,teacher t,sc,course c

WHERE s.sid = sc.sid

AND sc.cid = c.cid

AND c.tid = t.tid

AND t.tname = '张三'

7. 查询没有学全所有课程的同学的信息

/*1.先统计课程总数2.在成绩表已sid分组，查询各sid所学的课程数3.连接学生表*/

-- 方法一：SELECT * FROM student

LEFT JOIN sc -- 学生表连接成绩表，按sid分组ON student.SId=sc.SId

GROUP BY student.SId

HAVING COUNT(sc.score)

SELECT COUNT(cid) FROM course -- 统计课程总数);

-- 方法二：SELECT * FROM student

WHERE

student.sid NOT IN (

SELECT sc.sid FROM sc

GROUP BY sc.sid

HAVING COUNT(sc.cid)=(SELECT COUNT(cid) FROM course) -- 统计学过全部课程的学生id);

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

/*1.查询sid=01所学的cid2.成绩表连接学生表，cid in (条件1)*/

-- 方法一：SELECT DISTINCT s.*

FROM sc,student s

WHERE sc.sid = s.sid

AND cid IN (

SELECT cid

FROM sc

WHERE sid = '01' -- 学号为1所学的课程)

AND s.Sid <> '01'

-- 方法二：#------------------------------------------SELECT s.*

FROM student s

JOIN (

SELECT DISTINCT sid

FROM sc

WHERE cid =ANY(SELECT cid FROM sc WHERE sid = 1 ) -- sc表中符合条件1的sid) t

ON s.sid = t.sid

AND s.sid <> '01'

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

SELECT s.*

FROM sc,student s

WHERE sc.sid = s.sid

AND cid IN (

SELECT cid

FROM sc

WHERE sid = '01' -- 01同学学习了哪几门课)

GROUP BY sid -- 对学号进行分组HAVING COUNT(1) = (SELECT COUNT(1) FROM sc WHERE sid = '01') -- 01同学学习了几门课AND sid <> '01';

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

/* 思路：1.先求出学过张三老师课程的sid2.再用学生表not in 条件1*/

SELECT s.sname,s.sid -- 为了方便查看求出sidFROM student s

WHERE s.sid NOT IN

(

SELECT sid

FROM sc,course c,teacher t

WHERE sc.cid = c.cid

AND c.tid = t.tid

AND t.tname = '张三'

) -- 学过张三课程的学生sid

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

-- 方法一：SELECT sc.sid,s.sname,AVG(sc.score)

FROM sc,student s

WHERE sc.sid = s.sid

AND sc.sid IN

(

SELECT sid

FROM sc

WHERE score < 60

GROUP BY sid

HAVING COUNT(score) >= 2

)

GROUP BY s.sid;

-- 方法二：SELECT

s.`SId`,

s.`Sname`,

AVG(sc.`score`)

FROM student s

LEFT JOIN sc sc

ON s.`SId` = sc.`SId`

GROUP BY s.`SId`

HAVING SUM(CASE WHEN sc.`score`>=60 THEN 0 ELSE 1 END) >=2;

12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

-- 方法一： 子查询SELECT s.*,t.score

FROM student s,

(

SELECT sid,score

FROM sc

WHERE sc.cid = '01'

AND sc.score < 60

) t

WHERE s.sid = t.sid

ORDER BY t.score DESC

-- 方法二：连接查询SELECT s.*,sc.CId,sc.score

FROM sc,student s

WHERE sc.CId = '01'

AND sc.score < 60

AND sc.SId=s.SId

ORDER BY sc.score DESC

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

-- 方法一：mysql 8.0以上版本 加入了 开窗函数SELECT

sc.*,

AVG(score) over (PARTITION BY sc.sid) avg_sc

FROM sc

ORDER BY avg_sc DESC,sc.score DESC

-- 方法二：SELECT sc.*,avg_sc.t

FROM sc

JOIN (

SELECT sc.`SId`,ROUND(AVG(score),2) t

FROM sc

GROUP BY sc.`SId` -- 求各sid的平均成绩) avg_sc

WHERE sc.`SId`=avg_sc.`SId`

ORDER BY t DESC;

14. 查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT

sc.cid '课程 ID',

c.cname '课程 NAME',

MAX(sc.score) '最高分',

MIN(sc.score) '最低分',

AVG(sc.score) '平均分',

COUNT(*) '选修人数',

CONCAT (ROUND(SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') '及格率',

CONCAT (ROUND(SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') '中等率',

CONCAT (ROUND(SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') '优良率',

CONCAT (ROUND(SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') '优秀率'

FROM sc,course c

WHERE sc.cid = c.cid

GROUP BY sc.cid

ORDER BY COUNT(*) DESC,sc.CId ASC;

14.1 输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT cid,COUNT(sid) AS 选修人数

FROM sc

GROUP BY cid

ORDER BY COUNT(sid) DESC;

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

-- 方法一：开窗函数 MySQL8.0以后的版本可以使用SELECT

sc.*,

rank() over(PARTITION BY cid ORDER BY score DESC ) `rank`

FROM sc

-- 方法二：子查询SELECT

a.*,

(SELECT COUNT(score)

FROM sc AS b

WHERE a.cid=b.cid

AND a.score < b.score) + 1 AS `rank`

FROM sc AS a

ORDER BY cid,score DESC;

-- 方法三：连接查询SELECT a.*,COUNT(b.score)+1 AS `rank`

FROM SC AS a

LEFT JOIN SC AS b

ON a.score < b.score

AND a.cid = b.cid

GROUP BY a.cid, a.sid, a.score

ORDER BY a.cid, `rank` ASC;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

SELECT sc.*,

row_number() over (PARTITION BY cid ORDER BY score DESC) 'rank'

FROM sc

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

#方法一：开窗函数 MySQL8.0以后的版本可以使用

SELECT

t.*,

rank() over(ORDER BY sum_s DESC) AS 排名

FROM

(SELECT sid,SUM(score) sum_s

FROM sc

GROUP BY sid ) t; -- 先求出总成绩，再用窗口函数排名#方法二：子查询

SELECT

a.*,

(SELECT COUNT(sum_s) FROM (SELECT sid,SUM(score) sum_s FROM sc GROUP BY sid) b

WHERE a.sum_s < b.sum_s)+1 rk

FROM

(SELECT sid,SUM(score) sum_s

FROM sc

GROUP BY sid ) a

ORDER BY sum_s DESC;

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

SELECT

t.*,

row_number() over(ORDER BY sum_s DESC) AS 排名

FROM

(SELECT sid,SUM(score) sum_s

FROM sc

GROUP BY sid ) t; -- 先求出总成绩，再用窗口函数排名

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT

sc.`CId` AS '课程编号',

course.`Cname` AS '课程名称',

SUM(CASE WHEN sc.`score`<=100 AND sc.`score`>85 THEN 1 ELSE 0 END) AS '[100-85]个数',

SUM(CASE WHEN sc.`score`<=100 AND sc.`score`>85 THEN 1 ELSE 0 END)*100/COUNT(score) 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`<=85 AND sc.`score`>70 THEN 1 ELSE 0 END)*100/COUNT(score) 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`<=70 AND sc.`score`>60 THEN 1 ELSE 0 END)*100/COUNT(score) AS '[70-60]占比',

SUM(CASE WHEN sc.`score`<=60 AND sc.`score`>0 THEN 1 ELSE 0 END)AS '[60-0]个数',

SUM(CASE WHEN sc.`score`<=60 AND sc.`score`>0 THEN 1 ELSE 0 END)*100/COUNT(score) AS '[60-0]占比'

FROM sc,course

WHERE sc.`CId` = course.`CId`

GROUP BY sc.`CId`;

18. 查询各科成绩前三名的记录

--方法一：窗口函数SELECT t.*

FROM

(SELECT sc.*,c.`Cname`,rank() over (PARTITION BY c.cid ORDER BY sc.score DESC) rk

FROM course c JOIN sc

ON c.`CId` = sc.`CId`) t

WHERE rk IN (1,2,3);

--方法二：自连接SELECT

a.Cid, a.Sid, a.score, COUNT(b.score) + 1 rk

FROM

(SELECT * FROM SC GROUP BY Cid, Sid, score) a

LEFT JOIN

(SELECT * FROM SC GROUP BY Cid, Sid, score) b

ON a.Cid = b.Cid AND a.score < b.score

GROUP BY a.Cid, a.Sid, a.score

HAVING COUNT(b.score) < 3

ORDER BY a.Cid ASC, a.score DESC;

19. 查询每门课程被选修的学生数

SELECT COUNT(sid),cid

FROM sc

GROUP BY cid

20. 查询出只选修两门课程的学生学号和姓名

-- 方法一：SELECT

s.sid,s.sname,sca.c_sc

FROM

Student s JOIN (

SELECT COUNT(cid) c_sc,sid

FROM sc

GROUP BY sid

HAVING COUNT(cid) = 2 -- 先在成绩表中，查出只选修两门课程的学号，再连接学生表)sca

ON s.sid = sca.`SId`;

-- 方法二：SELECT student.SId,student.Sname

FROM sc,student

WHERE student.SId=sc.SId

GROUP BY sc.SId

HAVING COUNT(*)=2;

21. 查询男生、女生人数

-- 方法一SELECT

SUM(CASE WHEN ssex = '男' THEN 1 ELSE 0 END) AS '男生',

SUM(CASE WHEN ssex = '女' THEN 1 ELSE 0 END) AS '女生'

FROM student

-- 方法二SELECT Ssex, COUNT(Sid) AS '学生人数' FROM Student GROUP BY Ssex;

22. 查询名字中含有「风」字的学生信息

SELECT *

FROM student

WHERE sname LIKE '%风%';

23. 查询同名同性学生名单，并统计同名人数

-- 方法一：SELECT sname,COUNT(*)

FROM student

GROUP BY sname

HAVING COUNT(*) > 1;

-- 方法二：SELECT Student.Sname,

Student.Sid,

a.stu_cnt AS '学生人数'

FROM Student,

(

SELECT Sname,

COUNT(Sid) AS stu_cnt

FROM Student

GROUP BY Sname

HAVING COUNT(Sid) > 1

) a

WHERE Student.Sname = a.Sname;

24. 查询 1990 年出生的学生名单

SELECT *

FROM student

WHERE YEAR(sage) = 1990;

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

-- 方法一：SELECT sc.cid, course.cname, AVG(SC.SCORE) AS average

FROM sc, course

WHERE sc.cid = course.cid

GROUP BY sc.cid

ORDER BY average DESC,cid ASC;

-- 方法二：SELECT Cid,AVG(score)

FROM SC

GROUP BY Cid

ORDER BY Cid ASC;

26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

-- 方法一：SELECT s.sid,

s.sname,

sca.avs

FROM student s -- 2.连接学生表JOIN (

SELECT AVG(score) avs,sid

FROM sc

GROUP BY sid

HAVING AVG(score) >=85) sca -- 1.平均成绩大于等于 85 的所有学生的学号ON s.sid = sca.sid

-- 方法二：SELECT student.sid,

student.sname,

AVG(sc.score) AS ave

FROM student, sc

WHERE student.sid = sc.sid

GROUP BY sc.sid

HAVING aver > 85;

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

SELECT s.sname,

sc.`score`

FROM student s,sc,course c

WHERE s.sid = sc.`SId`

AND sc.`CId` = c.cid

AND c.cname = '数学'

AND sc.`score` < 60;

28. 查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)

SELECT s.*,

c.cname,

sc.`score`

FROM student s

LEFT JOIN sc

ON s.sid = sc.`SId`

JOIN course c

ON sc.cid = c.cid

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT s.sname,

c.cname,

sc.`score`

FROM student s,course c,sc

WHERE s.sid = sc.`SId`

AND c.cid = sc.`CId`

AND sc.`score` > 70;

30. 查询不及格的课程

SELECT DISTINCT c.cid,c.cname,

sc.sid,

s.sname,

sc.`score`

FROM sc,course c,student s

WHERE sc.score < 60

AND sc.`CId` = c.cid

AND s.sid = sc.`SId`;

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT student.sid,

student.sname

FROM student,sc

WHERE cid= 01

AND score>=80

AND student.sid = sc.sid;

32. 求每门课程的学生人数

SELECT COUNT(*),cid

FROM sc

GROUP BY cid

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 方法一：SELECT s.*,

sc.`score`

FROM teacher t,course c,sc,student s

WHERE t.tid = c.tid

AND c.cid = sc.`CId`

AND sc.`SId` = s.sid

AND t.tname = '张三'

ORDER BY sc.`score` DESC

LIMIT 1

-- 方法二：SELECT student.*,

sc.score,

sc.cid

FROM student, teacher, course,sc

WHERE teacher.tid = course.tid

AND sc.sid = student.sid

AND sc.cid = course.cid

AND teacher.tname = "张三"

HAVING MAX(sc.score); -- 直接可以having 条件跟聚合函数

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT Student.*,

SC.score

FROM Student, SC

WHERE Student.Sid = SC.Sid

AND score = (

SELECT SC.score

FROM SC, Course, Teacher

WHERE SC.Cid = Course.Cid

AND Course.Tid = Teacher.Tid

AND Teacher.Tname = '张三'

ORDER BY SC.score DESC

LIMIT 1); -- 选修「张三」老师所授课程的学生中，成绩最高的分数

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT Sid,

Cid,

score

FROM SC WHERE score IN (

SELECT score

FROM SC

GROUP BY score -- 在sc表中 按成绩分组求个数，having设置个数大于1的，即为重复HAVING COUNT(DISTINCT Cid) > 1);

36. 查询每门功成绩最好的前两名

-- 方法一：窗口函数SELECT t.*

FROM (

SELECT

a.`CId`,a.`score`,

rank() over (PARTITION BY a.cid ORDER BY a.score DESC) rk

FROM sc a) t

WHERE rk IN(1,2);

-- 方法二：SELECT a.sid,

a.cid,

a.score

FROM sc AS a

LEFT JOIN sc AS b

ON a.cid = b.cid

AND a.score

GROUP BY a.cid, a.sid

HAVING COUNT(b.cid)<2

ORDER BY a.cid;

37. 统计每门课程的学生选修人数(超过 5 人的课程才统计)

SELECT COUNT(`SId`),cid

FROM sc

GROUP BY cid

HAVING COUNT(sid) > 5;

38. 检索至少选修两门课程的学生学号

SELECT sid

FROM sc

GROUP BY sid

HAVING COUNT(cid) >= 2;

39. 查询选修了全部课程的学生信息

SELECT s.*

FROM student s

JOIN sc

ON s.sid = sc.`SId`

GROUP BY s.sid

HAVING COUNT(cid) = (SELECT DISTINCT COUNT(*) FROM course);

40. 查询各学生的年龄，只按年份来算

-- 方法一：SELECT Sid,sname,YEAR(NOW())-YEAR(sage) '年龄' FROM student;

-- 方法二：SELECT Sid, Sname, (YEAR(CURDATE()) - YEAR(Sage)) AS age FROM Student;

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

SELECT student.SId AS 学生编号,

student.Sname AS 学生姓名,

TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) AS 学生年龄

FROM student;

42. 查询本周过生日的学生

SELECT *

FROM Student

WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(CURDATE());

43. 查询下周过生日的学生

SELECT *

FROM student

WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

44. 查询本月过生日的学生

SELECT *

FROM Student

WHERE MONTH(Sage) = MONTH(CURDATE());

45. 查询下月过生日的学生

SELECT *

FROM Student

WHERE MONTH(Sage) = MONTH(CURDATE()) + 1;

无答案版

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

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

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

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

4.1 查有成绩的学生信息

5.查询「李」姓老师的数量

6.查询学过「张三」老师授课的同学的信息

7.查询没有学全所有课程的同学的信息

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

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

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

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

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

14.查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

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

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

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

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

17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

18.查询各科成绩前三名的记录

19.查询每门课程被选修的学生数

20.查询出只选修两门课程的学生学号和姓名

21.查询男生、女生人数

22.查询名字中含有「风」字的学生信息

23.查询同名同性学生名单，并统计同名人数

24.查询 1990 年出生的学生名单

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

26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

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

28.查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)

29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

30.查询不及格的课程

31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

32.求每门课程的学生人数

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

36.查询每门功成绩最好的前两名

37.统计每门课程的学生选修人数(超过 5 人的课程才统计)。

38.检索至少选修两门课程的学生学号

39.查询选修了全部课程的学生信息

40.查询各学生的年龄，只按年份来算

41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

42.查询本周过生日的学生

43.查询下周过生日的学生

44.查询本月过生日的学生

45.查询下月过生日的学生