PostgreSQL基础知识之SQL操作符实践指南

2024-09-28 08:41:22

前言

操作符是数据库具有的运算特性，对文本字符和一些标签字符的处理，使用操作符可以简化SQL代码，同时也可以节省开发时间和提高开发效率。

比较操作符

在PostgreSQL中，所有的数据类型都可以使用比较操作符，比较操作符运算结果返回一个布尔值。即 TRUE 或者 FALSE。比较操作符为双目操作符，因此被比较的两个值的数据类型必须是相同的数据类型或者在彼此之间可以进行隐式类型转换。在PostgreSQL中，提供的比较操作符有 > 、< 、<= 、>= 、= 、<> 或者 != 。在 PostgreSQL中，类似 a > b > c的操作是非法的，因为布尔值和数值之间无法做比较。

示例：

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中，除了比较的操作符以外，还有比较的谓词。有如下。

hrdb=> --BETWEEN AND谓词，表示范围。用法为某个指定的字符是否在此之间
hrdb=> SELECT 5 BETWEEN 1 AND 6 AS result;
 result
--------
 t
(1 row)

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; 将会返回FALSE
hrdb=> --因此要想返回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 返回TRUE
hrdb=> 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返回FALSE
hrdb=> 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 判断是否为TRUE
hrdb=> 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 判断是否为 FALSE
hrdb=> SELECT 'A' > 'B' IS FALSE AS result;
 result
--------
 t
(1 row)

hrdb=> --IS NOT FALSE 判断是否不为FALSE
hrdb=> 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值的总个数。如下：

hrdb=> SELECT num_nonnulls(1,null,2) AS total_nonnulls;
 total_nonnulls
----------------
    2
(1 row)

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。

对于逻辑操作符的之间的运算结果参考下表：

示例：

hrdb=> --2 > 1 为 TRUE, 'a' < 'b' 为TRUE，那么 AND 返回TRUE
hrdb=> SELECT 2 > 1 AND 'a' < 'b' AS result;
 result
--------
 t
(1 row)

hrdb=> --1 < 2 为 TRUE, 2 > 1为FALSE，那么 AND 的结果为 FALSE
hrdb=> SELECT 1 < 2 AND 2 > 1 AS result;
 result
--------
 t
(1 row)

hrdb=> -- NULL 和 2 > 1 为TRUE做比较，那么 AND 返回NULL
hrdb=> 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 返回FALSE
hrdb=> 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 返回TRUE
hrdb=> SELECT 2 > 1 AND 'a' < 'b' AS result;
 result
--------
 t
(1 row)

hrdb=> --1 < 2 为 TRUE, 2 > 1为FALSE，那么 OR 的结果为 TRUE
hrdb=> SELECT 1 < 2 AND 2 > 1 AS result;
 result
--------
 t
(1 row)

hrdb=>
hrdb=> -- NULL 和 2 > 1 为TRUE做比较，那么 OR 返回 TRUE
hrdb=> SELECT NULL AND 2 > 1 AS result;
 result
--------

(1 row)

hrdb=> --2 < 1 为 FALSE,'b' < 'a' 为FALSE，那么 OR 返回 FALSE
hrdb=> SELECT 2 < 1 AND 'b' < 'a' AS result;
 result
--------
 f
(1 row)

hrdb=> -- NULL 和 1 > 2 为FALSE做比较，那么 OR 返回 NULL
hrdb=> SELECT NULL AND 1 > 2 AS result;
 result
--------
 f
(1 row)

hrdb=> --NULL 和 NULL 那么 OR 的结果为 NULL
hrdb=> SELECT NULL AND NULL AS result;
 result
--------

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

算数操作符

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

示例：

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

hrdb=> --%取余操作符,5 除以 3 ,返回余数 2
hrdb=> SELECT 5 % 3 AS result;
 result
--------
  2
(1 row)

hrdb=> --^求幂操作,3 的 4次方为81
hrdb=> SELECT 3^4 AS result;
 result
--------
  81
(1 row)

hrdb=> --|/平方根,25的平方根为 5
hrdb=> SELECT |/ 25 AS result;
 result
--------
  5
(1 row)

hrdb=> --||/立方根,27 的立方根为 3
hrdb=> SELECT ||/ 27 AS result;
  result
--------------------
 3.0000000000000004
(1 row)

hrdb=> --!阶乘后缀,5的阶乘为 120
hrdb=> SELECT 5 ! AS result;
 result
--------
 120
(1 row)

hrdb=> --!!阶乘前缀,5的阶乘为 120
hrdb=> SELECT !! 5 AS result;
 result
--------
 120
(1 row)

hrdb=> --@绝对值,-1的绝对值为 1
hrdb=> SELECT @ -1 AS result;
 result
--------
  1
(1 row)

