MySQL实战题目 mysql50道题

一、准备工作

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-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-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);

二、题目

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

#2、查询同时存在” 01 “课程和” 02 “课程的情况

#3、查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

#4、查询不存在” 01 “课程但存在” 02 "课程的情况

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

#6、查询在 SC 表存在成绩的学生信息

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

#8、查有成绩的学生信息

#9、查询「李」姓老师的数量

#10、查询学过「张三」老师授课的同学的信息

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

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

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

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

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

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

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

#18、查询各科成绩最高分、最低分和平均分

#19、以如下形式显示：课程 ID，课程 name，及格率，中等率，优良率，优秀率;及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

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

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

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

#23、查询学生的总成绩，并进行排名

#24、查询每个学生的总成绩，平均分以及最高分

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

#26、查询各科成绩前三名的记录

#27、查询每门课程被选修的学生数

#28、查询出只选修两门课程的学生学号和姓名

#29、查询男生、女生人数

#30、查询名字中含有「风」字的学生信息

#31、查询同名同性学生名单，并统计同名人数

#32、查询 1990 年出生的学生名单

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

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

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

#36、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

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

#38、查询不及格的课程

#39、查询课程编号为 01 且课程成绩在 80 分以上(包括80分)的学生的学号和姓名

#40、求每门课程的学生人数

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

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

#43、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

#44、统计每门课程的学生选修人数（超过 5 人的课程才统计）

#45、检索至少选修两门课程的学生学号

#46、查询选修了全部课程的学生信息

#47、查询本周过生日的学生

#48、查询下周过生日的学生

#49、查询本月过生日的学生

#50、查询下月过生日的学生

三、答案

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

SELECT t1.sid, t1.score AS class1,t2.score AS class2 FROM
(SELECT sid,cid,score FROM sc WHERE cid = '01') t1,
(SELECT sid,cid,score FROM sc WHERE cid = '02') t2
WHERE t1.sid = t2.sid AND t1.score > t2.score

#2、查询同时存在” 01 “课程和” 02 “课程的情况

SELECT sc.SId,st.Sname FROM sc
INNER JOIN student st
ON sc.SId = st.SId
WHERE sc.CId = '01' 
AND sc.SId IN(SELECT SId FROM sc WHERE CId = '02')

#3、查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

SELECT t1.SId,t1.CId,t1.score,t2.SId,t2.CId,t2.score 
FROM  (SELECT SId,CId,score FROM sc WHERE sc.cid = '01') t1
LEFT JOIN (SELECT SId,CId,score FROM sc WHERE sc.cid = '02') t2
ON t1.sid = t2.sid

#4、查询不存在” 01 “课程但存在” 02 "课程的情况

SELECT sc.SId,st.Sname FROM sc
INNER JOIN student st
ON sc.SId = st.SId
WHERE sc.CId = '02' 
AND sc.SId NOT IN(SELECT SId FROM sc WHERE CId = '01')

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

SELECT s.SId AS student_SId,s.Sname,AVG(sc.score)
FROM student s
INNER JOIN sc 
ON s.SId = sc.SId
WHERE sc.score >= 60
GROUP BY student_SId

#6、查询在 SC 表存在成绩的学生信息

SELECT SId,Sname,Sage,Ssex FROM student WHERE SId IN(
SELECT SId FROM sc GROUP BY(SId))

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

SELECT st.SId,st.Sname,SUM(sc.CId),SUM(sc.score) FROM student st
LEFT JOIN sc
ON st.SId = sc.SId
GROUP BY(st.SId)

#8、查有成绩的学生信息

SELECT SId,Sname,Sage,Ssex FROM student 
WHERE SId IN(SELECT SId FROM sc GROUP BY(SId))

#9、查询「李」姓老师的数量

SELECT count(*) FROM teacher WHERE Tname LIKE '李%'

#10、查询学过「张三」老师授课的同学的信息

