PostgreSQL 知识点（含实操练习）

一、数据类型

1.1 数值类型

integer/int：整型，取值范围 -21亿 ~ 21亿，适用于存储一般数量、ID等场景；PostgreSQL 中 integer 和 int 无区别。
bigint：长整型，取值范围极大，适用于数据量超过21亿行时的自增主键。
serial/bigserial：自增整数，适用于简单主键；数据量小于21亿可使用 serial，大于21亿使用 bigserial，建议直接使用 bigserial。
numeric(p,s)：精确小数，其中 p 代表总位数，s 代表小数位数，适用于金额、财务数据等对精度要求高的场景。

补充说明：float 和 numeric 的区别——numeric 是精确数字，无精度损失，适合金额；float 是近似值，会存在精度损失。

1.2 字符类型

varchar(n)：可变长度字符串，最大长度为 n。
text：无长度限制的字符串，适用于不确定长度的文本存储。

1.3 日期/时间类型

timestamp：不带时区的时间戳。
timestamptz：带时区的时间戳。

**timestamptz**推荐使用的原因：timestamptz 存储时会自动转换为 UTC 时间，读取时根据客户端时区展示，可有效避免时区混乱问题。

1.4 其他常用类型

boolean：布尔类型，取值为 true 或 false。
jsonb：二进制 JSON 类型，支持索引，适用于灵活存储非结构化数据。
uuid：全局唯一标识符，使用时需配合 uuid-ossp 扩展。

二、TOAST 机制

TOAST 是 PostgreSQL 专门用于存储超过一个数据块的大字段的机制，可高效处理大文本、大二进制等数据。

三、约束

primary key：主键约束，兼具唯一和非空特性，一张表只能有一个主键。
unique：唯一约束，该列所有值不可重复，但允许存在多个 NULL。
not null：非空约束，禁止该列存储 NULL 值。
check：检查约束，可自定义校验条件（如 age > 0）。
default：默认值约束，当该列未插入数据时，自动填充默认值。
foreign key：外键约束，用于关联另一张表的主键，保证数据完整性。

外键删除关联动作：

on delete cascade：级联删除，当主表主键被删除时，从表关联数据也随之删除。
on delete set null：当主表主键被删除时，从表关联字段设为 NULL（需保证从表关联字段允许为 NULL）。
on delete restrict：限制删除，若主表主键存在从表关联数据，则无法删除该主键。

四、函数与表达式

4.1 字符串函数

|| 或 CONCAT()：字符串拼接。
LENGTH()：获取字符串长度。
UPPER()/LOWER()：将字符串转换为大写/小写。
SUBSTRING(string, start, length)：截取字符串，start 为起始位置，length 为截取长度。
REPLACE(string, old, new)：替换字符串中的指定内容，将 old 替换为 new。
REGEXP_MATCHES(string, pattern)：根据正则表达式匹配字符串，返回匹配结果。

4.2 日期/时间函数

--当前时间
SELECT NOW();                 -- 2025-04-02 10:23:45.123+08
SELECT CURRENT_DATE;         -- 2025-04-02
SELECT CURRENT_TIME;

-- 提取部分
SELECT EXTRACT(YEAR FROM NOW());
SELECT DATE_PART('month', NOW());

-- 截断到指定精度（常用于报表分组）
SELECT DATE_TRUNC('month', created_at) FROM orders;

-- 计算年龄
SELECT AGE('2025-04-02', '1990-01-01');  -- 35 years 3 mons 1 day

-- 日期加减
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '2 hours';

4.3 条件表达式

case when：多条件判断，语法为 case when 条件1 then 结果1 when 条件2 then 结果2 else 默认结果 end。
coalesce(value, default)：返回第一个非 NULL 值，若 value 为 NULL，则返回 default。
NULLIF(a, b)：若 a = b，则返回 NULL，否则返回 a。

五、聚合与分组

常用聚合函数（需配合 GROUP BY 分组使用，除非聚合整个表）：

count：统计行数。
sum：计算列的总和。
avg：计算列的平均值。
max：获取列的最大值。
min：获取列的最小值。
string_agg：将分组后的字符串拼接。
array_agg：将分组后的结果转为数组

六、组合练习：电商订单统计系统

6.1 建表语句

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    reg_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) CHECK (price > 0)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER CHECK (quantity > 0),
    unit_price NUMERIC(10,2)
);

6.2 写入测试数据

INSERT INTO customers (name, email, reg_date) VALUES
    ('张三', 'zhang@example.com', '2024-01-15'),
    ('李四', NULL, '2024-02-20'),
    ('王五', 'wang@example.com', '2024-03-10'),
    ('赵六', NULL, '2024-04-01'),
    ('孙七', 'sun@example.com', '2024-05-05');

INSERT INTO products (name, price) VALUES
    ('手机', 2999.00),
    ('电脑', 5999.00),
    ('耳机', 199.00),
    ('鼠标', 49.90),
    ('键盘', 299.00);

INSERT INTO orders (customer_id, order_date) VALUES
    (1, '2024-01-20 10:30:00+08'),
    (1, '2024-02-15 14:20:00+08'),
    (2, '2024-03-01 09:15:00+08'),
    (3, '2024-03-25 16:45:00+08'),
    (3, '2024-04-10 11:00:00+08'),
    (5, '2024-05-12 13:30:00+08');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 2999.00),
    (1, 3, 2, 199.00),
    (2, 2, 1, 5999.00),
    (2, 4, 1, 49.90),
    (3, 5, 2, 299.00),
    (4, 1, 1, 2999.00),
    (4, 3, 1, 199.00),
    (5, 2, 1, 5999.00),
    (5, 5, 1, 299.00),
    (5, 4, 2, 49.90),
    (6, 3, 3, 199.00);

6.3 练习题及答案

题目1：查询每个客户的订单总额

select 
c.customer_id,
c.name,
coalesce(sum(oi.quantity*oi.unit_price),0) as total
from customers c
left join orders o on  o.customer_id=c.customer_id
left join order_items oi on o.order_id=oi.order_id
group by c.customer_id,c.name
order by total desc;

题目2：查询从未下过单的客户

方法1（使用 not in）：

select name from customers
where customer_id
not in(select customer_id from orders);

方法2（使用 not exists）：

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

补充说明：not in 和 not exists 的区别

not in：若子查询返回结果包含 NULL，则整个 NOT IN 条件会变为 UNKNOWN，无法返回任何行，即使外层有匹配数据。
not exists：不受子查询中 NULL 的影响，查询效率更稳定。

建议优先使用 not exists；若子查询可能包含 NULL，优化器会生成更保守的执行计划，导致性能下降。

题目3：查询购买商品种类数超过3种的客户名称

SELECT c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT oi.product_id) > 3;

linux操作系统有网状态下安装postgres可参考： https://wdhlzy.xyz/posts/postgresql-ubuntu-install/

windows11安装postgres可参考： https://wdhlzy.xyz/posts/postgresql-install/

PostgreSQL 知识点（含实操练习）#

一、数据类型#

1.1 数值类型#

1.2 字符类型#

1.3 日期/时间类型#

1.4 其他常用类型#

二、TOAST 机制#

三、约束#

四、函数与表达式#

4.1 字符串函数#

4.2 日期/时间函数#

4.3 条件表达式#

五、聚合与分组#

六、组合练习：电商订单统计系统#

6.1 建表语句#

6.2 写入测试数据#

6.3 练习题及答案#

题目1：查询每个客户的订单总额#

题目2：查询从未下过单的客户#

题目3：查询购买商品种类数超过3种的客户名称#