hrdb=> --& 按位与，5 & 3的值为 1
hrdb=> --计算过程
hrdb=> --5 用二进制表示 0000 0101
hrdb=> --3 用二进制表示 0000 0011
hrdb=> --按位与操作,同时为1则为1，否则为0
hrdb=> --按位与操作,同时为1则为1，否则为0
hrdb=> -- 5 & 3   0000 0001 再转换为10进制为 1
hrdb=> SELECT 5 & 3 AS result;
 result
--------
  1
(1 row)

hrdb=> --| 按位或，6 | 3 的值为 7
hrdb=> --计算过程
hrdb=> --6 用二进制表示 0000 0110
hrdb=> --3 用二进制表示 0000 0011
hrdb=> --按位或操作，只要有一个为1则为1
hrdb=> --6 | 3   0000 0111 转换为10进制为7
hrdb=> SELECT 6 | 3 AS result;
 result
--------
  7
(1 row)

hrdb=> --# 按位异或，8 # 5 的值为13
hrdb=> --计算过程
hrdb=> --8 用二进制表示 0000 1000
hrdb=> --5 用二进制表示 0000 0101
hrdb=> --按位异或操作,不同为1，相同为0
hrdb=> --8 # 5   0000 1101 转换为10进制为 13
hrdb=> SELECT 8 # 5 AS result;
 result
--------
  13
(1 row)

hrdb=> --~按位取反,7 按位取反的值为 16
hrdb=> --计算过程
hrdb=> --7 用二进制表示 0000 0111
hrdb=> --按位取反,最高位为0 取 1表示符号,其余按位取反
hrdb=> --~7    1000 1000 转换为10进制为-8
hrdb=> SELECT ~7 AS result;
 result
--------
  -8
(1 row)

hrdb=> --<<左移，4 左移 2位的值为:
hrdb=> --计算过程
hrdb=> --4用二进制表示 0000 0100
hrdb=> --4左移两位 000001 0000 位数不足用 0 补位
hrdb=> --转换为10进制后为 16。因此左移类似于平方操作，但是效率比平方高
hrdb=> SELECT 4 << 2 AS result;
 result
--------
  16
(1 row)

hrdb=> -->>右移，8 右移 3 位的值为1
hrdb=> --8 用二进制表示 0000 1000
hrdb=> --8 >> 3 ，   0 0001000 多出的0去掉转换为10进制，为1
hrdb=> SELECT 8 >> 3 AS result;
 result
--------
  1
(1 row)

总结

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

到此这篇关于PostgreSQL基础知识之SQL操作符实践的文章就介绍到这了,更多相关PostgreSQL之SQL操作符实践内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

PostgreSQL教程(七)：函数和操作符详解（3）

九.序列操作函数: 序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表.一个序列对象通常用于为行或者表生成唯一的标识符.下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法. 函数返回类型描述 nextval(regclass) bigint 递增序列对象到它的下一个数值并且返回该值.这个动作是自动完成的.即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值. currval(regclass) bigint 在当
PostgreSQL教程(五)：函数和操作符详解（1）

一.逻辑操作符: 常用的逻辑操作符有:AND.OR和NOT.其语义与其它编程语言中的逻辑操作符完全相同. 二.比较操作符: 下面是PostgreSQL中提供的比较操作符列表: 比较操作符可以用于所有可以比较的数据类型.所有比较操作符都是双目操作符,且返回boolean类型.除了比较操作符以外,我们还可以使用BETWEEN语句,如: a BETWEEN x AND y 等效于 a >= x AND a <= y a NOT BETWEEN x AND y 等效于 a <
PostgreSQL教程(六)：函数和操作符详解（2）

六.模式匹配: PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式. 1. LIKE: 复制代码代码如下: string LIKE pattern [ ESCAPE escape-character ] string NOT LIKE pattern [ ESCAPE escape-character ] 每个pattern定义一个字串的集合.如果该string包含在pattern代
PostgreSQL基础知识之SQL操作符实践指南

前言操作符是数据库具有的运算特性,对文本字符和一些标签字符的处理,使用操作符可以简化SQL代码,同时也可以节省开发时间和提高开发效率. 比较操作符在PostgreSQL中,所有的数据类型都可以使用比较操作符,比较操作符运算结果返回一个布尔值.即 TRUE 或者 FALSE.比较操作符为双目操作符,因此被比较的两个值的数据类型必须是相同的数据类型或者在彼此之间可以进行隐式类型转换.在PostgreSQL中,提供的比较操作符有 > .< .<= .>= .= .<> 或
sql注入之必备的基础知识

什么是SQL注入(SQL Injection) 所谓SQL注入式攻击,就是攻击者把SQL命令插入到Web表单的输入域或页面请求的查询字符串,欺骗服务器执行恶意的SQL命令.在某些表单中,用户输入的内容直接用来构造(或者影响)动态SQL命令,或作为存储过程的输入参数,这类表单特别容易受到SQL注入式攻击. mysql常用注释 # --[空格]或者是--+ /*-*/ 在注意过程中,这些注释可能都需要进行urlencode. mysql认证绕过 ;%00 ' or 1=1 # ' /*!or */
PHP内核介绍及扩展开发指南—基础知识

