Database basic knowledge collation, common functions and common grammar

1 Common Database Aggregation functions
Max ()
Min ()
SUM ()
AVG ()
Count ()


2 string processing functions
Len () differs from datalength (): Len is the return character length datalength is the return byte length
LTrim () RTrim () Trim ()
IsNull (@FilterStr, N ") If space-time replaces it

CHARINDEX (N '; ', @TmpList) returns the starting position of an expression in a string instead of index
Paitndex ('%ssd% ', @temp) is basically similar to the CHARINDEX function

SUBSTRING (@TmpList, 1, @Index-1)

Replace (' string ', the character to replace ', ' replace the character ')
Uppre and Lower functions
Left (,) and right (,) take the number of characters before and after how many


3 Numeric functions
Ceiling () Take the whole big floor () take the whole take small
ABS () absolute value
Round (,) rounding
Square () squared
sqrt () Open radical
RAND () random value


4th-Period function
GETDATE () Get system time
DATEADD (time unit, plus minus variable, date) Date of addition and subtraction
DateDiff (Time unit, date, date) difference between two dates
DATEPART (time unit, date) remove the specified part of the date
Datename (time unit, date) Same as DatePart function
IsDate () determines whether to return 0 or 1 for the expected format

Time Unit format
YEAR,YY,YYYY (year)
QUARTER,MM,MM (month)
Dayofyear,dy, Y (Day of the week)
Day,dd,d (date)
WEEK,WK,WW (Day of the week)
Hour,hh (Hours)
Minute,mi,n (min)
Second,ss,s (SEC)
Millisecond,ms (MS)

5 Common Database syntax

Over () This function is an analytic function or a window function can be used in the background to do some operation examples:
Row_number () over (Partition by Group field order by sort field) to generate line numbers after grouping sorting

SUM () over (the Partition by Group field), for example, after grouping a column to add total

Sum,avg,count,min,max using Over (PARTITION by) statements


Returns the result filter substitution syntax
Case if RowNum > 1 then totalmeasuretime else 0 end
Case RowNum while >1 then totalmeasuretime Elee 0 End


Union merge two query results two query results must have the same column union All is all results including duplicates

Database basic knowledge collation, common functions and common grammar