mysql自己怎么练习_MySQL练习

把自做的经典SQL练习题答案记录下来，方便日后复习。

1、 查询Student表中的所有记录的Sname、Ssex和Class列。

SELECT sname, ssex, class FROM student;

2、 查询教师所有的单位即不重复的Depart列。

SELECT DISTINCT depart FROM teacher;

3、 查询Student表的所有记录。

SELECT * FROM student;

4、 查询Score表中成绩在60到80之间的所有记录。

SELECT * FROM score WHERE degree BETWEEN 60 AND 80;

5、 查询Score表中成绩为85，86或88的记录。

SELECT * FROM score WHERE degree IN (85, 86, 88);

6、 查询Student表中“95031”班或性别为“女”的同学记录。

SELECT * FROM student WHERE class = '95031' OR ssex = '女';

7、 以Class降序查询Student表的所有记录。

SELECT * FROM student ORDER BY class DESC;

8、 以Cno升序、Degree降序查询Score表的所有记录。

SELECT * FROM score ORDER BY cno, degree DESC;

9、 查询“95031”班的学生人数。

SELECT COUNT(sno) FROM student WHERE class = '95031';

10、查询Score表中的最高分的学生学号和课程号。

SELECT sno, cno FROM score HAVING degree = MAX(degree);

错了

SELECT sno, cno FROM score WHERE degree = MAX(degree);

错了

SELECT FIRST(sno), FIRST(cno) FROM score ORDER BY degree DESC;

错了

SELECT FIRST(sno, cno) FROM score ORDER BY degree DESC;

错了...看答案

法1：

SELECT sno, cno FROM score WHERE degree = (SELECT MAX(degree) FROM score);

法2：

SELECT sno, cno FROM score ORDER BY degree DESC LIMIT 1;

11、查询‘3-105’号课程的平均分。

SELECT AVG(degree) FROM score WHERE cno = '3-105';

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT AVG(degree) FROM score

WHERE cno LIKE '3%' AND

COUNT(sno) >= 5

GROUP BY cno;

错了 看答案

SELECT AVG(degree),cno

FROM score

WHERE cno LIKE '3%'

GROUP BY cno

HAVING COUNT(sno) >= 5;

*计数函数不能和WHERE一起用，要和HAVING一起

13、查询最低分大于70，最高分小于90的Sno列。

SELECT sno FROM score

WHERE MIN(degree) >70 AND MAX(degree) <90

GROUP BY sno;

错了

SELECT sno FROM score

GROUP BY sno

HAVING MIN(degree) >70 AND MAX(degree) <90;

对了

*找最大/小值的函数也要和HAVING一起

14、查询所有学生的Sname、Cno和Degree列。

SELECT a.sname, b.cno, b.degree

FROM student a JOIN score b

ON a.sno = b.sno;

15、查询所有学生的Sno、Cname和Degree列。

SELECT b.sno, a.cname, b.degree

FROM course a JOIN score b

ON a.cno = b.cno;

16、查询所有学生的Sname、Cname和Degree列。

SELECT a.sname, b.cname, c.degree

FROM student a JOIN (course b, score c)

ON a.sno = c.sno AND b.cno = c.cno;

17、查询“95033”班所选课程的平均分。

SELECT AVG(b.degree)

FROM student a JOIN score b

ON a.sno = b.sno

WHERE a.class = '95033';

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

SELECT * FROM score

WHERE cno = '3-105' AND

degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');

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

SELECT * FROM score

WHERE degree < (SELECT MAX(degree) FROM score)

GROUP BY sno

HAVING COUNT(sno) > 1

ORDER BY degree;

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

SELECT * FROM score

WHERE cno = '3-105' AND

degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');

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

SELECT sno, sname, sbirthday FROM student

WHERE YEAR(sbirthday) = (SELECT YEAR(sbirthday) FROM student WHERE sno = '108');

23、查询“张旭“教师任课的学生成绩。

SELECT b.degree

FROM course a JOIN (score b, teacher c)

ON a.tno = c.tno AND a.cno = b.cno

