Sql语句基础示例

1、说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。问题及描述：- -1.学生表Student(S#,Sname,Sage,Ssex)-S#学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别- -2.课程表Course(C#,Cname,T#)-C#-课程编号，Cname 课程名称,T#教师编号- -3.教师表Teacher(T#,Tname)-T#教师编号,Tname 教师姓名- -4.成绩表SC(S#,C#,score)-S#学生编号,C#课程编号，score 分数- /- -创建测试数据createtableStudent(S#varchar(1

2、0),Snamenvarchar(10),Sagedatetime,Ssexnvarchar(10)insertintoStudentvalues(01,N赵雷insertintoStudentvalues(02,N钱电insertintoStudentvalues(03,N孙风insertintoStudentvalues(04,N李云insertintoStudentvalues(05,N周梅insertintoStudentvalues(06,N吴兰insertintoStudentvalues(07,N关B竹insertintoStudentvalues(08,N王菊createtab

3、leCourse(C#varchar(10),Cnamenvarchar(10),T#varchar(10)insertintoCoursevalues(01,N语文,02)insertintoCoursevalues(02,N数学,01)insertintoCoursevalues(03,N英语,03)createtableTeacher(T#varchar(10),Tnamenvarchar(10)insertintoTeachervalues(01,N张三)insertintoTeachervalues(02,N李四)insertintoTeachervalues(03,N王五),199

4、0-01-01,N男),1990-12-21,NF),1990-05-20,N男),1990-08-06,N男),1991-12-01,N女),1992-03-01,N女),1989-07-01,N女),1990-01-20,N女)createtableSC(S#varchar(10),C#varchar(10),scoredecimal(18,1)insertintoSCvalues(01,01,80)insertintoSCvalues(01,02,90)insertintoSCvalues(01,03,99)insertintoSCvalues(02,01,70)insertintoSC

5、values(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)inse

6、rtintoSCvalues(06,03,34)insertintoSCvalues(07,02,89)insertintoSCvalues(07,03,98)go-1、查询01课程比02课程成绩高的学生的信息及课程分数-1.1、查询同时存在01课程和02课程的情况selecta.*,b.score课程01的分数,c.score课程02的分数fromStudenta,SCb,SCcwherea.S#=b.S#anda.S#=c.S#andb.C#=01andc.C#=02andb.scorec.score-1.2、 查询同时存在01课程和02课程的情况和存在01课程但可能不存在02课程的情况(

7、不存在时显示为 null)(以下存在相同内容时不再解释)selecta.*,b.score 课程01的分数 ,c.score 课程02的分数 fromStudentaleftjoinSCbona.S#=b.S#andb.C#=01leftjoinSCcona.S#=c.S#andc.C#=02whereb.scoreisnull(c.score,0)-2、查询01课程比02课程成绩低的学生的信息及课程分数-2.1、查询同时存在01课程和02课程的情况selecta.*,b.score课程01的分数,c.score课程02的分数fromStudenta,SCb,SCcwherea.S#=b.S#

8、anda.S#=c.S#andb.C#=01andc.C#=02andb.scorec.score-2.2、 查询同时存在01课程和02课程的情况和不存在01课程但存在02课程的情况selecta.*,b.score 课程01的分数 ,c.score 课程02的分数 fromStudentaleftjoinSCbona.S#=b.S#andb.C#=01leftjoinSCcona.S#=c.S#andc.C#=02whereisnull(b.score,0)=60orderbya.S#- -4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩- -4.1、查询在 sc 表存在成

9、绩的学生信息的 SQL 语句。selecta.S#,a.Sname,cast(avg(b.score)asdecimal(18,2)avg_scorefromStudenta,scbwherea.S#=b.S#groupbya.S#,a.Snamehavingcast(avg(b.score)asdecimal(18,2)60orderbya.S#- -4.2、查询在 sc 表中不存在成绩白学生信息的 SQL 语句。selecta.S#,a.Sname,isnull(cast(avg(b.score)asdecimal(18,2),0)avg_scorefromStudentaleftjoin

10、scbona.S#=b.S#groupbya.S#,a.Snamehavingisnull(cast(avg(b.score)asdecimal(18,2),0)60orderbya.S#- -5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩- -5.1、查询所有有成绩的 SQL。selecta.S#学生编号,a.Sname学生姓名,count(b.C#)选课总数，sum(score)所有课程的总成绩fromStudenta,SCbwherea.S#=b.S#groupbya.S#,a.Snameorderbya.S#- -5.2、查询所有(包括有成绩和无成绩)的 SQL。se

11、lecta.S#学生编号,a.Sname学生姓名,count(b.C#)选课总数，sum(score)所有课程的总成绩fromStudentaleftjoinSCbona.S#=b.S#groupbya.S#,a.Snameorderbya.S#- -6、查询李姓老师的数量- -方法 1selectcount(Tname)李姓老师白数量fromTeacherwhereTnamelikeN李- -方法 2selectcount(Tname)李姓老师白数量fromTeacherwhereleft(Tname,1)=N李/*李姓老师的数量1*/- -7、查询学过张三老师授课的同学的信息selectd

12、istinctStudent.*fromStudent,SC,Course,TeacherwhereStudent.S#=SC.S#andSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N张三orderbyStudent.S#- -8、查询没学过张三老师授课的同学的信息selectm.*fromStudentmwhereS#notin(selectdistinctSC.S#fromSC,Course,TeacherwhereSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=

13、N张三)orderbym.S#-9、查询学过编号为01并且也学过编号为02的课程的同学的信息- -方法 1selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#=01andexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#=02)orderbyStudent.S#-方法 2selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#=02andexists(Select1fromSCSC_2whereSC_2.S#=SC.S

