mysql 经典45道题（1-18题）

最新推荐文章于 2024-07-20 16:33:27 发布
最新推荐文章于 2024-07-20 16:33:27 发布
阅读量1.1k 收藏 11
点赞数
分类专栏： java必备 程序员 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45935461/article/details/118038323
版权

mysql 经典45道题

一.首先将建表语句分享给大家

# 学生表 Student：*

create table Student(
SId varchar(10) ,
Sname varchar(10),
Sage datetime,
Ssex varchar(10));


# 教师表 Teacher
create table Teacher(
TId varchar(10),
Tname varchar(10)); 


# 科目表 Course
create table Course(
CId varchar(10),
Cname nvarchar(10),
TId varchar(10)); 


# 成绩表 SC
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)); 


------------ 插入数据语句-----------------

# 学生表 Student：

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
insert into Course values('01' , '语文' , '02'); 
insert into Course values('02' , '数学' , '01'); 
insert into Course values('03' , '英语' , '03'); 



# 教师表 Teacher
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四'); 
insert into Teacher values('03' , '王五'); 



# 成绩表 SC

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);

二.好的，接下来就介绍下表数据

课程表数据

CId	Cname	TId
01	语文	    02
02	数学	    01
03	英语	   03

学生成绩表数据

SId	CId	score
01	01	80.0
01	02	90.0
01	03	99.0
02	01	70.0
02	02	60.0
02	03	80.0
03	01	80.0
03	02	80.0
03	03	80.0
04	01	50.0
04	02	30.0
04	03	20.0
05	01	76.0
05	02	87.0
06	01	31.0
06	03	34.0
07	02	89.0
07	03	98.0

学生信息表数据

SId	Sname	Sage			Ssex
01	赵雷	1990-01-01 00:00:00	男
02	钱电	1990-12-21 00:00:00	男
03	孙风	1990-05-20 00:00:00	男
04	李云	1990-08-06 00:00:00	男
05	周梅	1991-12-01 00:00:00	女
06	吴兰	1992-03-01 00:00:00	女
07	郑竹	1989-07-01 00:00:00	女
09	张三	2017-12-20 00:00:00	女
10	李四	2017-12-25 00:00:00	女
11	李四	2017-12-30 00:00:00	女
12	赵六	2017-01-01 00:00:00	女
13	孙七	2018-01-01 00:00:00	女

教师信息表数据

TId	Tname
01	张三
02	李四
03	王五

在这里插入图片描述

接下来就开始练习题目了：

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

SELECT
	  * 
FROM
	  student a
JOIN sc b ON a.SId = b.SId
JOIN sc c ON a.SId = c.SId 
AND  b.cId = '01' 
AND  c.CId = '02' 
AND  b.score > c.score;

在这里插入图片描述

1.1 查询同时存在01课程和02课程的情况
--方法①：
 SELECT
* 
FROM
	( SELECT * FROM sc WHERE sc.CId = '01' ) a
JOIN ( SELECT * FROM sc WHERE sc.CId = '02' )b 
ON a.SId = b.SId;


--方法②： 
SELECT
* 
FROM
	sc a
	JOIN sc b ON a.SId = b.SId 
	AND a.CId = '01' 
	AND b.CId = '02'

在这里插入图片描述

1.2. 查询存在“01”课程但可能不存在‘02’课程的情况（不存在时显示为null）
-- 方法① : 先查询出存在01 课程的记录 再关联sc表
SELECT 
  *
FROM (SELECT * FROM SC A WHERE A.CID ='01') A 
LEFT JOIN SC B 
ON A.SID = B.SID 
AND B.CID ='02';

-- 方法② : 直接关联，但是b.cid = '02' 要放在连接两表时，若放在where后则查不到课程02不存在的数据

SELECT *
FROM sc a 
left JOIN sc b 
on a.sid = b.sid  and b.CId = '02'
where a.CId = '01'

在这里插入图片描述

1.3查询不存在 ‘01’ 课程但是存在 '02’课程的情况
-- 方法①： 先找出不存在01课程的学生，再找出这些学生学习过02课程的
SELECT 
*
from  (SELECT * from sc WHERE sid not in (SELECT sid  from sc where cid  = '01')) a 
JOIN sc b
on a.SId = b.SId and b.CId = '02'

在这里插入图片描述

