Common SQL functions

SQL functions

Aggregation functions (for numeric columns):

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

       Math function ():
  ABS:     Absolute Value
   CEILING: Upper bounds rounding
  floor:  rounding  
  pi:     Pi
   round:  The first parameter is the number to be rounded, and the second parameter is a few
  rand () to be rounded to the decimal point: randomly generated
  sqrt:   Find square root, open square,
  square: squared
 
       datetime function:
   dateadd:    Add
  datediff:   take time difference  
  datename:    Returns the date and time of the month or minute, or week, week, or weeks
  datepart:   takes an integer of the specified field
  YEAR:        return year
  month:      number of months to return year
   day:        returns the number of days of the month
  isdate:     determines the date time
  getdate:    return database service time
  sysdatetime: Get the computer's system time

After the function, add as to the function column alias such as: as Alias

Application of aggregation functions:

---------three boys are a few more than Class two.
Select (select COUNT (*) from Zuoye where xingbing= ' man ' and banji= ' three shifts ')

---------combine grouping using aggregate functions, each group is calculated and displayed, having a filter
Select COUNT (*), AVG (Shengao), Banji from Zuoye Group by Banji have COUNT (*) >3

---------Find out all the information in each class that is taller than the average student in this class.
Select*from Zuoye as a where shengao> (select AVG (Shengao) from Zuoye as B where B.banji=a.banji)

---------query for the tallest person
Select MAX (Shengao) from Zuoye

---------find the person with the shortest height
Select MIN (Shengao) from Zuoye

---------Query the height of all people and
Select SUM (Shengao) from Zuoye

Mathematical functions:

---------Absolute Value
Select ABS (-1)

---------Ceiling Upper Limit rounding
Select CEILING (1.9)
Select CEILING (1.1)

---------Floor Rounding
Select Floor (1.1)
Select Floor (1.9)

---------Pi
Select Pi ()

---------round the first parameter is the number to be rounded, and the second argument is to be rounded to the decimal point after a few
Select Round (3.597,2)

---------rand () randomly generated
Select RAND ()

---------sqrt square root, open radical
Select sqrt (16)

Square---------Square
Select Square (4)

---------to take the upper limit after the square root of height and then equal to 14 of the information displayed by the students
Select *from Zuoye where Ceiling (SQRT (Shengao)) =14
Select Xingming,shengao,floor (SQRT (Shengao)) from Zuoye where ceiling (SQRT (Shengao)) =14

Date function:

---------DATEADD Add
Select DATEADD (year,1, ' 20000229 ')--Gagnin
Select DATEADD (month,1, ' 20000331 ')--plus month
Select DATEADD (day,1, ' 20000229 ')--Gatian

---------DateDiff Take time difference
Select DATEDIFF (year, ' 20111211 ', ' 20141116 ')--the difference of several years
Select DATEDIFF (Month, ' 20111211 ', ' 20141116 ')--months of difference
Select DATEDIFF (Day, ' 20111211 ', ' 20141116 ')--Days of difference
Select DATEDIFF (Week, ' 20111211 ', ' 20141116 ')--Week of difference

---------Datename Returns the date and time of the day, minute or minute, or week, week
Select Datename (year, ' 20261211 ')
Select Datename (Week, ' 20261211 ')
Select Datename (Weekday, ' 20261211 ')

---------datepart The integer of the specified field
Select DATEPART (year, ' 20261211 ')
Select DATEPART (WEEK, ' 20261211 ')
Select DATEPART (WEEKDAY, ' 20261211 ')

---------Year returns
Select year (' 20141125 ')

The number of months---------month that returns the year
Select month (' 20141125 ')

---------Day Returns the number of days of the month
Select Day (' 20141125 ')

---------getdate return database service time
Select GETDATE ()

---------getdate return database service time
Select GETDATE ()

---------IsDate judgment Date and time
Select IsDate (' 20140216 ')

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

---------Show people born in 1988
Select *from Zuoye where year (Shengri) =1998

---------Show everyone's name and birthday.

Select Xingming,year (Shengri) from Zuoye

Common SQL functions