[转]一些t-sql技巧
上一篇 / 下一篇 2008-08-06 13:23:22 / 个人分类:SQL数据库
摘自http://www.51testing.com/html/200807/n88553.html
一、 只复制一个表结构,不复制数据
select top 0 * into [t1] from [t2] |
1、 先用下面的脚本创建一个函数
if exists(select 1 from sysobjects where id=object_id('fgetscrīpt') and objectproperty(id,'IsInlineFunction')=0) drop function fgetscrīpt go create function fgetscrīpt( ) returns varchar(8000) --创建sqldmo对象 --连接服务器 exec @err=sp_oamethod @srvid,'connect',null,@servername if @err <>0 goto lberr --获取数据库集 --获取要取得脚本的数据库id --获取要取得脚本的对象id --取得脚本 --print @re lberr: |
2、 用法如下
print dbo.fgetscrīpt('服务器名','用户名','密码','数据库名','表名或其它对象名') |
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.fgetscrīpt('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 |
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 |
查询时:
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 |
六、 时间格式转换问题
因为新开发的软件需要用一些旧软件生成的一些数据,在时间格式上不统一,只能手工转换,研究了一下午写了三条语句,以前没怎么用过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 pubs.dbo.t_20( create table northwind.dbo.t_30( --分区视图 --插入数据 --更新数据 --删除测试 --显示结果 --删除测试 /**//*--测试结果 id name (所影响的行数为 3 行) |
八、 树型的实现
--参考 --树形数据查询示例 --示例数据 --查询指定id的所有子 --调用(查询所有的子) --删除测试 |
九、 排序问题
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)ASBEGINDECLARE @LvshiNames varchar(2000), @name varchar(50)select @LvshiNames=''DECLARE lvshi_cursor CURSOR FOR |
相关阅读:
- 精妙SQL语句收集『转载』 (51mobile, 2007-8-22)
TAG: SQL数据库
标题搜索
日历
|
|||||||||
日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
1 | 2 | 3 | 4 | 5 | 6 | ||||
7 | 8 | 9 | 10 | 11 | 12 | 13 | |||
14 | 15 | 16 | 17 | 18 | 19 | 20 | |||
21 | 22 | 23 | 24 | 25 | 26 | 27 | |||
28 | 29 | 30 |
我的存档
数据统计
- 访问量: 21619
- 日志数: 35
- 书签数: 1
- 建立时间: 2006-12-06
- 更新时间: 2010-09-15