你的位置:首页 > 数据库

[数据库]CUBRID学习笔记 26 数据类型3cubrid教程


接上面的集合

集合之 set 

每个集合元素是不同的值, 但是类型只能是一种.也可以有其他表的记录 如下

CREATE TABLE set_tbl ( col_1 set(CHAR(1)));

INSERT INTO set_tbl VALUES ({'c','c','c','b','b','a'});

INSERT INTO set_tbl VALUES ({NULL});

INSERT INTO set_tbl VALUES ({''});

SELECT * FROM set_tbl;

  col_1

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

{'a', 'b', 'c'}

{NULL}

{' '}

 

SELECT CAST(col_1 AS MULTISET), CAST(col_1 AS LIST) FROM set_tbl;

   cast(col_1 as multiset)   cast(col_1 as sequence)

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

  {'a', 'b', 'c'}  {'a', 'b', 'c'}

  {NULL}  {NULL}

  {' '}  {' '}

 

INSERT INTO set_tbl VALUES ('');

 

ERROR: Cannot coerce '' to type set.

 

集合 MULTISET 

和set差不多.但是元素的内容可重复

CREATE TABLE multiset_tbl ( col_1 multiset(CHAR(1)));

INSERT INTO multiset_tbl VALUES ({'c','c','c','b','b', 'a'});

SELECT * FROM multiset_tbl;

  col_1

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

  {'a', 'b', 'b', 'c', 'c', 'c'}

 

SELECT CAST(col_1 AS SET), CAST(col_1 AS LIST) FROM multiset_tbl;

   cast(col_1 as set)   cast(col_1 as sequence)

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

  {'a', 'b', 'c'}  {'c', 'c', 'c', 'b', 'b', 'a'}

 

 

集合 之list

可重复的,但是是有序的.保持插入前的顺序.list本身不会对内容排序.

 

CREATE TABLE list_tbl ( col_1 list(CHAR(1)));

INSERT INTO list_tbl VALUES ({'c','c','c','b','b', 'a'});

SELECT * FROM list_tbl;

  col_1

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

  {'c', 'c', 'c', 'b', 'b', 'a'}

 

SELECT CAST(col_1 AS SET), CAST(col_1 AS MULTISET) FROM list_tbl;

   cast(col_1 as set)  cast(col_1 as multiset)

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

  {'a', 'b', 'c'}  {'a', 'b', 'b', 'c', 'c', 'c'}

 

 

隐式转换

 

From \ To

DATETIME

DATE

TIME

TIMESTAMP

DOUBLE

FLOAT

NUMERIC

BIGINT

DATETIME

-

O

O

O

    

DATE

O

-

 

O

    

TIME

  

-

     

TIMESTAMP

O

O

O

-

    

DOUBLE

    

-

O

O

O

FLOAT

    

O

-

O

O

NUMERIC

    

O

O

-

O

BIGINT

    

O

O

O

-

INT

   

O

O

O

O

O

SHORT

    

O

O

O

O

MONETARY

    

O

O

O

O

BIT

        

VARBIT

        

CHAR

O

O

O

O

O

O

O

O

VARCHAR

O

O

O

O

O

O

O

O

NCHAR

O

O

O

O

O

O

O

O

VARNCHAR

O

O

O

O

O

O

O

O

Implicit Type Conversion Table 2

From \ To

INT

SHORT

MONETARY

BIT

VARBIT

CHAR

VARCHAR

NCHAR

VARNCHAR

DATETIME

     

O

O

O

O

DATE

     

O

O

O

O

TIME

     

O

O

O

O

TIMESTAMP

     

O

O

O

O

DOUBLE

O

O

O

  

O

O

O

O

FLOAT

O

O

O

  

O

O

O

O

NUMERIC

O

O

O

  

O

O

O

O

BIGINT

O

O

O

  

O

O

O

O

INT

-

O

O

  

O

O

O

O

SHORT

O

-

O

  

O

O

O

O

MONETARY

