PostgreSQL SQL 基础

数据库操作

-- 创建数据库
CREATE DATABASE dbname;

-- 删除数据库
DROP DATABASE dbname;

-- 重命名数据库
ALTER DATABASE old_name RENAME TO new_name;

表操作

创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INTEGER DEFAULT 18,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 带约束
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2) CHECK (total >= 0),
    status VARCHAR(20) DEFAULT 'pending',
    UNIQUE(user_id, status)
);

修改表结构

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

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

-- 添加约束
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE(email);

-- 修改列类型
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- 重命名列
ALTER TABLE users RENAME COLUMN age TO user_age;

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

删除表

DROP TABLE users;
DROP TABLE IF EXISTS users CASCADE;

INSERT 插入数据

-- 单行插入
INSERT INTO users (username, email, age) 
VALUES ('zhangsan', 'zhangsan@example.com', 25);

-- 多行插入
INSERT INTO users (username, email, age) VALUES 
    ('lisi', 'lisi@example.com', 30),
    ('wangwu', 'wangwu@example.com', 28),
    ('zhaoliu', 'zhaoliu@example.com', 35);

-- 从查询结果插入
INSERT INTO users (username, email)
SELECT username, email FROM old_users WHERE status = 'active';

-- 带 ON CONFLICT 处理
INSERT INTO users (username, email) 
VALUES ('zhangsan', 'new@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

-- 带 RETURNING
INSERT INTO users (username, email) 
VALUES ('test', 'test@example.com')
RETURNING id, username;

SELECT 查询数据

基本查询

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

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

-- 带别名
SELECT username AS "用户名", email AS "邮箱" FROM users;

-- 去重
SELECT DISTINCT status FROM users;

-- 限制结果
SELECT * FROM users LIMIT 10 OFFSET 20;

条件查询

-- WHERE 子句
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE status = 'active' AND age > 25;
SELECT * FROM users WHERE status IN ('active', 'pending');
SELECT * FROM users WHERE username LIKE 'zhang%';
SELECT * FROM users WHERE username ILIKE '%ZHANG%';  -- 不区分大小写

-- NULL 处理
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT COALESCE(email, 'no-email@example.com') FROM users;

排序

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 age NULLS LAST;

分组聚合

-- 分组
SELECT status, COUNT(*) FROM users GROUP BY status;
SELECT status, AVG(age) FROM users GROUP BY status;

-- HAVING 过滤分组
SELECT status, COUNT(*) 
FROM users 
GROUP BY status 
HAVING COUNT(*) > 5;

-- 聚合函数
SELECT 
    COUNT(*) AS total,
    COUNT(DISTINCT status) AS status_count,
    AVG(age) AS avg_age,
    SUM(age) AS sum_age,
    MIN(age) AS min_age,
    MAX(age) AS max_age
FROM users;

多表查询

-- 连接类型
SELECT u.username, o.total 
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT u.username, o.total 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

SELECT u.username, o.total 
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

SELECT u.username, o.total 
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- 交叉连接（笛卡尔积）
SELECT * FROM users CROSS JOIN statuses;

-- 自连接
SELECT a.username, b.username AS friend
FROM users a
JOIN friends f ON a.id = f.user_id
JOIN users b ON b.id = f.friend_id;

子查询

-- WHERE 中的子查询
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

-- FROM 中的子查询
SELECT status, avg_age 
FROM (SELECT status, AVG(age) AS avg_age FROM users GROUP BY status) t
WHERE avg_age > 25;

-- EXISTS
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- IN
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE total > 100
);

高级查询

-- CASE 表达式
SELECT 
    username,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age < 30 THEN '青年'
        WHEN age < 60 THEN '中年'
        ELSE '老年'
    END AS age_group
FROM users;

-- 窗口函数
SELECT 
    username,
    age,
    AVG(age) OVER () AS avg_age,
    RANK() OVER (ORDER BY age DESC) AS age_rank,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num,
    SUM(age) OVER (PARTITION BY status ORDER BY age) AS running_sum
FROM users;

-- CTE（公用表表达式）
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
),
avg_age AS (
    SELECT AVG(age) AS avg FROM active_users
)
SELECT * FROM active_users WHERE age > (SELECT avg FROM avg_age);

UPDATE 更新数据

-- 基本更新
UPDATE users SET age = 30 WHERE username = 'zhangsan';

-- 多列更新
UPDATE users 
SET age = 30, status = 'inactive', updated_at = NOW()
WHERE username = 'zhangsan';

-- 带子查询
UPDATE users 
SET age = (SELECT AVG(age) FROM users)
WHERE status = 'inactive';

-- 带 RETURNING
UPDATE users SET age = 35 WHERE username = 'lisi' RETURNING *;

DELETE 删除数据

-- 基本删除
DELETE FROM users WHERE username = 'zhangsan';

-- 带子查询
DELETE FROM users WHERE id IN (
    SELECT id FROM users WHERE created_at < '2024-01-01'
);

-- 删除所有数据
DELETE FROM users;
TRUNCATE TABLE users RESTART IDENTITY CASCADE;

事务控制

-- 开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 提交
COMMIT;

-- 回滚
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- 事务示例
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;  -- 或 ROLLBACK;

索引

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status_age ON users(status, age);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 部分索引
CREATE INDEX idx_users_active ON users(username) WHERE status = 'active';

-- 重建索引
REINDEX INDEX idx_users_email;

-- 删除索引
DROP INDEX idx_users_email;