WHERE c.tname = '张旭';

24、查询选修某课程的同学人数多于5人的教师姓名。

SELECT c.tname

FROM course a JOIN (score b, teacher c)

ON a.cno = b.cno AND a.tno = c.tno

GROUP BY b.cno

HAVING COUNT(b.cno) > 5;

25、查询95033班和95031班全体学生的记录。

SELECT * FROM student WHERE class IN ('95033', '95031');

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

SELECT cno FROM score

GROUP BY cno

HAVING MAX(degree) > 85;

27、查询出“计算机系“教师所教课程的成绩表。

SELECT b.cno, b.degree

FROM course a JOIN (score b, teacher c)

ON a.cno = b.cno AND a.tno = c.tno

WHERE c.depart = '计算机系';

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

SELECT tname, prof FROM teacher

WHERE depart = '计算机系'

AND prof NOT IN (SELECT prof FROM teacher WHERE depart = '电子工程系');

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

SELECT * FROM score

WHERE cno = '3-105'

AND degree > (SELECT degree FROM score WHERE cno = '3-245')

ORDER BY degree DESC;

错了

SELECT * FROM score

WHERE cno = '3-105'

AND degree > (SELECT degree FROM score WHERE cno = '3-245')

ORDER BY degree DESC

GROUP BY sno;

错了 看答案

SELECT * FROM score

WHERE cno = '3-105' AND

degree > ANY(SELECT degree FROM score WHERE cno ='3-245')

ORDER BY degree DESC;

*ANY()函数

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree。

SELECT * FROM score

WHERE cno = '3-105' AND

degree > ALL(SELECT degree FROM score WHERE cno ='3-245')

ORDER BY degree DESC;

*ALL()函数

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

SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacher

UNION

SELECT sname AS name, ssex AS sex, sbirthday AS birthday FROM student;

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

SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacher

WHERE tsex = '女'

UNION

SELECT sname AS name, ssex AS sex, sbirthday AS birthday FROM student

WHERE ssex = '女';

33、查询成绩比该课程平均成绩低的同学的成绩表。

SELECT * FROM score

GROUP BY cno

HAVING degree < AVG(degree);

错了 看答案

SELECT a.* FROM score a

WHERE degree < (SELECT AVG(degree) FROM score b WHERE a.cno = b.cno);

34、查询所有任课教师的Tname和Depart。

SELECT b.tname, b.depart

FROM course a JOIN teacher b

ON a.tno = b.tno;

35 、查询所有未讲课的教师的Tname和Depart。

SELECT tname, depart FROM teacher

WHERE tname NOT IN (SELECT b.tname FROM course a JOIN teacher b ON a.tno = b.tno);

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

SELECT class FROM student

GROUP BY class

HAVING COUNT(ssex) >= 2;

37、查询Student表中不姓“王”的同学记录。

SELECT * FROM student

WHERE sname NOT LIKE '王%';

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

SELECT sname, (YEAR(NOW())-YEAR(sbirthday)) AS age FROM student;

39、查询Student表中最大和最小的Sbirthday日期值。

SELECT MAX(sbirthday), MIN(sbirthday) FROM student;

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

SELECT * FROM student ORDER BY class DESC, sbirthday;

41、查询“男”教师及其所上的课程。

SELECT b.tname, a.cname

FROM course a JOIN teacher b

ON a.tno = b.tno

WHERE b.tsex = '男';

42、查询最高分同学的Sno、Cno和Degree列。

SELECT * FROM score ORDER BY degree DESC LIMIT 1;

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

SELECT sname FROM student

WHERE ssex = (SELECT ssex FROM student WHERE sname = '李军');

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

SELECT sname FROM student

WHERE ssex = (SELECT ssex FROM student WHERE sname = '李军')

AND class = (SELECT class FROM student WHERE sname = '李军');

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

SELECT c.*

FROM student a JOIN (course b, score c)

ON a.sno = c.sno AND b.cno = c.cno

WHERE b.cname = '计算机导论' AND a.ssex = '男';