first, System functions
1. Convert (type [length], expression [, style])
2.isnull (expression, default)
character functions:
len () length
LTrim () |rtrim () remove left and right spaces
Right () |left () intercepts the character
substring (); intercept by position and length
Date Function:
DATEADD () date plus operation
DateDiff () Two date difference
getdate () gets the current system date
datepart () get part of date
second, fuzzy query
Like ' pattern ' can only be used in text type data
%: Any character of any length
_: One character of any character
[]: Any character in the specified range [Yang Qing] [0-9][^a-z]
CHECK Stuno like ' [0-9][0-9][0-9] ' 001 002 101
Range:
between ... And ... Equivalent to and
list: in
field in (constant 1, constant 2) is equivalent to or
three, aggregation function;
1.sum (column name)
2.avg (column name)
3.max (column name) min (column name)
4.count (column name [*])
Iv. Grouping
SELECT column name, aggregate function () from table name
Where Condition
Group By column name
Having conditions
Order BY column name
columns after 1.SELECT must be available after group by
2. Order: where->group by->having->order by
SQL Server fuzzy query, grouping