接上面的集合
集合之 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')
===================================