第三章：SQL 基础

SQL 概述

SQL（Structured Query Language）是用于管理关系型数据库的标准语言。PostgreSQL 支持完整的 SQL 标准，并提供了许多扩展功能。

数据类型

数值类型

类型	描述	范围
SMALLINT	2字节整数	-32,768 到 32,767
INTEGER	4字节整数	-2,147,483,648 到 2,147,483,647
BIGINT	8字节整数	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
DECIMAL(p,s)	精确数值	精度 p，小数位数 s
NUMERIC(p,s)	精确数值	同 DECIMAL
REAL	4字节浮点数	6位十进制精度
DOUBLE PRECISION	8字节浮点数	15位十进制精度
SERIAL	自增整数	1 到 2,147,483,647
BIGSERIAL	自增大整数	1 到 9,223,372,036,854,775,807

字符类型

类型	描述	最大长度
CHAR(n)	定长字符串	最多 n 个字符
VARCHAR(n)	变长字符串	最多 n 个字符
TEXT	变长字符串	无限制
BYTEA	二进制数据	无限制

日期时间类型

类型	描述	范围
DATE	日期	4713 BC 到 5874897 AD
TIME	时间	00:00:00 到 24:00:00
TIMESTAMP	日期时间	4713 BC 到 294276 AD
TIMESTAMPTZ	带时区日期时间	4713 BC 到 294276 AD
INTERVAL	时间间隔	-178000000 年到 178000000 年

布尔类型

类型	描述
BOOLEAN	布尔值，true/false

其他类型

类型	描述
UUID	通用唯一标识符
JSON	JSON 数据
JSONB	二进制 JSON 数据
ARRAY	数组
HSTORE	键值对存储

创建和管理表

创建表

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- 创建产品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    category_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改列类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);

-- 添加约束
ALTER TABLE users ADD CONSTRAINT phone_unique UNIQUE (phone);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 重命名表
ALTER TABLE users RENAME TO customers;

-- 重命名列
ALTER TABLE users RENAME COLUMN username TO user_name;

删除表

-- 删除表（危险操作）
DROP TABLE users;

-- 删除表（如果存在）
DROP TABLE IF EXISTS users;

-- 级联删除（删除相关对象）
DROP TABLE users CASCADE;

数据操作语言（DML）

INSERT - 插入数据

-- 插入单行数据
INSERT INTO users (username, email, password_hash)
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- 插入多行数据
INSERT INTO users (username, email, password_hash) VALUES
('jane_smith', 'jane@example.com', 'hashed_password_1'),
('bob_johnson', 'bob@example.com', 'hashed_password_2'),
('alice_williams', 'alice@example.com', 'hashed_password_3');

-- 插入数据并返回结果
INSERT INTO users (username, email, password_hash)
VALUES ('charlie_brown', 'charlie@example.com', 'hashed_password')
RETURNING id, username, created_at;

-- 从查询结果插入
INSERT INTO user_backup (id, username, email)
SELECT id, username, email FROM users WHERE created_at < '2024-01-01';

SELECT - 查询数据

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

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

-- 使用别名
SELECT 
    id AS user_id,
    username AS user_name,
    email AS email_address
FROM users;

-- 去重查询
SELECT DISTINCT category_id FROM products;

-- 条件查询
SELECT * FROM users WHERE is_active = TRUE;

-- 多条件查询
SELECT * FROM products 
WHERE price > 100 
  AND stock_quantity > 0 
  AND category_id = 1;

-- 范围查询
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- IN 查询
SELECT * FROM users 
WHERE username IN ('john_doe', 'jane_smith', 'bob_johnson');

-- LIKE 模糊查询
SELECT * FROM users 
WHERE email LIKE '%@example.com';

-- 排序
SELECT * FROM products 
ORDER BY price DESC, name ASC;

-- 限制结果数量
SELECT * FROM users 
ORDER BY created_at DESC 
LIMIT 10;

-- 分页查询
SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 20;  -- 跳过前20条，取10条

UPDATE - 更新数据

-- 更新单行
UPDATE users 
SET email = 'new_email@example.com', updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- 更新多行
UPDATE products 
SET price = price * 0.9  -- 打9折
WHERE category_id = 2;

-- 使用子查询更新
UPDATE orders 
SET status = 'completed'
WHERE user_id IN (
    SELECT id FROM users WHERE created_at < '2024-01-01'
);

-- 更新并返回结果
UPDATE users 
SET is_active = FALSE 
WHERE last_login < CURRENT_DATE - INTERVAL '1 year'
RETURNING id, username, email;

DELETE - 删除数据

-- 删除特定行
DELETE FROM users WHERE id = 100;

-- 删除满足条件的行
DELETE FROM orders 
WHERE status = 'cancelled' 
  AND created_at < CURRENT_DATE - INTERVAL '6 months';

-- 删除所有数据（危险！）
DELETE FROM users;

-- 使用子查询删除
DELETE FROM products 
WHERE id IN (
    SELECT product_id FROM order_items WHERE quantity = 0
);

