mysql测试题汇总_总结：SQL练习【SQL经典练习题】

好像今天圣诞节

刚刷完SQL练习【SQL经典练习题】，本篇文章将对我不牢固的知识做简单汇总。

没对比就没标准，当练习超经典SQL练习题，做完这些你的ＳＱＬ就过关了时才知道自己以前练习的SQL是最基础的内容。至于文章内容这里不做描述，感兴趣可以自己练习。

#20.查询scores中选学一门以上课程的同学中分数为非最高分成绩的记录。##

select * from scores group by sno

having count(cno) >1

and

degree != max(degree) ;

#21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from scores

group by cno

having cno = 3105 and degree >

(select degree from scores where sno = 109 and cno = 3105);

#22.查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

select sno,sname,sbirthday from students

where sbirthday =

(select sbirthday from students where sno = 108);

#24.查询选修某课程的同学人数多于5人的教师姓名。为什么是模棱两可的？为什么分组直接用cno不行，需要用a.cno或者b.cn

select * from

(select * from coursers) a

inner join

(select * from scores) b

on a.cno = b.cno

inner join

(select * from teachers) c

on a.tno = c.tno

group by a.cno

having count(sno) > 5;

24给我的教训在表连接的时候在做条件选择和查询的时候指定列名出自哪张表

#26.查询存在有85分以上成绩的课程Cno.

我的代码：

select cno,degree from scores

group by cno

having max(degree) > 85;

题主代码：

SELECT distinct Cno

FROM Scores

WHERE Degree>85;

26绝对是自己的逻辑问题，为什么要按课程分组直接筛选大于85分成绩的同学的课程号就可以啦。看见distinct可能是天真的我想用group by 选择有几个cno吧

逻辑问题！

#28.查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

(我没有理解到题意……笨死……)

select tname,prof from teachers

where depart = '计算机系' and 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 = 3105 and degree >

(select min(degree) from scores where cno = 3245)

order by degree desc;

题主代码：

SELECT Cno,Sno,Degree

FROM Scores

WHERE Cno='3-105' AND Degree > ANY(SELECT Degree

FROM Scores

WHERE Cno='3-245')

ORDER BY Degree DESC;

29任意不能用最小值吗不能用min吗？第一次见any,方法自己还是会的，可能有些函数用的少

#31.查询所有教师和同学的name、sex和birthday.

我的代码：(感觉好可爱的自己)

select tname,tsex,tbirthday,sname,ssex,sbirthday from

(select * from teachers) a

inner join

(select * from coursers) b

on a.tno = b.tno

inner join

(select * from scores) c

on c.cno = b.cno

inner join

(select * from students) d

on d.sno = c.sno;

题主代码：

select sname,ssex,sbirthday from students

union

select tname,tsex,tbirthday from teachers;

#32.查询所有“女”教师和“女”同学的name、sex和birthday.

select sname,ssex,sbirthday from students where ssex = '女'

union

select tname,tsex,tbirthday from teachers where tsex = '女';

31原谅我已经把union已经忘了，现在还不是很会用……

#33.查询成绩比该课程平均成绩低的同学的成绩表。???不明白不懂

题主代码：

SELECT s1.*

FROM Scores AS s1 INNER JOIN (

SELECT Cno,AVG(Degree) AS aDegree

FROM Scores

GROUP BY Cno) s2

ON(s1.Cno=s2.Cno AND s1.Degree

33下来在看一下吧，明天上班路上复习。第一次见on可以连接多个条件，并且不是等号是小于符号

#34.查询所有任课教师的Tname和Depart.?????

题主代码：

select * from coursers;

SELECT Tname,Depart

FROM Teachers

WHERE Tno IN(

SELECT Tno

FROM Coursers);

#35.查询所有未讲课的教师的Tname和Depart.

SELECT Tname,Depart

FROM Teachers

WHERE Tno NOT IN(SELECT Tno FROM Coursers);

34绝对是没明白意思，任课老师，老师还有不上课的吗？这里居然是让我筛选哪些老师上课(PS：表中有些老师不上课……)

吐槽……简直颠覆本宝宝的常识，有不上课的老师吗？

#36.查询至少有2名男生的班号。

我的代码：

SELECT Class,COUNT(1) AS boyCount

FROM Students

WHERE Ssex='男'

GROUP BY Class

HAVING boyCount>=2;

题主代码：

select class,ssex,count(1) as boycount from students where

ssex = '男' group by class

having count(ssex) >= 2;

36用count的位置不同吧，第一次见count(1),居然不对字段进行操作的，是否有count(2)，下来去操作下

#38.查询Student表中每个学生的姓名和年龄。

我的代码：

select curtime(); #现在的时间

select curdate(); #现在的日期

select sname,year(now()) - year(Sbirthday) as sage

from students;

题主代码：

SELECT Sname,YEAR(NOW())-YEAR(Sbirthday) AS Sage

FROM Students;

38完全是时间的基础知识掌握不牢固

#43.查询和“李军”同性别的所有同学的Sname.

我的代码：

select sname from students

where ssex =

(select ssex from students where sname = '李军');

题主代码：

SELECT s1.Sname

FROM Students AS s1 INNER JOIN Students AS s2

ON(s1.Ssex=s2.Ssex)

WHERE s2.Sname='李军';

#44.查询和“李军”同性别并同班的同学Sname.

我的代码：

select * from students

where ssex =

(select ssex from students where sname = '李军')

and class =

(select class from students where sname = '李军');

题主代码：

SELECT s1.Sname

FROM Students AS s1 INNER JOIN Students AS s2

ON(s1.Ssex=s2.Ssex AND s1.Class=s2.Class)

WHERE s2.Sname='李军';

#45.查询所有选修“计算机导论”课程的“男”同学的成绩表

我的代码：

(可爱的我，这么勤劳联结这么多张表……不知疲劳)

select sname,degree,cname,ssex from

(select * from coursers) a

inner join

(select * from scores) b

on a.cno = b.cno

inner join

(select * from students) c

on b.sno = c.sno

where cname = '计算机导论' and ssex = '男';

题主代码：

SELECT * FROM Scores

WHERE Sno IN

(SELECT Sno FROM Students WHERE Ssex='男')

AND Cno IN (SELECT Cno FROM Coursers WHERE Cname='计算机导论');

43、44、45是每任何问题的，只是感觉题主的代码和我的不一样。不知道是不是我的方法有问题。

好啦，之前练习的SQL练习【SQL经典练习题】就总结到这里了……

20181225学习时间

7:00——7:30

6:00——7:10

9:30——12:30

仰卧起坐100，1点睡觉，6点20起床，6点50开始学习