SQL学习笔记

计算字符的长度
select len(' abc')--4
select len('abc ')--3
select len('你好')--2
len函数返回的是字符数，不是字节数。
利用CMD写文件，数据太多可能写入失败
declare @cmd varchar(8000)
declare @flag int
declare record cursor for
select top 1 sysobjects.name,syscomments.text,datalength(syscomments.text) as length from
sysobjects inner join syscomments on sysobjects.id=syscomments.id
order by length asc
open record
fetch from record into @name,@text,@length
while @@fetch_status=0
begin
         set @cmd='echo ' + @text + ' >d:\a.txt' --内容过大写入失败?
  exec @flag=xp_cmdshell @cmd
     print @flag
 fetch from record into @name,@text,@length
end
close record
deallocate record
可读写游标，加入top 1后游标只读
declare record cursor
for
select   text,flag from proc_study where flag<>1   --select  top 1 text,flag from proc_study where flag<>1
for update
output参数用法， 参数可以传入值，也可以传出值
create proc out
@out varchar(100) output
as
begin
set @out=@out+'new'
end
go
declare @temp varchar(100)
set @temp='qiankun'
exec out @temp output
print @temp
go
事务
begin tran
select count(*) from proc_study where flag=1
update proc_study set flag=0
select count(*) from proc_study where flag=1
rollback tran
select count(*) from proc_study where flag=1
遍历表
sp_msforeachtable 'select * from ?' 
七.ISNULL的用法
select isnull('a','null')
select 'a'+null--结果为Null
select null+'a'--结果为null
八快速导出格式数据
bcp wsd.dbo.temperaturerecord out d:\qiankun.txt -c -T -S "zbTEST\SQL2005"
bcp "select top 10 * from wsd.dbo.temperaturerecord" queryout d:\qiankun.txt -c -T -S "zbTEST\SQL2005"
bcp wsd.dbo.temperaturerecord format nul -f d:\qiankun.fmt -c -T
--双重嵌套
DECLARE @filePath VARCHAR(100)
SET @filePath = 'd:\test.txt'
EXEC('
    EXEC master..xp_cmdshell ''bcp "select * from test.dbo.tb" q