PostgreSQL从基础到进阶

CREATE DATABASE company;

\c company  -- 连接到company数据库

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER,
    department VARCHAR(50),
    salary NUMERIC,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建新用户
CREATE USER developer WITH PASSWORD 'secure_password';

-- 授予权限
GRANT CONNECT ON DATABASE company TO developer;
GRANT USAGE ON SCHEMA public TO developer;
GRANT SELECT, INSERT, UPDATE ON employees TO developer;

-- 创建只读用户
CREATE USER reader WITH PASSWORD 'readonly_pass';
GRANT CONNECT ON DATABASE company TO reader;
GRANT USAGE ON SCHEMA public TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    manager_id INTEGER,
    budget NUMERIC(15,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- 修改表结构
ALTER TABLE employees ADD COLUMN department_id INTEGER;
ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES departments(id);

INSERT INTO departments (name, budget) VALUES 
('Engineering', 1000000),
('HR', 500000),
('Finance', 800000);

INSERT INTO employees (name, email, age, department, salary) VALUES
('Alice Johnson', 'alice@company.com', 30, 'Engineering', 60000),
('Bob Smith', 'bob@company.com', 25, 'Engineering', 55000),
('Charlie Brown', 'charlie@company.com', 35, 'Finance', 70000);

-- 基本查询
SELECT name, email, salary FROM employees WHERE department = 'Engineering';

-- 排序和限制
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

-- 模糊查询
SELECT * FROM employees WHERE name LIKE 'A%';

-- 聚合查询
SELECT department, AVG(salary) as avg_salary, COUNT(*) as emp_count
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 1;

-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering';

-- 删除数据
DELETE FROM employees WHERE age > 60;

-- 内连接
SELECT e.name, e.salary, d.name as department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 左外连接
SELECT e.name, d.name as department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 子查询
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- CTE查询
WITH dept_avg AS (
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, e.department, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

SELECT 
    name, 
    department, 
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

BEGIN TRANSACTION;

-- 转移资金
UPDATE accounts SET balance = balance - 1000 WHERE id = 123;
UPDATE accounts SET balance = balance + 1000 WHERE id = 456;

-- 如果出现错误可以回滚
-- ROLLBACK;

-- 提交事务
COMMIT;

CREATE VIEW engineering_employees AS
SELECT name, age, salary 
FROM employees 
WHERE department = 'Engineering';

-- 使用视图
SELECT * FROM engineering_employees WHERE salary > 60000;

CREATE MATERIALIZED VIEW department_stats AS
SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary
FROM employees
GROUP BY department;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW department_stats;

-- 简单函数示例
CREATE OR REPLACE FUNCTION get_employee_count(dept_name TEXT)
RETURNS INTEGER AS $$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count
    FROM employees
    WHERE department = dept_name;
    
    RETURN count;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT get_employee_count('Engineering');

CREATE OR REPLACE FUNCTION give_raise(dept_name TEXT, raise_percent NUMERIC)
RETURNS void AS $$
BEGIN
    -- 验证输入参数
    IF raise_percent <= 0 OR raise_percent > 50 THEN
        RAISE EXCEPTION 'Raise percentage must be between 0 and 50';
    END IF;
    
    -- 更新工资
    UPDATE employees 
    SET salary = salary * (1 + raise_percent / 100)
    WHERE department = dept_name;
    
    -- 记录操作
    INSERT INTO salary_adjustments (department, adjust_percent, adjust_date)
    VALUES (dept_name, raise_percent, NOW());
    
EXCEPTION
    WHEN others THEN
        -- 错误处理
        RAISE NOTICE 'Error giving raise: %', SQLERRM;
        ROLLBACK;
END;
$$ LANGUAGE plpgsql;

-- 首先创建触发器函数
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

-- 复杂触发器示例：库存检查
CREATE OR REPLACE FUNCTION check_stock()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.stock_quantity < 10 THEN
        INSERT INTO stock_alerts (product_id, message, alert_date)
        VALUES (NEW.product_id, 'Stock low for product ' || NEW.product_name, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER stock_trigger
AFTER UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION check_stock();

CREATE INDEX idx_employees_name ON employees (name);
CREATE INDEX idx_employees_department_salary ON employees (department, salary DESC);

CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_employees_json ON employees USING GIN (metadata);

CREATE INDEX idx_sales_date ON sales USING BRIN (sale_date);

CREATE INDEX idx_high_salary ON employees (department) WHERE salary > 100000;

-- 创建父表
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 创建索引到每个分区
CREATE INDEX idx_sales_2023_date ON sales_2023 (sale_date);
CREATE INDEX idx_sales_2024_date ON sales_2024 (sale_date);

-- 创建全文搜索索引
CREATE INDEX idx_documents_content ON documents 
USING GIN (to_tsvector('english', content));

-- 全文搜索查询
SELECT title, 
       ts_headline('english', content, q) as excerpt,
       ts_rank_cd(to_tsvector('english', content), q) as rank
FROM documents, to_tsquery('english', 'database | postgres') q
WHERE to_tsvector('english', content) @@ q
ORDER BY rank DESC;

-- 安装扩展
CREATE EXTENSION postgres_fdw;

-- 创建外部服务器
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'foreignhost', dbname 'foreigndb', port '5432');

-- 创建用户映射
CREATE USER MAPPING FOR current_user
SERVER foreign_server
OPTIONS (user 'foreignuser', password 'foreignpassword');

-- 创建外部表
CREATE FOREIGN TABLE foreign_employees (
    id INTEGER,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC
) SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'employees');

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT e.name, e.salary, d.name as department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 70000
ORDER BY e.salary DESC;

-- 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 查看最耗时的查询
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

-- 查看当前活动连接
SELECT * FROM pg_stat_activity;

-- 查看锁信息
SELECT * FROM pg_locks;

-- 查看表级别统计信息
SELECT * FROM pg_stat_user_tables;

-- 创建缺失的索引
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date DESC);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

-- 调整内存设置
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET shared_buffers = '8GB';

-- 优化查询语句
-- 原查询：
SELECT * FROM orders WHERE user_id = 123 ORDER BY order_date DESC;

-- 优化后：
SELECT o.*, 
       (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) as item_count
FROM orders o
WHERE user_id = 123 
ORDER BY order_date DESC 
LIMIT 20;

-- 查看复制状态
SELECT * FROM pg_stat_replication;

-- 查看备库状态
SELECT * FROM pg_stat_wal_receiver;

-- 监控复制延迟
SELECT client_addr, state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;