子查询
--01.简单子查询(嵌套子查询)的执行机制：
--将子查询的结果作为外层父查询的一个条件。
--也就意味着先执行子查询，再执行父查询
--子查询:子查询语句必须用小括号括起来，然后通过
--比较运算符：>、<,=等连接起来
--注意点：.子查询必须用小阔号括起来
--2.子查询先执行出一个结果，然后将该结果作为父查询
--的一个条件而存在
select * from Student
where Birthday>
(
select Birthday from Student
where StudentName='李连杰'
)
--01.查询参加最近一次“OOP”考试成绩最高分和最低分
--人家要什么数据你就在select后写什么
--02.查询“oop”课程至少一次考试刚好等于分的学生姓名
--分析思路：查询结果是是什么，就写到Select后，
--然后根据需要的条件一步一步往下写，用到某个变量，需要
--跨表访问，让子查询帮我们完成。
--子查询要想用的熟练，必须对表之间的公共字段(外键)特别清晰。
--在脑海中能随意存取各表之间的对应关系。
--子查询引用场景要比表连接广
--子查询书写黄金法则：
所想即所得。--所有用到的限定条件都找子查询完成。
02.in/not in 子查询
案例1：.查询参加“oop”课程最近一次考试的在读学生名单(学生姓名，学生编号)
--既然查询在读学生名单，必须是成绩表中出现的学号
select studentname,studentno
from student
where studentno in
(
select studentno from result
where subjectid in
(
select subjectid from subject
where subjectname='oop'
)
and examdate=
(
select max(examdate) from result
where subjectid in
(
select subjectid from subject
where subjectname='oop'
)
)
)
案例2：.查询S1开设的课程
select * from subject
select * from grade
select subjectname from subject
where gradeid in
(
select gradeid from grade where gradename='S1'
)
select * from result
order by subjectid,examdate
select * from student
案例3：查询最近一次未参加oop考试的学生名单（studentname）
select studentname
from student
where studentno not in
(
select studentno from result
where subjectid in
(
select subjectid from subject where subjectname='oop'
)
and examdate=
(
select max(examdate) from result where subjectid in
(
select subjectid from subject where subjectname='oop'
)
)
)
and gradeid= --过滤掉本身就不用参加该门课程考试的学生
(
select gradeid from grade
where gradename='S1'
)
03.Exists和Not Exists子查询
use master
if exists (select * from sysdatabases where name='张三')
begin
print '你存在！'
end
else
print '不存在'
--判定符合条件的数据是否存在，如果存在，执行分支A，如果不存在，执行
--分支B
案例1：检查“oop”课程最近一次考试。
--如果有80分以上的成绩，则每人提分；
--否则，每人提分。最终的成绩不得大于分
--01.保证课程是oop
--02.最近一次
--检查是否有分以上的成绩
use myschool
select * from result
order by subjectid ,examdate
if exists
(
select * from result where subjectid=
(
select subjectid from subject
where subjectname='oop'
)
and examdate=
(
select max(examdate) from result
where subjectid=
(
select subjectid from subject
where subjectname='oop'
)
)
and studentresult>=80
)--如果有结果，有分以上的学员，每人加分
begin
--存在，加分
--99
--分情况，如果分数<=98加分，否则直接update成分 97
--将大于的分数置成
update result set studentresult=100
where studentresult>98
and subjectid=
(
select subjectid from subject
where subjectname='oop'
)
and examdate=
(
select max(examdate) from result
where subjectid=
(
select subjectid from subject
where subjectname='oop'
)
)
update result set studentresult+=2
where studentresult<=98
and subjectid=
(
select subjectid from subject
where subjectname='oop'
)
and examdate=
(
select max(examdate) from result
where subjectid=
(
select subjectid from subject
where subjectname='oop'
)
)
end
else --每人加分
begin
--每人加分
update result set studentresult+=5
where studentresult<=95
and subjectid=
(
select subjectid from subject
where subjectname='oop'
)
and examdate=
(
select max(examdate) from result
where subjectid=
(
select subjectid from subject
where subjectname='oop'
)
)
--将大于的分数置成
update result set studentresult=100
where studentresult>95
and subjectid=
(
select subjectid from subject
where subjectname='oop'
)
and examdate=
(
select max(examdate) from result
where subjectid=
(
select subjectid from subject
where subjectname='oop'
)
)
end
--使用union关键字进行表的联合查询
select studentno,studentname from student
union
select gradeid,gradename from grade
--使用distinct关键字进行去重操作
--product (id,proname,category)
select distinct(gradename) from grade
--重要：if exists(子查询) 子查询返回的必须是一个结果集，而不是一个bool值。
--结果集(用一个表结构将数据呈现出来，如果没有结果，返回的是一个空表)
--子查询的列可以跟单个列名，也可以跟星号，但是不能跟聚合函数，因为聚合函数
--返回的值永远是真，因为聚合函数也是结果集的一种，不能作为Exists判定的依据。
--06：为每个学生制作在校期间每门课程的成绩单，
--要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩
--成绩单中包括：
--学生姓名
--课程所属的年级名称
--课程名称
--考试日期
--考试成绩
--01.最终我们是要获取成绩单:包含信息（学生姓名,课程年级名称,课程名称,考试日期,考试成绩)
--02.【每个学员】的【每门课程】 【最后一次】三者都是限定
--相关子查询
相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。执行过程：
（1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。
（2）执行内层查询，得到子查询操作的值。
（3）外查询根据子查询返回的结果或结果集得到满足条件的行。
（4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。
下面举例说明：
--查询Booka表中大于该类图书价格平均值的图书信息
SElECT 图书名,出版社,类编号,价格
FROM Books As a
WHERE 价格 >
(
SELECT AVG(价格)
FROM Books AS b
WHERE b.类编号=a.类编号
)
GO
与前面介绍过的子查询不同，相关子查询无法独立于外部查询而得到解决。该子查询需要一个“类编号”的值。而这个值是个变量，随SQLSever检索Books表中的不同行而改变。下面详细说明该查询执行过程：
先将Books表中的第一条记录的“类编号”的值“2”代入子查询中，子查询变为：
SELECT AVG(价格)
FROM Books AS b
WHERE b.类编号=2
子查询的结果为该类图书的平均价格，所以外部查询变为：
SElECT 图书名,出版社,类编号,价格
FROM Books As a
WHERE 价格 > 34
如果WHERE条件为True，则第一条结果包括在结果集中，则否不包括。对Books表中的所有行运行相同的过程，最后形成的结果集及最后返回结果。
SELECT StudentName 姓名,
GradeName 课程所属年级,
SubjectName 课程名称, ExamDate 考试日期, StudentResult 成绩
FROM Result
INNER JOIN Student ON Result.StudentNo=Student.StudentNo
INNER JOIN Subject ON Subject.Subjectid=Result.Subjectid
inner join grade on subject.gradeid=grade.gradeid
WHERE Result.ExamDate = (
SELECT Max(ExamDate) FROM Result
WHERE Subjectid=Subject.Subjectid AND
StudentNo=Student.StudentNo
)
ORDER BY Result.StudentNo ASC,Result.Subjectid ASC
--只要知道子查询不一定先执行.还有相关子查询
SELECT StudentName 姓名,
( SELECT GradeName FROM Grade
WHERE GradeId=Subject.GradeId ) 课程所属年级,
SubjectName 课程名称, ExamDate 考试日期, StudentResult 成绩
FROM Result
INNER JOIN Student ON Result.StudentNo=Student.StudentNo
INNER JOIN Subject ON Subject.Subjectid=Result.Subjectid
WHERE Result.ExamDate = (
SELECT Max(ExamDate) FROM Result
WHERE Subjectid=Subject.Subjectid AND
StudentNo=Student.StudentNo
)
ORDER BY Result.StudentNo ASC,Result.Subjectid ASC
--查询大于平均成绩的学号,分数
select studentno,studentresult,subjectid
from result a
where studentresult>
(
select avg(studentresult)
from result b
where b.subjectid=a.subjectid
)
order by studentno,subjectid
select * from result
04.分页
--中国电信通话记录有上亿条数据
--服务器压力太大
--客户端等待时间过长
use zongjie
select * from info
分页目的:为了加快网站对数据的查询（检索）速度,我们引入了分页的概念.
--在SQL Server分页一般两种思路
--你会怎么搞呢？
use myschool
--每页条数据，我想要第二页的数据。
select * from student
方式一：跳过几条取几条（双top 双order by 方式）
select top 3 * from student
where studentno not in
(
select top 3 studentno from student
order by studentno
)
order by studentno
方式二：局限性（SQL Server2005之后的版本支持该写法，因为我们要用到row_number() over()函数，在之前是没有该函数）
select * from
(select *,row_number() over(order by studentno) as myid from student) as temp
where myid between 4 and 6
--注意点：子查询的排序方式，必须和父查询排序的方式一致
--SQL Server数据库下哪个版本都可以使用
select top 3 * from student
where studentno not in
(
select top 3 studentno from student order by studentno
)
order by studentno
--方式二：在SQL Server2005 后， 2012
--row_number() over()函数方式：实现分页
--原理：在原表的基础上加多了一列ID，ID列从开始给值，我们就可以使用
--Between and 给值。
select * from
(select *,row_number() over(order by studentno) as myid from student) as temp
where myid between 4 and 6