LUKIYA'S NEVERLAND

春去秋来，花谢花开。

[收集]一些Sql技巧

05/12/2006 03:52:00 PM Views: 139 Pictures: 0

一、?只复制一个表结构，不复制数据

?

select ? top ? 0 ? * ? into ? [ t1 ] ? from ? [ t2 ]


二、?获取数据库中某个对象的创建脚本

1、?先用下面的脚本创建一个函数

if ? exists ( select ? 1 ? from ?sysobjects? where ?id = object_id ( ' fgetscript ' )? and ? objectproperty (id, ' IsInlineFunction ' ) = 0 )
? drop ? function ?fgetscript
 go

create ? function ?fgetscript(
? @servername ? varchar ( 50 )????? -- 服务器名
?, @userid ? varchar ( 50 ) = ' sa ' ???? -- 用户名,如果为nt验证方式,则为空
?, @password ? varchar ( 50 ) = '' ???? -- 密码
?, @databasename ? varchar ( 50 )???? -- 数据库名称
?, @objectname ? varchar ( 250 )???? -- 对象名

)? returns ? varchar ( 8000 )
 as
begin
? declare ? @re ? varchar ( 8000 )???????? -- 返回脚本
? declare ? @srvid ? int , @dbsid ? int ??????? -- 定义服务器、数据库集id
? declare ? @dbid ? int , @tbid ? int ???????? -- 数据库、表id
? declare ? @err ? int , @src ? varchar ( 255 ),? @desc ? varchar ( 255 )? -- 错误处理变量

-- 创建sqldmo对象
? exec ? @err = sp_oacreate? ' sqldmo.sqlserver ' , @srvid ?output
? if ? @err <> 0 ? goto ?lberr

 -- 连接服务器
? if ? isnull ( @userid , '' ) = '' ? -- 如果是?Nt验证方式
? begin
?? exec ? @err = sp_oasetproperty? @srvid , ' loginsecure ' , 1
?? if ? @err <> 0 ? goto ?lberr

?? exec ? @err = sp_oamethod? @srvid , ' connect ' , null , @servername
? end
? else
?? exec ? @err = sp_oamethod? @srvid , ' connect ' , null , @servername , @userid , @password

? if ? @err <> 0 ? goto ?lberr

 -- 获取数据库集
? exec ? @err = sp_oagetproperty? @srvid , ' databases ' , @dbsid ?output
? if ? @err <> 0 ? goto ?lberr

 -- 获取要取得脚本的数据库id
? exec ? @err = sp_oamethod? @dbsid , ' item ' , @dbid ?output, @databasename
? if ? @err <> 0 ? goto ?lberr

 -- 获取要取得脚本的对象id
? exec ? @err = sp_oamethod? @dbid , ' getobjectbyname ' , @tbid ?output, @objectname
? if ? @err <> 0 ? goto ?lberr

 -- 取得脚本
? exec ? @err = sp_oamethod? @tbid , ' script ' , @re ?output
? if ? @err <> 0 ? goto ?lberr

? -- print?@re
? return ( @re )

lberr:
? exec ?sp_oageterrorinfo? NULL ,? @src ?out,? @desc ?out?
? declare ? @errb ? varbinary ( 4 )
? set ? @errb = cast ( @err ? as ? varbinary ( 4 ))
? exec ?master..xp_varbintohexstr? @errb , @re ?out
? set ? @re = ' 错误号:? ' + @re
??? + char ( 13 ) + ' 错误源:? ' + @src
??? + char ( 13 ) + ' 错误描述:? ' + @desc
? return ( @re )
 end
go

2、?用法如下
用法如下,

print ?dbo.fgetscript( ' 服务器名 ' , ' 用户名 ' , ' 密码 ' , ' 数据库名 ' , ' 表名或其它对象名 ' )

3、?如果要获取库里所有对象的脚本，如如下方式

declare ? @name ? varchar ( 250 )
 declare ?#aa? cursor ? for
