MySQL中常见查询
1 -- 1、查询“001”课程比“002”课程成绩高的所有学生的学号; 2 SELECT a.s FROM sc a,sc b WHERE a.s=b.s AND a.c='1' AND b.c='2' AND a.score > b.score; 3 -- 2、查询平均成绩大于60分的同学的学号和平均成绩; 4 SELECT student.s,avg(score) FROM student,sc WHERE student.s=sc.s GROUP BY student.s HAVING avg(score) > 60; 5 -- 3、查询所有同学的学号、姓名、选课数、总成绩; 6 SELECT student.s,sname,COUNT(*),SUM(score) FROM student,course,sc WHERE student.s=sc.s AND course.c=sc.c GROUP BY student.s; 7 -- 4、查询姓“李”的老师的个数; 8 SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%'; 9 -- 5、检索“004”课程分数小于60,按分数降序排列的同学学号 ; 10 SELECT student.s FROM course,student,sc WHERE course.c = sc.c AND student.s=sc.s AND course.c='4' AND score<60 ORDER BY score DESC; 11 -- 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 12 SELECT student.s,sname FROM student,sc a,sc b WHERE a.s=b.s AND student.s=a.s AND student.s=b.s AND a.c='1' AND b.c='2'; 13 -- 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 14 SELECT student.s,sname FROM student,course,teacher,sc WHERE student.s=sc.s AND course.c=sc.c AND course.t=teacher.t AND tname='叶平'; 15 -- 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 16 SELECT student.s,student.sname FROM student,sc a,sc b WHERE student.s=a.s AND student.s=b.s AND a.s=b.s AND a.c='1' AND b.c='2' AND a.score > b.score; 17 -- 9、查询所有课程成绩小于60分的同学的学号、姓名; 18 SELECT s,sname FROM student WHERE s NOT IN (SELECT s FROM sc WHERE score >= 60); 19 -- 10、查询没有学全所有课的同学的学号、姓名; 20 SELECT s,sname FROM student WHERE (SELECT COUNT(*) FROM sc WHERE student.s=sc.s) < (SELECT COUNT(*) FROM course); 21 -- 11、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名; 22 SELECT s,sname FROM student WHERE s IN (SELECT DISTINCT s FROM sc WHERE c IN (SELECT c FROM sc WHERE s='1')); 23 -- 12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名; 24 SELECT DISTINCT student.s,sname FROM student,sc WHERE student.s=sc.s AND sc.c IN (SELECT c FROM sc WHERE s='1') AND sc.s != '1'; 25 -- 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; 26 UPDATE sc SET score=(SELECT e.平均成绩 FROM (SELECT AVG(score) 平均成绩 FROM sc GROUP BY c HAVING c IN 27 (SELECT c FROM course,teacher WHERE course.t=teacher.t AND teacher.tname='叶平'))e); 28 -- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名; 29 SELECT DISTINCT student.s,sname FROM student,sc WHERE student.s=sc.s AND sc.c IN (SELECT c FROM sc WHERE s='2') AND sc.s != '2'; 30 -- 15、删除学习“叶平”老师课的SC表记录; 31 DELETE FROM sc WHERE c=(SELECT e.c FROM (SELECT DISTINCT c FROM sc WHERE c IN 32 (SELECT course.c FROM course,teacher WHERE course.t=teacher.t AND tname='叶平'))e); 33 -- 16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学 34 -- INSERT表查询结果可以直接将后面的查询结果插入表中 35 INSERT sc SELECT * FROM sc WHERE NOT s IN (SELECT s FROM sc WHERE c=3); 36 -- 17、查询每门课程被选修的学生数 37 SELECT c,COUNT(s) FROM sc GROUP BY c; 38 -- 18、查询出只选修了一门课程的全部学生的学号和姓名 39 SELECT s,sname FROM student WHERE s IN (SELECT s FROM sc GROUP BY s HAVING COUNT(c)=1); 40 -- 19、查询男生、女生人数 41 SELECT COUNT(*) FROM student WHERE ssex='男'; 42 SELECT COUNT(*) FROM student WHERE ssex='女'; 43 -- 20、查询姓“张”的学生名单 44 SELECT * FROM student WHERE sname LIKE '张%'; 45 -- 21、查询同名同性学生名单,并统计同名人数 46 SELECT *,COUNT(sname) FROM student GROUP BY sname HAVING COUNT(sname) > 1; 47 -- 22、1981年出生的学生名单(注:Student表中Sage列的类型是datetime) 48 SELECT s,sname FROM student WHERE DATE_FORMAT(saged,'%Y')='1981'; 49 -- 23、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 50 SELECT c,AVG(score) FROM sc GROUP BY c ORDER BY AVG(score) ASC,c DESC; 51 -- 24、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 52 SELECT student.s,sname,AVG(score) FROM sc,student WHERE student.s = sc.s GROUP BY student.s HAVING AVG(score) > 85; 53 SELECT student.s,student.sname ,e.平均成绩 FROM student,(SELECT s,AVG(score) 平均成绩 FROM sc GROUP BY s HAVING AVG(score)>85) e WHERE student.s=e.s; 54 -- 25、查询课程名称为“数据库”,且分数低于60的学生姓名和分数 55 SELECT sname,score FROM student,course,sc WHERE student.s=sc.s AND course.c=sc.c AND cname='数据库' AND score < 60; 56 SELECT student.s ,student.sname ,e.score FROM student,(SELECT s,score FROM sc WHERE c IN (SELECT c FROM course WHERE cname='数据库')AND score<60) e WHERE student.s=e.s; 57 -- 26、查询所有学生的选课情况; 58 SELECT student.s,sname,course.c,cname FROM student,sc,course WHERE course.c=sc.c AND student.s=sc.s; 59 -- 27、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; 60 SELECT sname,cname,score FROM student,course,sc WHERE student.s=sc.s AND course.c=sc.c AND score>70; 61 -- 28、查询不及格的课程,并按课程号从大到小排列 62 SELECT s,score,cname FROM course,sc WHERE course.c=sc.c AND score < 60 GROUP BY course.c ORDER BY course.c DESC; 63 -- 29、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名 64 SELECT student.s,sname FROM student,sc WHERE student.s=sc.s AND c=3 AND score>80; 65 -- 30、求选了课程的学生人数 66 SELECT COUNT(DISTINCT s) FROM sc; 67 -- 32、查询各个课程及相应的选修人数 68 SELECT course.c,cname,COUNT(DISTINCT s) FROM sc,course WHERE sc.c=course.c GROUP BY course.c; 69 -- 33、查询不同课程成绩相同的学生的学号、课程号、学生成绩 70 SELECT student.s,course.c,score FROM student,sc,course WHERE student.s=sc.s AND course.c=sc.c 71 -- 34、检索至少选修两门课程的学生学号 72 SELECT DISTINCT s FROM sc GROUP BY c HAVING COUNT(DISTINCT c); 73 -- 35、查询全部学生都选修的课程的课程号和课程名 74 SELECT c,cname FROM course WHERE c IN(SELECT c FROM sc GROUP BY c); 75 -- 36、查询没学过“叶平”老师讲授的任一门课程的学生姓名 76 SELECT sname FROM student WHERE s NOT IN(SELECT s FROM sc,course,teacher WHERE sc.c=course.c AND course.t=teacher.t AND teacher.tname='叶平'); 77 -- 37、查询两门以上不及格课程的同学的学号及其平均成绩 78 SELECT s,AVG(IFNULL(score,0)) FROM sc WHERE s IN (SELECT s FROM sc WHERE score<60 GROUP BY s HAVING COUNT(s)>=2 ) GROUP BY s; 79 -- 38、删除“002”同学的“001”课程的成绩 80 DELETE FROM sc WHERE s=2 AND c=1;