数据操作

数据操作是数据库最常用的功能，主要包括插入（INSERT）、查询（SELECT）、更新（UPDATE）和删除（DELETE），简称CRUD操作。本章将详细介绍PostgreSQL中的数据操作。

插入数据

基本插入

使用 INSERT INTO 语句向表中插入数据：

-- 创建示例表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INTEGER,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入单条数据（指定列名）
INSERT INTO users (username, email, age)
VALUES ('zhangsan', 'zhangsan@example.com', 25);

-- 插入单条数据（不指定列名，按表定义顺序）
INSERT INTO users
VALUES (DEFAULT, 'lisi', 'lisi@example.com', 30, DEFAULT, DEFAULT);
-- DEFAULT表示使用列的默认值

-- 插入数据并返回插入的行
INSERT INTO users (username, email, age)
VALUES ('wangwu', 'wangwu@example.com', 28)
RETURNING *;  -- 返回所有列

-- 只返回特定列
INSERT INTO users (username, email)
VALUES ('zhaoliu', 'zhaoliu@example.com')
RETURNING id, username, created_at;

批量插入

-- 插入多条数据
INSERT INTO users (username, email, age) VALUES
    ('user1', 'user1@example.com', 22),
    ('user2', 'user2@example.com', 25),
    ('user3', 'user3@example.com', 28),
    ('user4', 'user4@example.com', 31),
    ('user5', 'user5@example.com', 24);

-- 从查询结果插入数据
CREATE TABLE users_backup AS
SELECT * FROM users WHERE 1 = 0;  -- 创建空表

INSERT INTO users_backup
SELECT * FROM users;

-- 从查询结果插入部分数据
CREATE TABLE active_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO active_users (username, email)
SELECT username, email
FROM users
WHERE status = 'active';

使用DEFAULT和NULL

-- 使用DEFAULT关键字使用默认值
INSERT INTO users (username, email, age, status)
VALUES ('test1', 'test1@example.com', DEFAULT, DEFAULT);

-- 使用NULL插入空值
INSERT INTO users (username, email, age)
VALUES ('test2', 'test2@example.com', NULL);

-- 省略有默认值的列
INSERT INTO users (username, email)
VALUES ('test3', 'test3@example.com');

冲突处理（UPSERT）

PostgreSQL支持ON CONFLICT子句处理插入冲突：

-- 创建有唯一约束的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_code VARCHAR(20) UNIQUE,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    stock INTEGER DEFAULT 0
);

-- 插入或忽略（如果冲突则什么都不做）
INSERT INTO products (product_code, name, price)
VALUES ('P001', '笔记本电脑', 5999.00)
ON CONFLICT (product_code) DO NOTHING;

-- 插入或更新（如果冲突则更新）
INSERT INTO products (product_code, name, price, stock)
VALUES ('P001', '笔记本电脑Pro', 6999.00, 100)
ON CONFLICT (product_code) 
DO UPDATE SET 
    name = EXCLUDED.name,      -- EXCLUDED引用待插入的行
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;  -- 库存累加

-- 条件更新
INSERT INTO products (product_code, name, price)
VALUES ('P001', '笔记本电脑', 4999.00)
ON CONFLICT (product_code) 
DO UPDATE SET 
    price = EXCLUDED.price
WHERE products.price > EXCLUDED.price;  -- 只有新价格更低时才更新

-- 返回结果
INSERT INTO products (product_code, name, price)
VALUES ('P002', '鼠标', 99.00)
ON CONFLICT (product_code) DO UPDATE SET price = EXCLUDED.price
RETURNING id, product_code, name, price,
    (xmax = 0) AS inserted;  -- xmax=0表示是插入，否则是更新

查询数据

基本查询

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT username, email FROM users;

-- 使用列别名
SELECT 
    username AS 用户名,
    email AS 邮箱,
    age AS 年龄
FROM users;

-- 使用表达式
SELECT 
    username,
    email,
    age,
    age * 2 AS 双倍年龄,
    UPPER(username) AS 大写用户名
