50个常用的SQL语句练习.doc

基本信息Student(`S#`,Sname,Sage,Ssex)学生表Course(`C#`,Cname,`T#`)课程表SC(`S#`,`C#`,score)成绩表Teacher(`T#`,Tname)教师表问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；selecta.`S#`from(select`S#`,scorefromSCwhere`C#`='001')a,(select`S#`,scorefromSCwhere`C#`='002')bwherea.score>b.scoreanda.`S#`=b.`S#`;↑一张表中存在多对多情况的2、查询平均成绩大于60分的同学的学号和平均成绩；答案一：select`S#`,avg(score)fromscgroupby`S#`havingavg(score)>60;↑一对多，对组进行筛选答案二：SELECTs,scrFROM(SELECTsc.`S#`s,AVG(sc.`score`)scrFROMscGROUPBYsc.`S#`)rsWHERErs.scr>60ORDERBYrs.scrDESC↑嵌套查询可能影响效率3、查询所有同学的学号、姓名、选课数、总成绩；答案一：selectStudent.`S#`,Student.Sname,count(`C#`),sum(score)fromStudentleftOuterjoinSConStudent.`S#`=SC.`S#`groupbyStudent.`S#`,Sname↑如果学生没有选课，仍然能查出，显示总分null（边界情况）答案二：SELECTstudent.`S#`,student.`Sname`,COUNT(sc.`score`)选课数,SUM(sc.`score`)总分FROMStudent,scWHEREstudent.`S#`=sc.`S#`GROUPBYsc.`S#`↑如果学生没有选课，sc表中没有他的学号，就查不出该学生，有缺陷！4、查询姓“李”的老师的个数；selectcount(distinct(Tname))fromTeacherwhereTnamelike'李%';5、查询没学过“叶平”老师课的同学的学号、姓名；selectStudent.`S#`,Student.SnamefromStudentwhere`S#`notin(selectdistinct(SC.`S#`)fromSC,Course,TeacherwhereSC.`C#`=Course.`C#`andTeacher.`T#`=Course.`T#`andTeacher.Tname='叶平');↑反面思考Step1：先找学过叶平老师课的学生学号，三表联合查询Step2：在用notin选出没学过的Step3：distinct以防叶平老师教多节课；否则若某同学的几节课都由叶平教，学号就会出现重复6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；selectStudent.`S#`,Student.SnamefromStudent,SCwhereStudent.`S#`=SC.`S#`andSC.`C#`='001'andexists(Select*fromSCasSC_2whereSC_2.`S#`=SC.`S#`andSC_2.`C#`='002');↑注意目标字段`S#`关联existssubquery可以用insubquery代替，如下selectStudent.`S#`,Student.SnamefromStudent,ScwhereStudent.`S#`=SC.`S#`andSC.`C#`='001'andsc.`s#`in(selectsc_2.`s#`fromscassc_2wheresc_2.`c#`='002');↑不同之处，insubquery此处就不需要关联了查询学过“叶平”老师所教的所有课的同学的学号、姓名；select`S#`,SnamefromStudentwhere`S#`in(select`S#`fromSC,Course,TeacherwhereSC.`C#`=Course.`C#`andTeacher.`T#`=Course.`T#`andTeacher.Tname='叶平'groupby`S#`havingcount(SC.`C#`)=(selectcount(`C#`)fromCourse,TeacherwhereTeacher.`T#`=Course.`T#`andTname='叶平'));8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；（太混乱）Select`S#`,Snamefrom(selectStudent.`S