mysql面试题sql语句多表联查⽅法不唯⼀，有问题欢迎留⾔讨论！代码块见⽂章末尾⼆、代码
select * from student
select * from sc
select * from course
– 求学号为‘20022037’的同学的每门课的成绩，输出格式为：学号，课程名，课程成绩select sno,cname,grade from sc,course
where sco = courseo and sno = ‘20022037’
select sno,cname,grade from sc inner join course on sco = courseo
where sno = ‘20022037’
– 查询每个学⽣的每门课程的成绩，要求输出学号，课程名，成绩
select sno,cname,grade from sc,course
where sco = courseo
select sno,cname,grade from sc inner join course on sco = courseo
– 查询每个学⽣的每门课程的成绩，要求输出学号，姓名，课程号，成绩
select student.sno,sname,cno,grade from sc,student
where sc.sno = student.sno
select student.sno,sname,cno,grade from sc inner join student on sc.sno = student.sno
– 查询选修了’线性代数’课程的学⽣学号、姓名
select cname,sno,sname from student,course
where cname = ‘线性代数’
–从选修’218801’课程的同学中，选出成绩⾼于’季莹’的学⽣的学号和成绩
select sno,grade from sc
where cno = ‘218801’ and grade > (
select grade from sc
where cno = ‘218801’ and sno = (office2016平滑更新包
select sno from student where sname = ‘季莹’))
–查询成绩⽐该课程平均成绩低的学⽣成绩表
select sno,cno,grade from sc as a
where grade < (
select avg(grade) from sc as b
where ao = bo)
–H 查询所有学⽣都选修的课程名
select cname from course
where not exists(
select sno from student
where not exists(
select sno from sc
where sc.sno = student.sno and sco = courseo
)
)
select cname from course
where cno in(
select distinct cno from sc
group by cno
having count(distinct sno) in (select sno from sc)
)smart目标例子
–查询选修了’线性代数’课程或’英语⼝语’课程的学⽣学号、姓名
select sno,sname from student
where sno in (
select sno from sc
where sno in (
select sno from course
where cname = ‘线性代数’ or cname = ‘英语⼝语’)
)
–⽤集合操作符 UNION 查询选修了’线性代数’课程或’英语⼝语’课程的学⽣学号、姓名select sno,sname from student
where sno in (
select sno from sc
where sno in (
select sno from course
where cname = ‘英语⼝语’)
)
union
select sno,sname from student
where sno in (
select sno from sc
什么是javabean的属性where sno in (
select sno from course
where cname = ‘线性代数’)
)
–查询选修了’218801’课程但没有选修’216301’课程的学⽣学号。
select sno from sc
where cno = ‘218801’
except
select sno from sc
where cno = ‘216301’
– ⽅法⼆
select sno from sc
where cno = '218801’and sno not in(
select sno from sc
where cno = ‘216301’)
–求同时选修’218801’课程和’216301’课程的学⽣学号、姓名。，也可以使⽤多表查询select sc.sno,sname from sc,student
where sc.sno = ‘218801’
intersect
select sc.sno,sname from sc,student
where sc.sno = ‘216301’
–查询所有学⽣及其选课信息
select student.sno,sname,cno,grade
from student left outer JOIN sc
on student.SNO=sc.SNO
–创建课程平均分视图
–以列的⽅式统计每门课程的分数段⼈数。分数段为：不及格、60-70、70-80、80-90、90-100
(select cname,‘不及格’ AS fsd ,COUNT() AS rs
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE <60
GROUP BY cname)
UNION
(SELECT CNAME ,‘60-70’ AS fsd , COUNT ()
FROM sc,course
美琪死亡真相WHERE sc.CNO=course.CNO AND GRADE BETWEEN 60 AND 70
GROUP BY CNAME)
UNION
(SELECT CNAME ,‘70-80’ AS fsd , COUNT ()
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 70 AND 80
GROUP BY CNAME)
UNION
(SELECT CNAME ,‘80-90’ AS fsd , COUNT ()
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 80 AND 90
GROUP BY CNAME)
UNION
(SELECT CNAME ,‘90-100’ AS fsd , COUNT (*)
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 90 AND 100
GROUP BY CNAME)
–1．查询所有选课学⽣的姓名
select sname,sno from student
where exists(select * from sc where sc.sno = student.sno)
order by sname
–2．查询所有未选课的学⽣的姓名
select sname from student
where not exists(select * from sc where student.sno = sc.sno)
–3．按学⽣分类查询其选修课程的平均分，输出学号、姓名和平均成绩
select student.sno,student.sname,avg(grade) as 平均成绩 from student,sc
where student.sno = sc.sno
group by student.sno,student.sname
–4．查询所有课程的平均分，输出课程名和平均成绩，并按平均成绩递增
select cname,avg(grade) as 平均成绩 from sc,course
group by cname
order by 平均成绩
–5．查询少于 10 名同学选修的课程名称，授课班号，教师名，选课⼈数
select cname,sco,tname,count(sno) as 选课⼈数 from course,sc
group by cname,sco,tname
having count(*) < 10
order by sco
–6．按学号显⽰信息学院，‘通信专业’或‘电⼦科学专业’的每个学⽣的每门课程的成绩明细，并统计每个学⽣的总成绩，平均成绩select DNAME,student.SNO,sum(GRADE)‘总成绩’,AVG(GRADE)‘平均成绩’
from sc,student,dept
where student.DNO=dept.DNO and student.SNO=sc.SNO
and DNAME=‘信息学院’
GROUP BY DNAME,student.SNO
–7．统计每门课的不及格⼈数，列出课程名和不及格⼈数
select cname,count(*) from sc,course
where grade < 60
group by cname
SELECT CNAME ,‘不及格分数段’ AS fsd ,COUNT(*) AS rs
FROM sc, course
WHERE sc.CNO= course.CNO AND GRADE<60
GROUP BY CNAME
–（1） 使⽤嵌套⽅法查询存在有 95 分以上成绩的课程 CNO
select cno from sc
where grade > 95
–（2） 查询成绩⽐该课程平均成绩低的学⽣成绩表
select sno,grade from sc as a
where grade < (select avg(grade) from sc as b where a.sno = b.sno)
–（3） 按课程名称统计每⼀门课程的平均分，输出课程名称和平均分
select cname,avg(grade) as 平均分 from sc,course
group by cname
–（4） 按学⽣姓名统计其选修课程的总学分，输出学⽣姓名和总学分
select sname,student.sno，总成绩 from student
right join
(select sno,sum(grade) as 总成绩 from sc group by sno) as d
on student.sno = d.sno
–（5） 查询同时选修了‘203402’和‘244501’课程的同学名称
select sname from student
where student.sno in (select sno from sc
where cno = ‘203402’ or cno = ‘244501’)
–（6） 求最⾼分学⽣的学号
select sno,grade from sc
where grade >= all(select grade from sc)
–（7） 查询“线性代数”的所有授课班级的平均成绩，列出课程名和平均成绩select avg(grade) as 平均成绩,cname from sc,course
where cname = ‘线性代数’
group by cname
–（8） 查询“线性代数”成绩最⾼的前 5 名学⽣的姓名及成绩，结果按成绩降序select top 5 sname,grade from sc,student
order by grade descc语言老师在线解答
–（9） 查询学⽣“20002059”选修课程的总学分数
select sum(grade) as 总分数 from sc
where cno = ‘20002059’
–（10） 对每个同学，查其获得最⾼成绩的课程号
select sno,cno from sc as a
where grade >= all(select grade from sc as b
where a.sno = b.sno)
三、代码块
select*from student
select*from sc
select*from course
-- 求学号为‘20022037’的同学的每门课的成绩，输出格式为：学号，课程名，课程成绩

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。