数据库查询练习

今天练习数据库，尽量记录下数据库的万能模板。以下练习代码基于MySQL。

1. 设定表格

首先定义三个表，student，course，score。student表存储学生的名字及性别，course表存储课程名及任课老师名字，score表存储每个学生每个课程的成绩，每个表的设定如下：

• student表：

id	name	sex
1	van	boy
2	bill	girl
3	muji	girl

• course表：

id	name	teacher
1	zhexue	jill
2	wuli	fill
3	bug	gtt

• score表：

id	student_id	course_id	score
1	1	1	88
2	1	2	89
3	1	3	99
4	2	1	50
5	2	2	67
6	2	3	61
7	3	1	98
8	3	2	51
9	3	3	33

2.查询模板

（1）输出平均成绩最高且大于60分的2个学生的名字以及他们的成绩
这个相对来说比较简单，只需要合并，并且分组求平均值就完了，要注意的是分组后要加条件必须用HAVING语句。

SELECT student.`name`, AVG(score.score) AS "a_score" 
FROM student,course,score 
WHERE student.id=score.student_id AND course.id=score.course_id 
GROUP BY student.id
HAVING AVG(score.score) > 60 
ORDER BY AVG(score.score) DESC LIMIT 2

查询结果如下：

图1. 平均成绩最高并大于60分的2学生
（2）输出每个学生单科成绩最高的课程名，以及课程老师，学生名字
这个比较复杂，而且存在一个问题，就是GROUP BY NAME后不能获取到课程的名字。即

SELECT *, MAX(score.score) AS "max_score" 
FROM student,course,score 
WHERE student.id=score.student_id AND course.id=score.course_id 
GROUP BY score.student_id

图2. 分组后课程出错

这是由于用学生名字分组后课程名消失导致的出错，因此不能直接这么写，只能一步步来。可以分两步走。
<1> 取学生id和最高分。
从上面这图2看出来，这个是没有问题的。不过为了防止后面合并的时候名字重复，把学生的id改个名字叫s_id，然后最高分叫max_score。

SELECT score.student_id AS "s_id", MAX(score.score) AS "max_score" 
FROM student,course,score
WHERE student.id=score.student_id AND course.id=score.course_id 
GROUP BY score.student_id

图3. 获取学生id和最高分
<2> 合并课程表，成绩表，学生表和第一步的结果。
将第一步的结果作为集合n，并且与原来的三个表合并，即可得到最终结果。

SELECT student.`name`,student.sex,max_score,course.`name` AS c_name,course.teacher AS c_teacher 
FROM student,score,course,
(
    SELECT score.student_id AS "s_id", MAX(score.score) AS "max_score" 
    FROM student,course,score 
    WHERE student.id=score.student_id AND course.id=score.course_id 
    GROUP BY score.student_id
)n 
WHERE score.student_id = s_id AND score.course_id=course.id AND score.score = max_score AND student.id = s_id

图4. 最高分及课程名字等

未完待续~