MSSQL基本語(yǔ)法及實(shí)例操作語(yǔ)句
刷新本地緩存
Ctrl+Shift+R
查詢
select *from [table]
修改
1、普通更新
UPDATE [table] set [字段]=[values]
2、關(guān)聯(lián)表更新
UPDATE A SET A1 = B1, A2 = B2, A3 = B3 FROM A, B WHERE A.ID = B.ID
刪除(數(shù)據(jù))
delete [table]
刪除(表)
drop [table]
條件查詢
select *from [table] where [字段]=[values]
事務(wù)
1 開始事務(wù): begin transaction
2 提交事務(wù):commit transaction
3 回滾事務(wù): rollback transaction
4 結(jié)束事務(wù): 提交或回滾事務(wù)都將結(jié)束事務(wù)
/*--開始事務(wù)--*/ begin transaction declare @errorSum int --定義變量,用于累計(jì)事務(wù)執(zhí)行過(guò)程中的錯(cuò)誤 /*--轉(zhuǎn)帳--*/ update bank set currentMoney=currentMoney-800 where customerName='張三' set @errorSum=@errorSum+@@error --累計(jì)是否有錯(cuò)誤 update bank set currentMoney=currentMoney+800 where customerName='李四' set @errorSum=@errorSum+@@error --累計(jì)是否有錯(cuò)誤 print '查看轉(zhuǎn)帳事務(wù)過(guò)程中的余額' select * from bank /*--根據(jù)是否有錯(cuò)誤,確定事務(wù)是提交還是回滾--*/ if @errorSum>0 begin print '交易失敗,回滾事務(wù).' rollback transaction end else begin print '交易成功,提交事務(wù),寫入硬盤,永久保存!' /*--提交并且結(jié)束事務(wù)--*/ commit transaction end go print '查看轉(zhuǎn)帳后的余額' select * from bank go
查詢所有表名
select * from sys.tables
查詢所有表包含的字段名
select * from sys.columns
查詢所有字段說(shuō)明
select * from sys.extended_properties
根據(jù)表查詢所含字段說(shuō)明
SELECT t.[name] AS 表名,c.[name] AS 字段名,cast(ep.[value] as varchar(100)) AS [字段說(shuō)明] 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'--------表名
創(chuàng)建視圖
create view ViewName (字段1,字段2) as ( select 字段1, 字段2 from [table] )
數(shù)據(jù)字典
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 數(shù)據(jù)類型 ,t.Fieldlength 長(zhǎng)度,t.Scale 精確度 ,case t.FieldNullable when 0 then '不為空' when 1 then '' end as 說(shuō)明 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, 表說(shuō)明=case when a.colorder=1 then isnull(f.value,'') else '' end, 字段序號(hào)=a.colorder, 字段名=a.name, 標(biāo)識(shí)=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, 占用字節(jié)數(shù)=a.length, 長(zhǎng)度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小數(shù)位數(shù)=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允許空=case when a.isnullable=1 then '√'else '' end, 默認(rèn)值=isnull(e.text,''), 字段說(shuō)明=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
到此這篇關(guān)于MSSQL基本語(yǔ)法及實(shí)例操作語(yǔ)句的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持腳本之家。
相關(guān)文章
sql server使用公用表表達(dá)式CTE通過(guò)遞歸方式編寫通用函數(shù)自動(dòng)生成連續(xù)數(shù)字和日期
CTE是在內(nèi)存中準(zhǔn)備好數(shù)據(jù),而不是每次一條往返服務(wù)器和客戶端一次。如果需要再插入到臨時(shí)表的話就是全部數(shù)據(jù)一次性插入。 這篇文章主要介紹了sql server使用公用表表達(dá)式CTE通過(guò)遞歸方式編寫通用函數(shù)自動(dòng)生成連續(xù)數(shù)字和日期 ,需要的朋友可以參考下2019-07-07distinct 多列問(wèn)題結(jié)合group by的解決方法
distinct 多列問(wèn)題 group by 解決2010-06-06存儲(chǔ)過(guò)程配合UpdateDaset方法批量插入Dataset數(shù)據(jù)實(shí)現(xiàn)代碼
用存儲(chǔ)過(guò)程配合UpdateDaset方法批量插入Dataset數(shù)據(jù),感興趣的你可不要錯(cuò)過(guò)了哈,希望本文提供知識(shí)點(diǎn)可以幫助到你2013-02-02利用SQL Server數(shù)據(jù)庫(kù)郵件服務(wù)實(shí)現(xiàn)監(jiān)控和預(yù)警
這篇文章主要介紹了利用數(shù)據(jù)庫(kù)郵件服務(wù)實(shí)現(xiàn)監(jiān)控和預(yù)警,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2016-10-10在SQL Server中查詢資料庫(kù)的TABLE數(shù)量與名稱的sql語(yǔ)句
這篇文章主要介紹了在SQL Server中查詢資料庫(kù)的TABLE數(shù)量與名稱的sql語(yǔ)句,需要的朋友可以參考下2014-04-04SQL Server 數(shù)據(jù)庫(kù)分離與附加(圖文教程)
SQL Server 數(shù)據(jù)庫(kù)分離與附加(圖文教程),需要的朋友可以參考一下2013-05-05揭秘SQL Server 2014有哪些新特性(1)-內(nèi)存數(shù)據(jù)庫(kù)
微軟SQL Server 2014提供了眾多激動(dòng)人心的新功能,但其中最讓人期待的特性之一就是代號(hào)為” Hekaton”的內(nèi)存數(shù)據(jù)庫(kù)了,內(nèi)存數(shù)據(jù)庫(kù)特性并不是SQL Server的替代,而是適應(yīng)時(shí)代的補(bǔ)充,現(xiàn)在SQL Server具備了將數(shù)據(jù)表完整存入內(nèi)存的功能。那么今天我們就先來(lái)看看內(nèi)存數(shù)據(jù)庫(kù)2014-08-08