GBase 8a MPP Cluster ——操作符和函数

操作符和函数

在 SQL 语句中可以使用表达式，表达式可以包含常量，字段，NULL，操作符和函数。本章描述 GBase 8a MPP Cluster 中用于写在表达式中的操作符和函数。

包含 NULL 的表达式总是得出 NULL 值结果，除非表达式中的操作和函数在文档中有另外的说明。

本章中操作符和函数的执行示例使用表 t，并且该表已包含一行数据，此表并不是某个应用场景下的实际业务或者数据表，t 表信息如下：

create table t(a int);

insert into t values(1);

select a from t;

drop table if exists t;

操作符优先级

BINARY，COLLATE
!
-(unary minus),～(unary bit inversion)
^
*,/,DIV,%,MOD
-,+
<<,>>
&
=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
BETWEEN,CASE,WHEN,THEN,ELSE
NOT
&&,AND
OR,XOR
:=

圆括号

(…)
括号，使用它来规定一个表达式的运算顺序，放在括号里的操作符优先执行。
示例 1：不使用括号，表达式先执行乘法操作，再执行加法操作。

BEGIN TRANSACTION;

create table t(a int);

insert into t values(1);

select a from t;

-- drop table if exists t;

select 1+2+3 from t;

select (1+2)*3 from t;

比较函数和操作符

如果一个或两个参数是 NULL，比较的结果是 NULL，除了<=>比较符（含
有 NULL 参数时，比较结果不是 NULL）。

如果在一个比较操作中两个参数均是字符串，它们作为字符串被比较。

如果两个参数均是整数，它们作为整数被比较。

如果比较操作中，一个参数为字符串，另一个为整数，则以操作符左侧参数的数据类型为准。

十六进制值如果不与一个数字进行比较，那么它将当作一个二进制字符串。

如果参数之一是 DATETIME 列，而另一参数是一个常量，在比较执行之前，这个常量被转换为一个时间戳。需要注意的是 IN()中的参数不是这样的。为了安全起见，建议用户在比较时使用完整的 DATETIME/DATE/TIME 字符串。

在所有其它情况下，参数作为浮点（REAL）数字被比较。要转换一个值成为一个特殊的类型，用户可以使用 CAST()函数。

= 等于

a=b
如果两个操作数相等，则返回 1。

select 1=0 from t;

数字与字符串进行比较。

select '0' = 0 from t;

数字与字符串进行比较

select '0.0'='0.0' from t;
select '0.0'=0 from t;

两个操作数都是 NULL。

select NULL = NULL from t;

<=> NULL值安全等于

NULL 值安全等于：这个操作符像‚=‛操作符一样执行相等比较，但在下面 2 种情况下，获得的结果与‚=‛不同：

如果所有的操作数是 NULL，那么返回的是 1 而不是 NULL。
如果有且只有一个操作数是 NULL，那么返回的是 0 而不是 NULL。

示例 1：所有操作数为 NULL，或部分操作数为 NULL。

select 1 <=> 1, NULL <=> NULL, 1 <=> NULL from t;

select 1 = 1, NULL = NULL, 1 = NULL from t;

<> , != 不等于

select 1 != 2 from t;
select 1 <> 2 from t;

select 'ha' <> 'sh' from t;

小于、小于等于、大于、大于等于

select 1 < 2 from t;
select 1 <= 1 from t;
select 1 > 2 from t;
select 1 >= 3 from t;

is boolean_value ,is not boolean_value

使用 IS 语句检验一个值是否是 NULL

select 1 is NULL , NULL is NULL, 0 is NULL from t;

使用 IS NOT 语句检验一个值是否是 NULL。

select 1 is not NULL, 0 is not NULL, NULL is not NULL from t; 

expr BETWEEN min AND max

如果 expr 的值在 min 和 max 之间（包括 min 和 max），返回 1，否则返回 0。
若所有参数都是同一类型，则上述关系相当于表达式(min <= expr AND expr <= max)。其它类型的转换根据本章开篇所述规律进行，且适用于三种参数中任意一种

所有参数为同一类型，expr 不在 min 和 max 中。
所有参数为同一类型，expr 在 min 和 max 中。
参数中包含数字和字符串。

select 1 between 2 and 3 from t;
select 'b' between 'a' and 'c' from t;
select 2 between 2 and 'a' from t; 

expr NOT BETWEEN min AND max

等同于 NOT(expr BETWEEN min AND max)。

COALESCE(value,…)

