SQL Server 总结复习 （二）

1. 排名函数与partition by

--所有数据
select * from dbo.student as a inner join dbo.scoretb as b on a.id = b.stuid
where scorename = '语文'
-------------------------------------------
--row_number() 的使用 生成列从1开始依次增加
-------------------------------------------
select row_number() over (order by b.score desc) as rownumber ,a.name, b.score, a.id
from dbo.student as a inner join dbo.scoretb as b on a.id = b.stuid
where scorename = '语文'
--也可以在后面再加一个order by，则表示前面生成后的全部列又被以最后的列重新排列（排名列值不变）
select row_number() over (order by b.score desc) as rownumber ,a.name, b.score, a.id
from dbo.student as a inner join dbo.scoretb as b on a.id = b.stuid
where scorename = '语文' order by a.id
--要在分组统计后使用排名函数，则先进行分组，用cte或嵌套查询表整出结果集，再用row_number函数处理
with b as
(
select stuid, sum(score) as score from scoretb group by stuid
)
select * ,row_number() over (order by b.score desc) as rownumer
from dbo.student as a inner join b on a.id = b.stuid
----------------------------------------------------------------------------
--rank() 用法与row_numer函数想同，只是在出现order by同级时，排名会设置成一样，而下一个会根据之前的记录数生成序号
--例如前面三个是一样的，那么都是1，下一个则是4，示例略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--dense_rank() 密集排名 用法与row_numer、rank函数相同，只是在生成序号时是连续的，而rank函数生成的序号有可能不连续
--例如前面三个是一样的，那么都是1，下一个则是2，示例略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。
--为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。
--它有一个参数，用来指定桶数，例如
----------------------------------------------------------------------------
select ntile(2) over (order by b.score desc) as groupid ,a.name, isnull(b.score,0) score, a.id
from dbo.student as a left join dbo.scoretb as b on a.id = b.stuid and scorename = '语文'
--------------------------------------------------------------------------
--partition by 类似于向排名函数应用一个group by，分组后对每一个组单独排名
--------------------------------------------------------------------------
--统计各个学科的排名依次为：
select rank() over (partition by b.scorename order by b.score desc) as rownumber,b.scorename,
b.score, a.name, a.id from dbo.student as a inner join dbo.scoretb as b on a.id = b.stuid order by scorename

2. top 新用法

declare @num int = 101
select top (@num) * from student order by id --必须用括号括起来
select top (@num) percent * from student order by id --只接受float并且1-100之间的数，如果传入其他则会报错

3. group by all 字段 / group by 字段
前者有点像left join ,right join的感觉，两者的主要区别体现在有where条件被过滤的聚合函数，会重新抓取出来放入查询的数据表中，只是聚合函数会根据返回值的类型用默认值0或者null来代替聚合函数的返回值。
当然从效率上来说，后者优于前者，就像inner join 优于left join一样
4. count(*)/count(0) 与 count(字段)
如果查询出来的字段中没有null值，那么俩种查询条件无任何区别，如果有null，后者统计出来的记录则是 总记录数 - null记录数
从性能上来说，前者高于后者，因为后者会逐行扫描字段中是否有null值，有null则不加以统计，减少了逻辑读的开销，从而性能达到提升
5. top n with ties 的用法
详见 http://www.cnblogs.com/skynet/archive/2010/03/29/1700055.html
举个例子
select top 1 with ties * from student order by score desc
等价于
select * from student where score=(select top 1 score from student order by score desc)
6. apply运算符

view code
--准备数据
create table [dbo].[student](
[id] [int] null,
[name] [varchar](50) null
)
go
insert into dbo.student values (1, '张三')
insert into dbo.student values (2, '李斯')
insert into dbo.student values (3, '王五')
insert into dbo.student values (4, '神人')
go
create table [dbo].[scoretb](
[stuid] [int] null,
[scorename] [varchar](50) null,
[score] int null
)
go
insert into [scoretb] values (1,'语文',22)
insert into [scoretb] values (1,'数学',32)
insert into [scoretb] values (1,'外语',42)
insert into [scoretb] values (2,'语文',52)
insert into [scoretb] values (2,'数学',62)
insert into [scoretb] values (2,'外语',72)
insert into [scoretb] values (3,'语文',82)
insert into [scoretb] values (3,'数学',92)
insert into [scoretb] values (3,'外语',72)
--创建表值函数
create function [dbo].[fgetscore](@stuid int)
returns @score table
(
[stuid] [int] null,
[scorename] [varchar](50) null,
[score] int null
)
as
begin
insert into @score
select stuid,scorename,score from dbo.scoretb where stuid = @stuid
return;
end
go
--开始使用
select a.id,a.name,b.scorename,b.score from [student] a
cross apply [dbo].[fgetscore](a.id) b --相当于inner join效果
select a.id,a.name,b.scorename,b.score from [student] a
outer apply [dbo].[fgetscore](a.id) b --相当于left join效果
--而不能这样使用
--select a.id,a.name,b.scorename,b.score from [student] a
-- inner join [dbo].[fgetscore](a.id) b on a.id = b.stuid
-- select a.id,a.name,b.scorename,b.score from [student] a
-- inner join (select * from [dbo].[fgetscore](a.id)) b on a.id = b.stuid

