-- 返回一个表中所有的字段
select name from syscolumns where id=object_id('tb_usertable_online')
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
-- 获取最近添加的标识列的值
set @rs = @@identity
![](/Images/OutliningIndicators/None.gif)
--
print len('abcdef')
-- 大小写转换
print lower('ABCDEF')
print upper('abcdef')
-- 去空格
print ltrim(' abcd dfd df ')
print rtrim(' abcd dfd df ')
-- 求绝对值
print abs(-12)
![](/Images/OutliningIndicators/None.gif)
-- 幂
-- 3 的 2 次方
print power(3,2)
print power(3,3)
-- 随机数
-- 0 - 1000 之间的随机数
print convert(int,rand() * 10 )
-- 获取圆周率
print pi()
![](/Images/OutliningIndicators/None.gif)
-- 获取系统时间
print getdate()
![](/Images/OutliningIndicators/None.gif)
-- 获取3天前的时间
print dateadd(day, -3 , getdate())
-- 获取3天后的时间
print dateadd(day, 3 , getdate())
-- 获取3年前的时间
print dateadd(year, -3 , getdate())
-- 获取3年后的时间
print dateadd(year, 3 , getdate())
![](/Images/OutliningIndicators/None.gif)
-- 获取3月后的时间
print dateadd(month, 3 , getdate())
-- 获取9小时后的时间
print dateadd(hour, 9 , getdate())
-- 获取9分钟后的时间
print dateadd(minute, 9 , getdate())
![](/Images/OutliningIndicators/None.gif)
-- 获取指定时间之间相隔多少年
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')
![](/Images/OutliningIndicators/None.gif)
-- 字符串合并
print 'abc' + 'def'
![](/Images/OutliningIndicators/None.gif)
print 'abcder'
![](/Images/OutliningIndicators/None.gif)
print 'abc' + '456'
print 'abc' + 456
![](/Images/OutliningIndicators/None.gif)
-- 类型转换
print 'abc' + convert(varchar(10), 456)
![](/Images/OutliningIndicators/None.gif)
select title_id, type, price from titles
-- 字符串连接必须保证类型一致(以下语句执行将会出错)
-- 类型转换
select title_id + type + price from titles
-- 正确
select title_id + type + convert(varchar(10), price) from titles
![](/Images/OutliningIndicators/None.gif)
print '123' + convert(varchar(3), 123)
print '123' + '123'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
-- 是否可以定义一个函数
-- 将作者编号作为参数统计其作品数量并将其返回
select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount
from authors
order by TitleCount
![](/Images/OutliningIndicators/None.gif)
-- 根据给定的作者编号获取其相应的作品数量
create function GetTitleCountByAuID(@au_id varchar(12))
returns int
begin
return (select count(title_id)
from titleauthor
where au_id = @au_id)
end
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
-- 查看表结构
sp_help titles
-- 查看存储过程的定义内容
sp_helptext GetRankByTitleId
sp_helptext sp_helptext
sp_helptext xp_cmdshell
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
-- 声明
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
![](/Images/OutliningIndicators/None.gif)
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
![](/Images/OutliningIndicators/None.gif)
想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82
![](/Images/OutliningIndicators/None.gif)
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)
![](/Images/OutliningIndicators/None.gif)
2. 行列转换--合并
![](/Images/OutliningIndicators/None.gif)
有表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
![](/Images/OutliningIndicators/None.gif)
创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
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
go
![](/Images/OutliningIndicators/None.gif)
--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
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
![](/Images/OutliningIndicators/None.gif)
4.查看硬盘分区:
EXEC master..xp_fixeddrives
![](/Images/OutliningIndicators/None.gif)
5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
![](/Images/OutliningIndicators/None.gif)
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 '?'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
10:获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
![](/Images/OutliningIndicators/None.gif)
11:查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
![](/Images/OutliningIndicators/None.gif)
9:获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
![](/Images/OutliningIndicators/None.gif)
12:查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
![](/Images/OutliningIndicators/None.gif)
14:查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'