转载：SQL SERVER 2005新增语法
1.表的同义名(Synonyms)
为表创建同义名,这样就可以在Select语句里边使用表的同义名了.注意在更改表的定义的时候只能使用表的原名,而不能使用表的同义名.
--创建同义名
CREATE SYNONYM syn_OR
FOR dbo.Orders
--使用同义名查询表
select * from syn_OR
--删除同义名
DROP SYNONYM syn_OR
2.INTERSECT 操作符
INTERSECT操作符就是取表集A和表集B的交集.
--查询,Northwind数据库
select * from orders
intersect
select * from orders
where customerid='vinet'
3.varchar(max),nvarchar(max),varbinary(max)数据类型.
还记得在大文本的时候,我们被迫使用text数据类型带来的不便吗?这回好了,2005带来的新的数据类型兼容以前的varchar数据类型,但是存储的空间更大了.varchar(max)和varbinary(max)可以存储231,而nvarchar(max)可以存储230字节,够用了吧!
-- 创建测试表
create table #t(id int,Study_Varcharmax varchar(max))
-- 插入数据
insert #t select 1,'Imagine this is a very long non-Unicode string'
insert #t select 2,'Imagine this is another very long non-Unicode string'
-- 使用.write语法来进行更新
update #t
set Study_Varcharmax .write('n incredibly', 17, 5)
where id = 1
-- 查询结果.
select * from #t
当然varchar(max)完全兼容以前的varchar数据类型,比如你可以在游标的fetch语句取得它的值,使用.write更新,在触发器的inserted或者deleted里边引用,还可以使用字符串函数如,len,substr等.
4.Top后边的参数.
还记得Top后边不能使用参数给我们带来的不便吗?2005终于可以在Top后边使用参数了,快来和我一起试验吧!
DECLARE @n int
SET @n = 10
SELECT TOP(@n) *
FROM Products
ORDER BY UnitPrice DESC
5.获取DML语句所影响的行.
以前我们只能通过触发器来获取删除或者更新语句所影响的行的信息,现在我们只要使用新的Output语法,就能轻松的获取DML语句所影响的行的信息.
-- 创建测试表,并插入测试数据
create table #t(id int,name varchar(200))
insert #t select 1,'first'
insert #t select 2,'second'
-- 使用表变量来保存输出结果集.
declare @tb_t table(id int,name varchar(200))
update #t set name='abc'
output inserted.id,inserted.name into @tb_t
where id=1
select * from @tb_t
6.新增的Some,Any,All运算符.
语法:<scalar_expression> { = | <> | != | > | >= | !> | < | <= | !<}
{SOME | ANY} {subquery}
子查询返回一个结果集,scalar_expression和结果集任何一个比较成功,就返回True.
<scalar_expression> { = | <> | != | > | >= | !> | < | <= | !<}
All {subquery}
自查询返回一个结果集,scalar_expression只有和结果集所有比较成功,才返回True.
刚接触这个运算符,感觉和in比较类似,而实际上如果合理运用,这个运算符的功能是非常强大的.
select *
from Products
where CategoryID=any(select categoryid
from dbo.Categories
where categoryName in ('Produce','Seafood'))
7.PIVOT and UNPIVOT
PIVOT and UNPIVOT的功能相信大家都已经熟悉了,我们结束了用拼凑SQL字符串的方式来进行行列转换的时代.但是Pivot的功能不只如此,我觉得它最大的功能是能让我们灵活的对数据表进行聚集分析,就像OLAP多维数据集那样.
-- Northwind的Order表:统计每个雇员在1996,1997,1998的定单数量.
select EmployeeID,[1996] as Y_1996,[1997] as Y_1997,[1998] as Y_1998
from
(
select EmployeeID,Year(OrderDate) as Year_Order,OrderID
from orders
) t
pivot
(count(OrderID) for Year_Order in ([1996],[1997],[1998])) pvt
order by EmployeeID
8.表值函数增强功能.
以前我们使用表值函数,只能用select * from 表值函数 的形式,如果我们想和别的表连接就做不到了.现在2005新增的{CROSS | OUTER} APPLY运算符可以让我们轻松的做到这一点.下边我就以一个例子来说明.
表1(t):sail cmd
9258 LBQR|LB
92587 A|19|1
想变成如下的形式:
sail cmd
9258 LBQR
9258 LB
92587 A
92587 19
92587 1
-- 步骤1:表值函数
create function dbo.fn_t(@sail int,@cmd varchar(1000))
returns @tb_t table(sail int,cmd varchar(1000))
as
begin
while(charindex('|',@cmd)>0) -- 循环取得列表值,并插入@tb_t表里边.
begin
insert @tb_t select @sail,left(@cmd,charindex('|',@cmd)-1)
set @cmd=stuff(@cmd,1,charindex('|',@cmd),'')
end
insert @tb_t select @sail,@cmd -- 插入最后一个值
return
end
Go
-- 步骤2:查询
select t.sail,b.cmd
from t
cross apply dbo.fn_t(t.sail,t.cmd) b
order by t.sail
9.新的Ranking函数.
a.Row_Number()函数
-- Northwind表：对顾客的City进行排名
select Row_Number() over(Order By City) as RowID,*
from Customers
-- Northwind表:对国家内的城市进行排名
select Row_Number() over(Partition by Country Order By City),*
from Customers
b.Dense_Rank()函数
总的来说，Dense_Rank()函数和Row_Number()函数的功能是类似的，只不过在排名结果上略有不同．在有相同的值的时候，Dense_Rank()排名是连续的，而Row_Number()排名是跳跃的．
10.错误处理.(try catch)
还记得我们在SQL脚本里边使用大量的if @@error>0这样的语句来进行错误检测吗?现在好了,2005的try catch语法可以让我们轻松的进行错误检测.注意try catch 不能检测严重级小于等于10或者的错误,因为这样的错误只是一些警告信息.也不能检测严重级别大于20的的错误,因为这样的错误会终止数据库引擎的工作.
-- 创建测试表
create table #t(id int identity(1,1),name varchar(100) not null)
-- 执行脚本
begin try
insert #t(name) select Null --插入空值,违反了非空的约束条件.
end try
begin catch
select ERROR_NUMBER( ) ErrorNumber,
ERROR_STATE( ) ErrorState,
ERROR_SEVERITY( ) ErrorSeverity,
ERROR_MESSAGE( ) ErrorMessage
end catch
11.DDL触发器.
我记得以前我们总是为一个表的突然失踪而苦恼,不知道这个表被谁给删除了.现在好了,使用DDL触发器,我们可以追踪表的去向了.
-- 测试表
create table t(id int identity(1,1),name varchar(100) not null)
-- 日志记录表.记住详细信息是存储在xml类型中.
create table DB_Log(F_LogID int identity(1,1),F_Content xml not null)
-- 创建触发器.
create trigger tri_t
on database
for create_table,drop_table
as
insert into DB_Log values(EventData())
Go
--删除表
drop table t
select * from DB_Log
-- 追踪信息.看起来信息非常详细的.足够我们把"凶手"追查出来的.
<EVENT_INSTANCE>
<EventType>DROP_TABLE</EventType>
<PostTime>2006-05-13T09:11:15.767</PostTime>
<SPID>54</SPID>
<ServerName>LILY\STUDY2005</ServerName>
<LoginName>LILY\Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>Northwind</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>t</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>drop table t
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
12.使用OpenRowSet来读取数据文件.
以前我们只能用OpenRowSet来读取别的数据源的数据,但是对于数据文件的读取就不那边方便了.新的OpenRowSet语法增加了Bulk功能,可以让我们直接从数据文件里边读取数据.
-- 生成用于测试的数据文件和格式文件.(以Northwind的Customers表为例)
Bcp Northwind.dbo.Customers out D:\Customer.txt -T -c
Bcp Northwind.dbo.Customers format nul -f D:\Customer.fmt -T -c
-- 使用OpenRowSet来读取刚才生成的数据文件.
select *
from OpenRowSet(Bulk N'D:\Customer.txt',FormatFile=N'D:\Customer.fmt') as t
13.外连接的另类语法.
以前内连接我们可以使用where来代替,但是外连接就没有这样的语法了.2005新推出的*=和=*让我们做到了这点.
-- Northwind
select a.*,b.*
from Products a,Categories b
where a.CategoryID*=b.CategoryID
浙公网安备 33010602011771号