访问学生的物理最高成绩,并且打印出来,单个要打印出所有的信息 在添加几个 and 就可以啦。
select student.gender,student.sname from student where
student.sid =( select sid from s_mark where s_mark.score= (select max(score) from s_mark where s_mark.cid =(select cid from course where cname='physics' ))
and s_mark.cid = (select cid from course where cname='physics'));
步骤:
1、先通过course表得到物理的cid
select cid from course where cname='physics'
2、用得到的cid确定s_mark表中的最高成绩
s_mark.score = ( select max(score) from s_mark where s_mark.cid =(select cid from course where cname='physics' )
3、通过最高成绩和cid确定唯一的sid
上面这个一长串
-----+-----+-----+-------+
| mid | sid | cid | score | //表;s_mark
+-----+-----+-----+-------+
| 1 | 1 | 1 | 80 |
| 2 | 2 | 1 | 85 |
| 3 | 3 | 1 | 90 |
| 4 | 1 | 2 | 60 |
| 5 | 2 | 2 | 90 |
| 6 | 3 | 3 | 75 |
| 7 | 1 | 3 | 95 |
| 8 | 2 | 3 | 75 |
| 9 | 3 | 3 | 85 |
+-----+-----+-----+-------+
-----+-------+--------+
| sid | sname | gender | //表;student
+-----+-------+--------+
| 1 | Tom | male |
| 2 | Jack | male |
| 3 | Rose | female |
+-----+-------+--------+
+-----+-----------+
| cid | cname |//表;course
+-----+-----------+
| 1 | math |
| 2 | physics |
| 3 | chemistry |
+-----+-----------+
总结:都是围绕着最基本的格式,
select * from course,stduent,s_mark where(…….) and (………);