sql server

利用变量进行子查询

1.declare @date datetime

2.select @date=borndate from student

where studentname='张三'

3.select * from student where BornDate>@date

或者

select * from student where BornDate>（

select borndate from student

where studentname='张三'）

或者通过表连接的方式进行查询

select a.* from student as a, 

sutdent as b 

where b.studentname='张三' and a.borndate>b.Borndate

'in' 子查询，当查询结果有多个的时候用'in'，（非的方法为 not in）

select * from student where studentno in (

select studentno from result where studentresult=60)

if exists查询和更新操作

if exists (select * from result where studentresult>=80)

        update result set studentresult=studentresult+2

else

        update result set studentresult=studentresult+5

多项统计及计算比率

select count(*) as 及格人数，

（select count(*) from result) as 实参加人数

（select count(*) from student) as 应参加人数，

((convert（float，count(*))/(select count(*) from result)) as 通过率    ---反斜杠为整除符号

from result

where studentresult>=60

当报语法错误的时候，可以把查询结果命名为表名

select top 3 * from (select top 6 * from result order by studentresult desc)

as tb1

order by studentresult