一. 基础知识本章简要介绍一些Zend引擎的内部机制,这些知识和Extensions密切相关,同时也可以帮助我们写出更加高效的PHP代码. 1.1 PHP变量的存储 1.1.1 zval结构 Zend使用zval结构来存储PHP变量的值,该结构如下所示: 复制代码代码如下: typedef union _zvalue_value { long lval; /* long value */ double dval; /* double value */ struct { char *val;
快速学习MySQL基础知识

这篇文章主要梳理了 SQL 的基础用法,会涉及到以下方面内容: SQL大小写的规范数据库的类型以及适用场景 SELECT 的执行过程 WHERE 使用规范 MySQL 中常见函数子查询分类如何选择合适的 EXISTS 和 IN 子查询了解 SQL SQL 是我们用来最长和数据打交道的方式之一,如果按照功能划分可分为如下 4 个部分: DDL,数据定义语言.定义数据库对象,数据表,数据列.也就是,对数据库和表结构进行增删改操作. DML,数据操作语言.对数据表的增删改. DCL,数据控制语
PHP小白必须要知道的php基础知识(超实用)

很多人看到PHP就以为是程序员,就以为钱很多(虽然是事实),但是也要考虑下自己是不是适合这一行,知道PHP是什么吗?PHP都有什么样的功能,都能用来干嘛? PHP是什么? •PHP(PHP: Hypertext Preprocessor,超文本预处理器的缩写),是一种被广泛应用的开放源代码的.基于服务器端的用于产生动态网页的.可嵌入HTML中的脚本程序语言,尤其适合 WEB 开发. •当客户端向服务器的程序提出请求时,web服务器根据请求晌应对应的页面,当页面中含有php脚本时,服务器会交
Lua中函数与面向对象编程的基础知识整理

函数 1. 基础知识调用函数都需要写圆括号,即使没有参数,但有一种特殊例外:函数若只有一个参数且参数是字面字符串或table构造式,则圆括号可有可无,如dofile 'a.lua',f{x=10, y=20}. Lua为面向对象式的调用提供冒号操作符的特殊语法,如o.foo(o, x)等价于o:foo(x).和Javascript类似,调用函数时提供的实参数量可以与形参数量不同,若实参多了则舍弃,不足则多余的形参初始化为nil. 1.1 多重返回值 Lua允许函数返回多个结果,函数返回如ret
JavaScript基础知识及常用方法总结

JAVASCRIPT是AJAX技术中不可或缺的一部分,所以想学好AJAX以及现在流行的AJAX框架,学好JAVASCRIPT是最重要的. 一.基础知识: 1.document.write(""); 输出语句 2.JS中的注释为// 3.传统的HTML文档顺序是:document->html->(head,body) 4.一个浏览器窗口中的DOM顺序是:window->(navigator,screen,history,location,document) 5.得到表单
Java基础知识汇总

Java基础知识 1.Java语言的优点: 1)Java是纯面向对象语言 2)与平台无关性,一次编译到处运行 3)Java提供了狠多内置类库 4)提供了对web应用的支持 5)具有较好的安全性(数组边界检测.Bytecode检测)和健壮性(强制型机制.垃圾回收器.异常处理) 6)去除c++难以理解的一些特性(头文件指针运算符重载多重继承) 2.java与c++的异同: 1)Java为解释型语言,c++为编译型语言,java会慢但是跨平台 2)Jave为纯面向对象,c++既面向对象又能面向过
Python 专题三字符串的基础知识

在Python中最重要的数据类型包括字符串.列表.元组和字典等.该篇主要讲述Python的字符串基础知识. 一.字符串基础字符串指一有序的字符序列集合,用单引号.双引号.三重(单双均可)引号引起来.如: s1='www.csdn.NET' s2="www.csdn.Net" s3='''aaabbb''' 其中字符串又包括: 1.转义字符串像C语言中定义了一些字母前加"\"来表示常见的那些不能显示的ASCII字符,python也有转义字符.如下: \
ASP基础知识Command对象讲解

Coonamd 对象定义了将对数据源执行的命令,可以用于查询数据库表并返回一个记录集,也可以用于对数据库表进行添加.更改和删除操作. 一.使用Command 对象的步骤: 当在 ASP 页面中使用 Command 对象处理数据时,应首先设置命令类型.命令文本以及相关的活动数据库连接等,并通过 Parameter 对象传递命令参数,然后通过调用 Execute 方法来执行 SQL 语句或调用存储过程,以完成数据库记录的检索.添加.更改和删除任务.其步骤如下: 1.使用 ActiveCommand

PostgreSQL基础知识之SQL操作符实践指南

相关推荐

随机推荐