注: 网上有很多这套经典题,都大同小异,网上很多答案都是错误的或者还可以写的更好,所以写了这一篇,更多的 目的是做个学习记录
这些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