T-SQL Summary

SQL is an ANSI standard. Each database vendor develops its own database and language according to this standard. SQL Server is called T-SQL. Oracle's called PL/SQL.

A little summary of some of the commonly used T-SQL some things, think of where to write, the great God do not spray.

Function

1. Mathematical Functions

　　　Min (COLUMN): Minimum value-aggregate function;

Max (COLUMN): maximum-aggregate function;

AVG (COLUMN): Average--aggregate function;

SUM (COLUMN): Sum--aggregate function;

COUNT (COLUMN): Total-aggregate function;

ABS (X): absolute value;

SQRT (X): square root;

POWER (x, y): prescribe;

EXP (X): exponent;

Log (x, y): logarithmic;

Sign (X): Find the symbol;

RAND ([0]): Returns the random float value between 0~1, automatically outputs random values when the parameter is empty, and returns the same value as the same parameter;

Floor (x): Returns the largest integer less than or equal to the X value;

CEILING (x): Returns the smallest integer greater than or equal to the x value;

ROUND (x,length): Rounding function, length is positive, then the X decimal place rounding, length is negative, the X decimal points to the left of the length of the rounding, if length is both negative and its absolute value is greater than the number of x integer part, the function value is 0;

　　 2. String Functions

　　　Lower (XXX): Converts a string to lowercase;

UPPER (XXX): Converts a string to lowercase;

LTRIM (XXX): go short lattice;

RTRIM (XXX): Go to the right space;

Left (Xxx,y): Returns the specified number of characters from the beginning of the total string;

Right (Xxx,y): Returns the specified number of characters from the beginning of the string;

LEN (XXX): Returns the number of string characters;

Datalength (XXX): Returns the length of bytes passed;

SUBSTRING (XXX, Y, Z): Returns a string of Z from the left Y of the string

Replace (XXX,YYY,ZZZ): Replaces all YYY characters appearing in XXX with ZZZ;

STUFF (XXX, Y, Z, MMM): delete xxx Specify position Y start specifying length Z string and insert MMM at specified position;

REVERSE (XXX): Reverses the order of the characters of the specified string;

　　 3. Conversion Functions

Cast (xxx as DATATYPE): convert XXX to other data types;

Convert (datatypeb,xxx): Convert XXX to Datatypeb

4. Conversion Functions

GETDATE (): Gets the current time;

--Get the current year date YY representative years, MM for the month, DD on behalf of the day, HH for the time, the SS represents the second

Year YY 1753--9999

Quarter QQ 1--4

month mm 1--12

Day of the year dy 1--366

Day DD 1--31

Week wk 1--53

Weekday DW 1--7 (Sunday--saturday)

Hour HH 0--23

Minute Mi 0--59

Second SS 0--59

Milisecond Ms 0--999

DATEPART (Qq,getdate ()): Returns the current QQ (quarter-integer);
Datename (Qq,getdate ()): Returns the current QQ (quarter-character);

DATEDIFF (DD, ' 2011-7-7 ', GETDATE ()): To obtain two periods of time apart from the years yy, a few months mm, a few days DD;

DATEADD (Mm,-2,getdate ()): Add or subtract month and day from the current time

T-SQL Summary