加入收藏 | 设为首页 | 会员中心 | 我要投稿 银川站长网 (https://www.0951zz.com/)- 云通信、基础存储、云上网络、机器学习、视觉智能!
当前位置: 首页 > 站长学院 > MsSql教程 > 正文

新手学习有哪些MSSQL基础语法及操作要了解

发布时间:2023-05-06 11:11:10 所属栏目:MsSql教程 来源:
导读:在这篇文章中,我们来学习一下“新手学习有哪些MSSQL基本语法及操作要了解”的相关知识,下文有详细的讲解,易于大家学习和理解,有需要的朋友可以借鉴参考,下面就请大家跟着小编的思路一起来学习一下吧。

在这篇文章中,我们来学习一下“新手学习有哪些MSSQL基本语法及操作要了解”的相关知识,下文有详细的讲解,易于大家学习和理解,有需要的朋友可以借鉴参考,下面就请大家跟着小编的思路一起来学习一下吧。

刷新本地缓存

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

(编辑:银川站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章