O

O

-

  

O

O

O

O

BIT

   

-

O

O

O

O

O

VARBIT

   

O

-

O

O

O

O

CHAR

O

O

O

O

O

-

O

O

O

VARCHAR

O

O

O

O

O

O

-

O

O

NCHAR

O

O

O

O

O

O

O

-

O

VARNCHAR

O

O

O

O

O

O

O

O

-

 

 

转换规则

 

插入和修改

数据会转为目标列的数据类型

CREATE TABLE t(i INT);

INSERT INTO t VALUES('123');

 

SELECT * FROM t;

 

            i

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

          123

 

函数

参数被转为函数的参数类型,如下  mod参数需要的是数字类型. 所以传入的字符串被转为数字

SELECT MOD('123','2');

 

           mod('123', '2')

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

     1.000000000000000e+00

    层次关系

  • Date/Time Type (DATETIME > TIMESTAMP > DATE > TIME)
  • Approximate Numeric Type (MONETARY > DOUBLE > FLOAT)
  • Exact Numeric Type (NUMERIC > BIGINT > INT > SHORT)
  • String Type (CHAR/NCHAR > VARCHAR/VARNCHAR)

 

比较操作

perand1 Type

operand2 Type

Conversion

Comparison

Numeric Type

Numeric Type

None

NUMERIC

String Type

Converts operand2 to DOUBLE

NUMERIC

Date/Time Type

None

N/a

String Type

Numeric Type

Converts operand1 to DOUBLE

NUMERIC

String Type

None

String

Date/Time Type

Converts operand1 to date/time type

Date/Time

Date/Time Type

Numeric Type

None

N/A

String Type

Converts operand2 to date/time type

Date/Time

Date/Time Type

Converts it to the type with higher priority

Date/Time

字段类型的转换

operand1 Type

operand2 Type

Conversion

Comparison

String type

Numeric type

Converts operand2 to the string type

String

Date/Time type

Converts operand2 to the string type

String

 

If operand2 is a set operator(IS IN, IS NOT IN, = ALL, = ANY, < ALL, < ANY, <= ALL, <= ANY, >= ALL, >= ANY), the exception above is not applied.

如果operand2 是集合操作 如 IS IN, IS NOT IN, = ALL, = ANY, < ALL, < ANY, <= ALL, <= ANY, >= ALL, >= ANY, 则不适用

 

数字和字符串比较

字符串将被转换为dobule类型

CREATE TABLE t(i INT, s STRING);

INSERT INTO t VALUES(1,'1'),(2,'2'),(3,'3'),(4,'4'), (12,'12');

 

SELECT i FROM t WHERE i < '11.3';

 

            i

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

            1

            2

            3

            4

 

SELECT ('2' <= 11);

 

     ('2'<11)

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

            1

 

 

字符串和日期比较

字符串被转为日期

SELECT ('2010-01-01' < date'2010-02-02');

 

   ('2010-01-01'<date '2010-02-02')

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

                                1

 

SELECT (date'2010-02-02' >= '2010-01-01');

 

字符串类型和系统的数字类型

数字被转为字符串

PREPARE s FROM 'SELECT s FROM t WHERE s < ?';

EXECUTE s USING 11;

       s

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

     '1'

 

字符串类型和数字类型

数字被转为字符串

SELECT s FROM t WHERE s > 11;

       s

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

     '2'

     '3'

     '4'

     '12'

 

SELECT s FROM t WHERE s BETWEEN 11 AND 33;

        s

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

      '2'

      '3'

      '12'

字符串和日期类型

日期转为字符串

SELECT s FROM t;

 

           s

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

    '01/01/1998'

    '01/01/1999'

    '01/01/2000'

 

SELECT s FROM t WHERE s <= date'02/02/1998';

            s

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

    '01/01/1998'

    '01/01/1999'

    '01/01/2000'

 

 

 

 

 

 

 

 

 

  (date '2010-02-02'>='2010-01-01')

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