? select ?name? from ?sysobjects? where ?xtype? not ? in ( ' S ' , ' PK ' , ' D ' , ' X ' , ' L ' )
 open ?#aa
 fetch ? next ? from ?#aa? into ? @name
while ? @@fetch_status = 0
begin
? print ?dbo.fgetscript( ' onlytiancai ' , ' sa ' , ' sa ' , ' database ' , @name )
? fetch ? next ? from ?#aa? into ? @name
end
close ?#aa
 deallocate ?#aa

4、?声明，此函数是csdn邹建邹老大提供的
三、?分隔字符串
如果有一个用逗号分割开的字符串，比如说"a,b,c,d,1,2,3,4"，如何用t-sql获取这个字符串有几个元素，获取第几个元素的值是多少呢？因为t-sql里没有split函数，也没有数组的概念，所以只能自己写几个函数了。
1、?获取元素个数的函数

create ? function ?getstrarrlength?( @str ? varchar ( 8000 ))
 returns ? int
as
begin
?? declare ? @int_return ? int
?? declare ? @start ? int
?? declare ? @next ? int
?? declare ? @location ? int
?? select ? @str ? = ' , ' + ? @str ? + ' , '
?? select ? @str = replace ( @str , ' ,, ' , ' , ' )
?? select ? @start ? = 1
?? select ? @next ? = 1 ?
?? select ? @location ? = ? charindex ( ' , ' , @str , @start )
?? while ?( @location ? <> 0 )
?? begin
???? select ? @start ? = ? @location ? + 1
???? select ? @location ? = ? charindex ( ' , ' , @str , @start )
???? select ? @next ? = @next ? + 1
?? end
? select ? @int_return ? = ? @next - 2
? return ? @int_return
end

2、?获取指定索引的值的函数

create ? function ?getstrofindex?( @str ? varchar ( 8000 ), @index ? int ? = 0 )
 returns ? varchar ( 8000 )
 as
begin
?? declare ? @str_return ? varchar ( 8000 )
?? declare ? @start ? int
?? declare ? @next ? int
?? declare ? @location ? int
?? select ? @start ? = 1
?? select ? @next ? = 1 ? -- 如果习惯从0开始则select?@next?=0
?? select ? @location ? = ? charindex ( ' , ' , @str , @start )
?? while ?( @location ? <> 0 ? and ? @index ? > ? @next ?)
?? begin
???? select ? @start ? = ? @location ? + 1
???? select ? @location ? = ? charindex ( ' , ' , @str , @start )
???? select ? @next ? = @next ? + 1
?? end
?? if ? @location ? = 0 ? select ? @location ? = len ( @str ) + 1 ? -- 如果是因为没有逗号退出，则认为逗号在字符串后
?? select ? @str_return ? = ? substring ( @str , @start , @location ? - @start )? -- @start肯定是逗号之后的位置或者就是初始值1
?? if ?( @index ? <> ? @next ?)? select ? @str_return ? = ? '' ? -- 如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值1。
?? return ? @str_return
end

3、?测试

SELECT ? [ dbo ] . [ getstrarrlength ] ( ' 1,2,3,4,a,b,c,d ' )
 SELECT ? [ dbo ] . [ getstrofindex ] ( ' 1,2,3,4,a,b,c,d ' , 5 )

四、?一条语句执行跨越若干个数据库
我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?
第一种方法:

select ? * ? from ? OPENDATASOURCE ( ' SQLOLEDB ' , ' Data?Source=远程ip;User?ID=sa;Password=密码 ' ).库名.dbo.表名

第二种方法:
先使用联结服务器:

EXEC ?sp_addlinkedserver? ' 别名 ' , '' , ' MSDASQL ' , NULL , NULL , ' DRIVER={SQL?Server};SERVER=远程名;UID=用户;PWD=密码; '
exec ?sp_addlinkedsrvlogin?? @rmtsrvname = ' 别名 ' , @useself = ' false ' , @locallogin = ' sa ' , @rmtuser = ' sa ' , @rmtpassword = ' 密码 '
GO

然后你就可以如下：

