Sql-server Database Statement Learning

View all database names

Select  from Order  by name;

View all tables of the current data

Select  from where = ' U '  ORDER  by

Querying data structures for all tables (best of all)

SELECT( Case  whenA.colorder=1  ThenD.nameElse NULL End) Table name, A.colorder field ordinal, a.name field name, ( Case  when ColumnProperty(A.id,a.name,'isidentity')=1  Then '√'Else "' End) identification, ( Case  when(SELECT Count(*) fromsysobjectsWHERE(Nameinch(SELECTName fromsysindexesWHERE(ID=a.ID) and(indidinch  (SELECTIndid fromSysindexkeysWHERE(ID=a.ID) and(colidinch  (SELECTColid fromsyscolumnsWHERE(ID=a.ID) and(Name=( a.name) ))))) and(xtype= 'PK'))>0  Then '√' Else "' End) primary key, B.name type, A.length takes up the number of bytes,ColumnProperty(A.id,a.name,'PRECISION') aslength,IsNull(ColumnProperty(A.id,a.name,' Scale'),0) asNumber of decimal digits, ( Case  whenA.isnullable=1  Then '√'Else "' End) allows null,IsNull(E.text,"') Default value,IsNull(g.[value],' ') as [Description] fromsyscolumns a Left JoinSystypes b onA.xtype=B.xusertypeInner Joinsysobjects D ona.ID=D.id andD.xtype='U'  andD.name<>'dtproperties'  Left Joinsyscomments E onA.cdefault=e.id Left JoinSys.extended_properties g ona.ID=g.major_id andA.colid=g.minor_id Left JoinSys.extended_properties F onD.id=F.class andf.minor_id=0whereB.name is  not NULL--where d.name= ' table to query '--if you only query the specified table, add this conditionOrder  byA.id,a.colorder

Other operations conform to the SQL standard:

 --Library operations
Create DATABASE test;
Use test;

--Table operations
Drop TableUsers;Create TableUsers (IDint, usernameChar( -), passwordChar( -), remarksChar( -));

--Data manipulation Insert  intoUsersValues(1,'Admin','admin123','Administrator for System');
Update Users set username = ' king ', password = ' 123456 ' where username = ' admin ';
Delete from Users where username = ' king ';Select *  fromUsers;

Note: Strings need to be surrounded by single quotes, and if the name you enter contains spaces, you can use double quotes to indicate.

Other operations:

Sort by the first field

Select *  from Order  by 1;

Query the length of all data username fields

Select LEN  from Users;

Sql-server Database Statement Learning