查询50
1. 查询“001”课程比“002”课程成绩高的所有学生的学号
- select sc1.student_id from test.score sc1,test.score sc2
where sc1.student_id=sc2.student_id
and sc1.course_id='001'and sc2.course_id='002'
and sc1.score>sc2.score;
- select sc1.student_id from
(
select score,student_id from test.score
where course_id='001'
)sc1,
(select score,student_id from test.score
where course_id='002')sc2
where sc2.student_id=sc1.student_id
and sc1.score>sc2.score;
使用两个临时表sc1,sc2做联表查询
- select st.id,st.name from test.student as st where
(
(select score from test.score as sc where st.id=sc.student_id and sc.course_id='002')
<(select score from test.score as sc where st.id=sc.student_id and sc.course_id='001')
)
- select st.id,st.name from test.student as st
where st.id IN(
select sc1.student_id
from test.score sc1 join test.score sc2 on sc1.student_id=sc2.student_id
where sc1.course_id='001' and sc2.course_id='002'
and sc1.score>sc2.score
)
;
使用join on 联合多表查询
2. 查询平均成绩大于60分的同学的学号和平均成绩
select student_id,avg(score) as avg from test.score
group by student_id
having avg>60;
-
avg函数，求平均数
-
group by …… having
-引申1 排序：从高分到低分
select student_id,avg(score) as avg from test.score
group by student_id
having avg>60
order by avg desc;
-引申2 查询姓名、学号、平均分
select st.name,sc.student_id,avg(sc.score) avg
from test.score sc,test.student st
where sc.student_id=st.id
group by sc.student_id
having avg>60
;
3. 查询所有同学的学号、姓名、选课数、总成绩
select st.name,sc.student_id,count(sc.course_id),sum(sc.score)
from test.score sc,test.student st
where sc.student_id=st.id
group by sc.student_id
;
-
count函数，求数量
-
sum函数，求和
select id,name,
(select count(course_id) from test.score sc where st.id=sc.student_id) count_course,
(select sum(score) from test.score sc where st.id=sc.student_id) sum_score
from test.student st
;
[图片上传失败...(image-d11067-1684980120820)]
-
主表student
-
辅助表 score
9. 查询所有课程成绩不小于60分的同学的学号、姓名
select s.id,s.name
from test.student s
where s.id not in (
select student_id from test.score
where score<60
);
10. 查询没有学全所有课的同学的学号、姓名；
select s.id,s.name
from test.student s join
(select count(*) s,sc.student_id sid
from test.score sc
group by sc.student_id
having s<(select count(*) from test.course)
)st
on s.id=st.sid
;
网友评论