数据库专题 MySQL较全面的多种情况查询语句（45题） + 参考答案

最新推荐文章于 2021-08-04 13:28:04 发布

极光雨雨

最新推荐文章于 2021-08-04 13:28:04 发布

阅读量415

点赞数

分类专栏：数据库文章标签： sql mysql

本文链接：https://blog.csdn.net/weixin_44131922/article/details/108038409

版权

数据库专栏收录该内容

36 篇文章 0 订阅

订阅专栏

以前做的题，忘记题目保存到哪里了，如果找到了后期补上来，但是问题和自己写的SQL语句还在，可以参考是否有相近的问题来参考。如果能全部做出来的话SQL语句基本上就没有太大问题了。

这里多表查询大部分使用的都是隐式链接，习惯了以后感觉这个更好用一些

1，查询student表中的所有记录的sname，ssex和class列
select sname,ssex,class from students

2，查询教师所有的单位即不重复的depart列
select distinct depart from teachers

3，查询student表的所有记录
select * from students

4，查询score表中成绩在60到80之间的所有记录
select * from scores where degree BETWEEN 60 and 80

5，查询score表中成绩为85，86，88的记录
select * from scores where degree in (85,86,88)

6，查询student表中“95031”班或性别为女的同学记录
select * from student where class=95031 or sex='女'

7，以class降序查询student表中的所有记录
select * from students order by class DESC

8，以cno升序，degree降序查询score中的所有记录
select * from scores order by cno,degree DESC

9，查询“95031”班的学生人数
select class,COUNT(*) from students group by class having class=95031

10，查询score表中最高分的学生学号和课程号
select sno,cno from scores order by degree DESC limit 1

11，查询‘3-105’号课程的平均分
select cno,AVG(degree) from scores group by cno having cno='3-105'

12，查询score表中至少有5名学生选修的并以3开头的课程的平均分
select cno,COUNT(*) cn from scores GROUP BY cno having cn>=5

13，查询最低分大于70，最高分小于90的sno列
select sno from scores group by sno having MAX(degree)<=90 and MIN(degree>=70)

14，查询所有学生的sname，cno和degree列
select sname,cno,degree from scores sc,students st where sc.sno=st.sno

15，查询所有学生的sno，cname和degree列
select sno,cname,degree from scores,course where scores.cno=course.cno

16，查询所有学生的sname，cname和degree列
select sname,cname,degree from students,scores,course where students.sno=scores.sno and course.cno=scores.cno

17，查询‘95033’班所选课程的平均分
select class,b1.cno,b1.avgd from students,(select sno,cno,AVG(degree) avgd from scores group by cno) b1 where b1.sno=students.sno and class=95033

18，查询grade表中所有同学的sno，cno，和rank列
select sno,cno,degree,rank from grade,scores where scores.degree BETWEEN grade.low and grade.upp

19，查询选修‘3-105’课程的成绩高于“109”号同学成绩的所有同学的记录
select sno,cno,degree from scores where degree>(select degree from scores where sno=109 and cno='3-105') and cno="3-105"

20，查询scores中选学一门以上课程的同学中分数为非最高成绩的记录
select *,COUNT(*) keshu from scores group by sno having keshu>1 and degree!=max(degree)

21，查询成绩高于学号为“109”，课程号为“3-105”的成绩的所有记录
select * from scores where degree>(select degree from scores where sno=109 and cno="3-105")

22，查询和学号为108的同学同年出生的所有的学生的sno，sname，和sbirthday列
select sno,sname,sbirthday from students where (year(students.sbirthday))= (select (year(sbirthday)) from students where sno=108)

23，查询“张旭”教师任课的学生的成绩
select degree,s.sno from scores s,teachers t,course c where t.tno=c.tno and s.cno=c.cno

24，查询选修某课程的同学人数多于5人的教师姓名
select tname,teachers.tno,b1.cno,b1.renshu from teachers,course,(select cno,COUNT(cno)renshu from scores group by cno having renshu>5 ) b1 where b1.cno=course.cno and teachers.tno=course.tno

25，查询”95033“班和”95031“班全体学生的记录
select * from students,scores where students.sno=scores.sno order by students.sno DESC

26，查询存在有85分以上成绩的课程的cno
select DISTINCT cno from scores where degree>85

27，查询出“计算机系”教师所教课程的成绩表
SELECT c.tno,s.* from course c,scores s,(select tno,depart from teachers where depart="计算机系") jisuan where c.tno=jisuan.tno and s.cno=c.cno

28，查询“计算机系”与“电子工程系”不同职称的教师的Tname和prof
select tname,prof from teachers where prof not in (select DISTINCT prof from teachers where depart="电子工程系")

29，查询选修编号为“3-105”课程且成绩至少高于任意选修编号为“3-245”的同学的成绩的cno，sno和degree，并按degree从高到低次序排序
select cno,sno,degree  from scores where cno="3-105" and degree>(select MAX(DEGREE) from scores where cno="3-245") order by degree DESC

30，查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的所有同学的cno，sno和degree
select cno,sno,degree from scores where cno="3-105" and degree>(select MAX(degree)from scores where cno="3-245")

31，查询所有教师和同学的name，sex和birthday
select s.sname name,s.ssex sex,s.sbirthday birthday from students s UNION
select t.tname,t.tsex,t.tbirthday  from teachers t

32，查询所有女教师和女同学的的name，sex，birthday
select s.sname name, s.ssex sex,s.sbirthday birthday from students s where s.ssex="女" UNION
select t.tname,t.tsex,t.tbirthday from teachers t where t.tsex="女"

33，查询成绩比该课程平均分低的同学的成绩表
select s.sno,s.cno,s.degree from scores s,(select cno,AVG(DEGREE) avg1 from scores group by cno) b1 where b1.cno=s.cno and s.degree<b1.avg1;

34，查询所有任课教师的tname和depart
select tname,depart FROM teachers t,(select DISTINCT tno from course
) b1 where t.tno=b1.tno

35，查询所有未讲课的教师的Tname和depart
select tname,depart  from teachers s where s.tno not in(select DISTINCT tno from course)

36，查询至少有2名男生的班号
select class,COUNT(class) from students s where s.ssex="男" group by class having COUNT(class)

37，查询student表中不姓王的同学的记录
select * from students where sname not like "王%"

38，查询student表中每个学生的姓名和年龄
select sname,(2019-(YEAR(sbirthday)))  from students

39，查询student表中最大和最小的birthday日期值
select MAX(sbirthday),MIN(sbirthday) from students

40，以班号和年龄从大到小的顺序查询student表中的全部记录
select * from students order by class DESC,sbirthday

41，查询男教师及其所上的课程
select * from teachers t,course c where c.tno=t.tno

42，查询最高分同学的sno，cno，和degree列
select sno,cno,degree,MAX(degree) from scores group by cno

43，查询和李军同性别的所有同学的sname
select sname from students where ssex=(SELECT ssex from students where sname="李军")

44，查询和李军同性别并同班的同学的sname
select sname,class from students where
 class=(select class from students where sname="李军")
 and ssex=(select ssex from students where sname="李军")

45，查询所有选修计算机导论课程的男同学的成绩表
select scores.* from scores,(select sno,sname from students where ssex="男") b1,(select cno,cname from course where cname="计算机导论"
) b2 where scores.sno = b1.sno and scores.cno=b2.cno