前言
有表格如下:
student
score
course
teacher
查询平均成绩大于60分的同学的id和平均成绩;
mysql> SELECT student_id, AVG(number) AS number_avg
-> FROM score
-> GROUP BY student_id
-> HAVING AVG(number) > 60;
查询所有同学的id、姓名、选课的数量、总成绩;
mysql> SELECT student_id,
-> name,
-> COUNT(course_id) AS course_count,
-> SUM(number) AS number_sum
-> FROM student INNER JOIN score
-> ON student.id = score.student_id
-> GROUP BY student_id
-> ORDER BY student_id;
查询姓“李”的老师的个数;
mysql> SELECT COUNT(name) AS numbers
-> FROM teacher
-> WHERE name LIKE '李%';
查询没学过“李老师”课的同学的id、姓名;
mysql> SELECT student.id, student.name
-> FROM student
-> WHERE NOT (
-> student.id IN (
-> SELECT score.student_id
-> FROM score, course, teacher
-> WHERE score.course_id =course.id
-> AND course.teacher_id = teacher.id
-> AND teacher.name LIKE '李%'));
查询学过课程id为3和4的所有同学的id、姓名;
mysql> SELECT student.id, student.name
-> FROM student, score
-> WHERE student.id = score.student_id
-> AND score.course_id = 3
-> GROUP BY student.id
-> UNION ALL
-> SELECT student.id, student.name
-> FROM student, score
-> WHERE student.id = score.student_id
-> AND score.course_id = 4
-> GROUP BY student.id
-> HAVING COUNT(student.id) > 1;
查询两门以上不及格的同学的id、姓名、以及不及格课程数;
mysql> SELECT student.id, student.name, COUNT(CASE WHEN score.number < 60 THEN score.number ELSE NULL END) AS nums
-> FROM student, score
-> WHERE student.id = score.student_id
-> GROUP BY student.id
-> HAVING COUNT(CASE WHEN score.number < 60 THEN score.number ELSE NULL END) >=2;