sql的小测试50题（附带题目以及视频讲解的链接）

最新推荐文章于 2025-04-07 15:52:16 发布

laixiw

最新推荐文章于 2025-04-07 15:52:16 发布

阅读量307

点赞数 2

分类专栏： sql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_39834429/article/details/123900542

版权

sql 专栏收录该内容

1 篇文章

订阅专栏

本文通过一系列SQL查询实例，展示了如何从学生、课程、教师和成绩表中获取如课程对比、学生平均成绩、选课情况、教师课程数量、特定课程成绩分布等复杂信息。涉及子查询、连接操作、聚合函数、窗口函数等多种SQL技巧，旨在提升SQL查询能力。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

周日

原数据链接

视频讲解

数据链接失效了看下面的数据

表名和字段
–1.学生表

Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

–2.课程表

Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

–3.教师表

Teacher(t_id,t_name) –教师编号,教师姓名

–4.成绩表

Score(s_id,c_id,s_score) –学生编号,课程编号,分数
测试数据

-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
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('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

#1.查询课程编号为'01'的课程比'02'的课程成绩高的所有学生的学号
#方法一
SELECT s_id
FROM (
SELECT s1.s_id,s1.s_score c1,s2.`s_score` c2
FROM score s1 INNER JOIN  score s2
WHERE s1.`c_id`='01' AND s2.`c_id`='02' AND s1.`s_id`=s2.`s_id`
) t
WHERE t.c1>t.c2
#方法二
SELECT a.s_id 
FROM(
SELECT s1.s_id,s1.s_score c1
FROM score s1
WHERE s1.c_id='01'
) AS a INNER JOIN
(SELECT s1.s_id,s1.s_score c2
FROM score s1
WHERE s1.c_id='02') AS b
WHERE a.s_id=b.s_id AND a.c1>b.c2

#2.查询平均成绩大于60分的学生的学号和平均成绩
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score)>60

#3.查询所有学生的学号，姓名，选课数，总成绩
#方法一 先group by在连接
SELECT a.s_id,a.s_name,IFNULL(b.cnt,0),IFNULL(b.ssum,0)
FROM student a 
LEFT JOIN 
(SELECT s_id,COUNT(*) cnt,SUM(s_score) ssum
FROM score
GROUP BY s_id)b
ON a.s_id=b.s_id;

# 方法二先连接在group by
SELECT student.`s_id`,student.`s_name`, COUNT(*),SUM(score.s_score)
FROM student 
LEFT JOIN score ON student.`s_id`=score.`s_id`
GROUP BY s_id,s_name


##小知识
CASE WHEN语法
可以用 CASE WHEN b.ssum IS NULL THEN 0 ELSE b.ssum END 替换 IFNULL(b.ssum,0)

#4查询姓候老师的个数
SELECT COUNT(*)
FROM teacher
WHERE t_name LIKE '候%'

#5查询没学过张三老师课的学生的学号姓名
#方法一 多个子查询
SELECT student.s_id,student.`s_name`
FROM student 
WHERE s_id  NOT IN 
(SELECT score.`s_id`
FROM score
WHERE c_id=
(SELECT course.`c_id`
FROM course
WHERE t_id=
(SELECT teacher.`t_id`
 FROM teacher
 WHERE t_name='张三'))
)

#方法二尽量先连接
SELECT student.`s_id`,student.`s_name`
FROM student
WHERE student.`s_id` NOT IN(
SELECT score.`s_id`
FROM teacher INNER JOIN course ON teacher.`t_id`=course.`t_id`
INNER JOIN  score ON score.`c_id`=course.`c_id` 
WHERE teacher.`t_name`='张三'
)

# 方法二注意的小事项，下面这个是错误的
# 原因 一个学生有多门课，只要有张三老师教过，就算是学过，
# 假如加上！=就代表只要不是全是张三老师教过的就选出来
SELECT student.`s_id`,student.`s_name`
FROM teacher INNER JOIN course ON teacher.`t_id`=course.`t_id`
INNER JOIN  score ON score.`c_id`=course.`c_id` INNER JOIN
student ON student.`s_id`=score.`s_id`
WHERE teacher.`t_name`!='张三'

