版本：Next

运算符与字面量(Operators and Literals)

数值运算符(Numerical Operators)

+(加)
-(减)
*(乘)
/(除)
%(取模)

`+`

加法

sql> SELECT 1 + 2;
+---------------------+
| Int64(1) + Int64(2) |
+---------------------+
| 3                   |
+---------------------+

`-`

减法

sql> SELECT 4 - 3;
+---------------------+
| Int64(4) - Int64(3) |
+---------------------+
| 1                   |
+---------------------+

`*`

乘法

sql> SELECT 2 * 3;
+---------------------+
| Int64(2) * Int64(3) |
+---------------------+
| 6                   |
+---------------------+

`/`

除法(整数除法向零截断)

sql> SELECT 8 / 4;
+---------------------+
| Int64(8) / Int64(4) |
+---------------------+
| 2                   |
+---------------------+

`%`

取模(求余)

sql> SELECT 7 % 3;
+---------------------+
| Int64(7) % Int64(3) |
+---------------------+
| 1                   |
+---------------------+

比较运算符(Comparison Operators)

=(等于)
!=(不等于)
<(小于)
<=(小于或等于)
>(大于)
=(大于或等于)
<=>(三向比较,是 IS NOT DISTINCT FROM 的别名)
IS DISTINCT FROM
IS NOT DISTINCT FROM
~(正则匹配)
~*(正则匹配,不区分大小写)
!~(正则不匹配)
!~*(正则不匹配,不区分大小写)

`=`

等于

sql> SELECT 1 = 1;
+---------------------+
| Int64(1) = Int64(1) |
+---------------------+
| true                |
+---------------------+

`!=`

不等于

sql> SELECT 1 != 2;
+----------------------+
| Int64(1) != Int64(2) |
+----------------------+
| true                 |
+----------------------+

`<`

小于

sql> SELECT 3 < 4;
+---------------------+
| Int64(3) < Int64(4) |
+---------------------+
| true                |
+---------------------+

`<=`

小于或等于

sql> SELECT 3 <= 3;
+----------------------+
| Int64(3) <= Int64(3) |
+----------------------+
| true                 |
+----------------------+

`>`

大于

sql> SELECT 6 > 5;
+---------------------+
| Int64(6) > Int64(5) |
+---------------------+
| true                |
+---------------------+

`>=`

大于或等于

sql> SELECT 5 >= 5;
+----------------------+
| Int64(5) >= Int64(5) |
+----------------------+
| true                 |
+----------------------+

`<=>`

三向比较运算符。这是一个 NULL 安全的运算符:如果两个操作数相等或都为 NULL,返回 true,否则返回 false。

sql> SELECT NULL <=> NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

sql> SELECT 1 <=> NULL;
+------------------------------------+
| Int64(1) IS NOT DISTINCT FROM NULL |
+------------------------------------+
| false                              |
+------------------------------------+

sql> SELECT 1 <=> 2;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(2) |
+----------------------------------------+
| false                                  |
+----------------------------------------+

sql> SELECT 1 <=> 1;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(1) |
+----------------------------------------+
| true                                   |
+----------------------------------------+

`IS DISTINCT FROM`

保证比较的结果是 true 或 false,而不是空集

sql> SELECT 0 IS DISTINCT FROM NULL;
+--------------------------------+
| Int64(0) IS DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

`IS NOT DISTINCT FROM`

IS DISTINCT FROM 的否定形式

sql> SELECT NULL IS NOT DISTINCT FROM NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

`~`

正则匹配

sql> SELECT 'datafusion' ~ '^datafusion(-cli)*';
+-------------------------------------------------+
| Utf8("datafusion") ~ Utf8("^datafusion(-cli)*") |
+-------------------------------------------------+
| true                                            |
+-------------------------------------------------+

`~*`

正则匹配(不区分大小写)

sql> SELECT 'datafusion' ~* '^DATAFUSION(-cli)*';
+--------------------------------------------------+
| Utf8("datafusion") ~* Utf8("^DATAFUSION(-cli)*") |
+--------------------------------------------------+
| true                                             |
+--------------------------------------------------+

`!~`

正则不匹配

sql> SELECT 'datafusion' !~ '^DATAFUSION(-cli)*';
+--------------------------------------------------+
| Utf8("datafusion") !~ Utf8("^DATAFUSION(-cli)*") |
+--------------------------------------------------+
| true                                             |
+--------------------------------------------------+

`!~*`

正则不匹配(不区分大小写)

sql> SELECT 'datafusion' !~* '^DATAFUSION(-cli)+';
+---------------------------------------------------+
| Utf8("datafusion") !~* Utf8("^DATAFUSION(-cli)+") |
+---------------------------------------------------+
| true                                              |
+---------------------------------------------------+

`~~`

LIKE 匹配

