PostgreSQL基础知识之SQL操作符实践指南
作者:晟数学院 发布时间:2024-01-19 18:31:34
前言
操作符是数据库具有的运算特性,对文本字符和一些标签字符的处理,使用操作符可以简化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类型之间是否被包含)操作符。网络地址操作符如<<=(表示包含于或者等于),>>=(包含或者等于)。几何操作符如@-@(表示图形的周长或者长度),@@(表示圆心),<->(表示圆心距),&&(表示图形是否重叠)等,感兴趣的同学下去自行验证。
来源:https://segmentfault.com/a/1190000022736650
猜你喜欢
- 在二维矩阵间的运算:class torch.nn.Conv2d(in_channels, out_channels, kernel_size
- 数据处理在现代企业运营中变得越来越重要,越来越关键,甚至会成为企业发展的一项瓶颈. 数据保护的重要性也不言而喻. 如果一个企业没有很好的数据
- Python下载Python最新源码,二进制文档,新闻资讯等可以在Python的官网查看到:Python官网:http://www.pyth
- <% dim week_ymd(8) '测出可以手动设定日期,比如this_ymd=#2008-04-1
- 看代码~from django.contrib import messages messages.info(request, '要显
- 请先看看以下演示中的图案文字。这可不是图片效果,而是用CSS滤镜中的Chroma() 语句做成的文本文
- 前言最近,我已经成功将我的个人网站从 Flask 迁移到 Django 了,最早接触 Django 的时候大概是在 4 年前,我记得那个时候
- 本文讲述了Symfony核心类。分享给大家供大家参考,具体如下:Symfony的核心类Symfony的MVC方式使用了一些你以后会经常碰到的
- 下面是Sql Server 和 Access 操作数据库结构的常用Sql,如如何新建一个表,如何删除表,如何删除数据,插入数据等等..希望对
- 最近ChatGpt异常火热,访问网站经常会出现1020的错误代码,或无法访问。大概率是因为服务器压力大,网关返回的1020错误码,只能等待官
- 本文实例为大家分享了python实现石头剪刀布的具体代码,供大家参考,具体内容如下老师布置了一个石头剪刀布的作业,要可视化,还是先用代码实现
- readlines的帮助信息>>> fr=open('readme.txt')>>> h
- 有时候需要在终端显示彩色的字符,即根据需要显示不同颜色的字符串,比如我们要在终端打印一行错误提示信息,要把它弄成红色的。其实这个在Pytho
- 背景重装系统,发现之前装在E盘的python可以直接使用,就只是将python的安装目录加入到环境变量中,也一直没有管它,今天跟天软交互的时
- python 2.7.11django 1.8.4错误内容:related Field has invalid lookup: iconta
- 图画效果:代码分享:整体由四部分组成:红日、小鸟、大小船、水面from turtle import * #导入tu
- 仿射密码Affine Cipher是Multiplicative Cipher和Caesar Cipher算法的组合.仿射密码的基本实现如下
- 一: 删除LOG1:分离数据库 企业管理器->服务器->数据库->右键->分离数据库2:删除LOG文件3:附加数据库 企业管理器->服务器-
- 经常看到说正则的文章,但说的只是方法,却很少有说以下几个基本概念: 1.贪婪:+,*,?,{m,n}等默认是贪婪匹配,即尽可能多匹配,也叫最
- 字典(dict)对象是 Python 最常用的数据结构,社区曾有人开玩笑地说:"Python企图用字典装载整个世界",字