2022年mysql查询语句学生表、课程表、成绩表、教师表 .pdf

《2022年mysql查询语句学生表、课程表、成绩表、教师表 .pdf》由会员分享，可在线阅读，更多相关《2022年mysql查询语句学生表、课程表、成绩表、教师表 .pdf（25页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、【引用】学生表、课程表、成绩表、教师表50 个常用 sql 语句2011-08-07 15:01:48| 分类： 数据库 | 标签： |字号大中小 订阅本文引用自大河之舟 学生表、课程表、成绩表、教师表50 个常用 sql 语句001 -Student(S#,Sname,Sage,Ssex) -学生表002 -Course(C#,Cname,T#) -课程表003 -SC(S#,C#,score) -成绩表004 -Teacher(T#,Tname) -教师表005006create table Student(S# varchar (20),Sname varchar (10),Sage i

2、nt ,Ssex varchar (2) 007 - 前面加一列序号：008 if 009 exists(selecttable_name from information_schema.tables 010wheretable_name=Temp_Table ) 011 drop table Temp_Table 012 go 013 select排名=identity(int ,1,1),* INTO Temp_Table from Student 014 go 015 select* from Temp_Table 016 go 017018 drop database - 删除空的没有

3、名字的数据库019 - 问题：020 -1 、查询“”课程比“”课程成绩高的所有学生的学号；021selecta.S# from ( selects#,score from SC where C#= 001 ) a,( selects#,score 022from SC whereC#= 002 ) b 023wherea.scoreb.score anda.s#=b.s#; 024025 -2 、查询平均成绩大于分的同学的学号和平均成绩；026selectS#,avg(score) 027from sc 028group byS# having avg(score) 60; 029030 -

4、3 、查询所有同学的学号、姓名、选课数、总成绩；名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 25 页 - - - - - - - - - 031selectStudent.S#,Student.Sname,count (SC.C#), sum (score) 032from Student leftOuter join SC onStudent.S#=SC.S# 033group by Student.S#,Sname 034035 -4 、查询姓“李”的老师的个数；

5、036selectcount ( distinct(Tname) 037from Teacher 038whereTname like 李%; 039040 -5 、查询没学过“叶平”老师课的同学的学号、姓名；041selectStudent.S#,Student.Sname 042from Student 043where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# andTeacher.T#=Course.T# andTeacher.Tname=叶平 ); 044045 -6

6、 、查询学过“”并且也学过编号“”课程的同学的学号、姓名；046selectStudent.S#,Student.Sname from Student,SC whereStudent.S#=SC.S# andSC.C#= 001 andexists( Select * from SC as SC_2 whereSC_2.S#=SC.S# andSC_2.C#= 002 ); 047048 -7 、查询学过“叶平”老师所教的所有课的同学的学号、姓名；049selectS#,Sname 050from Student 051whereS# in (selectS# from SC ,Course

7、,Teacher where SC.C#=Course.C# andTeacher.T#=Course.T# andTeacher.Tname= 叶平 group by S# havingcount (SC.C#)=( selectcount (C#) from Course,Teacher whereTeacher.T#=Course.T# andTname= 叶平 ); 052053-8 、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；054Select S#,Sname from (selectStudent.S#,Student.Sname,score ,(sele

8、ctscore from SC SC_2 whereSC_2.S#=Student.S# andSC_2.C#= 002 ) score2 055from Student,SC whereStudent.S#=SC.S# andC#= 001 ) S_2 wherescore2 60); 061062 -10 、查询没有学全所有课的同学的学号、姓名；063selectStudent.S#,Student.Sname 064from Student,SC 065where Student.S#=SC.S# group by Student.S#,Student.Sname havingcount

9、 (C#) =60 THEN 1 ELSE 0 END )/ COUNT(*) AS及格百分数122FROM SC T,Course 123where t.C#=course.C# 124GROUP BYt.C# 125ORDER BY100 * SUM ( CASE WHEN isnull(score,0)=60 THEN 1 ELSE0 END )/ COUNT(*) DESC126127-20 、查询如下课程平均成绩和及格率的百分数( 用1 行 显示): 企业管理（），马克思（）， OO&UML （），数据库（）128SELECT SUM ( CASE WHEN C# =001THEN

10、score ELSE0 END )/ SUM ( CASE C# WHEN 001THEN 1 ELSE 0 END ) AS企业管理平均分129 ,100 * SUM ( CASE WHEN C# = 001AND score = 60 THEN 1 ELSE0 END )/ SUM (CASE WHEN C# = 001THEN 1 ELSE0 END ) AS企业管理及格百分数130 ,SUM ( CASE WHEN C# = 002THEN score ELSE0 END )/ SUM ( CASE C# WHEN 002THEN 1 ELSE 0 END ) AS马克思平均分131

11、,100 * SUM ( CASE WHEN C# = 002AND score = 60 THEN 1 ELSE0 END )/ SUM (CASE WHEN C# = 002THEN 1 ELSE0 END ) AS马克思及格百分数132 ,SUM ( CASE WHEN C# = 003THEN score ELSE0 END )/ SUM ( CASE C# WHEN 003THEN 1 ELSE 0 END ) ASUML平均分133 ,100 * SUM ( CASE WHEN C# = 003AND score = 60 THEN 1 ELSE0 END )/ SUM (CASE

12、 WHEN C# = 003THEN 1 ELSE0 END ) ASUML 及格百分数134 ,SUM ( CASE WHEN C# = 004THEN score ELSE0 END )/ SUM ( CASE C# WHEN 004THEN 1 ELSE 0 END ) AS数据库平均分135 ,100 * SUM ( CASE WHEN C# = 004AND score = 60 THEN 1 ELSE0 END )/ SUM (CASE WHEN C# = 004THEN 1 ELSE0 END ) AS数据库及格百分数136FROM SC 137138 -21 、查询不同老师所教

13、不同课程平均分从高到低显示139SELECT max (Z.T#) AS教师 ID, MAX (Z.Tname) AS教师姓名 ,C.C# AS课程, MAX (C.Cname) AS课程名称 , AVG (Score) AS平均成绩140FROM SC AST,Course ASC ,Teacher ASZ 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 25 页 - - - - - - - - - 141where T.C#=C.C# andC.T#=Z.T# 142

14、GROUP BYC.C# 143ORDER BYAVG (Score) DESC144145-22 、查询如下课程成绩第名到第名的学生成绩单：企业管理（），马克思（）， UML （），数据库（）146 学生 ID,学生姓名 , 企业管理 , 马克思 ,UML,数据库 , 平均成绩147SELECTDISTINCTtop 3 148 SC.S# As 学生学号 , 149 Student.Sname AS学生姓名 , 150 T1.score AS企业管理 , 151 T2.score AS马克思 , 152 T3.score ASUML, 153 T4.score AS数据库 , 154ISN

15、ULL (T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.score,0) + ISNULL (T4.score,0) as 总分155FROM Student,SC LEFTJOINSC AST1 156ONSC.S# = T1.S# AND T1.C# = 001157LEFTJOINSC AST2 158ONSC.S# = T2.S# AND T2.C# = 002159LEFTJOINSC AST3 160ONSC.S# = T3.S# AND T3.C# = 003161LEFTJOINSC AST4 162ONSC.S# = T4.S#

16、 AND T4.C# = 004163WHEREstudent.S#=SC.S# and164ISNULL (T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.score,0) + ISNULL (T4.score,0) 165NOT IN166 (SELECT167DISTINCT168TOP15 WITH TIES 169ISNULL (T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.score,0) + ISNULL (T4.score,0) 170FROM sc 171LEFTJOINsc AST

17、1 172ONsc.S# = T1.S# AND T1.C# = k1名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 25 页 - - - - - - - - - 173LEFTJOINsc AST2 174ONsc.S# = T2.S# AND T2.C# = k2175LEFTJOINsc AST3 176ONsc.S# = T3.S# AND T3.C# = k3177LEFTJOINsc AST4 178ONsc.S# = T4.S# AND T4.C# = k4

18、179ORDER BYISNULL (T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.score,0) + ISNULL (T4.score,0) DESC ); 180181-23 、统计列印各科成绩 , 各分数段人数 : 课程 ID, 课程名称,100-85,85-70,70-60, 60 182SELECT SC.C# as 课程 ID, Cname as 课程名称183 ,SUM ( CASE WHEN score BETWEEN85 AND 100 THEN 1 ELSE 0 END ) AS100 - 85 184 ,SUM ( CAS

19、E WHEN score BETWEEN70 AND 85 THEN 1 ELSE0 END ) AS85 - 70 185 ,SUM ( CASE WHEN score BETWEEN60 AND 70 THEN 1 ELSE0 END ) AS70 - 60 186 ,SUM ( CASE WHEN score T2. 平均成绩 ) as 名次, 198 S# as 学生学号 , 平均成绩199FROM ( SELECT S#,AVG (score) 平均成绩200FROM SC 201GROUP BYS# 202 ) AST2 203ORDER BY平均成绩 desc; 204名师资料总

20、结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 25 页 - - - - - - - - - 205 -25 、查询各科成绩前三名的记录:( 不考虑成绩并列情况 ) 206SELECT t1.S# as 学生 ID,t1.C# as 课程 ID,Score as 分数207FROM SC t1 208WHEREscore IN (SELECT TOP3 score 209FROM SC 210WHEREt1.C#= C# 211ORDER BYscore DESC212 ) 213

21、ORDER BYt1.C#; 214215 -26 、查询每门课程被选修的学生数216selectc#, count (S#) from sc group by C#; 217218 -27 、查询出只选修了一门课程的全部学生的学号和姓名219selectSC.S#,Student.Sname, count (C#) AS选课数220from SC ,Student 221whereSC.S#=Student.S# group by SC.S# ,Student.Sname havingcount (C#)=1; 222223 -28 、查询男生、女生人数224Select count (Ss

22、ex) as 男生人数 from Student group by Ssex havingSsex= 男 ; 225Select count (Ssex) as 女生人数 from Student group by Ssex havingSsex= 女 ；226227 -29 、查询姓“张”的学生名单228SELECT Sname FROM Student WHERE Sname like 张%; 229230 -30 、查询同名同性学生名单，并统计同名人数231selectSname, count (*) from Student group by Sname havingcount (*)

23、1; 232233 -31 、年出生的学生名单 ( 注：Student 表中 Sage列的类型是 datetime) 234selectSname, CONVERT( char (11),DATEPART( year ,Sage) asage 235from student 236where CONVERT(char (11),DATEPART( year ,Sage)= 1981 ; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 25 页 - - - - - - -

24、- - 237238-32 、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列239Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ; 240241 -33 、查询平均成绩大于的所有学生的学号、姓名和平均成绩242selectSname,SC.S# ,avg(score) 243from Student,SC 244where Student.S#=SC.S# group by SC.S#,Sname havingavg(score)85; 245246 -34 、查询课程

25、名称为“数据库”，且分数低于的学生姓名和分数247Select Sname, isnull(score,0) 248from Student,SC,Course 249where SC.S#=Student.S# andSC.C#=Course.C# and Course.Cname= 数据库 andscore =70 AND SC.S#=student.S#; 260261 -37 、查询不及格的课程，并按课程号从大到小排列262selectc# from sc where scor e 80 andC#= 003 ; 266267 -39 、求选了课程的学生人数268selectcount

26、 (*) from sc; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 25 页 - - - - - - - - - 269270-40 、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩271selectStudent.Sname,score 272from Student,SC,Course C,Teacher 273where Student.S#=SC.S# andSC.C#=C.C# and C.T#=Teacher.T# andTeache

27、r.Tname= 叶平 andSC.score=( selectmax (score) from SC whereC#=C.C# ); 274275 -41 、查询各个课程及相应的选修人数276selectcount (*) from sc group by C#; 277278 -42 、查询不同课程成绩相同的学生的学号、课程号、学生成绩279selectdistinctA.S#,B.score from SC A ,SC B where A.Score=B.Score andA.C# B.C# ; 280281 -43 、查询每门功成绩最好的前两名282SELECT t1.S# as 学生

28、 ID,t1.C# as 课程 ID,Score as 分数283FROM SC t1 284WHEREscore IN (SELECT TOP2 score 285FROM SC 286WHEREt1.C#= C# 287ORDER BYscore DESC288 ) 289ORDER BYt1.C#; 290291-44 、统计每门课程的学生选修人数（超过人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列292selectC# as 课程号 , count (*) as 人数293from sc 294group by

29、C# 295orderby count (*) desc,c# 296297 -45 、检索至少选修两门课程的学生学号298selectS# 299from sc 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 25 页 - - - - - - - - - 300group by s# 301havingcount (*) = 2 302303 -46 、查询全部学生都选修的课程的课程号和课程名304selectC#,Cname 305from Course 306wh

30、ere C# in(selectc# from sc group by c#) 307308 -47 、查询没学过“叶平”老师讲授的任一门课程的学生姓名309selectSname from Student whereS# not in (selectS# fromCourse,Teacher,SC where Course.T#=Teacher.T# andSC.C#=course.C# andTname= 叶平 ); 310311 -48 、查询两门以上不及格课程的同学的学号及其平均成绩312selectS#,avg( isnull(score,0) from SC where S# in

31、 ( selectS# fromSC wherescore 2) group byS#; 313314 -49 、检索“”课程分数小于，按分数降序排列的同学学号315selectS# from SC whereC#= 004 andscore 60 order by score desc; 316317 -50 、删除“”同学的“”课程的成绩318 delete from Sc whereS#=001 and C#= 001 ; 319320 - 学生表 (学号、姓名、性别、年龄、所在系) 321 - 课程表 (课程号、课程名、先修课号、学分) 322 - 学生选课表 ( 学号、课程号、成绩

32、) 323324 -1 ：查询全体学生的学号和姓名325326 -2 ：查询全体学生的姓名、学号和所在系327328 -3: 查询全体学生的详细记录329330 -4: 查询全体学生的姓名及其出生年份331332 -5 ：查询全体学生姓名、出生年份和所在系，要求用小写字母表示所有系名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 25 页 - - - - - - - - - 名333334 -6 ：查询选修了课程的学生学号335336 -7 ：查询计算机系 (IS) 所有

33、学生的名单337338 -8 ：查询所有年龄在20 以下学生的姓名和年龄339340 -9: 查询考试成绩有不及格的学生的学号341342 -10: 查询年龄在 20-23 （包括 20 和 23）之间的学生的姓名、 系别和年龄343344-11: 查询信息系（ IS）、数学系（ MA ）和计算机科学系（ CS ）学生的姓名和性别345346 -12: 查询学号为 95001的学生的详细情况347348 -13: 查询所有姓林的学生的姓名、学号和性别349350 -14: 查询姓“欧阳”且全名为三个汉字的学生的姓名351352 -15 ：查询名字中第二个字为“燕”字的学生姓名和学号353354

34、 -16 ：查询所有不姓“刘”的学生的姓名355356 -17 ：查询课程名为“ DB_DESIGN”的课程号的学分357358 -18 ：查询缺少成绩的学生的学号和相应的课程号（成绩字段值为Null ）359360 -19: 查询所有有成绩的学生的学号和课程号361362 -20: 查询所有计算机系年龄在20 以下的学生姓名363364 -21: 查询选修了 3 号课程的学生的学号和成绩， 查询结果按分数降序排列365366 -22: 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - -

35、- - - - 名师精心整理 - - - - - - - 第 12 页，共 25 页 - - - - - - - - - 学生按年龄降序排列367368 -23: 查询学生总人数369370 -24: 查询选修了课程的学生人数371372 -25: 计算 1号课程的学生的平均成绩373374 -26: 计算 1号课程的学生的最高成绩分数375376 -27 ：求各个课程号及相应的选课人数377378 -28: 查询选修了三门以上课程的学生学号379380 -29 ：查询每个学生及其选修课情况381382 -30 ：查询每一门课的间接先行课383384 -31 ：选修 2号课程且成绩在90 以上

36、的学生的学号和姓名385386 -32 ：查询每个学生的学号、姓名、选修的课程名及成绩387388 -33 ：查询与林燕芳在同一个系学习的学生姓名389390 -34: 查询其他系中比信息系某一学生小的学生姓名和年龄391392 -35 ：查询所有选修了1 号课程的学生的学生姓名393394 -36 ：查询选修了全部课程的学生姓名395396 -37 ：至少选修了学生95002选修全部课程的学生号码397398 selectSno,Sname from Student 399400 selectSno,Sname,Sdept from Student 401名师资料总结 - - -精品资料欢迎

37、下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 25 页 - - - - - - - - - 402 select* from Student /*也可以逐一列出列名并用逗号分隔*/ 403404- 其中 getdate 是获取当前系统时间。 这是一个获取到的结果： 2008-12-11 16:02:17.967 405406-datepart从获取到的系统时间中分离出需要的部分，这里是分离出年份，更多信息请查看 SQL Server 联机帮助407408 - 下面的 出生年 指定了别名来替代原来结果页出

38、现的文字409410selectSname , (datepart( year ,getdate()- Sage) 出生年 fromStudent 411412- 该实例利用了小写转换函数lower() 提示：通过查询分析器的公用对象的 字符串函数中你可以找到这个函数413414selectSname 姓名 , (datepart( year ,getdate()- Sage) 出生年 , lower (Sdept) 所在系 from Student 415416 selectSno from sc - 这里将返回全部结果，有重复的值417418selectdistinctSno from s

39、c- 加入关键字 distinct就可以去除重复结果，只留 1 个419420-sql 中默认对字符串大小写不敏感的， 所以下面的 sdept=IS你也可以写成 sdept=is 。如果你要启用大小写敏感，可以用下面的方法421422if 敏感423select* fromtablewhere field1=AAA COLLATEChinese_PRC_CS_AS 424else425select* fromtablewhere field1=AAA COLLATEChinese_PRC_CI_AS 426COLLATE的中文排序规则参数可用下列方式查到427SELECT* FROM:fn_h

40、elpcollations() where name likeChinese%428名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 25 页 - - - - - - - - - 429 selectSname from student wheresdept=IS430431 selectSname,Sage from student where Sage20 432433 selectSno from sc where grade60 434435 - 如果要查询不在这

41、个区间的记录，那只要改成 not between就可以了436437 selectSname,Sdept,Sage from student whereSage between 20 and23 438439- 如果要查询不属于信息系、数学系和计算机科学系的，可以在in 前面加上 NOT 440441- 也可以这样子写： select Sname,Ssex from student where Sdept=is or sdept=ma or sdept=cs 442443 selectSname,Ssex from student where Sdept in ( IS , MA , CS )

42、 444445 - 或者是 select * from student where sno = 95001 446447 select* from student where sno like95001 ：-like用于字符串匹配448449 - 百分号匹配多个字符，包括0 个450451 selectSname,Sno,Sage from student wheresname like 林%452453 - 一个下划线匹配单个字符454455 selectsname from student wheresname like 欧阳_456457 selectsname,sno from stu

43、dent wheresname like_ 燕%458459 selectsname from student wheresname not like 刘%460461 selectCcredit from course where cname likeDB_DESIGNescape 462名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 25 页 - - - - - - - - - 463 - 注意：这里不用使用 = null 464465 select sno,cno

44、 from sc where grade is null 466467 select sno,cno from sc where grade is not null 468469 select sname from student where sdept=CS and sage=3 492493 select a.sname,o from student a ,sc b where a.sno=b.sno 494495 或者496497 select a.sname,o from student a left outer join sc b 名师资料总结 - - -精品资料欢迎下载 - - -

45、 - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 25 页 - - - - - - - - - 498499 on a.sno=b.sno where o is not null 500501 - 自身连接502503 select a.Cno,b.Cpno from course a,course b where a.Cpno=b.Cno 504505 -31 ：506507 select student.sno,student.sname 508509 from student,sc 510511 where stud

46、ent.sno=sc.sno and 512513 o= 2 and 514515 sc.grade=90 516517 -32 ：518519 select student.sno,student.sname,ame,sc.grade 520521 from (student left join sc on student.sno=sc.sno) 522523 left join course on o=o 524525 - 或者：526527 - 忽略 cname和 grade 都为 null的行528529 Select student.sno,sname,cname,grade 530

47、531 From student,sc,course 532533 Where student.sno=sc.sno and o=o 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 25 页 - - - - - - - - - 534535 -33 ：536537 select sname from student 538539 where sdept=(select sdept from student where sname=林燕芳 ) 540541 -34 ：54

48、2543 select sname,sage 544545 from student 546547 where sageany( 548549 select sage from student 550551 where sdept= is 552553 ) and sdeptIS 554555 -35 ：利用 exists的查询556557 -exists根据是否存在行返回true/false 558559 - 如果要查询没有选修1 号课程的学生姓名，只要使用NOT Exists 即可560561 select * 562563 from student 564565 where exists

49、( 566567 select 1 from sc 568569 where student.sno=sc.sno and cno=1 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 25 页 - - - - - - - - - 570571 ) 572573 - 或者你可以使用连接查询574575 select * from student left join sc on student.sno=sc.sno 576577 where o=1 578579 -36 ：

50、580581 declare temp1 int 582583 declare temp2 int 584585 select temp1=count(*) from course 586587 select temp2=sno from sc group by sno 588589 having count(sno)=temp1 590591 select sname from student where sno in (temp2) 592593 - 或者594595 - 就是转换成查询没有一门课程没有选修的学生姓名596597 - 如果把两个 not 都去掉就是查询所有有选修课程的学生5