软件测试班级学员就业前，带大家复习Sql查询

select A.sname,sum(B.score) as 总分
from stu as A
inner join sc as B
on A.sid=B.sid
where cid!=2
group by sname
having sname!='bbb'
order by sum(B.score)

select stu.sname,sc.cid,sc.score
from sc,stu
where sc.sid=stu.sid

---三表联结，显示stu表中sname,课程表course中的课程名cname,成绩表sc中的学生成绩score
select A.sname,C.cname,B.score
from stu A,sc B,course C
where A.sid=B.sid
and B.cid=C.cid

---三表联结，采用内联结来写的另外一种方法,中的sc表既可以和stu表关联，又可以和course表关联，方法在中间
select A.sname,C.cname,B.score
from stu A
inner join sc B
on A.sid=B.sid
inner join course C
on B.cid=C.cid

---左外联
select  A.sname,B.cid,B.score
from stu A
left join sc B
on A.sid=B.sid

---全联结
select  A.sname,B.cid,B.score
from stu A
full join sc B
on A.sid=B.sid

---交叉联结
select A.sname,B.cid,B.score
from stu A
cross join sc B

---子查询
select *
from stu
where stuAge>

(select stuAge
from stu
where stuName='李斯文'
)

---使用子查询，查询笔试刚好通过60分的学员信息
select *
from stu
where stuNO in

(
select stuNO
from sc
where writtenexam=60
)

---表连接来做
select A.*
from stu A,sc B
where A.stuNo=B.stuNo
and B.writtenexam=60

---用子查询来做，查询参加考试的学员名单
select stuName
from stu
where stuNO in
(
select stuNo
from sc
)

---用表连接来做，查询参加考试的学员名单
select A.stuName
from stu A,sc B
where A.stuNo=B.stuNo