FROM users;

-- 去重查询
SELECT DISTINCT status FROM users;

-- 多列去重
SELECT DISTINCT status, age FROM users;

WHERE条件

-- 基本条件
SELECT * FROM users WHERE age = 25;

-- 比较运算符
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age >= 25;
SELECT * FROM users WHERE age < 25;
SELECT * FROM users WHERE age <= 25;
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <> 25;  -- 不等于的另一种写法

-- 逻辑运算符
SELECT * FROM users WHERE age > 20 AND age < 30;
SELECT * FROM users WHERE age < 20 OR age > 30;
SELECT * FROM users WHERE NOT status = 'inactive';

-- BETWEEN...AND（闭区间）
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- IN运算符
SELECT * FROM users WHERE age IN (20, 25, 30);
SELECT * FROM users WHERE status IN ('active', 'pending');

-- NOT IN
SELECT * FROM users WHERE age NOT IN (20, 25, 30);

-- LIKE模糊匹配
SELECT * FROM users WHERE username LIKE 'zhang%';   -- 以zhang开头
SELECT * FROM users WHERE username LIKE '%san';     -- 以san结尾
SELECT * FROM users WHERE username LIKE '%ang%';    -- 包含ang
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Gmail邮箱

-- ILIKE不区分大小写
SELECT * FROM users WHERE username ILIKE 'ZHANG%';

-- 下划线匹配单个字符
SELECT * FROM users WHERE username LIKE '_hangsan';  -- 第二个字符开始匹配

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE age IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

ORDER BY排序

-- 升序排序（默认）
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age ASC;

-- 降序排序
SELECT * FROM users ORDER BY age DESC;

-- 多列排序
SELECT * FROM users ORDER BY status, age DESC;

-- 按表达式排序
SELECT * FROM users ORDER BY LENGTH(username);

-- NULL值排序位置
SELECT * FROM users ORDER BY age NULLS FIRST;   -- NULL值排在前面
SELECT * FROM users ORDER BY age NULLS LAST;    -- NULL值排在后面

LIMIT和OFFSET

-- 限制返回行数
SELECT * FROM users LIMIT 5;

-- 跳过前N条记录
SELECT * FROM users OFFSET 5;  -- 跳过前5条

-- 分页查询
SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 0;   -- 第1页，每页10条

SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 10;  -- 第2页

SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 20;  -- 第3页

-- FETCH FIRST语法（SQL标准）
SELECT * FROM users 
ORDER BY id 
FETCH FIRST 5 ROWS ONLY;

-- FETCH WITH TIES（返回并列的最后几行）
SELECT * FROM users 
ORDER BY age 
FETCH FIRST 3 ROWS WITH TIES;  -- 如果第3名有并列，都返回

聚合函数

-- 创建订单表示例
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    product_name VARCHAR(100),
    quantity INTEGER,
    price NUMERIC(10, 2),
    order_date DATE
);

INSERT INTO orders (user_id, product_name, quantity, price, order_date) VALUES
    (1, '笔记本电脑', 1, 5999.00, '2024-01-15'),
    (1, '鼠标', 2, 99.00, '2024-01-15'),
    (2, '键盘', 1, 299.00, '2024-01-16'),
    (2, '显示器', 1, 1999.00, '2024-01-16'),
    (3, '耳机', 1, 599.00, '2024-01-17');

-- COUNT计数
SELECT COUNT(*) FROM orders;                    -- 总行数
SELECT COUNT(user_id) FROM orders;              -- 非NULL值的数量
SELECT COUNT(DISTINCT user_id) FROM orders;     -- 去重计数

-- SUM求和
SELECT SUM(quantity) AS 总数量 FROM orders;
SELECT SUM(quantity * price) AS 总金额 FROM orders;

-- AVG平均值
SELECT AVG(price) AS 平均价格 FROM orders;
SELECT ROUND(AVG(price), 2) AS 平均价格 FROM orders;  -- 保留2位小数

