基本函數：
1.len()不取字符串最后的空格個數，但會取字符串中間的空格個數。
datalength()取所有的，包括空格個數
2.reverse()將()里面的字符反過來
3.replicate(a,n)將a重復n次
4.space(n)返回n個空格
5.stuff(a,n1,n2,b)從n1起往a字符串中插入b字符串，n2是要覆蓋的個數。如果為0，則只是插入，不覆蓋a中的字符。否之。
6.raiserror('',16,1) 拋出消息
7.substring(a,i,j) 取a字符串中從i起，共j個長度的字符。
charindex(a,m) 得到字符串a在字符串m中的起始位置
8.isnull()函數 isnull(a，b)
如果列a為空，則用值b來代替，但并不改變其在數據庫中的值。
刪除R1表中R2的數據
delete r1 where checksum(*) in (select checksum(*) from r2)
9.exists()函數
如果()中查詢語句返回行數為0，則為false，否之則為true.
往a表中添加一列a01
alter table a add a01 int
修改列
alter table pp alter column a varchar(50)
更改字段名
exec sp_rename '表名.字段名','新字段名','column'
ALTER TABLE 表名 DISABLE TRIGGER 觸發(fā)器名 --禁止觸發(fā)器
ALTER TABLE 表名 ENABLE TRIGGER 觸發(fā)器名 --允許觸發(fā)器
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' --禁用數據庫中所有表的所有觸發(fā)器
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' --啟用數據庫中所有表的所有觸發(fā)器
--清除所有表主鍵
EXEC sp_MSforeachtable 'declare @PkName varchar(100)
select @PkName=name from sysobjects where xtype=''Pk'' and parent_obj=object_id(''?'');
exec(''alter table ? drop constraint
''+@PkName)'go
--修改所有表的字段為某字段：
EXEC sp_MSforeachtable 'exec sp_rename ''?.收款日期'',''發(fā)生日期''' --將數據庫中所有表的收款日期改為發(fā)生日期
修改text類型字段的值：
create table os(SoftIntro text,ID int)
insert into os
select 'aaa',1004
union all
select 'bbb',1003
union all
select 'ccc',1002
declare @ptr binary(16)
select @ptr=textptr(SoftIntro) from os where id=1002
updatetext os.SoftIntro @ptr null 0 'dddd'
select * from os
替換text類型字段的值
create table t(Cbody ntext)
insert into t select '324234234深圳羅湖324324'
insert into t select '深圳羅湖324324'
declare @ptrval binary(16),@star int
declare cur cursor for select textptr(Cbody),charindex('深圳羅湖',cbody)-1 from t where cbody like '%深圳羅湖%'
open cur
fetch next from cur into @ptrval,@star
while(@@fetch_status=0)
begin
declare @sql nvarchar(1000)
set @sql='UPDATETEXT t.cbody @ptrval ' +ltrim(@star)+' '+ ltrim(len('深圳羅湖'))+' N''深圳福田'''
exec sp_executesql @sql,N'@ptrval binary(16)',@ptrval
fetch next from cur into @ptrval,@star
end
close cur
deallocate cur
--創(chuàng)建表
create table 表(a1 varchar(10),a2 char(2))
--為表添加描述信息
EXECUTE sp_addextendedproperty N'MS_Description', '人員信息表', N'user', N'dbo', N'table', N'表', NULL, NULL
--為字段a1添加描述信息
EXECUTE sp_addextendedproperty N'MS_Description', '姓名', N'user', N'dbo', N'table', N'表', N'column', N'a1'
--更新表中列a1的描述屬性：
EXEC sp_updateextendedproperty 'MS_Description','字段1','user',dbo,'table','表','column',a1
--刪除表中列a1的描述屬性：
EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','表','column',a1
--刪除測試
drop table 表
取5以下隨機的數字
select convert(varchar,ceiling(rand()*5))
將數字轉換成百分比：
select rtrim(cast(2 * 100/10 as decimal(5,2))) + '%'
cast(2 * 100/10 as decimal(5,2)) 這個是將2*100/10轉換成5位且小數位為2位的浮點小數，
11.乘積：
declare @s table(id float)
insert into @s select 2
insert into @s select 3
insert into @s select 2.5
select exp(sum(log(id))) from @s
12.like的用法
查出記錄中的字符串包含1，2，3，4的記錄
Create Table TEST
(share varchar(100))
Insert TEST Select '1,2,24'
Union All Select '2,23,56'
Union All Select '6,10,11'
Union All Select '3,4,15'
Union All Select '6,29,31'
GO
Select * From TEST Where ',' + share + ',' Like '%,[1-4],%'
13，按條件分類排序
select brand from bra
order by case when brand ='飛利浦' then '' else brand end
//將brand為飛利浦的排在最上面
14.給返回的記錄加上一個行數
(1)當沒有自增的id列時：
select bh = identity(int,1,1) ,proname,price into temp from product
select '第' + cast(bh as varchar) + '個' , proname,price from temp
(2)當有自增的id列時
select bh,name,price from
(
SELECT bh=(SELECT COUNT(1) FROM product WHERE id > a.id)+ 1,* FROM product a
) t
order by bh
---SQL2005啟用 openrowset/opendataset
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
-- 啟用xp_cmdshell
-- 允許配置高級選項
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 啟用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO
--啟動遠程服務器的MSDTC服務
exec master..xp_cmdshell 'isql /S"10.128.34.22" /U"sa" /P"123456" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output
--啟動本機的MSDTC服務
exec master..xp_cmdshell 'net start msdtc',no_output
---遠程鏈接：
1、創(chuàng)建遠程鏈接服務器，然后進行查詢
exec sp_addlinkedserver 'HJZX_SYN','','SQLOLEDB','10.128.34.22'
exec sp_addlinkedsrvlogin 'HJZX_SYN','false',null,'sa','123456'
go
select * from HJZX_SYN.數據庫名.dbo.表名
---刪除連接
exec sp_dropserver 'MyLink','droplogins'
---查詢
select * from sysservers
2、
select * from openrowset('msdasql','driver={sql server};server=10.124.20.10;uid=ncc2008;pwd=ncc2008',hjzx4.dbo.t_p_order) AS a
3、
select * from opendatasource('sqloledb','Data Source=10.124.20.10;User ID=ncc2008;Password=ncc2008').hjzx4.dbo.t_p_order
如：alter database 數據庫名 COLLATE Chinese_PRC_CI_AS 不區(qū)分大小寫，
而 alter database 數據庫名 COLLATE Chinese_PRC_CS_AS 使之區(qū)分大小寫。
導入excel數據：
select * into # from OPENROWSET('microsoft.jet.oledb.4.0','Excel 5.0;hdr=yes;database=d:\1月安排.xls',準考證信息$)
insert into 表名
select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=f:\Test.xls;User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
office2007
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="f:\aa.xls";User ID=admin;Password=;Extended properties=Excel 5.0')...[sheet1$]
----bcp:
-t列分割符，默認是以制表符(\t)分割
-r行分割符，默認是以換行符(\n)分割
導入：in
EXEC master..xp_cmdshell 'bcp pruduct in d:\wsp.txt -c -t. -r\n'
導出：
--導出全表數據out
EXEC master..xp_cmdshell 'bcp pruduct out d:\wsp.txt -c -Usa -Psa'
--導出查詢結果queryout
EXEC master..xp_cmdshell 'bcp "select * from pruduct where part_id like ''80%''" queryout d:\wsp.txt -c -t, -Usa -Psa'
--導入文本文檔：
BULK INSERT os
FROM 'c:\d.txt'
WITH (
FIELDTERMINATOR = ',', --列以逗號隔開
ROWTERMINATOR = '\n' --行以換行符隔開
)
---osql,執(zhí)行sql命令
exec master..xp_cmdshell 'osql -U sa -P sa -i d:\tt.txt'
--用SQL語句備份、還原數據庫
BACKUP DATABASE test --這里的test指的是數據庫名稱
TO disk = 'c:\backup.bak' --這里指名的數據庫路徑(backup.bak為備份文件名)
WITH FORMAT,
NAME = 'Full Backup of MyNwind' --這個是備注，無所謂。。隨便寫。
RESTORE DATABASE jz1 --所被恢復的數據庫名稱
FROM disk = 'c:\backup.bak --本地硬盤路徑(backup.bak為備份文件名)
GO
--SQL語句分離、附加：
--分離
sp_detach_db 'zetian'
--附加
EXEC sp_attach_db @dbname = N'zetian',
@filename1 = N'C:\Inetpub\wwwroot\zetian\數據庫\zetian.mdf',
@filename2 = N'C:\Inetpub\wwwroot\zetian\數據庫\zetian_log.ldf'
--分解字咐
declare @a table(A varchar(20), B varchar(20), C varchar(20), D varchar(20))
insert @a select 'a1' ,'b1', 'c1', 'd1/da'
union all select 'a2' ,'b2' ,'c2' ,'d22/da/da22'
union all select 'a3' ,'b3' ,'c3' ,'d3'
declare @t table( id int identity(1,1),e int)
insert @t select top 500 1 from syscolumns
select a,b,c,substring(d+'/',id,charindex('/',d+'/',id+1)-id) X
from @a a,@t b
where substring('/'+d,id,1)='/'
--設置約束
CREATE TABLE jobs ( min_lvl int NOT NULL CHECK (min_lvl > = 10) )
判斷該文件是否存在：
DECLARE @err INT,@fso INT,@fleExists BIT,@file VARCHAR(100)
SET @file='d:\aaa.txt'
EXEC @err=sp_OACreate 'Scripting.FileSystemObject',@fso OUTPUT
EXEC @err=sp_OAMethod @fso, 'FileExists',@fleExists OUTPUT,@file
EXEC @err = sp_OADestroy @fso
IF @fleExists=0
PRINT '"' + @file + '" not exists'
ELSE
exec('exec xp_cmdshell ''del
'+@file+'''') --存在則刪除
設置級聯(lián)：
alter table 表名
constraint FK_employee foreign key (外鍵字段) references 主表(主鍵字段)ON UPDATE CASCADE
設置默認值：
alter table 表名
constraint FK_employee default 默認值 for 字段
