PostgreSQL - 运算符

PostgreSQL - 运算符 首页 / PostgreSQL入门教程 / PostgreSQL - 运算符

运算符是保留字或字符,主要用于PostgreSQL语句的WHERE子句中以执行操作,如比较和算术运算。

运算符用于指定PostgreSQL语句中的条件,并用作语句中多个条件的结合。

  • 算术运算符
  • 比较运算符
  • 逻辑运算符
  • 按位运算符

PostgreSQL算术运算符

假设变量 a =2,变量 b =3,则-

链接:https://www.learnfk.comhttps://www.learnfk.com/postgresql/postgresql-operators.html

来源:LearnFk无涯教程网

运算符描述
示例
+相加 a + b=5
-相减 a-b=-1
*相乘 a * b=6
/相除 b/a=1
取模(相除后的余数) b%a=1
^ a ^ b=8
|/平方根 |/25.0=5
||/立方根 ||/27.0=3
!阶乘 5!=120
!!阶乘(前缀运算符) !!5=120

这是显示PostgreSQL算术运算符用法的简单示例-

testdb=# select 2+3;
 ?column?
----------
        5
(1 row)


testdb=# select 2*3;
 ?column?
----------
        6
(1 row)


testdb=# select 10/5;
 ?column?
----------
        2
(1 row)


testdb=# select 12%5;
 ?column?
----------
        2
(1 row)


testdb=# select 2^3;
 ?column?
----------
        8
(1 row)


testdb=# select |/ 25.0;
 ?column?
----------
        5
(1 row)


testdb=# select ||/ 27.0;
 ?column?
----------
        3
(1 row)


testdb=# select 5 !;
 ?column?
----------
      120
(1 row)


testdb=# select !!5;
 ?column?
----------
      120
(1 row)

PostgreSQL比较运算符

假设变量a=10,变量b=20,则-

运算符描述示例
=相等(a=b) is not true.
!=不相等(a != b) is true.
<>不相等(a <> b) is true.
>大于(a > b) is not true.
<小于(a < b) is true.
>=大于或等于(a >= b) is not true.
<=小于或等于(a <= b) is true.

考虑具有以下记录的表COMPANY-

testdb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下SELECT语句列出了SALARY大于50,000.00的所有记录-

testdb=# SELECT * FROM COMPANY WHERE SALARY > 50000;

上面给出的PostgreSQL语句将产生以下结果-

 id | name  | age |address    | salary
----+-------+-----+-----------+--------
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(2 rows)

以下SELECT语句列出了SALARY等于20,000.00的所有记录-

testdb=#  SELECT * FROM COMPANY WHERE SALARY = 20000;

上面给出的PostgreSQL语句将产生以下结果-

  id | name  | age |  address    | salary
 ----+-------+-----+-------------+--------
   1 | Paul  |  32 | California  |  20000
   3 | Teddy |  23 | Norway      |  20000
(2 rows)

以下SELECT语句列出了SALARY不等于20,000.00的所有记录-

testdb=#  SELECT * FROM COMPANY WHERE SALARY != 20000;

上面给出的PostgreSQL语句将产生以下结果-

 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(5 rows)

以下SELECT语句列出了SALARY不等于20,000.00的所有记录-

testdb=# SELECT * FROM COMPANY WHERE SALARY <> 20000;

上面给出的PostgreSQL语句将产生以下结果-

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

以下SELECT语句列出了SALARY大于或等于65,000.00的所有记录-

testdb=# SELECT * FROM COMPANY WHERE SALARY >= 65000;

上面给出的PostgreSQL语句将产生以下结果-

 id | name  | age |  address  | salary
----+-------+-----+-----------+--------
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(2 rows)

PostgreSQL逻辑运算符

这是PostgresQL中可用的所有逻辑运算符的列表。

S. No.运算符& 描述
1

AND

AND运算符允许PostgresSQL语句的WHERE子句中存在多个条件。

2

NOT

NOT运算符颠倒了使用它的逻辑运算符的含义。

3

OR

OR运算符用于在PostgresSQL语句的WHERE子句中组合多个条件。

考虑具有以下记录的表COMPANY-

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

这是显示PostgreSQL LOGICAL运算符用法的简单示例,以下SELECT语句列出了AGE大于或等于25且(AND)薪金大于或等于65000.00的所有记录。

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;

上面给出的PostgreSQL语句将产生以下结果-

 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
  9 | James |  44 | Norway      |   5000
 10 | James |  45 | Texas       |   5000
(10 rows)

以下SELECT语句列出了AGE大于或等于25或(OR)工资大于或等于65000.00的所有记录-

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500;

上面给出的PostgreSQL语句将产生以下结果-

 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
  9 | James |  44 | Norway      |   5000
 10 | James |  45 | Texas       |   5000
(10 rows)

以下SELECT语句列出了AGE不为NULL的所有记录,这意味着所有记录,因为没有记录的AGE等于NULL-

testdb=#  SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;

上面给出的PostgreSQL语句将产生以下结果-

 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
  9 | James |  44 | Norway      |   5000
 10 | James |  45 | Texas       |   5000
(10 rows)

PostgreSQL位串运算符

按位运算符对位进行运算并执行逐位操作。&和|的真值表如下-

pqp & qp | q
0000
0101
1111
1001

假设A=60;和B=13;现在以二进制格式,它们将如下所示-

A=0011 1100

B=0000 1101

-----------------

A&B=0000 1100

A | B=0011 1101

A=1100 0011

下表列出了PostgreSQL支持的按位运算符-

运算符描述示例
&位 AND 操作(A & B)=12  =》  0000 1100
|位 OR 操作(A | B)=61 =》0011 1101
~位补码操作(~A )=-61 
<<位左移A << 2=240 =》 1111 0000
>>位右移A >> 2=15 =》 0000 1111
#按位异或。A # B =49 =》 0100 1001

这是显示PostgreSQL按位运算符用法的简单示例。假设变量A=60,变量B=13,然后-

testdb=# select 60 | 13;
 ?column?
----------
       61
(1 row)


testdb=# select 60 & 13;
 ?column?
----------
       12
(1 row)


testdb=#  select  (~60);
 ?column?
----------
      -61
(1 row)


testdb=# select  (60 << 2);
 ?column?
----------
      240
(1 row)


testdb=# select  (60 >> 2);
 ?column?
----------
       15
(1 row)


testdb=#  select 60 # 13;
 ?column?
----------
       49
(1 row)

祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)

技术教程推荐

技术领导力实战笔记 -〔TGO鲲鹏会〕

技术管理实战36讲 -〔刘建国〕

10x程序员工作法 -〔郑晔〕

黄勇的OKR实战笔记 -〔黄勇〕

TypeScript开发实战 -〔梁宵〕

大厂广告产品心法 -〔郭谊〕

React Native 新架构实战课 -〔蒋宏伟〕

深入浅出可观测性 -〔翁一磊〕

AI大模型之美 -〔徐文浩〕

好记忆不如烂笔头。留下您的足迹吧 :)