【一日速撕sql】MySql经典45题（含答案及解析过程）

2023-12-04 00:40
文章标签 sql mysql 经典 过程 解析 45 含答案 database 一日

本文主要是介绍【一日速撕sql】MySql经典45题（含答案及解析过程），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

 

一、建表工作

# 学生表 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

这篇关于【一日速撕sql】MySql经典45题（含答案及解析过程）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/451468。

相关文章

网页解析 lxml 库--实战

网页解析 lxml 库--实战

lxml库使用流程 lxml 是 Python 的第三方解析库，完全使用 Python 语言编写，它对 XPath表达式提供了良好的支 持，因此能够了高效地解析 HTML/XML 文档。本节讲解如何通过 lxml 库解析 HTML 文档。 pip install lxml lxm| 库提供了一个 etree 模块，该模块专门用来解析 HTML/XML 文档，下面来介绍一下 lxml 库
阅读更多...
浅析Spring Security认证过程

浅析Spring Security认证过程

类图 为了方便理解Spring Security认证流程，特意画了如下的类图，包含相关的核心认证类 概述 核心验证器 AuthenticationManager 该对象提供了认证方法的入口，接收一个Authentiaton对象作为参数; public interface AuthenticationManager {Authentication authenticate(Authenti
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
作业提交过程之HDFSMapReduce

作业提交过程之HDFSMapReduce

作业提交全过程详解 （1）作业提交 第1步：Client调用job.waitForCompletion方法，向整个集群提交MapReduce作业。 第2步：Client向RM申请一个作业id。 第3步：RM给Client返回该job资源的提交路径和作业id。 第4步：Client提交jar包、切片信息和配置文件到指定的资源提交路径。 第5步：Client提交完资源后，向RM申请运行MrAp
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
【C++】_list常用方法解析及模拟实现

【C++】_list常用方法解析及模拟实现

相信自己的力量，只要对自己始终保持信心，尽自己最大努力去完成任何事，就算事情最终结果是失败了，努力了也不留遗憾。💓💓💓 目录   ✨说在前面 🍋知识点一：什么是list？ •🌰1.list的定义 •🌰2.list的基本特性 •🌰3.常用接口介绍 🍋知识点二：list常用接口 •🌰1.默认成员函数 🔥构造函数（⭐） 🔥析构函数 •🌰2.list对象
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2