-- 方法②： 先找出不存在01课程的学生，再找出这些学生学习过02课程的
SELECT 
*
from  (SELECT * from sc WHERE sid not in (SELECT sid  from sc where cid  = '01')) a 

WHERE a.CId = '02'

在这里插入图片描述

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

-- 提示：要求查询平均成绩大于等于60分的同学信息，首先确定是在成绩表里找
-- 找到了之后，是不是可以用sid 学生信息表里面关联，
-- 就可以得到学生的姓名信息。那这里关键是找sid
SELECT 
b.Sname,a.SId, a.avg_score
from  (SELECT sid,avg(score) as avg_score from sc GROUP BY SId  HAVING avg(score) >60) a
join student b 
ON a.SId = b.SId

在这里插入图片描述

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

-- 提示：成绩表里面肯定都是有学生的
-- 所以用成绩表关联就可以得到学生信息
   
SELECT 
b.*
from sc a  left join student b
on a.SId = b.SId GROUP BY b.SId

在这里插入图片描述

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

SELECT
	a.SId,
	a.Sname,
	COUNT( b.score ) AS scount,
	SUM( score ) AS sumScore 
FROM
	student a
	LEFT JOIN sc b ON a.SId = b.SId 
GROUP BY
	a.SId

在这里插入图片描述

4.1 查有成绩的学生信息
SELECT
	* 
FROM
	student 
WHERE
	sid IN (SELECT	sid FROM sc )

在这里插入图片描述

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

SELECT COUNT(1) from teacher where Tname like '李%'

在这里插入图片描述

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

SELECT
	a.* ,d.Tname
FROM
	student a
	LEFT JOIN sc b ON a.sid = b.SId
	LEFT JOIN course c ON b.CId = c.CId
	INNER JOIN teacher d ON c.TId = d.TId 
WHERE
	d.Tname = '张三'

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

SELECT
	*,
	COUNT( b.CId ) 
FROM
	student a
	LEFT JOIN sc b ON a.SId = b.SId 
GROUP BY
	a.SId 
HAVING
	COUNT( b.CId ) <(
	SELECT
		count( 1 ) 
FROM
	course)

在这里插入图片描述

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

SELECT DISTINCT
	a.* 
FROM
	student a
	INNER JOIN sc b ON a.SId = b.SId 
WHERE
	b.CId IN ( SELECT cid FROM sc WHERE SId = '01' );

在这里插入图片描述

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


SELECT
	* 
FROM
	student a
	INNER JOIN sc b ON a.SId = b.SId 
GROUP BY
	b.SId 
HAVING
	b.CId NOT IN ( SELECT CId NOT IN ( SELECT cid FROM sc WHERE SId = '01' ) FROM sc GROUP BY cid ) 
AND COUNT( b.CId ) = (SELECT	COUNT( 1 ) FROM	sc WHERESId = '01')
and b.sid !='01'

在这里插入图片描述

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

SELECT * from student where SId not in
(SELECT 
	a.sid 
FROM
	student a
	INNER JOIN sc b ON a.SId = b.SId
	INNER JOIN course c ON b.CId = c.CId 
	INNER JOIN teacher d on c.TId = d.TId
WHERE d.Tname = '张三')

在这里插入图片描述

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

-- 查询出两门及以上不及格课程的同学的学号
SELECT SId  from sc WHERE score <60 GROUP  BY SId HAVING COUNT(1) >1;

SELECT
	a.SId,
	b.Sname,
	avg( score ) AS avg_Score 
FROM
	sc a
	INNER JOIN student b ON a.SId = b.SId 
GROUP BY
	a.sid 
HAVING
	a.SId IN (SELECT SId FROM	sc WHERE score < 60 GROUP BY SId HAVING COUNT( 1 ) > 1)

在这里插入图片描述

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

SELECT
	a.*,
	b.score 
FROM
	student a
	INNER JOIN sc b ON a.SId = b.SId 
WHERE
	b.CId = '01' 
	AND score < 60 
ORDER BY
	score DESC

在这里插入图片描述

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

-- 求出平均成绩
SELECT SId,score,AVG(score) from sc GROUP BY SId;

-- 再用sc  表联合 查出数据
SELECT
	a.*,
	b.avgscore 
FROM
	sc a
	LEFT JOIN ( SELECT SId, score, AVG( score ) AS avgscore FROM sc GROUP BY SId ) b ON a.SId = b.SId 
ORDER BY
	b.avgscore DESC

在这里插入图片描述

