发布于 2015-07-12 03:05:01 | 311 次阅读 | 评论: 0 | 来源: 网络整理
运算符是一个保留字或字符主要用于PostgreSQL的语句的WHERE子句中执行操作，如比较和算术运算。
运算符用于指定一个PostgreSQL表中的条件，并在一份声明中多个条件作为连词。
算术运算符
比较操作符
逻辑运算符
位运算符
假设变量a的值为2，而变量b的值为3：
下面是简单的例子，显示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)
| 运算符 | 描述 | 实例 |
|---|---|---|
| + | Addition - Adds values on either side of the operator | a + b will give 5 |
| - | Subtraction - Subtracts right hand operand from left hand operand | a - b will give -1 |
| * | Multiplication - Multiplies values on either side of the operator | a * b will give 6 |
| / | Division - Divides left hand operand by right hand operand | b / a will give 1 |
| % | Modulus - Divides left hand operand by right hand operand and returns remainder | b % a will give 1 |
| ^ | Exponentiation - This gives the exponent value of the right hand operand | a ^ b will give 8 |
| |/ | square root | |/ 25.0 will give 5 |
| ||/ | Cube root | ||/ 27.0 will give 3 |
| !/ | factorial | 5 ! will give 120 |
| !! | factorial (prefix operator) | !! 5 will give 120 |
假设变量，a变量的值为10，变量b的值为20:
考虑一下表 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 | yiibai.com | 24 | Houston2 | 10000 (7 rows)
下面是简单的例子，显示使用PostgreSQL比较操作符： yiibai.com
在这里，我们已经使用WHERE子句将在一个单独的章节解释，但现在可以明白，WHERE子句用于把一个条件语句与SELECT语句一起。
下面的SELECT语句列表的工资大于50,000.00下来的所有记录：
testdb=# SELECT * FROM COMPANY WHERE SALARY > 50000; yiibai.com
以上PostgreSQL的表会产生以下结果： yiibai.com
id | name | age |address | salary ----+-------+-----+-----------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows) yiibai.com
下面的SELECT语句了所有的记录，工资等于20,000.00列表：
testdb=# SELECT * FROM COMPANY WHERE SALARY = 20000;
以上PostgreSQL的表会产生以下结果： yiibai.com
id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 3 | Teddy | 23 | Norway | 20000 (2 rows)
下面的SELECT语句了所有的记录，工资不等于20,000.00列表：
testdb=# SELECT * FROM COMPANY WHERE SALARY != 20000; www.yiibai.com
以上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语句了所有的记录，工资不等于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语句列表的工资大于或等于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)
| 运算符 | 描述 | 例子 |
|---|---|---|
| = | Checks if the value of two operands are equal or not, if yes then condition becomes true. | (a = b) is not true. |
| != | Checks if the value of two operands are equal or not, if values are not equal then condition becomes true. | (a != b) is true. |
| <> | Checks if the value of two operands are equal or not, if values are not equal then condition becomes true. | (a <> b) is true. |
| > | Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. | (a > b) is not true. |
| < | Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. | (a < b) is true. |
| >= | Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. | (a >= b) is not true. |
| <= | Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. | (a <= b) is true. |
这里是一个所有的逻辑运算符可以在PostgreSQL中使用的列表。
考虑表 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 | yiibai.com | 24 | Houston2 | 10000 (7 rows) yiibai.com
下面是简单的例子，显示PostgreSQL的逻辑运算符的用法。年龄大于或等于25并且工资大于或等于65000.00了所有的记录，下面的SELECT语句列表：
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;
以上PostgreSQL表会产生以下结果： www.yiibai.com
id | name | age | address | salary ----+-------+-----+-----------------------------------------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (4 rows)
年龄大于或等于25或薪水大于或等于65000.00所有的记录列表，下面的SELECT语句： yiibai.com
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语句列表年龄不为NULL，这意味着所有记录下来，因为记录是没有年龄等于NULL： www.yiibai.com
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)
| 运算符 | 描述 |
|---|---|
| AND | The AND operator allows the existence of multiple conditions in a PostgresSQL statement's WHERE clause. |
| NOT | The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator. |
| OR | The OR operator is used to combine multiple conditions in a PostgresSQL statement's WHERE clause. |
位运算符位和位操作执行位。真值表＆|如下：
| p | q | p & q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
假设如果A= 60，B =13，他们现在以二进制格式将如下：
A = 0011 1100
B = 0000 1101
-----------------
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011
PostgreSQL支持位运算符下表中列出。假设变量A=60和变量B=13，那么：
下面是简单的例子显示使用PostgreSQL的位运算符：
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)
| 运算符 | 描述 | 实例 |
|---|---|---|
| & | Binary AND Operator copies a bit to the result if it exists in both operands. | (A & B) will give 12 which is 0000 1100 |
| | | Binary OR Operator copies a bit if it exists in either operand. | (A | B) will give 61 which is 0011 1101 |
| ~ | Binary Ones Complement Operator is unary and has the effect of 'flipping' bits. | (~A ) will give -60 which is 1100 0011 |
| << | Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. | A << 2 will give 240 which is 1111 0000 |
| >> | Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. | A >> 2 will give 15 which is 0000 1111 |
| # | bitwise XOR. | A # B will give 49 which is 0100 1001 |