查询平均成绩大于60分的同学的学号和姓名和平均成绩：
SELECT
B.student_id,
student.sname,
B.ccc
from
(select studuent_id,avg(num) as ccc from score GROUP BY student_id HAVING avg(num)>60 ) as B
left join
student on B.student_id = student.sid; # 聚合函数要先as然后在外面调用
查询所有同学的学号、姓名、选课数、总成绩：
SELECT
score.student_id,
student.sname,
count(student_id),
sun(num)
from score LEFT JOIN student on score.student_id = student.sid
GROUP BY score.student_id
查询没选过“李平老师”课的同学学号、姓名：
SELECT
student.sid,
student.sname
FROM
students
WHERE
sid NOT IN (
SELECT
student_id
FROM
score
WHERE
course_id IN
SELECT course.cid FROM course LEFT JOIN teacher ON course.cid = teacher.tid
WHERE teacher.tname = "李平老师"
)
GROUP BY
student_id
)
查询生物课程比物理课程成绩高的所有学生的学号：
select A.student_id from
(select score.sid,score.student_id,course.name,score.num from score LEFT JOIN course on score.course_id = course.cid where course.cname = "生物") as A
INNER JOIN
(select score.sid,score.student_id,course.name,score.num from score LEFT JOIN course on score.course_id = course.cid where course.cname = "物理") as B
on A.student_id = B.student_id
where A.num > B.num
查询学过编号1和2的课程的同学学号、姓名：
select score.student, student.sname from score
left join student on student_id = student.sid
where course_id = 1 or course_id = 2 GROUP BY student_id having count(course_id)>1
查询有分数低于60的同学姓名学号：
select sid,sname from student where sid in (
select DISTINCT student_id from score where num<60
)
查询没有学全所有课的同学的学号、姓名：
select student_id,count(1) from score GROUP BY student_id HAVING count(1) < (select count(1) from course); # count一般用1或者主键，效率高
按各科平均成绩从低到高和及格率的百分数从高到低排序：
select course_id,avg(num), sum(case when num<60 THEN 0 ELSE 1 END) / sum(1) as jgl from score GROUP BY course_id order by avg(num) asc, jgl desc;
avg(if(isnull(score.num),0,score.num)) # 三元运算，防止有空值影响计算平均值
http://www.cnblogs.com/wupeiqi/p/5748496.html
