CUBRID学习笔记 24 数据类型1

时间：2023-03-08 19:46:55

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

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

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%'

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

相关文章