服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - MSSQL基本语法及实例操作语句

MSSQL基本语法及实例操作语句

2022-09-12 14:13一事冇诚 Sql Server

这篇文章介绍了MSSQL的基本语法及实例操作语句，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

刷新本地缓存

?
1
Ctrl+Shift+R

查询

?
1
select *from [table]

修改

1、普通更新

?
1
UPDATE [table] set [字段]=[values]

2、关联表更新

?
1
UPDATE A SET A1 = B1, A2 = B2, A3 = B3 FROM A, B WHERE A.ID = B.ID

删除（数据）

?
1
delete [table]

删除（表）

?
1
drop [table]

条件查询

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

事务

1 开始事务: begin transaction

2 提交事务：commit transaction

3 回滚事务: rollback transaction

4 结束事务: 提交或回滚事务都将结束事务

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/*--开始事务--*/
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

查询所有表名

?
1
select * from sys.tables

查询所有表包含的字段名

?
1
select * from sys.columns

查询所有字段说明

?
1
select * from sys.extended_properties

根据表查询所含字段说明

?
1
2
3
4
5
6
7
8
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'--------表名

创建视图

?
1
2
3
4
5
6
7
create view ViewName
(字段1，字段2)
as
(
    select 字段1, 字段2
    from [table]
)

数据字典

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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基本语法及实例操作语句的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：https://www.cnblogs.com/ysmc/p/16128243.html

  • MSSQL
  • 语法

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    2005 　 18052 　 3456 　 1355 　 29506 　 9003 　 5120 　 21002 　 5173 　 15138 　 15404 　 功能语句 　 去零函数 　 from 　 字符串聚合 　 备份记录 　 增长情况 　 多行记录 　 逻辑查询 　 实用语句 　 数据一致性 　 自动收缩 　 字段编号 　 约束增强 　 行列转换 　 @@ERROR 　 日志备份 　 分组统计 　 已经存在 　 numeric 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图