MySQL练习题

题目来源于网络，答案全为博主手敲

一、表关系建立

表关系分析：

二、增删查改

1.查询“生物”课程比“物理”课程成绩高的所有学生的学号

select * from 
(select student_id,course_id,score from score left join course on score.course_id = course.cid where course.cname='生物') as A
left join
(select student_id,course_id,score from score left join course on score.course_id = course.cid where course.cname='物理') as B
on A.student_id = B.student_id 
where A.score>B.score

2.查询平均成绩大于60分的同学的学号和平均成绩

select student_id,avg(score)
from score 
GROUP BY student_id
HAVING avg(score)>60

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

select student_id,student.sname,count(1),sum(score)
from score left join student on score.student_id=student.sid
group by student_id

4.查询姓“波”的老师的个数

select count(1)
from teacher
where teacher.teacher like '波%'

5.查询没学过“波多”老师课的同学的学号、姓名

方法1：

select student.sid,student.sname from student WHERE sid not in(
select score.student_id from course
left join score
on score.course_id=course.cid
left join teacher
on course.teacher_id = teacher.tid
where teacher.teacher='波多'
)

方法2：

select student.sid,student.sname from student where student.sid not in(
select student_id from score
WHERE course_id in
(select cid from course 
left join teacher on course.teacher_id=teacher.tid
where teacher.teacher='波多')
)

6.查询学过“1”并且也学过编号“2”课程的同学的学号、姓名

select A.student_id from
(select student_id,score from score where course_id=1) as A
left join
(select student_id,score from score where course_id=2) as B
on A.student_id = B.student_id
where A.score is not null and B.score is not null

7.查询学过“波多”老师所教的所有课的同学的学号、姓名

select student_id,student.sname from score 
left join student
on score.student_id=student.sid
where score.course_id in
(select cid from course left join teacher on course.teacher_id=teacher.tid
where teacher.teacher = '波多')
GROUP BY student_id
HAVING count(1)=(select count(cid) from course left join teacher on course.teacher_id=teacher.tid
where teacher.teacher = '波多')

8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名

select P.student_id,student.sname from 
(select M.student_id from 
(select student_id,course_id,score from score where course_id=1) as M
left join
(select student_id,course_id,score from score where course_id=2) as N
on M.student_id = N.student_id
where M.score>N.score ) as P
left join student on student.sid=P.student_id

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

select DISTINCT student_id,student.sname from score
left join student on score.student_id=student.sid
where score.score<60

10.查询没有学全所有课的同学的学号、姓名

select student_id,student.sname from score 
left join student on score.student_id=student.sid
group BY student_id
having count(1)<(select count(1) from course)

11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名

select DISTINCT student_id from score
where course_id in
(select course_id from score
where student_id = 1)

12.查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名

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

13.查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名

select student_id,student.sname from score 
left join student on score.student_id=student.sid
where student_id in
(select student_id from score
group by student_id 
having student_id !=2 and
count(course_id)=(select count(course_id) from score where student_id=2) 
)
and 
course_id in (select course_id from score where student_id=2)
group by student_id
having count(student_id) = (select count(course_id) from score where student_id=2)

分析：

由内向外：

1.      找到所有选课数与二号相同的学生的id

select student_id from score

group by student_id

having student_id !=2 and

count(course_id)=

(select count(course_id) from score where student_id=2)#二号学生选课数

)

2.      在score表筛选student_id，这个student_id要在第一步找到的id中，这就保证了筛选出来的学生选课数与2号相同。

3.      把course_id不在2号所选的course_id的记录删掉，那么剩下的是所选course全在二号所选课中的记录

4.      分组，统计student_id出现的次数，如果次数与2号选课的数量相同，则表示该学生与2号学生选课完全一样

14.删除学习“Faker”老师课的SC表记录

delete from score where course_id in
(select cid from course left join 
teacher on course.teacher_id=teacher.tid
where teacher.teacher='Faker')

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

