SQL Numeric Data Type & Functions

Data type - Description

BIT(_size_) - A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.

TINYINT(_size_) - A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255)

BOOL - Zero is considered as false, nonzero values are considered as true.

BOOLEAN - Equal to BOOL

SMALLINT(_size_) - A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255)

MEDIUMINT(_size_) - A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255)

INT(_size_) - A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)

INTEGER(_size_) - Equal to INT(size)

BIGINT(_size_) - A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255)

FLOAT(_size_, _d_) - A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions

FLOAT(_p_) - A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE()

DOUBLE(_size_, _d_) - A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter

DECIMAL(_size_, _d_) - An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.

DEC(_size_, _d_) - Equal to DECIMAL(size,d)

Function - Description

ABS - Returns the absolute value of a number

ACOS - Returns the arc cosine of a number

ASIN - Returns the arc sine of a number

ATAN - Returns the arc tangent of one or two numbers

ATAN2 - Returns the arc tangent of two numbers

AVG - Returns the average value of an expression

CEIL - Returns the smallest integer value that is >= to a number

CEILING - Returns the smallest integer value that is >= to a number

COS - Returns the cosine of a number

COT - Returns the cotangent of a number

COUNT - Returns the number of records returned by a select query

DEGREES - Converts a value in radians to degrees

DIV - Used for integer division

EXP - Returns e raised to the power of a specified number

FLOOR - Returns the largest integer value that is <= to a number

GREATEST - Returns the greatest value of the list of arguments

LEAST - Returns the smallest value of the list of arguments

LN - Returns the natural logarithm of a number

LOG - Returns the natural logarithm of a number, or the logarithm of a number to a specified base

LOG10 - Returns the natural logarithm of a number to base 10

LOG2 - Returns the natural logarithm of a number to base 2

MAX - Returns the maximum value in a set of values

MIN - Returns the minimum value in a set of values

MOD - Returns the remainder of a number divided by another number

PI - Returns the value of PI

POW - Returns the value of a number raised to the power of another number

POWER - Returns the value of a number raised to the power of another number

RADIANS - Converts a degree value into radians

RAND - Returns a random number

ROUND - Rounds a number to a specified number of decimal places

SIGN - Returns the sign of a number

SIN - Returns the sine of a number

SQRT - Returns the square root of a number

SUM - Calculates the sum of a set of values

TAN - Returns the tangent of a number

TRUNCATE - Truncates a number to the specified number of decimal places