-- MAX/MIN最大最小值
SELECT MAX(price) AS 最高价格, MIN(price) AS 最低价格 FROM orders;

-- 综合使用
SELECT 
    COUNT(*) AS 订单总数,
    COUNT(DISTINCT user_id) AS 用户数,
    SUM(quantity) AS 商品总数,
    ROUND(AVG(price), 2) AS 平均单价,
    MAX(price) AS 最高单价,
    MIN(price) AS 最低单价
FROM orders;

GROUP BY分组

-- 按用户分组统计
SELECT 
    user_id,
    COUNT(*) AS 订单数,
    SUM(quantity * price) AS 总消费
FROM orders
GROUP BY user_id
ORDER BY 总消费 DESC;

-- 按日期分组统计
SELECT 
    order_date,
    COUNT(*) AS 订单数,
    SUM(quantity * price) AS 日销售额
FROM orders
GROUP BY order_date
ORDER BY order_date;

-- 多列分组
SELECT 
    user_id,
    order_date,
    COUNT(*) AS 订单数
FROM orders
GROUP BY user_id, order_date
ORDER BY user_id, order_date;

-- GROUP BY与聚合函数结合
SELECT 
    user_id,
    STRING_AGG(product_name, ', ') AS 购买商品,  -- 字符串聚合
    ARRAY_AGG(product_name) AS 商品数组,         -- 数组聚合
    SUM(quantity) AS 总数量
FROM orders
GROUP BY user_id;

HAVING子句

HAVING用于对分组结果进行过滤，类似于WHERE但作用于分组后：

-- 查找订单数大于1的用户
SELECT 
    user_id,
    COUNT(*) AS 订单数
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;

-- 查找总消费超过5000的用户
SELECT 
    user_id,
    SUM(quantity * price) AS 总消费
FROM orders
GROUP BY user_id
HAVING SUM(quantity * price) > 5000
ORDER BY 总消费 DESC;

-- HAVING与WHERE结合
SELECT 
    user_id,
    COUNT(*) AS 订单数,
    SUM(quantity * price) AS 总消费
FROM orders
WHERE price > 100  -- 先过滤价格大于100的订单
GROUP BY user_id
HAVING COUNT(*) >= 1  -- 再过滤分组
ORDER BY 总消费 DESC;

更新数据

基本更新

-- 更新单个字段
UPDATE users SET status = 'inactive' WHERE id = 1;

-- 更新多个字段
UPDATE users 
SET 
    age = 26,
    status = 'active'
WHERE username = 'zhangsan';

-- 更新所有行（危险操作！）
UPDATE users SET status = 'active';

-- 使用表达式更新
UPDATE products 
SET price = price * 1.1  -- 价格上调10%
WHERE product_code = 'P001';

-- 使用CASE条件更新
UPDATE users 
SET status = CASE 
    WHEN age < 18 THEN 'minor'
    WHEN age >= 18 AND age < 60 THEN 'adult'
    ELSE 'senior'
END
WHERE age IS NOT NULL;

从查询更新

-- 使用FROM子句从其他表获取数据更新
CREATE TABLE user_stats (
    user_id INTEGER PRIMARY KEY,
    total_orders INTEGER DEFAULT 0,
    total_amount NUMERIC(10, 2) DEFAULT 0
);

INSERT INTO user_stats (user_id) SELECT id FROM users;

-- 根据订单统计更新用户统计表
UPDATE user_stats us
SET 
    total_orders = o.order_count,
    total_amount = o.total_amount
FROM (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(quantity * price) AS total_amount
    FROM orders
    GROUP BY user_id
) o
WHERE us.user_id = o.user_id;

返回更新的数据

-- 返回更新后的数据
UPDATE users 
SET age = age + 1 
WHERE id = 1
RETURNING id, username, age;

-- 返回更新前后的值
UPDATE products 
SET price = price * 1.1
WHERE product_code = 'P001'
RETURNING 
    product_code,
    price AS 新价格,
    price / 1.1 AS 旧价格;

