PostgreSQL之SQL操作符介绍及实践

2020 年 5 月 25 日 by Leave a Comment



    • 操作符是数据库具有的运算特性，对文本字符和一些标签字符的处理，使用操作符可以简化SQL代码，同时也可以节省开发时间和提高开发效率。
    比较操作符
    在PostgreSQL中，所有的数据类型都可以使用比较操作符，比较操作符运算结果返回一个布尔值。即 TRUE 或者 FALSE。比较操作符为双目操作符，因此被比较的两个值的数据类型必须是相同的数据类型或者在彼此之间可以进行隐式类型转换。在PostgreSQL中，提供的比较操作符有 > 、< 、<= 、>= 、= 、<> 或者 != 。在 PostgreSQL中，类似 a > b > c的操作是非法的，因为布尔值和数值之间无法做比较。
    示例：
    <code><span class="code-snippet_outer">hrdb=<span class="code-snippet__comment"># SELECT 1 > 2 AS result;</span></span><span class="code-snippet_outer"> result </span><span class="code-snippet_outer">--------</span><span class="code-snippet_outer"> f</span><span class="code-snippet_outer">(1 row)</span><span class="code-snippet_outer">
hrdb=# SELECT 2 > 1 AS result; result -------- t(1 row)
hrdb=# SELECT 'a' = 'a' AS result; result -------- t(1 row)
hrdb=# SELECT 'a' <> 'b' AS result; result -------- t(1 row)
hrdb=# SELECT 'a' != 'b' AS result; result -------- t(1 row)
hrdb=# SELECT '@' > '!' AS result; result -------- t(1 row)
hrdb=# SELECT ascii('@'),ascii('!'); ascii | ascii -------+-------    64 |    33(1 row)
hrdb=# SELECT '@' > 'a' AS result; result -------- f(1 row)
hrdb=# SELECT ascii('@'),ascii('a'); ascii | ascii -------+-------    64 |    97
    在PostgreSQL中，除了比较的操作符以外，还有比较的谓词。有如下。
    <code><span class="code-snippet_outer">hrdb=> --BETWEEN <span class="code-snippet__keyword">AND</span>谓词，表示范围。用法为某个指定的字符是否在此之间</span><span class="code-snippet_outer">hrdb=> SELECT <span class="code-snippet__number">5</span> BETWEEN <span class="code-snippet__number">1</span> <span class="code-snippet__keyword">AND</span> <span class="code-snippet__number">6</span> <span class="code-snippet__keyword">AS</span> result;</span><span class="code-snippet_outer"> result </span><span class="code-snippet_outer">--------</span><span class="code-snippet_outer"> t</span><span class="code-snippet_outer">(<span class="code-snippet__number">1</span> row)</span><span class="code-snippet_outer">
