— 基本算术运算符
SELECT
10 + 5 AS addition,
10 – 5 AS subtraction,
10 * 5 AS multiplication,
10 / 3 AS division,
10 % 3 AS modulo,
10.0 / 3 AS float_division,
2 ^ 3 AS power,
|/ 16 AS square_root,
||/ 27 AS cube_root,
5 ! AS factorial;

执行结果：

addition | subtraction | multiplication | division | modulo | float_division | power | square_root | cube_root | factorial
———-+————-+—————-+——————+——–+——————–+——-+————-+———–+———–
15 | 5 | 50 | 3.33333333333333 | 1 | 3.3333333333333333 | 8 | 4 | 3 | 120
(1 row)

一元运算符：

— 一元运算符
SELECT
-10 AS negative,
+10 AS positive,
@ -10 AS absolute_value;

执行结果：

学习交流加群风哥微信: itpux-com

negative | positive | absolute_value
———-+———-+—————-
-10 | 10 | 10
(1 row)

— 基本比较运算符
SELECT
10 = 10 AS eq,
10 != 10 AS neq1,
10 <> 10 AS neq2,
10 < 20 AS lt, 10 > 5 AS gt,
10 <= 10 AS lte, 10 >= 10 AS gte;

执行结果：

eq | neq1 | neq2 | lt | gt | lte | gte
—–+——+——+—-+—-+—–+—–
t | f | f | t | t | t | t
(1 row)

— 范围比较运算符
SELECT
5 BETWEEN 1 AND 10 AS between_result,
5 BETWEEN SYMMETRIC 10 AND 1 AS between_symmetric,
15 NOT BETWEEN 1 AND 10 AS not_between,
‘c’ BETWEEN ‘a’ AND ‘z’ AS char_between;

执行结果：

between_result | between_symmetric | not_between | char_between
—————-+——————-+————-+————–
t | t | t | t
(1 row)

— NULL比较运算符
SELECT
NULL IS NULL AS is_null,
NULL IS NOT NULL AS is_not_null,
NULL = NULL AS null_eq_null,
NULL IS DISTINCT FROM NULL AS distinct_from_null,
NULL IS NOT DISTINCT FROM NULL AS not_distinct_from_null,
1 IS DISTINCT FROM NULL AS one_distinct_null,
1 IS NOT DISTINCT FROM 1 AS one_not_distinct_one;

执行结果：

is_null | is_not_null | null_eq_null | distinct_from_null | not_distinct_from_null | one_distinct_null | one_not_distinct_one
———+————-+————–+——————–+————————+——————-+———————-
t | f | | f | t | t | t
(1 row)

— IN和EXISTS运算符
SELECT
5 IN (1, 2, 3, 4, 5) AS in_result,
6 NOT IN (1, 2, 3, 4, 5) AS not_in_result,
5 = ANY(ARRAY[1, 2, 3, 4, 5]) AS any_result,
5 = ALL(ARRAY[5, 5, 5]) AS all_result;

执行结果：

in_result | not_in_result | any_result | all_result
———–+—————+————+————
t | t | t | t
(1 row)

— 逻辑运算符
SELECT
TRUE AND TRUE AS and_tt,
TRUE AND FALSE AS and_tf,
TRUE OR FALSE AS or_tf,
FALSE OR FALSE AS or_ff,
NOT TRUE AS not_t,
NOT FALSE AS not_f;

执行结果：

and_tt | and_tf | or_tf | or_ff | not_t | not_f
——–+——–+——-+——-+——-+——-
t | f | t | f | f | t
(1 row)

NULL与逻辑运算：

— NULL与逻辑运算
SELECT
TRUE AND NULL AS and_null,
FALSE AND NULL AS and_null_false,
TRUE OR NULL AS or_null,
FALSE OR NULL AS or_null_false,
NOT NULL AS not_null;

执行结果：

风哥提示：

and_null | and_null_false | or_null | or_null_false | not_null
———-+—————-+———+—————+———-
| f | t | |
(1 row)

— 位运算符
SELECT
5 & 3 AS bitwise_and,
5 | 3 AS bitwise_or,
5 # 3 AS bitwise_xor,
~5 AS bitwise_not,
5 << 2 AS left_shift, 5 >> 1 AS right_shift;

