-- 返回一個(gè)表中所有的字段
select name from syscolumns where id=object_id('tb_usertable_online')
-- 獲取最近添加的標(biāo)識(shí)列的值
set @rs = @@identity
--
print len('abcdef')
-- 大小寫轉(zhuǎn)換
print lower('ABCDEF')
print upper('abcdef')
-- 去空格
print ltrim(' abcd dfd df ')
print rtrim(' abcd dfd df ')
-- 求絕對(duì)值
print abs(-12)
-- 冪
-- 3 的 2 次方
print power(3,2)
print power(3,3)
-- 隨機(jī)數(shù)
-- 0 - 1000 之間的隨機(jī)數(shù)
print convert(int,rand() * 10 )
-- 獲取圓周率
print pi()
-- 獲取系統(tǒng)時(shí)間
print getdate()
-- 獲取3天前的時(shí)間
print dateadd(day, -3 , getdate())
-- 獲取3天后的時(shí)間
print dateadd(day, 3 , getdate())
-- 獲取3年前的時(shí)間
print dateadd(year, -3 , getdate())
-- 獲取3年后的時(shí)間
print dateadd(year, 3 , getdate())
-- 獲取3月后的時(shí)間
print dateadd(month, 3 , getdate())
-- 獲取9小時(shí)后的時(shí)間
print dateadd(hour, 9 , getdate())
-- 獲取9分鐘后的時(shí)間
print dateadd(minute, 9 , getdate())
-- 獲取指定時(shí)間之間相隔多少年
print datediff(year, '2005-01-01', '2008-01-01')
-- 獲取指定時(shí)間之間相隔多少月
print datediff(month, '2005-01-01', '2008-01-01')
-- 獲取指定時(shí)間之間相隔多少天
print datediff(day, '2005-01-01', '2008-01-01')
-- 字符串合并
print 'abc' + 'def'
print 'abcder'
print 'abc' + '456'
print 'abc' + 456
-- 類型轉(zhuǎn)換
print 'abc' + convert(varchar(10), 456)
select title_id, type, price from titles
-- 字符串連接必須保證類型一致(以下語句執(zhí)行將會(huì)出錯(cuò))
-- 類型轉(zhuǎn)換
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'
-- 是否可以定義一個(gè)函數(shù)
-- 將作者編號(hào)作為參數(shù)統(tǒng)計(jì)其作品數(shù)量并將其返回
select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount
from authors
order by TitleCount
-- 根據(jù)給定的作者編號(hào)獲取其相應(yīng)的作品數(shù)量
create function GetTitleCountByAuID(@au_id varchar(12))
returns int
begin
return (select count(title_id)
from titleauthor
where au_id = @au_id)
end
-- 查看表結(jié)構(gòu)
sp_help titles
-- 查看存儲(chǔ)過程的定義內(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
-- 關(guān)閉
close cur_titles
-- 釋放
deallocate cur_titles
假設(shè)有張學(xué)生成績表(CJ)如下
Name Subject Result
張三 語文 80
張三 數(shù)學(xué) 90
張三 物理 85
李四 語文 85
李四 數(shù)學(xué) 92
李四 物理 82
想變成
姓名 語文 數(shù)學(xué) 物理
張三 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. 行列轉(zhuǎn)換--合并
有表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)建一個(gè)合并的函數(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ù)得到結(jié)果
select distinct id,dbo.fmerg(id) from 表A
查詢某一個(gè)表的字段和數(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.殺掉所有的事件探察器進(jìn)程:
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：獲取某一個(gè)表的所有字段
select name from syscolumns where id=object_id('表名')
11：查看與某一個(gè)表相關(guān)的視圖、存儲(chǔ)過程、函數(shù)
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
9：獲取當(dāng)前數(shù)據(jù)庫中的所有用戶表
select Name from sysobjects where xtype='u' and status>=0
12：查看當(dāng)前數(shù)據(jù)庫中所有存儲(chǔ)過程
select name as 存儲(chǔ)過程名稱 from sysobjects where xtype='P'
14：查詢某一個(gè)表的字段和數(shù)據(jù)類型
select column_name,data_type from information_schema.columns
where table_name = '表名'
SQL字符函數(shù)：
返回字符串中從左邊開始指定個(gè)數(shù)的字符。LEFT ( character_expression , integer_expression )
Select LEFT(Name, 5) FROM Production.Product orDER BY ProductID;
RIGHT：返回字符表達(dá)式中從起始位置（從右端開始）到指定字符位置（從右端開始計(jì)數(shù)）的部分。
RIGHT(character_expression,integer_expression)
RIGHT("Mountain Bike", 4) 返回結(jié)果Bike
LEN：返回給定字符串表達(dá)式的字符數(shù)（不包括尾隨空格），而不是返回字節(jié)數(shù)。
LEN ( string_expression )
Select CompanyName, LEN(CompanyName)
FROM Customers
REPLICATE：按指定次數(shù)重復(fù)字符表達(dá)式。
REPLICATE ( character_expression, integer_expression)
下面的示例將 Employee 表中每一名雇員的姓氏復(fù)制兩次：
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：刪除指定長度的字符并在指定的起始點(diǎn)插入另一組字符。--替換
STUFF ( character_expression, start, length, character_expression )
Select STUFF(ProductID, 2,1, '000')
FROM Products
PATINDEX：對(duì)于所有有效的文本和字符數(shù)據(jù)類型，返回指定表達(dá)式中模式第一次出現(xiàn)的起始位置，如果未找到模式，則返回零。
PATINDEX ( '%pattern%', expression )
下面的示例搜索其名稱包含單詞“Anton”的產(chǎn)品的列表。
Select ProductName, PATINDEX('%Anton%', ProductName)
FROM Products
CHARINDEX：返回字符串中指定表達(dá)式的起始位置。
CHARINDEX ( expression1 , expression2 [ , start_location ] )
下面的示例從數(shù)據(jù)庫中的員工姓氏中搜索表達(dá)式“an”：
Select [Last Name], CHARINDEX('an', [Last Name]) AS Position
FROM Employees