MySQL-50道经典sql题汇总

最新推荐文章于 2023-07-09 20:49:56 发布

Turing·

最新推荐文章于 2023-07-09 20:49:56 发布

阅读量7.2k

点赞数 17

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_41835813/article/details/108329971

版权

数据库专栏收录该内容

12 篇文章 1 订阅

订阅专栏

题目是根据自己的理解做的，都能跑出来，但是肯定还有不足之处，欢迎大家提出问题，一起研究学习

建表

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;

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `SId` varchar(10) default NULL,
  `CId` varchar(10) default NULL,
  `score` int(4) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `SId` varchar(10) default NULL,
  `Sname` varchar(20) default NULL,
  `Sage` date default NULL,
  `Ssex` varchar(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `TId` varchar(10) default NULL,
  `Tname` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加数据

course

INSERT INTO `course` VALUES ('01', 'CHINESE', '02');
INSERT INTO `course` VALUES ('02', 'MATH', '01');
INSERT INTO `course` VALUES ('03', 'ENGLISH', '03');
INSERT INTO `course` VALUES ('04', 'JAVA', '04');

sc

INSERT INTO `sc` VALUES ('01', '01', 80);
INSERT INTO `sc` VALUES ('01', '03', 99);
INSERT INTO `sc` VALUES ('02', '04', 50);
INSERT INTO `sc` VALUES ('02', '03', 80);
INSERT INTO `sc` VALUES ('03', '01', 80);
INSERT INTO `sc` VALUES ('03', '03', 80);
INSERT INTO `sc` VALUES ('04', '01', 50);
INSERT INTO `sc` VALUES ('04', '03', 20);
INSERT INTO `sc` VALUES ('05', '01', 100);
INSERT INTO `sc` VALUES ('06', '01', 31);
INSERT INTO `sc` VALUES ('06', '03', 34);
INSERT INTO `sc` VALUES ('07', '03', 98);
INSERT INTO `sc` VALUES ('07', '04', 55);
INSERT INTO `sc` VALUES ('01', '04', 50);
INSERT INTO `sc` VALUES ('01', '02', 10);
INSERT INTO `sc` VALUES ('02', '02', 70);
INSERT INTO `sc` VALUES ('03', '02', 50);
INSERT INTO `sc` VALUES ('04', '02', 80);
INSERT INTO `sc` VALUES ('05', '02', 73);
INSERT INTO `sc` VALUES ('06', '02', 99);
INSERT INTO `sc` VALUES ('07', '02', 100);
INSERT INTO `sc` VALUES ('08', '02', 59);
INSERT INTO `sc` VALUES ('09', '02', 80);
INSERT INTO `sc` VALUES ('08', '01', 40);
INSERT INTO `sc` VALUES ('08', '03', 35);

student

INSERT INTO `student` VALUES ('01', 'Zhao Lei', '1990-1-1', 'MAN');
INSERT INTO `student` VALUES ('02', 'Qian Dian', '1990-12-21', 'MAN');
INSERT INTO `student` VALUES ('03', 'Sun Feng', '1990-5-20', 'MAN');
INSERT INTO `student` VALUES ('04', 'Li Yun', '1990-8-6', 'MAN');
INSERT INTO `student` VALUES ('05', 'Zhou Mei', '1991-12-1', 'WOMAN');
INSERT INTO `student` VALUES ('06', 'Wu Lan', '1992-3-1', 'WOMAN');
INSERT INTO `student` VALUES ('07', 'Zheng Zhu', '1989-7-1', 'WOMAN');
INSERT INTO `student` VALUES ('08', 'Wang Ju', '1990-1-20', 'WOMAN');
INSERT INTO `student` VALUES ('09', 'Wang Ju', '2020-8-30', 'MAN');

teacher

INSERT INTO `teacher` VALUES ('01', 'Li Pengfei');
INSERT INTO `teacher` VALUES ('02', 'Wang Wen');
INSERT INTO `teacher` VALUES ('03', 'Zhang Zhichao');
INSERT INTO `teacher` VALUES ('04', 'Ye Ping');

题目

01、查询'01'课程比'02'课程成绩高的所有学生的学号；

SELECT a.SId FROM (select SId,score from `sc` where CId = '01')as a 
INNER JOIN 
(select SId,score from `sc` where CId = '02')as b 
ON a.SId = b.SId
INNER JOIN student as c ON c.SId = a.SId
where a.score>b.score

02、查询平均成绩大于60分的同学的学号和平均成绩；

select SId,AVG(score) FROM sc GROUP BY SId HAVING AVG(score)>60

03、查询所有同学的学号、姓名、选课数、总成绩；

一SELECT t1.SId,t1.Sname,
(SELECT COUNT(CId) FROM sc s1 where s1.SId=t1.SId) 数量,
(SELECT SUM(score) FROM sc s2 where s2.SId = t1.SId) 总成绩
from student t1


二select stu.SId , stu.Sname,COUNT(s.CId),sum(s.score) from sc as s join student as stu 
on s.SId = stu.SId
GROUP BY stu.SId

04、查询姓“李”的老师的个数；

SELECT COUNT(Tid) FROM teacher where Tname LIKE 'Li%'

05、查询没学过“叶平”老师课的同学的学号、姓名；思路：查找所有学过yp老师可的学生，然后看哪个Sid没在里面

SELECT SId,Sname from student WHERE SId NOT IN 
(SELECT SId from sc where CId IN 
(SELECT CId FROM course where TId IN 
(SELECT TId FROM teacher where Tname ='Ye Ping')))

06、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；

SELECT SId,Sname from student st where 
(select COUNT(*) FROM sc s1 where s1.SId=st.SId and CId='01')>0
and (select COUNT(*) from sc s2 where s2.SId=st.SId and CId='02')>0


SELECT DISTINCT  stu.SId , stu.Sname from sc as s join student as stu on s.SId = stu.SId where 
01 in (SELECT CId from sc where stu.SId = SId) and 02 in (SELECT CId from sc where stu.SId = SId)

07、查询学过“Li Pengfei”老师所教的所有课的同学的学号、姓名；

select DISTINCT stu.SId ,stu.Sname from student as stu 
join sc as s on stu.SId = s.SId
where (SELECT COUNT(1) from sc as ss join course as c on ss.CId = c.CId 
JOIN teacher as t on c.TId = t.TId 
where s.SId = ss.SId and t.Tname = "Li PengFei" )
 =
 (select COUNT(1) from course as cou join teacher as tea on cou.TId = tea.TId
where tea.Tname = "Li PengFei")

08、查询课程编号“02”的成绩比课程编号“01”课程低的所有同学的学号、姓名；

一  SELECT st.SId,st.Sname from (SELECT SId,score from sc where CId = '02') a
INNER JOIN (SELECT SId,score from sc where CId = '01') b
ON a.SId = b.SId
INNER JOIN student st ON st.SId = b.SId where a.score<b.score
二  SELECT SId,Sname from student where SId IN 
(SELECT s1.SId from sc s1 ,sc s2 where 
s1.SId = s2.Sid and s2.CId = '02' and s1.CId = '01' and s1.score>s2.score)

09、查询所有课程成绩小于80分的同学的学号、姓名；

SELECT SId ,sname from student where SId NOT IN (select SId from sc where score >=80
)



SELECT  stu.SId,stu.Sname from  student as stu
where (SELECT COUNT(1) from sc where stu.SId = SId) = (SELECT COUNT(1) from sc WHERE stu.SId = SId and score<80)

10、查询没有学全所有课的同学的学号、姓名；

SELECT SId,Sname from student where SId IN(select SId from sc
group BY SId HAVING (SELECT COUNT(CId) from course)=COUNT(SId))


SELECT DISTINCT  stu.SId,stu.Sname from  student as stu 
where (SELECT COUNT(1) from sc where stu.SId = SId) = (SELECT COUNT(1) from course )

11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；

SELECT SId ,Sname from student where SId IN 
(select SId from sc where CId IN 
(select CId from sc  where SId = '01'))

12、查询学过学号为“07”同学所有门课的其他同学学号和姓名；

select SId ,Sname from student where SId IN(SELECT SId from sc WHERE 
CId IN(SELECT CId from sc where SId = '07')
GROUP BY CId HAVING COUNT(SId)>=(SELECT COUNT(SId) from sc where SId = '07'))

13.丢了知道的可以告诉我一下

14、查询和“07”号的同学学习的课程完全相同的其他同学学号和姓名；

select s.SId,stu.Sname  from sc as s join student as stu on s.SId = stu.SId where 
s.SId not in (select SId from sc where CId not in (select CId from sc where SId = 07)) and
(SELECT COUNT(1) from sc where SId = 07) = (SELECT COUNT(1) from sc where SId = s.SId)
GROUP BY s.SId,stu.Sname

15、删除学习“Li Pengfei”老师课的SC表记录；

DELETE from sc where CId IN (select CId from course where TId = (SELECT TId from teacher where Tname = 'Li Pengfei'))

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“03”课程的同学学号，“02”，以及“02”课的平均成绩；

INSERT INTO sc  SELECT SId,'02',b.av from sc,(SELECT AVG(score) AS av  from sc where CId = '02') b
where SId NOT IN (SELECT SId from sc s2 where CId ='03')

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”
三门的课程成绩，按如下形式显示：

select st.Sname,a.score as 'MATH',b.score as 'ENGLISH',c.score as 'CHINESE' from
(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'MATH')) as a
INNER JOIN (SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'ENGLISH')) as b ON a.SId = b.SId
INNER JOIN(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'CHINESE')) as c ON b.SId = c.SId
INNER JOIN student as st ON c.SId = st.SId 
GROUP BY st.Sname ORDER BY AVG(a.score+b.score+c.score)DESC