14.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

-- 查询各科成绩的最高分最低分 平均分
SELECT
	a.cid,
	b.Cname,
	MAX( score ) AS 最高分,
	MIN( score ) AS 最低分,
	AVG( score ) AS 平均分,
	COUNT( 1 ) AS 选修人数,
	SUM( CASE WHEN score >= 60 THEN 1 ELSE 0 END )/ COUNT( 1 ) AS 及格率,
	SUM( CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END )/ COUNT( 1 ) AS 中等率,
	SUM( CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END )/ COUNT( 1 ) AS 优良率,
	SUM( CASE WHEN score >= 90 THEN 1 ELSE 0 END )/ COUNT( 1 ) AS 优秀率 
FROM
	sc a
	LEFT JOIN course b ON a.CId = b.CId 
GROUP BY
	a.cid;

在这里插入图片描述

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

SELECT 
sid,cid,score,@rank:=@rank+1 as rk
FROM SC
ORDER BY score desc ;

在这里插入图片描述

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

select * ,
case when (@sco = score)
		 THEN @rank
		 else @rank:= @rank +1
		 end as rn,
@sco:= score 

from sc,(SELECT @rank:= 0,@sco:= NULL) as t

在这里插入图片描述

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


SELECT *, -- ,@rank:= @rank+1
case when (@sco= b.sumscore) then ''
		ELSE @rank:=@rank+1 
		END as rn,
@sco:= b.sumscore
from (SELECT sid,cid,SUM(score) as sumScore FROM sc GROUP BY SId) b,(select @rank:= 0,@sco:=null) as t
ORDER BY b.sumscore DESC

在这里插入图片描述

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


SELECT *, -- ,@rank:= @rank+1
case when (@sco= b.sumscore) then @rank
		ELSE @rank:=@rank+1 
		END as rn,
@sco:= b.sumscore
from (SELECT sid,cid,SUM(score) as sumScore FROM sc GROUP BY SId) b,(select @rank:= 0,@sco:=null) as t
ORDER BY b.sumscore DESC

在这里插入图片描述

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

SELECT
	CId,
	SUM( CASE WHEN 0 < score AND score <= 60 THEN 1 ELSE 0 END )/ COUNT( 1 ) AS '[0,60]',
	SUM( CASE WHEN 60 < score AND score <= 70 THEN 1 ELSE 0 END )/ COUNT( 1 ) AS '[60,70]',
	SUM( CASE WHEN 70 < score AND score <= 85 THEN 1 ELSE 0 END )/ COUNT( 1 ) AS '[70,80]',
	SUM( CASE WHEN 85 < score AND score <= 100 THEN 1 ELSE 0 END )/ COUNT( 1 ) AS '[70,80]' 
FROM
	sc 
GROUP BY
	CId

在这里插入图片描述

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

SELECT
	a.* 
FROM
	SC a 
WHERE
	( SELECT count( 1 ) FROM SC b WHERE a.cid = b.cid AND b.score > a.score )< 3 
ORDER BY
	cid DESC,
	score DESC

在这里插入图片描述

