数据库教程:MSSQL基本语法及实例操作语句

ctrl+shift+r

select *from [table]

update [table] set [字段]=[values]

update a set a1 = b1, a2 = b2, a3 = b3 from a, b where a.id = b.id

delete [table]

drop [table]

select *from [table] where [字段]=[values]

/*--开始事务--*/  begin transaction  declare @errorsum int    --定义变量，用于累计事务执行过程中的错误  /*--转帐--*/  update bank set currentmoney=currentmoney-800 where customername='张三'  set @errorsum=@errorsum+@@error    --累计是否有错误  update bank set currentmoney=currentmoney+800 where customername='李四'  set @errorsum=@errorsum+@@error --累计是否有错误    print '查看转帐事务过程中的余额'  select * from bank    /*--根据是否有错误，确定事务是提交还是回滚--*/  if @errorsum>0      begin          print '交易失败，回滚事务.'          rollback transaction      end  else      begin          print '交易成功，提交事务，写入硬盘，永久保存！'          /*--提交并且结束事务--*/          commit transaction      end  go    print '查看转帐后的余额'  select * from bank  go

select * from sys.tables

select * from sys.columns

select * from sys.extended_properties

select t.[name] as 表名,c.[name] as 字段名,cast(ep.[value]  as varchar(100)) as [字段说明]  from sys.tables as t  inner join sys.columns as c   on t.object_id = c.object_id  left join sys.extended_properties as ep  on ep.major_id = c.object_id and ep.minor_id = c.column_id where ep.class =1  and t.name='utb_gbnt_proj_info'--------表名

create view viewname  (字段1，字段2)  as  (      select 字段1, 字段2       from [table]   )

select t.fieldexp 名称,t.columnname 代码,    case t.fielddatatype   when 'bigint' then t.fielddatatype  when 'int' then t.fielddatatype  when 'datetime' then t.fielddatatype  when 'numeric' then t.fielddatatype  when 'nvarchar' then (cast(t.fielddatatype as varchar(100))+'('+cast(t.fieldlength as varchar(100))+')')  when 'decimal' then (cast(t.fielddatatype as varchar(100))+'('+cast(t.fieldlength as varchar(100))+','+cast(t.scale as varchar(100))+')')  end as 数据类型    ,t.fieldlength 长度,t.scale 精确度    ,case t.fieldnullable  when 0 then '不为空'  when 1 then '' end as 说明    from (select   colorder=c.column_id,   fieldexp=isnull(pfd.[value],n''),   columnname=c.name,   fielddatatype=t.name,   fieldlength=columnproperty(c.object_id ,c.name ,'precision'),   scale=c.scale,  fieldnullable=c.is_nullable  --fielddefval=d.definition  from sys.columns c   inner join sys.objects o   on c.[object_id]=o.[object_id]   and (o.type='u' or o.type='v')   and o.is_ms_shipped=0   inner join sys.types t   on c.user_type_id=t.user_type_id   left join sys.default_constraints d   on c.[object_id]=d.parent_object_id   and c.column_id=d.parent_column_id   and c.default_object_id=d.[object_id]   left join sys.extended_properties pfd   on pfd.class=1   and c.[object_id]=pfd.major_id   and c.column_id=pfd.minor_id   left join sys.extended_properties ptb   on ptb.class=1   and ptb.minor_id=0   and c.[object_id]=ptb.major_id   left join   (   select   idxc.[object_id],   idxc.column_id,   sort=case indexkey_property(idxc.[object_id],idxc.index_id,idxc.index_column_id,'isdescending')  when 1 then 'desc' when 0 then 'asc' else '' end,    primarykey=case when idx.is_primary_key=1 then n'√'else n'' end,   indexname=idx.name    from sys.indexes idx    inner join sys.index_columns idxc    on idx.[object_id]=idxc.[object_id]    and idx.index_id=idxc.index_id    left join sys.key_constraints kc    on idx.[object_id]=kc.[parent_object_id]    and idx.index_id=kc.unique_index_id    inner join    (    select [object_id], column_id, index_id=min(index_id)    from sys.index_columns    group by [object_id], column_id    ) idxcuq    on idxc.[object_id]=idxcuq.[object_id]    and idxc.column_id=idxcuq.column_id    and idxc.index_id=idxcuq.index_id    ) idx    on c.[object_id]=idx.[object_id]    and c.column_id=idx.column_id    where o.name='--tablename--')as t  order by colorder,columnname

select    表名=case when a.colorder=1 then d.name else '' end,   表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,  字段序号=a.colorder,   字段名=a.name,   标识=case when columnproperty(a.id,a.name,'isidentity')=1 then '√'else '' end,   主键=case when exists(select 1 from sysobjects where xtype='pk' and name in (    select name from sysindexes where indid in(     select indid from sysindexkeys where id = a.id and colid=a.colid      ))) then '√' else '' end,   类型=b.name,   占用字节数=a.length,   长度=columnproperty(a.id,a.name,'precision'),   小数位数=isnull(columnproperty(a.id,a.name,'scale'),0),   允许空=case when a.isnullable=1 then '√'else '' end,   默认值=isnull(e.text,''),   字段说明=isnull(g.[value],'')   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 sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id   left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0   --where d.name='要查询的表' --如果只查询指定表,加上此条件     order by a.id,a.colorder