常见的50道mysql题目

2022-09-26 07:58:40

首先创建学生表Student

create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
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' , '女')

创建成绩表 SC

create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
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)

　　创建课程表Course

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03')

　　创建教师表Teacher

create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五')

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

#第一步:查询" 01 "课程比" 02 "课程成绩高（对sc处理）
#第二步:将sc表与stduent表连接
SELECT s.Sid ,s.Sname,s.Ssex, a.score,b.score
From student  s ,(select Sid, score from sc where Cid=01 )  a,(select Sid, score from sc where Cid=02) b
WHERE    a.Sid=b.Sid and a.score>b.score   and s.Sid=a.Sid

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

#第一步:查出课程同时存在01 ，02 的情况
#第二步:连接表
SELECT s.Sid,  s.Sage, s.Ssex
FROM  student  s,(SELECT DISTINCT sc.Sid 
FROM sc
WHERE sc.Cid in('01','02')) c
WHERE s.Sid=c.Sid

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

SELECT DISTINCT sc.Sid
FROM sc
WHERE sc.Cid ='01' and sc.Sid not in(SELECT Sid FROM sc WHERE Cid='02' ) #排除含有cid=02的sid

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

SELECT DISTINCT sc.Sid   
FROM sc
WHERE sc.Cid ='02' and sc.Sid not in(SELECT Sid FROM sc WHERE Cid='01' )

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

#第一步:更加sc表查询每位同学的平均成绩，和Sid 条件为》60 以同学分组
#第二步:连接表
SELECT s.Sid, s.Sname ,c.avgscore
From student  s,(SELECT Sid,AVG(score) avgscore
From sc  c
GROUP BY Sid)  c
WHERE s.Sid=c.Sid
HAVING AVG(score)>=60

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

SELECT s.Sid ,s.Sage,s.Ssex,c.score
From student s  INNER  JOIN sc c  ON s.Sid=c.Sid

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

#第一步：查询学生选课总数和所有课程的总成绩 用SId分组
#第二步：左外连接表
SELECT s.Sid ,s.Sname,c.scores,c.Cids
FROM student  s LEFT JOIN 
(SELECT  Sid ,SUM(score)  scores ,COUNT(Cid) Cids
FROM sc  c
GROUP BY  Sid    )  c ON   s.Sid =c.Sid

5. 查询姓「李」学生的数量

SELECT COUNT(Sid)  姓李的人数
FROM student
WHERE Sname  LIKE "李%"

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

SELECT  *
FROM student 
WHERE Sid  IN
(
SELECT Sid
FROM  sc
WHERE Cid=
(
SELECT  Cid
FROM course
WHERE  Tid=
(
SELECT Tid
FROM teacher
WHERE Tname='张三')
)
)

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

SELECT s.Sid ,s.Sage
FROM  student s INNER JOIN
(SELECT DISTINCT Sid ,COUNT(Cid)  Cids
FROM sc 
GROUP BY Sid
HAVING COUNT(Cid)>2
)  c ON s.Sid=c.Sid

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

//第一步:查询01同学的课程信息
//第二步:找出与他至少有一门课程相同的Sid
//第三步:连接student

SELECT  s.Sid,s.Sname,Sage,Ssex
FROM student s INNER JOIN (SELECT Sid 
FROM sc
WHERE Cid IN(SELECT Cid  FROM sc  WHERE Sid ="01"  )  AND Sid<>"01"
GROUP BY Sid)  c
ON s.Sid=c.Sid

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 //同种类型,可以考虑用Count函数

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 //与6相同类型，四表连查

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

SELECT  s.Sid,s.Sname,Sage,Ssex
FROM student s INNER JOIN (
SELECT Sid
FROM sc
WHERE score>=60
GROUP BY Sid
HAVING COUNT(Sid)>=2)  c ON s.Sid=c.Sid

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

SELECT s.Sid,s.Sname,Sage,Ssex
FROM student s,(SELECT Sid
FROM sc
WHERE  Cid='01'and score >60
ORDER BY Sid DESC )   c
WHERE s.Sid=c.Sid

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

#答案有点问题，仅供参考

SELECT s.Sid , AVG(s.score) avgscore ,s1.score, s2.score,s3.score
FROM sc s,
(SELECT Sid, score
FROM sc
WHERE Cid='01') s1,
(SELECT Sid ,score
FROM sc
WHERE Cid='02'
) s2,
(SELECT Sid ,score
FROM sc
WHERE Cid='03') s3
WHERE s.Sid=s1.Sid and s.Sid=s2.Sid and s.Sid =s3.Sid
GROUP BY s.Sid
Order BY avgscore.

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