hrdb=> --上述等价于hrdb=> SELECT 5 > 1 AND 5 < 6 AS result; result -------- t(1 row)
hrdb=> --NOT BETWEEN AND ,表示不在某个范围hrdb=> SELECT 5 NOT BETWEEN 7 AND 10 AS result; result -------- t(1 row)
hrdb=> --BETWEEN SYMMETRIC AND，表示排序后比较hrdb=> --该谓词的用法通常发生在BETWEEN AND之间出现倒序的情况hrdb=> --如使用 SELECT 5 BETWEEN 10 AND 10; 将会返回FALSEhrdb=> --因此要想返回TRUE需要按照如下使用hrdb=> SELECT 5 BETWEEN SYMMETRIC 10 AND 1 AS result; result -------- t(1 row)
hrdb=> --NOT BETWEEN SYMMETRIC AND与上述 BETWEEN SYMMETRIC AND 相反hrdb=> --IS DISTINCT FROM 不等于，null 返回TRUEhrdb=> SELECT 'a' IS DISTINCT FROM 'b' AS result; result -------- t(1 row)
hrdb=> SELECT null IS DISTINCT FROM 'b' AS result; result -------- t(1 row)
hrdb=> --IS NOT DISTINCT FROM 等于，null返回FALSEhrdb=> SELECT 'a' IS NOT DISTINCT FROM 'b' AS result; result -------- f(1 row)
hrdb=> SELECT null IS NOT DISTINCT FROM 'b' AS result; result -------- f(1 row)
hrdb=> --IS NULL 判断为空hrdb=> SELECT 'a' IS NULL AS result; result -------- f(1 row)
hrdb=> SELECT '' IS NULL AS result; result -------- f(1 row)
hrdb=> --IS NOT NULL 判断不为空hrdb=> SELECT 'a' IS NOT NULL AS result; result -------- t(1 row)
hrdb=> SELECT '' IS NOT NULL AS result; result -------- t(1 row)
hrdb=> --ISNULL 判断为空 (非标准语法)hrdb=> --NOTNULL 判断不为空(非标准语法)hrdb=> SELECT 'a' ISNULL as result; result -------- f(1 row)
hrdb=> SELECT '' NOTNULL AS result; result -------- t(1 row)
hrdb=> --IS TRUE 判断是否为TRUEhrdb=> SELECT 'A' > 'B' IS TRUE AS result; result -------- f(1 row)
hrdb=> --IS NOT TRUE 判断是否为FALSE hrdb=> SELECT 'A' > 'B' IS NOT TRUE AS result; result -------- t(1 row)
hrdb=> --IS FALSE 判断是否为 FALSEhrdb=> SELECT 'A' > 'B' IS FALSE AS result; result -------- t(1 row)
hrdb=> --IS NOT FALSE 判断是否不为FALSEhrdb=> SELECT 'A' > 'B' IS NOT FALSE AS result; result -------- f(1 row)--IS UNKNOWN 未知hrdb=> SELECT NULL IS UNKNOWN AS result; result -------- t--IS NOT UNKNOWN SELECT 'a' > 'b' IS NOT UNKNOWN AS result;
    温馨提示

    字符之间的比较通常使用ascii值比较大小。同时，UNKNOWN 和 NOT UNKNOWN 通常和IS NULL 和IS NOT NULL类似。在PostgreSQL中，还有比较函数， num_nonnulls(VARIADIC "any"),表示返回一个非空输入字符的总个数。num_nulls(VARIADIC "any"),表示返回一个null值的总个数。如下：
    <code><span class="code-snippet_outer">hrdb=> SELECT num_nonnulls(<span class="code-snippet__number">1</span>,<span class="code-snippet__keyword">null</span>,<span class="code-snippet__number">2</span>) <span class="code-snippet__keyword">AS</span> total_nonnulls;</span><span class="code-snippet_outer"> total_nonnulls </span><span class="code-snippet_outer">----------------</span><span class="code-snippet_outer">              <span class="code-snippet__number">2</span></span><span class="code-snippet_outer">(<span class="code-snippet__number">1</span> row)</span><span class="code-snippet_outer">
hrdb=> SELECT num_nonnulls('PostgreSQL','MySQL','','null',null) AS total_nonnulls; total_nonnulls ----------------              4(1 row)
hrdb=> SELECT num_nulls(1,null,2) AS total_nulls; total_nulls -------------           1(1 row)
hrdb=> SELECT num_nulls('PostgreSQL','MySQL','','null',null) AS total_nulls; total_nulls -------------           1
    逻辑操作符

    在PostgreSQL中，逻辑操作符有 AND、 OR 和 NOT，逻辑操作符返回的结果通常为一个布尔值或者NULL，即 TRUE、FALSE或者NULL。其中NULL表示运算结果未知。同时对于逻辑操作符都有优先级顺序，AND 、OR 和 NOT的优先级顺序为: NOT > AND >OR。
    对于逻辑操作符的之间的运算结果参考下表：

    示例：

    <code><span class="code-snippet_outer">hrdb=> -<span class="code-snippet__number">-2</span> > <span class="code-snippet__number">1</span> 为 <span class="code-snippet__keyword">TRUE</span>, <span class="code-snippet__string">'a'</span> < <span class="code-snippet__string">'b'</span> 为<span class="code-snippet__keyword">TRUE</span>，那么 <span class="code-snippet__keyword">AND</span> 返回<span class="code-snippet__keyword">TRUE</span></span><span class="code-snippet_outer">hrdb=> SELECT <span class="code-snippet__number">2</span> > <span class="code-snippet__number">1</span> <span class="code-snippet__keyword">AND</span> <span class="code-snippet__string">'a'</span> < <span class="code-snippet__string">'b'</span> <span class="code-snippet__keyword">AS</span> result;</span><span class="code-snippet_outer"> result </span><span class="code-snippet_outer">--------</span><span class="code-snippet_outer"> t</span><span class="code-snippet_outer">(<span class="code-snippet__number">1</span> row)</span><span class="code-snippet_outer">