18、查询各科成绩最高和最低的分：以如下形式显示：course_id，max，min

SELECT CId as 'coures_id',MAX(score) as 'max',MIN(score) as 'min'
from sc 
GROUP BY CId

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT CId,AVG(score) as '平均成绩',sum(case when score>60 then 1 else 0 end)/count(*)*100 as '及格率'
from sc 
GROUP BY CId
ORDER BY AVG(score) asc,sum(case when score>=60 then 1 else 0 end)/count(*) desc



SELECT s.CId,cou.Cname,AVG(s.score),
(SELECT COUNT(1) from sc where CId = s.CId and score>60)/(SELECT COUNT(1) from sc WHERE CId = s.CId) as 及格率
from sc as s JOIN course as cou on s.CId = cou.CId
GROUP BY CId
ORDER BY AVG(s.score) ASC,及格率 DESC

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): math（01），chinese（02），english（03）

select AVG(a.score) as 'MATH',SUM(case when a.score>=60 then 1 else 0 end)/count(a.SId)*100  as 'MATH%',
AVG(b.score) as 'ENGLISH',sum(case when b.score>=60 then 1 else 0 end)/count(b.SId)*100  as 'ENGLISH%',
AVG(c.score) as 'CHINESE', sum(case when c.score>=60 then 1 else 0 end)/count(c.SId)*100  as 'CHINESE%' 
from
(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'MATH')) as a
INNER JOIN (SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'ENGLISH')) as b 
INNER JOIN(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'CHINESE')) as c 