SELECT Cid, MAX(score) ,AVG(score),MIN(score)
FROM  sc
GROUP BY  Cid

15. 按各科成绩进行排序

SELECT  Cid,score,Sid
FROM  sc
GROUP BY Cid,Sid
ORDER BY Cid,score

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

select c.cid as 课程编号, c.cname as 课程名称, A.*
from course as c,
(select cid,
    sum(case when score >= 85 then 1 else 0 end)/count(*) as 100_85,
    sum(case when score >= 70 and score < 85 then 1 else 0 end)/count(*) as 85_70,
    sum(case when score >= 60 and score < 70 then 1 else 0 end)/count(*) as 70_60,
    sum(case when score < 60 then 1 else 0 end)/count(*) as 60_0
from sc group by cid) as A
where c.cid = A.cid

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

SELECT
	a.Sid,
	a.Cid,
	a.score 
FROM
	sc a
	LEFT JOIN sc b ON a.cid = b.Cid 
	AND a.score < b.score
GROUP BY
	a.Sid,

	a.Cid,
	a.score 
HAVING
	COUNT(b.Cid ) < 3
ORDER BY
	a.Cid,
	a.score DESC
//不包括合并

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

SELECT COUNT(Sid) 学生数,Cid 
FROM sc
GROUP BY Cid

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

SELECT  s.Sid,s.Sname,s.Sage,s.Ssex
FROM student s INNER JOIN
(
SELECT Sid ,COUNT(Cid) 选课数
FROM sc 
GROUP BY Sid	
HAVING COUNT(Cid)=2)  c ON s.Sid=c.Sid

21. 查询男生、女生人数

SELECT  COUNT(Ssex)
FROM student
WHERE Ssex='男'

SELECT  COUNT(Ssex)
FROM student
WHERE Ssex='女生'

22. 查询名字中含有「风」字的学生信息 //与上面类型相同

23. 查询同名同性学生名单，并统计同名人数 //与上面类型相同

24. 查询 1990 年出生的学生名单 //与上面类型相同

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

SELECT Cid,AVG(score)
FROM sc
GROUP BY Cid
ORDER BY AVG(score) DESC,Cid  ASC

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

SELECT  s.Sid,s.Sname,Sage,Ssex,c.scores
FROM student s INNER JOIN (SELECT Sid,AVG(score) scores
FROM sc
GROUP BY Sid
HAVING scores>85
)  c ON s.Sid=c.Sid

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

SELECT  Sid,Sname,Sage,Ssex
FROM student 
WHERE Sid  IN
(
SELECT Sid 
FROM sc
WHERE score>60 and  Cid=
(
SELECT Cid
FROM course 
WHERE Cname='数学')
)

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） //以上类型相同

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 //sc 内连接

30. 查询不及格的课程 //以上含有类型

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名 //student 和sc 连表查询

32. 求每门课程的学生人数 //以上类型相同

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

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

SELECT  s.Sid,s.Sname,Sage,Ssex
FROM student s INNER JOIN (
SELECT Sid, Cid ,score
FROM sc
WHERE Cid=
(
SELECT Cid
FROM course
WHERE   Tid=(
SELECT Tid
FROM teacher
WHERE Tname="张三")
)
HAVING MAX(score)) c ON s.Sid=c.Sid

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

SELECT  d.Sid,d.Sname,d.Sage,d.Ssex,e.Cid,e.cid1
FROM student d INNER JOIN
(
SELECT s.Sid,s.Cid,c.Cid  cid1
FROM sc s  INNER JOIN
(SELECT Sid, Cid, score
 FROM  sc  
      ) c  ON  c.Sid=s.Sid  and s.Cid<>c.Cid
) e ON d.Sid=e.Sid

36. 查询每门功成绩最好的前两名 //与上面类型相同

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）//相同类型

38. 检索至少选修两门课程的学生学号 //以上类型相同

39. 查询选修了全部课程的学生信息 //类型相同用count函数

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

select * from student GROUP BY sage

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

select sname, year(now())-year(sage) as age from student

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

select * from student where week(now()) = week(sage)

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

select * from student where (week(now())+1) = week(sage)

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

select * from student where (month(now())) = month(sage)

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

select * from student where (month(now())+1) = month(sage)

本文只供学习使用,未用于商业
原文链接：https://blog.csdn.net/flycat296/java/article/details/63681089

码农公寓

相关文章