-- 返回一個表中所有的字段
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)
-- 隨機數(shù)
-- 0 - 1000 之間的隨機數(shù)
print convert(int,rand() * 10 )
-- 獲取圓周率
print pi()
-- 獲取系統(tǒng)時間
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
-- 字符串連接必須保證類型一致(以下語句執(zhí)行將會出錯)
-- 類型轉換
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'
-- 是否可以定義一個函數(shù)
-- 將作者編號作為參數(shù)統(tǒng)計其作品數(shù)量并將其返回
select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount
from authors
order by TitleCount
-- 根據(jù)給定的作者編號獲取其相應的作品數(shù)量
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
-- 查看存儲過程的定義內(nèi)容
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
張三 數(shù)學 90
張三 物理 85
李四 語文 85
李四 數(shù)學 92
李四 物理 82
想變成
姓名 語文 數(shù)學 物理
張三 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
創(chuàng)建一個合并的函數(shù)
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
--調(diào)用自定義函數(shù)得到結果
select distinct id,dbo.fmerg(id) from 表A
查詢某一個表的字段和數(shù)據(jù)類型
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.查看硬盤分區(qū):
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：查看與某一個表相關的視圖、存儲過程、函數(shù)
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
9：獲取當前數(shù)據(jù)庫中的所有用戶表
select Name from sysobjects where xtype='u' and status>=0
12：查看當前數(shù)據(jù)庫中所有存儲過程
select name as 存儲過程名稱 from sysobjects where xtype='P'
14：查詢某一個表的字段和數(shù)據(jù)類型
select column_name,data_type from information_schema.columns
where table_name = '表名'
SQL字符函數(shù)：
返回字符串中從左邊開始指定個數(shù)的字符。LEFT ( character_expression , integer_expression )
Select LEFT(Name, 5) FROM Production.Product orDER BY ProductID;
RIGHT：返回字符表達式中從起始位置（從右端開始）到指定字符位置（從右端開始計數(shù)）的部分。
RIGHT(character_expression,integer_expression)
RIGHT("Mountain Bike", 4) 返回結果Bike
LEN：返回給定字符串表達式的字符數(shù)（不包括尾隨空格），而不是返回字節(jié)數(shù)。
LEN ( string_expression )
Select CompanyName, LEN(CompanyName)
FROM Customers
REPLICATE：按指定次數(shù)重復字符表達式。
REPLICATE ( character_expression, integer_expression)
下面的示例將 Employee 表中每一名雇員的姓氏復制兩次：
Select REPLICATE (LastName, 2) AS "LastName Twice"
FROM Employees
SUBSTRING：返回 $sourceString 的子串，從 $startingLoc 指定的位置開始，長度為 $length 指定的字符數(shù)。 SUBSTRING ( expression, start, length )
下面的示例返回 Employees 表中每位雇員的名字首字母及完整姓氏：
Select SUBSTRING(First Name,1,1) AS Initial, Last Name
FROM Employees
STUFF：刪除指定長度的字符并在指定的起始點插入另一組字符。--替換
STUFF ( character_expression, start, length, character_expression )
Select STUFF(ProductID, 2,1, '000')
FROM Products
PATINDEX：對于所有有效的文本和字符數(shù)據(jù)類型，返回指定表達式中模式第一次出現(xiàn)的起始位置，如果未找到模式，則返回零。
PATINDEX ( '%pattern%', expression )
下面的示例搜索其名稱包含單詞“Anton”的產(chǎn)品的列表。
Select ProductName, PATINDEX('%Anton%', ProductName)
FROM Products
CHARINDEX：返回字符串中指定表達式的起始位置。
CHARINDEX ( expression1 , expression2 [ , start_location ] )
下面的示例從數(shù)據(jù)庫中的員工姓氏中搜索表達式“an”：
Select [Last Name], CHARINDEX('an', [Last Name]) AS Position
FROM Employees