SELECT s.CId,cou.Cname,AVG(s.score),
(SELECT COUNT(1) from sc where CId = s.CId and score>60)/(SELECT COUNT(1) from sc WHERE CId = s.CId) as 及格率
from sc as s JOIN course as cou on s.CId = cou.CId where s.CId in (01,02,03)
GROUP BY CId

21、查询不同老师所教不同课程平均分从高到低显示


SELECT tea.Tname,cou.Cname,AVG(s.score) as 平均分
from sc as s JOIN course as cou on s.CId = cou.CId 
JOIN teacher as tea on tea.TId = cou.TId
GROUP BY s.CId
ORDER BY 平均分 desc

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：math(01),chinese(02),english(03)——student_id,student_name,math,chinese,english,avg_score;

一：select stu.SId,stu.Sname,
(SELECT score   from sc as s1 where CId = "02" and SId = stu.SId) as 数学,
(SELECT score from sc  as s2 where CId = "01" and SId = stu.SId) as 语文,
(SELECT score   from sc as s3 where CId = "03" and SId = stu.SId) as 英语,
avg((SELECT score   from sc as s1 where CId = "02" and SId = stu.SId)+
(SELECT score   from sc as s1 where CId = "01" and SId = stu.SId)+
(SELECT score   from sc as s1 where CId = "03" and SId = stu.SId))/3 as 平均成绩
from student stu 
GROUP BY stu.SId
ORDER BY 平均成绩 desc LIMIT 2,4



二：select stu.SId,stu.Sname,ms.score as "数学",ys.score as "语文",es.score as "英语" ,avg(ms.score+ys.score+es.score)/3 as "平均成绩" from 

