空间管理您的位置: 51Testing软件测试网 » kopac的个人空间 » 日志

SQL 的一些实例技巧

上一篇 / 下一篇 2008-07-28 17:43:56 / 个人分类：SQL

查看( 523 ) / 评论( 0 ) / 评分( 0 / 0 )

-- 返回一个表中所有的字段

select name from syscolumns where id=object_id('tb_usertable_online')

-- 获取最近添加的标识列的值

set @rs = @@identity

print len('abcdef')

-- 大小写转换

print lower('ABCDEF')

print upper('abcdef')

-- 去空格

print ltrim(' abcd dfd df ')

print rtrim(' abcd dfd df ')

-- 求绝对值

print abs(-12)

-- 幂

-- 3 的 2 次方

print power(3,2)

print power(3,3)

-- 随机数

-- 0 - 1000 之间的随机数

print convert(int,rand() * 10 )

-- 获取圆周率

print pi()

-- 获取系统时间

print getdate()

-- 获取3天前的时间

print dateadd(day, -3 , getdate())

-- 获取3天后的时间

print dateadd(day, 3 , getdate())

-- 获取3年前的时间

print dateadd(year, -3 , getdate())

-- 获取3年后的时间

print dateadd(year, 3 , getdate())

-- 获取3月后的时间

print dateadd(month, 3 , getdate())

-- 获取9小时后的时间

print dateadd(hour, 9 , getdate())

-- 获取9分钟后的时间

print dateadd(minute, 9 , getdate())

-- 获取指定时间之间相隔多少年

print datediff(year, '2005-01-01', '2008-01-01')

-- 获取指定时间之间相隔多少月

print datediff(month, '2005-01-01', '2008-01-01')

-- 获取指定时间之间相隔多少天

print datediff(day, '2005-01-01', '2008-01-01')

-- 字符串合并

print 'abc' + 'def'

print 'abcder'

print 'abc' + '456'

print 'abc' + 456

-- 类型转换

print 'abc' + convert(varchar(10), 456)

select title_id, type, price from titles

-- 字符串连接必须保证类型一致(以下语句执行将会出错)

-- 类型转换

select title_id + type + price from titles

-- 正确

select title_id + type + convert(varchar(10), price) from titles

print '123' + convert(varchar(3), 123)

print '123' + '123'

-- 是否可以定义一个函数

-- 将作者编号作为参数统计其作品数量并将其返回

select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount

from authors

order by TitleCount

-- 根据给定的作者编号获取其相应的作品数量

create function GetTitleCountByAuID(@au_id varchar(12))

returns int

begin

return (select count(title_id)

from titleauthor

where au_id = @au_id)

end

-- 查看表结构

sp_help titles

-- 查看存储过程的定义内容

sp_helptext GetRankByTitleId

sp_helptext sp_helptext

sp_helptext xp_cmdshell

-- 声明

declare cur_titles cursor

for select title, price from titles

-- 打开

open cur_titles

declare @title varchar(80)

declare @price numeric(9,4)

declare @title_temp varchar(80)

declare @price_temp numeric(9,4)

-- 提取

fetch cur_titles into @title, @price

fetch cur_titles into @title_temp, @price_temp

while @@fetch_status = 0

begin

if @price < @price_temp

begin

set @price = @price_temp

set @title = @title_temp

end

fetch cur_titles into @title_temp, @price_temp

end

-- 关闭

close cur_titles

-- 释放

deallocate cur_titles

假设有张学生成绩表(CJ)如下

Name Subject Result

张三语文 80

张三数学 90

张三物理 85

李四语文 85

李四数学 92

李四物理 82

想变成

姓名语文数学物理

张三 80 90 85

李四 85 92 82

declare @sql varchar(4000)

set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'

from (select distinct Subject from CJ) as a

select @sql = @sql+' from test group by name'

exec(@sql)

2. 行列转换--合并

有表A,

id pid

1 1

1 2

1 3

2 1

2 2

3 1

如何化成表B:

id pid

1 1,2,3

2 1,2

3 1

创建一个合并的函数

create function fmerg(@id int)

returns varchar(8000)

begin

declare @str varchar(8000)

set @str=''

select @str=@str+','+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1)

return(@str)

End

--调用自定义函数得到结果

select distinct id,dbo.fmerg(id) from 表A

查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns

where table_name = '表名'

3.取回表中字段:

declare @list varchar(1000),@sql nvarchar(1000)

set @list = ''

--set @sql = ''

select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='tb_user_msg'

set @sql='select '+right(@list,len(@list)-1)+' from tb_user_msg'

-- print @sql

exec sp_ExecuteSql @sql

4.查看硬盘分区:

EXEC master..xp_fixeddrives

5.比较A,B表是否相等:

if (select checksum_agg(binary_checksum(*)) from A)

(select checksum_agg(binary_checksum(*)) from B)

print '相等'

else

print '不相等'

6.杀掉所有的事件探察器进程:

DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses

WHERE program_name IN('SQL profiler',N'SQL 事件探查器')

EXEC sp_msforeach_worker '?'

10：获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

11：查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

9：获取当前数据库中的所有用户表

select Name from sysobjects where xtype='u' and status>=0

12：查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

14：查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns

SQL 的一些实例技巧

相关阅读:

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