把工作这段期间sql server 中需要用的sql语句整理了一下

前些日子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 字段名 字段类型

--不允许空字符:
alter table 表名 add 新字段 字段类型 not 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) 　　--@@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就没有再测试一遍了.