#6 查询学过张三老师所教的所有课的同学的学号姓名
SELECT student.`s_id`,student.`s_name`
FROM teacher INNER JOIN course ON teacher.`t_id`=course.`t_id`
INNER JOIN  score ON score.`c_id`=course.`c_id` INNER JOIN
student ON student.`s_id`=score.`s_id`
WHERE teacher.`t_name`='张三'

#7 查询学过编号为01课程并且页学过编号为02课程的学生的学号，姓名

SELECT student.`s_id`,student.`s_name` 
FROM score INNER JOIN student ON student.`s_id`=score.`s_id`
WHERE score.`c_id`='01'
AND score.`s_id` IN(
SELECT s_id
FROM score
WHERE score.`c_id`='02')

SELECT student.`s_id`,student.`s_name`
FROM(
SELECT s_id
FROM score
WHERE score.`c_id`='01'
)a INNER JOIN(
SELECT s_id
FROM score
WHERE score.`c_id`='02') b
ON a.s_id=b.s_id
INNER JOIN student
ON a.s_id=student.`s_id`

#8 查询编号为02课程的总成绩
SELECT SUM(score.`s_score`)
FROM score
WHERE score.`c_id`='02'

#9 查询所有成绩小于60分的学生的学号，姓名
SELECT a.s_id,a.s_name
FROM student a
INNER JOIN( 
SELECT s_id
FROM score
GROUP BY s_id
HAVING MAX(s_score)<60) b
WHERE a.s_id=b.s_id

#10 查询没有没全所有课的学生的学号，姓名
# 下面是错误的因为可能有的学生没有选课
SELECT student.`s_id`,student.`s_name`
FROM
(SELECT s_id,COUNT(*) cnt
FROM score
GROUP BY s_id
)a
INNER JOIN(
SELECT COUNT(*) cnt
FROM course
)b ON a.cnt!=b.cnt
INNER JOIN student ON a.s_id=student.`s_id`

# 正确解法，考虑了学生没有选课，以及压根就没有课的情况
SELECT a.s_id,a.s_name
FROM (
SELECT a.s_id,a.s_name,COUNT(a.c_id) cnt
FROM (
SELECT student.s_id,student.`s_name`,score.`c_id`
FROM student
LEFT JOIN score ON student.`s_id`=score.`s_id`) a
GROUP BY a.s_id,a.s_name)a
WHERE cnt!=(SELECT COUNT(*) FROM course)

#11 查询至少有一门课与学号为01的学生所学课程相同的学生的学号和姓名
# 方法一
SELECT student.`s_id`,student.`s_name`
FROM student 
WHERE student.`s_id` IN
(
SELECT DISTINCT score.`s_id` 
FROM score
WHERE score.`c_id` IN(
SELECT c_id
FROM score
WHERE s_id='01')) AND student.`s_id`!='01'

#方法二 将in改成inner join
SELECT  student.`s_id`,student.`s_name`
FROM student
INNER JOIN(
SELECT DISTINCT score.`s_id`
FROM score
INNER JOIN(
SELECT score.`c_id`
FROM score
WHERE s_id='01') a
ON score.`c_id`=a.c_id)b
WHERE student.`s_id`=b.s_id AND student.`s_id`!='01'

#12 查询和01学号同学所学课程完全相同的其他同学的学号
# 思路 a 找出选课数目与01号相同的学号，b 找出选择了01号没有选择课程的学号，a中不在b的就是最终的答案
SELECT a.s_id
FROM(
SELECT score.`s_id`,COUNT(*) cnt
FROM score
GROUP BY score.`s_id`
)a
WHERE a.cnt=(SELECT COUNT(*)
FROM score 
WHERE score.`s_id`='01'
)AND a.s_id NOT IN(
SELECT score.`s_id`
FROM score
WHERE score.`c_id` 
NOT IN(
SELECT score.`c_id`
FROM score 
WHERE score.`s_id`='01')
) AND a.s_id !='01'

13题重复，14题没有

#15 查询两门及其以上不及格课程的同学的学号
# 方法一 憨憨内连接
SELECT DISTINCT a.s_id
FROM score a
INNER JOIN score b ON a.s_id=b.s_id AND a.c_id!=b.c_id
WHERE a.s_score<60 AND b.s_score<60
#方法二 先过滤，在分组查找
SELECT s_id
FROM(
SELECT s_id,c_id
FROM score
WHERE score.`s_score`<60
)a
GROUP BY s_id
HAVING COUNT(*)>=2