SELECT SId,Sname,Sage,Ssex FROM student WHERE SId IN(
SELECT SId FROM sc WHERE CId IN (
SELECT CId FROM course WHERE TId IN (
SELECT TId FROM teacher WHERE Tname = '张三')))

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

SELECT SId,Sname,Sage,Ssex FROM student WHERE SId NOT IN
(SELECT SId FROM sc 
GROUP BY SId
HAVING COUNT(CId) = (SELECT COUNT(Cname) FROM course))

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

SELECT SId,Sname,Sage,Ssex FROM student
WHERE SId IN (
SELECT SId FROM sc WHERE CId IN(
SELECT CId FROM sc WHERE SId = '01')
GROUP BY SId)

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

SELECT st.SId,st.Sname,st.Sage,st.Ssex 
FROM sc
INNER JOIN student st
ON sc.SId = st.SId 
WHERE sc.CId IN (SELECT cid FROM sc WHERE SId = '01')
AND sc.SId != '01'
GROUP BY sc.SId
HAVING COUNT(sc.SId) = (Select count(cid) from sc where sid = '01')

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

SELECT st.SId,st.sname FROM student st
WHERE st.sname NOT IN 
(SELECT st.sname FROM student st
INNER JOIN sc 
ON st.sid = sc.sid
INNER JOIN course c
ON sc.cid = c.cid
INNER JOIN teacher t
ON c.tid = t.tid
WHERE t.tname = '张三')

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

SELECT st.SId,st.Sname,AVG(sc.score) FROM student st
INNER JOIN sc
ON st.SId = sc.SId
WHERE sc.score < 60
GROUP BY sc.SId
HAVING COUNT(sc.CId) >= 2

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

SELECT s.SId,s.Sname,s.Sage,s.Ssex 
FROM student s 
INNER JOIN sc
ON s.SId = sc.SId 
AND sc.CId = '01' 
AND sc.score < 60
ORDER BY sc.score

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

SELECT sc.sid,t1.avg_score,sc.cid,sc.score FROM sc
LEFT JOIN (SELECT sid,avg(score) AS avg_score,cid,score FROM sc GROUP BY sid) t1
ON sc.sid = t1.sid
GROUP BY t1.avg_score DESC

#18、查询各科成绩最高分、最低分和平均分

SELECT c.Cname,MAX(sc.score),MIN(sc.score),AVG(sc.score) 
FROM course c
INNER JOIN sc
ON c.CId = sc.CId
GROUP BY(sc.CId)

#19、以如下形式显示：课程 ID，课程 name，及格率，中等率，优良率，优秀率;及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT sc.cid,c.cname,
sum(case when sc.score>=60 then 1 else 0 end)/count(sc.score) AS '及格',
sum(case when sc.score between 70 and 80 then 1 else 0 end)/count(sc.score) AS '中等',
sum(case when sc.score between 80 and 90 then 1 else 0 end)/count(sc.score) AS '优良',
sum(case when sc.score>=90 then 1 else 0 end)/count(sc.score) AS '优秀'
FROM sc
INNER JOIN course c
ON sc.cid = c.cid
GROUP BY sc.cid

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

SELECT a.cid, a.sid, a.score, 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;

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

SELECT *,
CASE WHEN @sco=score THEN @rank ELSE @rank:=@rank+1 END AS rn,
@sco:=score
FROM sc,(SELECT @rank:=0,@sco:=NULL) t
ORDER BY score DESC;

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

SELECT cid, count(sid) FROM sc
GROUP BY cid
ORDER BY count(sid) DESC,cid ASC

SELECT cid, count(sid) FROM sc
GROUP BY cid
ORDER BY 2 DESC,1 ASC

#23、查询学生的总成绩，并进行排名

SELECT sc.SId,st.Sname,SUM(score)
FROM sc
INNER JOIN student st
ON sc.SId = st.SId
GROUP BY SId
ORDER BY SUM(score) DESC

