mysql查询例题 mysql数据库查询题

  有student、teacher、sc、course四个表,题目如下：

---

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

方法一:
(select t1.SId,class1,class2 from
(select SId,score as class1 from sc where sc.CId='01')as t1,
(select SId,score as class2 from sc where sc.CId='02')t2
where t1.sid=t2.sid and t1.class1>t2.class2) r left join student on student.SId=r.sid;

方法二:
select * from
(select * from sc where sc.CId='01')as a,
(select * from sc where sc.CId='02')as 
b where a.SId=b.SId and a.score>b.score;

---

查询结果截图：

---

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

提示：这里指用根据学生id把成绩分组,对分组中的score求平均值,最后选取结果值avg大于60即可

select student.SId,Sname,ss from student,
(select sid,avg(score)as ss from sc
group by sid
having avg(score)>60)r
where student.SId=r.sid;

---

查询结果截图：

---

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

select s.sid,s.sname,r.scoresum,r.coursenumber from(
(select student.SId,student.Sname from student)as s
left join
(select sc.SId,sum(sc.score)as scoresum,count(cid)as coursenumber from sc
group by sc.SId)as r
on s.sid=r.sid
);

---

查询结果截图：

---

提示：最终要查没有上过张三老师的课的学生
那我先把张三老师教过的所有学生的sid查询出来，作为列子查询，使用not in

select * from student where student.SId not in
(select sc.sid from sc,course,teacher
where
sc.cid=course.cid
and course.TId=teacher.TId
and teacher.Tname='张三')

---

查询结果截图：

---

5.查询两门及以上不及格课程的同学的学号,姓名及平均成绩

提示：从sc表中选取score小于60的并group by sid，count(*)>1

select student.SId,student.Sname,avg(sc.score)from student,sc
where
	student.SId=sc.SId and sc.score<60
	group by sc.SId
	having count(*)>1;

---

查询结果截图：

---

6.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select * from sc 
	left join
	(select sid,avg(score) as avgscore from sc
	group by sid)r
	on sc.SId=r.sid order by avgscore desc;

---

查询结果截图：

---

7.查询出只选修两门课程的学生学号和姓名

 提示：先在成绩表中查询出来每个学生共2个成绩的学生id

select student.SId,student.Sname from student
where student.SId 
in(select sc.SId from sc 
group by sid 
having count(sc.CId)=2);

---

查询结果截图：

---

8.嵌套查询列出同名的全部学生的信息

select * from student 
where sname 
in(select sname from student 
group by sname 
having count(*)>1);

---

查询结果截图：

---

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

select sc.cid,course.Cname,avg(sc.score)as avgscore from sc,course
where sc.CId=course.CId
group by sc.CId 
order by avgscore desc,cid asc;

---

查询结果截图：

---

10.查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

 提示：明确主查询是学生信息student.*，score

select student.*,sc.score,sc.CId,teacher.Tname 
from student,sc,course,teacher
where teacher.TId=course.TId
and sc.CId=course.CId
and sc.SId=student.SId
and teacher.Tname='张三'
order by score desc limit 1;

---

查询结果截图：

---

11.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

 提示：先运行:update sc set score = 90 where sid ='07' and cid ='02';

select student.*,sc.score,sc.cid,teacher.Tname 
from student,sc,course,teacher
where teacher.TId=course.TId
and sc.CId=course.CId
and sc.sid=student.sid
and teacher.Tname='张三'
and score = (
   select max(sc.score) from sc,student,teacher,course
	 where teacher.TId=course.TId
	 and sc.sid=student.sid
	 and sc.cid= course.cid
	 and teacher.Tname='张三');

---

查询结果截图：