MSSQL基本语法及实例操作语句_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