7. intersect和except运算符
except 只包含excpet关键字左边而且右边的结果集中不存在的那些行 intersect 只包含两个结果集中都存在的那些行
往往exists关键字可以代替上面的关键字，并且从性能中可以看到比他们更好，但except/intersect更便于阅读和直观。还是建议从性能更优入手。
8. 索引提高查询效率的原理
索引与exists运算符在处理方式上很像，它们都可以在找到匹配值后立即退出查询运行，从而提高了查询性能
9. 表变量与临时表
主要区别： 1表变量不写日志，没有统计信息，频繁更改不会造成存储过程重新编译，不能建索引和统计信息，但是可以建立主键，变通实现索引查找，表变量不只是在内存中操作，数据量大的情况也会写tempdb，即物理磁盘的io操作。 2.事务回滚对表变量无效（原因没有统计信息）
一般来说，数据量大，临时结果集需要和其他表二次关联用临时表 数据量小，单独操作临时结果集用表变量
10. 脚本和批处理
go不是一条t-sql命令，他只能被编译工具management studio, sqlcmd识别，如果用第三方工具，不一定支持go命令。例如ado.net,ado。
11. sqlcmd的运用
sqlcmd -usa -psa -q "select * from testdb.dbo.mytable"
sqlcmd -usa -psa -i testsql.sql 运行文件里的sql语句
12. exec 使用说明
在执行过exec之后，可以使用类似@@rowcount这样的变量查看影响行数；不能在exec的参数中，针对exec字符串运行函数，例如cast(xx as varchar)，对于exec的参数，只能用字符串相加，或者是整体的字符串。
13. waitfor 的含义
waitfor time <'time'> 定时执行; waitfor delay <'time'> 延迟执行
14. 存储过程 总结
1）用try/catch 替代 @@error这种更科学，其一@@error没有tra/catch直观，其二遇到错误级别在11-19的错误，错误会使运行直接中断，导致@@error判断错误与否无效。
2）使用raiserror 抛错
with log，当严重级别大于等于19时，需要使用这个选项
with seterror，使其重写@@error值，方便外部调用
with nowait 立刻将错误通知给客户端
15. 游标的复习
游标主要部分包括：1）声明 2）打开 3）使用或导航 4）关闭 5）释放
嵌套使用游标示例

declare billmsgcursor cursor for
select typeno,tabdetailname from dbo.billtype
open billmsgcursor
declare @typeno char(5)
declare @detailname varchar(50)
fetch next from billmsgcursor into @typeno,@detailname
while @@fetch_status = 0
begin
declare @datafieldname varchar(50)
declare columnname cursor for
select name from syscolumns where id = object_id(@detailname)
open columnname
fetch next from columnname into @datafieldname
print '单据编号:' + @typeno
while @@fetch_status = 0
begin
print 'listdetaildatafiled.add('''+@datafieldname+''');'
fetch next from columnname into @datafieldname
end
close columnname
deallocate columnname
fetch next from billmsgcursor into @typeno,@detailname
end
close billmsgcursor
deallocate billmsgcursor

@@fetch_status值的意义：0 fetch 语句成功；-1 fetch 语句失败或此行不在结果集中；-2 被提取的行不存在
fetch [next/prior/first/last] from ... into 针对游标为scroll类型的
16. 游标的分类
1）静态游标（static）：相当于临时表，会保存在tempdb里的私有表中，犹如快照表复制一份
a.一旦创建了游标，它就与实际记录相分离并不再维持任何锁
b.游标就是独立的，不再以任何方式与原始数据相关联
2）键集驱动的游标（keyset）：需要在一定程度上感知对数据的修改，但不必了解最新发生的所有插入
a.表必须具有唯一索引
b.只有键集在tempdb中，而非整个数据集，对整个服务器性能产生有利的影响
c.能感知到对己是键集一部分的行所做的修改（改删），不能感知新增
3）动态游标（dynamic）
a.完全动态，非常敏感，对底层数据做的所有事情都会影响，性能当然也是最差的
b.它们会带来额外的并发性问题
c.每发出一次fetch，都要重建游标
d.可允许运行没有唯一索引的表中，但弊端会造成sqlserver无法追踪它在游标的位置造成死循环，应避免这样使用
4）快进游标（fast_forward）
在许多情况下，fast_forward游标会隐式转换为其他游标类型