返回值为列表当中的第一个非 NULL 值，在全部为 NULL 值的情况下返回值为 NULL。

其中一个参数值为 NULL。
每个参数值都为 NULL

select coalesce(null, 1) from t;
select coalesce(null, null, null) from t;

GREATEST(value1,value2,…)

参数值为整型数字。
参数值为浮点型数字
参数值为字符串，不区分大小写。
参数值为字符串，字符串参数前加上 BINARY，区分大小写。
参数值中包含 NULL，则执行结果为 NULL。

select createst(2, 0) from t;
select createst(34.0, 2.0, 78.0,2.0) from t;
select createst('a', 'b', 'C') from t;
select createst('A', binary 'c', 'D') from t;
select ceratest('A', 'b', NULl) from t;

expr IN (value,…)

如果 expr 是 IN 列表中的任一值，它将返回 1，否则返回 0。
如果所有的值均是常量，那么所有的值被按照 expr 的类型进行计算和排
序。

示例 1：expr 不是 IN 列表中的任一值。

select 2 in (0, 3, 5, '8') from t;
select '1' in (0, 3, 5, '1') from t;

如果左边的表达式是 NULL，或者在列表中没有发现相匹配的值并且列表中的一个表达式是 NULL，IN 均返回 NULL。IN()语法也可以用于子查询类型。

select null in (0, 3, 5, 'wefwf') from t;
select 1 in (2,3,null) from t;

子查询中包含 IN（）函数。
示例中用到的表及数据：

create table sc (sno Varchar(4), grade INT);
insert into sc values('101', 82),('102', 59), ('103', 90), ('104', 88), ('106', 82);

select sno from sc where grade in (select grade from sc where grade > 60) group by sno;

expr NOT IN (value,…)

等价于 NOT(expr IN (value,…))。

ISNULL(expr)

如果 expr 为 NULL，ISNULL()的返回值为 1，否则返回值为 0。
示例 1：expr 的值不为 NULL。
示例 2：1/0 的结果为 NULL，ISNULL()的返回值为 1。
示例 3：对 NULL 值使用‚=‛进行比较，ISNULL 结果为 1。

select isnull(1+1) from t;
select isnull(1/0) from t;
select isnull(null=null) from t;

LEAST(value1,value2,…)

有两个或者更多的参数，返回最小的参数值。假如任意一个变量为 NULL，
则 LEAST()的返回值为 NULL。
LEAST()对参数进行比较所依据的规则同 GREATEST()相同

示例 1：参数值为整型数字，返回最小的参数值。

select least(2, 0) from t;

参数值为浮点型数字，返回最小的参数值。

select least(34.0, 28.0, 0.2) from t;

参数值为字符串，不区分大小写。
参数值为字符串，字符串参数前加上 BINARY，区分大小写。
参数值中包含 NULL，则执行结果为 NULL。

select least('B', 'A', 'c') from t;
select least(binary 'A', binary "d", c) from t;
seelct least('a', null, 'c') from t;

逻辑操作符

在 SQL 中，所有的逻辑操作符返回的值均为 TRUE、FALSE 或NULL(UNKNOWN)，它们是由 1(TRUE）、0(FALSE）和 NULL 来实现的。

NOT，!逻辑非

如果操作数为 0，返回 1；如果操作数为非零，返回 0；如果操作数为 NULL，返回 NULL。

操作数为非零，返回值为 0。
操作数为 0，返回值为 1。
操作数为 NULL，返回值为 NULL。
表达式的值为非零，返回值为 0。
表达式! 1+1 与(!1)+1 等价，执行结果为 1。
…NOT IN…

select not 10 from t;
select not 0 from t;
select not null from t;
select !(1+1) from t;
select ! 1+1 from t;
select (!1) + 1 from t;
select 1 not in (2, 3, null) from t;

XOR逻辑异或

当任意一个操作数为 NULL 时，返回值为 NULL。
对于非 NULL 的操作数：
真（1）异或假（0）的结果是真，假（0）异或真（1）的结果也是真。
真（1）异或真（1）的结果是假，假（0）异或假（0）的结果是假。
就是说两个值不相同，则异或结果为真，反之，为假。

操作数不是 NULL，真异或真，结果为假，即返回值为 0

select 1 XOR 0 from t;
select 1 XOR NULL from t;
select 0 XOR NULL from t;
select 'a' XOR 'b';
select ('a' and (not 'b') )or ((not 'a') and 'b');
select 1 XOR 1 XOR 1 from t;