查询数据总数_25道mysql查询题目，更深程度的掌握查询语句

题目如下，代码均为亲手打的，可以得到结果，但不保证是最简便的方法，如有更精简的语句，欢迎交流。

学生表（stu）数据如下：

课程表（sco）数据如下：

教师表（te）数据如下：

成绩表（sc）数据如下：

题目如下：

-- 1.查询课程名称为“数学”，且分数低于60的学生姓名和分数；

select s_name,c_name,score

from stu left join sc on stu.s_id=sc.s_id

left join co on sc.c_id=co.c_id

where c_name="数学" and score<60;

-- 2.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select stu.s_id,s_name

from stu left join sc on stu.s_id=sc.s_id

where c_id="01" and score>=80;

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

select stu.s_id,s_name,count(score) 选课总数,ifnull(sum(score),0) 总成绩

from stu left join sc on stu.s_id=sc.s_id

group by stu.s_id;

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

select stu.s_id,s_name,avg(score) 平均成绩

from stu left join sc on stu.s_id=sc.s_id

group by stu.s_id

having avg(score)>=60;

-- 5.查询平均成绩大于等于85的所有学生的学号、课程编号和成绩；

select stu.s_id,c_id,score

from stu left join sc on stu.s_id=sc.s_id

where stu.s_id in

(select s_id from sc

group by s_id

having avg(score)>=85);

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

select stu.s_id,s_name,avg(score) 平均成绩

from stu left join sc on stu.s_id=sc.s_id

group by stu.s_id

having sum(score<60)>=2;

-- 7.检索“01”课程分数小于60，按分数降序排列的学生信息；

select stu.*

from stu left join sc on stu.s_id=sc.s_id

where c_id="01" and score<60

order by score desc;

-- 8.查询学过“张三”老师授课的同学的信息；

select stu.*

from stu left join sc on stu.s_id=sc.s_id

left join co on sc.c_id=co.c_id

left join te on co.t_id=te.t_id

where t_name="张三";

-- 9.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩；

方法一：

select sc.*,平均成绩 from sc left join

(select s_id,avg(score) 平均成绩 from sc

group by s_id) aa on sc.s_id=aa.s_id

order by avgs desc;

方法二：

select stu.s_id,

max(if(c_id=01,score,0)) "语文",

max(if(c_id=02,score,0)) "数学",

max(if(c_id=03,score,0)) "英语",

avg(score) 平均成绩

from stu left join sc on stu.s_id=sc.s_id

group by sc.s_id order by avg(score) desc;

-- 10.查询不同老师所教不同课程平均分从高到低显示；

select t_id,sc.c_id,avg(score) 平均成绩

from sc left join co on sc.c_id=co.c_id

group by t_id,c_id

order by avg(score) desc;

-- 11.查询出只有两门课程的全部学生的学号和姓名；

方法一：

select stu.s_id,s_name

from stu left join sc on stu.s_id=sc.s_id

group by stu.s_id

having count(c_id)=2;

方法二：

select s_id,s_name from stu

where s_id in

(select s_id from sc

group by s_id

having count(*)=2);

-- 12.查询没有学全所有课程的同学的信息；

方法一：

select * from stu

where s_id in

(select s_id from sc

group by s_id

having count(*)<(select count(*) from co));

方法二：

select stu.* from

stu left join sc on stu.s_id=sc.s_id

group by stu.s_id

having count(*)<(select count(*) from co);

-- 13.查询没学过“张三”老师授课的同学的信息；

方法一：

select stu.*

from stu left join sc on stu.s_id=sc.s_id

left join co on sc.c_id=co.c_id

left join te on co.t_id=te.t_id

group by stu.s_id

having sum(ifnull(t_name="张三",0))=0;

方法二：

select * from stu where s_id not in(

select stu.s_id

from stu left join sc on stu.s_id=sc.s_id

left join co on sc.c_id=co.c_id

left join te on co.t_id=te.t_id

where t_name="张三");

-- 14.查询至少有一门课与学号为“01”的同学所学相同的同学的信息；

方法一：

select * from stu

where s_id in

(select s_id from sc where c_id in

(select c_id from sc where s_id="01"));

方法二：

select stu.*

from stu left join sc on stu.s_id=sc.s_id

group by stu.s_id

having sum(sc.c_id in (select c_id from sc where s_id="01"))>=1;

方法三：

select distinct stu.*

from stu left join sc on stu.s_id=sc.s_id

