CUBRID学习笔记 24 数据类型1

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

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

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'

  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.

 

 

 4  字符串  

基本也是varchar nvarchar之类的

CHAR or VARCHAR 长度 1,073,741,823

 NCHAR or NCHAR  长度  536,870,911

 CSQL语句长度8,192 KB

字符集 可以通过数据库的环境变量设置

转义字符和方法

• \' : Single quotes (')
• \" : Double quotes (")
• \n : Newline, linefeed character
• \r : Carriage return character
• \t : Tab character
• \\ : Backslash
• \% : Percent sign (%). 
• \_ : Underbar (_). 

示例

欢迎转载 ，转载时请保留作者信息。本文版权归本人所有，如有任何问题，请与我联系wang2650@sohu.com 。 过错

 

SELECT LENGTH('\\');

 

   char_length('\')

 

CREATE TABLE t1 (a varchar(200));

INSERT INTO t1 VALUES ('aaabbb'), ('aaa%');

 

SELECT a FROM t1 WHERE a LIKE 'aaa\%' escape '\\';

 

  a

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

  'aaa%'

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---恢复内容结束---