# 16检索01课程分数小于60，按分数降序排列学生信息
# 方法一 先过滤在连接
SELECT b.*,a.s_score
FROM
(
SELECT s_id,c_id,s_score
FROM score
WHERE score.`c_id`='01' AND s_score<60
ORDER BY score.`s_score` DESC
)a
INNER JOIN student b ON a.s_id=b.s_id

# 方法二 
SELECT student.*,score.`s_score`
FROM student INNER JOIN score
ON student.`s_id`=score.`s_id`
WHERE score.`c_id`='01' AND score.`s_score`<60
ORDER BY s_score DESC

# 17按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成
#行转列 看似比较秀，但是c_id和”课程名称“都是写死的，主要是掌握case when 的语法


SELECT s_id "学号",
MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END) "语文" ,
MAX(CASE WHEN c_id='02' THEN s_score ELSE NULL END) "数学" ,
MAX(CASE WHEN c_id='03' THEN s_score ELSE NULL END) "英语" ,
AVG(s_score) "平均成绩"
FROM score
GROUP BY s_id
ORDER BY AVG(s_score) DESC

# 18 查询各科成绩最高分，最低分和平均分
# 显示格式课程id 课程name 最高分最低分 平均分 及格率 中等率 优良率 优秀率
# 及格》=60 中等70-80 优良80-90 优秀》=90


SELECT score.`c_id` "课程id",
course.`c_name` "课程名称",
MAX(score.`s_score`) "最高分",
MIN(score.`s_score`) "最低分",
AVG(score.`s_score`) "平均分"  ,
SUM(CASE WHEN score.`s_score`>= 60 THEN 1 ELSE 0 END)/COUNT(score.`s_score`)  "及格率",
SUM(CASE WHEN score.`s_score`>= 70 AND score.`s_score`<80 THEN 1 ELSE 0 END)/COUNT(score.`s_score`)  "中等率",
SUM(CASE WHEN score.`s_score`>= 80 AND score.`s_score`<90 THEN 1 ELSE 0 END)/COUNT(score.`s_score`)  "优良率",
SUM(CASE WHEN score.`s_score`>= 90 THEN 1 ELSE 0 END)/COUNT(score.`s_score`)  "优秀率"
FROM score INNER JOIN course ON score.`c_id`=course.`c_id`
GROUP BY score.`c_id`

# 19 按照各科成绩进行排序，并显示排名
# 给下面函数二个参数一个参数是决定排序时按照哪个分组（就是类似与选择考试科目进行分组，注意根groupby 不一样，group by 会丢失数据），另外一个参数是决定按照哪个字段进行排序
# row number() 1234
#dense rank()1223
#rank() 1224
SELECT score.`s_id`,score.`c_id`,score.`s_score`,RANK() OVER(PARTITION BY score.`c_id` ORDER BY score.`s_score` DESC ) AS 'rank'
FROM score

# 20 查询学生总成绩并进行排名
SELECT score.`s_id`,SUM(score.`s_score`)
FROM score
GROUP BY score.`s_id`
ORDER BY SUM(score.`s_score`) DESC

# 显示行号的版本,使用子查询
SELECT a.id,a.s,RANK() OVER(ORDER BY a.s DESC ) AS 'rank'
FROM(
SELECT score.`s_id` id,SUM(score.`s_score`) s
FROM score
GROUP BY score.`s_id`) a

# 不使用子查询
SELECT score.`s_id` id,SUM(score.`s_score`) s,ROW_NUMBER() OVER(ORDER BY SUM(score.`s_score`) DESC) m
FROM score
GROUP BY score.`s_id`

