sql server学习

1、sql server查表结构:

     sp_help 表名

2、从备份的表中恢复表:

     drop table taskstorelist

     select * into taskstorelist from taskstorelist_bak_5

3、查询所有的表:

     select name from sysobjects where xtype ='U'

4、sql server多行插入:

begin tran


INSERT INTO [WWMSSDB].[dbo].[TaskStoreSpecialPicture]
(
CreateDate,
CreateTime,
Creator,
Modifier,
ModifyTime,
PDAFLAG,
TASK_PICTURE_SID,
TASK_STORE_SPECIAL_SID
)
select

'2013-03-27',
'09-01-18',
'00123441',
'00123441',
'09-01-18',
'I',
'P130327001234415a46',
'1033610'
UNION ALL
select
'2013-03-27',
'09:01:24',
'00123441',
'00123441',
'09:01:24',
'I',
'P130327001234415a4c',
'1033610'

UNION ALL
select

'2013-03-27',
'09:01:32',
'00123441',
'00123441',
'09:01:32',
'I',
'P130327001234415a52',
'1033610'
UNION ALL
select

'2013-03-27',
'09:01:45',
'00123441',
'00123441',
'09:01:45',
'I',
'P130327001234415a5f',
'1033610'
UNION ALL
select
'2013-03-27',
'09:04:28',
'00123441',
'00123441',
'09:04:28',
'I',
'P130327001234415aeg',
'1033609'
UNION ALL
select
'2013-03-27',
'09:04:34',
'00123441',
'00123441',
'09:04:34',
'I',
'P130327001234415af4',
'1033609'


rollback tran

5、查看表字段长度:

select length from syscolumns where id=(select id from sysobjects where name='taskpicture')and name='description'
 
6、筛选值的字段长度:
select  *from taskpicture where update_date1 >='2013-04-08' and len(description) >95
 
7、表增加删除字段:
alter table AuditDisplayStandard    add type char(2)
alter table DisplayBadRel drop column PDAFLAG,SSISFLAG
 
8、表重命名:
sp_rename 原表, 新表
 
9、复制表:
select * into  AuditDisplayStandard2  from  AuditDisplayStandard
 
10、添加删除主键:
     添加主键:Alter table tabname add primary key(col)
   删除主键:Alter table tabname drop primary key(col)
11、添加删除外键:

设置表myBBS中的authorId为外键,参照author表的Id字段,直接使用transact sql语句,过程如下: --增加表mybbs(authorId)的外键约束FK_mybbs_author,表myBBS中的authorId受表author中的主键ID约束:

alter table dbo.mybbs add constraint FK_mybbs_author foreign key (authorId) references dbo.author([id]) ON UPDATE CASCADE ON DELETE CASCADE

--删除外键约束FK_mybbs_author: --alter table dbo.mybbs drop constraint FK_mybbs_author --rollback

alter table AuditDisplayStandard drop constraint FK_Display_Bad_Tbl_author

12、修改表某个字段的属性

Alter table [表名] Alter column [列名] [列类型] [NOT NULL或者 NULL]

13、插入单笔数据

insert into SMSTbl(Linkman,PhoneNumber) values (N'杰','1551091068')

14、拼接update语句

 select ' update TaskStorePrice set IS_ENTERSTORE = null , name=123, sex=nan  where SID ='+ cast(sid as  char(10))+';' from TaskStorePrice where Pdaflag ='U' --and SID = '49606427'

15、转义用“”

update TaskStoreRoute set  "FIT-LINE"=null,REASON_FIT_LINE=null,PDAFLAG=null,IS_ALL=0

where UPDATE_USER1 =  @loginId

16、case when 
  CASE
            WHEN changebilltype='SY'
            THEN '消费'
            WHEN changebilltype='CZ'
             THEN '充值'
              WHEN changebilltype='SK'
             THEN '售卡'
        END

 

 

posted @ 2013-04-02 18:48  Earic  阅读(258)  评论(0编辑  收藏  举报