整理的一些T-sql（轉）
把長日期轉換為短日期 Convert(char(10),getdate(),120)
MS-SQL資料庫開發常用匯總 1.按姓氏筆畫排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.資料庫加密:
select encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同 encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同
3.取回表中欄位:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@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 '?'
7.記錄搜尋:
開頭到N條記錄
Select Top N * From 表
-------------------------------
N到M條記錄(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到結尾記錄
Select Top N * From 表 Order by ID Desc
8.如何修改資料庫的名稱:
sp_renamedb 'old_name', 'new_name'
9：獲取當前資料庫中的所有使用者表
select Name from sysobjects where xtype='u' and status>=0
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 '%表名%'
12：檢視當前資料庫中所有儲存過程
select name as 儲存過程名稱 from sysobjects where xtype='P'
13：查詢使用者建立的所有資料庫
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid ＜> 0x01
14：查詢某一個表的欄位和資料型別
select column_name,data_type from information_schema.columns
where table_name = '表名'
[n].[標題]:
Select * From TableName Order By CustomerName
[n].[標題]:
來自http://dev.csdn.net/develop/article/83/83138.shtm
select top 0 * into [t1] from [t2]
if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
drop function fgetscript
go
create function fgetscript(
@servername varchar(50) --伺服器名
,@userid varchar(50)='sa' --使用者名稱,如果為nt驗證方式,則為空
,@password varchar(50)='' --密碼
,@databasename varchar(50) --資料庫名稱
,@objectname varchar(250) --物件名
) returns varchar(8000)
as
begin
declare @re varchar(8000) --返回指令碼
declare @srvid int,@dbsid int --定義伺服器、資料庫集id
declare @dbid int,@tbid int --資料庫、表id
declare @err int,@src varchar(255), @desc varchar(255) --錯誤處理變數
--建立sqldmo物件
exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
if @err<>0 goto lberr
--連線伺服器
if isnull(@userid,'')='' --如果是 Nt驗證方式
begin
exec @err=sp_oasetproperty @srvid,'loginsecure',1
if @err<>0 goto lberr
exec @err=sp_oamethod @srvid,'connect',null,@servername
end
else
exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password
if @err<>0 goto lberr
--獲取資料庫集
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
if @err<>0 goto lberr
--獲取要取得指令碼的資料庫id
exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
if @err<>0 goto lberr
--獲取要取得指令碼的物件id
exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
if @err<>0 goto lberr
--取得指令碼
exec @err=sp_oamethod @tbid,'script',@re output
if @err<>0 goto lberr
--print @re
return(@re)
lberr:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4)
set @errb=cast(@err as varbinary(4))
exec master..xp_varbintohexstr @errb,@re out
set @re='錯誤號: '+@re
+char(13)+'錯誤源: '+@src
+char(13)+'錯誤描述: '+@desc
return(@re)
end
go
print dbo.fgetscript('伺服器名','使用者名稱','密碼','資料庫名','表名或其它物件名')
declare @name varchar(250)
declare #aa cursor for
select name from sysobjects where xtype not in('S','PK','D','X','L')
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
fetch next from #aa into @name
end
close #aa
deallocate #aa
create function getstrarrlength (@str varchar(8000))
returns int
as
begin
declare @int_return int
declare @start int
declare @next int
declare @location int
select @str =','+ @str +','
select @str=replace(@str,',,',',')
select @start =1
select @next =1
select @location = charindex(',',@str,@start)
while (@location <>0)
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
select @int_return = @next-2
return @int_return
end
create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1 --如果習慣從0開始則select @next =0
select @location = charindex(',',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1 --如果是因為沒有逗號退出，則認為逗號在字串後
select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗號之後的位置或者就是初始值1
if (@index <> @next ) select @str_return = '' --如果二者不相等，則是因為逗號太少，或者@index小於@next的初始值1。
return @str_return
end
SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)
select * from OPENDATASOURCE('SQLOLEDB','Data Source=遠端ip;User ID=sa;Password=密碼').庫名.dbo.表名
EXEC sp_addlinkedserver '別名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=遠端名;UID=使用者;PWD=密碼;'
exec sp_addlinkedsrvlogin @rmtsrvname='別名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密碼'
GO
select * from 別名.庫名.dbo.表名
insert 庫名.dbo.表名 select * from 別名.庫名.dbo.表名
select * into 庫名.dbo.新表名 from 別名.庫名.dbo.表名
go
Create view fielddesc
as
select o.name as table_name,c.name as field_name,t.name as type,c.length as
length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
from syscolumns c
join systypes t on c.xtype = t.xusertype
join sysobjects o on o.id=c.id
left join sysproperties p on p.smallid=c.colid and p.id=o.id
where o.xtype='U'
Select * from fielddesc where table_name = '你的表名'
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'欄位序號',
a.name N'欄位名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'標識',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主鍵',
b.name N'型別',
a.length N'佔用位元組數',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位數',
(case when a.isnullable=1 then '√'else '' end) N'允許空',
isnull(e.text,'') N'預設值',
isnull(g.[value],'') AS N'欄位說明'
--into ##tx
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
UPDATE lvshi
SET shengri = '19' + REPLACE(shengri, '.', '-')
WHERE (zhiyezheng = '139770070153')
SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month,
SUBSTRING(shengri, 9, 2) AS day
FROM lvshi
WHERE (zhiyezheng = '139770070153')
UPDATE lvshi
SET shenling = CONVERT(varchar(4), YEAR(shenling))
+ '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2),
month(shenling)) ELSE CONVERT(varchar(2), month(shenling))
END + '-' + CASE WHEN LEN(day(shenling)) = 1 THEN '0' + CONVERT(char(2),
day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END
WHERE (zhiyezheng = '139770070153')
--看下面的示例
--示例表
create table tempdb.dbo.t_10(
id int primary key check(id between 1 and 10),name varchar(10))
create table pubs.dbo.t_20(
id int primary key check(id between 11 and 20),name varchar(10))
create table northwind.dbo.t_30(
id int primary key check(id between 21 and 30),name varchar(10))
go
--分割槽檢視
create view v_t
as
select * from tempdb.dbo.t_10
union all
select * from pubs.dbo.t_20
union all
select * from northwind.dbo.t_30
go
--插入資料
insert v_t select 1 ,'aa'
union all select 2 ,'bb'
union all select 11,'cc'
union all select 12,'dd'
union all select 21,'ee'
union all select 22,'ff'
--更新資料
update v_t set name=name+'_更新' where right(id,1)=1
--刪除測試
delete from v_t where right(id,1)=2
--顯示結果
select * from v_t
go
--刪除測試
drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
drop view v_t
/**//*--測試結果
id name
----------- ----------
1 aa_更新
11 cc_更新
21 ee_更新
（所影響的行數為 3 行）
==*/
--參考
--樹形資料查詢示例
--作者: 鄒建
--示例資料
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中國'
union all select 0,'美國'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江蘇'
union all select 6,'蘇州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'無錫'
union all select 2,'紐約'
union all select 2,'舊金山'
go
--查詢指定id的所有子
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
/**//**//**//*--如果只顯示最明細的子(下面沒有子),則加上這個刪除
delete a from @re a
where exists(
select 1 from [tb] where [pid]=a.[id])
--*/
return
end
go
--呼叫(查詢所有的子)
select a.*,層次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go
--刪除測試
drop table [tb]
drop function f_cid
go
CREATE TABLE [t] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
insert t values (newid())
select * from t
select * from t
order by case id when 4 then 1
when 5 then 2
when 1 then 3
when 2 then 4
when 3 then 5 end
select * from t order by (id+2)%6
select * from t order by charindex(cast(id as varchar),'45123')
select * from t
WHERE id between 0 and 5
order by charindex(cast(id as varchar),'45123')
select * from t order by case when id >3 then id-5 else id end
select * from t order by id / 4 desc,id asc
delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0
delete from table1 where id in(1,2,3,4 
)
CREATE FUNCTION fn_Get05LvshiNameBySuo (@p_suo Nvarchar(50))
RETURNS Nvarchar(2000)
AS
BEGIN
DECLARE @LvshiNames varchar(2000), @name varchar(50)
select @LvshiNames=''
DECLARE lvshi_cursor CURSOR FOR
資料庫裡有1,2,3,4,5 共5條記錄,要用一條sql語句讓其排序,使它排列成4,5,1,2,3,怎麼寫?
--資料操作
SELECT --從資料庫表中檢索資料行和列
INSERT --向資料庫表新增新資料行
DELETE --從資料庫表中刪除資料行
UPDATE --更新資料庫表中的資料
--資料定義
CREATE TABLE --建立一個資料庫表
DROP TABLE --從資料庫中刪除表
ALTER TABLE --修改資料庫表結構
CREATE VIEW --建立一個檢視
DROP VIEW --從資料庫中刪除檢視
CREATE INDEX --為資料庫表建立一個索引
DROP INDEX --從資料庫中刪除索引
CREATE PROCEDURE --建立一個儲存過程
DROP PROCEDURE --從資料庫中刪除儲存過程
CREATE TRIGGER --建立一個觸發器
DROP TRIGGER --從資料庫中刪除觸發器
CREATE SCHEMA --向資料庫新增一個新模式
DROP SCHEMA --從資料庫中刪除一個模式
CREATE DOMAIN --建立一個資料值域
ALTER DOMAIN --改變域定義
DROP DOMAIN --從資料庫中刪除一個域
--資料控制
GRANT --
MS-SQL資料庫開發常用匯總 1.按姓氏筆畫排序:Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2.資料庫加密:select encrypt('原始密碼')select pwdencrypt('原始密碼')select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同 encrypt('原始密碼')select pwdencrypt('原始密碼')select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同3.取回表中欄位:declare @list varchar(1000),@sql nvarchar(1000) select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'set @sql='select '+right(@list,len(@list)-1)+' from 表A' exec (@sql)4.檢視硬碟分割槽:EXEC master..xp_fixeddrives5.比較A,B表是否相等:if (select checksum_agg(binary_checksum(*)) from A)=(select checksum_agg(binary_checksum(*)) from B)print '相等'elseprint '不相等'6.殺掉所有的事件探察器程式:DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocessesWHERE program_name IN('SQL profiler',N'SQL 事件探查器')EXEC sp_msforeach_worker '?'7.記錄搜尋:開頭到N條記錄Select Top N * From 表-------------------------------N到M條記錄(要有主索引ID)Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc----------------------------------N到結尾記錄Select Top N * From 表 Order by ID Desc8.如何修改資料庫的名稱:sp_renamedb 'old_name', 'new_name' 9：獲取當前資料庫中的所有使用者表select Name from sysobjects where xtype='u' and status>=010：獲取某一個表的所有欄位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 '%表名%'12：檢視當前資料庫中所有儲存過程select name as 儲存過程名稱 from sysobjects where xtype='P'13：查詢使用者建立的所有資料庫select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')或者select dbid, name AS DB_NAME from master..sysdatabases where sid ＜> 0x0114：查詢某一個表的欄位和資料型別select column_name,data_type from information_schema.columnswhere table_name = '表名' [n].[標題]:Select * From TableName Order By CustomerName [n].[標題]:一、 只複製一個表結構，不復制資料
select top 0 * into [t1] from [t2]
二、 獲取資料庫中某個物件的建立指令碼
1、 先用下面的指令碼建立一個函式
if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0) drop function fgetscriptgocreate function fgetscript( @servername varchar(50) --伺服器名 ,@userid varchar(50)='sa' --使用者名稱,如果為nt驗證方式,則為空 ,@password varchar(50)='' --密碼 ,@databasename varchar(50) --資料庫名稱 ,@objectname varchar(250) --物件名) returns varchar(8000)asbegin declare @re varchar(8000) --返回指令碼 declare @srvid int,@dbsid int --定義伺服器、資料庫集id declare @dbid int,@tbid int --資料庫、表id declare @err int,@src varchar(255), @desc varchar(255) --錯誤處理變數--建立sqldmo物件 exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output if @err<>0 goto lberr--連線伺服器 if isnull(@userid,'')='' --如果是 Nt驗證方式 begin exec @err=sp_oasetproperty @srvid,'loginsecure',1 if @err<>0 goto lberr exec @err=sp_oamethod @srvid,'connect',null,@servername end else exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password if @err<>0 goto lberr--獲取資料庫集 exec @err=sp_oagetproperty @srvid,'databases',@dbsid output if @err<>0 goto lberr--獲取要取得指令碼的資料庫id exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename if @err<>0 goto lberr--獲取要取得指令碼的物件id exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname if @err<>0 goto lberr--取得指令碼 exec @err=sp_oamethod @tbid,'script',@re output if @err<>0 goto lberr --print @re return(@re)lberr: exec sp_oageterrorinfo NULL, @src out, @desc out declare @errb varbinary(4) set @errb=cast(@err as varbinary(4)) exec master..xp_varbintohexstr @errb,@re out set @re='錯誤號: '+@re +char(13)+'錯誤源: '+@src +char(13)+'錯誤描述: '+@desc return(@re)endgo2、 用法如下
用法如下,
print dbo.fgetscript('伺服器名','使用者名稱','密碼','資料庫名','表名或其它物件名')3、 如果要獲取庫裡所有物件的指令碼，如如下方式
declare @name varchar(250)declare #aa cursor for select name from sysobjects where xtype not in('S','PK','D','X','L')open #aafetch next from #aa into @namewhile @@fetch_status=0begin print dbo.fgetscript('onlytiancai','sa','sa','database',@name) fetch next from #aa into @nameendclose #aadeallocate #aa4、 宣告，此函式是csdn鄒建鄒老大提供的
三、 分隔字串
如果有一個用逗號分割開的字串，比如說"a,b,c,d,1,2,3,4"，如何用t-sql獲取這個字串有幾個元素，獲取第幾個元素的值是多少呢？因為t-sql裡沒有split函式，也沒有陣列的概念，所以只能自己寫幾個函式了。
1、 獲取元素個數的函式
create function getstrarrlength (@str varchar(8000))returns intasbegin declare @int_return int declare @start int declare @next int declare @location int select @str =','+ @str +',' select @str=replace(@str,',,',',') select @start =1 select @next =1 select @location = charindex(',',@str,@start) while (@location <>0) begin select @start = @location +1 select @location = charindex(',',@str,@start) select @next =@next +1 end select @int_return = @next-2 return @int_returnend2、 獲取指定索引的值的函式
create function getstrofindex (@str varchar(8000),@index int =0)returns varchar(8000)asbegin declare @str_return varchar(8000) declare @start int declare @next int declare @location int select @start =1 select @next =1 --如果習慣從0開始則select @next =0 select @location = charindex(',',@str,@start) while (@location <>0 and @index > @next ) begin select @start = @location +1 select @location = charindex(',',@str,@start) select @next =@next +1 end if @location =0 select @location =len(@str)+1 --如果是因為沒有逗號退出，則認為逗號在字串後 select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗號之後的位置或者就是初始值1 if (@index <> @next ) select @str_return = '' --如果二者不相等，則是因為逗號太少，或者@index小於@next的初始值1。 return @str_returnend3、 測試
SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)四、 一條語句執行跨越若干個資料庫
我要在一條語句裡操作不同的伺服器上的不同的資料庫裡的不同的表,怎麼辦呢?
第一種方法:
select * from OPENDATASOURCE('SQLOLEDB','Data Source=遠端ip;User ID=sa;Password=密碼').庫名.dbo.表名第二種方法:
先使用聯結伺服器:
EXEC sp_addlinkedserver '別名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=遠端名;UID=使用者;PWD=密碼;'exec sp_addlinkedsrvlogin @rmtsrvname='別名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密碼'GO然後你就可以如下：
select * from 別名.庫名.dbo.表名insert 庫名.dbo.表名 select * from 別名.庫名.dbo.表名select * into 庫名.dbo.新表名 from 別名.庫名.dbo.表名go五、 怎樣獲取一個表中所有的欄位資訊
蛙蛙推薦：怎樣獲取一個表中所有欄位的資訊
先建立一個檢視
Create view fielddesc asselect o.name as table_name,c.name as field_name,t.name as type,c.length as length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp from syscolumns c join systypes t on c.xtype = t.xusertypejoin sysobjects o on o.id=c.id left join sysproperties p on p.smallid=c.colid and p.id=o.id where o.xtype='U'查詢時:
Select * from fielddesc where table_name = '你的表名'
還有個更強的語句，是鄒建寫的，也寫出來吧
SELECT (case when a.colorder=1 then d.name else '' end) N'表名', a.colorder N'欄位序號', a.name N'欄位名', (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'標識', (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '√' else '' end) N'主鍵', b.name N'型別', a.length N'佔用位元組數', COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度', isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位數', (case when a.isnullable=1 then '√'else '' end) N'允許空', isnull(e.text,'') N'預設值', isnull(g.[value],'') AS N'欄位說明'--into ##txFROM syscolumns a left join systypes b on a.xtype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'left join syscomments eon a.cdefault=e.idleft join sysproperties gon a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder六、 時間格式轉換問題
因為新開發的軟體需要用一些舊軟體生成的一些資料，在時間格式上不統一，只能手工轉換，研究了一下午寫了三條語句，以前沒怎麼用過convert函式和case語句，還有"+"運算子在不同上下文環境也會起到不同的作用，把我搞暈了要，不過現在看來是差不多弄好了。
1、把所有"70.07.06"這樣的值變成"1970-07-06"
UPDATE lvshiSET shengri = '19' + REPLACE(shengri, '.', '-')WHERE (zhiyezheng = '139770070153')
2、在"1970-07-06"裡提取"70","07","06"
SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month, SUBSTRING(shengri, 9, 2) AS dayFROM lvshiWHERE (zhiyezheng = '139770070153')3、把一個時間型別欄位轉換成"1970-07-06"
UPDATE lvshiSET shenling = CONVERT(varchar(4), YEAR(shenling)) + '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2), month(shenling)) ELSE CONVERT(varchar(2), month(shenling)) END + '-' + CASE WHEN LEN(day(shenling)) = 1 THEN '0' + CONVERT(char(2), day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) ENDWHERE (zhiyezheng = '139770070153')七、 分割槽檢視
分割槽檢視是提高查詢效能的一個很好的辦法
--看下面的示例--示例表create table tempdb.dbo.t_10(id int primary key check(id between 1 and 10),name varchar(10))create table pubs.dbo.t_20(id int primary key check(id between 11 and 20),name varchar(10))create table northwind.dbo.t_30(id int primary key check(id between 21 and 30),name varchar(10))go--分割槽檢視create view v_tasselect * from tempdb.dbo.t_10union allselect * from pubs.dbo.t_20union allselect * from northwind.dbo.t_30go--插入資料insert v_t select 1 ,'aa'union all select 2 ,'bb'union all select 11,'cc'union all select 12,'dd'union all select 21,'ee'union all select 22,'ff'--更新資料update v_t set name=name+'_更新' where right(id,1)=1--刪除測試delete from v_t where right(id,1)=2--顯示結果select * from v_tgo--刪除測試drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10drop view v_t/**//*--測試結果id name ----------- ---------- 1 aa_更新11 cc_更新21 ee_更新（所影響的行數為 3 行）==*/
八、 樹型的實現
--參考--樹形資料查詢示例--作者: 鄒建--示例資料create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))insert [tb] select 0,'中國'union all select 0,'美國'union all select 0,'加拿大'union all select 1,'北京'union all select 1,'上海'union all select 1,'江蘇'union all select 6,'蘇州'union all select 7,'常熟'union all select 6,'南京'union all select 6,'無錫'union all select 2,'紐約'union all select 2,'舊金山'go--查詢指定id的所有子create function f_cid(@id int)returns @re table([id] int,[level] int)asbegin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.[id],@l from [tb] a,@re b where a.[pid]=b.[id] and b.[level]=@l-1 end/**//**//**//*--如果只顯示最明細的子(下面沒有子),則加上這個刪除 delete a from @re a where exists( select 1 from [tb] where [pid]=a.[id])--*/ returnendgo--呼叫(查詢所有的子)select a.*,層次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]go--刪除測試drop table [tb]drop function f_cidgo
九、 排序問題
CREATE TABLE [t] ( [id] [int] IDENTITY (1, 1) NOT NULL , [GUID] [uniqueidentifier] NULL ) ON [PRIMARY]GO
下面這句執行5次
insert t values (newid())
檢視執行結果
select * from t
1、 第一種
select * from t order by case id when 4 then 1 when 5 then 2 when 1 then 3 when 2 then 4 when 3 then 5 end
2、 第二種
select * from t order by (id+2)%6
3、 第三種
select * from t order by charindex(cast(id as varchar),'45123')
4、 第四種
select * from tWHERE id between 0 and 5order by charindex(cast(id as varchar),'45123')
5、 第五種
select * from t order by case when id >3 then id-5 else id end
6、 第六種
select * from t order by id / 4 desc,id asc
十、 一條語句刪除一批記錄
首先id列是int標識類型別，然後刪除ID值為5,6,8,9,10,11的列，這裡的cast函式不能用
convert函式代替，而且轉換的型別必須是varchar，而不能是char，否則就會執行出你不希望的結果，這裡的"5,6,8,9,10,11"
可以是你在頁面上獲取的一個chkboxlist構建成的值，然後用下面的一句就全部刪
除了，比迴圈用多條語句高效吧應該。
delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0
還有一種就是
delete from table1 where id in(1,2,3,4 )
十一、獲取子表內的一列資料的組合字串
下面這個函式獲取05年已經註冊了的某個所的律師，唯一一個引數就是事務所的名稱，然後返回zhuce欄位裡包含05字樣的所有律師。
CREATE FUNCTION fn_Get05LvshiNameBySuo (@p_suo Nvarchar(50))RETURNS Nvarchar(2000)ASBEGIN DECLARE @LvshiNames varchar(2000), @name varchar(50) select @LvshiNames='' DECLARE lvshi_cursor CURSOR FOR資料庫裡有1,2,3,4,5 共5條記錄,要用一條sql語句讓其排序,使它排列成4,5,1,2,3,怎麼寫?
--資料操作 SELECT --從資料庫表中檢索資料行和列 INSERT --向資料庫表新增新資料行 DELETE --從資料庫表中刪除資料行 UPDATE --更新資料庫表中的資料 --資料定義 CREATE TABLE --建立一個資料庫表 DROP TABLE --從資料庫中刪除表 ALTER TABLE --修改資料庫表結構 CREATE VIEW --建立一個檢視 DROP VIEW --從資料庫中刪除檢視 CREATE INDEX --為資料庫表建立一個索引 DROP INDEX --從資料庫中刪除索引 CREATE PROCEDURE --建立一個儲存過程 DROP PROCEDURE --從資料庫中刪除儲存過程 CREATE TRIGGER --建立一個觸發器 DROP TRIGGER --從資料庫中刪除觸發器 CREATE SCHEMA --向資料庫新增一個新模式 DROP SCHEMA --從資料庫中刪除一個模式 CREATE DOMAIN --建立一個資料值域 ALTER DOMAIN --改變域定義 DROP DOMAIN --從資料庫中刪除一個域 --資料控制 GRANT --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/93029/viewspace-1023498/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 整理的一些常用系統表 (轉)
- 整理Object的一些方法Object
- oracle set的一些整理Oracle
- 關於一些爬蟲專案教程的整理（轉載）爬蟲
- scipy.sparse的一些整理
- 整理一些CSS的知識CSS
- GitHub上整理的一些工具Github
- 關於MySQL event的一些整理MySql
- 整理的一些常用到的 Nginx 配置Nginx
- T-SQL——將字串轉為單列SQL字串
- Proguard整理一些資料
- 一些老筆記整理筆記
- Linux的一些工具使用技巧整理Linux
- 整理的一些SQL題,與討論SQL
- MySQL的一些常用的SQL語句整理MySql
- T-SQL——將字串轉換為多列SQL字串
- 【T-SQL】〇、 T-SQL語法說明SQL
- Linux下一些操作的簡單整理Linux
- JavaScript關於陣列的一些方法整理JavaScript陣列
- 專案中常用到的一些方法整理
- 【API分享】整理一些免費好用的APIAPI
- Spring 常見的一些面試題整理Spring面試題
- Unity5 AssetBundle的一些整理（一）Unity
- 整理總結的一些前端面試題前端面試題
- 整理的一些oracle備份筆記 (zt)Oracle筆記
- 一些前端小問題整理前端
- 整理一些筆記上傳筆記
- latch free事件的整理(轉)事件
- Flutter Notes ｜ 我用到的一些外掛整理Flutter
- 一些常見的重置密碼漏洞分析整理密碼
- js 一些專案中常用的原型方法整理JS原型
- 整理記錄一些好用的隨機圖API隨機API
- DBA的最佳選擇—圖形介面還是T-SQL命令？ (轉)SQL
- T-SQL AliasSQL
- 整理了一些前端設計相關的網站前端網站
- 整理了一些好用的api，含免費次數API
- 一些知識點的整理以及面試題記錄面試題
- 整理一些前端開發比較好的node包前端