如题,题目和数据来源 Mysql经典练习题50题,修改了部分题目的表述方式。使用的Mysql版本为MySql5.7,答案为自己做的仅供参考,如有不同意见欢迎一起交流
1.创建测试数据
- 学生表 Student
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' , '女');
- 科目表 Course
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');
- 教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
- 成绩表 SC
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);
2.题目
- 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
- 查询同时选修" 01 “课程和” 02 "课程的学生
- 查询选修" 01 “课程的学生的“01”课程、” 02 “课程的成绩(如未选修” 02 "课程则显示为null)
- 查询未选修" 01 “课程但选修” 02 "课程的学生的成绩
- 查询平均成绩>60分的学生
- 查询在 SC 表存在成绩的学生信息
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
- 查询有成绩的学生信息
- 查询李姓老师的数量
- 查询学过「张三」老师授课的同学的信息
- 查询没有学全所有课程的同学的信息(包括一门课都未选的同学)
- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
- 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
- 查询没学过"张三"老师讲授的任一门课程的学生姓名
- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
- 查询" 01 "课程分数小于60,按分数降序排列的学生信息
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 查询各科成绩最高分、最低分和平均分: 以如下形式显示:
– 课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
– 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
– 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 - 按各科成绩进行排序,并显示排名,Score 重复时保留名次空缺
- 按各科成绩进行排序,并显示排名, Score 重复时合并名次
- 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
- 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
- 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
- 查询各科成绩前三名的记录
- 查询每门课程被选修的学生数
- 查询出只选修两门课程的学生学号和姓名
- 查询男生、女生人数
- 查询名字中含有「风」字的学生信息
- 查询同名同性学生名单,并统计同名人数
- 查询 1990 年出生的学生名单
- 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
- 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
- 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
- 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
- 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
- 查询不及格的课程
- 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
- 求每门课程的学生人数
- 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
- 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 查询每门功课成绩最好的前两名
- 统计每门课程的学生选修人数(超过 5 人的课程才统计)
- 检索至少选修两门课程的学生学号
- 查询各学生的年龄,只按年份来算
- 查询各学生的年龄,按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
- 查询本周过生日的学生
- 查询下周过生日的学生
- 查询本月过生日的学生
- 查询下月过生日的学生
3.参考答案
-
查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT
student.*, score1,
score2
FROM
(
SELECT
SC.SId AS SId1,
SC.score AS score1
FROM
SC
WHERE
SC.CId = '01'
) AS t1
INNER JOIN (
SELECT
SC.SId AS SId2,
SC.score AS score2
FROM
SC
WHERE
SC.CId = '02'
) AS t2 ON SId1 = SId2
INNER JOIN student ON student.SId = SId1
WHERE
score1 > score2
-
查询同时选修" 01 "课程和" 02 "课程的学生
SELECT
SId,
Sname
FROM
(
SELECT
SC.SId AS SId1,
SC.score AS score1
FROM
SC
WHERE
SC.CId = '01'
) AS t1
INNER JOIN (
SELECT
SC.SId AS SId2,
SC.score AS score2
FROM
SC
WHERE
SC.CId = '02'
) AS t2 ON SId1 = SId2
INNER JOIN student ON student.SId = SId1
-
查询选修" 01 "课程的学生的“01”课程、" 02 "课程的成绩(如未选修" 02 "课程则显示为null)
SELECT
*
FROM
(
SELECT
SC.SId,
SC.score
FROM
SC
WHERE
SC.CId = '01'
) AS t1
LEFT JOIN (
SELECT
SC.SId,
SC.score
FROM
SC
WHERE
SC.CId = '02'
) AS t2 ON t1.SId = t2.SId
-
查询未选修" 01 "课程但选修" 02 "课程的学生的成绩
SELECT
*
FROM
sc
WHERE
SId NOT IN (
SELECT
SId
FROM
sc
WHERE
CId = '01'
)
AND CId = '02'
-
查询平均成绩>60分的学生
SELECT
t1.SId,
t1.sname,
t2.avgscore
FROM
student AS t1
INNER JOIN (
SELECT
sc.SId,
avg(sc.score) AS avgscore
FROM
sc
GROUP BY
sc.SId
HAVING
avgscore >= 60
) AS t2 ON t1.SId = t2.SId
-
查询在 SC 表存在成绩的学生信息
SELECT DISTINCT
student.*
FROM
student,
sc
WHERE
student.SId = sc.SId
-
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT
t1.SId,
t1.Sname,
course_number,
total_score
FROM
student AS t1
INNER JOIN (
SELECT
SId,
count(*) AS course_number,
SUM(score) AS total_score
FROM
sc
GROUP BY
SId
) AS t2 ON t1.SId = t2.SId
-
查询有成绩的学生信息
SELECT DISTINCT
student.*
FROM
student
INNER JOIN sc ON student.SId = sc.SId
-
查询李姓老师的数量
SELECT
count(*) AS num
FROM
teacher
WHERE
Tname LIKE concat('李', '%')
-
查询学过「张三」老师授课的同学的信息
SELECT DISTINCT
student.*
FROM
student,
sc
WHERE
student.SId = sc.SId
AND sc.CId IN (
SELECT
CId
FROM
course,
teacher
WHERE
course.TId = teacher.TId
AND Tname = '张三'
)
-
查询没有学全所有课程的同学的信息(包括一门课都未选的同学)
SELECT DISTINCT
t1.*
FROM
student AS t1
WHERE
t1.SId NOT IN (
SELECT
t1.SId
FROM
Student AS t1
INNER JOIN (
SELECT
sc.SId,
count(*) AS courseNum
FROM
sc
GROUP BY
sc.SId
HAVING
courseNum IN (SELECT count(*) FROM course)
) AS t2 ON t1.SId = t2.SId
)
-
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT DISTINCT
t1.*
FROM
student AS t1
INNER JOIN (
SELECT
SId
FROM
sc
WHERE
CId IN (
SELECT
CId
FROM
sc
WHERE
SId = '01'
)
) AS t2 ON t1.SId = t2.SId
WHERE
t1.SId <> '01'
-
查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
SELECT DISTINCT
t1.*
FROM
student AS t1
INNER JOIN (
SELECT
SId
FROM
sc
WHERE
CId IN (
SELECT
CId
FROM
sc
WHERE
SId = '01'
)
) AS t2 ON t1.SId = t2.SId
WHERE
t1.SId <> '01'
GROUP BY
t2.SId
HAVING
count(*) = (
SELECT
count(*)
FROM
sc
WHERE
sc.SId = '01'
)
-
查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT
SName
FROM
student
WHERE
SId NOT IN (
SELECT DISTINCT
SId
FROM
sc,
course,
teacher
WHERE
sc.CId = course.CId
AND course.TId = teacher.TId
AND teacher.Tname = '张三'
)
-
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT DISTINCT
t1.SId,
SName,
avgscore
FROM
student AS t1
INNER JOIN (
SELECT
SId,
AVG(score) AS avgscore
FROM
sc
WHERE
SId IN (
SELECT
SId
FROM
sc
WHERE
score < 60
GROUP BY
SId
HAVING
count(*) >= 2
)
GROUP BY
SId
) AS t2
WHERE
t1.SId = t2.SId
-
查询" 01 "课程分数小于60,按分数降序排列的学生信息
SELECT DISTINCT
t1.*, t2.score
FROM
student AS t1
INNER JOIN (
SELECT
SId,
score
FROM
SC
WHERE
CId = '01'
AND score < 60
) AS t2 ON t1.SId = t2.SId
ORDER BY
score DESC
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
sc.SId,
Sname,
Cname,
score,
avgscore
FROM
(student, sc, course)
LEFT JOIN (
SELECT
SId,
avg(score) AS avgscore
FROM
sc
GROUP BY
SId
) AS avgtable ON sc.SId = avgtable.SId
WHERE
student.SId = sc.SId
AND sc.CId = course.CId
ORDER BY
avgscore DESC
- 查询各科成绩最高分、最低分和平均分: 以如下形式显示:
– 课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
– 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
– 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT
sc.CId AS '课程ID',
CName AS '课程name',
max(sc.score) AS '最高分',
min(sc.score) AS '最低分',
avg(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
LEFT JOIN course ON sc.CId = course.CId
GROUP BY
sc.CId
ORDER BY
count(*) DESC,
sc.CId ASC
-
按各科成绩进行排序,并显示排名,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
ORDER BY
a.CId,
rank ASC
-
按各科成绩进行排序,并显示排名, Score 重复时合并名次
SELECT
a.CId,
a.SId,
a.score,
count(DISTINCT b.score) + 1 AS rank
FROM
sc AS a
LEFT JOIN sc AS b ON a.CId = b.CId
AND a.score < b.score
GROUP BY
a.CId,
a.SId
ORDER BY
a.CId,
rank ASC
-
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
SELECT
a.SId,
a.sumscore,
count(b.sumscore) + 1 AS rank
FROM
(
SELECT
SId,
sum(score) AS sumscore
FROM
sc
GROUP BY
SId
) AS a
LEFT JOIN (
SELECT
SId,
sum(score) AS sumscore
FROM
sc
GROUP BY
SId
) AS b ON a.sumscore < b.sumscore
GROUP BY
a.SId
ORDER BY
rank ASC
-
查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
SELECT
a.SId,
a.sumscore,
count(DISTINCT b.sumscore) + 1 AS rank
FROM
(
SELECT
SId,
sum(score) AS sumscore
FROM
sc
GROUP BY
SId
) AS a
LEFT JOIN (
SELECT
SId,
sum(score) AS sumscore
FROM
sc
GROUP BY
SId
) AS b ON a.sumscore < b.sumscore
GROUP BY
a.SId
ORDER BY
rank ASC
23.
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
SELECT
sc.CId,
CName,
sum(
CASE
WHEN sc.score >= 85 THEN
1
ELSE
0
END
) AS '100-85',
sum(
CASE
WHEN sc.score >= 70
AND sc.score < 85 THEN
1
ELSE
0
END
) AS '85-70',
sum(
CASE
WHEN sc.score >= 60
AND sc.score < 70 THEN
1
ELSE
0
END
) AS '70-60',
sum(
CASE
WHEN sc.score >= 0
AND sc.score < 60 THEN
1
ELSE
0
END
) AS '60-0',
sum(
CASE
WHEN sc.score >= 85 THEN
1
ELSE
0
END
) / count(*) AS '100-85%',
sum(
CASE
WHEN sc.score >= 70
AND sc.score < 85 THEN
1
ELSE
0
END
) / count(*) AS '85-70%',
sum(
CASE
WHEN sc.score >= 60
AND sc.score < 70 THEN
1
ELSE
0
END
) / count(*) AS '70-60%',
sum(
CASE
WHEN sc.score >= 0
AND sc.score < 60 THEN
1
ELSE
0
END
) / count(*) AS '60-0%'
FROM
sc
LEFT JOIN course ON sc.CId = course.CId
GROUP BY
sc.cid
-
查询各科成绩前三名的记录
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
HAVING
rank <= 3
ORDER BY
a.CId,
rank ASC
-
查询每门课程被选修的学生数
SELECT
CId,
count(*)
FROM
sc
GROUP BY
Cid
-
查询出只选修两门课程的学生学号和姓名
SELECT
sc.SId,
SName
FROM
sc,
student
WHERE
sc.SId = student.SId
GROUP BY
sc.SId
HAVING
count(sc.SId) = 2
-
查询男生、女生人数
SELECT
Ssex,
count(*)
FROM
student
GROUP BY
student.Ssex
-
查询名字中含有「风」字的学生信息
SELECT
*
FROM
student
WHERE
Sname LIKE concat('%', '风', '%')
-
查询同名同性学生名单,并统计同名人数
SELECT
t1.*, count(t2.SId) + 1 AS num
FROM
student AS t1
LEFT JOIN student AS t2 ON t1.Sname = t2.Sname
WHERE
t1.SId <> t2.SId
GROUP BY
t1.Sid
-
查询 1990 年出生的学生名单
SELECT
*
FROM
student
WHERE
Sage LIKE CONCAT('1990-', '%')
-
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT
CId,
avg(score) AS avgscore
FROM
sc
GROUP BY
CId
ORDER BY
avgscore DESC,
CId ASC
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT
sc.SId,
SName,
avg(sc.score) AS avgscore
FROM
student,
sc
WHERE
sc.SId = student.SId
GROUP BY
SId
HAVING
avgscore >= 85
-
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT
SName,
score
FROM
student,
sc,
course
WHERE
student.SId = sc.SId
AND sc.CId = course.CId
AND Cname = '数学'
AND score < 60
-
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT
student.SId,
SName,
CName,
score
FROM
student
CROSS JOIN course
LEFT JOIN sc ON student.SId = sc.SId
AND course.CId = sc.CId
ORDER BY
student.SId,
course.Cid
-
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT
SName,
CName,
score
FROM
student
LEFT JOIN sc ON student.SId = sc.SId
LEFT JOIN course ON sc.CId = course.CId
WHERE
sc.score > 70
-
查询不及格的课程
SELECT
student.SId,
SName
FROM
sc
LEFT JOIN student ON sc.SId = student.SId
WHERE
sc.score < 60
ORDER BY
student.SId
-
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT
student.SId,
SName
FROM
sc
LEFT JOIN student ON sc.SId = student.SId
WHERE
sc.CId = '01'
AND sc.score > 80
ORDER BY
student.Sid
-
求每门课程的学生人数
SELECT
CId,
count(*)
FROM
sc
GROUP BY
sc.CId
-
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT
student.*, 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
LEFT JOIN student ON a.SId = student.SId
LEFT JOIN course ON a.CId = course.CId
LEFT JOIN teacher ON course.TId = teacher.TId
WHERE
TName = '张三'
GROUP BY
a.CId,
a.SId
HAVING
rank = 1
LIMIT 1
-
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT
student.*, 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
LEFT JOIN student ON a.SId = student.SId
LEFT JOIN course ON a.CId = course.CId
LEFT JOIN teacher ON course.TId = teacher.TId
WHERE
TName = '张三'
GROUP BY
a.CId,
a.SId
HAVING
rank = 1
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT
a.SId,
a.CId,
a.score
FROM
sc AS a
INNER JOIN sc AS b ON a.score = b.score
AND a.SId <> b.SId
AND a.CId <> b.CId
LEFT JOIN student ON a.SId = student.SId
GROUP BY
a.SId,
a.CId
-
查询每门功课成绩最好的前两名
SELECT
a.CId,
a.SId,
a.score
FROM
sc AS a
LEFT JOIN sc AS b ON a.CId = b.CId
AND a.score < b.score
GROUP BY
a.CId,
a.SId
HAVING
count(b.score) + 1 <= 2
ORDER BY
a.CId,
count(b.score) + 1 ASC
-
统计每门课程的学生选修人数(超过 5 人的课程才统计)
SELECT
CId,
count(*)
FROM
sc
GROUP BY
sc.CId
HAVING
count(*) > 5
-
检索至少选修两门课程的学生学号
SELECT
SId
FROM
sc
GROUP BY
sc.SId
HAVING
count(*) >= 2
-
查询各学生的年龄,只按年份来算
SELECT
SId,
Sname,
(YEAR(curdate()) - YEAR(Sage)) AS age
FROM
student
-
查询各学生的年龄,按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
SELECT
SId,
Sname,
TIMESTAMPDIFF(YEAR, sage, CURDATE()) AS age
FROM
student
-
查询本周过生日的学生
SELECT
*
FROM
student
WHERE
WEEK (
CONCAT(
YEAR (CURDATE()),
"-",
MID(sage, 6, 5)
)
) = WEEK (NOW())
-
查询下周过生日的学生
SELECT
*
FROM
student
WHERE
WEEK (
CONCAT(
YEAR (CURDATE()),
"-",
MID(sage, 6, 5)
)
) = WEEK (NOW()) + 1
-
查询本月过生日的学生
SELECT
*
FROM
student AS s
WHERE
MONTH (
CONCAT(
YEAR (CURDATE()),
"-",
MID(s.sage, 6, 5)
)
) = MONTH (CURDATE())
-
查询下月过生日的学生
SELECT
*
FROM
student AS s
WHERE
MONTH (
CONCAT(
YEAR (CURDATE()),
"-",
MID(s.sage, 6, 5)
)
) = MONTH (CURDATE()) + 1