第一题:
对于这两张表我已在数据库做了基础模型:
第一解析:计算出所有学生的英语平均成绩,需要运用到数据操作的多表联合查询跟集合函数的avg去求算学生的平均值 select * from 表1 join 表2 on 表1.字段1 = 表2.字段2
答案:select avg(b.english) from c_cust a JOIN m_mark b on a.sno = b.sdo;
第二解析:运用的知识数据操作里面逻辑判断in跟多表联合查询的用法 由于学号是11,22....我没有添加那多信息,我们就设置3,4,5,6,7学生的学号 ,select * from 表1 join 表2 on 表1.字段1 = 表2.字段2
答案:SELECT a.sdo,a.`name`,a.address,a.telep,b.math from c_cust a JOIN m_mark b ONa.sno = b.sdowhere a.sdo in (3,4,5,6,7);
第三解析:运用的知识多表联合查询跟跟排序order by select * from 表1 join 表2 on 表1.字段1 = 表2.字段2 oder by 字段名 desc;
答案:SELECT a.`name`,b.coputer from c_cust a JOIN m_mark b ON a.sno = b.sdo ORDER BY b.coputer DESC;
第四题解析:运用知识:多表联合查询跟集合函数sum,逻辑判断“>”,分组group by/having还有排序order by
答案:SELECT a.sdo,a.`name`,sum(b.math+b.english+b.coputer)from c_cust a JOIN m_mark b ON a.sno = b.sdo GROUP BY a.sdo,a.`name` HAVING sum(b.math+b.english+b.coputer)>240 ORDER BY sum(b.math+b.english+b.coputer) DESC;
第二题:
第一题解析:运用的知识集合函数count() 跟逻辑判断"like" select * from 表名 where 字段 like ' 王%';
答案:SELECT COUNT(*) FROM s_stu where stname LIKE '王%';
第二题有点懵逼为啥查询出来的是空值:SELECT stid FROM s_stu WHERE (select s_sc.score,c_cou.coname FROM s_sc join c_cou on c_cou.coid = s_sc.cid join s_stu on s_stu.stid = s_sc.sid AND c_cou.coname = 'Mmath')>(select s_sc.score,c_cou.coname FROM s_sc join c_cou on c_cou.coid = s_sc.cid join s_stu on s_stu.stid = s_sc.sid AND c_cou.coname = 'Chineses')
第三题解析:运用的知识一般查询加上聚合函数'avg()'
答案:SELECT sid,AVG(score)FROM s_sc group by sid having avg(score) >90