《2022年数据库MYSQL经典题材,面试前必备终稿》由会员分享，可在线阅读，更多相关《2022年数据库MYSQL经典题材,面试前必备终稿（8页珍藏版）》请在金锄头文库上搜索。
1、从一个问题中看常用的SQL 语句2009-11-06 数据库如下：Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表问题：1、查询 “ 001 ” 课程比 “ 002 ” 课程成绩高的所有学生的学号selecta.S# from( selects#,score fromSC whereC#= 001) a,(select s#,score from SC where C#= 002) b where a.scoreb.score and a.s#=b.s#;
2、 2、查询平均成绩大于60 分的同学的学号和平均成绩；select S#,avg (score) from sc groupby S# havingavg (score) 60; 3、查询所有同学的学号、姓名、选课数、总成绩；select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student leftOuterjoin SC on Student.S#=SC.S# groupby Student.S#,Sname 4、查询姓 “ 李” 的老师的个数；selectcount( distinct(Tname) from Teac
3、her where Tname like 李%; 5、查询没学过 “ 叶平 ” 老师课的同学的学号、姓名；select Student.S#,Student.Sname from Student whereS# notin( selectdistinct( SC.S#) fromSC,Course,Teacher whereSC.C#=Course.C# andTeacher.T#=Course.T# andTeacher.Tname= 叶平 ); 6、查询学过 “ 001 ” 并且也学过编号“ 002 ” 课程的同学的学号、姓名；select Student.S#,Student.Sname
4、 from Student,SC where Student.S#=SC.S# and SC.C#=001andexists( Select* fromSC asSC_2 whereSC_2.S#=SC.S# andSC_2.C#= 002); 7、查询学过 “ 叶平 ” 老师所教的所有课的同学的学号、姓名；select S#,Sname from Student where S# in ( select S# from SC ,Course ,Teacher whereSC.C#=Course.C# andTeacher.T#=Course.T# andTeacher.Tname= 叶平 g
5、roupbyS# havingcount(SC.C#)=(selectcount(C#) fromCourse,Teacher 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 8 页 - - - - - - - - - where Teacher.T#=Course.T# and Tname= 叶平 ); 8、查询课程编号“ 002 ” 的成绩比课程编号“ 001 ” 课程低的所有同学的学号、姓名；SelectS#,Sname from( selectStudent.S#
6、,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=002) score2 fromStudent,SC whereStudent.S#=SC.S# andC#=001) S_2 wherescore2 60); 10 、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# groupbyStudent.S#,Student.Sname havingc
7、ount(C#) =60 THEN1 ELSE0 END)/ COUNT (*) AS 及格百分数FROM SC T,Course where t.C#=course.C# GROUP BY t.C# ORDER BY 100 * SUM ( CASE WHENisnull(score,0)=60 THEN 1 ELSE 0 END)/ COUNT (*) DESC20 、查询如下课程平均成绩和及格率的百分数( 用1 行显示 ): 企业管理（ 001 ） ，马克思（ 002 ） ， OO&UML （003 ） ，数据库（ 004 ）SELECT SUM ( CASE WHEN C# =001T
8、HEN score ELSE 0 END)/ SUM ( CASEC# WHEN 001THEN 1 ELSE 0 END) AS 企业管理平均分,100 * SUM ( CASE WHEN C# = 001AND score = 60 THEN 1 ELSE 0 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 8 页 - - - - - - - - - END)/ SUM ( CASE WHEN C# = 001THEN 1 ELSE 0 END) AS 企业管理及格百
9、分数, SUM( CASE WHENC# = 002THEN score ELSE 0 END)/ SUM ( CASE C# WHEN002THEN 1 ELSE 0 END) AS 马克思平均分,100 * SUM ( CASE WHEN C# = 002AND score = 60 THEN 1 ELSE 0 END)/ SUM ( CASE WHEN C# = 002THEN 1 ELSE 0 END) AS 马克思及格百分数, SUM( CASE WHENC# = 003THEN score ELSE 0 END)/ SUM ( CASE C# WHEN003THEN 1 ELSE
10、0 END) AS UML 平均分,100 * SUM ( CASE WHEN C# = 003AND score = 60 THEN 1 ELSE 0 END)/ SUM ( CASE WHEN C# = 003THEN 1 ELSE 0 END) AS UML 及格百分数, SUM( CASE WHENC# = 004THEN score ELSE 0 END)/ SUM ( CASE C# WHEN004THEN 1 ELSE 0 END) AS 数据库平均分,100 * SUM ( CASE WHEN C# = 004AND score = 60 THEN 1 ELSE 0 END)/
11、 SUM ( CASE WHEN C# = 004THEN 1 ELSE 0 END) AS 数据库及格百分数FROM SC 21 、查询不同老师所教不同课程平均分从高到低显示SELECTmax(Z.T#) AS 教师ID, MAX(Z.Tname) AS 教师姓名 ,C.C# AS 课程 ID, MAX(C.Cname) AS 课程名称 , AVG(Score) AS 平均成绩FROM SC AS T,Course AS C ,Teacher AS Z where T.C#=C.C# and C.T#=Z.T# GROUP BY C.C# ORDER BY AVG(Score) DESC22
12、 、查询如下课程成绩第3 名到第6 名的学生成绩单：企业管理（001 ） ，马克思（002 ） ，UML （ 003 ） ，数据库（ 004 ） 学生 ID,学生姓名 , 企业管理 ,马克思 ,UML,数据库 ,平均成绩SELECTDISTINCTtop 3 SC.S# As 学生学号 , Student.Sname AS 学生姓名 , T1.score AS 企业管理 , T2.score AS 马克思 , T3.score AS UML, T4.score AS 数据库 , ISNULL (T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.sco
13、re,0) + ISNULL (T4.score,0) as总分FROM Student,SC LEFTJOIN SC AS T1 ON SC.S# = T1.S# AND T1.C# = 001LEFTJOIN SC AS T2 ON SC.S# = T2.S# AND T2.C# = 002LEFTJOIN SC AS T3 ON SC.S# = T3.S# AND T3.C# = 003LEFTJOIN SC AS T4 ON SC.S# = T4.S# AND T4.C# = 004名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - -
14、 - - 名师精心整理 - - - - - - - 第 4 页，共 8 页 - - - - - - - - - WHERE student.S#=SC.S# andISNULL (T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.score,0) + ISNULL (T4.score,0) NOT IN( SELECTDISTINCTTOP 15 WITH TIES ISNULL (T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.score,0) + ISNULL (T4.score,0) FROM sc
15、 LEFTJOIN sc AS T1 ON sc.S# = T1.S# AND T1.C# = k1LEFTJOIN sc AS T2 ON sc.S# = T2.S# AND T2.C# = k2LEFTJOIN sc AS T3 ON sc.S# = T3.S# AND T3.C# = k3LEFTJOIN sc AS T4 ON sc.S# = T4.S# AND T4.C# = k4ORDERBYISNULL (T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.score,0) + ISNULL (T4.score,0) DESC); 23、
16、统 计 列印 各 科 成 绩, 各 分 数 段人数:课程ID,课程名称,100-85,85-70,70-60, 60SELECT SC.C# as课程 ID, Cname as课程名称, SUM( CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS100 - 85 , SUM( CASE WHENscore BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS 85 - 70 , SUM( CASE WHENscore BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS 70 -
17、 60 , SUM( CASE WHEN score T2.平均成绩 ) as名次 , S# as学生学号 , 平均成绩FROM ( SELECT S#,AVG(score) 平均成绩FROM SC GROUP BY S# 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 8 页 - - - - - - - - - ) AS T2 ORDER BY 平均成绩desc ; 25 、查询各科成绩前三名的记录:( 不考虑成绩并列情况)SELECT t1.S# as学生 ID,t1
18、.C# as课程 ID,Score as分数FROM SC t1 WHERE score IN ( SELECTTOP 3 score FROM SC WHERE t1.C#= C# ORDER BY score DESC) ORDER BY t1.C#; 26 、查询每门课程被选修的学生数select c#,count(S#) from sc groupby C#; 27 、查询出只选修了一门课程的全部学生的学号和姓名select SC.S#,Student.Sname,count(C#) AS 选课数from SC ,Student where SC.S#=Student.S# group
19、by SC.S# ,Student.Sname havingcount(C#)=1; 28 、查询男生、女生人数Selectcount(Ssex) as男生人数fromStudent groupbySsex havingSsex= 男 ; Selectcount(Ssex) as女生人数fromStudent groupbySsex havingSsex= 女 ；29 、查询姓 “ 张” 的学生名单SELECT Sname FROM Student WHERE Sname like 张% ; 30 、查询同名同性学生名单，并统计同名人数selectSname, count(*) fromStu
20、dent groupbySname havingcount(*)1; 31 、1981 年出生的学生名单( 注： Student表中 Sage 列的类型是datetime)select Sname, CONVERT( char (11),DATEPART(year ,Sage) as age from student whereCONVERT( char (11),DATEPART(year ,Sage)=1981; 32 、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列SelectC#, Avg (score) fromSC groupby C# order
21、byAvg (score),C# DESC ; 33 、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩select Sname,SC.S# ,avg (score) from Student,SC whereStudent.S#=SC.S# groupbySC.S#,Sname havingavg (score)85; 34 、查询课程名称为“ 数据库 ” ，且分数低于 60 的学生姓名和分数Select Sname,isnull(score,0) from Student,SC,Course 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - -
22、 - - - - - 名师精心整理 - - - - - - - 第 6 页，共 8 页 - - - - - - - - - where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname=数据库 and score =70 AND SC.S#=student.S#; 37 、查询不及格的课程，并按课程号从大到小排列select c# from sc where scor e 80 and C#= 003; 39 、求选了课程的学生人数selectcount(*) from sc; 40 、查询选修 “ 叶平 ” 老师所授课程的学生中，成绩
23、最高的学生姓名及其成绩select Student.Sname,score from Student,SC,Course C,Teacher where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# andTeacher.Tname= 叶平 and SC.score=(selectmax(score)from SC where C#=C.C# ); 41 、查询各个课程及相应的选修人数selectcount(*) from sc groupby C#; 42 、查询不同课程成绩相同的学生的学号、课程号、学生成绩selectdistinc
24、tA.S#,B.score fromSC A ,SC B whereA.Score=B.Score and A.C# B.C# ; 43 、查询每门功成绩最好的前两名SELECT t1.S# as学生 ID,t1.C# as课程 ID,Score as分数FROM SC t1 WHERE score IN ( SELECTTOP 2 score FROM SC WHERE t1.C#= C# ORDER BY score DESC) ORDER BY t1.C#; 44 、统计每门课程的学生选修人数（超过10 人的课程才统计） 。要求输出课程号和选修人数， 查询结果按人数降序排列，查询结果按人
25、数降序排列，若人数相同， 按课程号升序排列select C# as课程号 , count(*) as人数from sc groupby C# orderbycount(*) desc ,c# 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 8 页 - - - - - - - - - 45 、检索至少选修两门课程的学生学号select S# from sc groupby s# havingcount(*) = 2 46 、查询全部学生都选修的课程的课程号和课程名selec
26、t C#,Cname from Course where C# in ( select c# from sc groupby c#) 47 、查询没学过 “ 叶平 ” 老师讲授的任一门课程的学生姓名selectSname fromStudent whereS# notin( selectS# fromCourse,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname= 叶平 ); 48 、查询两门以上不及格课程的同学的学号及其平均成绩select S#,avg ( isnull(score,0) from SC
27、where S# in ( select S# from SC where score 2)groupby S#; 49 、检索 “ 004 ” 课程分数小于 60 ，按分数降序排列的同学学号selectS# fromSC whereC#=004and score 60 orderby score desc ; 50 、删除 “ 002 ” 同学的 “ 001 ” 课程的成绩deletefrom Sc where S#= 001and C#= 001; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 8 页 - - - - - - - - -
