四．高级查询
1. 子查询和相关子查询
示例：
Select studentno,studentname,sex,borndate,address from student
Where borndate>
(
Select borndate from student where studentname=’李斯文’
)
采用变量实现:
Declare @birthday datetime
Seelct @birthday=borndate from student
Where studentname=’李斯文’
Select studentno,studentname,sex,borndate,address from student
Where borndate>@birthday
Go
语法：
Select ...... From 表1 where 列1>(子查询)
子查询必须放置在一对圆括号内，在列1后面除了‘>’,还可以使用其他运算符号。
注意：将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个。
问题:查询Java课程至少一次考试刚好等于60分的学生
方法一：
Select studentname from student stu
Inner join result r on stu.studentno=r.studentno
Inner join subject sub on r.subjectno=sub.subjectno
Where studentresult=60 and subjectname=’java’
Go
方法二：
Select studentname from student
Where studentno=
(
Select studentno from result
Inner join subject on result.subjectno=subject.subjectno
Where studentresult=60 and subjectname=’java’
)
Go
注意：一般来说，表连接都可以用子查询替代，但是反过来可不一定，有的子查询不能用表连接来替代。子查询比较灵活，方便，形式多样，适合于作为查询的筛选条件，而表连接更适合于查看多表的数据。
2.In 和 not in 子查询
使用in关键字可以使父查询匹配子查询返回的多个单列值
示例：
/*---采用in子查询---*/
Select studentname from student where studentno in
(
Select studentno from result
Where subjectno=
(
Select subjectno from subject
Where subjectname=’java’
)
And studentresult=60
)
Go
问题：查询参加Java课程最近一次考试的在读学生名单
（1）获得java 课程的课程编号
（2）根据课程编号查询最近一次Java考试的考试日期
（3）根据课程编号和最近的考试日期查询学生信息
Select studentno,studentname from student
Where studentno in
(
Select studentno from result
Where subjectno=
(
Select subjectno from subject
Where subjectname=’java’
) and examdate=
(
Selcct max(examdate) from result
Where subjectno=
(
Select subjectno from subject
Where subjectname=’java’
)
)
)
Not in 子查询：
示例：
/*---采用not in子查询，查看未参加考试的在读的学生名单---*/
Select studentno,studentname from student
Where studentno not in
(
Select studentno from result
Where subjectno=
(
Select subjectno from subject
Where subjectname=’java’
) and examdate=
(
Selcct max(examdate) from result
Where subjectno=
(
Select subjectno from subject
Where subjectname=’java’
)
)
)
3.Exists 和 not exists 子查询
Exists 关键字能够检测数据是否存在
--查询年级名称是否存在
if exists
(
select gradeid from Grade
where GradeName='S1'
)
begin
update Grade set GradeName='S2'
where GradeId in
(
select gradeid from Grade
where GradeName='S1'
)
end
else
begin
print '没有这个班级'
end
--查询学生姓名，年级名称，课程名称，考试日期，考试成绩
select Studentname,gradename,subjectname,examdate,StudentResult
from Grade
join Subject on Grade.GradeId=Subject.GradeId
join Result on subject.SubjectId=result.SubjectId
join Student on result.StudentNo=student.StudentNo
where student.StudentNo in
(
select studentno from Student
where Result.StudentNo=student.StudentNo
)
and
subject.SubjectId in
(
select subjectid from Subject
where ExamDate in
(
select MAX(examdate) from Result
where result.SubjectId=Subject.SubjectId
)
)
group by studentname,gradename,subjectname,examdate,StudentResult
子查询注意事项：
（1） 子查询语句可以嵌套在SQL语句的任何表达式出现的位置
（2）在子查询的select子句中不能出现text,ntext或image数据类型的列
（3）只出现在子查询中而没有出现在父查询中的表不能包含在输出列
合并多个表中的数据的3种方法是：联合，子查询和表连接
4.分页
分页的目的是：为了加快网站对数据的查询速度
--分页1
--跳过三条取三条
--
select top 3 * from student
where studentno not in
(
select top 3 studentno from student
)
---分页2
--在内存中增加一列 查询列的表 然后条件
--1.from 表名 2.where 条件 3.查询结果
select * from
(
select *,ROW_NUMBER() over(order by studentno) as myid
from student
) as temp
where myid between 4 and 6
五.
---查询6~10条的租房信息
select top 5 * from dbo.hos_house
where HMID not in
(
select TOP 5 HMID from dbo.hos_house
)
--查询张三发布的所有出租房信息
select dname as 区县,sname as 街道,htname as 户型,PRICE as 价格,TOPIC as 标题,CONTENTS as 描述,htime as 时间,copy as 备注
from dbo.hos_house
join dbo.hos_street on dbo.hos_house.SID=dbo.hos_street.SID
join dbo.hos_type on dbo.hos_house.HTID=dbo.hos_type.HTID
join dbo.hos_district on dbo.hos_street.SDID=dbo.hos_district.DID
where UID=
(
select UID from dbo.sys_user
where UNAME='张三'
)
---根据区县制作房屋出租清单
select htname as 户型,UNAME as 姓名,dname as 区县,sname as 街道
from dbo.hos_house
join dbo.sys_user on dbo.hos_house.UID=dbo.sys_user.UID
join dbo.hos_street on dbo.hos_house.SID=dbo.hos_street.SID
join dbo.hos_district on dbo.hos_street.SDID=dbo.hos_district.DID
join dbo.hos_type on dbo.hos_house.HTID=dbo.hos_type.HTID
where dbo.hos_street.sID in
(
select SID from dbo.hos_street
where SDID in
(select SDID from dbo.hos_street
where sid in
(
select sid from dbo.hos_house
group by sid
having COUNT(HMID)>0
)
group by SDID
having COUNT(SDID)>=2
)
)
order by 户型
六.事务，视图和索引
1. 事务是单个的工作单元，不可分割的整体。具有要么都成功或者都失败的属性
事务的四个属性：原子性（atomicity） 一致性（consistency） 隔离性（isolation） 持久性（durability）
2.执行事务的语法：
（1）开始事务
begin transaction
(2)提交事务
commit transaction
(3)回滚事务
rollback transaction
SQL Server的默认模式：自动提交事务
use Myschool
create table bank
(
id int identity(1,1) primary key not null,
name varchar(32),
price decimal(18,2)
)
insert into bank
values('张三',1000)
insert into bank
values('李四',1)
--添加检查约束，银行卡余额必须>=1
alter table bank
add constraint ck_price check(price>=1)
--开始转账
--开启事务
begin transaction
declare @errorSum int
set @errorSum=0
--更改张三的余额
update bank set price-=500
where id=1
set @errorSum=@errorSum+@@ERROR
--更改李四的余额
update bank set price+=500
where id=2
set @errorSum=@errorSum+@@ERROR
if(@errorSum>0)
begin
--存在错误回滚事务
rollback transaction
end
else
begin
--没有错误提交事务
commit transaction
end
3.视图
视图是一种虚拟的表，通常是作为来自一个或多个表的行或列的子集创建的。
视图并不是数据库中存储的数据值的集合，它的行和列来自查询中引用的表，在执行时，它直接显示来自表中的数据。
视图充当着查询中指定表筛选器
4.创建视图的语法:
create view vw_name
as
<select语句>
删除视图的语法：
drop view vw_name
查看视图的语法：
select * from vw_name
示例：
use Myschool
create view vw_stuinfo
as
select Studentname,gradename,subjectname,examdate,StudentResult
from Grade
inner join Subject on Grade.GradeId=Subject.GradeId
inner join Result on subject.SubjectId=result.SubjectId
inner join Student on result.StudentNo=student.StudentNo
and student.GradeId=Grade.GradeId
go
select * from vw_stuinfo
索引（Index）加快表中记录的检索速度，同时降低增删改速度