刷新本地缓存
ctrl+shift+r
查询
select *from [table]
修改
1、普通更新
update [table] set [字段]=[values]
2、关联表更新
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]
事务
1 开始事务: begin transaction
2 提交事务:commit transaction
3 回滚事务: rollback transaction
4 结束事务: 提交或回滚事务都将结束事务
/*--开始事务--*/ 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
到此这篇关于mssql基本语法及实例操作语句的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持<计算机技术网(www.ctvol.com)!!>。
需要了解更多数据库技术:MSSQL基本语法及实例操作语句,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/1086912.html