SQL server2005 学习笔记

1.导出.xls文件
sql server 2005下开启xp_cmdshell的办法
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
EXEC master..xp_cmdshell 'bcp db#nhibernate.dbo.users out c:/Temp.xls -c -q -S "WIN2003A" -U "sa" -P "sa"'
2.case 用法
update person set name=Case when name='女' then '男'
                       else  '女' end
2.函数调用
use db#nhibernate
go
create function get_supinfo(@num1 int,@num2 int,@str char(2))
returns varchar(2000)
as
begin
declare @sum as int
set @sum=case when @str='*' then @num1*@num2
              when @str='-' then @num1-@num2
              when @str='/' then @num1/@num2
              when @str='+' then @num1+@num2
              else 0 end
return convert(varchar(2000),@sum);
end
select dbo.get_supinfo(3,2,'+')
3.查询本月或者本周，本季，本年
本周：select * from table where datediff(week,C_CALLTIME,getdate())=0     --C_CALLTIME 为日期字段
本月：select * from table where datediff(Month,C_CALLTIME,getdate())=0   --C_CALLTIME 为日期字段
本季：select * from table where datediff(qq,C_CALLTIME,getdate())=0
前半年1-6，后半年7-12：select * from table where datepart(mm,C_CALLTIME)/7 = datepart(mm,getdate())/7
4.导入xls文件
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
INSERT INTO [db#nhibernate].[dbo].[AdvCpy]
 select 目录名称  FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0',
 'Data Source="C:/Tcity.xls";User ID=Admin;Password=;Extended properties=Excel 12.0')...Sheet2$ 
5、行变列
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR,',')+PdtSimName FROM Product

SELECT @STR
6、将表的列名弄成字符串
--获取列名
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR,'')+','+a.Name FROM (Select   SysColumns.Name   From   SysColumns  
Left   Outer   Join   SysObjects   On 
 SysColumns.ID=SysObjects.ID   Where   SysObjects.Name= 'FinDue ' ) a
SELECT @STR