SQL Server Learning--1

1. Remote Login mode

2. Query the first 100 data

Select top * FROM [dbo]. [Flow_share_day_tax]

3. Get the system time

SELECT GETDATE () as ' current time '

Get other Time:

DATEPART () returns an integer representing the specified date part of the specified date

SELECT DATEPART (year, GETDATE ()) as ' years '
SELECT DATEPART (month, GETDATE ()) as ' month '
SELECT DATEPART (Day, GETDATE ()) as ' days '
SELECT DATEPART (DW, GETDATE ()) as ' Week '
SELECT DATEPART (WEEK, GETDATE ()) as ' Week '
SELECT DATEPART (HOUR, GETDATE ()) as ' hour '
SELECT DATEPART (MINUTE, GETDATE ()) as ' min '
SELECT DATEPART (SECOND, GETDATE ()) as ' s '

or

Datename () returns a string representing the specified date part of the specified date

SELECT Datename (Year,getdate ()) as ' year '
SELECT Datename (Month,getdate ()) as ' month '
SELECT Datename (Day,getdate ()) as ' Day '
SELECT Datename (Dw,getdate ()) as ' Week '
SELECT Datename (Week,getdate ()) as ' Week '
SELECT Datename (Hour,getdate ()) as ' hour '
SELECT Datename (Minute,getdate ()) as ' min '
SELECT Datename (Second,getdate ()) as ' s '

Compare:

The DATEPART and DATENAME functions give the specified portion of a datetime value (year, quarter, day, hour, and so on) to an integer value or an ASCII string. Since smalldatetime can only be accurate to minutes,

So when you use the smalldatetime value in both functions, the seconds and milliseconds that are returned are always zero.

Other functions on date and time:

4. View table structure

(1) can only be a table, can not add the mode name. Otherwise, the error

(2) can only be a table, can not add the mode name. Otherwise, the error

5. View the Build Table statement

6.IDENTITY () function

[PID] [decimal] (0IDENTITY(1,1not forREPLICATION  notNULL

IDENTITY (+): Indicates starting from 1, self-increment +1

7. Querying all table names in the database

SELECT  from Where XType='U'ORDER by Name

8. Table Structure Query

SELECT Case when Col.colorder = 1 then obj.name ELSE ' END as table name,

Col.colorder asserial number, Col.name ascolumn name,ISNULL(ep.[value],"') ascolumn description, T.name asdata type, Col.length aslength,ISNULL(ColumnProperty(Col.id, Col.name,' Scale'),0) asNumber of decimal digits, Case  when ColumnProperty(Col.id, Col.name,'isidentity')= 1  Then '1'ELSE "' END  asidentification, Case  when EXISTS(SELECT 1                            fromdbo.sysindexes siINNER JOINDbo.sysindexkeys Sik onSi.id=sik.id andSi.indid=Sik.indidINNER JOINDbo.syscolumns SC onSc.id=sik.id andSc.colid=Sik.colidINNER JOINDbo.sysobjects so onSo.name=Si.name andSo.xtype= 'PK'                           WHERESc.id=col.id andSc.colid=Col.colid
) Then '1'ELSE "'END  asprimary KEY, Case  whenCol.isnullable= 1  Then '1'ELSE "'END  asallow NULL,ISNULL(Comm.text,"') asDefault Value fromDbo.syscolumns Col Left  JOINDbo.systypes T onCol.xtype=T.xusertypeInner JOINDbo.sysobjects obj onCol.id=obj.id andObj.xtype= 'U'  andObj.status>= 0         Left  JOINDbo.syscomments Comm onCol.cdefault=comm.id Left  JOINSys.extended_properties EP onCol.id=ep.major_id andCol.colid=ep.minor_id andEp.name= 'ms_description'         Left  JOINSys.extended_properties Eptwo onObj.id=eptwo.major_id andeptwo.minor_id= 0  andEptwo.name= 'ms_description'WHEREObj.name= 'Flow_share_day_tax'--Table nameORDER  byCol.colorder;

SQL Server Learning--1