【openGauss实战6】运算符及表达式

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 6 天，点击查看活动详情。

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程😜😜😜
擅长Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

摘要：本篇介绍了openGauss数据库初级管理，包括创建数据库、DML语句及常用的SQL检索等.

📣 1.运算符

openGauss中运算符是一个保留关键字或字符，一般用在WHERE语句中，作为过滤条件。常见运算符如下。

✨ 1.1 比较操作符

比较操作符均为双目操作符，被比较的两个数据类型必须是相同的数据类型或者是可以进行隐式转换的类型。 可用比较操作符进行比较，并返回一个布尔类型的值 openGauss提供的比较操作符如下表

操作符	描述
<	小于
大于
<=	小于或等于
>=	大于或等于
=	等于
<> 或 !=或^=	不等于

不等号的计算优先级高于等号。当输入的数据不同且无法隐式转换时, 比较操作将会失败

openGauss=# select 3<4;
 ?column?
----------
 t
(1 row)

openGauss=# select 3>4;
 ?column?
----------
 f
(1 row)

openGauss=# select 3>=4;
 ?column?
----------
 f
(1 row)

openGauss=# select 3<=4;
 ?column?
----------
 t
(1 row)

openGauss=# select 3=4;
 ?column?
----------
 f
(1 row)

openGauss=# select 3!=4;
 ?column?
----------
 t
(1 row)

✨ 1.2 逻辑操作符

常用的逻辑操作符有AND、OR和NOT，他们的运算结果有三个值，分别为TRUE、FALSE和NULL，其中NULL代表未知。他们运算优先级顺序为：NOT>AND>OR。 操作符AND和OR具有交换性，即交换左右两个操作数，不影响其结果。 openGauss提供的逻辑操作符如下表

a	b	a AND b的结果	a OR b的结果	NOT a的结果
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	NULL	NULL	TRUE	FALSE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	NULL	FALSE	NULL	TRUE
NULL	NULL	NULL	NULL	NULL

📢📢📢 运算符and案例：

找到customer_id为hello且customer_sk为3869的行。

test=# SELECT * FROM customer_t WHERE  customer_sk = 3869 AND customer_id = 'hello';
 customer_sk | customer_id | first_name | last_name 
-------------+-------------+------------+-----------
        3869 | hello       | Grace      | 

📢📢📢 运算符or案例：

找到customer_sk大于6985或者customer_id为hello的行。

test=# SELECT * FROM customer_t WHERE  customer_sk > 6985 OR customer_id = 'hello';
 customer_sk | customer_id | first_name | last_name 
-------------+-------------+------------+-----------
        3869 | hello       | Grace      | 
        9976 | world       | James      | 

📢📢📢 运算符NOT NULL案例：

找到customer_t中last_name字段不为空的行。
test=# SELECT * FROM customer_t WHERE  last_name IS NOT NULL;
 customer_sk | customer_id | first_name | last_name 
-------------+-------------+------------+-----------
        3869 | 5           | Grace      | White   

✨ 1.3 算术运算符

openGauss中运算符一般用于结果集，常见运算符如下。

1.算术运算符-加
openGauss=# SELECT 2+3 AS RESULT;         
 result 
--------
      5

2.算术运算符-减
openGauss=# SELECT 2-3 AS RESULT;
 result 
--------
     -1

3.算术运算符-乘
openGauss=# SELECT 2*3 AS RESULT;
 result 
--------
      6

4.算术运算符-除（除法操作符不会取整）
openGauss=# SELECT 4/2 AS RESULT;
 result 
--------
      2

5.算术运算符-模（求余）
openGauss=# SELECT 5%4 AS RESULT;
 result 
--------
      1

6.算术运算符-绝对值
openGauss=# SELECT @ -5.0 AS RESULT;
 result 
--------
    5.0

7.算术运算符-幂（指数运算）
openGauss=# SELECT 2.0^3.0 AS RESULT;
       result       
--------------------
 8.0000000000000000

8.算术运算符-平方根
openGauss=# SELECT |/ 25.0 AS RESULT;
 result
--------
      5

9.算术运算符-立方根
openGauss=# SELECT ||/ 27.0 AS RESULT;
 result
--------
      3

10.算术运算符-阶乘
 从1到n的连续自然数相乘的积,叫做阶乘,用符号n!表示
