mysql 至少选修一门课程

一门课程"/>

mysql 至少选修一门课程

1、自行创建测试数据

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

select A.student_id,sw,ty from (select student_id,number as sw from score left join course on score.corse_id = course.cid where courseame = ‘生物‘) as A left join (select student_id,number as ty from score left join course on score.corse_id = course.cid where courseame = ‘体育‘) as B on A.student_id = B.student_id where sw > if(isnull(ty),0,ty);

3、查询平均成绩大于60分的同学的学号和平均成绩；

思路：

产寻学生的id，和成绩，然后用avg函数来求得同一id号的学生平均成绩，并用having进行成绩的筛选

select student_id,avg(number) from score group by student_id having avg(number)>60;

增加显示学生名

select student_id,student.sname,avg(number) from score left join student on score.student_id=student.sid group by student_id having avg(number)>60;

第二种实现方式

个人觉得这种方式好理解一些，语法结构是

先通过select student_id,avg(number) as stu_num from score group by student_id语句分组将数据取出并起临时表别名为SCORE，然后在和student表进行连表。

select SCORE.student_id,SCORE.stu_num from (select student_id,avg(number) as stu_num from score group by student_id) as SCORE left join student on SCORE.student_id=student.sid;

4、查询所有同学的学号、姓名、选课数、总成绩；

语句进化过程：

(1)先讲student表关联起来，关联条件是student_id

select * from score left join student on score.student_id = student.sid;

(2)再通过条件筛选自己需要显示的内容，用limit来分页显示

select score.student_id,student.sname,score.corse_id,score.number from score left join student on score.student_id = student.sid limit 5;

(3)用聚合函数count来统计课程数，用sum来算成绩的合。

select score.student_id,student.sname,count(score.corse_id),sum(score.number) from score left join student on score.student_id = student.sid group by score.student_id limit 5;

终极：

select score.student_id,student.sname,count(score.corse_id),sum(score.number) from score left join student on score.student_id = student.sid group by score.student_id;

5、查询姓“李”的老师的个数；

select count(tname) from teacher where tname like "李%";

6、查询没学过“叶平”老师课的同学的学号、姓名；

(1)查出李平老师所受的课

select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname="李平老师";

(2)查出选择李平老师讲课的学生

select * from score where score.corse_id in (select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname="李平老师")

(3)排除选择李平老师讲课的学生

select sid,sname from student where student.sid not in (select student_id from score where score.corse_id in (select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname="李平老师"));

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

(1)取出课程id是1和2的课程

select * from score where corse_id=1 or corse_id=2;

(2)通过student_id来进行