操作符
1 操作符优先级
操作符优先级在下面列出,从最高到最低。同一行的操作符具有同样的优先级。
BINARY,COLLATE
!
-(unary minus),~(unary bit inversion)
^
*,/,DIV,%,MOD
-,+
<<,>>
&
|
=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
BETWEEN,CASE,WHEN,THEN,ELSE
NOT
AND
OR,XOR
:=
2 圆括号
(...)
括号,使用它来规定一个表达式的运算顺序,放在括号里的操作符优先执行。
示例1:不使用括号,表达式先执行乘法操作,再执行加法操作。
gbase> SELECT 1+2*3 FROM t;
+-------+
| 1+2*3 |
+-------+
| 7 |
+-------+
1 row in set
示例2:使用括号,表达式先执行括号中的加法操作,再执行括号外的乘法操作。
gbase> SELECT (1+2)*3 FROM t;
+---------+
| (1+2)*3 |
+---------+
| 9 |
+---------+
1 row in set
3 比较函数和操作符
比较运算的结果是1(TRUE)、0(FALSE)或NULL。这些运算可用于数字和字符串上。根据需要,字符串将会自动地被转换到数字,而数字也可自动转换为字符串。
说明:本章中的一些函数(如GREATEST()和LEAST())的所得值虽然不包括1(TRUE)、0(FALSE)或NULL,但对参数值进行比较时,也会基于下述规则。
GBase 8s 分布式使用下列规则进行数值比较:
如果一个或两个参数是NULL,比较的结果是NULL,除了<=>比较符(含有NULL参数时,比较结果不是NULL)。
如果在一个比较操作中两个参数均是字符串,它们作为字符串被比较。
如果两个参数均是整数,它们作为整数被比较。
如果比较操作中,一个参数为字符串,另一个为整数,则以操作符左侧参数的数据类型为准。
十六进制值如果不与一个数字进行比较,那么它将当作一个二进制字符串。
如果参数之一是DATETIME列,而另一参数是一个常量,在比较执行之前,这个常量被转换为一个时间戳。需要注意的是IN()中的参数不是这样的。为了安全起见,建议用户在比较时使用完整的DATETIME/DATE/TIME字符串。
在所有其它情况下,参数作为浮点(REAL)数字被比较。
要转换一个值成为一个特殊的类型,用户可以使用CAST()函数。
3.1 = 等于
a=b
如果两个操作数相等,则返回1。
示例1:两个操作数都是数字。
gbase> SELECT 1 = 0 FROM t;
+-------+
| 1 = 0 |
+-------+
| 0 |
+-------+
1 row in set
示例2:数字与字符串进行比较。
gbase> SELECT '0' = 0 FROM t;
+---------+
| '0' = 0 |
+---------+
| 1 |
+---------+
1 row in set
示例3:数字与字符串进行比较。
gbase> SELECT '0.0' = 0 FROM t;
+-----------+
| '0.0' = 0 |
+-----------+
| 1 |
+-----------+
1 row in set
注意不支持以下连等写法:
select * from t1 where i=j=1;
不支持使用等于符号判断 NULL 值, 如:
Select * from t1 where a = null; -- 不支持
3.2 <> , != 不等于
a<>b或a!=b
如果两个操作数不相等,则返回1。
示例1:操作数都为字符串。
gbase> SELECT '01'<>'1';
+-----------+
| '01'<>'1' |
+-----------+
| 1 |
+-----------+
1 row in set
示例2:其中一个操作数为字符串。
gbase> SELECT 01<>'1';
+---------+
| 01<>'1' |
+---------+
| 0 |
+---------+
1 row in set
示例3:操作数都为字符串。
gbase> SELECT 'zapp' <> 'zappp' FROM t;
+-------------------+
| 'zapp' <> 'zappp' |
+-------------------+
| 1 |
+-------------------+
1 row in set
3.3 <= 小于或者等于
a<=b
如果a小于或等于b,则返回1。
示例1:两个操作数都为数字。
gbase> SELECT 0.1 <= 2 FROM t;
+----------+
| 0.1 <= 2 |
+----------+
| 1 |
+----------+
1 row in set
3.4 < 小于
a<b
如果a小于b,则返回1。
示例1:两个操作数都为数字。
gbase> SELECT 2 < 2 FROM t;
+-------+
| 2 < 2 |
+-------+
| 0 |
+-------+
1 row in set
3.5 >= 大于或者等于
a>=b
如果a大于或等于b,则返回1。
示例1:两个操作数都为数字。
gbase> SELECT 2 >= 2 FROM t;
+--------+
| 2 >= 2 |
+--------+
| 1 |
+--------+
1 row in set
3.6 > 大于
a>b
如果a大于b,则返回1。
示例1:两个操作数都为数字。
gbase> SELECT 2 > 2 FROM t;
+-------+
| 2 > 2 |
+-------+
| 0 |
+-------+
1 row in set
3.7 expr BETWEEN min AND max
如果expr的值在min和max之间(包括min和max),返回1,否则返回0。
若所有参数都是同一类型,则上述关系相当于表达式(min <= expr AND expr <= max)。其它类型的转换根据本章开篇所述规律进行,且适用于三种参数中任意一种。
示例1:所有参数为同一类型,expr不在min和max中。
gbase> SELECT 1 BETWEEN 2 AND 3 FROM t;
+-------------------+
| 1 BETWEEN 2 AND 3 |
+-------------------+
| 0 |
+-------------------+
1 row in set
示例2:所有参数为同一类型,expr在min和max中。
gbase> SELECT 'b' BETWEEN 'a' AND 'c' FROM t;
+-------------------------+
| 'b' BETWEEN 'a' AND 'c' |
+-------------------------+
| 1 |
+-------------------------+
1 row in set
示例3:参数中包含数字和字符串。
gbase> SELECT 2 BETWEEN 2 AND '3' FROM t;
+---------------------+
| 2 BETWEEN 2 AND '3' |
+---------------------+
| 1 |
+---------------------+
1 row in set
3.8 expr NOT BETWEEN min AND max
等同于NOT(expr BETWEEN min AND max)。
3.9 COALESCE(value,...)
返回值为列表当中的第一个非NULL值,在全部为NULL值的情况下返回值为NULL。如果指定了多个参数,每个参数的数据类型须一致,如均为字符型、数据值、日期型等,否则报错。
示例1:其中一个参数值为NULL。
gbase> SELECT COALESCE(NULL,1) FROM t;
+------------------+
| COALESCE(NULL,1) |
+------------------+
| 1 |
+------------------+
1 row in set
示例2:每个参数值都为NULL。
gbase> SELECT COALESCE(NULL,NULL,NULL) FROM t;
+--------------------------+
| COALESCE(NULL,NULL,NULL) |
+--------------------------+
| NULL |
+--------------------------+
1 row in set
3.10 GREATEST(value1,value2,...)
当有两个或多个参数时,返回值为最大的参数值。
当参数中有一个为NULL时,直接返回NULL。
当参数都是字符串时,默认是区分大小写的。
这些参数比较使用下列规则:
如果返回值在INTEGER上下文中或者所有的参数是整型值,那么它们使用整数比较;
如果返回值在REAL上下文中或者所有的参数是实数值,那么它们使用实数比较;
示例1:参数值为整型数字。
gbase> SELECT GREATEST(2,0) FROM t;
+---------------+
| GREATEST(2,0) |
+---------------+
| 2 |
+---------------+
1 row in set
示例2:参数值为浮点型数字。
gbase> SELECT GREATEST(34.0,3.0,5.0,767.0) FROM t;
+------------------------------+
| GREATEST(34.0,3.0,5.0,767.0) |
+------------------------------+
| 767.0 |
+------------------------------+
1 row in set
示例3:参数值为字符串,区分大小写。
gbase> SELECT GREATEST('B','a','C') FROM t;
+-----------------------+
| GREATEST('B','a','C') |
+-----------------------+
| a |
+-----------------------+
1 row in set
示例5:参数值中包含NULL,则执行结果为NULL。
gbase> SELECT GREATEST('B',NULL,'C') FROM t;
+------------------------+
| GREATEST('B',NULL,'C') |
+------------------------+
| NULL |
+------------------------+
1 row in set
3.11 expr IN (value,...)
如果expr是IN列表中的任一值,它将返回1,否则返回0。
如果所有的值均是常量,那么所有的值被按照expr的类型进行计算和排序。
示例1:expr不是IN列表中的任一值。
gbase> SELECT 2 IN (0,3,5,'8') FROM t;
+------------------+
| 2 IN (0,3,5,'8') |
+------------------+
| 0 |
+------------------+
1 row in set
示例2:expr是IN列表中的值。
gbase> SELECT '1' IN (0,3,5,'1') FROM t;
+--------------------+
| '1' IN (0,3,5,'1') |
+--------------------+
| 1 |
+--------------------+
1 row in set
如果左边的表达式是NULL,或者在列表中没有发现相匹配的值并且列表中的一个表达式是NULL,IN均返回NULL。IN()语法也可以用于子查询类型。
示例3:expr的值为NULL。
gbase> SELECT NULL IN (0,3,5,'wefwf') FROM t;
+-------------------------+
| NULL IN (0,3,5,'wefwf') |
+-------------------------+
| NULL |
+-------------------------+
1 row in set
示例4:子查询中包含IN()函数。
示例中用到的表及数据:
CREATE TABLE sc (sno VARCHAR(4), grade INT);
INSERT INTO sc VALUES ('101',82),('102',59),('103',90),('104',88),('106',82);
查询所有课程都及格的同学的学号。
gbase> SELECT sno FROM sc WHERE grade IN (SELECT grade FROM sc WHERE grade>60) GROUP BY sno;
+------+
| sno |
+------+
| 103 |
| 101 |
| 104 |
| 106 |
+------+
4 rows in set
注意:不支持多表达式in运算,如:(expr1,expr2) in (value1,value2)不支持。不支持列表中的值为逻辑表达式,如 expr in (100=100) ,不支持。
3.12 expr NOT IN (value,...)
等价于NOT(expr IN (value,...))。
3.13 LEAST(value1,value2,...)
有两个或者更多的参数,返回最小的参数值。假如任意一个变量为NULL,则LEAST()的返回值为NULL。
LEAST()对参数进行比较所依据的规则同GREATEST()相同。
示例1:参数值为整型数字,返回最小的参数值。
gbase> SELECT LEAST(2,0) FROM t;
+------------+
| LEAST(2,0) |
+------------+
| 0 |
+------------+
1 row in set
示例2:参数值为浮点型数字,返回最小的参数值。
gbase> SELECT LEAST(34.0,3.0,5.0,767.0) FROM t;
+---------------------------+
| LEAST(34.0,3.0,5.0,767.0) |
+---------------------------+
| 3.0 |
+---------------------------+
1 row in set
示例3:参数值为字符串,不区分大小写。
gbase> SELECT LEAST('B','A','C') FROM t;
+--------------------+
| LEAST('B','A','C') |
+--------------------+
| A |
+--------------------+
1 row in set
gbase> SELECT LEAST('B','a', 'C') FROM t;
+---------------------+
| LEAST('B','a', 'C') |
+---------------------+
| a |
+---------------------+
1 row in set
示例4:参数值为字符串,字符串参数前加上BINARY,区分大小写。
gbase> SELECT LEAST(BINARY 'B',BINARY 'a', 'C') FROM t;
+-----------------------------------+
| LEAST(BINARY 'B',BINARY 'a', 'C') |
+-----------------------------------+
| B |
+-----------------------------------+
1 row in set
示例5:参数值中包含NULL,则执行结果为NULL。
gbase> SELECT LEAST('C',NULL,'B') FROM t;
+---------------------+
| LEAST('C',NULL,'B') |
+---------------------+
| NULL |
+---------------------+
1 row in set
4 逻辑操作符
在SQL中,所有的逻辑操作符返回的值均为TRUE、FALSE或NULL(UNKNOWN),它们是由1(TRUE)、0(FALSE)和NULL来实现的。
4.1 NOT
如果操作数为0,返回1;如果操作数为非零,返回0;如果操作数为NULL,返回NULL。
示例1:操作数为非零,返回值为0。
gbase> SELECT NOT 10 FROM t;
+--------+
| NOT 10 |
+--------+
| 0 |
+--------+
1 row in set
示例2:操作数为0,返回值为1。
gbase> SELECT NOT 0 FROM t;
+-------+
| NOT 0 |
+-------+
| 1 |
+-------+
1 row in set
示例3:操作数为NULL,返回值为NULL。
gbase> SELECT NOT NULL FROM t;
+-----------+
| NOT NULL |
+-----------+
| NULL |
+-----------+
1 row in set
示例4:..NOT IN...
gbase> SELECT 1 NOT IN (2,3,null) FROM t;
+---------------------+
| 1 NOT IN (2,3,null) |
+---------------------+
| NULL |
+---------------------+
1 row in set
5 转换操作符和函数
5.1 CAST和CONVERT函数
CAST(expr AS type),CONVERT(expr,type),CONVERT(expr USING transcoding_name)
CAST()和CONVERT()函数可以用于将一个类型的数值转换到另一个类型。
type可以是下列值之一:
CHAR、DATE、DECIMAL、NUMERIC、INT、SIGNED INT、SIGNED。
CAST()和CONVERT(...USING...)是标准的SQL语法。
CAST(expr AS CHAR)把表达式看作是默认字符集中的字符串。
注意:使用CAST()函数改变列类型为DATE,DATETIME或TIME,只是标识此列,使其变为一个指定的数据类型,而不是改变列的值。
CAST()的最终执行结果将会转化为正确的列类型。
示例1:将NOW()转换为DATE类型。
gbase> SELECT CAST(NOW() AS DATE) FROM t;
+---------------------+
| CAST(NOW() AS DATE) |
+---------------------+
| 2013-10-17 |
+---------------------+
1 row in set
示例2:字符串和数字类型的转换是隐式操作,用户使用时只要把字符串值当做一个数字即可。
gbase> SELECT 1+'1' FROM t;
+-------+
| 1+'1' |
+-------+
| 2 |
+-------+
1 row in set
示例3:CAST(str AS BINARY)等价于BINARY str。
gbase> SELECT CAST('a' AS BINARY) = 'a ' FROM t;
+----------------------------+
| CAST('a' AS BINARY) = 'a ' |
+----------------------------+
| 0 |
+----------------------------+
1 row in set
gbase> SELECT BINARY 'a' = 'a ' FROM t;
+-------------------+
| BINARY 'a' = 'a ' |
+-------------------+
| 0 |
+-------------------+
1 row in set
6 日期算术运算
语法格式如下:
日期 +(-) bit_expr
同以下语法等价:
日期 +(-) interval expr type
日期加减运算跟普通的加减运算逻辑一样,只是后面加的数字、字符或表达式的单位为天数。该语法是在date类型、datetime类型、timestamp类型、time类型的变量后面加(或减去)指定的bit_expr的天数。
示例1:CAST('2002-06-18' as date) + 30为日期,返回增加30天后的日期。
gbase> SELECT CAST('2002-06-18' as date) + 30 from t;
+----------------------------------+
| CAST('2002-06-18' as date) + 30 |
+----------------------------------+
| 2002-07-18 |
+----------------------------------+
1 row in set