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<sec.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<b.`平均成绩`;

---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%'