删除数据

基本删除

-- 删除指定行
DELETE FROM users WHERE id = 1;

-- 删除多行
DELETE FROM users WHERE age < 18;

-- 删除所有行（危险操作！）
DELETE FROM users;

-- 使用TRUNCATE快速清空表（更快，不能回滚，重置序列）
TRUNCATE TABLE users;

-- TRUNCATE多表（有外键关联时）
TRUNCATE TABLE orders, users CASCADE;

从查询删除

-- 使用子查询删除
DELETE FROM users
WHERE id IN (
    SELECT user_id FROM orders
    GROUP BY user_id
    HAVING SUM(quantity * price) < 100
);

-- 使用USING子句删除
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
AND u.status = 'inactive';

返回删除的数据

-- 返回被删除的数据
DELETE FROM users 
WHERE status = 'inactive'
RETURNING id, username, email;

-- 删除并备份
CREATE TABLE deleted_users AS
DELETE FROM users 
WHERE status = 'inactive'
RETURNING *;

合并数据（MERGE）

PostgreSQL 15+支持MERGE语句，实现条件性的插入、更新、删除：

-- 创建目标表和源表
CREATE TABLE target_products (
    product_code VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    stock INTEGER
);

CREATE TABLE source_products (
    product_code VARCHAR(20),
    name VARCHAR(100),
    price NUMERIC(10, 2),
    stock INTEGER,
    operation VARCHAR(10)  -- 'insert', 'update', 'delete'
);

INSERT INTO target_products VALUES 
    ('P001', '笔记本电脑', 5999.00, 100),
    ('P002', '鼠标', 99.00, 500);

INSERT INTO source_products VALUES 
    ('P001', '笔记本电脑Pro', 6999.00, 50, 'update'),
    ('P003', '键盘', 299.00, 200, 'insert'),
    ('P002', NULL, NULL, NULL, 'delete');

-- 使用MERGE语句
MERGE INTO target_products t
USING source_products s
ON t.product_code = s.product_code
WHEN MATCHED AND s.operation = 'update' THEN
    UPDATE SET 
        name = s.name,
        price = s.price,
        stock = t.stock + s.stock
WHEN MATCHED AND s.operation = 'delete' THEN
    DELETE
WHEN NOT MATCHED AND s.operation = 'insert' THEN
    INSERT (product_code, name, price, stock)
    VALUES (s.product_code, s.name, s.price, s.stock);

复制数据

COPY命令

-- 从CSV文件导入数据
COPY users (username, email, age)
FROM '/path/to/users.csv'
DELIMITER ','
CSV HEADER;

-- 导出数据到CSV文件
COPY (SELECT * FROM users WHERE status = 'active')
TO '/path/to/active_users.csv'
DELIMITER ','
CSV HEADER;

-- 从标准输入导入
COPY users (username, email) FROM STDIN WITH (FORMAT csv);
-- 然后输入CSV数据，以\.结束

-- 导出到标准输出
COPY users TO STDOUT WITH (FORMAT csv, HEADER);

\copy元命令（psql）

-- 在psql中使用\copy（不需要超级用户权限）
\copy users TO 'users.csv' WITH CSV HEADER
\copy users FROM 'users.csv' WITH CSV HEADER

-- 复制查询结果
\copy (SELECT * FROM users ORDER BY created_at DESC LIMIT 100) TO 'recent_users.csv' WITH CSV HEADER

小结

本章介绍了PostgreSQL数据操作的主要内容：

操作	命令	说明
插入	INSERT INTO	插入单条或多条数据
查询	SELECT	查询数据，支持条件、排序、分组等
更新	UPDATE	更新数据，支持表达式和子查询
删除	DELETE	删除数据，支持条件过滤
冲突处理	ON CONFLICT	插入冲突时的处理策略
合并	MERGE	条件性的插入、更新、删除
批量导入导出	COPY	高效的数据批量操作

下一章我们将学习更高级的查询技巧。