SQL数字类型

SQL数字数据类型包括整数,固定点类型和浮点类型。

Integer Types: small, 整数, big

整数类型的值范围如下:

类型 最低 最高

small

-32768

32767

整数

-2147483648

2147483647

big

-9223372036854775808

9223372036854775807

Fixed Point Types: NUMERIC, DECIMAL

The DECIMAL data type is an alias for the NUMERIC data type. For a NUMERIC fixed point type, the precision and scale are usually specified using the following syntax:

NUMERIC [ (precision [,scale]) ]

DECIMAL [ (precision [,scale]) ]

在哪里

  • precision 是数字中的数字数。

  • scale 是数字中小数点右侧的数字数。

如果未指定精度,则默认为18.如果未指定缩放,则默认为0.如果要指定缩放,则还必须指定精度。例如,声明的列:

DECIMAL(6,4)

has values with 6 digits, 4 of which are after the decimal point, such as 12.3456.

Limits for fixed point types are determined by the specified precision and scale. Conversions from values with a larger scale to values with a smaller scale are performed through rounding (i.e. a least significat digit of 5 or more is rounded up). Conversions from values with a larger integral part (i.e. digits before the decimal point) to values with smaller integral part result in an overflow error. For example:

SQL> select cast(12.345 as numeric(4,2)) from dual;
12.35

SQL> select cast(12.345 as numeric(3,2)) from dual;
Numeric Overflow: 12.35 does not fit in precision scale (3,2)

数字类型的最大精度为38.如果使用大于38的精度定义了数值数据类型,则返回错误。如果数字/十进制类型的任何操作会产生具有大于38的精度的数字,则返回算术溢出。

浮点类型: 双倍的

NuoDB supports 双倍的 as a floating type.

The following table shows the range of values for 双倍的:

类型 最低 最高

双倍的

-1.7976931348623157e + 308

-2.2250738585072014e-308

2.2250738585072014e-308

1.7976931348623157e + 308

About NaN, Inf, -Inf

NuoSQL supports NaN, Inf, -Inf, and Null as values for 双倍的. There is no concept of -Nan. The order of values with regard to ORDER BY, from lowest to highest, is Null, Nan, -Inf, Inf. When comparing (that is, if Nan or Null is greater than or less than any other value) the comparison always returns false. For example:

选择 * FROM t2;

   S1
 ------
 -inf
 nan
 20
 0
 999.99
 inf
 -1000

选择 * FROM t2 WHERE s1>0;

   S1
 ------
 20
 999.99
 inf

NuoDB treats FLOAT values the same as 双倍的. Specifying (float)Nan is the same as specifying (double)Nan.

Aggregate functions supported by NuoDB include COUNT, AVG and SUM. These functions work with NaN, Inf, and -Inf as shown in the following table. COUNT works as expected. AVG and SUM report values as described in the following table, based on the kind of values specified as column values.

列中的值 报告的价值

Nan

NaN

Inf

Inf

-Inf

-Inf

Any combination of NaN, Inf, or -Inf

NaN