hrdb=> --1 < 2 为 TRUE, 2 > 1为FALSE，那么 AND 的结果为 FALSEhrdb=> SELECT 1 < 2 AND 2 > 1 AS result; result -------- t(1 row)
hrdb=> -- NULL 和 2 > 1 为TRUE做比较，那么 AND 返回NULLhrdb=> SELECT NULL AND 2 > 1 AS result; result -------- (1 row)
hrdb=> --2 < 1 为 FALSE,'b' < 'a' 为FALSE，那么 AND 返回 FALSE hrdb=> SELECT 2 < 1 AND 'b' < 'a' AS result; result -------- f(1 row)
hrdb=> -- NULL 和 1 > 2 为FALSE做比较，那么 AND 返回FALSEhrdb=> SELECT NULL AND 1 > 2 AS result; result -------- f(1 row)
hrdb=> --NULL 和 NULL AND 的结果为 NULL hrdb=> SELECT NULL AND NULL AS result; result -------- (1 row)hrdb=> --2 > 1 为 TRUE, 'a' < 'b' 为TRUE，那么 OR 返回TRUEhrdb=> SELECT 2 > 1 AND 'a' < 'b' AS result; result -------- t(1 row)
hrdb=> --1 < 2 为 TRUE, 2 > 1为FALSE，那么 OR 的结果为 TRUEhrdb=> SELECT 1 < 2 AND 2 > 1 AS result; result -------- t(1 row)
hrdb=> hrdb=> -- NULL 和 2 > 1 为TRUE做比较，那么 OR 返回 TRUEhrdb=> SELECT NULL AND 2 > 1 AS result; result -------- (1 row)
hrdb=> --2 < 1 为 FALSE,'b' < 'a' 为FALSE，那么 OR 返回 FALSEhrdb=> SELECT 2 < 1 AND 'b' < 'a' AS result; result -------- f(1 row)
hrdb=> -- NULL 和 1 > 2 为FALSE做比较，那么 OR 返回 NULLhrdb=> SELECT NULL AND 1 > 2 AS result; result -------- f(1 row)
hrdb=> --NULL 和 NULL 那么 OR 的结果为 NULLhrdb=> SELECT NULL AND NULL AS result; result --------

    关于 NOT 结果，在这里不做示例，有兴趣的可以下去自行验证。

    算数操作符

    PostgreSQL中的算数操作符号有 +（加法） 、-（减法） 、* （乘法）、/（除法） 、%（取余）、^（幂运算符）、|/（平方根）、||/（立方根）、!（阶乘[后缀]）、!!（阶乘[前缀]）、@（绝对值）、&（按位与）、|（按位或）、#（按位异或）、~（按位取反）、<<（左移）、>>（右移）操作符，这些操作符简化了数学运算方式。

    示例：

    加减乘除示例在此略。此示例重点介绍一下后面的操作符号。

    <code><span class="code-snippet_outer">hrdb=> --%取余操作符,5 除以 3 ,返回余数 2</span><span class="code-snippet_outer">hrdb=> SELECT 5 % 3 AS result;</span><span class="code-snippet_outer"> result </span><span class="code-snippet_outer">--------</span><span class="code-snippet_outer">      2</span><span class="code-snippet_outer">(1 row)</span><span class="code-snippet_outer">
