天津理工大学数据库期末复习――SQL语句汇总

1.建表：（1）创建学生表Student:CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,SnameCHAR(20)UNIQUE,SsexCHAR(2),SsageSMALLINT,SdeptCHAR(20));（2）创建课程表:COURSECREATETABLECOURSE(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40)NOTNULL,CpnoCHAR(4),CcreditSMALLINT,FOREIGNKEY(Cpno)REFERENCESCourse(Cno));（3）创建学生-选课表SCCREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Cno)REFERENCESCOURSE(Cno));2．基本SQL数据操作：（1）查询信息系和计算机系的学生，并按学生所在系和学号排序。SELECT*FROMStudentWHERESdept='IS'ORSdept='CS'ORDERBYSdept,Sno;（2）查询学生表中最小的年龄。SELECTMIN(Ssage)FROMStudent;（3）查询课程名中包含“设计”的课程名。SELECTCnameFROMCOURSEWHERECnameLIKE'%设计%';（4）查询先行课程为空值的课程号、课程名及学分。SELECTCno,Cname,CcreditFROMCOURSEWHERECpnoisNULL;（5）查询李勇选修的数据库课程的成绩。SELECTGradeFROMStudent,COURSE,SCWHEREStudent.Sno=SC.SnoANDCOURSE.Cno=SC.CnoANDStudent.Sname='李勇'ANDCOURSE.Cname='数据库';（6）查询平均成绩80分以上的学生的学号。SELECTSnoFROMSCGROUPBYSnoHAVINGAVG(Grade)80;（7）求计算机系没有选修数据库课程的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDNOTEXISTS(SELECT*FROMCOURSE,SCWHEREStudent.sno=SC.SnoANDCOURSE.Cno=SC.CnoANDCourse.Cname='数据库');（8）求至少选修了学号为20152201所选修的全部课程的学生学号。SELECTStudent.*,SC.*FROMStudent,SCWHERESC.Cno=(SELECTCnoFROMSCWHERESno='20152201')ANDSC.Sno=Student.Sno（9）求各系的系的学生人数的，并将结果按学生人数的降序排序。SELECTSdept,COUNT(Sno)FROMStudentGROUPBYSdeptORDERBYCOUNT(Sno)DESC;（10）查询选修了数据库课程并且成绩高于该门课程平均分的学生学号和成绩。SELECTSno,GradeFROMSCWHEREGRADE(SELECTAVG(Grade)FROMSCWHERECno=(SELECTCnoFROMCOURSEWHERECname='数据库'))ANDCno=(SELECTCnoFROMCOURSEWHERECname='数据库');3.复杂SQL数据操作：(1)将学习了数据库课程的学生成绩加5分。UPDATESCSETGrade=Grade+5WHERECno=(SELECTCnoFROMCOURSEWHERECname='数据库');(2)将计算机系学习了2号课程的学生成绩置0。UPDATESCSETGrade=0WHERESnoIN(SELECTSnoFROMStudentWHERESdept='CS'ANDCno='2');(3)将李勇的数据库成绩改为85。UPDATESCSETGrade=85WHERESnoIN(SELECTSnoFROMStudentWHERESname='李勇')ANDCnoIN(SELECTCnoFROMCOURSEWHERECname='数据库');(4)将选修了2号课程且成绩为空的选课记录删除。DELETEFROMSCWHERECno='2'ANDGRADE=0;(5)从课程表中删除在选课表中没有选课记录的课程记录。DELETEFROMCOURSEWHERECnoNOTIN(SELECTCnoFROMSC);(6)删除计算机系学生选修了数据库课程的选课记录。DELETEFROMSCWHERECnoIN(SELECTCnoFROMCOURSEWHERECname='数据库')ANDSnoIN(SELECTSnoFROMStudentWHERESdept='CS');(7)求各系的系名及男女生人数并将结果保存到另一个表中。CREATEVIEWDEPT(Sdept,Ssex,Num)ASSELECTSdept,Ssex,COUNT(Ssex)FROMStudentGROUPBYSdept,Ssex;(8)将平均成绩80分以上的学生的学号，选学的课程数和平均成绩保存到另一个表中。CREATEVIEWGrade(Sno,Num,Ave)ASSELECTSno,COUNT(Cno),AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)=80;(9)创建一个视图，查询没有选修课程的学生学号、姓名和所在系，并利用该视图查询所有没有选修课程的学生信息。CREATEVIEWS(Sno,Sname,Sdept)ASSELECTSno,Sname,SdeptFROMStudentWHERESnoNOTIN(SELECTSnoFROMSC);(10)创建一个给出学生的姓名、课程名和成绩的视图，并利用该视图查询某个学生学习的课程名和成绩。CREATEVIEWSTDASSELECTSname,Cname,GradeFROMStudent,COURSE,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=COURSE.CnoSELECTCname,GradeFROMSTDWHERESname='李力';(11)创建一个视图，求各门课程的课程号、选课人数、平均分、最高分，并利用该视图查询1号课程的选课人数、平均分、最高分。CREATEVIEWCASSELECTCno,COUNT(Sno)countc,AVG(Grade)avec,MAX(Grade)maxcFROMSCGROUPBYCno;SELECTcountc,avec,maxcFROMCWHERECno='1';(12)创建一个视图，求选修了2号课程且成绩高于该门课程平均分的学生学号和成绩。CREATEVIEWC1(Sno,Grade)ASSELECTSno,GradeFROMSCWHERECno='2'ANDGrade(SELECTAVG(Grade)FROMSCWHERECno='2');(13)创建用户user1，将学生表的select权限赋给用户user1，然后以user1登录，检查权限情况。CREATEROLEUSE1GRANTSELECTONStudentTOUSE1;(14)收回用户user1的对学生表的select权限，然后以user1登录，检查权限情况。REVOKESELECTONStudentFROMUSE1