sqlSELECT 'datafusion' ~~ 'dat_f%n';
+---------------------------------------+
| Utf8("datafusion") ~~ Utf8("dat_f%n") |
+---------------------------------------+
| true                                  |
+---------------------------------------+

`~~*`

LIKE 匹配(不区分大小写)

sqlSELECT 'datafusion' ~~* 'Dat_F%n';
+----------------------------------------+
| Utf8("datafusion") ~~* Utf8("Dat_F%n") |
+----------------------------------------+
| true                                   |
+----------------------------------------+

`!~~`

LIKE 不匹配

sqlSELECT 'datafusion' !~~ 'Dat_F%n';
+----------------------------------------+
| Utf8("datafusion") !~~ Utf8("Dat_F%n") |
+----------------------------------------+
| true                                   |
+----------------------------------------+

`!~~*`

LIKE 不匹配(不区分大小写)

sqlSELECT 'datafusion' !~~* 'Dat%F_n';
+-----------------------------------------+
| Utf8("datafusion") !~~* Utf8("Dat%F_n") |
+-----------------------------------------+
| true                                    |
+-----------------------------------------+

逻辑运算符(Logical Operators)

`AND`

逻辑与

sql> SELECT true AND true;
+---------------------------------+
| Boolean(true) AND Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

`OR`

逻辑或

sql> SELECT false OR true;
+---------------------------------+
| Boolean(false) OR Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

位运算符(Bitwise Operators)

&(按位与)
|(按位或)
#(按位异或)
(按位右移)
<<(按位左移)

`&`

按位与

sql> SELECT 5 & 3;
+---------------------+
| Int64(5) & Int64(3) |
+---------------------+
| 1                   |
+---------------------+

`|`

按位或

sql> SELECT 5 | 3;
+---------------------+
| Int64(5) | Int64(3) |
+---------------------+
| 7                   |
+---------------------+

`#`

按位异或(可与 ^ 互换)

sql> SELECT 5 # 3;
+---------------------+
| Int64(5) # Int64(3) |
+---------------------+
| 6                   |
+---------------------+

`>>`

按位右移

sql> SELECT 5 >> 3;
+----------------------+
| Int64(5) >> Int64(3) |
+----------------------+
| 0                    |
+----------------------+

`<<`

按位左移

sql> SELECT 5 << 3;
+----------------------+
| Int64(5) << Int64(3) |
+----------------------+
| 40                   |
+----------------------+

其他运算符(Other Operators)

||(字符串拼接)
@>(数组包含)
<@(被数组包含)

`||`

字符串拼接

sql> SELECT 'Hello, ' || 'DataFusion!';
+----------------------------------------+
| Utf8("Hello, ") || Utf8("DataFusion!") |
+----------------------------------------+
| Hello, DataFusion!                     |
+----------------------------------------+

`@>`

数组包含

sql> SELECT make_array(1,2,3) @> make_array(1,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3)) @> make_array(Int64(1),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

`<@`

被数组包含

sql> SELECT make_array(1,3) <@ make_array(1,2,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(3)) <@ make_array(Int64(1),Int64(2),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

字面量(Literals)

字面量值使用单引号。例如,字符串 foo bar 写作 'foo bar'

sqlselect 'foo';

转义(Escaping)

与许多其他语言不同,SQL 字面量默认不支持 C 风格的转义序列(例如表示换行的 \n)。' 字符串中的所有字符都按字面处理。

要在 SQL 字面量中转义 ',请使用 '':

sql> select 'it''s escaped';
+----------------------+
| Utf8("it's escaped") |
+----------------------+
| it's escaped         |
+----------------------+
1 row(s) fetched.

诸如 foo\nbar 这样的字符串表示 \ 后跟 n(而不是换行):

sql> select 'foo\nbar';
+------------------+
| Utf8("foo\nbar") |
+------------------+
| foo\nbar         |
+------------------+
1 row(s) fetched.
Elapsed 0.005 seconds.

要加入换行或制表符等转义字符,请使用 E 风格的字符串而不是 \n。例如,要加入带换行的文本

textfoo
bar

你可以使用 E'foo\nbar'

sql> select E'foo\nbar';
+-----------------+
| Utf8("foo
bar") |
+-----------------+
| foo
bar         |
+-----------------+

数值运算符(Numerical Operators)​

+

-​

*​

/​

%​

比较运算符(Comparison Operators)​

=​

!=​

<​

<=​

>​

>=​

<=>​

IS DISTINCT FROM​

IS NOT DISTINCT FROM​

~​

~*​

!~​

!~*​

~~​

~~*​

!~~​

!~~*​

逻辑运算符(Logical Operators)​

AND​

OR​

位运算符(Bitwise Operators)​

&​

|​

#​

>>​

<<​

其他运算符(Other Operators)​

||​

@>​

<@​

字面量(Literals)​

转义(Escaping)​