mysql 姓刘或姓李_MySQL 40题练习题和答案

最新推荐文章于 2022-08-10 18:06:19 发布

原创最新推荐文章于 2022-08-10 18:06:19 发布 · 266 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql 姓刘或姓李

本文详细介绍了使用SQL解决复杂查询需求的方法，包括成绩对比、平均分计算、特定条件的学生筛选等，提供了丰富的示例帮助读者理解和掌握。

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

思路：

获取所有有生物课程的人(学号，成绩) - 临时表

获取所有有物理课程的人(学号，成绩) - 临时表

根据【学号】连接两个临时表：

学号物理成绩生物成绩

然后再进行筛选

select A.student_id, a, b

from

(select score.student_id, number as a from score left join course on course.cid = score.corse_id where cname=‘生物‘) as A

left join

(select score.student_id, number as b from score left join course on course.cid = score.corse_id where cname=‘物理‘) as B

A.student_id = B.student_id where a > if(isnull(b),0,b);

3、查询平均成绩大于60分的同学的学号和平均成绩；

思路：

根据学生分组，使用avg获取平均值，通过having对avg进行筛选

select student_id, avg(number) from score group by student_id having avg(number)>60;

4、查询所有同学的学号、姓名、选课数、总成绩；

select score.student_id, student.sname, count(score.corse_id), sum(score.number)

from

score

left join

student on student.sid = student_id

group by

student_id;

5、查询所姓李的老师个数

select count(tid) from teacher where tname like ‘波%‘;

6、查询没学过“叶平”老师课的同学的学号、姓名；

思路：

先查到“李平老师”老师教的所有课ID

获取选过课的所有学生ID

学生表中筛选

select * from student where sid not in (

select DISTINCT student_id from score where score.corse_id in (

select cid from course left join teacher on course.teacher_id = teacher.tid where tname=‘波多‘)

);

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

思路：

先查到既选择001又选择002课程的所有同学

根据学生进行分组，如果学生数量等于2表示，两门均已选择

法一：

select student.sid, student.sname from

(select student_id, corse_id from score where corse_id=1 or corse_id=2) as B

left join student on B.student_id = student.sid group by student_id having count(student_id)>1;

法二：

select student.sid, student.sname from score left join student on student.sid= student_id

where corse_id= 1 or corse_id=2 group by student_id having count(corse_id)>1;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名

思路：

同上,只不过将1和2变成in(老师课程)

select student_id, student.sname from score left join student on student.sid=student_id where corse_id in

(select course.cid from course left join teacher on course.teacher_id=teacher.tid where tname=‘波多‘)

group by student_id having count(corse_id) = (select count(course.cid) from course left join teacher on

course.teacher_id=teacher.tid where tname=‘波多‘);

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

思路：

先找出分别找出课程1 和2 的成绩

在连表 (id ，A.成绩，B.成绩) 比较成绩大小

找出学生id 然后用 in

select student.sid, student.sname from student where sid in (

select A.student_id as c from (select student_id,number from score where corse_id=1) as A

left join (select student_id, number from score where corse_id=2) as B on A.student_id=B.student_id

where A.number> if(isnull(B.number),0,B.number)

);

10、查询有课程成绩小于60分的同学的学号、姓名；

思路：

先在score表找到成绩小于60的学生学号

再用 sid in

select student.sid, student.sname from student where sid in (

select student_id from score where number<60

);

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

思路：

在分数表中根据学生进行分组，获取每一个学生选课数量

如果数量==总课程数量，表示已经选择了所有课程

select student.sid from student where sid not in (

select student.sid from score

left join student on student_id=student.sid group by student_id having

count(corse_id)=(select count(1) from course));

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

思路：

法一：

获取 001 同学选择的所有课程

获取课程在其中的所有人以及所有课程

根据学生筛选，获取所有学生信息

再与学生表连接，获取姓名

student.sid, student.sname from score left join student on score.student_id=student.sid

where student_id !=1 and corse_id in (select corse_id from score where student_id=1);

法二：

select student.sid, student.sname from

(select * from score where corse_id in (select corse_id from score where student_id=1)) as A

left join student on student.sid = A.student_id where student_id !=1;

13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；

思路：

法一：

同12

先找到学过001的所有人

然后个数=001所有的学科个数

select student.sid, student.sname from

(select * from score where corse_id in (select corse_id from score where student_id=1)) as A

left join student on student.sid= A.student_id where A.student_id !=1 group by student_id having

count(corse_id)=(select count(corse_id) from score where student_id=1) ;

法二：

select student_id,sname, count(course_id)

from score left join student on score.student_id=student.sid

where stduent_id !=1 and score_id in (select corse_id from score where student_id=1) group by student_id having

count(corse_id)=(select count(corse_id) from score where student_id=1);

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

思路：

先找到002学过的课程号

在用 in 找到学过所有相同的课程号的人

在通过连表，分组，计数把相同的人找出来

select student.sid,student.sname from (select * from score where corse_id in (select corse_id from score where 　　　　　　　　student_id=1)) as A

left join student on student.sid = A.student_id where A.student_id!=2 group by A.student_id having

count(A.student_id)= (select count(corse_id) from score where student_id=2);

15、删除学习“叶平”老师课的SC表记录；

思路：

先连表查姓名为叶平的老师然后在删除

delete from score where corse_id in (

select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=‘饭岛‘);

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；

思路：

由于insert 支持

inset into tb1(xx,xx) select x1,x2 from tb2;

所有，获取所有没上过002课的所有人，获取002的平均成绩

insert into score(student_id, course_id, number) select sid,2,(select avg(number) from score where score_id=2)

from student where sid not in (select stduent_id from score where score_id=2);

17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分；

思路：

连表查学生id,有效课程数,平均分

通过已查询到的学生id为约束条件，再分别连表查询课程号们

select student.sid,

(select avg(number) from score left join course on course.cid = score.corse_id where course.cname="语文" and student_id = student.sid) as 语文,

(select avg(number) from score left join course on course.cid = score.corse_id where course.cname="数学" and student_id = student.sid) as 数学,

(select avg(number) from score left join course on course.cid = score.corse_id where course.cname="英语" and student_id = student.sid) as 英语,

count(student_id)as 有效课程数,

avg(number)as 平均成绩

from score

left join student on student.sid = student_id

group by student_id order by avg(number) desc;

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

思路：

法一：

通过联表把课程表和成绩表关联

再通过corse_id分组找出最大最小成绩

select course.cid,max(number),min(number) from score left join course on course.cid = corse_id

group by corse_id;

法二：

select corse_id,max(number),min(number) from score group by corse_id;

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

思路：

case when .. then

select

corse_id,

avg(number) as avgnum,

sum(case when score.number > 60 then 1 else 0 END)/count(1)*100 as percent,

from score group by corse_id order by avgnum asc, percent desc;

20、课程平均分从高到低显示(现实任课老师)；

思路：

联表分组

select

avg(if (isnull(score.number),0,score.number)),

teacher.tname

from course

left join score on course.cid = score.corse_id

left join teacher on course.teacher_id = teacher.tid

group by score.corse_id;