#24、查询每个学生的总成绩，平均分以及最高分

SELECT sc.SId,st.Sname,SUM(score),AVG(score),MAX(score) 
FROM sc
INNER JOIN student st
ON sc.SId = st.SId
GROUP BY SId

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

SELECT CId,
ROUND((SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(SId)),2) AS '[100-85]',
ROUND((sum(case when score between 70 and 85 then 1 else 0 end)/count(sid)),2) AS '[85-70]',
ROUND((sum(case when score between 60 and 70 then 1 else 0 end)/count(sid)),2) AS '[70-60]',
ROUND((sum(case when score between 0 and 60 then 1 else 0 end)/count(sid)),2) AS '[60-0]'
FROM sc 
GROUP BY CId

#26、查询各科成绩前三名的记录

SELECT CO.cname '课程',
(SELECT sc.SId
   FROM sc
  WHERE sc.CId = co.CId
  ORDER BY sc.score Desc LIMIT 0, 1) '第一名',
(SELECT sc.SId
   FROM sc
  WHERE sc.CId = co.CId
  ORDER BY sc.score Desc LIMIT 1, 1) '第二名',
	(SELECT sc.SId
   FROM sc
  WHERE sc.CId = co.CId
  ORDER BY sc.score Desc LIMIT 2, 1) '第三名'
FROM course co;

#27、查询每门课程被选修的学生数

SELECT sc.CId,COUNT(sc.SId) AS '被选修的学生数' FROM sc GROUP BY(sc.CId)

#28、查询出只选修两门课程的学生学号和姓名

SELECT sc.SId,st.Sname,COUNT(sc.CId) AS cidCount,sc.score 
FROM sc 
INNER JOIN student st
ON sc.SId = st.SId
GROUP BY SId
HAVING cidCount = 2

#29、查询男生、女生人数

SELECT COUNT(*) AS '男生、女生人数' FROM student GROUP BY(Ssex)

SELECT count(DISTINCT t1.sid) 男,count(DISTINCT t2.sid) 女 FROM 
(SELECT sid FROM student WHERE ssex = '男') t1,
(SELECT sid FROM student WHERE ssex = '女') t2

#30、查询名字中含有「风」字的学生信息

SELECT SId,Sname,Sage,Ssex FROM student WHERE Sname LIKE CONCAT('%','风','%')

#31、查询同名同性学生名单，并统计同名人数

SELECT sname, count(*) FROM student
GROUP BY sname
HAVING count(*)>1;

SELECT SId,Sname,Sage,Ssex FROM
(SELECT t1.SId,t1.Sname,t1.Sage,t1.Ssex 
FROM student t1
INNER JOIN student t2
ON t1.sname = t2.sname 
WHERE t1.sid != t2.sid 
GROUP BY t1.sid) a

#32、查询 1990 年出生的学生名单

SELECT SId,Sname,YEAR(Sage) AS SageYear,Ssex FROM student WHERE YEAR(Sage) = '1990'

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

SELECT SId,CId,AVG(score) as score_avg FROM sc 
GROUP BY CId
ORDER BY score_avg DESC,CId ASC

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

SELECT sc.SId,s.Sname,AVG(sc.score) AS Avgscore FROM sc
INNER JOIN student s
ON sc.SId = s.SId
GROUP BY(sc.SId)
HAVING Avgscore >= 85

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

SELECT s.Sname,sc.score FROM sc
INNER JOIN course c
ON sc.CId = c.CId
INNER JOIN student s
ON s.SId = sc.SId
WHERE c.Cname = '数学'
AND sc.score < 60

#36、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT st.sid,st.sname,st.Sage,st.Ssex,sc.CId,sc.score 
FROM student st
LEFT JOIN sc
ON st.sid = sc.sid

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

