把工作這段期間sql server 中需要用的sql語句整理了一下

標籤：des   style   io   strong   for   ar   資料   2014   cti   

前些日子sql用到哪裡寫到哪裡,亂七八糟,今天整理了一下，以作備份(雖然開通部落格已經八個月了,但是今天還是第一次發表博文,好緊張啊~~)

--2014.08.27號整理sql語句

1:進入資料庫
use [資料庫名]
eg: use [dev]

2:建立表
create table 表名(
[ID] int identity(1,1) primary key,
[列名] 資料類型 約束 ,
)
eg:
create table atblTest1(
[ID] int identity(1,1) primary key, --從1開始每次增加1
[Key] varchar(200) unique ,
[Value] decimal(18,4) ,                --小數點後面保留四位小數,資料長18位(不加小數點位)
[Unit1] varchar(100),

--Message text,

--Time datetime

--[count] int,
)

 

3:操作現有表/欄位

--修改欄位資料類型
alter table 表名 alter column 欄位名 欄位類型

--刪除欄位出現約束什麼錯誤
ALTER TABLE 表名 DROP CONSTRAINT 預設約束名
GO
ALTER TABLE 表名 DROP COLUMN欄位名
GO

--擷取預設約束名
select name
from sysobjects
where id=(select cdefault
from syscolumns
where name=‘欄位名‘ and id = (select id
from sysobjects
where name = ‘表名‘))

--添加欄位
alter table 表名 add 欄位名 欄位類型

--不允許Null 字元:
alter table 表名 add 新欄位 欄位類型 not NULL

--允許Null 字元:
alter table 表名 add 新欄位 欄位類型 NULL

--修改欄位
exec sp_rename ‘表名.原列名‘,‘新列名‘,‘column‘

--修改表名
exec sp_rename ‘舊錶名‘, ‘新表名‘

eg:
exec sp_rename ‘atblTest1‘, ‘atblTest‘
exec sp_rename ‘atblTest.Unit1‘,‘Unit‘,‘column‘
alter table atblTest alter column [Unit] varchar(200)
alter table atblTransactions add IsInsertAccounting bit
alter table atblMembers add AccountUserId int
alter table atblAccounts add PendingBalance money

 

4:添加資料
insert into 表名(列名1,列名2,列名3) values(資料1,資料2,資料3)

eg: insert into atblTest values(‘LOBcheckFee‘,3,‘dollars‘)

 

5:更改資料
update 表名 set 列名 = 列名資料 where id=標識資料

eg: update atblTest set [Unit] =‘dollar‘ where [ID]=1

 

6:建立觸發器 (就不連貫著來發例子了啊)

create trigger 觸發器名     --建立觸發器名字
on 觸發器所在表               --在這個表中建立觸發器
for Update                     -- 因為哪個事件而觸發(insert ,update,delete)
as                                 --事件觸發後所做的事情
if Update(該表欄位)          --如果修改XX欄位
begin
引發的sql操作
end

eg:(主要是如果ablLeads表QuantityOnHand數量改變,就會將改變的過程存到另一個表中:1-->0)
create trigger trQuantityOnHand
on atblLeads
for Update
as
if Update(QuantityOnHand)
begin
　　declare @QuantityOnHandOld int, @QuantityOnHandNew int,@LeadID int;

　　select @QuantityOnHandNew=QuantityOnHand,@LeadID=LeadID from inserted;

　　select @QuantityOnHandOld=QuantityOnHand from deleted;

　　if(@QuantityOnHandOld != @QuantityOnHandNew)
　　begin
　　　　insert into atblTrigger([Type],[Time],[Result],[LeadID])
　　values(‘QuantityOnHand‘,getdate(),convert(varchar, @QuantityOnHandOld)+‘ -> ‘+convert(varchar,@QuantityOnHandNew),@LeadID);
end
end

 

7:刪除觸發器

if(object_id(‘觸發器名稱‘) is not null)
drop trigger 觸發器名稱

