11.6今日小总结（子查询，分页和join）

--一、子查询
--1.查询高二二班的所有学生,学生姓名\性别\学号...

--第一种
select * from T_Student where FstuClassId=
(select clsId from T_Class where cName='高二二班')
--第二种
select * from T_Student where
exists(select * from T_Class where cName='高二二班' and T_Student.FstuClassId=T_Class.clsId)

--2.查询高二二班和高二一班的所有学生,学生姓名\性别\学号。

select * from T_Student where FstuClassId in
(select clsId from T_Class where cName='高二一班' or cName='高二二班')

--3.查询刘备、关羽、张飞的成绩
select * from T_Student
select * from T_Score
select * from T_Class
select FscoreId,FEnglish,Fmath from T_Score where FstuId in
(select FstuId from T_Student where FstuName in('刘备' , '关羽' , '张飞'))

--二、分页（row_number() over()）
--4.查询MyStudents表中 第8页中的数据（每页3条记录）
use Test
select * from Mystudents
select * from
(select *,ROW_NUMBER() over(order by FId asc)as RNumber from Mystudents)
as TBL3 where RNumber between (3*7+1) and (3*8)

--三、join
--1.查询年龄超过20岁的姓名\年龄\所在班级
use TextSchool
select FstuName,FstuAge,FstuClassId from T_Student as TS
inner join T_Class as TC on TS.FstuClassId=TC.clsId
where FstuAge>20

--2.查询出所有参加考试的同学的学生编号，姓名，考试成绩。
select * from T_Student
select * from T_Class
select * from T_Score

select Fstubianhao,FstuName,FEnglish,Fmath from T_Student
inner join T_Score on T_Score.FstuId=T_Student.FstuId

--3.查询出所有没有参加考试的同学的学生编号，姓名，考试成绩。     
select * from T_Student
select * from T_Score
delete from T_Score where FscoreId=6 or FscoreId=3
--使用子查询实现命题没有参加考试的同学的学生编号，姓名，考试成绩。     
select Fstubianhao,FstuName  from T_Student
where T_Student.FstuId not in (select T_Score.FstuId from T_Score)