mysql中select查询强化50题（附解答）

1、---1、查询“linux”课程比“hadoop”课程成绩高的所有学生的学号；
SELECt linux_s.stu_id FROM
(SELECt stu_id,course_id,scores FROM tb_score s JOIN (SELECt lessonId FROM tb_course WHERe lessonName='linux') c ON s.course_id=c.lessonId) linux_s 
JOIN 
(SELECt stu_id,course_id,scores FROM tb_score s JOIN (SELECt lessonId FROM tb_course WHERe lessonName='hadoop') c ON s.course_id=c.lessonId) hadoop_s 
ON linux_s.stu_id=hadoop_s.stu_id 
WHERe linux_s.scores>hadoop_s.scores;

--3、查询所有同学的学号、姓名、选课数、总成绩；
语句进化过程：
（1）先讲student表关联起来，关联条件是student_id

（2）再通过条件筛选自己需要显示的内容，用limit来分页显示

（3）用聚合函数count来统计课程数，用sum来算成绩的合。
一个学号、在一个课程中有多个成绩(取一个成绩)

SELECt stu_id,sname,COUNT(DISTINCT course_id) scNo,SUM(scores) FROM tb_score a JOIN tb_student b ON a.`stu_id`=b.`sid` GROUP BY stu_id LIMIT 50;

--4、查询姓“李”的老师的个数；

select count(select tid where tname like '李%') from 

--5、查询没学过“苍空”老师课的同学的学号、姓名；
SELECt sname FROM tb_student WHERe sid IN
(SELECt  DISTINCT stu_id FROM tb_score WHERe stu_id NOT IN
 (SELECt stu_id FROM tb_score s JOIN 
 (SELECt lessonId FROM tb_course WHERe teacher_id IN 
 (SELECt tid FROM tb_teacher WHERe tname='苍空')
 ) c ON s.course_id=c.lessonId));

 
 --6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
（1）取出课程id是1和2的课程
（2）通过student_id来进行分组根据having来过来选择两门的学生
（3）连表
SELECt stu_id FROM tb_score WHERe course_id=1 AND stu_id IN (SELECt stu_id FROM tb_score WHERe course_id=2) 

