create database Lession3
go
use Lession3
create table StuInfo(
StuID int identity primary key not null,
StuName varchar(10) not null,
StuSex char(2) not null,
ClassID int not null
)
go
use Lession3
insert into StuInfo(StuName,StuSex,ClassID) values('李四','男',1),('钱七','女',2),('王五','男',1),('张三','女',1),('赵六','女',2)
go

use Lession3
create table StuMarks(
ExamNO int identity primary key not null,
StuID int  not null,
Subject varchar(10) not null,
Score int not null
)
go
use Lession3
insert into StuMarks(StuID,Subject,Score) values(1,'HTML',85),(1,'Java',80),(1,'SQL',82),(2,'HTML',70),(2,'Java',81),(2,'SQL',60),(3,'HTML',70),(3,'Java',90),(3,'SQL',85),(4,'Java',61),(4,'SQL',68),(5,'HTML',90),(5,'Java',81),(5,'SQL',65)
go

--查询李四同学分数大于80分的考试成绩记录
--连接查询实现
select StuName,Subject,Score from StuInfo s1,StuMarks s2 where s1.StuID = s2.StuID 
and s1.StuName='李四' and s2.Score > 80

--子查询实现
select StuName,Subject,Score from StuInfo s1,(select * from StuMarks where Score>80) s2
where s1.StuID=s2.StuID and s1.StuName='李四'


--查询学号在王五同学前面的学员信息
select * from StuInfo where StuID < (select StuID from StuInfo where StuName='王五')

--查询所有学员的html成绩，如果没有成绩显示成null
--子查询作为临时表使用
select s1.*,s2.Score from StuInfo s1 left outer join (select * from StuMarks where Subject='HTML') S2 on s1.StuID = s2.StuID

--子查询作为列使用
select s1.*,(select Score from StuMarks s2 where s1.StuID=s2.StuID and Subject='HTML') Score from StuInfo s1

--使用IN完成子查询
--查询学号为1和3的学员信息
select * from StuInfo where StuID in(1,3)

--查找Java分数大于85分的学员姓名
select StuName from StuInfo where StuID in (select StuID from StuMarks where Score>85 and Subject='Java')

--EXISTS 和 NOT EXISTS案例
--查询存在分数的学员信息
select * from StuInfo where EXISTS (select * from Stumarks where StuMarks.StuID = StuInfo.StuID)

select * from StuInfo where NOT EXISTS (select * from Stumarks where StuMarks.StuID = StuInfo.StuID)

--ANY,SOME 父查询中的结果集大于子查询中任意一个结果集中的值,则为真
select * from StuInfo where StuID > SOME (select StuID from Stumarks where Score>80 and Subject='SQL')
select * from StuInfo where StuID > ANY (select StuID from Stumarks where Score>80 and Subject='SQL')
--ALL 父查询中的结果集大于子查询中每一个结果集中的值,则为真
select * from StuInfo where StuID > ALL (select StuID from Stumarks where Score>80 and Subject='SQL')

--排序函数示例
--对学员的java成绩进行排名
--row_number() (没有并列编号，不跳空编号)
select ROW_NUMBER() OVER(order by Score DESC) AS 排名,s1.StuName,s2.Score from StuInfo s1,StuMarks s2
where s1.StuID = s2.StuID and s2.Subject='Java'

--rank()(有并列编号，有跳空编号)
select rank() OVER(order by Score DESC) AS 排名,s1.StuName,s2.Score from StuInfo s1,StuMarks s2
where s1.StuID = s2.StuID and s2.Subject='Java'

--dense_rank()(有并列编号，没有跳空编号)
select dense_rank() OVER(order by Score DESC) AS 排名,s1.StuName,s2.Score from StuInfo s1,StuMarks s2
where s1.StuID = s2.StuID and s2.Subject='Java'

--公式表表达式示例
--查询学员与其相应的成绩
WITH StuInfo_Mark(StuID,StuName,Subject,Score)
AS(
    select s1.StuID,s1.StuName,s2.Subject,s2.Score from StuInfo s1,StuMarks s2 where s1.StuID=s2.StuID

)
select * from StuInfo_Mark
GO