sql思维训练.docx

写sql是程序员基本功课,找工作面试一般必有sql题,实际工作中对sql的需求更是千变万化,所以掌握好sql对于一个程序员来说是件非常重要的事情.本文通过一个简单易懂的关系(学生/课程/成绩关系)尽量构造出各种各样的需求来提高我们写sql的功底,其中有些sql还是有一些难度的,大多数sql我都给出了思考思路,希望本人N天的劳动成果对您有所帮助. 限于本人水平有限,有些地方的sql写的并不完美,希望大家不吝赐教.最后提一点小小的要求,希望大家对本文积极进行评论,大家的评论是我改进和提高的动力,我希望在和大家的交流中得到提高.表关系:建表和初始化sql:1. -创建表2. createtableT_STUDENT(snoNUMBERnotnull,snameVARCHAR2(30),sdreeVARCHAR2(50),sageNUMBER,ssexCHAR(2);3. altertableT_STUDENTaddprimarykey(SNO);4. createtableT_SCORE(snoNUMBER,cnoNUMBER,gradeNUMBER(4,1),tnoNUMBER,idNUMBERnotnull);5. altertableT_SCOREaddprimarykey(ID);6. createtableT_COURSE(cnoNUMBERnotnull,cnameVARCHAR2(30);7. altertableT_COURSEaddprimarykey(CNO);8. 9. -初始化学生表10. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(1,李坤,天融信,26,男);11. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(2,曹贵生,中银,26,男);12. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(3,柳波,淘宝,27,男);13. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(4,纪争光,IBM,23,男);14. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(5,李学宇,微软,25,女);15. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(6,李雪琪,文思,25,女);16. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(7,陈绪,百度,26,男);17. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(8,韩正阳,中海油,24,男);18. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(9,陈伟东,腾讯,24,男);19. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(10,刘兵,华为,24,男);20. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(11,丁成云,联想,25,女);21. insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(12,王鹏,中兴,25,男);22. commit;23. 24. -初始化课程表25. insertintoT_COURSE(CNO,CNAME)values(1,JAVA程序设计);26. insertintoT_COURSE(CNO,CNAME)values(2,ORACLE开发);27. insertintoT_COURSE(CNO,CNAME)values(3,C+程序设计);28. insertintoT_COURSE(CNO,CNAME)values(4,C#程序设计);29. insertintoT_COURSE(CNO,CNAME)values(5,Windows实战);30. insertintoT_COURSE(CNO,CNAME)values(6,CenterOS教程);31. insertintoT_COURSE(CNO,CNAME)values(7,Jsp/Servlet开发);32. insertintoT_COURSE(CNO,CNAME)values(8,J2EE从入门到精通);33. insertintoT_COURSE(CNO,CNAME)values(9,EJB及设计模式);34. insertintoT_COURSE(CNO,CNAME)values(10,Javascript/jQuery实战);35. insertintoT_COURSE(CNO,CNAME)values(11,Flash设计);36. insertintoT_COURSE(CNO,CNAME)values(12,HTML/CSS/JAVASCRIPT实战);37. insertintoT_COURSE(CNO,CNAME)values(13,精通ASP.NET);38. insertintoT_COURSE(CNO,CNAME)values(14,JBoss入门);39. insertintoT_COURSE(CNO,CNAME)values(15,Spring开发);40. commit;41. 42. -初始化成绩表43. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(1,2,90.0,2,1);44. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(1,3,80.0,3,2);45. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(1,4,90.0,4,3);46. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,2,70.0,2,4);47. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(2,11,66.0,11,5);48. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(2,15,77.0,15,6);49. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(2,8,87.0,8,7);50. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(2,6,96.0,6,8);51. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(3,2,89.0,2,9);52. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(3,1,91.0,1,10);53. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(4,2,83.0,2,11);54. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(5,4,73.0,4,12);55. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(5,1,60.0,1,13);56. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(5,8,82.0,8,14);57. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(6,8,90.5,10,15);58. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(8,2,58.0,2,16);59. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,3,80.0,3,17);60. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(9,11,65.0,11,18);61. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(9,12,67.0,12,19);62. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(9,15,95.0,15,20);63. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(9,13,59.0,13,21);64. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(10,4,98.0,4,22);65. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(10,6,97.0,6,23);66. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(10,7,96.0,7,24);67. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,7,95.0,7,25);68. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(12,8,69.0,8,26);69. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(12,9,85.0,9,27);70. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(10,14,100.0,14,28);71. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(6,9,100.0,9,29);72. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,1,59.0,1,30);73. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,4,90.0,4,31);74. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,5,91.0,5,32);75. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,6,58.0,6,33);76. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,8,93.0,8,34);77. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,9,57.0,9,35);78. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,10,95.0,10,36);79. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,11,96.0,11,37);80. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,12,97.0,12,38);81. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,13,98.0,13,39);82. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,14,99.0,14,40);83. insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,15,89.0,15,41);84. commit;在思维举重前,先来一个热身运动吧:1. -(一)查询选修课程名称为JAVA程序设计的学员学号和姓名2. -方法一:3. -1.查询JAVA程序设计这门课程的课程号4. -2.在成绩表中查询课程号为步骤1的课程号的成绩信息.5. -3.在学生表中查询学号为步骤2结果的学生信息6. selectst.sno,st.sname7. fromstudentst8. wherest.snoin9. (selectsno10. fromscoresc11. o=12. (ame=JAVA程序设计);13. 14. -方法二:15. -1.查询JAVA程序设计这门课程的课程号16. -2.在成绩表和学生表关联结果中查询课程号等于步骤1的课程号的成绩信息.17. -sql8618. selectst.sno,st.sname19. fromstudentst,scoresc20. wherest.sno=sc.sno21. o=22. (ame=JAVA程序设计);23. 24. -sql9225. selectst.sno,st.sname26. fromstudentst27. joinscoresc28. onst.sno=sc.sno29. o=30. (ame=JAVA程序设计);31. 32. -方法三:33. -1.在成绩表和课程表关联结果中查询选修了JAVA程序设计这门课的学生的学号34. -2.在学生表中查询步骤1中的学号的学生的详细信息.35. -sql8636. selectst.sno,st.sname37. fromstudentst38. wherest.snoin(selectsc.sno39. fromscoresc,courseco40. o=o41. ame=JAVA程序设计);42. 43. -sql9244. selectst.sno,st.sname45. fromstudentst46. wherest.snoin(selectsc.sno47. fromscoresc48. joincourseco49. o=o50. ame=JAVA程序设计);51. 52. -方法四:53. -在成绩表和课程表和学生表三表关联的结果中过滤得到选修了JAVA程序设计的学生基本信息54. -sql8655. selectst.sno,st.sname56. fromstudentst,scoresc,courseco57. wherest.sno=sc.sno58. o=o59. ame=JAVA程序设计;60. 61. -sql92(1)用where过滤62. selectst.sno,st.sname63. fromstudentst64. joinscoresc65. onst.sno=sc.sno66. joincourseco67. o=o68. ame=JAVA程序设计;69. 70. -sql92(2)在关联条件中过滤71. selectst.sno,st.sname72. fromstudentst73. joinscoresc74. onst.sno=sc.sno75. joincourseco76. o=o77. ame=JAVA程序设计;78. 79. 注:1.对于sql86和sql92的区别见这篇文章:/lk_blog/article/details/758030080. 2.如果您在看的过程中觉得不熟悉的地方很多,建议您先看此文:/lk_blog/article/details/7585501进入正文:1. -(一)查询不选修课程编号为1的学员姓名和所属单位2. -1.在成绩表中查询课程号为1的所有学生学号3. -2.在学生表中查询学号不在步骤1中的学生的基本信息.4. selectst.sname,st.sdree5. fromt_studentst6. wherest.snonotin(selectsc.snofromt_o=1);7. 8. 9. -(二)查询平均成绩大于85的所有学生的学号、姓名和平均成绩?10. selectsc.sno,st.sname11. fromt_scoresc12. joint_studentst13. onsc.sno=st.sno14. groupbysc.sno,st.sname15. havingavg(sc.grade)85;16. 17. 18. -(三)查询课程名称为JAVA程序设计,且分数低于60的学生姓名和分数19. select*20. fromt_scoresc21. joint_courseco22. o=o23. wheresc.grade60;24. 25. select*26. fromt_scoresc27. joint_courseco28. o=o29. andsc.grade60;30. 31. 32. -(四)查询任何一门课程成绩全部都在70分以上的姓名、课程名称和分数?33. -1.查询出成绩小于70分的学生的学号.34. -2.将学生,成绩,课程三张表作关联.35. -3.在关联表中过滤出不在步骤1查询结果中的学生信息.36. selectst.sname,ame,sc.grade37. fromt_studentst38. joint_scoresc39. onst.sno=sc.sno40. joint_courseco41. o=o42. wherest.snonotin(ofromt_scoresc1wheresc1.grade5);86. 87. 88. -(十)查询出没有选课的学生基本信息89. -1.在成绩表中查询出所有选过课的学生的学号.90. -2.在学生表中查询出步骤1中学生的基本信息.91. select*92. fromt_studentst93. wherest.snonotin(selectsc.snofromt_scoresc);94. 95. -下面的两个sql等价,在成绩表中数据量很大时使用下面的sql96. select*97. fromt_studentst98. wherest.snonotin(selectdistinct(sc.sno)fromt_scoresc);99. 100. select*101. fromt_studentst102. wherest.snonotin(selectsc.snofromt_scorescgroupbysc.sno);103. 104. 105. -(十一)列出有二门以上不及格课程的学生姓名及其平均成绩106. -方法一107. -1.在成绩表中查询出2门不及格学生的学号,结果记作t1108. -2.将学生表和t1和成绩表三表作关联得到关联表,在关联表中取学生基本信息和平均成绩.109. -sql92110. selectst.sno,st.sname,avg(sc.grade)111. fromt_studentst112. join(selectsc.sno113. fromt_scoresc114. wheresc.grade2)t1117. onst.sno=t1.sno118. joint_scoresc119. onsc.sno=t1.sno120. groupbyst.sno,st.sname;121. 122. -sql86123. selectst.sno,st.sname,avg(sc.grade)124. fromt_studentst,125. t_scoresc,126. (selectsc.sno127. fromt_scoresc128. wheresc.grade2)t1131. wherest.sno=t1.sno132. andsc.sno=t1.sno133. groupbyst.sno,st.sname;134. 135. -方法二:136. -1.在成绩表中查询出2门不及格学生的学号137. -2.将学生表和成绩表通过学号作关联并根据步骤1中的结果作过滤,在关联结果中取出学生基本信息和平均成绩138. selectst.sno,st.sname,avg(sc.grade)139. fromt_studentst140. joint_scoresc141. onst.sno=sc.sno142. wherest.snoin(selectsc.sno143. fromt_scoresc144. wheresc.grade2)147. groupbyst.sno,st.sname;148. 149. 150. -(十二)查询平均成绩大于60分的同学的学号和平均成绩151. -学生表和课程表关联,在having子句中过滤平均成绩大于60分.152. selectst.sno,avg(sc.grade)153. fromt_studentst,t_scoresc154. wherest.sno=sc.sno155. groupbyst.sno156. havingavg(sc.grade)60;157. 158. -1.学生表和课程表关联,将关联的结果记作t1159. -2.在t1中过滤平均成绩大于60的学生学号.160. selectt1.sno,t1.avg_grade161. from(selectst.sno,avg(sc.grade)avg_grade162. fromt_studentst,t_scoresc163. wherest.sno=sc.sno164. groupbyst.sno)t1165. wheret1.avg_grade60;166. 167. 168. -(十三)查询出只选修了一门课程的全部学生的学号和姓名169. -方法一:170. -1.将学生表和成绩表作关联,在分组函数中使用having子句过滤出只选了一门课程的学生基本信息.171. selectsc.sno,st.sname172. fromt_scoresc173. joint_studentst174. onsc.sno=st.sno175. groupbysc.sno,st.sname176. havingcount(o)=1;177. 178. -方法二:179. -1.在成绩表中查找学号,分组函数的过滤条件判断只选择了一门课程的学生.180. -2.在学生表中查找学号在步骤1中的值的学生的基本信息181. selectst.sno,st.sname182. fromt_studentst183. wherest.snoin(selectsc.sno184. fromt_scoresc185. groupbysc.sno186. havingcount(o)=1);187. 188. 189. 190. -(十四)查询至少有一门课与学号为1的同学所学相同的同学的学号和姓名191. selectst.sno,st.sname192. fromt_studentst193. joint_scoresc1194. onst.sno=sc1.sno195. oin(ofromt_scorescwheresc.sno=1)196. groupbyst.sno,st.sname;197. 198. 199. 200. -(十五)列出既学过1号课程,又学过2号课程的所有学生姓名201. -1.将成绩表和课程表作关联,在关联条件中作过滤查询出既选过课程1又选过课程2的学生的学号,注意看oin(1,2)和havingcount(o)=2的位置.202. -2.在学生表中根据步骤1的结果作过滤查询出学生的基本信息.203. -方法一:204. -sql86205. selectst.sno,st.sname206. fromt_studentst,207. (selectsc.sno208. fromt_scoresc,t_courseco209. o=o210. oin(1,2)211. groupbysc.sno212. havingcount(o)=2)t1213. wherest.sno=t1.sno;214. 215. -sql92216. selectst.sno,st.sname217. fromt_studentstjoin218. (selectsc.sno219. fromt_scorescjoint_courseco220. o=o221. oin(1,2)222. groupbysc.sno223. havingcount(o)=2)t1224. onst.sno=t1.sno;225. 226. -方法二:227. -sql86228. selectst.sno,st.sname229. fromt_studentst230. wherest.snoin(selectsc.sno231. fromt_scoresc,t_courseco232. o=o233. oin(1,2)234. groupbysc.sno235. havingcount(o)=2);236. 237. -sql92238. selectst.sno,st.sname239. fromt_studentst240. wherest.snoin(selectsc.sno241. fromt_scoresc242. joint_courseco243. o=o244. oin(1,2)245. groupbysc.sno246. havingcount(o)=2);247. 248. 249. 250. -(十六)查询至少学过学号为1的同学所有门课的同学学号和姓名251. -1.查询出1号同学学习的全部课程.252. -2.查询出1号同学学习全部课程的数量.253. -3.将课程表和成绩表做关联,在关联表中查询出学生的学号,关联条件中加入过滤条件课程号在步骤1查询结果范围内,过滤条件中加入数量等级步骤2中得到的