作为一枚Java后端开发者，数据库知识必不可少，对数据库的掌握熟悉度的考察也是对这个人是否有扎 实基本功的考察。特别对于初级开发者，面试可能不会去问框架相关知识，但是绝对不会不去考察数据 库知识，这里收集一些常见类型的SQL语句，无论对于平常开发还是准备面试，都会有助益。
基础表结构
student(sno,sname,sage,ssex) 学生表course(cno,cname,tno) 课程表sc(sno,cno,score) 成绩表teacher(tno,tname) 教师表
1、查询课程1的成绩比课程2的成绩高的所有学生的学号
selecta.snofrom(selectsno,scorefromscwherecno=1) a, (selectsno,scorefromscwherecno=2) bwherea.score>b.scoreanda.sno=b.sno
2、查询平均成绩大于60分的同学的学号和平均成绩
selecta.snoas"学号", avg(a.score) as"平均成绩"from (selectsno,scorefromsc) agroupbysnohavingavg(a.score)>60
3、查询所有同学的学号、姓名、选课数、总成绩
selecta.snoas学号, b.snameas姓名, count(a.cno) as选课数, sum(a.score) as总成绩fromsca, studentbwherea.sno=b.snogroupbya.sno, b.sname或者selectstudent.snoas学号, student.snameas姓名, count(sc.cno) as选课数, sum(score) as总成绩fromstudentleftouterjoinsconstudent.sno=sc.snogroupbystudent.sno, sname
4、查询姓“张”的老师的个数
selectcount(tno) fromteacherwheretnamelike'张%'
5、查询没学过“张三”老师课的同学的学号、姓名
selectstudent.sno,student.snamefromstudentwheresnonotin (selectdistinct(sc.sno) fromsc,course,teacherwheresc.cno=course.cnoandteacher.tno=course.tnoandteacher.tname='张三')
6、查询同时学过课程1和课程2的同学的学号、姓名
selectsno, snamefromstudentwheresnoin (selectsnofromscwheresc.cno=1) andsnoin (selectsnofromscwheresc.cno=2) 或者selectc.sno, c.snamefrom(selectsnofromscwheresc.cno=1) a, (selectsnofromscwheresc.cno=2) b, studentcwherea.sno=b.snoanda.sno=c.sno或者selectstudent.sno,student.snamefromstudent,scwherestudent.sno=sc.snoandsc.cno=1andexists(select*fromscassc_2wheresc_2.sno=sc.snoandsc_2.cno=2)
7、查询学过“李四”老师所教所有课程的所有同学的学号、姓名
selectsno--子查询结果如果要当临时表来使用的话需要起个别名(比如这里面的t) from ( --学过该老师课程的同学学号(包含只学一门) selectsno,count(cno) numfromscwherecnoin ( --该老师教了哪些课程selectcnofromcoursewheretnoin ( selecttnofromteacherwheretname='李四' ) ) groupbysno) twheret.num= ( --统计该老师总课程数selectcount(cno) fromcoursewheretno= ( selecttnofromteacherwheretname='李四' ) )
8、查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名
selecta.sno, a.snamefromstudenta, (selectsno, scorefromscwherecno=1) b, (selectsno, scorefromscwherecno=2) cwhereb.score>c.scoreandb.sno=c.snoanda.sno=b.sno
9、查询所有课程成绩小于60分的同学的学号、姓名
selectsno,snamefromstudentwheresnonotin (selectdistinctsnofromscwherescore>60)
10、查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名
selectdistincta.sno, a.snamefromstudenta, scbwherea.sno<>1anda.sno=b.snoandb.cnoin (selectcnofromscwheresno=1) 或者selects.sno,s.snamefromstudents, (selectsc.snofromscwheresc.cnoin (selectsc1.cnofromscsc1wheresc1.sno=1) andsc.sno<>1groupbysc.sno) r1wherer1.sno=s.sno
11、 把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩
updatescsetscore=(selectavg(sc_2.score) fromscsc_2wheresc_2.cno=sc.cno) wherecnoin(selectc.cnofromcoursecleftjointeachertont.tno=c.tnowheret.tname='王五');
12、查询和编号为1002的同学学习的课程完全相同的其他同学学号和姓名
selectsc_1.snofrom (selectcnofromscwheresno='1002')aleftjoinscsc_1ona.cno=sc_1.cnowheresc_1.sno<>'1002'groupbysc_1.snohavingcount(sc_1.cno) =(selectcount(cno) fromscwheresno='1002'); selecta.sno,s.snamefrom(selectsno,GROUP_CONCAT(cnoorderbycnoseparator',') ascid_strfromscwheresno='1002')b, (selectsno,GROUP_CONCAT(cnoorderbycnoseparator',') ascid_strfromscgroupbysno)aleftjoinstudentsona.sno=s.snowherea.cid_str=b.cid_stranda.sno<>'1002';
13、删除学习“王五”老师课的sc表记录
deletescfromcourse, teacherwherecourse.cno=sc.cnoandcourse.tno=teacher.tnoandtname='王五'
14、向sc表中插入一些记录，这些记录要求符合以下条件：将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩
insertscselectsno, 3, (selectavg(score) fromscwherecno=2) fromstudentwheresnonotin (selectsnofromscwherecno=3)
15、按平平均分从高到低显示所有学生的如下统计报表：学号，企业管理，马克思，UML，数据库，物理，课程数，平均分
selectsnoas学号,max(casewhencno=1thenscoreend) AS企业管理,max(casewhencno=2thenscoreend) AS马克思,max(casewhencno=3thenscoreend) ASUML,max(casewhencno=4thenscoreend) AS数据库,max(casewhencno=5thenscoreend) AS物理,count(cno) AS课程数,avg(score) AS平均分FROMscGROUPbysnoORDERbyavg(score) DESC
16、查询各科成绩最高分和最低分：以如下形式显示：课程号，最高分，最低分
selectcnoas课程号, max(score) as最高分, min(score) 最低分fromscgroupbycnoselectcourse.cnoas'课程号',MAX(score) as'最高分',MIN(score) as'最低分'fromsc,coursewheresc.cno=course.cnogroupbycourse.cno
17、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECTt.cnoas课程号, c.cnameas课程名, COALESCE(avg(score),0) as平均成绩, 100*sum(casewhenCOALESCE(score,0)>=60then1else0END)/count(*) as及格百分数fromsctleftjoincoursecont.cno=c.cnogroupbyt.cnoorderby100*sum(casewhenCOALESCE(score,0)>=60then1else0END)/count(*);
18、查询如下课程平均成绩和及格率的百分数(用"1行"显示)：企业管理（001），马克思（002），UML（003），数据库（004）
selectavg(casewhencno=1thenscoreend) as平均分1, avg(casewhencno=2thenscoreend) as平均分2, avg(casewhencno=3thenscoreend) as平均分3, avg(casewhencno=4thenscoreend) as平均分4, 100*sum(casewhencno=1andscore>60then1else0end) /sum(casewhencno=1then1else0end) as及格率1, 100*sum(casewhencno=2andscore>60then1else0end) /sum(casewhencno=2then1else0end) as及格率2, 100*sum(casewhencno=3andscore>60then1else0end) /sum(casewhencno=3then1else0end) as及格率3, 100*sum(casewhencno=4andscore>60then1else0end) /sum(casewhencno=4then1else0end) as及格率4fromsc
19、查询不同老师所教不同课程平均分, 从高到低显示
selectr.tnameas'教师',r.rnameas'课程', AVG(score) as'平均分'fromsc, (selectt.tname,c.cnoasrcso,c.cnameasrnamefromteachert ,coursecwheret.tno=c.tno)rwheresc.cno=r.rcsogroupbysc.cno,r.tname,r.rnameorderbyAVG(score) desc
20、查询如下课程成绩均在第3名到第6名之间的学生的成绩：学生ID，学生姓名，企业管理，马克思，UML，数据库，平均成绩
selecttop6max(a.sno) 学号, max(b.sname) 姓名, max(casewhencno=1thenscoreend) as企业管理, max(casewhencno=2thenscoreend) as马克思, max(casewhencno=3thenscoreend) asUML, max(casewhencno=4thenscoreend) as数据库, avg(score) as平均分fromsca, studentbwherea.snonotin(selecttop2snofromscwherecno=1orderbyscoredesc) anda.snonotin (selecttop2snofromscwherecno=2orderbyscoredesc) anda.snonotin (selecttop2snofromscwherecno=3orderbyscoredesc) anda.snonotin (selecttop2snofromscwherecno=4orderbyscoredesc) anda.sno=b.snogroupbya.sno
