mysql练习11-13题
11 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
查询学号为01的学生学的课程
select c_id from score where s_id='01'
查询至少有一门课与01同学重合的学生id
select distinct s.s_id,s.s_name from student s
join score on s.s_id=score.s_id
where s.s_id in(
select s_id from score where c_id in (
select c_id from score where s_id='01'
) and s_id<>'01'
);
注意用distinct去重
参考答案
select distinct s.s_id, s.s_name from student s
join score on s.s_id=score.s_id
where score.c_id in
(select c_id from score where s_id='01')
and s.s_id<>'01';
12 查询和“01”号同学所学课程完全相同的其他同学的学号
当子查询返回多个值时，需要使用in、any、all等关键词，其中in可以单独使用，相当于=ANY
其它的需要配合>、<、=、<>等运算符使用
in表示只要c_id与值列表中的任意一个值相等，就可以选出这条记录
使用having过滤组
select s_id,count(c_id) num from score where c_id in (
select c_id from score where s_id='01'
) and s_id<>'01'
group by s_id
having num=(select count(c_id) from Score where s_id='01')
参考答案
select s_id from Score
where c_id in
(select c_id from Score where s_id='01')
and s_id <> '01'
group by s_id
having count(c_id)=(select count(c_id) from Score where s_id='01');
其实思路一致
13 把“SCORE”表中“张三”老师教的课的成绩都更改为此课程的平均成绩
查询张老师教的课
select c_id from course where t_id=(select t_id from teacher where t_name='张三')
查询张三老师的学生
select * from score where c_id=(
select c_id from course where t_id=(select t_id from teacher where t_name='张三')
);
update score set s_score=(
select r.a from (select avg(s_score) a from score where c_id=(select c_id from course c join teacher t on c.t_id=t.t_id where t.t_name='张三'))r)
where c_id=(
select c_id from course where t_id=(select t_id from teacher where t_name='张三')
)
注意需要套一层r.a，否则会出现“You can't specify target table for update in FROM clause”的错，据说zhiyoumysql会有这种错。（ref：https://blog.csdn.net/z_youarethebest/article/details/53785487）
此外需要注意子查询的括号的位置。
参考答案
查询各课程的平均分
select avg(s_score) t, score.c_id from score
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id
group by c_id
查询张三老师的课程的平均分
select avg(s_score) t, score.c_id from score
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id
where t_name='张三'
group by c_id
更新分数
update score a JOIN
(select avg(s_score) t, score.c_id from score
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id
where t_name='张三'
group by c_id) b
on a.c_id=b.c_id
set a.s_score=b.t
参考答案更易理解，且思路更清晰
