25 SQL练习题大全——MySQL必备测试！（mysqlsql练习题）

25 SQL练习题大全——MySQL必备测试！

如果你是一个MySQL数据库的初学者或者想要进一步提升自己的SQL水平，那么这份25 SQL练习题大全必将是你的必备测试！在这份练习题中涵盖了从基础语句到高级用法的各种题目，旨在帮助你深入理解SQL查询语言，掌握其使用技巧和应用方法。

以下是其中的部分SQL练习题，希望对你的SQL学习有所帮助。

1. 查询所有学生的姓名和性别

SELECT name, gender FROM students;

2. 查询所有学生的学号、姓名和专业

SELECT id, name, major FROM students;

3. 查询所有年龄大于18岁的学生信息

SELECT * FROM students WHERE age > 18;

4. 查询所有专业为“计算机科学”且成绩大于等于80的学生信息

SELECT * FROM students WHERE major = '计算机科学' AND score >= 80;

5. 查询所有专业不是“计算机科学”且成绩大于等于80的学生信息

SELECT * FROM students WHERE major != '计算机科学' AND score >= 80;

6. 查询所有专业为“计算机科学”或成绩大于等于80的学生信息

SELECT * FROM students WHERE major = '计算机科学' OR score >= 80;

7. 查询所有成绩在90分以上的学生的姓名、成绩和所在班级

SELECT students.name, students.score, classes.name FROM students INNER JOIN classes ON students.class_id = classes.id WHERE students.score > 90;

8. 查询所有有成绩的学生信息（即score不为空）

SELECT * FROM students WHERE score IS NOT NULL;

9. 查询所有年龄在18岁到21岁之间的学生信息

SELECT * FROM students WHERE age BETWEEN 18 AND 21;

10. 查询所有学生的姓名、班级名称和班级所在地

SELECT students.name, classes.name, classes.location FROM students INNER JOIN classes ON students.class_id = classes.id;

11. 查询每个班级的平均成绩和人数

SELECT classes.name, COUNT(students.id) AS num_of_students, AVG(students.score) AS avg_score FROM classes LEFT JOIN students ON classes.id = students.class_id GROUP BY classes.name;

12. 查询每个班级平均成绩大于80分的学生的姓名和成绩

SELECT students.name, students.score FROM students INNER JOIN (SELECT class_id, AVG(score) AS avg_score FROM students GROUP BY class_id HAVING AVG(score) > 80) AS t ON students.class_id = t.class_id AND students.score >= t.avg_score;

13. 查询每个班级成绩排名第一的学生的姓名和成绩

SELECT name, score FROM (SELECT students.*, IF(@last_class=students.class_id,@rank:=@rank+1,@rank:=1) AS rank, @last_class:=students.class_id FROM students, (SELECT @rank:=0, @last_class:=NULL) AS t ORDER BY class_id, score DESC) AS result WHERE rank = 1;

14. 查询每个学生的选课情况和成绩

SELECT students.name, courses.name, scores.score FROM students INNER JOIN scores ON students.id = scores.student_id INNER JOIN courses ON scores.course_id = courses.id;

15. 查询所有选修了“高等数学”的学生信息

SELECT * FROM students WHERE id IN (SELECT student_id FROM scores WHERE course_id = (SELECT id FROM courses WHERE name = '高等数学'));

16. 查询所有没有选修任何课程的学生信息

SELECT * FROM students WHERE id NOT IN (SELECT DISTINCT student_id FROM scores);

17. 查询每个学生的平均成绩和总成绩

SELECT students.name, SUM(scores.score) AS total_score, AVG(scores.score) AS avg_score FROM students LEFT JOIN scores ON students.id = scores.student_id GROUP BY students.id;

18. 查询所有选修了“数据结构”或“计算机网络”课程的学生信息

SELECT * FROM students WHERE id IN (SELECT student_id FROM scores WHERE course_id IN (SELECT id FROM courses WHERE name IN ('数据结构', '计算机网络')));

19. 查询选修了至少两门课程的学生信息

SELECT * FROM students WHERE id IN (SELECT student_id FROM scores GROUP BY student_id HAVING COUNT(DISTINCT course_id) >= 2);

20. 查询所有成绩同时有“语文”和“数学”两门课程成绩的学生信息

SELECT * FROM students WHERE id IN (SELECT student_id FROM scores WHERE course_id = (SELECT id FROM courses WHERE name = '语文') AND score >= 60) AND id IN (SELECT student_id FROM scores WHERE course_id = (SELECT id FROM courses WHERE name = '数学') AND score >= 60);

21. 查询选修课程人数超过10人的课程信息

SELECT courses.name, COUNT(DISTINCT student_id) AS num_of_students FROM courses INNER JOIN scores ON courses.id = scores.course_id GROUP BY courses.name HAVING COUNT(DISTINCT student_id) > 10;

22. 查询选修课程总成绩排名前5的课程名称和总成绩

SELECT courses.name, SUM(scores.score) AS total_score FROM courses INNER JOIN scores ON courses.id = scores.course_id GROUP BY courses.id ORDER BY total_score DESC LIMIT 5;

23. 查询选修课程总成绩排名前5的学生姓名和总成绩

SELECT students.name, SUM(scores.score) AS total_score FROM students INNER JOIN scores ON students.id = scores.student_id GROUP BY students.id ORDER BY total_score DESC LIMIT 5;

24. 查询选修课程总成绩排名倒数第3的学生姓名和总成绩

SELECT name, total_score FROM (SELECT students.*, IF(@rank IS NULL OR @last_scoretotal_score,@rank:=@rank+1,@rank:=@rank) AS rank, @last_score:=total_score FROM students INNER JOIN (SELECT student_id, SUM(score) AS total_score FROM scores GROUP BY student_id ORDER BY total_score DESC) AS t ON students.id = t.student_id, (SELECT @rank:=0, @last_score:=NULL) AS init ORDER BY total_score DESC) AS result WHERE rank = 3;

25. 查询选修课程人数最多的教师名字和所教授的课程数量

SELECT teachers.name, COUNT(DISTINCT scores.course_id) AS num_of_courses FROM teachers INNER JOIN classes ON teachers.id = classes.teacher_id INNER JOIN students ON classes.id = students.class_id INNER JOIN scores ON students.id = scores.student_id GROUP BY teachers.id ORDER BY num_of_courses DESC LIMIT 1;