student <-> class student.class_id
teacher <-> course course.teacher_id
score <-> student,course score.student_id score.course_id
1、查询所有的课程的名称以及对应的任课老师姓名
# 内联写法
select
course.cname,teacher.tname
from
course inner join teacher on course.teacher_id = teacher.tid
# where 写法
select
course.cname,teacher.tname
from
course,teacher
where
course.teacher_id = teacher.tid
2、查询学生表中男女生各有多少人
select
gender,count(*)
from
student
group by
gender
3、查询物理成绩等于100的学生的姓名
# inner join
select
student.sid,student.sname
from
course inner join score on course.cid = score.course_id
inner join student on score.student_id = student.sid
where
course.cname = "物理" and score.num = 100
# where 写法
select
student.sid,student.sname
from
course,score,student
where
course.cid = score.course_id
and
score.student_id = student.sid
and
course.cname = "物理"
and
score.num = 100
4、查询平均成绩大于八十分的同学的姓名和平均成绩
select
student_id,avg(num)
from
score
group by
student_id
having
avg(num)>80
# 内联写法
select
student_id,avg(num),student.sname
from
score inner join student on student.sid = score.student_id
group by
student_id
having
avg(num)>80
# where 写法
select
student_id,avg(num),student.sname
from
score,student
where
student.sid = score.student_id
group by
student_id
having
avg(num)>80
5、查询所有学生的学号，姓名，选课数，总成绩
# 选课数
select
student_id,count(*)
from
score
group by
student_id
# 总成绩
select
student_id,sum(num)
from
score
group by
student_id
# inner join 联一张student学生表
select
student_id,student.sname,count(*),sum(num)
from
score inner join student on score.student_id = student.sid
group by
student_id
# where
select
student_id,student.sname,count(*),sum(num)
from
score,student
where
score.student_id = student.sid
group by
student_id
# 附加需求(展现所有学生)
select
student.sid,student.sname,count(score.course_id),sum(num)
from
score right join student on score.student_id = student.sid
group by
student.sid
6、 查询姓李老师的个数
select
count(*)
from
teacher
where
tname like "李%"
7、 查询没有报李平老师课的学生姓名
# distinct 自动去重 例如:distinct(id)
# 1.报了李萍老师的课程的学生是谁?
select
distinct(score.student_id)
from
teacher,course,score
where
teacher.tid = course.teacher_id
and
course.cid = score.course_id
and
teacher.tname = "李平"
# 2.除了这些id的,就是没有报李萍老师课程的
select * from student where sid not in (1号)
# 3.综合拼接:
select student.sid,student.sname
from
student where sid not in (select
distinct(score.student_id)
from
teacher,course,score
where
teacher.tid = course.teacher_id
and
course.cid = score.course_id
and
teacher.tname = "李平")
8、 查询物理课程的分数比生物课程的分数高的学生的学号
# 1.物理课程的学生分数
select
score.student_id as t1id , course.cid, score.num
from
course inner join score on course.cid = score.course_id
where
course.cname = "物理"
# 2.生物课程的学生分数
select
score.student_id as t2id , course.cid, score.num
from
course inner join score on course.cid = score.course_id
where
course.cname = "生物"
# 3.综合拼装
select
*
from
(select
score.student_id as t1id , course.cid, score.num
from
course inner join score on course.cid = score.course_id
where
course.cname = "物理") as t1 inner join
(select
score.student_id as t2id , course.cid, score.num
from
course inner join score on course.cid = score.course_id
where
course.cname = "生物") as t2 on t1.t1id = t2.t2id
where
t1.num > t2.num
9、 查询没有同时选修物理课程和体育课程的学生姓名
# 1.找体育和物理课程的id
select
course.cid
from
course
where
cname="体育" or cname = "物理"
# 2.找选了体育或者物理课程的学生id
select
student_id
from
score
where
score.course_id in (1号)
# 拼装数据 查一下所有的id号
select
student_id
from
score
where
score.course_id in (select
course.cid
from
course
where
cname="体育" or cname = "物理")
# 3.(同时)选了物理和体育课程的学生id
select
score.student_id
from
score
where
score.course_id in (select course.cid from course where cname="体育" or cname = "物理")
group by
score.student_id
having
count(*) = 2
# 4.除了同时选择的这些学生id,剩下的就是没有同时选择的学生id
select
student.sid
from
student
where
student.sid not in (3号)
# 综合拼装:
select
student.sid,student.sname
from
student
where
student.sid not in (select
score.student_id
from
score
where
score.course_id in (select course.cid from course where cname="体育" or cname = "物理")
group by
score.student_id
having
count(*) = 2)
10、查询挂科超过两门(包括两门)的学生姓名和班级
select
score.student_id,student.sname,class.caption
from
score inner join student on student.sid = score.student_id
inner join class on class.cid = student.class_id
where
score.num < 60
group by
score.student_id
having
count(*) >= 2
11、查询选修了所有课程的学生姓名
# 1.先计算一下所有课程的总数
select count(*) from course;
# 2.按照学生分组,总数量是1号sql得出来的数据,满足条件
select
score.student_id,student.sname
from
score inner join student on score.student_id = student.sid
group by
score.student_id
having
count(*) = 4
# 3.综合拼接
select
score.student_id,student.sname
from
score inner join student on score.student_id = student.sid
group by
score.student_id
having
count(*) = (select count(*) from course)
12、查询李平老师教的课程的所有成绩记录
# 内联写法
select
course.cid,course.cname,score.student_id,score.num
from
teacher,course,score
where
course.teacher_id = teacher.tid
and
score.course_id = course.cid
and
teacher.tname = "李平"
# 1.找李平老师所有课程id
select
course.cid
from
teacher,course
where
teacher.tid = course.teacher_id
and
teacher.tname = "李平"
# 2.找所有分数
select
*
from
score
where
course_id in (2,4)
# 3.综合拼接
select
count(*)
from
score
where
course_id in (select
course.cid
from
teacher,course
where
teacher.tid = course.teacher_id
and
teacher.tname = "李平")
order by sid desc
13、查询全部学生都选修了的课程号和课程名
# 1.通过score表,找有成绩的学生个数
select
count(distinct student_id)
from
score
# 2.按照课程的种类分组,筛选出学生总数等于13(一个学科被13个人学习)
select
course_id
from
score
group by
course_id
having
count(*) = 13
# 综合拼装
select
course_id,course.cname
from
score,course
where
score.course_id = course.cid
group by
course_id
having
count(*) = (select
count(distinct student_id)
from
score)
14、查询每门课程被选修的次数
select
course_id,count(*)
from
score
group by
course_id
15、查询只选修了一门课程的学生学号和姓名
# 1.按照学生分进分类,统计总课数是1的;选一门
select
student_id
from
score
group by
student_id
having
count(*) = 1
# 2.顺带连一张学生表
select
student_id,student.sname
from
score,student
where
score.student_id = student.sid
group by
student_id
having
count(*) = 1
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
select distinct(num) from score order by num desc
select distinct(num),student_id from score order by num desc
17、查询平均成绩大于85的学生姓名和平均成绩
# 1.先搜出来学生的id
select
score.student_id,avg(num)
from
score
group by
score.student_id
having
avg(num) > 85
# 2.顺带着联一张学生表student,找姓名
select
score.student_id,avg(num),student.sname
from
score inner join student on score.student_id = student.sid
group by
score.student_id
having
avg(num) > 85
18、查询生物成绩不及格的学生姓名和对应生物分数
select
student.sname,score.num,course.cname
from
course inner join score on score.course_id = course.cid
inner join student on score.student_id = student.sid
where
course.cname = "生物"
and
score.num<60
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
# 1.找李平老师的课程id
select
course.cid
from
teacher,course
where
teacher.tid = course.teacher_id
and
teacher.tname = "李平"
# 2.按照学生分组,在选择里李平老师课程的情况下,找平均成绩最高的id
select
score.student_id
from
score
where
course_id in (2,4)
group by
score.student_id
order by
avg(num) desc limit 1
# 3.通过id 找出学生的姓名
select
score.student_id,student.sname
from
score,student
where
score.student_id = student.sid
and
course_id in (2,4)
group by
score.student_id
order by
avg(num) desc limit 1
20、查询每门课程成绩最好的课程id、学生姓名和分数
# 1.找分数的最大值,按照课程分类
select
course_id,max(num) as max_num
from
score
group by
score.course_id
# 2.找出该分数的所有数据
select
*
from
score t1
inner join (1号) t2 on t1.course_id = t2.course_id
inner join student t3 on t1.student_id = t3.sid
# 3.找需求中的对应字段
select
t2.max_num,t3.sname,t1.course_id
from
score t1
inner join (select
course_id,max(num) as max_num
from
score
group by
score.course_id) t2 on t1.course_id = t2.course_id
inner join student t3 on t1.student_id = t3.sid
where
t2.max_num = t1.num
21、查询不同课程但成绩相同的课程号、学生号、成绩
select
s1.student_id as s1_sid,
s2.student_id as s2_sid,
s1.course_id as s1_cid,
s2.course_id as s2_cid,
s1.num as s1_num,
s2.num as s2_num
from
score as s1,
score as s2
where
s1.num = s2.num
and
s1.course_id > s2.course_id
22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称
# 1.找李平老师所有课程id
select
course.cid
from
teacher,course
where
teacher.tname = "李平"
and
teacher.tid = course.teacher_id
# 2.找学过李平老师课程的学生id
select
distinct(score.student_id)
from
score
where
course_id in (1号)
# 3.取反,除了这些学生的id,剩下的都没学过李平老师课程
# 方法一
select
student.sid ,student.sname,course.cname
from
student inner join score on student.sid = score.student_id
inner join course on course.cid = score.course_id
where
student.sid not in (2号)
# 方法二
select
student.sid ,student.sname,course.cname
from
student left join score on student.sid = score.student_id
left join course on course.cid = score.course_id
where
student.sid not in (2号)
23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名
# 1.找学号为2的学生,选了什么学科
select course_id from score where student_id = 2
# 2.找也选了1,3,4这些课程的学生id
select student_id from score where course_id in (1号)
# 需要去重,把必须的字段查询出来
select
distinct student_id,student.sname
from
score inner join student on student.sid = score.student_id
where
score.course_id in (1,3,4)
# 综合拼接
select
distinct student_id,student.sname
from
score inner join student on student.sid = score.student_id
where
score.course_id in (select course_id from score where student_id = 2)
24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数
# 1.找出任课最多的老师,任课 "2" 门
select count(*) from course group by teacher_id order by count(*) desc limit 1
# 2.找所有课程总数是2的老师id
select
teacher_id
from
course
group by
teacher_id
having
count(*) = (1号)
# 3.通过老师id,找到对应的课程
select cid from course where teacher_id = (2号)
# 4.通过该课程号找里面的最大分数
select
course_id,
max(num) as max_num
from
score
where
course_id in (3号)
group by
course_id
# 5.把最大分数,分数表,学生表拼在一起,做一次单表查询
select
t1.num,t2.max_num,t3.sid,t3.sname,t1.course_id
from
score t1 inner join (4号) t2 on t1.course_id = t2.course_id
inner join student t3 on t3.sid = t1.student_id
where
t1.num = t2.max_num
# 综合拼接:
select
t1.num,t2.max_num,t3.sid,t3.sname,t1.course_id
from
score t1 inner join (select
course_id,
max(num) as max_num
from
score
where
course_id in (select cid from course where teacher_id = (select
teacher_id
from
course
group by
teacher_id
having
count(*) = (select count(*) from course group by teacher_id order by count(*) desc limit 1)))
group by
course_id) t2 on t1.course_id = t2.course_id
inner join student t3 on t3.sid = t1.student_id
where
t1.num = t2.max_num