(SELECT SId,score  from sc where CId = "02") as ms
INNER join (SELECT SId,score  from sc where CId = "01" )as ys on ms.SId = ys.SId
INNER join (SELECT SId,score  from sc where CId = "03")as es on ys.SId = es.SId
INNER join student stu on stu.SId = es.SId

GROUP BY stu.SId,stu.Sname,ms.score,ys.score,es.score
ORDER BY avg(ms.score+ys.score+es.score) DESC

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[-60]

--分组求和 sum case when then else end
SELECT cou.CId ,cou.Cname,
SUM(CASE when s.score>=85 then 1 ELSE 0 END) as "[100-85]" ,
SUM(CASE when s.score<85 and s.score >=70 then 1 ELSE 0 END) as "[85-70]" ,
SUM(CASE when s.score<70 and s.score>=60 then 1 ELSE 0 END) as "[70-60]" ,
SUM(CASe when s.score<60 then 1 ELSE 0 END) as "60以下"
from sc as s join course as cou on cou.CId = s.CId
GROUP BY cou.CId ,cou.Cname

24、查询学生平均成绩及其名次

select SId,平均成绩,
(SELECT COUNT(1) from 
(SELECT SId,AVG(score) as 平均成绩 from sc GROUP BY SId) 
as a where a.平均成绩>b.平均成绩)+1 as 排名 from 
(select SId,AVG(score) as 平均成绩 from sc GROUP BY SId) as b
GROUP BY SId
ORDER BY 平均成绩 DESC


SELECT 
(SELECT COUNT(1) from (SELECT AVG(score) as pjcj,SId from sc GROUP BY SId) as b where b.pjcj>a.平均成绩)+1 as 排名,
 姓名,
平均成绩
from 
(SELECT stu.Sname as 姓名, AVG(s.score) as 平均成绩 from sc as s join student as stu on s.SId = stu.SId GROUP BY stu.Sname)
as a
ORDER BY 排名

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

//有瑕疵
SELECT cou.Cname,b.score,b.SId,
(SELECT COUNT(1) from sc as c where c.score>b.score and c.CId = b.CId)+1 as 排名 
from 
sc as b join course as cou on b.CId = cou.CId where (select COUNT(1) from sc as a WHERE a.CId = b.CId and a.score>b.score order BY a.CId)<3
GROUP By cou.Cname,b.SId
ORDER BY cou.Cname,b.score desc


SELECT s.CId ,s.SId ,s.score from 
(SELECT CId,SId,score,
(SELECT COUNT(1) from sc as s2 WHERE s2.score>s1.score and s2.CId = s1.CId)+1 as c1 from sc as s1
 HAVING c1<4
)
as s 
GROUP BY s.CId ,s.SId
ORDER BY s.CId, s.score DESC

26、查询每门课程被选修的学生数

select cou.Cname,(SELECT count(*) from sc as a WHERE a.CId = b.CId),COUNT(b.SId)
from sc as b JOIN course as cou on b.CId = cou.Cid
GROUP BY cou.Cname

27、查询出只选修了2门课程的全部学生的学号和姓名

select stu.SId,stu.Sname from student as stu join (SELECT SId,COUNT(SId) from sc 
GROUP BY SId HAVING COUNT(SId) = 2) as a on stu.SId = a.SId

28、查询男生、女生人数

SELECT Ssex,count(Ssex) from student 
GROUP BY Ssex

29、查询姓“王”的学生名单

select * from student WHERE Sname LIKE "Wang%"

30、查询同名同性学生名单，并统计同名人数

select a.Sname,(SELECT COUNT(1) from student as b where a.Sname = b.Sname)as c from student as a
GROUP BY a.Sname
HAVING c>1

31、1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

select * from student where YEAR(Sage)=1990

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

SELECT s.CId,cou.Cname,AVG(s.score) from sc as s JOIN course as cou on s.CId = cou.CId
GROUP BY s.CId
ORDER BY AVG(s.score) DESC ,s.CId ASC

33、丢了知道的告诉我一下

34、查询课程名称为“MATH”，且分数低于60的学生姓名和分数

SELECT stu.Sname,s.score from sc as s join student as stu on s.SId = stu.SId
where s.score<60 and s.CId = (SELECT CId from course where Cname = "MATH")

35、查询所有学生的选课情况；

