50道经典查询题(含答案与解析)-MySQL版

最新推荐文章于 2024-04-06 01:47:19 发布

米二

最新推荐文章于 2024-04-06 01:47:19 发布

阅读量2.5k

点赞数 4

分类专栏：数据库文章标签： MySQL 逻辑运算 select运用

本文链接：https://blog.csdn.net/Q176782/article/details/90289384

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

一. 表结构
二. 题目

注: 网上有很多这套经典题,都大同小异,网上很多答案都是错误的或者还可以写的更好,所以写了这一篇,更多的目的是做个学习记录
这些sql全是测试过,可能仍存在不正确的SQL或者有更优的写法,请各位指出,我虚心请教并更正!

我用的mysql版本是5.7.17,为了测试不同场景,数据我有所改动,序号也没对上号,数量无关痛痒,关键是自己能学到东西(我一个也没删,甚至还加了几个,我也不知道为啥对不上号…),

一. 表结构

-- 用工具导出,不美观,但是方便,直接运行即可
DROP TABLE IF EXISTS course;
CREATE TABLE course (
  CId varchar(10) DEFAULT NULL,
  Cname varchar(10) DEFAULT NULL,
  TId varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO course VALUES ('01','语文','02'),('02','数学','01'),('03','英语','03'),('04','化学','01');


DROP TABLE IF EXISTS stucou;
CREATE TABLE stucou (
  SId varchar(10) DEFAULT NULL,
  CId varchar(10) DEFAULT NULL,
  score decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO stucou VALUES ('01','01',20.0),('01','02',90.0),('01','03',99.0),('02','01',80.0),('02','02',60.0),('02','03',80.0),('03','01',80.0),('03','02',60.0),('03','03',80.0),('04','01',80.0),('04','02',50.0),('04','03',80.0),('05','01',76.0),('05','02',87.0),('06','01',31.0),('06','03',34.0),('07','02',89.0),('07','03',90.0),('03','04',55.0),('08','02',90.0);


DROP TABLE IF EXISTS student;
CREATE TABLE student (
  SId varchar(10) DEFAULT NULL,
  Sname varchar(10) DEFAULT NULL,
  Sage datetime DEFAULT NULL,
  Ssex varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO student VALUES ('01','赵雷','1990-01-01 00:00:00','男'),('02','钱电','1990-12-21 00:00:00','男'),('03','孙风','1990-12-20 00:00:00','男'),('04','李云','1990-12-06 00:00:00','男'),('05','周梅','1991-12-01 00:00:00','女'),('06','吴兰','1992-01-01 00:00:00','女'),('07','郑竹','1989-01-01 00:00:00','女'),('09','张三','2017-05-20 00:00:00','女'),('10','李四','2017-12-25 00:00:00','女'),('11','李四','2012-06-06 00:00:00','女'),('12','赵六','2013-05-13 00:00:00','女'),('13','孙七','2014-05-12 00:00:00','女'),('08','周八','2017-12-22 00:00:00','男');

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
  TId varchar(10) DEFAULT NULL,
  Tname varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO teacher VALUES ('01','张三'),('02','李四'),('03','王五');

二. 题目

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

SELECT s.*,sc.score,sc1.score
FROM stucou sc
INNER JOIN student s ON s.SId=sc.sid AND sc.cid='01'
INNER JOIN stucou sc1 ON sc1.SId = s.SId AND sc1.CId='02'
WHERE sc.score > sc1.score;

– 1.1 查询同时存在" 01 “课程和” 02 "课程的情况

SELECT sc.CId,sc.score,sc1.CId,sc1.score,sc.SId
FROM  stuCou   sc  
INNER JOIN stucou sc1 ON sc.SId=sc1.SId AND sc1.CId='02' AND  sc.CId='01'

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

SELECT sc.CId,sc.score,sc1.CId,sc1.score,sc.SId
FROM  stuCou   sc  
LEFT JOIN stucou sc1 ON sc.SId=sc1.SId AND sc1.CId='02'
WHERE sc.CId='01' ;

– 1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

SELECT sc.CId,sc.score,sc.SId
FROM  stuCou   sc  
INNER JOIN student s ON s.SId = sc.SId
WHERE sc.SId NOT IN (SELECT sc1.SId from stucou sc1 where  sc1.CId = '01')
AND sc.SId IN (SELECT sc1.SId from stucou sc1 where  sc1.CId = '02')

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

SELECT 
	sc.SId,s.Sname,avg(sc.score)
FROM stucou sc
INNER JOIN student s ON s.SId = sc.SId
GROUP BY sc.SId
HAVING avg(sc.score) >= 60

– 3.查询在 SC 表存在成绩的学生信息

SELECT 
s.*
FROM student s 
WHERE   exists (SELECT 1 FROM stucou WHERE s.SId = SId)

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

SELECT
	s.SId,s.Sname,count(sc.CId),sum(sc.score)
FROM student s
LEFT JOIN stucou sc ON sc.SId = s.SId
GROUP BY s.SId

– 4.1 查有成绩的学生信息

SELECT
	s.SId,s.Sname,count(sc.CId),sum(sc.score)
FROM student s
INNER JOIN stucou sc ON sc.SId = s.SId
GROUP BY s.SId;

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

SELECT
*
FROM teacher
WHERE Tname LIKE '李%';

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

SELECT
s.*
FROM student s 
INNER JOIN stucou sc ON sc.SId = s.SId
INNER JOIN course c ON c.CId = sc.CId
INNER JOIN teacher t ON t.TId = c.TId
WHERE t.Tname = '张三';

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

SELECT
	s.*
FROM student s 
LEFT JOIN stucou sc ON sc.SId = s.SId
GROUP BY s.SId
HAVING count(sc.CId) < (SELECT count(1) FROM course);

-- 存在一个课程,有这个课,但是学生没学,(练习蕴含逻辑运算)
SELECT
	s.*
FROM student s 
WHERE EXISTS (
	SELECT 1 FROM course c
	WHERE NOT EXISTS (
		SELECT 1 FROM stucou sc 
		WHERE sc.CId = c.CId AND s.SId=sc.SId
	)
);

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

SELECT
	s.*
FROM student s
INNER JOIN stucou sc ON sc.SId = s.SId
WHERE sc.CId IN (
	SELECT cid FROM stucou WHERE SId = '01'
)
AND s.SId != '01'
GROUP BY s.SId;

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

网上很多这道题写的很复杂,或者用判断课程数的方式来做(这是不正确的),其实这是逻辑运算,涉及到离散数学的知识,详情解释:https://blog.csdn.net/qsyzb/article/details/12525955, 前后几个题我按照这种方式写了答案,以供练习
此题抄至:https://www.jianshu.com/p/476b52ee4f1b, 评论区第22楼

		-- 不存在这样的课程 , 01学了,但是学生x没学  ->   蕴含逻辑运算
SELECT *
FROM student
WHERE  NOT EXISTS( 
	SELECT * 
	FROM stucou  sc1
	WHERE sc1.SId='01' AND NOT EXISTS( 
		SELECT * 
		FROM stucou sc2 
		WHERE sc2.SId=student.SId AND sc1.CId=sc2.CId)
)
-- and s.sid!='01'  (需要排除01就排除呗)

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

SELECT
	s.*
FROM student s
WHERE NOT EXISTS (
	SELECT 1 FROM stucou sc 
	INNER JOIN course c ON c.CId= sc.CId
	INNER JOIN teacher t ON t.TId= c.TId
	WHERE t.Tname='张三' AND sc.SId = s.SId
)

– 10.1查询没学过"张三"老师讲授的全部课程的学生姓名

	-- 存在这样的课程,张三老师教了,但是学生没学
SELECT
	s.*
FROM student s 
WHERE   EXISTS (
	SELECT 1 FROM teacher t 
	INNER JOIN course c ON  c.TId= t.TId
	WHERE t.Tname='张三' AND  NOT EXISTS (
		SELECT 1 FROM stucou sc 
		WHERE c.CId = sc.CId AND sc.SId=s.SId
	)
)

– 10.2查询学过"张三"老师讲授的全部课程的学生姓名

	-- 不存在这样的课程,张三老师教了,但是学生没学
SELECT
	s.*
FROM student s 
WHERE NOT EXISTS (
	SELECT 1 FROM teacher t 
	INNER JOIN course c ON  c.TId= t.TId
	WHERE t.Tname='张三' AND  NOT EXISTS (
		SELECT 1 FROM stucou sc 
		WHERE c.CId = sc.CId AND sc.SId=s.SId
	)
)

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

像这种求两门以上的数据,都可以自联(左联),比较大小,判断个数,后面还有还几个这样的题

SELECT
	s.*,avg(sc.score)
FROM student s 
INNER JOIN stucou sc ON sc.sid = s.SId
WHERE EXISTS (
	SELECT * FROM stucou sc1 
	WHERE sc1.score<60  AND s.SId = sc1.SId
	GROUP BY sc1.SId
	HAVING count(sc1.SId)>=2
)
GROUP by s.SId

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

SELECT 
	s.*,sc.CId,sc.score
FROM student s 
INNER JOIN stucou sc ON sc.SId= s.SId
WHERE sc.CId='01' AND sc.score < 60
ORDER BY sc.score desc

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

SELECT
	*
FROM  stucou sc  
LEFT JOIN (
	SELECT sc1.SId, avg(sc1.score) avgsc FROM stucou sc1 
	GROUP BY sc1.SId
)r ON   sc.SId=r.SId
ORDER BY sc.SId DESC

– 13.查询各科成绩最高分、最低分和平均分：

SELECT
	sc.CId,min(sc.score),max(sc.score),avg(sc.score)
FROM stucou sc
GROUP BY sc.CId

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

SELECT
	sc.CId,c.Cname,max(sc.score),min(sc.score),avg(sc.score),
	sum(CASE  WHEN sc.score>=60 THEN 1 ELSE 0 END )/ count(1) 及格率,
	sum(CASE  WHEN sc.score>=70 AND sc.score<80 THEN 1 ELSE 0 END )/ count(1) 中等率,
	sum(CASE  WHEN sc.score>=80 AND sc.score<90 THEN 1 ELSE 0 END )/ count(1) 优良率,
	sum(CASE  WHEN sc.score>=90 THEN 1 ELSE 0 END )/ count(1) 优秀率,
	count(1) num 
FROM stucou sc 
INNER JOIN course c ON c.CId=sc.CId
GROUP BY c.CId
ORDER BY num DESC,c.CId ASC

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

在oralce中有rank()等开窗函数,在mysql中没有,可以这样写,思路和前面的求两门及格的学生差不多

SELECT
	sc.CId,sc.SId,sc.score,count(sc1.score)+1 num  
FROM stucou sc 
LEFT JOIN stucou sc1 ON sc.CId=sc1.CId AND sc.score < sc1.score -- 用and不用where,在连接时就需过滤,
GROUP BY sc.CId,sc.SId
ORDER BY sc.CId , num  ASC

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

解释见 16.1

SELECT
	sc.CId,sc.SId,sc.score,count(sc1.SId)+1 num  
FROM stucou sc 
LEFT JOIN stucou sc1 ON sc.CId=sc1.CId AND (sc.score < sc1.score OR (sc.score = sc1.score AND sc.SId > sc1.SId)  )
GROUP BY sc.CId,sc.SId
ORDER BY sc.CId ASC , num  ASC

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

SELECT a.SId, a.sum_score, COUNT(b.SId)+1 AS rank
FROM 
(SELECT SId, SUM(score) AS sum_score FROM stucou SC GROUP BY SId) AS a 
LEFT JOIN (SELECT SId, SUM(score) AS sum_score FROM stucou SC GROUP BY SId) AS b 
ON (a.sum_score<b.sum_score )
GROUP BY a.SId, a.sum_score
ORDER BY rank ASC;

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

此题抄至: https://www.jianshu.com/p/476b52ee4f1b ,评论区第48楼

SELECT a.SId, a.sum_score, COUNT(b.SId)+1 AS rank
FROM 
(SELECT SId, SUM(score) AS sum_score FROM stucou SC GROUP BY SId) AS a 
LEFT JOIN (SELECT SId, SUM(score) AS sum_score FROM stucou SC GROUP BY SId) AS b 
ON (a.sum_score<b.sum_score OR (a.sum_score=b.sum_score AND a.SId > b.SId)) -- 要算上分数相同,但不是自己的,还不能写 != ,因为分数相同不一定因为自己和别人,还有可能是另外两个人相同
GROUP BY a.SId, a.sum_score
ORDER BY rank ASC;

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

SELECT
	c.CId,c.Cname,
	sum(CASE WHEN sc.score >=85 THEN 1 ELSE 0 END) excellent,
	sum(CASE WHEN sc.score <85 AND sc.score >=70 THEN 1 ELSE 0 END) good,
	sum(CASE WHEN sc.score <70 AND sc.score >=60 THEN 1 ELSE 0 END) pass,
	sum(CASE WHEN sc.score <60  THEN 1 ELSE 0 END) fail
FROM stucou sc 
INNER JOIN course c ON c.CId=sc.CId
GROUP BY sc.CId

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

SELECT
	sc.*
FROM stucou sc 
LEFT JOIN stucou sc1 ON sc.CId=sc1.CId AND sc.score<sc1.score
GROUP BY sc.CId,sc.SId
HAVING count(sc1.CId)<3
ORDER BY sc.CId, sc.score

– 19.查询每门课程被选修的学生数

SELECT
	sc.CId,count(1)
FROM stucou sc 
GROUP BY sc.CId

– 20.查询出只选修两门课程的学生学号和姓名

SELECT
	s.SId,s.Sname,count(sc.CId)
FROM stucou sc 
INNER JOIN student s ON s.SId=sc.SId
GROUP BY sc.SId
HAVING count(sc.CId) = 2

– 21.查询男生、女生人数

SELECT
	Ssex,count(1)
FROM student 
GROUP BY Ssex

– 22.查询名字中含有「风」字的学生信息

SELECT 
	*
FROM student
WHERE Sname LIKE '%风%'

– 23查询相同姓名学生名单，并统计同名人数

SELECT
	SId,Sname,count(1)
FROM student 
GROUP BY Sname
HAVING count(1) >1

– 24查询 1990 年出生的学生名单

SELECT
	*
FROM student s
WHERE  YEAR(s.Sage) = '1990'

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

SELECT
		sc.CId,avg(sc.score) avgs
FROM stucou sc 
GROUP BY sc.CId
ORDER BY avgs DESC , sc.CId ASC

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

SELECT
	sc.SId,s.Sname, avg(sc.score) avgs
FROM stucou sc 
INNER JOIN student s ON s.SId=sc.SId
GROUP BY sc.SId
HAVING avgs>=85

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

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

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

SELECT 
	s.Sname,sc.CId,sc.score,s.SId
FROM student s 
LEFT JOIN stucou sc ON sc.SId=s.SId

– 29查询任何一门课程成绩在 70 分以上的学生姓名、课程名称和分数

SELECT 
	s.SId,s.Sname,c.CId,c.Cname,sc.score
FROM stucou sc
INNER JOIN student s ON s.SId=sc.SId
INNER JOIN course c ON c.CId = sc.CId
WHERE NOT EXISTS (
	SELECT 1 FROM stucou sc1 
	WHERE sc1.sId = sc.sId AND sc1.score < 70
)

– 30查询不及格课程的学生和课程

SELECT 
	s.SId,s.Sname,sc.CId,sc.score
FROM  student s 
INNER JOIN stucou sc ON s.SId=sc.SId AND sc.score<60
GROUP by s.sid,sc.CId

– 31查询课程编号为 01 且课程成绩在 70 分以上的学生的学号和姓名

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

– 32求每门课程的学生人数

SELECT
	sc.CId,count(1)
FROM stucou  sc
GROUP BY sc.CId

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

SELECT
	s.SId,max(sc.score)
FROM stucou sc 
INNER JOIN student s ON s.SId=sc.SId
INNER JOIN course c ON c.CId=sc.CId
INNER JOIN teacher t ON t.TId=c.TId
WHERE t.Tname='张三'

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

SELECT
	s.SId,sc.score
FROM stucou sc 
INNER JOIN student s ON s.SId=sc.SId
INNER JOIN course c ON c.CId=sc.CId
INNER JOIN teacher t ON t.TId=c.TId
LEFT  JOIN stucou sc1 ON sc.CId=sc1.CId   AND  sc.score < sc1.score
WHERE t.Tname='张三' 
GROUP BY sc.SId
HAVING count(sc1.score)<1

– 35查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
	sc.SId,sc.CId,sc.score
FROM stucou sc 
INNER JOIN stucou sc1 ON sc.CId!=sc1.CId AND sc.score=sc1.score
GROUP BY sc.SId,sc.CId

– 36查询每门功成绩最好的前两名

SELECT
	sc.*
FROM stucou sc 
LEFT JOIN stucou sc1 ON sc1.CId=sc.CId AND sc.score<sc1.score
GROUP BY sc.CId,sc.SId
HAVING count(sc.score)<2

– 37统计每门课程的学生选修人数（超过 5 人的课程才统计）。

SELECT
	sc.CId,count(1) num
FROM stucou sc 
GROUP BY sc.CId
HAVING num > 5

– 38检索至少选修两门课程的学生学号

SELECT 
	sc.SId,count(1)
FROM stucou sc 
GROUP BY sc.SId

– 39查询选修了全部课程的学生信息

SELECT
	sc.SId, count(1) num 
FROM stucou sc 
GROUP BY sc.SId
HAVING num = (
	SELECT count(1) FROM course
)

– 40查询各学生的年龄，只按年份来算

select 
	student.SId as 学生编号 , student.Sname  as  学生姓名 , 
	TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄
from student

– 41按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

SELECT
	s.sname, 
	sum(CASE WHEN  
			date_format(sysdate(),'%m-%d') > date_format(s.sage,'%m-%d') 
		THEN date_format(sysdate(),'%Y') - date_format(s.sage,'%Y') +1 
		ELSE date_format(sysdate(),'%Y') - date_format(s.sage,'%Y')  END )age
FROM student s 
GROUP BY s.SId

– 42查询本周过生日的学生

– WEEKOFYEAR 得到时间在那一年的第几周,例如, WEEKOFYEAR(‘2018-02-15’) 得到的是2月15这天在2018是第几周
SELECT yearweek(sysdate()) --结果:201919 ; 估计得到的是从1990年到sysdate()的周数,值都特别大

SELECT
	 s.*
FROM student s 
WHERE WEEKOFYEAR(concat(date_format(sysdate(),'%Y'),'-',date_format(s.Sage, '%m-%d'))) = WEEKOFYEAR(sysdate());

– 43查询下周过生日的学生

SELECT
	 s.*
FROM student s 
WHERE WEEKOFYEAR(concat(date_format(sysdate(),'%Y'),'-',date_format(s.Sage, '%m-%d'))) = WEEKOFYEAR(sysdate())+1;

– 44查询本月过生日的学生

SELECT
	 s.*
FROM student s 
WHERE MONTH(sysdate()) = MONTH(s.Sage)

– 45查询下月过生日的学生

 SELECT
	 s.*
FROM student s 
WHERE MONTH(sysdate())+1 = MONTH(s.Sage)

https://blog.csdn.net/fashion2014/article/details/78826299 这位博主的文章很热门,可以看看,个人感觉可以多看评论,正文中还是有许多不正确的地方, 时间截止2019-05-17

米二

关注

4
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录