-- 删除并返回结果
DELETE FROM users 
WHERE is_active = FALSE
RETURNING id, username, email;

聚合函数

-- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT category_id) FROM products;

-- 求和
SELECT SUM(total_amount) FROM orders;
SELECT SUM(quantity * price) FROM order_items;

-- 平均值
SELECT AVG(price) FROM products;
SELECT AVG(total_amount) FROM orders WHERE status = 'completed';

-- 最大值/最小值
SELECT MAX(price) FROM products;
SELECT MIN(created_at) FROM users;

-- 分组聚合
SELECT 
    category_id,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    SUM(stock_quantity) as total_stock
FROM products
GROUP BY category_id;

-- 分组后过滤
SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

连接查询

内连接（INNER JOIN）

-- 获取用户订单信息
SELECT 
    u.username,
    u.email,
    o.id as order_id,
    o.total_amount,
    o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 多表连接
SELECT 
    u.username,
    p.name as product_name,
    oi.quantity,
    oi.price,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

左连接（LEFT JOIN）

-- 获取所有用户及其订单（包括没有订单的用户）
SELECT 
    u.username,
    u.email,
    o.id as order_id,
    o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 使用 COALESCE 处理 NULL
SELECT 
    u.username,
    COALESCE(COUNT(o.id), 0) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

右连接（RIGHT JOIN）

-- 获取所有订单及其用户（包括用户已删除的订单）
SELECT 
    o.id as order_id,
    o.total_amount,
    u.username
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

全外连接（FULL OUTER JOIN）

-- 获取所有用户和所有产品的组合
SELECT 
    u.username,
    p.name as product_name
FROM users u
FULL OUTER JOIN products p ON TRUE
WHERE u.id IS NOT NULL AND p.id IS NOT NULL;

自连接（SELF JOIN）

-- 查找同一部门的员工
SELECT 
    e1.name as employee_name,
    e2.name as colleague_name,
    e1.department
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department
WHERE e1.id != e2.id;

子查询

标量子查询

-- 在 SELECT 中使用子查询
SELECT 
    username,
    email,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;

-- 在 WHERE 中使用子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 在 HAVING 中使用子查询
SELECT category_id, AVG(price) as avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > (SELECT AVG(price) FROM products);

行子查询

-- 查找价格最高的产品
SELECT * FROM products
WHERE (price, id) = (
    SELECT MAX(price), MIN(id) 
    FROM products 
    WHERE stock_quantity > 0
);

表子查询

-- 使用子查询作为临时表
SELECT 
    category_name,
    product_count,
    avg_price
FROM (
    SELECT 
        c.name as category_name,
        COUNT(p.id) as product_count,
        AVG(p.price) as avg_price
    FROM categories c
    LEFT JOIN products p ON c.id = p.category_id
    GROUP BY c.id, c.name
) as category_stats
WHERE product_count > 0
ORDER BY avg_price DESC;

常用函数

字符串函数

-- 字符串连接
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;
SELECT first_name || ' ' || last_name as full_name FROM users;

-- 大小写转换
SELECT UPPER(username), LOWER(email) FROM users;

-- 字符串长度
SELECT LENGTH(username) as name_length FROM users;

-- 子字符串
SELECT SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) as username FROM users;

-- 替换
SELECT REPLACE(description, 'old', 'new') FROM products;

-- 去除空格
SELECT TRIM(BOTH ' ' FROM username) FROM users;

数值函数

-- 四舍五入
SELECT ROUND(price, 2) FROM products;
SELECT CEIL(price), FLOOR(price) FROM products;

-- 绝对值
SELECT ABS(price - 100) as diff_from_100 FROM products;

-- 幂运算
SELECT POWER(2, 10) as two_power_ten;

-- 随机数
SELECT RANDOM() as random_number;

日期时间函数

-- 当前时间
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

-- 提取日期部分
SELECT 
    EXTRACT(YEAR FROM created_at) as year,
    EXTRACT(MONTH FROM created_at) as month,
    EXTRACT(DAY FROM created_at) as day
FROM users;

-- 日期加减
SELECT 
    created_at + INTERVAL '1 day' as tomorrow,
    created_at - INTERVAL '1 month' as last_month
FROM users;

-- 日期格式化
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') as formatted_date FROM users;

事务控制

-- 开始事务
BEGIN;

-- 执行多个操作
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
INSERT INTO orders (user_id, total_amount) VALUES (currval('users_id_seq'), 100.00);

-- 提交事务
COMMIT;

-- 或者回滚事务
ROLLBACK;

-- 使用事务块
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    -- 如果出现错误，自动回滚
    SAVEPOINT transfer_point;
    -- 可以回滚到保存点
    ROLLBACK TO transfer_point;
COMMIT;

总结

本章介绍了 PostgreSQL 的 SQL 基础，包括： - 数据类型和表创建 - 数据操作（INSERT、SELECT、UPDATE、DELETE） - 聚合函数和分组查询 - 各种连接查询 - 子查询的使用 - 常用函数 - 事务控制

掌握这些基础知识是使用 PostgreSQL 的关键。下一章我们将学习数据类型和表设计的高级技巧。