执行结果：

bitwise_and | bitwise_or | bitwise_xor | bitwise_not | left_shift | right_shift
————-+————+————-+————-+————+————-
1 | 7 | 6 | -6 | 20 | 2
(1 row)

位运算详解：

— 位运算详解
SELECT
5::BIT(4) AS five_binary,
3::BIT(4) AS three_binary,
(5 & 3)::BIT(4) AS and_result,
(5 | 3)::BIT(4) AS or_result,
(5 # 3)::BIT(4) AS xor_result;

执行结果：

five_binary | three_binary | and_result | or_result | xor_result
————-+————–+————+———–+————
0101 | 0011 | 0001 | 0111 | 0110
(1 row)

— 字符串运算符
SELECT
‘Hello’ || ‘ ‘ || ‘World’ AS concat,
‘PostgreSQL’ LIKE ‘Post%’ AS like_result,
‘PostgreSQL’ NOT LIKE ‘%SQL’ AS not_like,
‘PostgreSQL’ ILIKE ‘post%’ AS ilike_result,
‘PostgreSQL’ SIMILAR TO ‘P%L’ AS similar_result;

执行结果：

concat | like_result | not_like | ilike_result | similar_result
—————+————-+———-+————–+—————-
Hello World | t | f | t | t
(1 row)

正则表达式运算符：

— 正则表达式运算符
SELECT
‘PostgreSQL’ ~ ‘^Post’ AS regex_match,
‘PostgreSQL’ ~* ‘^post’ AS regex_match_case,
‘PostgreSQL’ !~ ‘MySQL’ AS regex_not_match,
‘PostgreSQL’ !~* ‘mysql’ AS regex_not_match_case,
‘abc123’ ~ ‘[0-9]+’ AS regex_has_digit;

执行结果：

regex_match | regex_match_case | regex_not_match | regex_not_match_case | regex_has_digit
————-+——————+—————–+———————-+—————–
t | t | t | t | t
(1 row)

— JSON运算符
SELECT
‘{“name”: “风哥1号”, “age”: 30}’::JSONB -> ‘name’ AS json_arrow,
‘{“name”: “风哥1号”, “age”: 30}’::JSONB ->> ‘name’ AS json_text,
‘{“info”: {“name”: “风哥2号”}}’::JSONB #> ‘{info,name}’ AS json_path,
‘{“info”: {“name”: “风哥2号”}}’::JSONB #>> ‘{info,name}’ AS json_path_text,
‘{“a”: 1, “b”: 2}’::JSONB @> ‘{“a”: 1}’ AS json_contains,
‘{“a”: 1}’::JSONB <@ '{"a": 1, "b": 2}' AS json_contained;

执行结果：

更多视频教程www.fgedu.net.cn

json_arrow | json_text | json_path | json_path_text | json_contains | json_contained
—————–+———–+———–+—————-+—————+—————-
“风哥1号” | 风哥1号 | “风哥2号” | 风哥2号 | t | t
(1 row)

JSON键存在运算符：

— JSON键存在运算符
SELECT
‘{“a”: 1, “b”: 2}’::JSONB ? ‘a’ AS key_exists,
‘{“a”: 1, “b”: 2}’::JSONB ?| ARRAY[‘a’, ‘c’] AS any_key_exists,
‘{“a”: 1, “b”: 2}’::JSONB ?& ARRAY[‘a’, ‘b’] AS all_keys_exist,
‘{“a”: [1, 2, 3]}’::JSONB -> ‘a’ @> ‘[1, 2]’ AS array_contains;

执行结果：

学习交流加群风哥QQ113257174

key_exists | any_key_exists | all_keys_exist | array_contains
————+—————-+—————-+—————-
t | t | t | t
(1 row)

— 数组运算符
SELECT
ARRAY[1, 2, 3] && ARRAY[3, 4, 5] AS overlap,
ARRAY[1, 2] @> ARRAY[1] AS contains,
ARRAY[1] <@ ARRAY[1, 2] AS contained, ARRAY[1, 2] || ARRAY[3, 4] AS concat_array, ARRAY[1, 2] || 3 AS fgappend_element;

执行结果：

overlap | contains | contained | concat_array | fgappend_element
———+———-+———–+————–+—————-
t | t | t | {1,2,3,4} | {1,2,3}
(1 row)

— 创建产品表
CREATE TABLE fgedu_products_filter (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price NUMERIC(10, 2),
stock INTEGER,
status INTEGER,
tags TEXT[],
attributes JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入测试数据
INSERT INTO fgedu_products_filter (product_name, category, price, stock, status, tags, attributes) VALUES
(‘笔记本电脑’, ‘电子产品’, 5999.00, 50, 1, ARRAY[‘电脑’, ‘办公’], ‘{“brand”: “ThinkPad”, “cpu”: “i7”}’),
(‘无线鼠标’, ‘电子产品’, 99.00, 200, 1, ARRAY[‘外设’, ‘办公’], ‘{“brand”: “Logitech”, “wireless”: true}’),
(‘机械键盘’, ‘电子产品’, 399.00, 100, 1, ARRAY[‘外设’, ‘游戏’], ‘{“brand”: “Cherry”, “rgb”: true}’),
(‘显示器’, ‘电子产品’, 1599.00, 80, 0, ARRAY[‘显示’, ‘办公’], ‘{“brand”: “Dell”, “size”: “27”}’),
(‘办公椅’, ‘家具’, 899.00, 30, 1, ARRAY[‘办公’, ‘家具’], ‘{“brand”: “Herman”, “ergonomic”: true}’),
(‘书架’, ‘家具’, 299.00, 50, 1, ARRAY[‘家具’, ‘收纳’], ‘{“material”: “wood”}’),
(‘台灯’, ‘家居’, 199.00, 100, 1, ARRAY[‘照明’, ‘办公’], ‘{“type”: “LED”}’);

— 综合条件筛选
SELECT
product_id,
product_name,
category,
price,
stock,
CASE
WHEN status = 1 THEN ‘上架’
ELSE ‘下架’
END AS status_text
FROM fgedu_products_filter
WHERE status = 1
AND price BETWEEN 100 AND 2000
AND stock > 50
AND tags && ARRAY[‘办公’]
ORDER BY price;

执行结果：

product_id | product_name | category | price | stock | status_text
————+————–+———–+——–+——-+————-
2 | 无线鼠标 | 电子产品 | 99.00 | 200 | 上架
6 | 书架 | 家具 | 299.00 | 50 | 上架
7 | 台灯 | 家居 | 199.00 | 100 | 上架
(3 rows)

JSON属性筛选：

— JSON属性筛选
SELECT
product_name,
attributes ->> ‘brand’ AS brand,
price
FROM fgedu_products_filter
WHERE attributes @> ‘{“brand”: “Logitech”}’
OR attributes ? ‘wireless’;

执行结果：

product_name | brand | price
————–+———–+——–
无线鼠标 | Logitech | 99.00
(1 row)

位运算筛选：

— 位运算筛选（状态位）
— 假设status是位掩码：1=上架, 2=推荐, 4=热销
UPDATE fgedu_products_filter SET status = 3 WHERE product_id = 1;
UPDATE fgedu_products_filter SET status = 5 WHERE product_id = 2;
UPDATE fgedu_products_filter SET status = 7 WHERE product_id = 3;

SELECT
product_name,
status,
status & 1 AS is_online,
status & 2 AS is_recommended,
status & 4 AS is_hot,
CASE WHEN status & 1 > 0 THEN ‘上架’ ELSE ‘下架’ END AS online_status,
CASE WHEN status & 2 > 0 THEN ‘推荐’ ELSE ” END AS recommended,
CASE WHEN status & 4 > 0 THEN ‘热销’ ELSE ” END AS hot
FROM fgedu_products_filter
WHERE product_id <= 3;

执行结果：

product_name | status | is_online | is_recommended | is_hot | online_status | recommended | hot
————–+——–+———–+—————-+——–+—————+————-+—–
笔记本电脑 | 3 | 1 | 2 | 0 | 上架 | 推荐 |
无线鼠标 | 5 | 1 | 0 | 4 | 上架 | | 热销
机械键盘 | 7 | 1 | 2 | 4 | 上架 | 推荐 | 热销
(3 rows)

— 删除测试表
DROP TABLE IF EXISTS fgedu_products_filter;

执行结果：

更多学习教程公众号风哥教程itpux_com

DROP TABLE