select单表查询和多表查询
数据表
1).学生表: Student
字段: (SID,Sname,Sage,Ssex) -SID学生编号,Sneme学生姓名,Sage出生年月，Ssex学生性别
2).课程表: Course
字段: (CD,Cname,TI) –CID课程编号,Cname课程名称,TID教师编号
3).教师表: Teacher
字段: (TID,Tname) -ID教师编号,Tname教师姓名
4).成绩表: SC
字段: (SID,CID,score) -SID学生编号,CID课程编号,score分数
select查询
1.单表查询
问题：查询SID为1的学生信息
查询语句：select Sname,Sage,Ssex from student where SID=1
2.多表查询
问题：查询”01″课程比”02”课程成绩高的学生的信息及课程分数（这里01和02课程指的是CID）
方法一：from多表查询
1）第一步先查出所有学生的01课程的分数，查询结果作为a表
查询语句：select SID,CID,score from sc where CID=01
2）第二步再查出所有学生的02课程的分数，查询结果作为b表
查询语句：select SID,CID,score from sc where CID=02
3）第三步查询同一个学生的01课程分数大于02课程分数的学生SID以及课程分数，查询结果作为c表
查询语句：select a.SID,a.score as score01,b.score as score02 from (select SID,CID,score from sc where CID=01) as a,(select SID,CID,score from sc where CID=02) as b
where a.SID = b.SID and a.score>b.score
4）第四步根据c表和student表查询出学生具体信息
查询语句：select d.SID,d.Sname,d.Sage,d.Ssex,score01,score02 from student as d,
(
select a.SID,a.score as score01,b.score as score02 from (select SID,CID,score from sc where CID=01) as a,(select SID,CID,score from sc where CID=02) as b
where a.SID = b.SID and a.score>b.score
) as c
where d.SID=c.SID
方法二：使用join连接
1）第一步先查出所有学生的01课程的分数，查询结果作为a表
查询语句：select SID,CID,score from sc where CID=01
2）第二步再查出所有学生的02课程的分数，查询结果作为b表
查询语句：select SID,CID,score from sc where CID=02
3）第三步使用join连接student表、a表和b表
查询语句：select c.SID, c.Sname,c.Sage, c.Ssex,a.score as score01, b.score as score02 from student as c
right join (select SID,CID,score from sc where CID=01) as a on a.SID=c.SID
right join (select SID,CID,score from sc where CID=02) as b on b.SID=c.SID
where a.SID=b.SID and a.score>b.score