sql习题：http://www.cnblogs.com/wupeiqi/articles/5729934.html
习题答案参考：https://www.cnblogs.com/wupeiqi/articles/5748496.html （有些答案有错）
-- SELECT count(*) from score WHERE num>60; -- 查找分数大于60的个数 -- select count(cid),teacher_id from course group by teacher_id; -- select tid,teacher.tname,course.cname from course left join teacher on course.teacher_id = teacher.tid; -- select count(sid),gender from student GROUP BY gender -- 男女生个数 -- 2、查询“生物”课程比“物理”课程成绩高的所有学生的学号； -- 找出生物成绩，找出物理成绩，联合这两张临时表，找出B.num > P.num -- select B.student_id,B.cname,B.num as b_num,P.cname,P.num as p_num from -- (select * from score left join course on score.course_id = course.cid where cname = '生物') as B -- inner join -- (select * from score left join course on score.course_id = course.cid where cname = '物理') as P -- on B.student_id = P.student_id where B.num > P.num;; -- 3、查询平均成绩大于60分的同学的学号和平均成绩;(进阶：以及姓名) -- 首先选择出平均分大于60分的同学的学号，再和学生表join，选择出姓名 -- SELECT -- student.sid, -- student.sname, -- b1.avg_num -- FROM -- ( SELECT avg( num ) AS avg_num, student_id FROM score GROUP BY student_id HAVING avg( num ) > 60 ) AS b1 -- LEFT JOIN student ON b1.student_id = student.sid; -- 4、查询所有同学的学号、姓名、选课数、总成绩；（两种解法，另一种是先把表连起来再group by） -- 这里注意 count(1)的用法， 类似select age,1 from t1; 会出现列名1，属性全为1 -- 首先成绩表和学生表连表，再根据学号进行分组，然后选择出学号，姓名，聚合学科数，求和num -- SELECT -- student.sid, -- student.sname, -- b2.course_num, -- b2.sum_num -- FROM -- ( SELECT student_id, count( course_id ) AS course_num, sum( num ) AS sum_num FROM score GROUP BY student_id ) AS b2 -- LEFT JOIN student ON b2.student_id = student.sid; -- select student.sid,student.sname,count(1) as course_num,sum(num) from score left join student on score.student_id = student.sid group by score.student_id -- 5、查询姓“李”的老师的个数； -- select count(1) from teacher where tname like '李%'; -- 6、查询没学过“李平”老师课的同学的学号、姓名； -- 首先课程表和老师表join，选择出李平老师的课程id，然后在成绩表中把选择了 这些课程id的学号选出，用学号分组后，用not in 从学生表中，选择出没有选择过课程的学号和姓名 -- select sid,sname from student where sid not in (select student_id from score where course_id in (select course.cid from course left join teacher on course.teacher_id=teacher.tid where tname = '李平老师') group by student_id ); -- -- 7、查询学过“1”课程并且也学过编号“2”课程的同学的学号、姓名； -- 先查到既选择001又选择002课程的所有同学 -- 根据学生进行分组，如果学生数量等于2表示，两门均已选择 -- select student.sid,student.sname from -- (select student_id from score where course_id in (1,2) group by student_id having count(*)>1) as b4 -- left join student on b4.student_id = student.sid; -- 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名； -- SELECT sname,sid from student where sid in (select student_id from score where course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='李平老师') group by student_id having count(*) = (select count(cid) from course left join teacher on course.teacher_id = teacher.tid where tname='李平老师')); -- 10、查询有课程成绩小于60分的同学的学号、姓名； # distinct 如果有重复项，只选择一个 -- select sid,sname from student where sid in ( -- select distinct student_id from score where num<60); -- 11、查询没有学全所有课的同学的学号、姓名； -- select sid,sname from student where sid not in (select student_id from score group by student_id having count(*)=(select count(1) from course)); -- 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名； -- select distinct student_id,student.sname from score left join student on score.student_id = student.sid where student_id != 1 and course_id in (select course_id from score where student_id = 1); -- select student_id,sname, count(course_id) -- from score left join student on score.student_id = student.sid -- where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id -- 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名； -- select student_id from score where student_id !=1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(*) >= (select count(course_id) from score where student_id = 1); -- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名； #1.课程包含 002，且课程数一样 -- select student_id,sname from score left join student on score.student_id = student.sid where student_id in ( -- select student_id from score where student_id != 2 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 2) -- ) and course_id = (select count(1) from score where student_id = 2) -- 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； -- 思路： -- 由于insert 支持 -- inset into tb1(xx,xx) select x1,x2 from tb2; -- 所有，获取所有没上过002课的所有人，获取002的平均成绩 -- -- insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2) -- from student where sid not in ( -- select student_id from score where course_id = 2) -- 17、按平均成绩从低到高显示所有学生的“生物”、“物理”、“体育”三门的课程成绩，按如下形式显示： 学生ID,生物,物理,体育,有效课程数,有效平均分； -- SELECT -- student_id, -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文, -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学, -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语 -- from score as s1; -- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； -- select course_id,max(num),min(num),cname,case when min(num) < 10 THEN 0 else min(num) end from score left join course on score.course_id = course.cid group by course_id; -- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序； -- select course_id,avg(num),sum(case when num<60 then 0 else 1 end),sum(1),sum(case when num<60 then 0 else 1 end)/sum(1) as b from score group by course_id order by avg(num) asc,b desc; -- 20、课程平均分从高到低显示（显示任课老师）； -- select course_id,cname,tname,avg(if(isnull(score.num),0,score.num)) from score -- left join course on score.course_id = course.cid -- left join teacher on course.teacher_id = teacher.tid -- group by course_id order by avg(num) desc; -- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) -- select * from -- ( -- select -- student_id, -- course_id, -- num, -- 1, -- (select num from score as s2 where s2.course_id = s1.course_id group by s2.num order by s2.num desc limit 0,1), -- (select num from score as s2 where s2.course_id = s1.course_id group by s2.num order by s2.num desc limit 3,1) as cc -- -- from score as s1 -- ) as B -- where B.num > B.cc order by B.course_id desc; -- 26、查询同名同姓学生名单，并统计同名人数； -- select sname,count(1) from student group by sname -- 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； -- select avg(if(isnull(score.num),0,score.num)),course_id from score group by course_id order by avg(num) asc,course_id desc; -- 29、查询课程名称为“数学”，且分数低于60的学生姓名和分数； -- select student.sname,score.num from score -- left join course on score.course_id = course.cid -- left join student on score.student_id = student.sid -- where course.cname = '生物' and score.num < 60; -- 32、查询选修“李平老师”所授课程的学生中，成绩最高的学生姓名及其成绩； -- select student_id,sname,max(num) from score left join student on score.student_id = student.sid where course_id in ( -- select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师') group by student_id order by max(num) desc limit 0,1; -- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩； -- 此处要了解笛卡儿积 -- select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id; -- -- 38、查询没学过“李平老师”老师讲授的任一门课程的学生姓名； #找出选过李平老师的，然后在学生表 not in -- select sid,sname from student where sid not in (select student_id from score where course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname='李平老师') group by student_id)
##还可以选择查询语句，不过这个语句结果需要为一个常量 select age,name,(select count(1) from tb) from tb1; 如果不是常量，需要设定条件，否则会变为笛卡儿积了。如下所示 # -- 17、按平均成绩从低到高显示所有学生的“生物”、“物理”、“体育”三门的课程成绩，按如下形式显示： 学生ID,生物,物理,体育,有效课程数,有效平均分； # -- SELECT # -- student_id, # -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文, # -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学, # -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语 # -- from score as s1; ''' select id,(select * from tb1 where tb1.id = 1) from tb2; 此时 子查询类似一个常量， 每一行就是 id1 常量， id2 常量这样子 ''' ''' #?????? -- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) # select * from # ( # select # student_id, # course_id, # num, # 1, # (select num from score as s2 where s2.course_id = s1.course_id group by s2.num order by s2.num desc limit 0,1), # (select num from score as s2 where s2.course_id = s1.course_id group by s2.num order by s2.num desc limit 3,1) as cc # # from score as s1 # ) as B # where B.num > B.cc order by B.course_id desc; ''' #重点 s2.student_id=s1.student_id # SELECT # student_id, # (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文, # (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学, # (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语 # from score as s1; # case then 条件 else 字段 END #select course_id,max(num),min(num),cname, # case when min(num) < 10 THEN 0 else min(num) end from score left join course on score.course_id = course.cid group by course_id; #-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序； # select course_id,avg(num),sum(case when num<60 then 0 else 1 end),sum(1),sum(case when num<60 then 0 else 1 end)/sum(1) from score group by course_id; #-- 20、课程平均分从高到低显示（显示任课老师）； # select course_id,cname,tname,avg(if(isnull(score.num),0,score.num)) from score # left join course on score.course_id = course.cid # left join teacher on course.teacher_id = teacher.tid # group by course_id order by avg(num) desc; #if(isnull(score.num),0,score.num) 如果 score.num是空，那么就是0，否则是它本身 # -- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩； # -- 此处要了解笛卡儿积，连接两张表不加 on 条件，会进行笛卡儿积，就是一张表的首行遍历连接另一张表所有行，然后第二行继续遍历连接..... # -- select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;