Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MSSQL basic grammar case analysis", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MSSQL basic grammar case analysis" bar!

Refresh local cache Ctrl+Shift+R query select * from [table] modify 1, normal update UPDATE [table] set [field] = [values] 2, associated table update UPDATE A SET A1 = B1, A2 = B2, A3 = B3 FROM A, B WHERE A.ID = B.ID delete (data) delete [table] delete (table) drop [table] conditional query select * from [table] where [field] = [values] transaction

1 start transaction: begin transaction

2 commit transaction: commit transaction

3 Roll back the transaction: rollback transaction

4 end transaction: commit or roll back the transaction will end the transaction

/ *-- start a transaction-- * / begin transactiondeclare @ errorSum int-- define variables Used to accumulate errors during transaction execution / *-- transfer money-- * / update bank set currentMoney=currentMoney-800 where customerName=' Zhang San 'set @ errorSum=@errorSum+@@error-- accumulate whether there are errors update bank set currentMoney=currentMoney+800 where customerName=' Li Si' set @ errorSum=@errorSum+@@error-- accumulate whether there are errors print 'check the balance during the transfer transaction' select * from bank/*-- according to whether there are errors Determine whether the transaction is committed or rolled back-- * / if @ errorSum > 0 begin print 'transaction failed, rollback transaction.' Rollback transaction endelse begin print 'transaction succeeded, commit transaction, write to hard disk, save permanently!' / *-- commit and end the transaction-- * / commit transaction endgoprint 'View the balance after transfer' select * from bankgo query all table names select * from sys.tables query all table field names select * from sys.columns query all field description select * from sys.extended_properties according to the table query field description SELECT t. [name] AS table name, c. [name] AS field name Cast (ep.[ value] as varchar) AS [field description] FROM sys.tables AS tINNER JOIN sys.columns AS c ON t.object_id = c.object_idLEFT JOIN sys.extended_properties AS epON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class = 1AND t.nameplate / table name create view create view ViewName (field 1 Field 2) as (select field 1, field 2 from [table]) data dictionary SELECT t.FieldExp name, t.ColumnName code Case t.FieldDataTypewhen 'bigint' then t.FieldDataTypewhen' int' then t.FieldDataTypewhen 'datetime' then t.FieldDataTypewhen' numeric' then t.FieldDataTypewhen 'nvarchar' then (cast (t.FieldDataType as varchar)) +' ('+ cast (t.Fieldlength as varchar) +') when 'decimal' then (cast (t.FieldDataType as varchar)) +' ('+ cast (t.Fieldlength as varchar)) +','+ cast (t.Scale as varchar (100)) +') end as data type T.Fieldlength length, t.Scale accuracy, case t.FieldNullablewhen 0 then 'not empty' when 1 then''end as description from (select colorder=C.column_id, FieldExp=ISNULL (PFD. [value], Null'), ColumnName=C.name, FieldDataType=T.name, Fieldlength=COLUMNPROPERTY (c.object_id, c.name, 'PRECISION'), Scale=C.scale FieldNullable=C.is_nullable--FieldDefVal=D.definitionFROM sys.columns C INNER JOIN sys.objects O ON C. [object _ id] = O.[ object _ id] AND (O.typewritten examples U' or O.typewritten examples) 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 dating 'END IndexName=IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX. [object _ id] = [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.nameqlashi') as t ORDER BY colorder,ColumnNameSELECT table name = case when a.colorder=1 then d.name else''end, table description = case when a.colorder=1 then isnull (f. ValueCool') else''end Field serial number = a.colorder, field name = a.name, identity = case when COLUMNPROPERTY (a.idSELECT indid FROM sysindexkeys WHERE id= a.id AND colid=a.colid a. Namefilm IsIdentity') = 1 then '√' else''end, primary key = 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, type = b.name, occupied bytes = a.length, length = COLUMNPROPERTY (a.id) A.nameforce else else'), decimal places = isnull (COLUMNPROPERTY (a.idgramma a.namememe else'), 0), null = isnull 'else' 'end, field description = 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.xtypewritten tables U' and d.nameplates 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.nametables' tables to be queried'- if only specified tables are queried Plus this condition, order by a. ID. A. Colorder. Thank you for your reading. The above is the content of "MSSQL basic grammar case analysis". After the study of this article, I believe you have a deeper understanding of the problem of MSSQL basic grammar case analysis, and the specific use needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.