select ? * ? from ?别名.库名.dbo.表名
 insert ?库名.dbo.表名? select ? * ? from ?别名.库名.dbo.表名
 select ? * ? into ?库名.dbo.新表名? from ?别名.库名.dbo.表名
 go

五、?怎样获取一个表中所有的字段信息
蛙蛙推荐：怎样获取一个表中所有字段的信息
先创建一个视图

Create ? view ?fielddesc????
 as
select ?o.name? as ?table_name,c.name? as ?field_name,t.name? as ?type,c.length? as ?

length,c.isnullable? as ?isnullable, convert ( varchar ( 30 ),p.value)? as ?desp?
 from ?syscolumns?c??
 join ?systypes?t? on ?c.xtype? = ?t.xusertype
 join ?sysobjects?o? on ?o.id = c.id?
 left ? join ????sysproperties?p? on ?p.smallid = c.colid? and ?p.id = o.id????
 where ?o.xtype = ' U '

查询时:

Select ? * ? from ?fielddesc? where ?table_name? = ? ' 你的表名 '

?

还有个更强的语句，是邹建写的，也写出来吧

SELECT ?
?( case ? when ?a.colorder = 1 ? then ?d.name? else ? '' ? end )?N ' 表名 ' ,
?a.colorder?N ' 字段序号 ' ,
?a.name?N ' 字段名 ' ,
?( case ? when ? COLUMNPROPERTY (?a.id,a.name, ' IsIdentity ' ) = 1 ? then ? ' √ ' else ? '' ? end )?N ' 标识 ' ,
?( case ? when ?( SELECT ? count ( * )
? FROM ?sysobjects
? WHERE ?(name? in
???????????( SELECT ?name
?????????? FROM ?sysindexes
?????????? WHERE ?(id? = ?a.id)? AND ?(indid? in
????????????????????( SELECT ?indid
??????????????????? FROM ?sysindexkeys
??????????????????? WHERE ?(id? = ?a.id)? AND ?(colid? in
?????????????????????????????( SELECT ?colid
???????????????????????????? FROM ?syscolumns
???????????????????????????? WHERE ?(id? = ?a.id)? AND ?(name? = ?a.name)))))))? AND
????????(xtype? = ? ' PK ' )) > 0 ? then ? ' √ ' ? else ? '' ? end )?N ' 主键 ' ,
?b.name?N ' 类型 ' ,
?a.length?N ' 占用字节数 ' ,
? COLUMNPROPERTY (a.id,a.name, ' PRECISION ' )? as ?N ' 长度 ' ,
? isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 )? as ?N ' 小数位数 ' ,
?( case ? when ?a.isnullable = 1 ? then ? ' √ ' else ? '' ? end )?N ' 允许空 ' ,
? isnull (e. text , '' )?N ' 默认值 ' ,
? isnull (g. [ value ] , '' )? AS ?N ' 字段说明 '
-- into?##tx

FROM ??syscolumns??a? left ? join ?systypes?b?
 on ??a.xtype = b.xusertype
 inner ? join ?sysobjects?d?
 on ?a.id = d.id?? and ??d.xtype = ' U ' ? and ??d.name <> ' dtproperties '
left ? join ?syscomments?e
 on ?a.cdefault = e.id
 left ? join ?sysproperties?g
 on ?a.id = g.id? AND ?a.colid? = ?g.smallid??
 order ? by ? object_name (a.id),a.colorder

六、?时间格式转换问题
因为新开发的软件需要用一些旧软件生成的一些数据，在时间格式上不统一，只能手工转换，研究了一下午写了三条语句，以前没怎么用过convert函数和case语句，还有"+"操作符在不同上下文环境也会起到不同的作用，把我搞晕了要，不过现在看来是差不多弄好了。

1、把所有"70.07.06"这样的值变成"1970-07-06"

UPDATE ?lvshi
 SET ?shengri? = ? ' 19 ' ? + ? REPLACE (shengri,? ' . ' ,? ' - ' )
 WHERE ?(zhiyezheng? = ? ' 139770070153 ' )

?

2、在"1970-07-06"里提取"70","07","06"