14、#andSC_2.C#=01)orderbyStudent.S#- -方法 3selectm.*fromStudentmwhereS#in(selectS#from(selectdistinctS#fromSCwhereC#=01unionallselectdistinctS#fromSCwhereC#=02)tgroupbyS#havingcount(1)=2)orderbym.S#-10、查询学过编号为01但是没有学过编号为02”的课程的同学的信息- -方法 1selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#=01andn

15、otexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#=02)orderbyStudent.S#- -方法 2selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#=01andStudent.S#notin(SelectSC_2.S#fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#=02)orderbyStudent.S#-11、查询没有学全所有课程的同学的信息-11.1、selectStudent.*fromStudent,SCwhereStuden

16、t.S#=SC.S#groupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhavingcount(C#)(selectcount(C#)fromCourse)-11.2selectStudent.*fromStudentleftjoinSConStudent.S#=SC.S#groupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhavingcount(C#)(selectcount(C#)fromCourse)-12、查询至少有一门课与学号为01的同学所学相同的同学的信息se

17、lectdistinctStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#in(selectC#fromSCwhereS#=01)andStudent.S#01-13、查询和01”号的同学学习的课程完全相同的其他同学的信息selectStudent.*fromStudentwhereS#in(selectdistinctSC.S#fromSCwhereS#01andSC.C#in(selectdistinctC#fromSCwhereS#=01)groupbySC.S#havingcount(1)=(selectcount(1)fromSC

18、whereS#=01)-14、查询没学过张三”老师讲授的任一门课程的学生姓名selectstudent.*fromstudentwherestudent.S#notin(selectdistinctsc.S#fromsc,course,teacherwheresc.C#=course.C#andcourse.T#=teacher.T#andteacher.tname=N 张三)orderbystudent.S#- -15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩selectstudent.S#,student.sname,cast(avg(score)asdecimal(18,

19、2)avg_scorefromstudent,scwherestudent.S#=SC.S#andstudent.S#in(selectS#fromSCwherescore=2)groupbystudent.S#,student.sname-16、检索01”课程分数小于 60,按分数降序排列的学生信息selectstudent.*,sc.C#,sc.scorefromstudent,scwherestudent.S#=SC.S#andsc.score=60,中等为：70-80,优良为：80-90,优秀为：=90- -方法 1selectm.C#课程编号,m.Cname课程名称,max(n.sc