INSERT into score (student_id,course_id,score)
select sid,2,(select avg(score) from score where course_id=2 group by student_id)
from student where sid not in
(select student_id from score where course_id = 2)

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

select sc.student_id,
	(select score from score left join course on score.course_id=course.cid where course.cname="LOL" and 
score.student_id=sc.student_id) as lo,
	(select score from score left join course on score.course_id=course.cid where course.cname="性教育" 
and score.student_id=sc.student_id) as xing,
	(select score from score 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.score)
from score as sc
group by student_id desc

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

select course_id,max(score),min(score) from score
group by course_id

18.按各科平均成绩从低到高和及格率的百分数从高到低顺序(case when score.score>60 then 1 else 0 end )相当于if else 判断，如果score.score>60就返回1，否则返回0

select course_id,avg(score), 
sum(case when score.score>60 then 1 else 0 END)/count(1)*100 as percent
from score
group BY course_id

19.课程平均分从高到低显示（显示任课老师）

select avg(if(isnull(score.score),0,score.score)),teacher.teacher 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

if( isnull(score.score) , 0 , score.score) 如果条件成立返回0，否则返回score.score，条件为score.score是否为空

20.查询各科成绩前三名的记录:(不考虑成绩并列情况)，暂时没做出来，贴一个网上的答案

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

21.查询每门课程被选修的学生数

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

T23
select student_id 
from score
GROUP BY student_id
having count(1)=1

22.查询出只选修了一门课程的全部学生的学号

select student_id 
from score
GROUP BY student_id
having count(1)=1

23.查询男生、女生的人数

select * from 
(select count(1) as man from student where sex='男') as A,
(select count(1) as woman from student where sex='女') as B

24.查询姓“钢”的学生名单

select student.sid,student.sname from student
where sname like '钢%'

25.查询同名同姓学生名单，并统计同名人数

select student.sname,count(1)
from student,(select sid,sname from student)as SC
where student.sid!=SC.sid and student.sname=SC.sname

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

select course_id,avg(score)
from score
group by course_id
ORDER BY score ASC,course_id desc

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

select student_id,student.sname,avg(score)
from score left JOIN student on score.student_id=student.sid
group by student_id
having avg(score)>85

28.查询课程名称为“物理”，且分数低于60的学生姓名和分数

select student.sname,score from score left join student on score.student_id=student.sid
where course_id = (select cid from course where course.cname='物理')
and score < 60

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

select student_id,student.sname from score left join student on score.student_id=student.sid
where course_id = 2 and score>80

30.求选了课程的学生人数

select count(1) from 
(select student_id from score 
group by student_id) as M

31.查询选修“波多”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select student.sname,max(score) from score left join 
student on score.student_id=student.sid
where course_id in 
(select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.teacher='波多')

32.查询各个课程及相应的选修人数

select course_id,count(1),M.cname from score left join
(select cid,cname from course) as M
on score.course_id=M.cid
group by course_id

33.查询不同课程但成绩相同的学生的学号、课程号、学生成绩

select score.student_id,M.student_id,score.course_id,score.score from score left join 
(select student_id,score,course_id from score) as M
on score.score=M.score and score.student_id!=M.student_id
and score.course_id!=M.course_id

34.查询每门课程成绩最好的前两名，同20

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

35.检索至少选修两门课程的学生学号

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

36.查询全部学生都选修的课程的课程号

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

37.查询没学过“波多”老师讲授的任一门课程的学生姓名

select sid from student where sid not in(
select DISTINCT student_id from score
where course_id in
(select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.teacher='波多')
)

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

select student_id from score 
group by student_id 
having count(case when score<60 then 1 ELSE null END)>2

注：0会被count计数，null不会

39.检索“4”课程分数小于60，按分数降序排列的同学学号

select student_id,score 
from score
where course_id = 4 and score<60 
order by score DESC

40.删除“2”同学的“1”课程的成绩

delete from score 
where student_id = 2
and course_id = 1