一、 只复制一个表结构,不复制数据
select top 0 * into [t1] from [t2]
二、 获取数据库中某个对象的创建脚本
1、 先用下面的脚本创建一个函数
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
drop function fgetscript
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create function fgetscript(
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
@servername varchar(50) --服务器名
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
,@userid varchar(50)='sa' --用户名,如果为nt验证方式,则为空
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
,@password varchar(50)='' --密码
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
,@databasename varchar(50) --数据库名称
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
,@objectname varchar(250) --对象名
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
) returns varchar(8000)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @re varchar(8000) --返回脚本
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @srvid int,@dbsid int --定义服务器、数据库集id
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @dbid int,@tbid int --数据库、表id
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--创建sqldmo对象
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if @err<>0 goto lberr
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--连接服务器
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if isnull(@userid,'')='' --如果是 Nt验证方式
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec @err=sp_oasetproperty @srvid,'loginsecure',1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if @err<>0 goto lberr
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec @err=sp_oamethod @srvid,'connect',null,@servername
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
else
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if @err<>0 goto lberr
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--获取数据库集
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if @err<>0 goto lberr
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--获取要取得脚本的数据库id
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if @err<>0 goto lberr
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--获取要取得脚本的对象id
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if @err<>0 goto lberr
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--取得脚本
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec @err=sp_oamethod @tbid,'script',@re output
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if @err<>0 goto lberr
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--print @re
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
return(@re)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
lberr:
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec sp_oageterrorinfo NULL, @src out, @desc out
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @errb varbinary(4)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
set @errb=cast(@err as varbinary(4))
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec master..xp_varbintohexstr @errb,@re out
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
set @re='错误号: '+@re
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
+char(13)+'错误源: '+@src
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
+char(13)+'错误描述: '+@desc
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
return(@re)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
2、 用法如下
用法如下,
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
print dbo.fgetscript('服务器名','用户名','密码','数据库名','表名或其它对象名')
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
3、 如果要获取库里所有对象的脚本,如如下方式
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @name varchar(250)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare #aa cursor for
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select name from sysobjects where xtype not in('S','PK','D','X','L')
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
open #aa
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
fetch next from #aa into @name
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
while @@fetch_status=0
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
fetch next from #aa into @name
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
close #aa
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
deallocate #aa
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
4、 声明,此函数是csdn邹建邹老大提供的
三、 分隔字符串
如果有一个用逗号分割开的字符串,比如说"a,b,c,d,1,2,3,4",如何用t-sql获取这个字符串有几个元素,获取第几个元素的值是多少呢?因为t-sql里没有split函数,也没有数组的概念,所以只能自己写几个函数了。
1、 获取元素个数的函数
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create function getstrarrlength (@str varchar(8000))
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
returns int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @int_return int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @start int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @next int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @location int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @str =','+ @str +','
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @str=replace(@str,',,',',')
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @start =1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @next =1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @location = charindex(',',@str,@start)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
while (@location <>0)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @start = @location +1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @location = charindex(',',@str,@start)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @next =@next +1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @int_return = @next-2
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
return @int_return
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
2、 获取指定索引的值的函数
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create function getstrofindex (@str varchar(8000),@index int =0)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
returns varchar(8000)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @str_return varchar(8000)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @start int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @next int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @location int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @start =1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @next =1 --如果习惯从0开始则select @next =0
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @location = charindex(',',@str,@start)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
while (@location <>0 and @index > @next )
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @start = @location +1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @location = charindex(',',@str,@start)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @next =@next +1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出,则认为逗号在字符串后
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗号之后的位置或者就是初始值1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
if (@index <> @next ) select @str_return = '' --如果二者不相等,则是因为逗号太少,或者@index小于@next的初始值1。
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
return @str_return
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
3、 测试
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
四、 一条语句执行跨越若干个数据库
我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?
第一种方法:
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
第二种方法:
先使用联结服务器:
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
exec sp_addlinkedsrvlogin @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
然后你就可以如下:
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from 别名.库名.dbo.表名
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
五、 怎样获取一个表中所有的字段信息
蛙蛙推荐:怎样获取一个表中所有字段的信息
先创建一个视图
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
Create view fielddesc
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select o.name as table_name,c.name as field_name,t.name as type,c.length as
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
from syscolumns c
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
join systypes t on c.xtype = t.xusertype
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
join sysobjects o on o.id=c.id
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
left join sysproperties p on p.smallid=c.colid and p.id=o.id
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
where o.xtype='U'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
查询时:
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
Select * from fielddesc where table_name = '你的表名'
还有个更强的语句,是邹建写的,也写出来吧
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(case when a.colorder=1 then d.name else '' end) N'表名',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
a.colorder N'字段序号',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
a.name N'字段名',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(case when (SELECT count(*)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM sysobjects
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE (name in
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(SELECT name
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM sysindexes
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE (id = a.id) AND (indid in
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(SELECT indid
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM sysindexkeys
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE (id = a.id) AND (colid in
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(SELECT colid
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM syscolumns
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE (id = a.id) AND (name = a.name))))))) AND
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
b.name N'类型',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
a.length N'占用字节数',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(case when a.isnullable=1 then '√'else '' end) N'允许空',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
isnull(e.text,'') N'默认值',
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
isnull(g.[value],'') AS N'字段说明'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--into ##tx
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM syscolumns a left join systypes b
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
on a.xtype=b.xusertype
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
inner join sysobjects d
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
left join syscomments e
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
on a.cdefault=e.id
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
left join sysproperties g
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
on a.id=g.id AND a.colid = g.smallid
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
order by object_name(a.id),a.colorder
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
六、 时间格式转换问题
因为新开发的软件需要用一些旧软件生成的一些数据,在时间格式上不统一,只能手工转换,研究了一下午写了三条语句,以前没怎么用过convert函数和case语句,还有"+"操作符在不同上下文环境也会起到不同的作用,把我搞晕了要,不过现在看来是差不多弄好了。
1、把所有"70.07.06"这样的值变成"1970-07-06"
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
UPDATE lvshi
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SET shengri = '19' + REPLACE(shengri, '.', '-')
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE (zhiyezheng = '139770070153')
2、在"1970-07-06"里提取"70","07","06"
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month,
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SUBSTRING(shengri, 9, 2) AS day
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM lvshi
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE (zhiyezheng = '139770070153')
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
3、把一个时间类型字段转换成"1970-07-06"
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
UPDATE lvshi
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SET shenling = CONVERT(varchar(4), YEAR(shenling))
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
+ '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2),
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
month(shenling)) ELSE CONVERT(varchar(2), month(shenling))
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
END + '-' + CASE WHEN LEN(day(shenling)) = 1 THEN '0' + CONVERT(char(2),
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE (zhiyezheng = '139770070153')
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
七、 分区视图
分区视图是提高查询性能的一个很好的办法
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--看下面的示例
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--示例表
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create table tempdb.dbo.t_10(
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
id int primary key check(id between 1 and 10),name varchar(10))
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create table pubs.dbo.t_20(
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
id int primary key check(id between 11 and 20),name varchar(10))
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create table northwind.dbo.t_30(
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
id int primary key check(id between 21 and 30),name varchar(10))
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--分区视图
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create view v_t
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from tempdb.dbo.t_10
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from pubs.dbo.t_20
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from northwind.dbo.t_30
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--插入数据
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
insert v_t select 1 ,'aa'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 2 ,'bb'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 11,'cc'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 12,'dd'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 21,'ee'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 22,'ff'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--更新数据
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
update v_t set name=name+'_更新' where right(id,1)=1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--删除测试
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
delete from v_t where right(id,1)=2
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--显示结果
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from v_t
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--删除测试
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
drop view v_t
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
/**//*--测试结果
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
id name
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
----------- ----------
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
1 aa_更新
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11 cc_更新
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21 ee_更新
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
(所影响的行数为 3 行)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
==*/
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
八、 树型的实现
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--参考
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--树形数据查询示例
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--作者: 邹建
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--示例数据
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
insert [tb] select 0,'中国'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 0,'美国'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 0,'加拿大'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 1,'北京'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 1,'上海'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 1,'江苏'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 6,'苏州'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 7,'常熟'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 6,'南京'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 6,'无锡'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 2,'纽约'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
union all select 2,'旧金山'
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--查询指定id的所有子
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
create function f_cid(
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
@id int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
)returns @re table([id] int,[level] int)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
declare @l int
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
set @l=0
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
insert @re select @id,@l
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
while @@rowcount>0
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
set @l=@l+1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
insert @re select a.[id],@l
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
from [tb] a,@re b
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
where a.[pid]=b.[id] and b.[level]=@l-1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
/**//**//**//*--如果只显示最明细的子(下面没有子),则加上这个删除
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
delete a from @re a
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
where exists(
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
select 1 from [tb] where [pid]=a.[id])
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
--*/
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
return
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--调用(查询所有的子)
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--删除测试
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
drop table [tb]
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
drop function f_cid
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
go
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
九、 排序问题
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE TABLE [t] (
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
[id] [int] IDENTITY (1, 1) NOT NULL ,
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
[GUID] [uniqueidentifier] NULL
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
) ON [PRIMARY]
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
下面这句执行5次
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
insert t values (newid())
查看执行结果
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from t
1、 第一种
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from t
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
order by case id when 4 then 1
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
when 5 then 2
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
when 1 then 3
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
when 2 then 4
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
when 3 then 5 end
2、 第二种
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from t order by (id+2)%6
3、 第三种
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from t order by charindex(cast(id as varchar),'45123')
4、 第四种
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from t
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE id between 0 and 5
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
order by charindex(cast(id as varchar),'45123')
5、 第五种
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from t order by case when id >3 then id-5 else id end
6、 第六种
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
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构建成的值,然后用下面的一句就全部删
除了,比循环用多条语句高效吧应该。
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0
还有一种就是
![](https://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
delete from table1 where id in(1,2,3,4
![](https://ghd258.cnblogs.com/Images/dot.gif)
)