SQL查询的：子查询和多表查询

select * from student
--查询成绩大于80分的学生信息
--方法一：子查询
select * from student --主干查询学生信息
where sno in 
(
 select sno from sc --只有成绩表中有成绩所以从成绩表中查询大于80分的学号再与学生表对应
 where grade > 80
)
--方法二：多表查询
select distinct student.* from student --distinct 去重 因为查询成绩大于80的同学的信息,但每一同学的成绩不止一门但我们只需要一次同学的信息，所以重复的我们就去重
inner join sc on student.sno = sc.sno
where grade > 80

--查询数据库成绩大于80的学生信息
--方法一:子查询
select * from student
where sno in (
 select sno from sc -- 查询在课程号和成绩的限制下的学号
 where grade > 80 and cno = (
 select cno from course -- 查询数据库的课程号
 where cname = '数据库'
 )

)
--方法二:多表查询
select student.* from student
inner join sc on student.sno = sc.sno
join course on sc.cno = course.cno
where cname = '数据库' and grade > 80


--选修10门课的学生学号
--思路：我们要先查出来每名同学选的总门数，再筛选掉
select sno,count(*) 科目数 from sc
group by sno
having COUNT(*) = 10

--选修10门课同学的信息
select * from student
inner join (select sno from sc group by sno having COUNT(*) = 10) a on student.sno = a.sno --把查询结果当成新的一张表