SELECT ? SUBSTRING (shengri,? 3 ,? 2 )? AS ? year ,? SUBSTRING (shengri,? 6 ,? 2 )? AS ? month ,?
?????? SUBSTRING (shengri,? 9 ,? 2 )? AS ? day
FROM ?lvshi
 WHERE ?(zhiyezheng? = ? ' 139770070153 ' )

3、把一个时间类型字段转换成"1970-07-06"

UPDATE ?lvshi
 SET ?shenling? = ? CONVERT ( varchar ( 4 ),? YEAR (shenling))?
?????? + ? ' - ' ? + ? CASE ? WHEN ? LEN ( MONTH (shenling))? = ? 1 ? THEN ? ' 0 ' ? + ? CONVERT ( varchar ( 2 ),?
?????? month (shenling))? ELSE ? CONVERT ( varchar ( 2 ),? month (shenling))?
?????? END ? + ? ' - ' ? + ? CASE ? WHEN ? LEN ( day (shenling))? = ? 1 ? THEN ? ' 0 ' ? + ? CONVERT ( char ( 2 ),?
?????? day (shenling))? ELSE ? CONVERT ( varchar ( 2 ),? day (shenling))? END
WHERE ?(zhiyezheng? = ? ' 139770070153 ' )

七、?分区视图
分区视图是提高查询性能的一个很好的办法

-- 看下面的示例

-- 示例表
create ? table ?tempdb.dbo.t_10(
id? int ? primary ? key ? check (id? between ? 1 ? and ? 10 ),name? varchar ( 10 ))

 create ? table ?pubs.dbo.t_20(
id? int ? primary ? key ? check (id? between ? 11 ? and ? 20 ),name? varchar ( 10 ))

 create ? table ?northwind.dbo.t_30(
id? int ? primary ? key ? check (id? between ? 21 ? and ? 30 ),name? varchar ( 10 ))
 go

-- 分区视图
create ? view ?v_t
 as
select ? * ? from ?tempdb.dbo.t_10
 union ? all
select ? * ? from ?pubs.dbo.t_20
 union ? all
select ? * ? from ?northwind.dbo.t_30
 go

-- 插入数据
insert ?v_t? select ? 1 ?, ' aa '
union ?? all ? select ? 2 ?, ' bb '
union ?? all ? select ? 11 , ' cc '
union ?? all ? select ? 12 , ' dd '
union ?? all ? select ? 21 , ' ee '
union ?? all ? select ? 22 , ' ff '

-- 更新数据
update ?v_t? set ?name = name + ' _更新 ' ? where ? right (id, 1 ) = 1

-- 删除测试
delete ? from ?v_t? where ? right (id, 1 ) = 2

-- 显示结果
select ? * ? from ?v_t
 go

-- 删除测试
drop ? table ?northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
 drop ? view ?v_t

 /* --测试结果

id??????????name???????
-----------?----------?
1???????????aa_更新
11??????????cc_更新
21??????????ee_更新

（所影响的行数为?3?行）
== */


八、?树型的实现

-- 参考

-- 树形数据查询示例
-- 作者:?邹建

-- 示例数据
create ? table ? [ tb ] ( [ id ] ? int ? identity ( 1 , 1 ), [ pid ] ? int ,name? varchar ( 20 ))
 insert ? [ tb ] ? select ? 0 , ' 中国 '
union ?? all ?? select ? 0 , ' 美国 '
union ?? all ?? select ? 0 , ' 加拿大 '
union ?? all ?? select ? 1 , ' 北京 '
union ?? all ?? select ? 1 , ' 上海 '
union ?? all ?? select ? 1 , ' 江苏 '
union ?? all ?? select ? 6 , ' 苏州 '
union ?? all ?? select ? 7 , ' 常熟 '
union ?? all ?? select ? 6 , ' 南京 '
union ?? all ?? select ? 6 , ' 无锡 '
union ?? all ?? select ? 2 , ' 纽约 '
union ?? all ?? select ? 2 , ' 旧金山 '
go