你看看别人家的孩子
关注
  • 0
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 45题 答案_MySQL经典45题（一）
weixin_28753113的博客
01-18 668
1、学生表 Student(SId,Sname,Sage,Ssex)SId ：学生编号Sname：学生姓名Sage ：出生年月Ssex：学生性别2、课程表 Course(CId,Cname,TId)CId ：课程编号Cname ：课程名称TId ：教师编号3、教师表 Teacher(TId,Tname)TId ：教师编号Tname ：教师姓名4、成绩表 SC(SId,CId,score)SId ：...
mysql常见练习题45题
qq_40169177的博客
10-01 2185
create database school; use school; #学生表 create table Student ( Sno varchar(20) not null COMMENT ‘人名’, Sname varchar(20) not null COMMENT ‘姓名’, Ssex varchar(20) not null COMMENT ‘性别’, Sbirthday datet...
mysql45道题目_MYSQL 45道练习题
weixin_36337823的博客
01-31 254
学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。四个表的结构分别如表1-1的表(一)~表(四)所示，数据如表1-2的表(一)~表(四)所示。用SQL语句创建四个表并完成相关题目。表1-1数据库的表结构表(一)Student (学生表)属性名数据类型可否为空含 义Snovarchar (20)否学号(主码)Snamevarchar (20)否学...
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
YouMing_Li的博客
08-28 3985
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
Mysql经典45道题(详解16-30)
Hudas的博客
02-12 5517
Mysql经典45道题(详解16-30)
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
热门推荐
qq_21853321的博客
10-07 6万+
建表语句点击详见 – 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 SELECT st.s_id,st.s_name, (CASE WHEN AVG(sc4.s_score) IS NULL THEN 0 ELSE AVG(sc4.s_score) END) "平均分", (CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END) "语文", (CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.
【MySQL】按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
python全栈
12-06 1万+
思路： 1、先查找所有学生的平均成绩 select sid,avg(degree) from score GROUP BY sid 2、再查找所有学生的所有课程的成绩 select st.sid,st.sname,co.cname,sc.degree from score sc,course co,student st where sc.cid=co.cid and sc.sid=st.sid 3、四表联查，并按照平均成绩从高到低排序 select st.sid,st.sname,co.cname,
数据分析 | MySQL45道练习题（10~18）
weixin_42444823的博客
01-23 1507
MySQL45道练习题
MySQL OCP 8.0 1Z0-908题库
03-21
1、我是此套题库的作者，对准确性负责，考试83分通过2、扔掉你手上所有的那些错误百出的题库，只保留这一份终极题库即可3、作者提供考试全周期技术答疑与指导，资料内含作者联系方式4、作者亲自爆肝整理、实操验证每...
mysql5.7-ocp-888-题库与解析
09-21
内含两份MySQL 5.7 OCP（1Z0-888）MySQL 5.7 Database Administrator题库，前段时间考证自用。亲测大约有90%的考试题量，通过考试足以。
2023最新MySQL100道面试题-附答案解析
03-30
1. 为什么要使用数据库 数据保存在内存 优点：存取速度快 缺点：数据不能永久保存 数据保存在文件 优点：数据永久保存 缺点：1）速度比内存操作慢，频繁的IO操作。2）查询数据不方便 数据保存在数据库 1）数据永久...
18道经典 MySQL 面试题.txt
07-12
18道经典 MySQL 面试题。1、用一句话介绍什么是MySQL？2、对MySQL数据库去重的关键字是什么？3、MySQL多表连接有哪些方式？怎么用的？这些连接都有什么区别？4、MySQL数据库和Redis的区别？5、说一下索引的优势和...
Mysql经典40道面试题
03-28
1、ID自增主键的问题：当删除记录后，MySQL重启并插入新记录时，ID通常会按照自增顺序继续递增，所以新的记录ID将是18，而不是15。 2、MySQL的技术特点包括：ACID合规性、支持多种存储引擎（如InnoDB、MyISAM）、多...
2022-8-17 第七小组 学习日记 (day41)Mysql练习题30道+综合题1道
weixin_56982770的博客
08-19 1743
将这两天学习的Mysql知识进行实际操作，目标是在以后开发中Mysql不会成为我们的绊脚石
2021-09-12
m0_46513929的博客
09-12 3380
mysql数据库题型总结 mysql50道经典题mysql数据库题型总结1.学生表2.课程表3.教师表4.成绩表学生表 Student科目表 Course教师表 Teacher成绩表 SC练习题目答案 网上流传较广的50道SQL训练，奋斗了不知道多久终于写完了。前18道题的难度依次递增，从19题开始的后半部分算是循环练习和额外function的附加练习，难度恢复到普通状态。 第9题非常难，我反正没有写出来，如果有写出来了的朋友还请赐教。 这50道里面自认为应该没有太多错误，而且尽可能使用了最简单或是最直接的
SQL总结（七）简单的查询练习
cc
07-17 2018
一、场景 给定一个场景，学生选课系统为例，大家很熟悉。 主要关系： 学生（学号、姓名、年龄、性别） 教师（教师ID，教师姓名） 课程（课程ID,课程名称,任教教师ID） 成绩（学生ID,课程ID，成绩） 二、创建表并预置数据 创建关系表： --学生:Student(SID,SName,SAge,SSex) --学生表（学号、姓名、年龄、性别） --性别,0表示男,1表示女 --...
5.Mysql多表练习题20道
qq_44438941的博客
07-01 458
日常总结
Mysql多表练习题30道
weixin_49627122的博客
08-17 6188
mysql练习题30道，希望大家能够通过这30道题，精通select查询语言
MySQL：SELECT 语句
最新发布
Zachyy的博客
07-20 403
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值