超细致SQL语句练习（附解释，全部手敲亲测过）

本文是阿桂我在强化学习SQL语句过程的总结的，题目源自于点击打开链接，阿桂我全部手敲亲测过，删掉了部分重复的和一些过于简单的语句，数据库是MySQL5.6；

随便说一下这是我的第一篇博客，大家点个赞鼓励一下吧！！！！！！！！

数据库的结构如下：

学生表：Student(SNO,Sname,Sage,Ssex) 
课程表：Course(CNO,Cname,TNO) 
成绩表：SC(SNO,CNO,score) 
教师表：Teacher(TNO,Tname) 

-- 1、查询“001”课程比“002”课程成绩高的所有学生的学号

SELECT a.SNO FROM (SELECT SNO,score FROM SC WHERE CNO='001') a, (SELECT SNO,score FROM SC WHERE CNO='002') bWHERE a.score>b.score AND a.SNO = b.SNO;-- 2、查询平均成绩大于60分的同学的学号和平均成绩SELECT SNO,AVG(score) AS ss FROM SC GROUP BY SNO HAVING ss>60;-- 3、查询所有同学的学号、姓名、选课数、总成绩SELECT a.SNO,a.SNAME,COUNT(b.CNO),SUM(b.score) FROM student a,sc b WHERE a.SNO=b.SNO GROUP BY a.SNO;-- 4、查询姓‘李’的老师的个数：SELECT COUNT(DISTINCT(Tname)) FROM Teacher WHERE Tname LIKE '李%'-- #1. %：表示任意0个或多个字符-- #2. _： 表示任意单个字符-- #3. [ ]：表示括号内所列字符中的一个（类似正则表达式）-- #4. [^ ] ：表示不在括号所列之内的单个字符-- 5、查询没有学过“`叶平`”老师可的同学的学号、姓名：SELECT a.Sname,a.Sno FROM Student a WHERE a.Sno NOT IN( SELECT SC.SNO FROM SC,Course,Teacher WHERE SC.CNO = Course.CNO AND Teacher.`TNO` = Course.`TNO` AND Teacher.`Tname`='叶平');-- 6、查询学过“叶平”老师所教的所有课的同学的学号、姓名-- #1、查找 叶平 老师的教授所有课程-- #2、找出 叶平 老师的教过的学生（学过他的课程的学生）并统计 统计(筛选)学生学过该老师多少门课-- #3、筛选(having)学过的课程数和老师教授课程数相同-- #4、根据筛选出来的学生编号去找学生信息SELECT s.`SNO`,s.`Sname` FROM student s WHERE s.`SNO` IN(SELECT sc.SNO num FROM course c,teacher t,sc WHERE t.`Tname`='叶平' AND c.`TNO`=t.`TNO` AND sc.`CNO`=c.`CNO` GROUP BY sc.`SNO`HAVING COUNT(sc.`CNO`)=(SELECT COUNT(c.CNO) FROM course c,teacher t WHERE t.`Tname`='叶平' AND c.`TNO`=t.`TNO`));-- 7、查询学过“011”并且也学过编号“004”课程的同学的学号、姓名：SELECT s.SNO ,s.`Sname` FROM SC c ,Student s WHERE c.`SNO`=s.`SNO` AND c.`CNO`='001' AND EXISTS (  SELECT SNO FROM SC b WHERE c.`SNO`=b.`SNO` AND b.`CNO`='004');SELECT s.SNO ,s.`Sname` FROM SC c ,Student s WHERE c.`SNO`=s.`SNO` AND c.`CNO`='001' AND c.`SNO` IN(  SELECT SNO FROM SC b WHERE b.`CNO`='004');-- 8、查询所有课程成绩小于60的同学的学号、姓名：SELECT SNO,SNAME FROM Student WHERE SNO NOT IN( SELECT s.SNO FROM sc,Student s WHERE sc.score>60 AND s.`SNO`=sc.`SNO` GROUP BY s.`SNO`);-- 9、查询没有学全所有课的同学的学号、姓名：SELECT sc.SNO,s.Sname FROM sc,student s WHERE sc.`SNO`=s.`SNO` GROUP BY SNO HAVING COUNT(CNO) <(  SELECT COUNT(*) FROM Course) ;-- 10、查询至少有一门课与学号为“S004”同学所学相同的同学的学号和姓名SELECT DISTINCT a1.SNO,s.`Sname` FROM sc a1,Student s WHERE s.`SNO` = a1.`SNO` AND a1.`SNO`<> 'S004' AND a1.`CNO` IN( SELECT a2.CNO FROM sc a2 WHERE a2.`SNO`='S004')  GROUP BY a1.`SNO`;-- 11、查询和“S002”号的同学学习的课程  完全 相同的其他同学学号和姓名：SELECT SNO FROM sc WHERE CNO IN (    SELECT CNO FROM sc WHERE SNO='S002')GROUP BY SNO HAVING COUNT(*)=(    SELECT COUNT(*) FROM sc WHERE SNO='S002');-- 12、删除学习“叶平”老师课的SC表记录：DELETE SC FROM course c , teacher t WHERE c.`CNO`=sc.`CNO` AND c.`TNO`=t.`TNO` AND t.`Tname`='叶平';-- 13、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、002号课的平均成绩INSERT SC   SELECT SNO,'002',(SELECT AVG(score) FROM SC WHERE CNO='002')   FROM student WHERE SNO NOT IN (    SELECT SNO FROM SC WHERE CNO='002');-- 14、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，--     按如下形式显示：学生ID，语文，数学，英语，有效课程数，有效平均分：SELECT   s.SNO AS '学生ID',   (SELECT s1.score FROM sc s1 WHERE s1.SNO=s.SNO AND s1.CNO='001') AS '语文',   (SELECT s2.score FROM sc s2 WHERE s2.SNO=s.SNO AND s2.CNO='002') AS '数学',  (SELECT s3.score FROM sc s3 WHERE s3.SNO=s.SNO AND s3.CNO='003') AS '英语',  COUNT(CNO) AS '有效课程数',  AVG(score) AS '有效平均分' FROM SC s GROUP BY s.SNO ORDER BY '有效平均分';-- 15、查询各科成绩最高和最低的分： 以如下的形式显示：课程ID，最高分，最低分SELECT s.`CNO` AS '课程ID',(SELECT MAX(score) FROM sc s1 WHERE s1.CNO=s.`CNO`) AS '最高分',(SELECT MIN(score) FROM sc s2 WHERE s2.CNO=s.`CNO`) AS '最低分'FROM sc s GROUP BY s.`CNO`;SELECT L.cNO AS 课程ID, L.score AS 最高分,R.score AS 最低分 FROM sc L, sc R WHERE L.cNO = R.cNO AND L.score = (SELECT MAX(IL.score) FROM sc IL, student AS IM WHERE L.cNO=IL.cNO AND IM.sNO=IL.sNO GROUP BY IL.cNO)AND R.score = (SELECT MIN(IR.score)FROM sc AS IR WHERE R.cNO=IR.cNO GROUP BY IR.cNO);-- 16、按各科平均成绩从低到高和及格率的百分数从高到低顺序SELECT s.CNO AS '课程ID',c.Cname AS '课程名',AVG(s.score) AS '平均成绩',(SELECT COUNT(*) FROM sc s1 WHERE s.`CNO`=s1.CNO AND s1.score>60)/COUNT(SNO)*100 AS '及格率'FROM sc s ,course c WHERE s.CNO=c.CNO GROUP BY s.CNO  ORDER BY '及格率' DESC;SELECT t.CNO AS 课程号,MAX(course.Cname)AS 课程名,IFNULL(AVG(score),0) AS 平均成绩,100 * SUM(CASE WHEN  IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数     FROM SC T,Course     WHERE t.CNO=course.CNO     GROUP BY t.CNO      ORDER BY 及格百分数 DESC ;-- 17、查询不同老师所教不同课程平均分从高到低显示：SELECT c.CNO AS 课程号,c.Cname AS 课程名,t.Tname AS 教师名,AVG(s.score) AS 平均分FROM course c, teacher t, sc sWHERE t.`TNO`=c.`TNO` AND c.`CNO`=s.`CNO`GROUP BY c.`CNO` ORDER BY 平均分; -- 18、查询如下课程成绩第3名到第6名的学生成绩单：语文(001)，数学(002)，英语(003)SELECT s.`Sname`,c.`Cname`,sc.`score`FROM student s,course c,sc WHERE s.`SNO`=sc.`SNO` AND c.`CNO`=sc.`CNO` AND sc.`CNO`='001' -- in ('001','002','003')        ORDER BY sc.`score`        LIMIT 0,3;-- 19、查询学生平均成绩及其名次：(***************************)SELECT 1+(SELECT COUNT(DISTINCT 平均成绩) FROM (SELECT sc.SNO,AVG(score) AS 平均成绩 FROM sc GROUP BY SNO ) AS T1 WHERE T1.平均成绩>T3.平均成绩) AS 名次,s.Sname 学生名字,平均成绩FROM (SELECT sc.SNO,AVG(score) AS 平均成绩 FROM sc ,student GROUP BY SNO) AS T3,student sWHERE s.`SNO` = T3.`SNO`GROUP BY 平均成绩;-- 20、查询各科成绩前三名的记录（不考虑成绩并列情况）：SELECT t1.SNO AS 学生ID,t1.CNO AS 课程ID,Score AS 分数 FROM SC t1 WHERE score IN (    SELECT TOP 3 score FROM SC WHERE t1.CNO= CNO ORDER BY score DESC);-- 有问题 MySql不支持 TOP    SELECT t1.SNO AS 学生ID,t1.CNO AS 课程ID,Score AS 分数 FROM SC t1 WHERE (SELECT COUNT(t2.`score`)FROM sc t2 WHERE t2.`score`>=t1.`score` AND t1.`CNO`=t2.`CNO`)>=3ORDER BY t1.`CNO`,t1.`score` DESC;-- 21、查询每门课程被选修的学生数SELECT CNO ,COUNT(SNO) AS num FROM sc GROUP BY CNO;-- 22、查询出只选修两门课程的全部学生的学号和姓名：SELECT sc.SNO,COUNT(CNO)AS num,Sname FROM sc,student s WHERE s.`SNO`=sc.`SNO` GROUP BY SNO HAVING num=2;-- 23、查询男生、女生人数SELECT s.`Sex`,COUNT(s.`Sex`) FROM student s GROUP BY s.`Sex`; -- 统计男女生的人数SELECT s.`Sex`,COUNT(s.`Sex`) FROM student s GROUP BY s.`Sex` HAVING s.`Sex`='男';-- 单独查-- 24、查询同名同姓的学生名单，并统计同名人数：SELECT sname,COUNT(*) FROM student GROUP BY sname HAVING COUNT(*)>1;-- 25、查询平均成绩大于85的所有学生的学号、姓名和平均成绩：SELECT sc.SNO ,s.Sname,AVG(sc.`score`) num FROM sc ,student s WHERE s.`SNO`=sc.`SNO` GROUP BY sc.`SNO` HAVING num>80;-- 26、查询任何一门课程成绩在70分以上的姓名、课程名称和分数：SELECT  sc.`SNO`,s.`Sname`,sc.`CNO`,c.`Cname`,sc.`score` FROM sc,student s,course c WHERE sc.`score`>70 AND s.`SNO`=sc.`SNO` AND c.`CNO`=sc.`CNO`;-- 27、查询不及格的课程，并按课程号从大到小的排列：SELECT sc.`CNO` ,c.`Cname` FROM sc,course c WHERE sc.`score`<60 AND c.`CNO`=sc.`CNO` ORDER BY sc.`CNO`;-- 28、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩：SELECT sc.SNO,sc.`CNO`,MAX(sc.`score`) 最高分 FROM sc WHERE sc.`CNO` IN (    SELECT c.`CNO` FROM teacher t,course c WHERE t.`TNO`=c.`TNO` AND t.`Tname`='叶平') GROUP BY sc.`CNO`;-- 29、查询不同课程成绩相同的学生和学号、课程号、学生成绩：SELECT a.SNO,s.`Sname`,a.`CNO`,a.`score` FROM sc a, sc b,student s WHERE s.`SNO`=a.`SNO` AND a.`CNO`<>b.`CNO` AND a.`score`=b.`score` ORDER BY a.`score`;-- 30、查询每门课程成绩最好的前两名SELECT c.`CNO`,c.`Cname`,MAX(score) FROM sc,course c WHERE c.`CNO`=sc.`CNO` GROUP BY CNO;-- 各科最好的SELECT a.`SNO`,a.`CNO`,a.`score` FROM sc a WHERE (SELECT COUNT(DISTINCT b.score) FROM sc b WHERE a.`CNO`=b.`CNO` AND b.`score`>=a.`score`)<=2ORDER BY a.`CNO`,a.`score` DESC;-- 31、统计每门课程的学生选修人数(超过2人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序：SELECT CNO,COUNT(SNO)num FROM sc GROUP BY CNO HAVING num>2 ORDER BY num DESC,CNO ASC;-- 32、查询没学过”叶平”老师讲授的任一门课程的学生姓名：SELECTb.`SNO` FROM sc b WHERE b.`SNO` NOT IN(SELECT a.SNO FROM sc a WHERE a.`CNO` IN(SELECT c.`CNO` FROM course c,teacher t WHERE t.`Tname`='叶平' AND t.`TNO`=c.`TNO`))GROUP BY b.`SNO`;-- #1 查出 叶平 教授的课程-- #2 查出 学过 叶平教授的课程的任-- #3 排除 上步的人SELECT sname FROM student WHERE sNO NOT IN (SELECT sNO FROM course,teacher,sc WHERE course.tNO=teacher.tNO AND sc.cNO=course.cNO AND tname='叶平');-- 33、查询两门以上不及格课程的同学的学号以及其平均成绩：SELECT SNO,AVG(score)FROM sc GROUP BY SNO HAVING COUNT(score<60)>2; -- 有错误，不能这么写-- 这才是正确的SELECT a.SNO,AVG(IFNULL(a.score,0)) FROM sc a WHERE SNO IN(SELECT b.SNO FROM sc b WHERE score<60 GROUP BY SNO HAVING COUNT(*)>2)GROUP BY sNO;