四.高级查询
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)加快表中记录的检索速度,同时降低增删改速度