讯享网

1、  一个班级有且有多名学生，一名学生只属于一个班级；
2、  学生有可能没有成绩；
建立表格:

create database xs character set utf8; use xs; create table bjb(id int primary key auto_increment,bjmc varchar(20))charset=utf8; insert into bjb values (1,'一班'),(2,'二班'),(3,'三班'),(4,'四班'),(5,'五班'); create table xsb(id int primary key auto_increment,xh int,xm varchar(20),xb char(1),bjb_id int); insert into xsb values(null,,'张三','男',1),(null,,'李婷','女',1),(null,,'李四','男',2),(null,,'王丽','女',3),(null,,'王五','男',3); create table cjb(id int,yw int,sx int ); insert into cjb values(1,70,47),(2,80,60),(3,50,82),(4,80,90),(null,57,92); update xsb set xh= where xm="李振";

（1）查询所有学生的信息（学号、姓名、性别、班级名称）

讯享网select x.xh,x.xm,x.xb,b.bjmc from xsb x,bjb b where x.bjb_id=b.id;

（2）查询所有人的课程分数（学号、姓名、性别、班级名称、语文分数、数学分数）

select x.xh,x.xm,x.xb,b.bjmc,c.yw,c.sx from xsb x,bjb b,cjb c where b.id=x.bjb_id and x.id=c.id;

（3）查询所有班级的人数（班级编号、班级名称、人数）

讯享网select b.id,b.bjmc,count(*) from xsb x join bjb b on x.bjb_id=b.id where x.bjb_id group by x.bjb_id;

（4）查询总分数（语文+数学）>=150的学生信息（学号、姓名、班级名称、总分数）

select x.xh,x.xm,b.bjmc,sum(c.yw+c.sx) as zf from xsb x,bjb b,cjb c where x.bjb_id=b.id and x.id=c.id and c.yw+c.sx>=150 group by x.xh;

（5）查询所有班级的平均分数（班级编号、班级名称、语文平均分数、数学平均分数）

讯享网select b.id,b.bjmc,avg(c.yw),avg(c.sx) from xsb x,bjb b,cjb c where x.bjb_id=b.id and x.id=c.id group by x.bjb_id;

（6）查询各科都合格【分数>=60分】的学生（学号、姓名、语文分数、数学分数）

SELECT x.xh,x.xm,c.yw,c.sx  FROM bjb b,xsb x,cjb c WHERE x.bjb_id = b.id AND x.id = c.id AND c.yw >= 60 AND c.sx >= 60 GROUP BY x.xh; SELECT x.xh,x.xm,c.yw,c.sx  FROM xsb x,cjb c WHERE x.id = c.id AND c.yw >= 60 AND c.sx >= 60; select x.xh,x.xm,c.yw,c.sx from xsb x join cjb c on x.id=c.id where c.yw>=60 and c.sx>=60; 

（7）查询有挂科【分数<60分】现象的学生（学号、姓名、语文分数、数学分数）

讯享网SELECT x.xh,x.xm,c.yw,c.sx FROM bjb b ,xsb x ,cjb c WHERE x.bjb_id=b.id AND x.id=c.id AND (c.yw < 60 or c.sx < 60 ); SELECT x.xh,x.xm,c.yw,c.sx FROM bjb b JOIN xsb x JOIN cjb c ON x.bjb_id=b.id AND x.id=c.id WHERE (c.yw < 60 or c.sx < 60 ); SELECT x.xh,x.xm,c.yw,c.sx FROM xsb x JOIN cjb c ON x.id=c.id WHERE c.yw < 60 or c.sx < 60 ;

（8）查询班级人数>=30的班级（班级编号、班级名称、人数）

SELECT b.id,b.bjmc,count(c.id) AS rs FROM bjb b ,xsb x, cjb c WHERE c.id=x.id AND b.id=x.bjb_id  GROUP BY b.id,b.bjmc HAVING count(c.id) >=30;(错误!!!!!!) SELECT b.id,b.bjmc,count(*) rs FROM bjb b JOIN xsb x ON b.id=x.bjb_id GROUP BY x.bjb_id HAVING count(*) >=30;

（9）查询没有参加考试【没有成绩表】的学生（学号、姓名、性别、班级名称）

讯享网SELECT x.xh,x.xm,x.xb,b.bjmc FROM bjb b ,xsb x ,cjb c WHERE x.bjb_id=b.id AND x.id not in (select id from cjb) GROUP BY x.xh,x.xm,x.xb,b.bjmc; select x.xh,x.xm,x.xb,b.bjmc from bjb b join xsb x join cjb c on x.bjb_id=b.id and x.id not in (select id from cjb) group by x.xh,x.xm,x.xb,b.bjmc; select x.xh,x.xm,x.xb,b.bjmc from bjb b join xsb x on x.bjb_id=b.id join cjb c on x.id not in (select id from cjb) group by x.xh,x.xm,x.xb,b.bjmc; 

（10）假设分数>=60分合格，分析学生的成绩是否合格（学号、姓名、语文合格情况[合格/不合格]、数学合格情况[合格/不合格]）

SELECT x.xh,x.xm,IF(c.yw >= 60,'合格','不合格') AS '语文合格情况',IF(c.sx >= 60,'合格','不合格') AS '数学合格情况' FROM bjb b,xsb x,cjb c WHERE x.bjb_id = b.id AND x.id = c.id;