一 select stu.SId,stu.Sname,s.CId,cou.Cname from sc as s join student as stu on s.SId = stu.SId
join course as cou on cou.CId = s.CId
GROUP BY stu.SId,s.CId
ORDER BY s.SId asc


二.合并SELECT stu.SId,stu.Sname,GROUP_CONCAT(cou.Cname SEPARATOR '  , ') as 选课 from sc as s join student as stu ON s.SId = stu.SId
JOIN course as cou on cou.CId = s.CId
GROUP BY  stu.SId

36、查询每门课程成绩在70分以上的姓名、课程名称和分数；

SELECT stu.Sname,cou.Cname,s.score from student as stu join sc as s on stu.SId = s.SId JOIN course as cou on cou.CId = s.CId
where s.score>=70

37、查询不及格的课程，显示学号、姓名、课程号、成绩

SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SId
where s.score <60

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名；

SELECT stu.SId,stu.Sname from student as stu join sc as s on stu.SId = s.SId
where s.score >=80 and s.CId = 03

39、求选了课程的学生人数

去重
SELECT COUNT(DISTINCT SId) as 选课人数 from sc

40、查询选修“Li Pengfei”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select stu.Sname,s.score from student as stu 
join sc as s on stu.SId = s.SId
where s.score = (SELECT MAX(score) from sc as s join course as cou on cou.CId = s.CId  
join teacher as tea on tea.TId = cou.TId where tea.Tname = "Li Pengfei" )
and s.CId = (SELECT DISTINCT s.CId from sc as s join course as cou on cou.CId = s.CId  
join teacher as tea on tea.TId = cou.TId where tea.Tname = "Li Pengfei" )

41、查询各个课程及相应的选修人数

SELECT cou.Cname,count(1) from sc as s join course as cou on s.CId = cou.CId
GROUP BY cou.Cname

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

理解俩种意思：
一：不同课程，不同学生，成绩相同
SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SId
where s.score in (SELECT score from sc where SId!=s.SId)
ORDER BY  s.score DESC

二：不同课程，相同学生，成绩相同  （可以去重不用in）
SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SId
where s.score in (SELECT score from sc where SId=s.SId and CId!=s.CId)
ORDER BY  s.score DESC

43、查询每门课程成绩最好的前两名

SELECT s.CId,stu.Sname,s.score from student as stu join sc as s on stu.SId = s.SId
where (SELECT COUNT(1) FROM sc where CId = S.CId and score>s.score)<2
GROUP BY s.CId,stu.Sname
ORDER BY s.CId,s.score DESC

44、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT cou.Cname,cou.CId,count(1) from sc as s join course as cou on s.CId = cou.CId
GROUP BY cou.Cname HAVING COUNT(1) >= 5
ORDER BY  COUNT(1) desc,cou.CId ASC

45、检索至少选修3门课程的学生学号

select DISTINCT s.SId from sc as s where (SELECT COUNT(1) from sc where SId = s.SId)>=3

46、查询全部学生都选修的课程的课程号和课程名

select DISTINCT s.CId,cou.Cname from sc as s join course as cou on s.CId = cou.CId
where (SELECT COUNT(1) from sc where CId = s.CId) = (SELECT COUNT(1) from student )

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

SELECT DISTINCT stu.Sname from student as stu join sc as s on s.SId = stu.SId JOIN course as cou on cou.CId = s.CId
JOIN teacher as tea on tea.TId = cou.TId
where (SELECT cou.CId from teacher as tea join course as cou on tea.TId = cou.TId where tea.Tname = "Ye Ping") not in (select CId from sc where s.SId = SId)

48、查询两门以上不及格课程的同学的学号及其平均成绩这里用groupby去重，因为distinct去掉的是全部属性

SELECT s.SId,s.score from sc as s  where (SELECT COUNT(1) from sc where s.SId = SId and score<60)>2
GROUP BY s.SId

49、检索“04”课程分数小于60，按分数降序排列的同学学号

SELECT s.SId from sc as s  where s.score<60 and s.CId = 04
ORDER BY s.score DESC

50、删除“002”同学的“01”课程的成绩

DELETE from sc where SId = 02 and CId = 01

Turing·

关注

17
点赞
踩
99

收藏

觉得还不错? 一键收藏
3
评论
MySQL-50道经典sql题汇总

建表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;-- ------------------------------ Table structure fo.
复制链接

扫一扫