if(object_id(‘trQuantityOnHand‘) is not null)
drop trigger trQuantityOnHand

 

8:對錶資料分頁
select * from
(
select * , row_number() over
(
order by
某種規律的欄位名(id)
) as rownum
from 表名
)DATA
where DATA.rownum>開始頁碼*每頁列顯數量 and DATA.rownum<開始頁碼*每頁列顯數量+每頁列顯數量

eg:列顯第六頁的資料即600-610之間的資料
select * from
(
select * , row_number() over
(
order by
LEADID
) as rownum
from atblLeads
)DATA
where DATA.rownum>60*10 and DATA.rownum<60*10+10

 

9:刪除表
drop table 表名

-----如果表存在就drop掉------------------------------
if exists (select 1
from sysobjects
where id = object_id(‘表名‘)
and type = ‘U‘)
drop table 表名
go

--eg:
if exists (select 1
from sysobjects
where id = object_id(‘[dbo].[db3_IndexingDetails]‘)
and type = ‘U‘)
drop table [dbo].[db3_IndexingDetails]
go

10:控制sql修改數量

eg:
begin tran
update atblOrders set OrderStatus = 2 where EbayOrderID = ‘131235536269-0‘
if(@@ROWCOUNT>1) 　　[email protected]@ROWCOUNT 是受影響的行數
　　begin rollback tran
　　　　print ‘sql超過了指定的受影響行數,將不會執行該語句‘
　　end
else if(@@ROWCOUNT=0)
　　begin
　　　　print ‘執行失敗‘
　　end
else
　　begin commit tran
　　print ‘恭喜你執行完成‘
end

 

11:獲得行數
select count(*) from 你的表名

 

12:獲得列名以及詳細資料
select * from syscolumns where id = object_id(‘表名‘)

eg: select * from syscolumns where id = object_id(‘atblUsers‘)

 

13:獲得列名數量
select count(*) from syscolumns where id=object_id(‘你的表名‘)

 

14:讀取庫中的所有表名
select name from sysobjects where xtype=‘u‘

 

15:讀取指定表的所有列名
select name from syscolumns where id=(select max(id) from sysobjects where xtype=‘u‘ and name=‘表名‘)

 

16:擷取欄位類型
select t.name from sysobjects o,syscolumns c,systypes t
where o.id=c.id and c.usertype=t.usertype and o.name=‘表名‘ and c.name=‘列名‘

17:Select語句 區分查詢資料的大小寫

--不區分大小寫（預設不區分）
select top 10 * from atblUsers where [Password]=‘sunflower134‘ COLLATE Chinese_PRC_CI_AS

eg:--區分大小寫 針對某個欄位
select top 10 * from atblUsers where [Password]=‘sunflower134‘ COLLATE Chinese_PRC_CS_AS AND Status=1

 

18:資料查詢

eg:
select TransactionID,AccountID,TransactAmount,TransactDate,TransactType,
Credit = CASE WHEN TransactAmount = 0 THEN 0 WHEN TransactAmount > 0 THEN TransactAmount END, --(添加的列顯欄位)
Debit = CASE WHEN TransactAmount = 0 THEN 0 WHEN TransactAmount < 0 THEN -TransactAmount END
FROM [atblAccounting]
WHERE [AccountID] = 53 order by TransactDate DESC ,TransactType desc

select Sum(TransactAmount) from atblAccounting where AccountID=54

select TOP 200 * from atblAccounting order by TransactionID DESC

select b.IsInsertAccounting, * from atblAccounting a
inner join atblTransactions b on a.ebayOrderID=b.eBayOrderID and a.ItemID = b.ItemID

select count(*) from atblUsereBayAuthToken

 

19:刪除表中的所有資料
DELETE FROM 表名

 

20:查詢SQLserver的詳細版本資訊
select @@VERSION

 

大晚上了,這些sql大多數是已經寫過了,所以例子中sql就沒有再測試一遍了.

把工作這段期間sql server 中需要用的sql語句整理了一下