--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECt sname,sid FROM tb_student WHERe sid IN 
( SELECt fir.stu_id FROM (SELECt stu_id, scores FROM tb_score WHERe course_id=1) fir JOIN 
(SELECt stu_id,scores FROM tb_score WHERe course_id=2) sec ON fir.stu_id=sec.stu_id
 WHERe fir.scores

--9、查询有课程成绩小于60分的同学的学号、姓名；
 SELECt sid,sname FROM tb_student WHERe sid IN
 (SELECt DISTINCT stu_id FROM tb_score WHERe scores<60) 
 ORDER BY sid;

--10、查询没有学全所有课的同学的学号、姓名；

SELECt b.stu_id FROM (
SELECt stu_id ,COUNT(DISTINCT course_id) num FROM tb_score GROUP BY stu_id) b WHERe b.num<>(
SELECt COUNT(lessonId) FROM tb_course);

--11、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
--（1）查询学生所选课程是否在学生id为1的学生的课程里面
--（2）和学生表关联取出相关的ID和姓名
SELECt sid,sname FROM tb_student WHERe sid IN (
SELECt DISTINCT a.stu_id FROM tb_score a JOIN (SELECt DISTINCT stu_id,course_id FROM tb_score WHERe stu_id=1) b ON a.`course_id`=b.course_id WHERe a.stu_id<>1
)

--13、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
002学生选择了1,2两门课程，1,3;1;2;2,4;
(1)和002号同学选择的个数相同的学生id
（2）在筛选和002好同学选择课程名相同的学生id
 SELECt sname,sid FROM tb_student WHERe sid IN
 (SELECt tmp.stu_id2 FROM
 (SELECt t.stu_id2 ,COUNT(t.stu_id2) cou FROM 
 (SELECt a.stu_id stu_id1,a.course_id course_id1,b.stu_id stu_id2, b.course_id course_id2 FROM 
 (SELECt DISTINCT stu_id, course_id FROM tb_score WHERe stu_id=2) a JOIN 
 (SELECt stu_id ,course_id FROM tb_score WHERe stu_id IN 
 (SELECt a.stu_id FROM 
 (SELECt stu_id, COUNT(DISTINCT course_id) cou FROM tb_score GROUP BY stu_id) a WHERe a.cou=3 AND a.stu_id<>2)) b ON a.course_id=b.course_id) t
 GROUP BY t.stu_id2) tmp WHERe tmp.cou=3);
 
 --14、删除学习“叶平”老师课的SC表记录；
 DELETe FROM tb_score WHERe course_id IN
 (SELECt lessonId FROM tb_course WHERe teacher_id IN 
 ( SELECt tid FROM tb_teacher WHERe tname='麻希'));
 
 --15、向SC表中插入一些记录，这些记录要求符合以下条件：
 ①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；?
    SELECt sid FROM tb_student WHERe sid NOT IN(
        SELECt DISTINCT stu_id FROM tb_score WHERe course_id=2); 
    ---计算平均成绩
    SELECt AVG(scores) FROM tb_score WHERe course_id=2;
 
 
--16、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，
    按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
    第一种实现方式
    第二种实现方式
    
    计算有效平均分
    select stu_id, avg(scores) av from tb_score GROUP by stu_id;
    
    
    SELECt a.stu_id,a.scores AS 'java',b.scores AS 'sqlll',c.scores AS 'python',
COUNT(DISTINCT a.course_id)+COUNT(DISTINCT b.course_id)+COUNT(DISTINCT c.course_id) '有效课程数',
(a.scores+b.scores+c.scores)/3 '有效平均分'
FROM tb_score a JOIN tb_score b ON a.stu_id=b.stu_id JOIN tb_score c ON a.stu_id=c.stu_id
WHERe a.course_id=1 AND b.course_id=3 AND c.course_id=5 GROUP BY a.stu_id ORDER BY '有效平均分'DESC
    
    
--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
--思路：通过课程id来进行分组，这个时候会显示四行，然后用聚合函数max，min来找出最大值和最小值。
SELECt course_id '课程ID',MAX(scores) '最高分',MIN(scores) '最低分' FROM tb_score GROUP BY course_id; 

--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
--新知识点：case when then相当于if判断
 ---平均成绩
 SELECt course_id ,AVG(scores) '平均成' FROM tb_score GROUP BY course_id;
---求及格率：
SELECt a.course_id,ROUND(goodscore/allscore*100,2)'及格率%' FROM ( SELECt course_id,COUNT(scores) allscore FROM tb_score GROUP BY course_id)a JOIN (SELECt course_id,COUNT(scores) goodscore FROM tb_score WHERe scores>=60 GROUP BY course_id)b ON a.course_id=b.course_id;
---连接
SELECt c.course_id,c.`平均成绩`,d.`及格率%`  FROM 
(SELECt course_id ,AVG(scores) '平均成绩' FROM tb_score GROUP BY course_id) c
 JOIN 
(SELECt a.course_id,ROUND(goodscore/allscore*100,2)'及格率%' FROM 
    ( SELECt course_id,COUNT(scores) allscore FROM tb_score GROUP BY course_id)a 
        JOIN 
    (SELECt course_id,COUNT(scores) goodscore FROM tb_score WHERe scores>=60 GROUP BY course_id)b ON a.course_id=b.course_id) d
 ON c.course_id=d.course_id ORDER BY c.`平均成绩`;
    
    
20、课程平均分从高到低显示（显示任课老师）；
---课程平均分
  SELECt course_id ,ROUND(AVG(scores),2) '平均成绩' FROM tb_score GROUP BY course_id;
 ---连接教师表
 SELECt a.lessonId,b.tname,c.`平均成绩` FROM tb_course a JOIN tb_teacher b ON a.teacher_id=b.tid 
    JOIN 
        (SELECt course_id ,ROUND(AVG(scores),2) '平均成绩' FROM tb_score GROUP BY course_id) c 
    ON a.lessonId=c.course_id;
 
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
 
SELECt a.stu_id,a.`course_id`,a.scores FROM tb_score a JOIN tb_score b 
ON a.`course_id`=b.`course_id` AND a.`scores`<=b.`scores`
GROUP BY a.`course_id`,a.`scores`
HAVINg COUNT(a.scores)<=3
ORDER BY a.`course_id`,b.`scores` DESC;

---22、查询每门课程被选修的学生数；
SELECt course_id ,COUNT(stu_id) AS'选修人数' FROM tb_score GROUP BY course_id

---23、查询出只选修了一门课程的全部学生的学号和姓名；

SELECt sid,sname FROM tb_student WHERe sid IN 
    (SELECt s.stu_id FROM 
        (SELECt stu_id, COUNT(DISTINCT course_id)'选课数' FROM tb_score GROUP BY stu_id) s 
WHERe s.`选课数`=1)
 
 
 ---24、查询男生、女生的人数；
SELECt COUNT(sid), gender FROM tb_student GROUP BY gender
 
--25、查询姓“张”的学生名单；
select sname from tb_student where sname like '张%';

---26、查询同名同姓学生名单，并统计同名人数；
1张三
2张三
3张三
SELECt r.sname,r.num FROM 
    (SELECt sname ,COUNT(sname)num FROM tb_student GROUP BY sname) r 
WHERe r.num<>1

---27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

SELECt course_id,AVG(scores) FROM tb_score GROUP BY course_id ORDER BY AVG(scores),course_id;

---28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

SELECt r.stu_id,s.sname,AVG(r.scores) FROM tb_score r JOIN tb_student s ON s.sid = r.stu_id
GROUP BY r.stu_id HAVINg AVG(r.scores)>=85

--29、查询课程名称为“php”，且分数低于60的学生姓名和分数；
 SELECt sname,sid FROM tb_student WHERe sid IN
 (SELECt stu_id FROM tb_score WHERe course_id IN 
 ( SELECt lessonId FROM tb_course WHERe lessonName='php') AND scores<60);

--30、求选了mysql课程的学生人数
 SELECt COUNT(stu_id) FROM tb_score WHERe course_id IN ( SELECt lessonId FROM tb_course WHERe lessonName='MySql');

---31、查询选修“悠亚”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

    SELECt stu_id ,sname,MAX(scores) FROM tb_score JOIN tb_student ON sid=stu_id WHERe course_id IN (
        SELECt lessonId FROM tb_course WHERe teacher_id IN (
            SELECt tid FROM tb_teacher WHERe tname='悠亚'
        )
    )

---33、查询各个课程及相应的选修人数；
SELECt course_id ,COUNT(DISTINCT stu_id)'选课人数' FROM tb_score GROUP BY course_id;

--34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
SELECt lef.stu_id,lef.course_id,ROUND(lef.scores,0) FROM tb_score lef JOIN tb_score rig ON lef.`stu_id`=rig.`stu_id` AND lef.`scores`=rig.scores AND lef.`course_id`<>rig.`course_id` ORDER BY stu_id;

---35、查询每门课程成绩最好的前两名；
SELECt a.stu_id,a.course_id,b.scores FROM tb_score a JOIN tb_score b ON a.course_id=b.course_id AND a.scores<=b.scores
GROUP BY a.course_id,a.scores
HAVINg COUNT(a.scores) <=2
ORDER BY a.course_id,b.scores DESC;

---36、检索至少选修两门课程的学生学号；
SELECt a.stu_id,a.`选课数` FROM (SELECt stu_id ,COUNT(DISTINCT course_id)'选课数' FROM tb_score GROUP BY stu_id) a WHERe a.`选课数`>=2

---37、查询全部学生都选修的课程的课程号和课程名；
SELECt lessonId, lessonName FROM tb_course WHERe lessonId IN (
    SELECt course_id FROM tb_score GROUP BY course_id HAVINg COUNT(DISTINCT stu_id)=(
        SELECt COUNT(sid) FROM tb_student))

--38、查询没学过“苍空”老师讲授的任一门课程的学生姓名；
SELECt sid,sname FROM tb_student WHERe sid NOT IN
(SELECt DISTINCT stu_id FROM tb_score WHERe course_id IN
(SELECt lessonId FROM tb_course WHERe teacher_id IN 
(SELECt tid FROM tb_teacher WHERe tname='苍空')));

---38、查询两门以上不及格课程的同学的学号及其平均成绩；

SELECt r.stu_id ,s.`平均成绩` FROM(
    SELECt b.stu_id FROM(
    SELECt a.stu_id ,COUNT(a.stu_id) '不及格数' FROM (
    SELECt scoreid ,stu_id FROM tb_score WHERe scores<60)a GROUP BY a.stu_id
) b WHERe b.`不及格数`>=2
) r 
JOIN (
    SELECt stu_id,AVG(scores) '平均成绩' FROM tb_score GROUP BY stu_id
) s ON r.stu_id=s.stu_id

---39、检索“004”课程分数小于60，按分数降序排列的同学学号；
SELECt stu_id FROM tb_score WHERe course_id=4 AND scores <60 ORDER BY scores;

--40、删除“002”同学的“001”课程的成绩；

DELETe FROM tb_score WHERe scoreid IN 
(SELECt tt.scoreid FROM 
(SELECt * FROM tb_score WHERe stu_id=2 AND course_id=1) tt);

--41、查询成绩表中成绩为85，86或88的学生姓名
SELECt sname FROM tb_student WHERe sid IN
(SELECt stu_id FROM tb_score WHERe ROUND(scores,0)=85 OR ROUND(scores,0)=86 OR ROUND(scores,0)=88);

---42、查询“kb03”班的学生人数
SELECt COUNT(sid) FROM tb_student WHERe classId IN(SELECt cid FROM tb_class WHERe cname='KB03');

---43、查询“吉泽“教师任课的学生平均成绩
SELECt AVG(scores) FROM tb_score WHERe course_id=(SELECt lessonId FROM tb_course WHERe teacher_id =(SELECt tid FROM tb_teacher WHERe tname='吉泽'))

--44、查询和学号为8的同学年级相同的所有学生的姓名
 SELECt sid,sname FROM tb_student WHERe classId IN (SELECt classId FROM tb_student WHERe sid=8);

---45、查询成绩比该课程平均成绩低的同学的成绩表
SELECt a.stu_id,a.scores FROM tb_score a JOIN (SELECt course_id, AVG(scores)'平均成绩' FROM tb_score GROUP BY course_id) b ON a.course_id=b.course_id WHERe a.scores

---46、查询至少有3名女生的班号
SELECt stu_id ,course_id FROM tb_score WHERe stu_id IN (SELECt sid FROM tb_student WHERe gender ='女')
SELECt a.course_id,COUNT(DISTINCT a.stu_id) FROM (SELECt stu_id ,course_id FROM tb_score WHERe stu_id IN (SELECt sid FROM tb_student WHERe gender ='女')) a GROUP BY course_id HAVINg COUNT(DISTINCT a.stu_id)>=3 

---47、查询“男”教师及其所上的课程成绩表

SELECt scores FROM tb_score WHERe course_id IN (SELECt lessonId FROM tb_course WHERe teacher_id IN (SELECt tid FROM tb_teacher WHERe tname='麻希'));

---48、查询分别展示男，女学员的平均成绩

SELECt a.`女生平均成绩`,b.`男生平均成绩` FROM 
    (SELECt AVG(scores)'女生平均成绩' FROM tb_score WHERe stu_id IN 
        (SELECt sid FROM tb_student WHERe gender ='女')) a 
JOIN 
    (SELECt AVG(scores)'男生平均成绩' FROM tb_score WHERe stu_id IN 
        (SELECt sid FROM tb_student WHERe gender ='男')) b
        
---49、查询选修某课程的同学人数多于5人的教师姓名

SELECt tname FROM tb_teacher WHERe tid IN(
    SELECt teacher_id FROM tb_course WHERe lessonId IN (
        SELECt course_id FROM tb_score GROUP BY course_id HAVINg COUNT(DISTINCT stu_id)>15)
)

---50、查询课程表中至少有5名学生选修的并以java开头的课程的平均分数

SELECt a.lessonId,a.lessonName,b.avs FROM tb_course a JOIN (
    SELECt course_id, AVG(scores) avs FROM tb_score GROUP BY course_id HAVINg COUNT(DISTINCT stu_id)>10
)b ON a.lessonId=b.course_id WHERe lessonName LIKE 'java%'