aggregate functions, mathematical functions, date-time functions

Ggregate Functions (Transact-SQL) aggregation functions

Avg: Averaging points
Count: Number of calculations
Max: Ask for maximum value
Min: Find minimum value
Sum: Sum

Find Average height
Select AVG (Shengao) from student
As: Added column name
Select AVG (Shengao) as average height from student

Find the number
Select COUNT (*) from student where Xingbie = ' Male '

Ask for maximum
Select MAX (Shengao) from student

To find the minimum value
Select MIN (Shengao) from student

Sum
Select SUM (Shengao) from student

Using aggregate functions in conjunction with grouping, each group is calculated and displayed separately
Having: The number of screening more than 6
Select COUNT (*), AVG (Shengao) class from Xueshengxinxibiao Group by class
Having Count (*) >6

Mathematical functions
ABS: Take absolute value
Select ABS (-1)

CEILING: Ceiling, as long as the value is not 0 after the decimal point, will be in a rounding
Select CEILING (1.0)
Select CEILING (1.1)

Floor: Take an integer before the decimal point, and the top counter
Select Floor (1.9)
Select Floor (2.0)

Pi: Pi
Select PI ()

Round:4 5 In, there is a parameter behind, identify to the small tree point behind several
Select Round (3.5437,2)
Select Round (3.5474,2)

Rand: Random Meaning
Select rand ()

SQRT: Finding the square root
Select sqrt (16)

Square: Ask for the square
Select Square (4)

Print: Output meaning, output to message box

Date-time functions
Select ' 1990-09-09 '

DateAdd: Go to a date and time, add a certain amount of time

Plus one year
Select DATEADD (year,1, ' 1990-09-09 ')
Plus month
Select DATEADD (month,1, ' 2014-03-31 ')

DateDiff: Take the time difference
A few years.
Select DateDiff (year, ' 2011-09-22 ', ' 2014-01-01 ')
A few months.
Select DateDiff (Month, ' 2011-09-22 ', ' 2014-01-01 ')
A few days.
Select DateDiff (Day, ' 2011-09-22 ', ' 2014-01-01 ')
A few weeks.
Select DateDiff (Week, ' 2011-09-22 ', ' 2014-01-01 ')

Datename: Returns the day of the month or minute, or the week, or weeks, of a date and time.
Select Datename (year, ' 2014-11-25 ')
Select Datename (Week, ' 2014-11-25 ') "is displayed as the 48th week of the Year"
Select Datename (Weekday, ' 2014-11-25 ') "Show Tuesday"

DatePart: Takes an integer of the specified segment
Select DATEPART (year, ' 2014-11-25 ') "Show 2014"
Select DATEPART (week, ' 2014-11-25 ') "Show 48"
Select DATEPART (Weekday, ' 2014-11-25 ') "Show 3"

Day: Returns the number of days of the month
Select Day (' 2014-11-25 ') "Show 25"

GETDATE: Return database service time
Select GETDATE ()

IsDate: Determines whether a datetime is correct, returns one correctly, returns zero incorrectly
Select IsDate (' 2014-02-12 ')

Month: Get months
Select month (' 2014-08-22 ')
Year: Get Years
Select year (' 2014-08-22 ')

Sysdatetime: Get the computer's system time
Select Sysdatetime ()

Aggregate function, mathematical function, datetime function