-- 查询指定id的所有子
create ? function ?f_cid(
 @id ? int
) returns ? @re ? table ( [ id ] ? int , [ level ] ? int )
 as
begin
? declare ? @l ? int
? set ? @l = 0
? insert ? @re ? select ? @id , @l
? while ? @@rowcount > 0
? begin
?? set ? @l = @l + 1
?? insert ? @re ? select ?a. [ id ] , @l
?? from ? [ tb ] ?a, @re ?b
?? where ?a. [ pid ] = b. [ id ] ? and ?b. [ level ] = @l - 1
? end
/**/ /* --如果只显示最明细的子(下面没有子),则加上这个删除
?delete?a?from?@re?a
?where?exists(
??select?1?from?[tb]?where?[pid]=a.[id])
-- */
? return
end
go

-- 调用(查询所有的子)
select ?a. * ,层次 = b. [ level ] ? from ? [ tb ] ?a,f_cid( 2 )b? where ?a. [ id ] = b. [ id ]
go

-- 删除测试
drop ? table ? [ tb ]
drop ? function ?f_cid
 go

?

九、?排序问题

CREATE ? TABLE ? [ t ] ?(
? [ id ] ? [ int ] ? IDENTITY ?( 1 ,? 1 )? NOT ? NULL ?,
? [ GUID ] ? [ uniqueidentifier ] ? NULL ?
)? ON ? [ PRIMARY ]
GO


下面这句执行5次

insert ?t? values ?( newid ())


查看执行结果

select ? * ? from ?t


1、?第一种

select ? * ? from ?t
? order ? by ? case ?id? when ? 4 ? then ? 1
?????????????????? when ? 5 ? then ? 2
?????????????????? when ? 1 ? then ? 3
?????????????????? when ? 2 ? then ? 4
?????????????????? when ? 3 ? then ? 5 ? end


2、?第二种

select ? * ? from ?t? order ? by ?(id + 2 ) % 6


3、?第三种

select ? * ? from ?t? order ? by ? charindex ( cast (id? as ? varchar ), ' 45123 ' )


4、?第四种

select ? * ? from ?t
 WHERE ?id? between ? 0 ? and ? 5
order ? by ? charindex ( cast (id? as ? varchar ), ' 45123 ' )


5、?第五种

select ? * ? from ?t? order ? by ? case ? when ?id? > 3 ? then ?id - 5 ? else ?id? end


6、?第六种

select ? * ? from ?t? order ? by ?id? / ? 4 ? desc ,id? asc

?

十、?一条语句删除一批记录
首先id列是int标识类类型，然后删除ID值为5,6,8,9,10,11的列，这里的cast函数不能用convert函数代替，而且转换的类型必须是varchar，而不能是char，否则就会执行出你不希望的结果，这里的"5,6,8,9,10,11"可以是你在页面上获取的一个chkboxlist构建成的值，然后用下面的一句就全部删
除了，比循环用多条语句高效吧应该。

delete ? from ? [ fujian ] ? where ? charindex ( ' , ' + cast ( [ id ] ? as ? varchar ) + ' , ' , ' , ' + ' 5,6,8,9,10,11, ' + ' , ' ) > 0


还有一种就是

delete ? from ?table1? where ?id? in ( 1 , 2 , 3 , 4 ?)


十一、获取子表内的一列数据的组合字符串
下面这个函数获取05年已经注册了的某个所的律师，唯一一个参数就是事务所的名称，然后返回zhuce字段里包含05字样的所有律师。

CREATE ??? FUNCTION ?fn_Get05LvshiNameBySuo??( @p_suo ? Nvarchar ( 50 ))
 RETURNS ? Nvarchar ( 2000 )
 AS
BEGIN ??
? DECLARE ? @LvshiNames ? varchar ( 2000 ),? @name ? varchar ( 50 )
? select ? @LvshiNames = ''
? DECLARE ?lvshi_cursor? CURSOR ? FOR

数据库里有1,2,3,4,5 共5条记录,要用一条sql语句让其排序,使它排列成4,5,1,2,3,怎么写?