SELECT sc.sid,s.Sname,c.Cname,sc.score FROM sc 
INNER JOIN student s
ON sc.SId = s.SId
INNER JOIN course c
ON sc.CId = c.CId
WHERE sc.score > 70

#38、查询不及格的课程

SELECT sc.sid,s.Sname,c.Cname,sc.score FROM sc 
INNER JOIN course c
ON sc.CId = c.CId
INNER JOIN student s
ON s.SId = sc.SId
WHERE score < 60

#39、查询课程编号为 01 且课程成绩在 80 分以上(包括80分)的学生的学号和姓名

SELECT s.SId,s.Sname,sc.score FROM sc 
INNER JOIN student s
ON sc.SId = s.SId
WHERE sc.CId = '01' AND sc.score >= 80

#40、求每门课程的学生人数

SELECT c.Cname,COUNT(sc.SId) AS '每门课程的学生人数' FROM sc 
INNER JOIN course c 
ON sc.CId = c.CId
GROUP BY(sc.CId)

SELECT sc.cid, COUNT(sc.sid) AS '每门课程的学生人数' FROM sc
GROUP BY sc.cid

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

SELECT st.SId,st.Sname,st.Sage,st.Ssex,max(sc.score) FROM sc
INNER JOIN course c
ON sc.CId = c.CId
INNER JOIN teacher t
ON c.TId = t.TId AND t.Tname = '张三'
INNER JOIN student st
ON sc.SId = st.SId

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

SELECT t1.SId,t1.Sname,t1.Sage,t1.Ssex, t1.score FROM
(SELECT st.SId,st.Sname,st.Sage,st.Ssex, sc.score FROM sc
INNER JOIN course c 
ON sc.cid = c.cid
INNER JOIN teacher t
ON c.tid = t.tid AND t.tname = '张三'
INNER JOIN student st
ON sc.sid = st.sid) t1,
(SELECT st.SId,st.Sname,st.Sage,st.Ssex,max(sc.score) AS max_score FROM sc
INNER JOIN course c
ON sc.CId = c.CId
INNER JOIN teacher t
ON c.TId = t.TId AND t.Tname = '张三'
INNER JOIN student st
ON sc.SId = st.SId) t2
WHERE t1.score = t2.max_score

#43、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT sc.* FROM sc,
(SELECT sid,cid,score FROM sc
GROUP BY sid
HAVING count(DISTINCT cid) > count(DISTINCT score)) t1
WHERE sc.sid = t1.sid

#44、统计每门课程的学生选修人数（超过 5 人的课程才统计）

SELECT sc.CId,c.Cname,COUNT(sc.SId) AS Scount FROM sc 
INNER JOIN course c 
ON sc.CId = c.CId
GROUP BY(sc.CId)
HAVING Scount > 5

#45、检索至少选修两门课程的学生学号

SELECT SId,COUNT(CId) Cnum from sc GROUP BY(SId)
HAVING Cnum >= 2

#46、查询选修了全部课程的学生信息

SELECT SId,Sname,Sage,Ssex FROM student WHERE SId IN(
SELECT SId FROM sc
GROUP BY(SId)
HAVING COUNT(sc.CId) = (SELECT COUNT(CId) AS Ccount FROM course))

#47、查询本周过生日的学生

SELECT SId,Sname,Sage,Ssex
FROM student 
WHERE WEEKOFYEAR(Sage)=WEEKOFYEAR(current_date())

#48、查询下周过生日的学生

SELECT SId,Sname,Sage,Ssex
FROM student 
WHERE WEEKOFYEAR(Sage)=WEEKOFYEAR(current_date())+1

#49、查询本月过生日的学生

SELECT SId,Sname,Sage,Ssex FROM student 
WHERE MONTH(Sage) = MONTH(current_date())

#50、查询下月过生日的学生

SELECT SId,Sname,Sage,Ssex FROM student 
WHERE MONTH(Sage) = MONTH(current_date())+1