最全50个Mysql数据库查询练习题
1. 查询所有学生的信息
SELECT * FROM students;
2. 查询学生名为“张三”的信息
SELECT * FROM students WHERE name = '张三';
3. 查询学生性别为“女”的信息
SELECT * FROM students WHERE gender = '女';
4. 查询年龄最大的学生的信息
SELECT * FROM students ORDER BY age DESC LIMIT 1;
5. 查询学生的平均年龄
SELECT AVG(age) FROM students;
6. 查询学生的总人数
SELECT COUNT(*) FROM students;
7. 查询学生的总人数和平均年龄
SELECT COUNT(*), AVG(age) FROM students;
8. 查询所有老师的信息
SELECT * FROM teachers;
9. 查询老师名为“李四”的信息
SELECT * FROM teachers WHERE name = '李四';
10. 查询老师教授课程为“数学”的信息
SELECT * FROM teachers WHERE course = '数学';
11. 查询教授课程为“数学”的老师名单
SELECT name FROM teachers WHERE course = '数学';
12. 查询学生和老师的总人数
SELECT (SELECT COUNT(*) FROM students) + (SELECT COUNT(*) FROM teachers) AS total_number;
13. 查询学生和老师的平均年龄
SELECT (SELECT AVG(age) FROM students) + (SELECT AVG(age) FROM teachers) AS average_age;
14. 查询学生选课情况
SELECT students.name, courses.course_name FROM students JOIN course_student ON students.id=course_student.student_id JOIN courses ON courses.id=course_student.course_id;
15. 查询老师任教学生情况
SELECT teachers.name, students.name as student_name, courses.course_name FROM teachers JOIN courses ON teachers.id=courses.teacher_id JOIN course_student ON courses.id=course_student.course_id JOIN students ON students.id=course_student.student_id;
16. 查询学生选课数量和课程数量
SELECT COUNT(DISTINCT course_student.student_id) AS students_number, COUNT(DISTINCT course_student.course_id) AS courses_number FROM course_student;
17. 查询学生选课数量和每门课的选课人数
SELECT courses.course_name, COUNT(course_student.student_id) AS student_number FROM courses JOIN course_student ON courses.id=course_student.course_id GROUP BY courses.course_name;
18. 查询学生选课数量最多的前5门课程
SELECT courses.course_name, COUNT(course_student.student_id) AS student_number FROM courses JOIN course_student ON courses.id=course_student.course_id GROUP BY courses.course_name ORDER BY student_number DESC LIMIT 5;
19. 查询每个老师任课的课程数量
SELECT teachers.name, COUNT(courses.id) AS courses_number FROM teachers JOIN courses ON teachers.id=courses.teacher_id GROUP BY teachers.name;
20. 查询每个老师教授课程的平均学生人数
SELECT teachers.name, COUNT(course_student.student_id)/COUNT(courses.id) AS average_students_number FROM teachers JOIN courses ON teachers.id=courses.teacher_id JOIN course_student ON courses.id=course_student.course_id GROUP BY teachers.name;
21. 查询选修了2门及以上课程的学生名单
SELECT students.name FROM students JOIN course_student ON students.id=course_student.student_id GROUP BY students.id HAVING COUNT(course_student.course_id)>=2;
22. 查询没有选修课程的学生名单
SELECT students.name FROM students WHERE students.id NOT IN (SELECT student_id FROM course_student);
23. 查询选修了课程的学生数量
SELECT COUNT(DISTINCT course_student.student_id) AS students_number FROM course_student;
24. 查询选修“数学”课程的学生名单
SELECT students.name FROM students JOIN course_student ON students.id=course_student.student_id JOIN courses ON courses.id=course_student.course_id WHERE courses.course_name = '数学';
25. 查询每个学生选修的课程数
SELECT students.name, COUNT(course_student.course_id) AS courses_number FROM students JOIN course_student ON students.id=course_student.student_id GROUP BY students.name;
26. 查询每个老师教授的所有学生名单
SELECT teachers.name, students.name FROM teachers JOIN courses ON teachers.id=courses.teacher_id JOIN course_student ON course_student.course_id=courses.id JOIN students ON course_student.student_id=students.id;
27. 查询选修了“数学”课程但没有选修“英语”课程的学生名单
SELECT students.name FROM students JOIN course_student ON students.id=course_student.student_id JOIN courses ON courses.id=course_student.course_id WHERE courses.course_name = '数学' AND students.id NOT IN (SELECT student_id FROM course_student JOIN courses ON courses.id=course_student.course_id WHERE courses.course_name='英语');
28. 查询没有选修“数学”课程但选修了“英语”课程的学生名单
SELECT students.name FROM students JOIN course_student ON students.id=course_student.student_id JOIN courses ON courses.id=course_student.course_id WHERE courses.course_name = '英语' AND students.id NOT IN (SELECT student_id FROM course_student JOIN courses ON courses.id=course_student.course_id WHERE courses.course_name='数学');
29. 查询选修了所有课程的学生名单
SELECT students.name FROM students JOIN course_student ON students.id=course_student.student_id GROUP BY students.name HAVING COUNT(DISTINCT course_student.course_id)=(SELECT COUNT(*) FROM courses);
30. 查询没有选修所有课程的学生名单
SELECT students.name FROM students WHERE students.id NOT IN (SELECT student_id FROM course_student GROUP BY student_id HAVING COUNT(DISTINCT course_student.course_id)=(SELECT COUNT(*) FROM courses)));
31. 查询选修课程最多的学生名单
SELECT students.name FROM students JOIN course_student ON students.id=course_student.student_id GROUP BY students.name ORDER BY COUNT(DISTINCT course_student.course_id) DESC LIMIT 1;
32. 查询选修次数最多的课程名单
SELECT courses.course_name FROM courses JOIN course_student ON courses.id=course_student.course_id GROUP BY courses.course_name ORDER BY COUNT(course_student.student_id) DESC LIMIT 1;
33. 查询选修课程最多的老师名单
SELECT teachers.name FROM teachers JOIN courses ON teachers.id=courses.teacher_id JOIN course_student ON courses.id=course_student.course_id GROUP BY teachers.name ORDER BY COUNT(DISTINCT course_student.student_id) DESC LIMIT 1;
34. 查询年龄大于20岁的学生名单
SELECT name FROM students WHERE age > 20;
35. 查询年龄在20岁到25岁之间的学生名单
SELECT name FROM students WHERE age BETWEEN 20 AND 25;
36. 查询年龄不在20岁到25岁之间的学生名单
SELECT name FROM students WHERE age NOT BETWEEN 20 AND 25;
37. 查询姓某个字符的学生名单
SELECT * FROM students WHERE name LIKE '某%';
38. 查询姓某个字符且年龄大于20岁的学生名单
SELECT * FROM students WHERE name LIKE '某%' AND age > 20;
39. 查询名字中包含某个字符的学生名单
SELECT * FROM students WHERE name LIKE '%某%';
40. 查询生日在某个日期之前的学生名单
SELECT * FROM students WHERE birthday < '2000-01-01';
41. 查询生日在某个日期之后的学生名单
SELECT * FROM students WHERE birthday > '2000-01-01';
42. 查询生日在某个日期范围内的学生名单
SELECT * FROM students WHERE birthday BETWEEN '2000-01-01' AND '2005-01-01';
43. 查询某个城市中所有学生的平均年龄
SELECT AVG(age) FROM students WHERE city = '某个城市';
44. 查询某个城市中所有学生的总人数
SELECT COUNT(*) FROM students WHERE city = '某个城市';
45. 查询某个城市中姓某个字符的学生名单
SELECT * FROM students WHERE city = '某个城市' AND name LIKE '某%';
46. 查询某个城市中年龄最大的学生的信息
SELECT * FROM students WHERE city = '某个城市' ORDER BY age DESC LIMIT 1;
47. 查询某个城市中年龄最小的学生的信息
SELECT * FROM students WHERE city = '某个城市' ORDER BY age LIMIT 1;
48. 查询某个城市中所有男生的平均年龄
SELECT AVG(age) FROM students WHERE city = '某个城市' AND gender = '男';
49. 查询某个城市中所有女生的平均年龄
SELECT AVG(age