虽然网上已经有很多相关资源，但是正确解答的优质资源并不太多，因此本篇文章将这50道题重新整理了一下，希望能给刚学习sql的童鞋一点帮助。
Student(S,Sname,Sage,Ssex) 学生表
Course(C,Cname,T) 课程表
SC(S,C,score) 成绩表
【mysql|MySQL经典50题】Teacher(T,Tname) 教师
1.1 创建学生表
CREATE TABLE `student` (
`S` varchar(10) NOT NULL,
`Sname` varchar(10) DEFAULT NULL,
`Sage` varchar(10) DEFAULT NULL,
`Ssex` varchar(15) DEFAULT NULL,
PRIMARY KEY (`S`)
);
1.2 创建课程表
CREATE TABLE `course` (
`C` varchar(20) NOT NULL,
`Cname` varchar(20) DEFAULT NULL,
`T` varchar(10) DEFAULT NULL,
PRIMARY KEY (`C`)
);
1.3 创建教师表
CREATE TABLE `teacher` (
`T` varchar(10) NOT NULL,
`Tname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`T`)
);
1.4 创建成绩表
CREATE TABLE `sc` (
`s` int NOT NULL,
`C` varchar(10) NOT NULL,
`score` varchar(10) DEFAULT NULL,
PRIMARY KEY (`s`,`C`)
);
2.1 向学生表中添加数据
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' , '女');
2.2 向课程表中添加数据
insert into Course values('01','CHINESE','02');
insert into Course values('02','MATH','01');
insert into Course values('03','ENGLISH','03');
2.3 向教师表中添加数据
insert into Teacher values('01','Li Pengfei');
insert into Teacher values('02','Wang Wen');
insert into Teacher values('03','Zhang Zhichao');
2.4 向课程表中添加数据
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);
3.1 查询'01'课程比'02'课程成绩高的所有学生的学号
SELECT a.s
FROM ( SELECT s,score FROM sc WHERE c='01') AS a,
( SELECT s,score FROM sc WHERE c='02') AS b
WHERE a.score>b.score AND a.s=b.s;
3.2 查询平均成绩大于60分的同学的学号和平均成绩
SELECT s,ROUND(AVG(score)) avg_score
FROM sc
GROUP BY s
HAVING avg_score>60;
3.3 查询平均成绩大于60分的同学的学号和平均成绩、课程总数和总分
SELECT s '学号',
ROUND(AVG(score),2) '平均成绩',
COUNT(c) '课程总数',
SUM(score) '总分'
FROM SC
GROUP BY s
HAVING 平均成绩>60;
3.4 查询姓“李”的老师的个数
SELECT count(Tname)
FROM teacher
WHERE Tname like '%li%';
3.5 查询没学过“Li Pengfei”老师课的同学的学号、姓名
SELECT s,sname
FROM student
WHERE sname NOT IN (SELECT DISTINCT(sname)
FROM student AS st
INNER JOIN sc
ON st.s=sc.s
INNER JOIN course AS co
ON sc.C=co.C
INNER JOIN teacher AS te
ON co.T=te.T
WHERE tname = 'Li Pengfei');
3.6 查询学过“01”并且也学过编号“02”课程的同学的学号、姓名
SELECT student.s,Sname
FROM sc,student
WHERE sc.s=student.s AND c='01'AND Sname IN(
SELECT sname
FROM sc,student
WHERE sc.s=student.s AND c='02');
3.7 查询学过“Li Pengfei”老师所教的所有课的同学的学号、姓名
SELECT s,sname
FROM student
WHERE s in (
SELECT sc.s
FROM sc
INNER JOIN course AS co
ON sc.C=co.C
INNER JOIN teacher AS te
ON co.T=te.T
WHERE tname = 'Li Pengfei'
GROUP BY sc.s
HAVING COUNT(1)= (SELECT COUNT(1) FROM course,teacher
WHERE course.t=teacher.t AND Tname='Li Pengfei'));
3.8 查询课程编号“02”的成绩比课程编号“01”课程低的所有同学的学号、姓名
SELECT student.s,Sname
FROM student,
(SELECT s,score FROM sc WHERE c='01') AS t1,
(SELECT s,score FROM sc WHERE c='02') ASt2
WHERE student.s=t1.S AND t1.s=t2.s AND t1.score>t2.score;
3.9 查询所有课程成绩小于80分的同学的学号、姓名
SELECT student.s,sname
FROM student,sc
WHERE student.s=sc.s
GROUP BY student.s
HAVING SUM(CASE WHEN IFNULL(score,0)<80 THEN 1 ELSE 0 END)=COUNT(C);
3.10 查询没有学全所有课的同学的学号、姓名
SELECT student.s,Sname
FROM sc,student
WHERE sc.s=student.s
GROUP BY s
HAVING COUNT(c)!=(SELECT COUNT(c) FROM course );
3.11 查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
SELECT DISTINCT student.s,sname
FROM student
INNER JOIN sc
ON student.s=sc.s
WHERE c in (SELECT c
FROM sc
WHERE s='01')
AND SC.S!='01' 3.12 查询学过学号为“07”同学所有门课的其他同学学号和姓名
SELECT s,Sname
FROM student
WHERE s IN (SELECT DISTINCT s
FROM sc
WHERE c in (SELECT DISTINCT c FROM sc WHERE s='07')
AND s!='07'
GROUP BY s
HAVING count(*)=(SELECT count(1) FROM sc WHERE s='07'));
3.13 把“sc”表中“Li Pengfei”老师教的课的成绩都更改为此课程的平均成绩
UPDATE sc a JOIN
(SELECT ROUND(AVG(score)) t , sc.c from sc
JOIN course ON sc.c=course.c
JOIN teacher ON course.T = teacher.t
WHERE Tname='Li Pengfei'
GROUP BY c ) b
ON a.c=b.c
SET a.score= b.t;
3.14 查询和“07”号的同学学习的课程完全相同的其他同学学号和姓名
SELECT student.s, Sname
FROM sc,student
WHERE sc.s=student.s AND sc.s!='07' AND
sc.sNOT IN ( SELECT s FROM sc
WHERE cNOT IN(SELECT c FROM sc WHERE s='07'))
GROUP BY sc.s
HAVING COUNT(*) = (SELECT COUNT(*) FROM SC WHERE s='07');
3.15 删除学习“Li Pengfei”老师课的SC表记录
DELETE FROM sc WHERE c IN (
SELECT course.c FROM course,teacher
WHERE course.t=teacher.t AND Tname='Li Pengfei');
3.16 向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“03”课程的同学学号，’02‘，插入“02”课的平均成绩
INSERT INTO sc
(SELECTs,02,(SELECT ROUNd(AVG(score),2) FROM sc WHERE c='02' )
FROM student
WHEREs NOT IN (SELECT s FROM sc WHERE c='03' ));
3.17 按平均成绩从高到低显示所有学生的“数学”、“语文”、“英语”三门的课程成绩，所学课程数量，按平均成绩降序排列
SELECT s,
IFNULL ((SELECT score FROM sc,course WHERE t.s=sc.s ANDsc.C=course.C AND cNAME='MATH'),0) "数学",
IFNULL ((SELECT score FROM sc,course WHERE t.s=sc.s AND sc.C=course.C AND cNAME='CHINESE'),0) "语文",
IFNULL ((SELECT score FROM sc,course WHERE t.s=sc.s AND sc.C=course.C AND cNAME='ENGLISH'),0) "英语" ,
COUNT(c),
ROUND(AVG(score),2) avg_score
FROM sc t
GROUP BY S
ORDER BY avg_score DESC;
3.18 查询各科成绩最高和最低的分
SELECT c course_id,
max(score) max,
min(score) min
FROM sc
GROUP BY c;
3.19 按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT c course_id,
ROUND(AVG(score)) avg_score,
ROUND(SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(c)*100,2) "RATE(%)"
FROM sc
GROUP BY c
ORDER BY avg_score ,"RATE(%)" DESC;
3.20 查询如下课程平均成绩和及格率的百分数(用"1行"显示):math、chinese、english
SELECT
round(sum(CASE WHEN Cname='MATH' THEN score ELSE 0 END)/sum(CASE WHEN Cname='MATH' THEN 1 ELSE 0 END),2 ) avg_score_math,
round(sum(CASE WHEN Cname='MATH' AND ifnull(score,0)>=60 THEN 1 ELSE 0 END)/sum(CASE WHEN Cname='MATH' THEN 1 ELSE 0 END)*100,2 ) 'math(%)',
round(sum(CASE WHEN Cname='chinese' THEN score ELSE 0 END)/sum(CASE WHEN Cname='chinese' THEN 1 ELSE 0 END),2 ) avg_score_chinese,
round(sum(CASE WHEN Cname='chinese' AND ifnull(score,0)>=60 THEN 1 ELSE 0 END)/sum(CASE WHEN Cname='chinese' THEN 1 ELSE 0 END)*100,2 ) 'chinese(%)',
round(sum(CASE WHEN Cname='english' THEN score ELSE 0 END)/sum(CASE WHEN Cname='english' THEN 1 ELSE 0 END),2 ) avg_score_english,
round(sum(CASE WHEN Cname='english' AND ifnull(score,0)>=60 THEN 1 ELSE 0 END)/sum(CASE WHEN Cname='english' THEN 1 ELSE 0 END)*100,2 ) 'english(%)'
FROM sc,course
WHERE sc.c=course.c;
3.21 查询不同老师所教不同课程平均分从高到低显示
SELECT teacher.t,Tname,course.cname,ROUND(AVG(score),2) avg_score
FROM sc,teacher,course
WHERE sc.c=course.c AND course.t=teacher.t
GROUP BY t,course.c
ORDER BY avg_score DESC;
3.22 查询如下课程成绩第 3 名到第 6 名的学生成绩单：math、chinese、english
SELECT * FROM
(SELECT c.*,ROW_NUMBER() OVER(ORDER BY c.avg_score DESC) AS rn
FROM
(SELECT student.s,sname,
SUM(CASE WHEN cname='math' then score ELSE 0 END) 'math',
SUM(CASE WHEN cname='chinese' then score ELSE 0 END) 'chinese',
SUM(CASE WHEN cname='english' then score ELSE 0 END) 'english',
ROUND(AVG(sc.score),2) avg_score
FROM student ,sc ,course
WHERE student.s=sc.s AND sc.c=course.c
GROUP BY student.s) c
)d
LIMIT 2,4;
3.23 统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[-60]
SELECT course.c,cname,
SUM( CASE WHEN score<=100 AND score>85 THEN 1 ELSE 0 END)AS "100-85",
SUM(CASE WHEN score<=85 AND score>70 THEN 1 ELSE 0 END) AS "85-70",
SUM(CASE WHEN score<=70 AND score>60 THEN 1 ELSE 0 END) AS"70-60",
SUM(CASE WHEN score<=60 THEN 1ELSE 0 END) AS"-60"
FROM sc,course
WHERE sc.c=course.c
GROUP BY course.c,cname;
3.24 查询学生平均成绩及其名次
SELECT s,Sname,平均成绩,
(SELECT 1+COUNT(平均成绩)
FROM (SELECT sc.s,Sname,ROUND(AVG(score),2) "平均成绩"
FROM sc,student
WHERE sc.s=student.s
GROUP BY sc.s) AS t1
WHERE t1.平均成绩 > t2.平均成绩) AS "名次"
FROM (SELECT sc.s,Sname,ROUND(AVG(score),2) "平均成绩"
FROM sc,student
WHERE sc.s=student.s
GROUP BY sc.s) AS t2
order by 平均成绩 DESC;
3.25 查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT s,c,Sname,score,排名
FROM(SELECT sc.s,c,Sname,score,
ROW_NUMBER() over (PARTITION BY c order by score DESC) as "排名"
FROM sc,student
WHERE sc.s=student.s) t1
WHERE 排名<=3;
3.26 查询每门课程被选修的学生数
SELECT c,COUNT(s)
FROM sc
GROUP BY c;
3.27 查询出只选修了2门课程的全部学生的学号和姓名
SELECT sc.s,Sname,COUNT(c)
FROM sc
INNER JOIN student
ON sc.s=student.s
GROUP BY sc.s
HAVING COUNT(c)=2;
3.28 查询男生、女生人数
SELECT Ssex,COUNT(Ssex)
FROM student
GROUP BY Ssex;
3.29 查询姓“张”的学生名单
SELECT Sname
FROM student
WHERE Sname like "张%";
3.30 查询同名同性学生名单，并统计同名人数
SELECT Sname,Ssex,COUNT(*)
FROM student
GROUP BY Sname,Ssex
HAVING COUNT(*)>1;
3.31 1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT Sname,Sage
FROM student
WHERE YEAR(Sage)='1990';
3.32 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT s,ROUND(AVG(score),2) avg_score
FROM sc
GROUP BY s
ORDER BY avg_score,C DESC;
3.33 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT sc.s,Sname,ROUND(AVG(score),2) avg_score
FROM SC
INNER JOIN student
ON sc.s=student.s
GROUP BY sc.s
HAVING avg_score>85;
3.34 查询课程名称为“MATH”，且分数低于60的学生姓名和分数
SELECT student.s,Sname,score
FROM sc,student,course
WHERE sc.s=student.s AND course.c=sc.c AND Cname='MATH' AND score<60;
3.35 查询所有学生的选课情况
SELECT sc.s,sname,course.c,cname
FROM sc,student,course
WHERE sc.s=student.s AND sc.c=course.c;
3.36 查询每门课程成绩在70分以上的姓名、课程名称和分数
SELECT sc.s,Sname,Cname,score
FROM sc
INNER JOIN student st
ON sc.s=st.s
INNER JOIN course co
ON sc.c=co.c
WHERE sc.s NOT IN (SELECT sc.s FROM sc WHEREscore<70 )
ORDER BY sc.s;
3.37 查询不及格的课程，显示学号、姓名、课程号、成绩
SELECT sc.s,Sname,Cname,score
FROM sc
INNER JOIN student st
ON sc.s=st.s
INNER JOIN course co
ON sc.c=co.c
WHERE score<60;
3.38 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
SELECT sc.s,Sname,Cname,score
FROM sc
INNER JOIN student st
ON sc.s=st.s
INNER JOIN course co
ON sc.c=co.c
WHEREco.c='03' AND score>80;
3.39 求选了课程的学生人数
SELECT count(DISTINCT s)
FROM sc;
3.40 查询选修“Li Pengfei”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT sname,sc.c,score
FROM sc
INNER JOIN student
ON sc.s=student.s
INNER JOIN course
ON course.c=sc.c
INNER JOIN teacher
ON teacher.t=course.t
WHERE Tname="Li Pengfei"
LIMIT 1;
3.41 查询各个课程及相应的选修人数
SELECT cname,count(DISTINCT(s))
FROM sc,course
GROUP BY Cname;
3.42 查询不同课程成绩相同的学生的学号、课程号、学生成绩
SELECT DISTINCT t1.s,t1.c,t1.score
FROM sc t1,sc t2
WHERE t1.s!=t2.s AND t1.score=t2.score
ORDER BY t1.s;
3.43 查询每门课程成绩最好的前两名
SELECT s,c,rn
FROM (SELECT s,c,ROW_NUMBER() over(PARTITION BY c order by score DESC) as rn
FROM sc) AS t1
WHERE rn<3;
3.44 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人 数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT c,count(DISTINCT s)
FROM sc
GROUP BY C
HAVING count(DISTINCT s)>5
ORDER BY count(DISTINCT s) DESC,c;
3.45 检索至少选修3门课程的学生学号
SELECT s, count(c)
FROM sc
GROUP BY s
HAVING COUNT(c)>=3;
3.46 查询全部学生都选修的课程的课程号和课程名
select course.c,Cname
from SC,course
WHERE sc.c=course.c
group by sc.c
having count(*) = (select count(DISTINCT s) from sc);
3.47 查询没学过“叶平”老师讲授的任一门课程的学生姓名
SELECT sname
FROM student
WHERE s NOT IN (SELECT s FROM course,teacher,sc
WHERE course.T=teacher.T AND sc.c=course.C AND Tname="Li Pengfei");
3.48 查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s,ROUND(AVG(score),2)
FROM sc
WHERE score<60
GROUP BY S
HAVING SUM(CASE WHEN IFNULL(score,0)<60 THEN 1 ELSE 0 END)>2;
3.49 检索“03”课程分数小于60，按分数降序排列的同学学号
SELECT s,c,score
FROM sc
WHERE c='03' AND score<60
order by score desc;
3.50 删除“02”同学的“01”课程的成绩
DELETE FROM sc WHERE s="02" AND c="01";
推荐阅读
- 脚本部署mysql
- 登录WordPress后如何查找访问的第一个URL用户
- serverless|持续优化云原生体验，阿里云在Serverless容器与多云上的探索
- #yyds干货盘点#数据分析从零开始实战，PythonPandas与各类数据库
- Redis|24 Redis 缓存替换时的数据淘汰策略
- 如何在SQL数据库中访问自定义WordPress邮箱的内容
- boost.asio|boost.asio mysql_boost.asio服务器使用io_service作为work pool
- MySQL8.0学习笔记|【MySQL】数据库多表操作通关教程(外键约束、多表联合查询)
- #yyds干货盘点#单台zabbix5.0服务器如何拆分数据库角色