高级查询
01.简单子查询
子查询优势 1不用变量。2所想即所得。
案例1:查询学生表中比"美洋洋"小的学员信息(姓名,地址)
案例2:查询“oop”课程至少一次考试刚好等于60分的学生(姓名,成绩)
--用子查询如何实现???
--用到什么条件,交给子查询解决
selectstudentname,studentresult
fromStudent,Result
whereStudent.StudentNo=Result.StudentNo
andSubjectId=(selectSubjectIdfromSubjectwhereSubjectName='oop')
andStudentResult=60
--只检索学生姓名
selectstudentname
fromStudent
whereStudentNoin
(selectStudentNo
fromResult
whereStudentResult=60andSubjectId=
(selectSubjectId
fromSubject
whereSubjectName='oop'
)
)
子查询 小结
简单子查询(嵌套子查询)的执行机制:
将子查询的结果作为外层父查询的一个条件。
也就意味着先执行子查询,再执行父查询
子查询:子查询语句必须用小括号括起来,
然后通过比较运算符:>、<,=等连接起来
注意点:.子查询必须用小阔号括起来
子查询先执行出一个结果,然后将该结果作为父查询
的一个条件而存在。
in/notin子查询
案例:查询最近一次未参加oop考试的学生名单
selectstudentname
fromstudent
wherestudentnonotin
(
selectstudentnofromresult
wheresubjectidin
(
selectsubjectidfromsubjectwheresubjectname='oop'
)
andexamdate=
(
selectmax(examdate)fromresultwheresubjectidin
(
selectsubjectidfromsubjectwheresubjectname='oop'
)
)
)
andgradeid=
selectgradeidfromgrade
wheregradename='S1'
)
在这里注意 =和in 的用法
当返回的数据不是一行,而是多行的时候 使用 in 反之 则使用= 。
Exists和NotExists子查询
案例:检查“oop”课程最近一次考试。
select*fromresult
orderbysubjectid,examdate
ifexists
(
select*fromresultwheresubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
andexamdate=
(
selectmax(examdate)fromresult
wheresubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
)
andstudentresult>=80
)
begin
updateresultsetstudentresult=100
wherestudentresult>98
andsubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
andexamdate=
(
selectmax(examdate)fromresult
wheresubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
)
updateresultsetstudentresult+=2
wherestudentresult<=98
andsubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
andexamdate=
(
selectmax(examdate)fromresult
wheresubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
)
end
else
begin
updateresultsetstudentresult+=5
wherestudentresult<=95
andsubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
andexamdate=
(
selectmax(examdate)fromresult
wheresubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
)
updateresultsetstudentresult=100
wherestudentresult>95
andsubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
andexamdate=
(
selectmax(examdate)fromresult
wheresubjectid=
(
selectsubjectidfromsubject
wheresubjectname='oop'
)
)
end
selectstudentno,studentnamefromstudent
union
selectgradeid,gradenamefromgrade
--product(id,proname,category)
selectdistinct(gradename)fromgrade
--重要:ifexists(子查询)子查询返回的必须是一个结果集,而不是一个bool值。
--结果集(用一个表结构将数据呈现出来,如果没有结果,返回的是一个空表)
--子查询的列可以跟单个列名,也可以跟星号,但是不能跟聚合函数,因为聚合函数
--返回的值永远是真,因为聚合函数也是结果集的一种,不能作为Exists判定的依据。
相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。执行过程:
(1)从外层查询中取出一个元组,将元组相关列的值传给内层查询。
(2)执行内层查询,得到子查询操作的值。
(3)外查询根据子查询返回的结果或结果集得到满足条件的行。
(4)然后外层查询取出下一个元组重复做步骤1-3,直到外层的元组全部处理完毕。
分页
分页目的:为了加快网站对数据的查询(检索)速度,我们引入了分页的概念。
方式一:核心思想:跳过几条取几条(双top双orderby方式)
--分页查询
--双top双order by 例(跳五行查两行)
select top 2* from Student
where StudentNo not in
(select top 5 StudentNo from Student
order by StudentNo)
order by StudentNo
方式二:局限性(SQLServer之后的版本支持该写法,因为我们要用到row_number()over()函数,在之前是没有该函数)
select*from
(select*,row_number()over(orderbystudentno)asmyidfromstudent)astemp
wheremyidbetween4and6。