openGauss=# SELECT 5! AS RESULT;
 result
--------
    120

📣 2.表达式

表达式类似一个公式，我们可以将其应用在查询语句中，用来查找数据库中指定条件的结果集。

✨ 2.1 简单表达式

1.逻辑表达式 2.比较表达式 除比较操作符外，还可以使用以下句式结构： 1)BETWEEN操作符 a BETWEEN x AND y等效于a >= x AND a <= y a NOT BETWEEN x AND y等效于a < x OR a > y 2)检查一个值是不是NULL expression IS NULL expression IS NOT NULL 3.伪列（ROWNUM） ROWNUM是一个伪列，它返回一个数字，表示从查询中获取结果的行编号。第一行的ROWNUM为1，第二行的为2，依此类推

✨ 2.2 条件表达式

在执行SQL语句时，可通过条件表达式筛选出符合条件的数据。 条件表达式主要有以下几种

📢📢📢 CASE表达式

说明：CASE子句可以用于合法的表达式中。condition是一个返回BOOLEAN数据类型的表达式
如果结果为真，CASE表达式的结果就是符合该条件所对应的result。
如果结果为假，则以相同方式处理随后的WHEN或ELSE子句。
如果各WHEN condition都不为真，表达式的结果就是在ELSE子句执行的result。如果省略了ELSE子句且没有匹配的条件，结果为NULL。

openGauss=# CREATE TABLE case_when(CW_COL1 INT);

openGauss=# INSERT INTO case_when VALUES (1), (2), (3);

openGauss=# SELECT * FROM case_when;
cw_col1
---------
 1
 2
 3
(3 rows)

openGauss=# SELECT CW_COL1, CASE WHEN CW_COL1=1 THEN 'one' WHEN CW_COL1=2 THEN 'two' ELSE 'other' END FROM case_when ORDER BY 1;
 cw_col1 | case
---------+-------
       1 | one
       2 | two
       3 | other
(3 rows)

openGauss=# DROP TABLE case_when;

📢📢📢 decode表达式

将表达式base_expr与后面的每个compare(n) 进行比较，如果匹配返回相应的value(n)。
如果没有发生匹配，则返回default。

openGauss=# SELECT DECODE('A','A',1,'B',2,0);
 case
------
    1
(1 row)

📢📢📢 COALESCE表达式

COALESCE返回它的第一个非NULL的参数值。如果参数都为NULL，则返回NULL。

openGauss=# CREATE TABLE c_tabl(description varchar(10), short_description varchar(10), last_value varchar(10)) ;

openGauss=# INSERT INTO c_tabl VALUES('abc', 'efg', '123');
openGauss=# INSERT INTO c_tabl VALUES(NULL, 'efg', '123');
openGauss=# INSERT INTO c_tabl VALUES(NULL, NULL, '123');

openGauss=# SELECT description, short_description, last_value, COALESCE(description, short_description, last_value) FROM c_tabl ORDER BY 1, 2, 3, 4;
 description | short_description | last_value | coalesce
-------------+-------------------+------------+----------
 abc         | efg               | 123        | abc
             | efg               | 123        | efg
             |                   | 123        | 123
(3 rows)

openGauss=# DROP TABLE c_tabl;

📢📢📢 NULLIF表达式

只有当value1和value2相等时，NULLIF才返回NULL。否则它返回value1。

openGauss=# CREATE TABLE null_if (
    NI_VALUE1 VARCHAR(10),
    NI_VALUE2 VARCHAR(10)
);

openGauss=# INSERT INTO null_if VALUES('abc', 'abc');
openGauss=# INSERT INTO null_if VALUES('abc', 'efg');

openGauss=# SELECT NI_VALUE1, NI_VALUE2, NULLIF(NI_VALUE1, NI_VALUE2) FROM null_if 
 ni_value1 | ni_value2 | nullif
-----------+-----------+--------
 abc       | abc       |
 abc       | efg       | abc
(2 rows)
openGauss=# DROP TABLE null_if;

📢📢📢 GREATEST（最大值），LEAST（最小值）

openGauss=# SELECT greatest(9000,155555,2.01);
 greatest
----------
   155555

openGauss=# SELECT least(9000,2);
 least
-------
     2

📢📢📢 NVL表达式

如果value1为NULL则返回value2，如果value1非NULL，则返回value1。

