MYSQL题讲答案

2丶查询‘生物’课程比‘物理’课程成绩高的所有学生的学号

思路：

　　获取所有有生物课成的人（学号，成绩） -- 临时表

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

　　根据[学号]连接两个临时表：

　　　　学号　　物理成绩　　生物成绩

　　然后在进行筛选

　　select A.studeny_id,sw,ty from

　　(select student_id,num as sw  from score left join coure.course_id = course.cid where course.cname = '生物') as A

　　left join

　　(select student_id,num as ty from score left join course on score.course_id = course.cid where course.cname = '物理') as B

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

思路：

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

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

4丶查询所有同学的学号,姓名,选课数,总成绩:

　　select

　　score.student_id,sum(score.num),count(score.student_id),stident.sname

　　from 

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

　　group by score.student_id

5丶查询姓“李”的老师的个数：

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

　　2,  select count(1) from (select tid from teacher where tname like '李%') as B

6丶查询没学过‘叶平’老师课的同学的学号,姓名：

　　思路：

　　　　先查到‘李平老师’所教的所有课的ID

　　　　获取选过课的所有学生ID

　　　　学生表中筛选

　　select * from student where sid not in (

　　　　select DISTINCT student_id from score where score.course_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_id,sname from 

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

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

8丶查询学过‘叶平’老师所教的所有课的学号,姓名：

　　　　同上,支部过奖001和002变成了    in  （叶平老师的所有课）

9丶查询课程编号'002'的成绩比课程编号‘001’课程低的所有同学的学号,姓名：

　　同第一题

10丶查询所有课程成绩小于60分的同学的学号,姓名:

　　select sid,sname from student where sid in (

　　　　select distinct student_id from score where num < 60

　　)

11丶查询没有学全所有课的同学的学号,姓名：

　　思路：

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

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

　　　　select student_id，sname

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

　　　　group by student_id having count(course_id) = (select count(1) from course)

12丶查询只收有一门课与学号为‘001’的同学所学相同的同学的学号和姓名:

　　思路：

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

　　　　获取课程在其中的素偶有人一级所有课程

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

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

　　　　select student_id,sname,count(course_id)

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

　　　　where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id

13丶查询只少学过学号为‘001’同学所有课的其他同学学号和姓名:

　　先找到和001的学过的所有人

　　然后个数 = 001所有学科   == > 其他人可以选择的更多

　　select student_id,sname,count(course_id)

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

　　where student-id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) = (select count(course_id) from score where student_id = 1)

14丶查询和'002'号的同学学习的课程完全相同的其他同学学号的姓名:

　　个数相同

　　002学过的也相同

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

　　select student_id from score where student_id != 1 group by student_id having count(course_id) = (select count(1) from score where student_id = 1)

)and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) = (select count(1) from score where student_id = 1)

15丶删除学习‘叶平’老师课的score表记录

delete from score where course_id in (

 　　select cid from course left join tecaher on course.teacher_id = teacher.tid where teacher.name = '叶平'

 )

16丶向sc表中插入一些记录,这些记录要求符合以下条件:没有上过编号'002'课程的同学学号:插入‘002’号课程的平均成绩:

　　思路:

　　　　由于insert 支持

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

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

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

　　from student where sid not in(

　　　　select student_id from score where course_id = 2　　

)

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

　　select sc.student_id,

　　　　(select num from score left join course on score.score.course_id = course.cid where course.cname = '生物' and score.student_id = sc.student_id ) as sy,

　　　　(select num from score left join course on score.course_id = course.cid where course.cname = '物理' and score.student_id = sc.student_id) as w,

　　　　(select num from left join course on score.course_id = course.cid where course.cname = '体育' and score.student_id = sc.student_id) as ty,

　　　　count(sc.course_id),

　　　　avg(sc.num)

　　from score as sc

　　group by student_id desc

18丶查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;

　　select course_id,max(num) as max_num,min(num) as min_num from score group by course_id;

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

　　思路：    case where   ...   thed

　　select course_id,avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1) * 100 as percent from score group by course_id order by avgnum asc,percent desc;

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

　　select avg(if(isnull(score.num),0,score.num)),teacher.tname from course

　　left join score on course.cid = score.course_id

　　left join teacher on course.teacher_id = teacher.tid

　　group by score.course_id

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)

select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join

(

select

sid,

(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,

(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as second_num

from

score as s1

) as T

on score.sid =T.sid

where score.num <= T.first_num and score.num >= T.second_num

22、查询每门课程被选修的学生数；

select course_id, count(1) from score group by course_id;

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

select student.sid, student.sname, count(1) from score

left join student on score.student_id  = student.sid

group by course_id having count(1) = 1

24、查询男生、女生的人数；

select * from

(select count(1) as man from student where gender='男') as A ,

(select count(1) as feman from student where gender='女') as B

25、查询姓“张”的学生名单；

select sname from student where sname like '张%';

26、查询同名同姓学生名单，并统计同名人数；

select sname,count(1) as count from student group by sname;

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

select course_id,avg(if(isnull(num), 0 ,num)) as avg from score group by course_id order by avg asc,course_id desc;

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

select student_id,sname, avg(if(isnull(num), 0 ,num)) from score left join student on score.student_id = student.sid group by student_id;

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

select student.sname,score.num from score

left join course on score.course_id = course.cid

left join student on score.student_id = student.sid

where score.num < 60 and course.cname = '生物'

30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

select * from score where score.student_id = 3 and score.num > 80

31、求选了课程的学生人数

select count(distinct student_id) from score

select count(c) from (

select count(student_id) as c from score group by student_id) as A

32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

select sname,num from score

left join student on score.student_id = student.sid

where score.course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='张磊老师') order by num desc limit 1;

33、查询各个课程及相应的选修人数；

select course.cname,count(1) from score

left join course on score.course_id = course.cid

group by course_id;

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;

35、查询每门课程成绩最好的前两名；

select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join

(

select

sid,

(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,

(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num

from

score as s1

) as T

on score.sid =T.sid

where score.num <= T.first_num and score.num >= T.second_num

36、检索至少选修两门课程的学生学号；

select student_id from score group by student_id having count(student_id) > 1

37、查询全部学生都选修的课程的课程号和课程名；

select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);

38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；

select student_id,student.sname from score

left join student on score.student_id = student.sid

where score.course_id not in (

select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '张磊老师'

)

group by student_id

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

select student_id,count(1) from score where num < 60 group by student_id having count(1) > 2

40、检索“004”课程分数小于60，按分数降序排列的同学学号；

select student_id from score where num< 60 and course_id = 4 order by num desc;

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

delete from score where course_id = 1 and student_id = 2