CUBRID学习笔记 24 数据类型1

最新推荐文章于 2016-03-18 13:47:00 发布

weixin_30919235

最新推荐文章于 2016-03-18 13:47:00 发布

阅读量72

点赞数

原文链接：http://www.cnblogs.com/wang2650/p/5286455.html

版权

---恢复内容开始---

一数字类型注意小数的四舍五入问题

1数字型

Type	Bytes	Mix	Max	Exact/approx.
SHORT SMALLINT	2	-32,768	32,767	exact numeric
INTEGER INT	4	-2,147,483,648	+2,147,483,647	exact numeric
BIGINT	8	-9,223,372,036,854,775,808	+9,223,372,036,854,775,807	exact numeric
NUMERIC DECIMAL	16	precision p: 1 scale s: 0	precision p: 38 scale s: 38	exact numeric
FLOAT REAL	4	-3.402823466E+38 (ANSI/IEEE 754-1985 standard)	+3.402823466E+38 (ANSI/IEEE 754-1985 standard)	approximate numeric floating point : 7
DOUBLE DOUBLE PRECISION	8	-1.7976931348623157E+308 ANSI/IEEE 754-1985 standard)	+1.7976931348623157E+308(ANSI/IEEE 754-1985 standard)	approximate numeric floating point : 15
MONETARY	12	-3.402823466E+38	+3.402823466E+38	approximate numeric

2 bit类型

就是0和1 最大的bit字符串长度1,073,741,823

bit(n)

n must be a number greater than 0.
If the length of the string exceeds n, it is truncated and filled with 0s.
If a bit string smaller than n is stored, the remainder of the string is filled with 0s.

Example

CREATE TABLE bit_tbl(a1 BIT, a2 BIT(1), a3 BIT(8), a4 BIT VARYING);

INSERT INTO bit_tbl VALUES (B'1', B'1', B'1', B'1');

INSERT INTO bit_tbl VALUES (0b1, 0b1, 0b1, 0b1);

INSERT INTO bit_tbl(a3,a4) VALUES (B'1010', B'1010');

INSERT INTO bit_tbl(a3,a4) VALUES (0xaa, 0xaa);

SELECT * FROM bit_tbl;

a1 a2 a3 a4

=========================================================================

X'8' X'8' X'80' X'8'

NULL NULL X'a0' X'a'

NULL NULL X'aa' X'aa'

BIT VARYING(n)

If the length of the string exceeds n, it is truncated and filled with 0s.
The remainder of the string is not filled with 0s even if a bit string smaller than n is stored.
n must be a number greater than 0.

Example

CREATE TABLE bitvar_tbl(a1 BIT VARYING, a2 BIT VARYING(8));

INSERT INTO bitvar_tbl VALUES (B'1', B'1');

INSERT INTO bitvar_tbl VALUES (0b1010, 0b1010);

INSERT INTO bitvar_tbl VALUES (0xaa, 0xaa);

INSERT INTO bitvar_tbl(a1) VALUES (0xaaa);

SELECT * FROM bitvar_tbl;

a1 a2

============================================

X'8' X'8'

X'a' X'a'

X'aa' X'aa'

X'aaa' NULL

INSERT INTO bitvar_tbl(a2) VALUES (0xaaa);

ERROR: Data overflow coercing X'aaa' to type bit varying.

3 日期型

The range of year in DATE is 0001 - 9999 AD.
By default, the range of a time value is represented by the 24-hour system
The range of TIMESTAMP is between 1970-01-01 00:00:01 - 2038-01-19 03 03:14:07 (GMT). For KST (GMT+9), values from 1970-01-01 00:00:01 to 2038-01-19 12:14:07 can be stored.

格式

DATE Type
YYYY-MM-DD
MM/DD/YYYY
TIME Type
HH:MM:SS ["AM"|"PM"]
DATETIME Type
YYYY-MM-DD HH:MM:SS[.msec] ["AM"|"PM"]
TIMESTAMP Type
YYYY-MM-DD HH:MM:SS ["AM"|"PM"]

Type	bytes	Min.	Max.	Note
DATE	4	0001-01-01	9999-12-31	As an exception, DATE '0000-00-00' format is allowed.
TIME	4	00:00:00	23:59:59
TIMESTAMP	4	1970-01-01 00:00:01 (GMT) 1970-01-01 09:00:01 (KST)	2038-01-19 03:14:07 (GMT) 2038-01-19 12:14:07 (KST)	As an exception, TIMESTAMP '0000-00-00 00:00:00' format is allowed.
DATETIME	8	0001-01-01 00:00:0.000	9999-12-31 23:59:59.999	As an exception, DATETIME '0000-00-00 00:00:00' format is allowed.