openGauss=# SELECT nvl(null,1);
nvl
-----
 1

openGauss=# SELECT nvl ('Hello World' ,1);
      nvl
---------------
 Hello World

✨ 2.3 子查询表达式

📢📢📢 EXISTS/NOT EXISTS

EXISTS的参数是一个任意的SELECT语句，或者说子查询。系统对子查询进行运算以判断它是否返回行。
如果它至少返回一行，则EXISTS结果就为"真"；如果子查询没有返回任何行， EXISTS的结果是"假"。
这个子查询通常只是运行到能判断它是否可以生成至少一行为止，而不是等到全部结束。

openGauss=# create table a (id number,name varchar2(10));
openGauss=# create table b (id number,aid number,name varchar2(10));
openGauss=# insert into a values(1,'A1'),(2,'A2'),(3,'A3');
openGauss=# insert into b values(1,1,'B1'),(2,2,'B2'),(3,2,'B3');

openGauss=# select id,name from a where exists (select * from b where a.id=b.aid);
 id | name 
----+------
  1 | A1
  2 | A2

openGauss=# select id,name from a where not exists (select * from b where a.id=b.aid);
 id | name 
----+------
  3 | A3

📢📢📢 IN/NOT IN

如果找到任何相等的子查询行，则IN结果为"真"。如果没有找到任何相等行，则结果为"假"
openGauss=# SELECT ID,NAME FROM A WHERE ID IN (SELECT AID FROM B);
 id | name 
----+------
  1 | A1
  2 | A2

openGauss=# SELECT ID,NAME FROM A WHERE ID NOT IN(SELECT AID FROM B);
 id | name 
----+------
  3 | A3

📢📢📢 ALL/ANY

ALL：是所有，表示全部都满足才返回true 
ANY/SOME：是任意一个 ，表示有任何一个满足就返回true


openGauss=# create table m (id number);
openGauss=# create table n (id number);
openGauss=# insert into m values(2),(3),(4);
openGauss=# insert into n values(1),(2),(3),(4),(5);

openGauss=# SELECT id FROM n WHERE id > ALL (SELECT id FROM m);
 id 
----
  5

openGauss=# SELECT id  FROM n WHERE id < ALL (SELECT id FROM m);
 id 
----
  1


openGauss=# SELECT id FROM n WHERE id > ANY (SELECT id FROM m);
 id 
----
  3
  4
  5

✨ 2.4 数组表达式

📢📢📢 ANY/SOME(array)

expression operator **ANY ** (array expression)
右侧括号中的是一个数组表达式，它必须产生一个数组值。左侧表达式的结果使用操作符对数组表达式的每一行结果都进行计算和比较，比较结果必须是布尔值。

如果对比结果至少获取一个真值，则ANY的结果为true。
如果对比结果没有真值，则ANY的结果为false。
如果结果没有真值，并且数组表达式生成至少一个值为null，则ANY的值为NULL，而不是false。这样的处理方式和SQL返回空值的布尔组合规则是一致的。
SOME是ANY的同义词。

openGauss=# SELECT 8000+500 < SOME (array[10000,9000]) AS RESULT;
  result
----------
 t
(1 row)

openGauss=# SELECT 8000+500 < ANY (array[10000,9000]) AS RESULT;
  result
----------
 t
(1 row)

📢📢📢 ALL(array)

expression operator **ALL ** (array expression)
右侧括号中的是一个数组表达式，它必须产生一个数组值。左侧表达式的结果使用操作符对数组表达式的每一行结果都进行计算和比较，比较结果必须是布尔值。

如果所有的比较结果都为真值（包括数组不含任何元素的情况），则ALL的结果为true。
如果存在一个或多个比较结果为假值，则ALL的结果为false。
如果数组表达式产生一个NULL数组，则ALL的结果为NULL。如果左边表达式的值为NULL ，则ALL的结果通常也为NULL(某些不严格的比较操作符可能得到不同的结果)。另外，如果右边的数组表达式中包含null元素并且比较结果没有假值，则ALL的结果将是NULL(某些不严格的比较操作符可能得到不同的结果)， 而不是真。这样的处理方式和SQL返回空值的布尔组合规则是一致的。

openGauss=# SELECT 8000+500 < ALL (array[10000,9000]) AS RESULT;
  result
----------
 t