搜索
--创建测试数据createtableStudent(Svarchar(10),Snamenvarchar(10),Sagedatetime,Ssexnvarchar(10));insertintoStudentvalues('01','赵英','1990-01-01','男');insertintoStudentvalues('02','钱雄','1990-12-21','男');insertintoStudentvalues('03','孙豪','1990-05-20','男');insertintoStudentvalues('04','李杰','1990-08-06','男');insertintoStudentvalues('05','周梅','1991-12-01','女');insertintoStudentvalues('06','吴兰','1992-03-01','女');insertintoStudentvalues('07','郑竹','1989-07-01','女');insertintoStudentvalues('08','王菊','1990-01-20','女');createtableCourse(Cvarchar(10),Cnamenvarchar(10),Tvarchar(10));insertintoCoursevalues('01','语文','02');insertintoCoursevalues('02','数学','01');insertintoCoursevalues('03','英语','03');insertintoCoursevalues('04','物理','01');createtableTeacher(Tvarchar(10),Tnamenvarchar(10));insertintoTeachervalues('01','张三');insertintoTeachervalues('02','李四');insertintoTeachervalues('03','王五');createtableSC(Svarchar(10),Cvarchar(10),scoredecimal(18,1));insertintoSCvalues('01','01',80);insertintoSCvalues('01','02',90);insertintoSCvalues('01','03',99);insertintoSCvalues('02','01',70);insertintoSCvalues('02','02',60);insertintoSCvalues('02','03',80);insertintoSCvalues('03','01',80);insertintoSCvalues('03','02',80);insertintoSCvalues('03','03',80);insertintoSCvalues('04','01',50);insertintoSCvalues('04','02'30);insertintoSCvalues('04','03',20);insertintoSCvalues('05','01',76);insertintoSCvalues('05','02',87);insertintoSCvalues('06','01',31);insertintoSCvalues('06','03',34);insertintoSCvalues('07','02',89);insertintoSCvalues('07','03',98);go1、查询01课程比02课程成绩高的学生的信息及课程分数select*fromstudent(SELECT*FROMsccWHEREc.`C`='01'SELECT*FROMsccWHEREc.`C`='02')(SELECTs.*,c1.`score`01分数,c2.`score`02分数FROMstudents,scc1,scc2WHEREs.`S`=c1.`S`ANDs.`S`=c2.`S`ANDc1.`C`='01'ANDc2.`C`='02'ANDc1.`score`c2.`score`)2、查询01课程比02课程成绩低的学生的信息及课程分数select*fromstudent(SELECT*FROMsccWHEREc.`C`='01'SELECT*FROMsccWHEREc.`C`='02')(SELECTs.*,c1.`score`01分数,c2.`score`02分数FROMstudents,scc1,scc2WHEREs.`S`=c1.`S`ANDs.`S`=c2.`S`ANDc1.`C`='01'ANDc2.`C`='02'ANDc1.`score`c2.`score`)3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩selects.`S`,s.`Sname`,AVG(c.`score`)fromstudents,sccwheres.`S`=c.`S`GROUPBYs.`S`HAVINGAVG(c.`score`)=604、查询平均成绩小于等于60分的同学的学生编号和学生姓名和平均成绩selects.`S`,s.`Sname`,AVG(c.`score`)fromstudents,sccwheres.`S`=c.`S`GROUPBYs.`S`HAVINGAVG(c.`score`)=605、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩SELECTs.`S`,s.`Sname`,COUNT(c.`C`)'选课总数',SUM(c.`score`)总成绩FROMstudents,sccWHEREs.`S`=c.`S`GROUPBYs.`S`6、查询李姓老师的数量SELECTCOUNT(*)FROMteachertWHEREt.`Tname`LIKE'李%'7、查询学过张三老师授课的同学的信息selectDISTINCTs.*fromstudents,scc,coursecc,teachertwheres.S=c.Sandc.C=cc.Candcc.T=t.Tandt.Tname='张三'8、查询没学过张三老师授课的同学的信息select*fromstudentwheresnotin(selectDISTINCTs.Sfromstudents,scc,coursecc,teachertwheres.S=c.Sandc.C=cc.Candcc.T=t.Tandt.Tname='张三')9、查询学过编号为01并且也学过编号为02的课程的同学的信息selects.*fromstudents,scc1,scc2wheres.`S`=c1.`S`ands.`S`=c2.`S`andc1.`C`='01'andc2.`C`='02'10、查询学过编号为01但是没有学过编号为02的课程的同学的信息SELECT*FROMstudents,sccWHEREs.`S`=c.`S`ANDc.`C`='01'ANDs.`S`NOTIN(SELECTsFROMscWHEREc='02')11、查询没有学全所有课程的同学的信息selects.*fromstudents,sccwheres.S=c.SGROUPBYs.Shavingcount(c.score)(selectcount(*)fromcourse)12、查询至少有一门课与学号为01的同学所学相同的同学的信息SELECTDISTINCTs.*FROMstudentsLEFTJOINsccONs.`S`=c.`S`WHEREc.`C`IN(SELECTcFROMscWHEREs='01')ANDs.`S`'01'13、查询和01号的同学学习的课程完全相同的其他同学的信息SELECT*FROM(SELECTs.`S`,COUNT(c.`score`)aaFROMstudentsLEFTJOINsccONs.`S`=c.`S`WHEREc.`C`IN(SELECTcFROMscWHEREs='01')GROUPBYs.`S`HAVINGCOUNT(c.`score`)=(SELECTCOUNT(*)FROMscWHEREs='01'))aLEFTJOIN(SELECTs,COUNT(*)bbFROMscGROUPBYs)bONa.s=b.sWHEREa.aa=b.bbANDa.s'01'14、查询没学过张三老师讲授的任一门课程的学生姓名select*fromstudentwheresnotin(selects.Sfromstudents,scc,coursecc,teachertwheres.S=c.Sandc.C=cc.Candcc.T=t.Tandt.Tname='张三'15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩selects.S,s.Sname,round(avg(c.score))fromstudents,sccwheres.S=c.Sandc.score6016、检索01课程分数小于60，按分数降序排列的学生信息selects.*,c.C,c.scorefromstudents,sccwheres.S=C.Sandc.score60andc.C='01'orderbyc.scoredescGROUPBYs.Shavingcount(c.score)=217、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩CASEaWHEN1THEN方法体1WHEN2THEN方法体2ELSE不满足所有条件时执行的方法体ENDselects.S,s.Sname,MAX(CASEc.cWHEN'01'THENc.scoreELSE0END)语文,MAX(CASEc.cWHEN'02'THENc.scoreELSE0END)数学,MAX(CASEWHENc.c='03'THENc.scoreELSE0END)英语,MAX(CASEWHENc.c='04'THENc.scoreELSE0END)物理,AVG(c.`score`)afromstudentsLEFTJOINsccons.S=c.SGROUPBYs.SORDERBYadesc18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率SELECTc.`C`,c.`Cname`,MAX(s.`score`),MIN(s.`score`),AVG(s.`score`),(SELECTCOUNT(1)FROMscWHEREsc.`C`=s.`C`ANDsc.`score`=60)/(SELECTCOUNT(1)FROMscWHEREsc.`C`=s.c)及格率FROMscs,coursecWHEREs.`C`=c.`C`GROUPBYc.`C`19、按各科成绩进行排序，并显示排名SELECTc.*,(SELECTCOUNT(1)FROMscc1WHEREc1.c='01'ANDc1.scorec.`score`)+1mcFROMsccWHEREc.c='01'ORDERBYmc20.查询不同老师所教不同课程平均分从高到低显示selectt.T,t.Tname,c.C,c.Cname,avg(s.score)fromscs,coursec,teachertwheres.C=c.Candc.T=t.TGROUPBYt.T,c.CORDERBYavg(s.score)desc21.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩SELECTs.`S`,s.`Sname`,a.c,a.score,a.mcFROMstudents,(SELECTc.`S`,c.`C`,c.`score`,(SELECTCOUNT(*)FROMscccWHEREcc.c='01'A