hrdb=> --^求幂操作,3 的 4次方为81hrdb=> SELECT 3^4 AS result; result --------     81(1 row)
hrdb=> --|/平方根,25的平方根为 5hrdb=> SELECT |/ 25 AS result; result --------      5(1 row)
hrdb=> --||/立方根,27 的立方根为 3hrdb=> SELECT ||/ 27 AS result;       result       -------------------- 3.0000000000000004(1 row)
hrdb=> --!阶乘后缀,5的阶乘为 120hrdb=> SELECT 5 ! AS result; result --------    120(1 row)
hrdb=> --!!阶乘前缀,5的阶乘为 120hrdb=> SELECT !! 5 AS result; result --------    120(1 row)
hrdb=> --@绝对值,-1的绝对值为 1hrdb=> SELECT @ -1 AS result; result --------      1(1 row)
hrdb=> --& 按位与，5 & 3的值为 1hrdb=> --计算过程hrdb=> --5 用二进制表示 0000 0101hrdb=> --3 用二进制表示 0000 0011hrdb=> --按位与操作,同时为1则为1，否则为0hrdb=> --按位与操作,同时为1则为1，否则为0hrdb=> -- 5 & 3         0000 0001 再转换为10进制为 1hrdb=> SELECT 5 & 3 AS result; result --------      1(1 row)
hrdb=> --| 按位或，6 | 3 的值为 7hrdb=> --计算过程hrdb=> --6 用二进制表示 0000 0110hrdb=> --3 用二进制表示 0000 0011hrdb=> --按位或操作，只要有一个为1则为1hrdb=> --6 | 3          0000 0111 转换为10进制为7hrdb=> SELECT 6 | 3 AS result; result --------      7(1 row)
hrdb=> --# 按位异或，8 # 5 的值为13hrdb=> --计算过程hrdb=> --8 用二进制表示 0000 1000hrdb=> --5 用二进制表示 0000 0101hrdb=> --按位异或操作,不同为1，相同为0hrdb=> --8 # 5          0000 1101 转换为10进制为 13hrdb=> SELECT 8 # 5 AS result; result --------     13(1 row)
hrdb=> --~按位取反,7 按位取反的值为 16hrdb=> --计算过程 hrdb=> --7 用二进制表示 0000 0111hrdb=> --按位取反,最高位为0 取 1表示符号,其余按位取反hrdb=> --~7             1000 1000 转换为10进制为-8hrdb=> SELECT ~7 AS result; result --------     -8(1 row)
hrdb=> --<<左移，4 左移 2位的值为:hrdb=> --计算过程hrdb=> --4用二进制表示 0000 0100hrdb=> --4左移两位   000001 0000 位数不足用 0 补位hrdb=> --转换为10进制后为 16。因此左移类似于平方操作，但是效率比平方高hrdb=> SELECT 4 << 2 AS result; result --------     16(1 row)
hrdb=> -->>右移，8 右移 3 位的值为1hrdb=> --8 用二进制表示 0000 1000hrdb=> --8 >> 3 ，         0 0001000 多出的0去掉转换为10进制，为1hrdb=> SELECT 8 >> 3 AS result; result --------      1(1 row)
    总结

    在PostgreSQL 中，除了以上三种操作符为关系型数据库中常用的操作符外，还有其它的操作符为PostgreSQL中独有的操作符，比如文本查找操作符，@@（tsvector类型与tsquery类型字符是否匹配），@>（tsquery类型之间是否包含）,<@（tsquery类型之间是否被包含）操作符。网络地址操作符如<<=（表示包含于或者等于），>>=（包含或者等于）。几何操作符如@-@（表示图形的周长或者长度）,@@（表示圆心），<->（表示圆心距）,&&（表示图形是否重叠）等，感兴趣的同学下去自行验证。
    Filed Under: PostgreSQL

    Speak Your Mind

    *

    京ICP备14059771号-2