----Arithmetic Functions ----
/***Trigonometric function***/
SIN(float_expression) -- Returns the sine of the angle represented in radians
COS(float_expression) --Returns the cosine of the angle represented in radians
TAN(float_expression) -- Returns the tangent of the angle expressed in radians
COT(float_expression) -- Returns the co-cut of the angle expressed in radians
/***Inverse trigonometric function***/
ASIN(float_expression) -- Returns the angle represented in radians of the sine of the FLOAT value
ACOS(float_expression) -- Returns the angle expressed in radians of the cosine of the FLOAT value
ATAN(float_expression) --Returns the angle expressed in radians of the tangent of the FLOAT value
ATAN2(float_expression1,float_expression2)
--Return tangent is the angle expressed in radians of float_expression1/float_expression2
DEGREES(numeric_expression)
-- Convert radians to angles to return the same data type as the expression can be
--INTEGER/MONEY/REAL/FLOAT Type
RADIANS(numeric_expression)--Convert angle to radians to return the same data type as the expression can be
--INTEGER/MONEY/REAL/FLOAT Type
EXP(float_expression) -- Returns the exponential value of the expression
LOG(float_expression) --Returns the natural logarithmic value of the expression
LOG10(float_expression)--Returns the logarithmic value of the expression with base 10
SQRT(float_expression) -- Returns the square root of the expression
/***Approximate value function***/
CEILING(numeric_expression) --Return >=The minimum integer of the expression returns the same data type as the expression.
--INTEGER/MONEY/REAL/FLOAT Type
FLOOR(numeric_expression) -- Return the minimum integer of <= expression. The data type returned is the same as the expression.
--INTEGER/MONEY/REAL/FLOAT Type
ROUND(numeric_expression) --Return as integer_expression
Data returned for rounded values of precision
--The same type as the expression can be of INTEGER/MONEY/REAL/FLOAT type
ABS(numeric_expression) -- Return the absolute value of the expression The data type returned is the same as the expression.
--INTEGER/MONEY/REAL/FLOAT Type
SIGN(numeric_expression)--The positive and negative sign of the test parameter returns 0, zero value 1, positive or -1, data type returned by negative number
--The same as the expression can be of type INTEGER/MONEY/REAL/FLOAT
PI() --The return value is π, i.e. 3.1415926535897936
RAND([integer_expression]) -- Using optional [integer_expression] as seeds is worth 0-1
Random floating point numbers between
----String function ----
ASCII() -- The function returns the ASCII code value of the leftmost character of the character expression
CHAR() -- function is used to convert ASCII code into characters
-- If there is no ASCII code value between 0 ~ 255, the CHAR function will return a NULL value
LOWER() -- The function converts all strings to lowercase
UPPER() -- The function converts all strings to uppercase
STR() -- The function converts numerical data into character data
LTRIM() -- The function removes the spaces at the head of the string
RTRIM() -- The function removes the space at the end of the string
LEFT(),RIGHT(),SUBSTRING() -- function returns part of string
CHARINDEX(),PATINDEX()--function returns the start position of the occurrence of a specified substring in the string
SOUNDEX() -- The function returns a four-digit character code
--The SOUNDEX function can be used to find strings with similar sounds, but the SOUNDEX function only returns 0 for both numbers and Chinese characters.
DIFFERENCE() -- The function returns the difference in the values of the two character expressions returned by the SOUNDEX function
--0 The first character of the return value of the two SOUNDEX functions is different
--1 The first character of the return value of the two SOUNDEX functions is the same
--2 The first and second characters of the return value of the two SOUNDEX functions are the same
--3 The first, second and third characters of the return value of the two SOUNDEX functions are the same
--4 The return values of the two SOUNDEX functions are exactly the same
QUOTENAME() -- The function returns a string enclosed by a specific character
/*select quotename('abc', '{') quotename('abc')
The operation results are as follows
----------------------------------{
{abc} [abc]*/
REPLICATE() -- The function returns a string of repeated character_expression specified times
/*select replicate('abc', 3) replicate( 'abc', -2)
The operation results are as follows
----------- -----------
abcabcabc NULL*/
REVERSE() -- The function reverses the character arrangement order of the specified string
REPLACE() -- The function returns the string that has been replaced with the specified substring
/*select replace('abc123g', '123', 'def')
The operation results are as follows
----------- -----------
abcdefg*/
SPACE() -- function returns a blank string of specified length
STUFF() -- The function replaces the substring of the specified position length with another substring
----Data type conversion function ----
The CAST() function syntax is as follows
CAST() (<expression> AS <data_ type>[ length ])
The CONVERT() function syntax is as follows
CONVERT() (<data_ type>[ length ], <expression> [, style])
select cast(100+99 as char) convert(varchar(12), getdate())
The operation results are as follows
------------------------------ ------------
199 Jan 15 2000
----Date function ----
DAY() -- function returns the date value in date_expression
MONTH() -- function returns the month value in date_expression
YEAR() -- function returns the year value in date_expression
DATEADD(<datepart> ,<number> ,<date>)
--The function returns the new date generated by the specified date plus the specified additional date interval number
DATEDIFF(<datepart> ,<number> ,<date>)
--The function returns the differences between two specified dates in datepart
DATENAME(<datepart> , <date>) --The function returns the specified part of the date as a string
DATEPART(<datepart> , <date>) --The function returns the specified part of the date as an integer value
GETDATE() -- The function returns the current date and time of the system in the default format of DATETIME
----System Functions ----
APP_NAME() -- function returns the name of the currently executing application
COALESCE() -- The function returns the value of the first non-NULL expression among many expressions
COL_LENGTH(<'table_name'>, <'column_name'>) --function returns the length value of the specified field in the table
COL_NAME(<table_id>, <column_id>) -- The function returns the name of the specified field in the table, i.e. the column name
DATALENGTH() -- The function returns the actual length of the data of the data expression
DB_ID(['database_name']) -- The function returns the database number
DB_NAME(database_id) -- function returns the name of the database
HOST_ID() -- The function returns the name of the server computer
HOST_NAME() -- The function returns the name of the server computer
IDENTITY(<data_type>[, seed increment]) [AS column_name])
--IDENTITY() function is only used in the SELECT INTO statement to insert an identity column into the new table
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE()--function determines whether the given expression is a reasonable date
ISNULL(<check_expression>,<replacement_value>)--The function will NULL in the expression
Values are replaced with the specified value
ISNUMERIC() -- The function determines whether the given expression is a reasonable numerical value
NEWID() -- function returns a value of type UNIQUEIDENTIFIER
NULLIF(<expression1>, <expression2>)
--NULLIF function returns NULL when expression1 is equal to expression2
If the values are not equal, the value of expression1 is returned
Reserved words in sql
action add aggregate all
alter after and as
asc avg avg_row_length auto_increment
between bigint bit binary
blob bool both by
cascade case char character
change check checksum column
columns comment constraint create
cross current_date current_time current_timestamp
data database databases date
datetime day day_hour day_minute
day_second dayofmonth dayofweek dayofyear
dec decimal default delayed
delay_key_write delete desc describe
distinct distinctrow double drop
end else escape escaped
enclosed enum explain exists
fields file first float
float4 float8 flush foreign
from for full function
global grant grants group
having heap high_priority hour
hour_minute hour_second hosts identified
ignore in index infile
inner insert insert_id int
integer interval int1 int2
int3 int4 int8 into
if is isam join
key keys kill last_insert_id
leading left length like
lines limit load local
lock logs long longblob
longtext low_priority max max_rows
match mediumblob mediumtext mediumint
middleint min_rows minute minute_second
modify month monthname myisam
natural numeric no not
null on optimize option
optionally or order outer
outfile pack_keys partial password
precision primary procedure process
processlist privileges read real
references reload regexp rename
replace restrict returns revoke
rlike row rows second
select set show shutdown
smallint soname sql_big_tables sql_big_selects
sql_low_priority_updates sql_log_off sql_log_update
sql_select_limit
sql_small_result sql_big_result sql_warnings straight_join
starting status string table
tables temporary terminated text
then time timestamp tinyblob
tinytext tinyint trailing to
type use using unique
unlock unsigned update usage
values varchar variables varying
varbinary with write when
where year year_month zerofill