SQL Server T-SQL functions

An aggregate function (statistical function)!!!!!

Number of Counts count ()

Select COUNT (*) from table name

Sum sum ()

Select SUM (column name) from table name

Averaging avg ()

Select AVG (column name) from table name

Max ()

Select Max (column name) from table name

Minimum min ()

Select min (column name) from table name

Two. Math function: Manipulate one data and return a result

Upper limit ceiling ()

Select Code,name,ceiling (price) from car; --The column name disappears after the value operation is used as an alias

Lower floor ()

Select Floor, from car

Absolute ABS ()

Rounding round (A, B)-- A is the required value or column, and B is the number of digits left after the decimal point

Select round (oil, 0) from car--the decimal place depends only on the number of digits followed by whether or not a rounding is required, not additive

Pi (),- -no need to add anything in parentheses

Round (PI (), 2)

Open square root SQRT ()

Squared, multiply yourself Square ()

Three. String Function!!!

Convert Capital Upper ()

Select Upper (pic) from car;

Convert lowercase lower ()

Go to left Space LTrim () --a function with no spaces on both sides

Go right space RTrim ()

Intercept substring (A, B, C)----a operation target object B index (index starting at 1) C number of truncation number of bits

Left (, b)---intercept the B-bit

Right (, B)---intercept the B-bit

Length len ()

Select Len (' aaaaaa '); return several lengths

Replaces replace (A, B, C)----a operation target object B to be replaced by the C to be replaced by

Select replace (' aaaaabbaaaaa ', ' BB ', ' haha '); replace the BB in the first string with the haha

Flip Reverse ()

Select reverse (' abc '); The result is CBA

Four conversion function --in database +: string concatenation, value type mathematical operation

1 convert (A, b)----a type B column/value to be converted to

convert (nvarchar), oil)

2 cast (A as B) ---- a column/value B type to convert to

Cast (oil as nvarchar (10))

Five. Time-Date function

Gets the current system time GetDate ()----sysdatetime () Gets the timestamp of the database service Selec T getdate ()

Date of year ()

month () --No seconds

Day ()

Select year (' 1999-1-1 ')

Select Month (GetDate ())

Determine if the date is correct isdate () Select IsDate (' 2000-2-31 ') ----return bit type , False is 0,true is 1

Add Time DateAdd (A, B, D)----a add what type (year,month,day) B plus how many C to who add

Select DATEADD (year,5, ' 2000-1-1 ')

Returns the day of the Week Datename (weekday, time)-The returned value is the same as the string datepart can return a few weeks, but the type of int is returned

Select Datename (Weekday, ' 2000-1-1 ')

Datename (week, time)--weeks

Datename (day, time)-the day of the month

Datename (DayOfYear, Time)--the first day of the year

SQL Server T-SQL functions