where c_id in(

select c_id from sc where s_id="01");

-- 15.查询学过编号为“01”并且也学过编号为“02”的课程的同学的信息；

方法一：

select * from stu where s_id in

(select s_id from sc where c_id in ("01","02")

group by s_id

having count(*)=2);

方法二：

select * from stu where s_id in

(select s_id from sc

group by s_id

having sum(c_id in("01","02"))=2);

方法三：

select stu.* from stu left join sc on stu.s_id=sc.s_id

where c_id in("01","02")

group by stu.s_id

having count(*)=2;

方法四：

select stu.* from

stu join sc sc1 on stu.s_id=sc1.s_id and sc1.c_id="01"

join sc sc2 on stu.s_id=sc2.s_id and sc2.c_id ="02" ;

-- 16.查询“01”课程比“02”课程成绩高的学生的信息及课程分数；

方法一：

select * from

stu left join sc sc1 on stu.s_id=sc1.s_id and sc1.c_id="01"

left join sc sc2 on stu.s_id=sc2.s_id and sc2.c_id ="02"

where ifnull(sc1.score,0)>ifnull(sc2.score,0);

方法二：

select stu.*,

max(if(sc.c_id="01",score,0)) as "01",

max(if(c_id="02",score,0)) as "02"

from stu left join sc on stu.s_id=sc.s_id

group by stu.s_id

having max(if(sc.c_id="01",score,0))>max(if(c_id="02",score,0));

-- 17.查询学过编号为“01”但是没有学过编号为“02”的课程的同学的信息；

方法一：

select stu.* from

stu left join sc sc1 on stu.s_id=sc1.s_id and sc1.c_id="01"

left join sc sc2 on stu.s_id=sc2.s_id and sc2.c_id ="02"

where sc1.score is not null and sc2.score is null;

方法二：

select stu.*

from stu left join sc on stu.s_id=sc.s_id

group by stu.s_id

having sum(c_id="01")=1 and sum(c_id="02")=0;

方法三：

select * from stu

where s_id in (select s_id from sc where c_id="01")

and s_id not in (select s_id from sc where c_id="02");

方法四：

select stu.* from stu left join sc on stu.s_id=sc.s_id

where c_id in("01","02")

group by stu.s_id

having group_concat(c_id)="01";

-- 18.查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

方法一：

select s_name,c_name,score

from stu left join sc on stu.s_id=sc.s_id left join co on sc.c_id=co.c_id

where stu.s_id in

(select s_id from sc

group by s_id

having max(score)>70);

方法二：

select stu.*,

max(if(sc.c_id=01,score,0)) 语文,

max(if(sc.c_id=02,score,0)) 数学,

max(if(sc.c_id=03,score,0)) 英语

from stu left join sc on stu.s_id=sc.s_id

group by sc.s_id

having 语文>70 or 数学>70 or 英语>70;

-- 19.查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩；

select stu.*,score

from stu left join sc on stu.s_id=sc.s_id

left join co on sc.c_id=co.c_id

left join te on co.t_id=te.t_id

where t_name="张三"

order by score desc

limit 1;

-- 20.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩；

select * from sc

where (select sum(score>sc.score) from sc sc1 where c_id=sc.c_id) in (1,2);

-- 21. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率；

select sc.c_id,c_name,max(score)最高分,min(score)最低分,avg(score)平均分, avg(score>=60)及格率,avg(score>=70 and score<80)中等率,avg(score>=80 and score<90)优良率,avg(score>=90) 优秀率

from sc left join co on sc.c_id=co.c_id

group by sc.c_id;

-- 22.查询所有和01选修完全一样的同学信息；

select stu.* from stu

left join sc on stu.s_id=sc.s_id

group by stu.s_id

having group_concat(c_id order by c_id)=(select group_concat(c_id) from sc where s_id=01);

-- 23.将成绩表转换为二维表；

select s_id,

sum(if(c_id=01,score,0)) "01",

sum(if(c_id=02,score,0)) "02",

sum(if(c_id=03,score,0)) "03“

from sc

group by s_id;

-- 24.查询至少两门课程及格的学生学号；

select s_id from sc group by s_id having sum(score>=60)>1；

-- 25.按各科成绩进行排序，并显示排名；

select s_id,c_id,score,

(select sum(score>sc.score) from sc sc1 where c_id=sc.c_id)+1 排名

from sc

order by c_id,排名;

如果有用，给个赞呗~~