20、ore)最高分,min(n.score)最低分,cast(avg(n.score)asdecimal(18,2)平均分,cast(selectcount(1)fromSCwhereC#=m.C#andscore=60)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2)及格率(%),cast(selectcount(1)fromSCwhereC#=m.C#andscore=70andscore=80andscore=90)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2)优

21、秀率(%)fromCoursem,SCnwherem.C#=n.C#groupbym.C#,m.Cnameorderbym.C#-方法 2selectm.C#课程编号,m.Cname课程名称,(selectmax(score)fromSCwhereC#=m.C#)最高分,(selectmin(score)fromSCwhereC#=m.C#)最低分,(selectcast(avg(score)asdecimal(18,2)fromSCwhereC#=m.C#)平均分,cast(selectcount(1)fromSCwhereC#=m.C#andscore=60)*100.0/(selectc

22、ount(1)fromSCwhereC#=m.C#)asdecimal(18,2)及格率(%),cast(selectcount(1)fromSCwhereC#=m.C#andscore=70andscore=80andscore=90)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2)优秀率(%)fromCoursemorderbym.C#-19、按各科成绩进行排序，并显示排名-19.1sql2000 用子查询完成-Score 重复时保留名次空缺selectt.*,px=(selectcount(1)fromSCwhereC#=t

23、.C#andscoret.score)+1fromsctorderbyt.c#,px-Score 重复时合并名次selectt.*,px=(selectcount(distinctscore)fromSCwhereC#=t.C#andscore=t.score)fromsctorderbyt.c#,px-19.2sql2005 用 rank,DENSE_RANK 完成-Score 重复时保留名次空缺(rank 完成)selectt.*,px=rank()over(partitionbyc#orderbyscoredesc)fromsctorderbyt.C#,px-Score 重复时合并名次(

24、DENSE_RANK 完成)selectt.*,px=DENSE_RANK()over(partitionbyc#orderbyscoredesc)fromsctorderbyt.C#,px-20、查询学生的总成绩并进行排名-20.1 查询学生的总成绩selectm.S#学生编号,m.Sname学生姓名,isnull(sum(score),0)总成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Snameorderby总成绩desc-20.2 查询学生的总成绩并进行排名，sql2000 用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种

25、。selectt1.*,px=(selectcount(1)from(selectm.S#学生编号,m.Sname学生姓名,isnull(sum(score),0)总成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t2where 总成绩t1.总成绩)+1from(selectm.S#学生编号,m.Sname学生姓名,isnull(sum(score),0)总成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t1orderbypxselectt1.*,px=(select

26、count(distinct 总、成绩)from(selectm.S#学生编号,m.Sname学生姓名,isnull(sum(score),0)总成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t2where 总成绩=t1.总成绩)from(selectm.S#学生编号,m.Sname学生姓名,isnull(sum(score),0)总成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t1orderbypx-20.3 查询学生的总成绩并进行排名， sql2005 用 r

27、ank,DENSE_RANK 完成，分总分重复时保留名次空缺和不保留名次空缺两种。selectt.*,px=rank()over(orderby总、成绩desc)from(selectm.S#学生编号,m.Sname学生姓名,isnull(sum(score),0)总成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)torderbypxselectt.*,px=DENSE_RANK()over(orderby总成绩desc)from(selectm.S#学生编号,m.Sname学生姓名,isnull(sum(score),0)总成绩

28、fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)torderbypx-21、查询不同老师所教不同课程平均分从高到低显示selectm.T#,m.Tname,cast(avg(o.score)asdecimal(18,2)avg_scorefromTeacherm,Coursen,SCowherem.T#=n.T#andn.C#=o.C#groupbym.T#,m.Tnameorderbyavg_scoredesc-22、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩-22.1sql2000 用子查询完成-Score 重

29、复时保留名次空缺select*from(selectt.*,px=(selectcount(1)fromSCwhereC#=t.C#andscoret.score)+1fromsct)mwherepxbetween2and3orderbym.c#,m.px-Score 重复时合并名次select*from(selectt.*,px=(selectcount(distinctscore)fromSCwhereC#=t.C#andscore=t.score)fromsct)mwherepxbetween2and3orderbym.c#,m.px-22.2sql2005 用 rank,DENSE_R

30、ANK 完成-Score 重复时保留名次空缺(rank 完成)select*from(selectt.*,px=rank()over(partitionbyc#orderbyscoredesc)fromsct)mwherepxbetween2and3orderbym.C#,m.px-Score 重复时合并名次(DENSE_RANK 完成)select*from(selectt.*,px=DENSE_RANK()over(partitionbyc#orderbyscoredesc)fromsct)mwherepxbetween2and3orderbym.C#,m.px-23、统计各科成绩各分数段

31、人数：课程编号，课程名称,100-85,85-70,70-60,0-60及所占百分比-23.1 统计各科成绩各分数段人数：课程编号，课程名称,100-85,85-70,70-60,0-60- -横向显示selectCourse.C#课程编号,Cnameas课程名称,sum(casewhenscore=85then1else0end)85-100,sum(casewhenscore=70andscore=60andscore70then1else0end)60-70,sum(casewhenscore=85then85-100whenn.score=70andn.score=60andn.sco

32、re=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.score70then60-70else0-60end)orderbym.C#,m.Cname,分数段- -23.2 统计各科成绩各分数段人数：课程编号，课程名称,100-85,85-70,70-60,60及所占百分比- -横向显示selectm.C#课程编号，m.Cname 课程名称，(selectco

33、unt(1)fromSCwhereC#=m.C#andscore60)0-60,cast(selectcount(1)fromSCwhereC#=m.C#andscore=60andscore=60andscore=70andscore=70andscore=85)85-100,cast(selectcount(1)fromSCwhereC#=m.C#andscore=85)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2)百分比()fromCoursemorderbym.C#- -纵向显示 1(显示存在的分数段)selectm.C

34、#课程编号,m.Cname课程名称,分数段=(casewhenn.score=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.scoret1.平均成绩)+1from(selectm.S#学生编号,m.Sname学生姓名,isnull(cast(avg(scor

35、e)asdecimal(18,2),0)平均成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t1orderbypxselectt1.*,px=(selectcount(distinct 平均成绩)from(selectm.S#学生编号,m.Sname学生姓名,isnull(cast(avg(score)asdecimal(18,2),0)平均成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t2where 平均成绩=t1.平均成绩)from(selectm.S#学生编号

36、,m.Sname学生姓名,isnull(cast(avg(score)asdecimal(18,2),0)平均成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t1orderbypx-24.2 查询学生的平均成绩并进行排名，sql2005 用 rank,DENSE_RANK 完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。selectt.*,px=rank()over(orderby平均成绩desc)from(selectm.S#学生编号,m.Sname学生姓名,isnull(cast(avg(score)asdecimal

37、(18,2),0)平均成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)torderbypxselectt.*,px=DENSE_RANK()over(orderby平均成绩desc)from(selectm.S#学生编号,m.Sname学生姓名,isnull(cast(avg(score)asdecimal(18,2),0)平均成绩fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)torderbypx- -25、查询各科成绩前三名的记录- -25.1 分数重复时保留名次空缺

38、selectm.*,n.C#,n.scorefromStudentm,SCnwherem.S#=n.S#andn.scorein(selecttop3scorefromscwhereC#=n.C#orderbyscoredesc)orderbyn.C#,n.scoredesc- -25.2 分数重复时不保留名次空缺，合并名次- -sql2000 用子查询实现select*from(selectt.*,px=(selectcount(distinctscore)fromSCwhereC#=t.C#andscore=t.score)fromsct)mwherepxbetween1and3order

39、bym.c#,m.px- -sql2005 用 DENSE_RANK 实现select*from(selectt.*,px=DENSE_RANK()over(partitionbyc#orderbyscoredesc)fromsct)mwherepxbetween1and3orderbym.C#,m.px-26、查询每门课程被选修的学生数selectc#,count(S#)学生数fromscgroupbyC#-27、查询出只有两门课程的全部学生的学号和姓名selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#groupbyStudent.S#,Student.Snamehavingcount(SC.C#)=2orderbyStudent.S#-28、查询男生、女生人数selectcount(Ssex)as 男生人数 fromStudentwhereSsex=N男selectcount(Ssex)as 女生人数 fromStude