# 21 查询不同课程的平均成绩
SELECT score.`c_id`,AVG(score.`s_score`),course.`c_name`
FROM score INNER JOIN course ON score.`c_id`=course.`c_id`
GROUP BY score.`c_id

# 22 查询所有课程的成绩第二名到第三名的学生成绩以及该课程成绩
SELECT *
FROM(
SELECT score.`s_id`,score.`c_id`,score.`s_score`,ROW_NUMBER() OVER(PARTITION BY score.`c_id` ORDER BY score.`s_score` DESC) m
FROM score
)a
WHERE m IN(2,3)

# 23 使用分段【100-85）【85-70）【70-60）【<60）来统计各科成绩，分别统计个分数段人数，课程id和课程名称

SELECT score.`c_id` "课程id",
course.`c_name` "课程名称" ,
SUM(CASE WHEN score.`s_score`>85 THEN 1 ELSE 0 END) "优秀",
SUM(CASE WHEN score.`s_score`>70 AND score.`s_score`<=85 THEN 1 ELSE 0 END) "良好",
SUM(CASE WHEN score.`s_score`>=60 AND score.`s_score`<=70 THEN 1 ELSE 0 END) "一般",
SUM(CASE WHEN score.`s_score`<60  THEN 1 ELSE 0 END) "不及格"
FROM score INNER JOIN course ON score.`c_id`=course.`c_id`
GROUP BY score.`c_id`

#24 查询学生平均成绩及其名次
SELECT a.*,RANK() OVER(ORDER BY c DESC) "名次"
FROM(
SELECT score.`s_id` b, AVG(score.`s_score`) c
FROM score
GROUP BY score.`s_id`
)a
#不使用子查询
SELECT score.`s_id`,AVG(score.`s_score`),ROW_NUMBER() OVER(ORDER BY AVG(score.`s_score`) DESC) m
FROM score
GROUP BY score.`s_id`

25题不讲，重复

# 26 查询每门课程被选修的学生数
SELECT course.`c_id`,course.`c_name`,COUNT(course.`c_id`)
FROM course LEFT JOIN score ON course.`c_id`=score.`c_id`
GROUP BY course.`c_id`,course.`c_name`

# 27 查询出只有两门课程的全部学生的学号和姓名
SELECT student.`s_id`,student.`s_name`
FROM(
SELECT score.`s_id`,COUNT(*) cnt
FROM score
GROUP BY score.`s_id`)a
INNER JOIN student ON a.s_id=student.`s_id`
WHERE a.cnt=2

# 28查询男生，女生人数
SELECT s_sex,COUNT(*)
FROM student
GROUP BY s_sex

# 29查询名字中含有'风'字的学生信息
SELECT *
FROM student
WHERE student.`s_name` LIKE '%风%'

30 题没有

# 31 查询1990年出生的学生名单
SELECT *
FROM student
WHERE YEAR(s_birth)=1990

# 32 查询平均成绩大于等于85的所有学生的学号，姓名和平均成绩
SELECT score.`s_id`,AVG(score.`s_score`),student.`s_name`
FROM score INNER JOIN student ON score.`s_id`=student.`s_id`
GROUP BY score.`s_id`
HAVING AVG(score.`s_score`)>=85

# 33 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时按照课程号降序排列
SELECT score.`c_id`,AVG(score.`s_score`) AS a
FROM score
GROUP BY score.`c_id`
ORDER BY a ASC,c_id DESC

# 34 查询课程名称为数学，且分数低于60的学生姓名和分数
# 方法一
SELECT score.`s_id`,student.`s_name`,score.`s_score`
FROM score INNER JOIN student ON score.`s_id`=student.`s_id`
WHERE score.`s_score`<60 AND
score.`c_id`=(
SELECT course.`c_id`
FROM course 
WHERE course.`c_name`='数学')
# 方法二
SELECT  score.`s_id`,student.`s_name`,score.`s_score`
FROM score INNER JOIN course ON score.`c_id`=course.`c_id` INNER JOIN
student ON score.`s_id`=student.`s_id`
WHERE course.`c_name`='数学' AND score.`s_score`<60

# 35查询所学生的课程及分数情况

SELECT score.`s_id`,
MAX(CASE WHEN course.`c_name`="语文" THEN s_score ELSE NULL END )"语文",
MAX(CASE WHEN course.`c_name`="数学"  THEN s_score ELSE NULL END )"数学",
MAX(CASE WHEN course.`c_name`="英语"  THEN s_score ELSE NULL END )"英语"
FROM score INNER JOIN course ON score.`c_id`=course.`c_id`
GROUP BY score.`s_id`

# 36查询课程成绩在70分以上课程名称，分数和学生姓名
SELECT course.`c_name`,score.`s_score`,student.`s_name`
FROM score INNER JOIN course ON score.`c_id`=course.`c_id` INNER JOIN student ON student.`s_id`=score.`s_id`
WHERE score.`s_score`>70

# 37查询不及格的课程并按课程号从大到小排列
SELECT *
FROM score
WHERE score.`s_score`<60
ORDER BY c_id DESC

# 38查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
SELECT student.`s_id`,student.`s_name`
FROM score INNER JOIN student ON score.`s_id`=student.`s_id`
WHERE score.`c_id`='03' AND score.`s_score`>80

# 39求每门课程的学生人数
SELECT score.`c_id`,COUNT(*)
FROM course LEFT JOIN score ON course.`c_id`=score.`c_id`
GROUP BY score.`c_id`

# 40查询选修张三老师所授的课程的学生中成绩最高的学生的姓名以及成绩
SELECT student.`s_name`,score.`s_score`
FROM teacher INNER JOIN course ON teacher.`t_id`=course.`t_id`
INNER JOIN score ON score.`c_id`=course.`c_id` INNER JOIN student
ON student.`s_id`=score.`s_id`
WHERE teacher.`t_name`='张三'
ORDER BY score.`s_score` DESC
LIMIT 1

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

SELECT a.s_id
FROM(SELECT score.`s_id`
FROM score
GROUP BY score.`s_id`,score.`s_score`
)a INNER JOIN student ON a.s_id=student.`s_id`
GROUP BY a.s_id
HAVING COUNT(*)=1

42 无

# 43统计每门课程的学生选修人数，超过5人才统计
# 输出课程号和选修人数，查询结果按照人数降序排列，若人数相同，按照课程号升序排列
SELECT score.`c_id`,COUNT(*)cnt
FROM score
GROUP BY score.`c_id`
HAVING COUNT(*)>5
ORDER BY cnt DESC,c_id ASC

# 44检索至少选修两门课程的学生学号
SELECT score.`s_id`
FROM score
GROUP BY score.`s_id`
HAVING COUNT(*)>=2

# 45查询选修了全部课程的学生信息
SELECT student.*
FROM score INNER JOIN student ON score.`s_id`=student.`s_id`
GROUP BY score.`s_id`
HAVING COUNT(*)=(
SELECT COUNT(*) cnt
FROM course 
)

# 46查询个学生的年龄
SELECT s_id, s_birth, FLOOR (DATEDIFF('2022-4-1',s_birth)/365 )
FROM student
# 使用now
SELECT s_id, s_birth, FLOOR (DATEDIFF(NOW(),s_birth)/365 )
FROM student
#DATEDIFF(a,b)返回a,b相差的天数，floor(x)返回小于或等于 x 的最大整数

# 47查询没学过张三老师讲授的任一们课程的学生姓名
SELECT *
FROM student
WHERE student.`s_id` NOT IN(
SELECT score.`s_id`
FROM score
WHERE score.`c_id` IN(
SELECT course.`c_id`
FROM teacher INNER JOIN course ON teacher.`t_id`=course.`t_id`
WHERE teacher.`t_name`='张三')
)

# 48查询下周过生日的同学
# week(date,mode) mode=1时代表以周一作为星期的开始
SELECT *
FROM student
WHERE WEEK(s_birth,1)=WEEK(DATE(NOW()),1)+1

# 48查询下周过生日的同学
# week(date,mode) mode=1时代表以周一作为星期的开始
# 思路：将表中的月和日得到，和当前年进行拼接 在使用拼接后的周数和当前日期的周数比较是否差一，
SELECT *
FROM student
WHERE WEEK(
CONCAT('2019-',SUBSTRING(s_birth,6,5))
,1)=WEEK(
'2019-05-15'
,1)+1

# 49查询本月过生日的人
SELECT *
FROM student
WHERE MONTH(student.`s_birth`)=MONTH(NOW())

# 50查询下个月过生日的人
SELECT *
FROM student
WHERE MONTH(student.`s_birth`)=MONTH(NOW())+1