Mysql常见50条数据查询

1：-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select student.*,a.score '课程01分数' ,b.score '课程02分数' from student 

LEFT JOIN score a on student.s_id=a.s_id and a.c_id='01'

LEFT JOIN score b on student.s_id=b.s_id and b.c_id='02' or b.c_id=null

WHERE a.score > b.score ;

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT
    student.sname,
    student.s_id,
    ROUND(AVG(score.score), 1)
FROM
    student,
    score
WHERE
    student.s_id = score.s_id
GROUP BY
    student.s_id
HAVING
    AVG(score.score) >= 60;

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT student.s_id,student.sname,COUNT(score.c_id),SUM(score.score) from student LEFT JOIN score ON

 student.s_id=score.s_id GROUP BY student.s_id,student.sname;
需要注意的是：此处使用left join  因为此时有的学生可能没有选课

-- 7、查询学过"张三"老师授课的同学的信息

SELECT
    student.*
FROM
    student LEFT join score ON student.s_id = score.s_id
AND score.c_id IN ( SELECT course.c_id FROM course WHERE course.t_id IN (
SELECT
                    teacher.t_id
                FROM
                    teacher
                WHERE
                    teacher.tname = '张三'
            )
    );

方法二：

SELECT student.* from student,score,course,teacher where student.s_id=score.s_id AND score.c_id=course.c_id and
course.t_id=teacher.t_id AND teacher.tname='张三';

-- 8、查询没学过"张三"老师授课的同学的信息

select * from student
   where s_id not in (
      select score.s_id from score where score.c_id  in (
      select course.c_id from course where course.t_id   = (
      select teacher.t_id from teacher where teacher.tname='张三' ))

);

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT
    student.*
FROM
    student,
    score s1,
    score s2
WHERE
    student.s_id = s1.s_id
AND s1.c_id = '01'
AND student.s_id = s2.s_id
AND s2.c_id = '02'
AND s1.s_id = s2.s_id;

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT student.* from student where student.s_id in 
(select score.s_id from score where score.c_id='01') 
AND student.s_id not in 
(select score.s_id from score where score.c_id ='02');

select student.* from student
join (select s_id from score where c_id =1 )tmp1
    on student.s_id=tmp1.s_id
left join (select s_id from score where c_id =2 )tmp2
    on student.s_id =tmp2.s_id
where tmp2.s_id is null;

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
    student.sname,
    student.s_id,
    ROUND(t1.avgScore, 1)
FROM
    student,
    (
        SELECT
            s_id,
            AVG(score) avgScore
        FROM
            score
        WHERE
            score < 60
        GROUP BY
            score.s_id
        HAVING
            COUNT(1) >= 2
    ) t1
WHERE
    t1.s_id = student.s_id;

-- 28、查询男生、女生人数

SELECT SUM(CASE WHEN ssex='男' then 1 else 0 end ) as 男生人数,SUM(CASE WHEN ssex='女' then 1 else 0 end ) as 女生人数 FROM student ;

select ssex,COUNT(ssex) from  student GROUP BY student.ssex;

-- 42、查询每门课程成绩最好的前两名

(select * from score where c_id ='01' order by s_score desc limit 2)
union (
select * from score where c_id ='02' order by s_score desc limit 2)
union (
select * from score where c_id ='03' order by s_score desc limit 2);

-- 35、查询所有学生的课程及分数情况

SELECT student.s_name , 
SUM(case when score.c_id ='01' then score.s_score else 0 END) '语文',
SUM(case when score.c_id ='02' then score.s_score else 0 END) '数学',
SUM(case when score.c_id ='03' then score.s_score else 0 END) '英语',
SUM(score.s_score) '总分'
FROM student,score,course where student.s_id=score.s_id AND score.c_id=course.c_id 
GROUP BY student.s_id