还剩10页未读，继续阅读
本资源只提供10页预览，全部文档请下载后查看！喜欢就下载吧，查找使用更方便
文本内容:
学生表StudentSid,Sname,Sage,SsexCREATE TABLEstudent sidvarchar10NOT NULL,sName varchar20DEFAULT NULL,sAge datetimeDEFAULT J1980-10-1223:12:36,sSex varchar10DEFAULT NULL,PRIMARY KEYsidENGINE=InnoDB DEFAULTCHARSET=utf8;课程表CourseCid Cname,TidzCREATE TABLEcourse cidvarchar10NOT NULL,cName varchar10DEFAULT NULL,tid int20DEFAULT NULL,PRIMARY KEYcidENGINE=lnnoDB DEFAULTCHARSET=utf8;成绩表SCSid,Cid,scoreCREATE TABLEsc sidvarchar10DEFAULT NULL,、册］除“同学日勺“课程日勺成绩50I002”001”delete fromSc whereSid=001*and Cid=,001,;cid varcharlODEFAULT NULL,score int10DEFAULT NULLENGINE=InnoDBDEFAULT CHARSET=utf8;教师表TeacherTid TnamezCREATETABLE taachertid int10DEFAULT NULL,tName varcharlODEFAULT NULLENGINE=InnoDB DEFAULTCHARSET=utf8;数据MySQLinsert intotaachertid tNamevalues1，李老师2:何以琛’,3,叶平；z张三丰，「男,张无极,,・insert intostudentsid,sName,sAge,sSex values100171980T0T223:12:361002,199510-12男,李奎］・・］女李元宝］・・］女,李世明23:12:36,100371992101223:12:36,1004,,1980101223:12:3610057/198L1O12，男赵六，男,田七］女；23:12:36’,10067’:1986T0T223:12:36,1007/:198L10T223:12:36insert intoscsid,cid,scorevalues11,；001,801,,,00260,1；003,,75,,2；00185,2,,002,,70,3,；0041100⑶次匕刻心:皿⑸乂—皿///////,/牌,，4/003,60;企业管理马克思微据库英语」；insert intocoursecid,cName tidvalues001,‘,,3,002,3003,UML,2,004,1005z问题、查询课程比课程成绩高的所有学生日勺学号;1“001”“002”J selecta.Sid fromselectsid,score from SC where Cid=001a,selectsid,scorefrom SC where Cid=0021bwhere a.scoreb.score anda.sid=b.sid;、查询平均成绩不小于分日勺同学的学号和平均成绩；260select Sid,avgscorefrom sc、查询所有同学日勺学号、姓名、选课数、group by Sid havingavgscore60;3总成绩；select Student.Sid Student.Sname countSC.Cid,sumscore35from Studentleft Outerjoin SCon Student.Sid=SC.Sid group by Student.Sid,Sname、查询姓“李”日勺老师日勺个数；4select countdistinctTnamefrom Teacher李％where Tnamelike;、查询没学过“叶平”老师课日勺同学的］学号、姓名；5selectStudent.Sid,Student.Snamefrom Studentwhere Sid not in select distinct SC.Sid from SC,Course,Teacher where』口十平;SC.Cid=Course.Cid andTeacher.Tid=Course.Tid andTeacher.Tname、查询学过并且也学过编号课程日勺同学日勺学号、姓名;6“001”“002”selectStudent.Sid,Student.Sname from Student,SC whereStudent.Sid=SC.Sid andSC.Cid=OOVand existsSelect*from SCas SC_2where SC_
2.Sid=SC.Sid andSC_
2.Cid=1002,;、查询学过“肝平”老师所教日勺所有课的同学日勺学号、姓名；7selectSid,Snamefrom Studentwhere Sid in select Sid from SCCourse,Teacher where SC.Cid=Course.Cid and5』口十平Teacher.Tid=Course.Tid andTeacher.Tname group bySid havingcountSC.Cid=select countCid from Course,Teacher where口十平;、查询课程编号的成Teacher.Tid=Course.Tid andTname=*8“002”绩比课程编号课程低的所有同学日勺学号、姓“001”名；Select Sid,Sname fromselect Student.Sid,Student.Sname,score,select scorefrom SC SC_2where SC_
2.Sid=Student.Sid andSC_
2.Cid=,002,score2from Student,SC where Student.Sid=SC.Sid and Cid=001S_2where score2score;、查询所有课程成绩不不小于分日勺同学日勺学号、姓名；960selectSid,Snamefrom Studentwhere Sid not in select Student.Sid from Student,SC whereS.Sid=SC.Sid and查询没有学全所有课日勺同学日勺学号、姓名；score60;10xselect StudentSid,Student.Snamefrom StudentSC5where Student.Sid=SC.Sid group by Student.Sid,Student.Sname having、查询至少有一门课与学号为countCid select countCidfrom Course;11“1001”日勺同学所学相似日勺同学日勺学号和姓名;select SidSname from Student,SC
5、where Student.Sid=SC.Sid andCid in select Cidfrom SCwhere Sid=100T;12查询至少学过学号为同学所有一门课日勺其他同学学号和姓名;“001”selectdistinct SC.Sid,Snamefrom Student,SCwhere Student.Sid=SC.Sid andCid inselect Cidfrom SCwhere Sid=,001,;
13、把表中“叶平”老师教日勺课日勺成绩都更改为此课程日勺平均成绩;“SC”updateSC setscore=select avgSC_
2.score from SCSC_2where SC_
2.Cid=SC.Cidfrom Course,Teacher where Course.Cid=SC.Cid and口十平;、查询和号Course.Tid=Teacher.Tid andTeacher.Tname=14“1002”日勺同学学习日勺课程完全相似日勺其他同学学号和姓名;select Sidfrom SCwhere Cid inselect Cidfrom SCwhere Sid=1002,group bySid havingcount*=select count*from SCwhere Sid=10021;
15、删除学习“叶平”老师课日勺表记录；SC DelectSCfrom courseJeacher叶平、where Course.Cid=SC.Cid andCourse.Tid=Teacher.Tid andTname=16向表中插入某些记录，这些记录规定符合如下条件没有上过编号课程日SC“003”勺同学学号、号课的平均成绩；2Insert SCselectSid,002,Select avgscorefrom SCwhere Cid=,002,fromStudentwhereSidnotinSelect SidfromSC whereCid=0021;、按平均成绩从高到低显示所有学生日勺“数据库”、“企业管理”、“英语”三17门的课程成绩，按如下形式显示学生数据库，企业管理，英语，有效课程数,ID,,有效平均分学生SELECT Sid as ID数据,SELECT scoreFROM SCWHERE SC.Sid=t.Sid ANDCid=OO4AS库』企业管理,SELECT scoreFROM SCWHERE SC.Sid=t.Sid ANDCid001AS英,SELECT scoreFROM SCWHERE SC.Sid=t.Sid ANDCid=006AS语有效课程数，平均成绩,COUNT*AS AVGt.score AS FROM SCAStGROUP BYSidORDER BYavgt.score、查询各科成绩最高和最低日勺分以如下形式显示课程最高分，最低分18ID,课程最高分最低分SELECT L.Cid AsID,L.score ASRscore ASFROM SCL,SC ASRWHERE L.Cid=R.Cid andL.score SELECTAS IL,Student MAXIL.scoreFROM SC=IL.Cid andAS IMWHEREL.Cid BYIM.Sid=IL.SidGROUP IL.CidAND SELECT SC ASR.Score R.Cid=MINIR.scoreFROM IRBYWHEREIR.Cid；GROUP IR.Cid按各科平均成绩从低到高和及格率19x日勺百分数从高到低次序课程号,课程SELECT t.Cid ASmaxcourse.CnameAS名平均成,isnullAVGscore,0AS绩,100*SUMCASE WHENisnullscore,0=60THEN1ELSE0END/COUNT*及格百分数ASFROM SC^Coursewhere t.Cid=course.CidGROUP BYt.CidORDER BY100*SUMCASE WHENisnullscore0=60THEN1ELSE05END/COUNT*DESC、查询如下课程平均成绩和及格率日勺百分数用行”显示企业管理马20“1001,克思数据库」002,OOUML003004SELECT SUMCASE WHEN Cid3007THEN scoreELSE0END/SUMCASE CidWHEN W1THEN1ELSE0END企业管理平均分AS,100*SUMCASE WHENCid=WV AND score=60THEN1企业管理ELSE0END/SUMCASE WHENCid=001THEN1ELSE0END AS及格百分数,SUMCASE WHENCid=!002THEN scoreELSE0END/SUMCASE CidWHEN马克思平均分*002*THEN1ELSE0END AS,100*SUMCASE WHENCid=*002*AND score=60THEN1ELSE0END/SUMCASE WHENCid=002马克思及格百分数THEN1ELSE0END AS,SUMCASE WHENCid=!003THEN scoreELSE0END/SUMCASE CidWHEN，平均分003THEN1ELSE0END ASUML,100*SUMCASE WHENCid=003AND score=60THEN1ELSE0END/SUMCASE WHENCid=003THEN1及格百分数ELSE0END ASUML,SUMCASE WHENCid=THEN scoreELSE0END/SUMCASE CidWHEN004，数据库平均分THEN1ELSE0END AS,100*SUMCASE WHENCid=004,ANDscore=60THEN1ELSE0END/SUMCASE WHENCid=THEN1ELSE0数据库及格百分数END ASFROMSC、查询不一样老师所教不一样课程平均分从高到低显示21乙教师教师姓名,课程SELECT maxTid ASID,MAXZ.Tname AS C.Cid ASI课程名称平均成绩D,MAXC.Cname AS,AVGScore ASFROMSC AS T,Course不ASC,Teacher ASZ whereT.Cid=C.Cid and0乙丁21=1€1GROUP BYC.CidORDER BYAVGScore DESC、查询如下课程成绩第名到第名日勺学生成绩单企业管理马克思2236001,数据库002,UML003,004SELECT DISTINCTtop3SC.Sid As学生学号，Student.Sname AS学生姓名，T
1.score AS企业管理,T
2.score AS马克思，T
3.score T
4.score ASAS据库，UML,ISNULLT
1.score0+5数+ISNULLT
3.score,0+ISNULLT
4.score05ISNULLT
2.score,0FROM Student,SC ON总分asSC.Sid=SC AS T1LEFT JOINLEFT JOINTl.Cid=,0011Tl.Sid ANDONSC.Sid=AS T2SCLEFT JOINT
2.Cid=*002*T
2.Sid ANDONSC.Sid=AS T3SCLEFT JOINT
3.Cid=003T
3.Sid ANDONSC.Sid=AS T4SCWHERET
4.Cid=004T
4.Sid ANDstudent.Sid=SC.Sid and［学生］［学生姓名］，企业管理，马克思，数据库，平均成绩ID,UML,ISNULLT
1.score0+ISNULLT
2.score,0+ISNULLT
3.score,0+5ISNULLT
4.score,0NOT INSELECTDISTINCTTOP15WITH TIESISNULLT
1.score0+ISNULLT
2.score,0+ISNULLT
3.score,0+5ISNULLT
4.score,0FROM scLEFT JOIN sc AST1ON sc.Sid—Tl.Sid ANDTl.Cid—WLEFT JOINsc AST2ON sc.Sid T
2.Sid ANDT
2.Cid k
2.——LEFTJOINsc AST3ON sc.Sid=T
3.Sid ANDT
3.Cid=LEFTJOINscAST4ON sc.Sid—T
4.Sid ANDT
4.Cid—k4ORDER BYISNULLT
1.score,0+ISNULLT
2.score,0+ISNULLT
3.score,0+ISNULLT
4.score0DESC;
3、记录列印各科成绩，各分数段人数课程课程名称23ID,,[100-85],[85-70],[70-60],
[60]课程课程名称SELECT SC.Cid asID,Cname as,SUMCASE WHENscoreBETWEEN85AND100THEN1ELSE0END AS[100-85],SUMCASE WHENscore BETWEEN70AND85THEN1ELSE0END AS[85-70],SUMCASE WHENscore BETWEEN60AND70THEN1ELSE0END AS[70-60],SUMCASEWHENscore60THEN1ELSE0END AS[60-]FROMSC,Coursewhere SC.Cid=Course.Cid、查询学绩及其名次平均成绩）平24生平均成均成绩SELECT1+SELECTCOUNT distinctFROM SELECT FROMSCSid,AVGscore ASGROUP BY SC.Cid,Cname;GROUPBY名Sid T1AS次，成WHERE平均成绩T
2.平均成绩）as绩平均学生学号，Sid as成绩平均FROMSELECTSid,AVGscoreSCFROMSidGROUP BYT2ASdesc;平均成ORDER BY、查询各科成绩前三名的记录（不考虑成绩并25学生列状况）SELECT tl.Sid asFROM课程分数WHERE score IN ID^I.Cid asID,Score asFROMSC t1WHERE SELECTTOP3scoreSCORDER BYt
1.Cid=Cidscore DESCORDER、查询每门26BY tl.Cid;select cidcountSid5课程被选修日勺学生数from sc group by Cid;、查询出只选修了一门课程日勺所有学生日勺学27号和姓名选课数select SC.Sid,Student.Sname countCidAS3fromSC,Studentwhere SC.Sid=Student.Sid group by SC.Sid,Student.Sname havingcountCid=1;查询男生、女生人数28男生人数男Select countSsexas fromStudent groupby Ssexhaving Ssex=5女生人数女Select countSsexas fromStudent groupby Ssexhaving Ssex=、查询姓“张”日勺学生名单29张％;、查询同名同性SELECT SnameFROM StudentWHERE Snamelike30学生名单，并记录同名人数select Sname,count*fromStudentgroupbySname、年出生日勺学生名单主表中列日having count*1;;311981C StudentSage勺类型是datetime select Sname,CONVERTchar11,DATEPART year,Sageas agefrom studentwhereCONVERTchar11,DATEPARTyear Sage=
19815、查询每门课程的平均成绩，成果按平均成绩升序排列，平均成绩相似时，按课32程号降序排列、Select Cid,Avgscore fromSC groupbyCidorder byAvgscore,Cid DESC;33查询平均成绩不小于日勺所有学生日勺学号、姓名和平均成绩85select Sname,SC.Sid,avgscorefrom Student,SC、whereStudent.Sid=SC.Sid groupby SC.Sid,Sname havingavgscore85;34查询课程名称为“数据库”，且分数低于日勺学生姓名和分数60SelectSname,isnullscoreQfrom Student,SC,Course数据库where SC.Sid=Student.Sid andSC.Cid=Course.Cid andCourse.Cname=and score60;、查询所有学生日勺选课状况；35SELECTSC.Sid,SC.Cid,Sname,CnameFROM SC,Student,Course、查询任何一门课程成绩where SC.Sid=Student.Sid andSC.Cid=Course.Cid;36在分以上的姓名、课程名称和分数;70SELECT distinctstudent.Sid,student.Sname,SC.Cid,SC.scoreFROM student,Sc、查询不及格的课程，并按课WHERE SC.score=70AND SC.Sid=student.Sid;37程号从大到小排列、查询课select cid from scwhere score60order byCid;38程编号为且课程成绩在分以上日勺学生日勺学号和姓名;00380select SC.Sid,Student.Sname fromSC StudentwhereSC.Sid=Student.Sid and5Score80andCid=003;求选了课程日勺学生人数39select count*from sc;、查询选修“叶平”老师所讲课程日勺学生中，成绩最高日勺学生姓名及其成绩40selectStudent.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.Sid=SC.Sid andSC.Cid=C.Cid andC.Tid=Teacher.Tid and』叶平，Teacher.Tname andSC.score=select maxscorefromSCwhereCid=C.Cid;、查询各个课程及对应的选修人数
41、查询不一样课程成绩相似日勺学生日勺selectcount*from scgroupbyCid;42学号、课程号、学生成绩selectdistinctA.Sid,B.score fromSCA,SC BwhereA.Score=B.Score andA.CidoB.Cid;、查询每门43功成绩最佳日勺前两名课程ID,t
1.Cid as学生SELECT tl.SidasFROM分数ID,Score asWHEREscoreINFROMSC t1WHERESELECT TOP2scoreORDER BYSCt
1.Cid=CidORDERscore DESC、记录每门课程日勺学生选修人数超过人日勺课程才记录规定输出课程4410BY tl.Cid;似按程号升序排列人数select Cidas程号，count asfromSC Cidgroupdesc,cid生学byorder by号Sidcount*
45、少选修两门课程日勺学scselect sidfrom和课程名2groupCid Cnameby5having count*=Course
46、学生都选修的课程日勺课程号selectfrom号和选修人数，查询成果按人数降序排列，查询成果按人数降序排列，若人数相老师讲授时任一门课程whereCidinselectcidfromscgroupby cid、查询没学过“叶平”日勺学生姓名47selectSnamefromStudentwhereSidnotinselect SidfromCourse,Teacher,SC叶whereCourse.Tid=Teacher.Tid andSC.Cid=course.Cid andTname=平1;、查询两门以上不及格课程日勺同学的学号及其平均成绩48select Sid,avgisnullscore0fromSCwhereSidinselect SidfromSCwhere
3、检索课程分数不score60groupbySidhavingcount*2groupbySid;49“004”不小于60,按分数降序排列日勺同学学号selectSidfromSCwhereCid=,004,andscore60order byscore desc;。
个人认证
优秀文档
获得点赞 0
