mysql 应用题_mysql练习题

端口登录 3306或3308-- 1 查询不及格的学生及课程名SELECT b.`sname`,c.`cname` FROM score a JOIN student b ON a.`sno` = b.`sno` JOIN course c ON a.`cno` = c.`cno` WHERE a.degree < 60;-- 2 查询出每个学生的班级，选修的课程和成绩SELECT b.`sname`,b.class,c.`cname`,a.`degree` FROM score a JOIN student b ON a.`sno` = b.`sno` JOIN course c ON a.`cno` = c.`cno`;-- 3 查询学生姓名为李军的每门课的成绩，显示学生姓名，课程名称和成绩SELECT b.`sname`,c.`cname`,a.`degree` FROM score a JOIN student b ON a.`sno` = b.`sno` JOIN course c ON a.`cno` = c.`cno` WHERE b.`sname` = '李军';-- 4 查询李诚老师教的学生姓名SELECT a.`tname`,d.`sname` FROM teacher a JOIN course b ON a.`tno` = b.`tno` JOIN score c ON b.`cno` = c.`cno` JOIN student d ON c.`sno` = d.`sno` WHERE a.`tname` = '李诚';-- 5 统计下每个老师教科目的平均成绩SELECT a.`tname`,b.`cname`,AVG(c.`degree`) FROM teacher a JOIN course b ON a.`tno` = b.`tno` JOIN score c ON b.`cno` = c.`cno` GROUP BY a.`tname`;-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列SELECT Sname,Ssex,Class FROM student;SELECT Sname,Ssex,Class FROM student;-- 2、 查询教师所有的单位即不重复的Depart列。SELECT DISTINCT depart FROM `teacher`;-- 3、 查询Student表的所有记录。SELECT * FROM `student`;-- 4、 查询Score表中成绩在60到80之间的所有记录。SELECT * FROM `score` WHERE degree BETWEEN 60 AND 80;-- 5、 查询Score表中成绩为85，86或88的记录。SELECT * FROM `score` WHERE degree IN (85,86,88);-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。SELECT * FROM `student` WHERE class = '95031' OR ssex = '女';-- 7、 以Class降序查询Student表的所有记录。SELECT * FROM `student` ORDER BY class DESC;-- 8、 以Cno升序、Degree降序查询Score表的所有记录。SELECT * FROM `score` ORDER BY cno ASC,degree DESC;-- 9、 查询“95031”班的学生人数。SELECT class,COUNT(*) FROM `student` WHERE class = '95031';-- 10、查询Score表中的最高分的学生学号和课程号。(有难度)SELECT sno,cno,degree FROM `score` WHERE degree = (SELECT degree FROM `score` ORDER BY degree DESC LIMIT 1);-- 11、查询每门课的平均成绩。SELECT cno,AVG(degree) FROM `score` GROUP BY cno;-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。(有难度)SELECT cno,AVG(degree) FROM `score` GROUP BY cno HAVING COUNT(*) >= 5 AND cno LIKE '3%';-- 13、查询分数大于70，小于90的Sno列。SELECT * FROM `score` WHERE degree > 70 AND degree < 90;(这一题因为不包含70分和90分所以不能用between and)-- 14、查询所有学生的Sname、Cno和Degree列。(多表关联)SELECT a.sname,b.cno,b.degree FROM student a JOIN score b ON a.sno = b.sno;-- 15、查询所有学生的Sno、Cname和Degree列。(多表关联)SELECT a.sno,b.cname,a.degree FROM score a JOIN `course` b ON a.`cno` = b.`cno`;-- 16、查询所有学生的Sname、Cname和Degree列。(多表关联)SELECT a.sname,c.cname,b.degree FROM student a JOIN score b ON a.`sno` = b.`sno` JOIN course c ON b.`cno` = c.`cno`; -- 17、查询“95033”班学生的平均分。SELECT b.class,AVG(a.degree) FROM score a JOIN student b ON a.`sno` = b.`sno` WHERE b.class = '95033';-- 18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。(有难度)SELECT b.*,a.cno,a.degree FROM score a JOIN student b ON a.`sno` = b.`sno` WHERE a.cno = '3-105' AND a.degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');-- 19、查询选修某课程的同学人数多于5人的教师姓名。(有难度)(多表关联)SELECT c.`tname` FROM `score` a JOIN course b ON a.`cno` = b.`cno` JOIN teacher c ON b.`tno`-- 20、查询95033班和95031班全体学生的记录。SELECT * FROM `student` WHERE class IN ('95033','95031');-- 21、查询存在有85分以上成绩的课程Cno.SELECT DISTINCT cno FROM score WHERE degree > 85;-- 22、查询出“计算机系“教师所教课程的成绩表。(有难度)(多表关联)SELECT a.* FROM score a JOIN course b ON a.`cno` = b.`cno` WHERE b.`tno` IN (SELECT tno FROM `teacher` WHERE depart = '计算机系');-- 23、查询至少有2名男生的班号。SELECT class FROM `student` WHERE ssex = '男' GROUP BY class HAVING COUNT(*) >= 2; -- 24、查询Student表中不姓“王”的同学记录。SELECT * FROM `student` WHERE sname NOT LIKE '王%';-- 25、以班号和年龄从大到小的顺序查询Student表中的全部记录。SELECT * FROM `student` ORDER BY class DESC,sbirthday ASC;-- 26、查询“男”教师及其所上的课程。(多表关联)SELECT b.cname FROM teacher a JOIN course b ON a.`tno` = b.`tno` WHERE a.`tsex` = '男';-- 查询 比王芳操作系统成绩高的所有学生SELECT a.sname FROM `student` a JOIN `score` b ON a.sno =b.sno WHERE b.degree >(SELECT b.degree FROM student a JOIN score b ON a.`sno` = b.`sno` JOIN course c ON b.`cno` =c.`cno` WHERE a.sname = '王芳' AND c.`cname` = '操作系统');-- 查询 平均成绩高于60分的课程名SELECT cname FROM `course` WHERE cno IN (SELECT cno FROM score GROUP BY cno HAVING AVG(degree) >60 ); 嵌套SELECT c.cname FROM student a JOIN score b ON a.sno = b.sno JOIN course c ON b.cno = c.cno GROUP BY b.cno HAVING AVG(b.degree) > 60;