今天練習數(shù)據(jù)庫，盡量記錄下數(shù)據(jù)庫的萬能模板。以下練習代碼基于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. 分組后課程出錯
這是由于用學生名字分組后課程名消失導致的出錯，因此不能直接這么寫，只能一步步來?？梢苑謨刹阶摺?br>
<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. 最高分及課程名字等
未完待續(xù)~
