搜索
MySql数据库DQL示例数据库中有四个表学生表（学号[主键]、姓名、性别、籍贯、电话、出生日期）课程表（课程编号[主键]、课程姓名、教师编号[外键]）教师表（教师编号[主键]、教师姓名）成绩表（学号[外键]、课程编号[外键]、分数、考试时间）外键说明：1.成绩表中的学号必须是学生表中有的学号，所以成绩表学号做外键关联学生表学号；2.成绩表中的课程编号必须是课程表中有的课程，所以成绩表课程编号做外键关联课程表课程编号；3.课程表中的教师编号必须是教师表中有的教师，所以课程表教师编号做外键关联教师表教师编号。练习单表查询1.从学生表中查询所有学生的所有信息SELECT*FROMstudent;2.从学生表查询所有学生的学号姓名信息并分别赋予别名SELECTstudentIdAS'学号',studentNameAS'姓名'FROMstudent;3.从学生表中查询学号是302的学生信息SELECT*FROMstudentWHEREstudentId=302;4.从学生表中查询学号在203——504之间的所有学生信息SELECT*FROMstudentWHEREstudentId=203ANDstudentId=504;或者：SELECT*FROMstudentWHEREstudentIdBETWEEN203AND504;5.查询年龄小于23岁的所有学生的学号和姓名SELECT*FROMstudentWHEREbornDate'1994';或者：SELECT*FROMstudentWHEREDATEDIFF(NOW(),bornDate)23*365;6.查询学生表中所有姓张的学生信息SELECT*FROMstudentWHEREstudentNameLIKE'张%';7.查询学生表中所有姓张的且名字两个字的学生信息SELECT*FROMstudentWHEREstudentNameLIKE'张_';8.查询学生表中包含‘辉’的学生的信息SELECT*FROMstudentWHEREstudentNameLIKE'%辉%';9.查询学生表中第3个字是‘涛’的学生信息SELECT*FROMstudentWHEREstudentNameLIKE'__涛';10.查询学生表中的姓名和号码插入到新表phonelist中CREATETABLEphonelist(SELECTstudentNameAS'姓名',phoneAS'电话号码'FROMstudent);11.从成绩表中查询学号和成绩并且学生成绩按降序排列SELECTstudentId,subjectNo,scoreFROMresultORDERBYscoreDESC;12.从成绩表中查询学号和成绩并且学生成绩按降序排列的基础上课程编号按升序排序SELECTstudentId,subjectNo,scoreFROMresultORDERBYscoreDESC,subjectNo;13.查询学生表信息，学号升序，并且每页显示10条数据SELECT*FROMstudentORDERBYstudentIdLIMIT10;14.查询比李俊贤小的学生信息SELECTbornDateFROMstudentWHEREstudentName='李俊贤';SELECT*FROMstudentWHEREbornDate'1994-04-25';子查询方式：SELECT*FROMstudentWHEREbornDate(SELECTbornDateFROMstudentWHEREstudentName='李俊贤');15.查询每门课程的平均分，显示课程编号和平均分SELECTsubjectNo,AVG(score)AS'课程平均分'FROMresultGROUPBYsubjectNo;16.上题查询结果再按照降序排列SELECTsubjectNo,AVG(score)AS'课程平均分'FROMresultGROUPBYsubjectNoORDERBYAVG(score)DESC;17.从学生表中查询所有籍贯的人数，显示人数和籍贯SELECTCOUNT(*)AS'人数',addressAS'籍贯'FROMstudentGROUPBYaddress;18.从学生表中查询所有籍贯的人数，并且男女生分开统计，显示人数、籍贯和性别SELECTCOUNT(*)AS'人数',addressAS'籍贯',sexAS'性别'FROMstudentGROUPBYaddress,sex;19.从学生表中查询同籍贯人数超过2人的籍贯名称，显示籍贯和人数SELECTCOUNT(*)AS'人数',addressAS'籍贯'FROMstudentGROUPBYaddressHAVINGCOUNT(*)2;20.查询每门课程的平均分，显示平均分大于80分的课程编号和平均分SELECTsubjectNo,AVG(score)AS'课程平均分'FROMresultGROUPBYsubjectNoHAVINGAVG(score80);21.查询每门课程及格的人数和及格学生的平均分，显示人数、平均分、课程编号SELECTCOUNT(*)AS'及格人数',AVG(score)AS'平均分',subjectNoAS'课程编号'FROMresultWHEREscore=60GROUPBYsubjectNo;22.查询每门课程及格的人数和及格学生的平均分大于80的记录，显示人数、平均分、课程编号SELECTCOUNT(*)AS'及格人数',AVG(score)AS'平均分',subjectNoAS'课程编号'FROMresultWHEREscore=60GROUPBYsubjectNoHAVINGAVG(score)80;多表连接查询内连接：根据表中共同列匹配，两表存在主外键关系时使用（1）.使用where子句连接23.查询学生的姓名和考试成绩SELECTstudentName,scoreFROMstudent,resultWHEREstudent.`studentId`=result.`studentId`;24.查询学生的姓名、课程名称和课程的考试成绩SELECTstudentName,subjectName,scoreFROMstudent,result,`subject`WHEREstudent.`studentId`=result.`studentId`AND`subject`.`subjectNo`=result.`subjectNo`;25.上题再将每位学生的记录集合到一起显示SELECTstudentName,score,subjectNameFROMstudent,result,`subject`WHEREstudent.`studentId`=result.`studentId`AND`subject`.`subjectNo`=result.`subjectNo`ORDERBYstudentName;26.查询学号为102的学生参加考试的课程名称，分数和考试时间SELECTsubjectName,score,examDateFROM`subject`,resultWHEREresult.studentId=102AND`subject`.subjectNo=result.subjectNo;别名写法：SELECTsubjectName,score,examDateFROM`subject`u,resultrWHEREr.studentId=102ANDu.subjectNo=r.subjectNo;27.查询由汪老师授课的课程名称SELECTsubjectNameFROM`subject`u,teachereWHEREe.`teacherName`='汪老师'ANDe.`teacherNo`=u.teacherNo;（2）.在from子句中使用innerjoin...on28.查询考试成绩大于80分的学生姓名SELECTstudentName,scoreFROMstudentsINNERJOINresultrON(s.`studentId`=r.`studentId`)WHEREr.`score`80;29.查询java基础考试成绩大于80分的学生姓名SELECTstudentName,score,subjectNameFROMstudentsINNERJOINresultrON(s.`studentId`=r.`studentId`)INNERJOIN`subject`uON(u.`subjectNo`=r.`subjectNo`)WHEREr.`score`80ANDsubjectName='java基础';外连接：参与连接的表有主从之分，符合条件的列直接返回结果集，不符合条件的列填空值返回（1）.左外连接30.查询所有学生的考试的分数，没参加考试的也显示出来，显示学生姓名，课程编号和分数SELECTstudentName,subjectNo,scoreFROMstudentsLEFTOUTERJOINresultrONs.`studentId`=r.`studentId`;31.查询所有学生的考试的分数，没参加考试的也显示出来，显示学生姓名，课程名称和分数（学生表左表，成绩表右表）SELECTstudentName,subjectName,scoreFROMstudentsLEFTOUTERJOINresultrONs.`studentId`=r.`studentId`LEFTOUTERJOIN`subject`uONu.`subjectNo`=r.`subjectNo`;32.上题再加以成绩降序并且按课程名称集中显示SELECTstudentName,subjectName,scoreFROMstudentsLEFTOUTERJOINresultrONs.`studentId`=r.`studentId`LEFTOUTERJOIN`subject`uONu.`subjectNo`=r.`subjectNo`ORDERBYsubjectName,scoreDESC;注：降序注意前后顺序（2）.右外连接33.查询中心所有老师的姓名和教授的课程名称，没有授课的老师记录也显示出来（课程表左表，教师表右表）SELECTteacherName,subjectNameFROM`subject`uRIGHTOUTERJOINteachereONe.`teacherNo`=u.`teacherNo`;多层嵌套子查询练习IN和NOTIN34.查询java基础考试刚好考到100分的学生名单（1）查询课程表，查询java基础对应的课程编号SELECTsubjectNoFROMSUBJECTWHEREsubjectName='java基础';（2）根据课程编号去成绩表中查询成绩是100分的学生的学号SELECTstudentIdFROMresultWHEREsubjectNo=(SELECTsubjectNoFROMSUBJECTWHEREsubjectName='java基础')ANDscore=100;（3）去学生表中查询学号对应的学生姓名SELECTstudentNameFROMstudentWHEREstudentIdIN(SELECTstudentIdFROMresultWHEREsubjectNo=(SELECTsubjectNoFROMSUBJECTWHEREsubjectName='java基础')ANDscore=100);或者多表连接查询实现：SELECTstudentNameFROMstudentsJOINresultrONs.`studentId`=r.`studentId`JOINSUBJECTuONu.`subjectNo`=r.`subjectNo`WHEREscore=100ANDsubjectName='java基础';35.查询参加java基础第一次考试的学生学号和分数（1）