PostgreSQL 练习题集

基础练习

练习1：数据库和表操作

-- 1. 创建一个名为 company 的数据库
CREATE DATABASE company;

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

-- 3. 创建员工表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT true
);

-- 4. 创建部门表
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    manager_id INTEGER,
    budget DECIMAL(12,2)
);

-- 5. 添加外键约束
ALTER TABLE employees ADD COLUMN dept_id INTEGER;
ALTER TABLE employees ADD CONSTRAINT fk_employee_dept 
    FOREIGN KEY (dept_id) REFERENCES departments(id);

练习2：数据插入和查询

-- 插入部门数据
INSERT INTO departments (name, budget) VALUES
('技术部', 1000000.00),
('销售部', 800000.00),
('人事部', 500000.00),
('财务部', 600000.00);

-- 插入员工数据
INSERT INTO employees (name, email, department, salary, dept_id) VALUES
('张三', 'zhangsan@company.com', '技术部', 8000.00, 1),
('李四', 'lisi@company.com', '技术部', 9000.00, 1),
('王五', 'wangwu@company.com', '销售部', 7000.00, 2),
('赵六', 'zhaoliu@company.com', '销售部', 7500.00, 2),
('钱七', 'qianqi@company.com', '人事部', 6000.00, 3);

-- 练习查询
-- 1. 查询所有员工信息
SELECT * FROM employees;

-- 2. 查询技术部员工
SELECT * FROM employees WHERE department = '技术部';

-- 3. 查询薪资大于7000的员工
SELECT name, salary FROM employees WHERE salary > 7000;

-- 4. 查询员工总数
SELECT COUNT(*) as total_employees FROM employees;

-- 5. 查询各部门平均薪资
SELECT department, AVG(salary) as avg_salary 
FROM employees 
GROUP BY department;

练习3：连接查询

-- 1. 内连接查询员工和部门信息
SELECT e.name, e.salary, d.name as department_name, d.budget
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 2. 左连接查询所有部门及其员工数量
SELECT d.name as department, COUNT(e.id) as employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.name;

-- 3. 查询每个部门的最高薪资员工
SELECT d.name as department, e.name as employee, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.salary = (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.dept_id = e.dept_id
);

中级练习

练习4：窗口函数

-- 1. 为每个员工添加部门内薪资排名
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank
FROM employees;

-- 2. 计算累计薪资
SELECT 
    name,
    salary,
    SUM(salary) OVER (ORDER BY hire_date) as cumulative_salary
FROM employees
ORDER BY hire_date;

-- 3. 计算移动平均薪资（前3个员工）
SELECT 
    name,
    salary,
    AVG(salary) OVER (
        ORDER BY hire_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg
FROM employees
ORDER BY hire_date;

练习5：JSON 数据处理

-- 创建包含 JSON 的表
CREATE TABLE employee_profiles (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    profile JSONB
);

-- 插入 JSON 数据
INSERT INTO employee_profiles (employee_id, profile) VALUES
(1, '{"skills": ["Java", "Python", "SQL"], "experience": 5, "certifications": ["AWS", "Oracle"]}'),
(2, '{"skills": ["JavaScript", "React", "Node.js"], "experience": 3, "certifications": ["Google Cloud"]}'),
(3, '{"skills": ["Sales", "CRM", "Negotiation"], "experience": 7, "certifications": ["Salesforce"]}');

-- JSON 查询练习
-- 1. 查询具有特定技能的员工
SELECT e.name, ep.profile->'skills' as skills
FROM employees e
JOIN employee_profiles ep ON e.id = ep.employee_id
WHERE ep.profile->'skills' ? 'Python';

-- 2. 查询经验超过4年的员工
SELECT e.name, (ep.profile->>'experience')::int as experience
FROM employees e
JOIN employee_profiles ep ON e.id = ep.employee_id
WHERE (ep.profile->>'experience')::int > 4;

-- 3. 更新 JSON 数据
UPDATE employee_profiles 
SET profile = profile || '{"last_review": "2024-01-15"}'
WHERE employee_id = 1;

练习6：数组操作

-- 创建项目表
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    team_members INTEGER[],
    technologies TEXT[],
    start_date DATE,
    end_date DATE
);

-- 插入数组数据
INSERT INTO projects (name, team_members, technologies, start_date) VALUES
('电商平台', ARRAY[1, 2, 3], ARRAY['Java', 'Spring', 'MySQL', 'Redis'], '2024-01-01'),
('移动应用', ARRAY[2, 4], ARRAY['React Native', 'Node.js', 'MongoDB'], '2024-02-01'),
('数据分析', ARRAY[1, 5], ARRAY['Python', 'Pandas', 'PostgreSQL'], '2024-03-01');

-- 数组查询练习
-- 1. 查询使用特定技术的项目
SELECT name, technologies 
FROM projects 
WHERE 'Java' = ANY(technologies);

-- 2. 查询包含特定员工的项目
SELECT name, team_members 
FROM projects 
WHERE 1 = ANY(team_members);

-- 3. 统计每种技术被使用的次数
SELECT 
    unnest(technologies) as technology,
    COUNT(*) as usage_count
FROM projects
GROUP BY unnest(technologies)
ORDER BY usage_count DESC;

高级练习

练习7：存储过程和函数

-- 创建计算奖金的函数
CREATE OR REPLACE FUNCTION calculate_bonus(emp_salary DECIMAL, performance_rating INTEGER)
RETURNS DECIMAL AS $$
BEGIN
    CASE performance_rating
        WHEN 5 THEN RETURN emp_salary * 0.20;  -- 优秀
        WHEN 4 THEN RETURN emp_salary * 0.15;  -- 良好
        WHEN 3 THEN RETURN emp_salary * 0.10;  -- 一般
        WHEN 2 THEN RETURN emp_salary * 0.05;  -- 需改进
        ELSE RETURN 0;                          -- 不合格
    END CASE;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT 
    name,
    salary,
    calculate_bonus(salary, 4) as bonus
FROM employees;

-- 创建存储过程更新薪资
CREATE OR REPLACE PROCEDURE update_salary(
    emp_id INTEGER,
    increase_percentage DECIMAL
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE employees 
    SET salary = salary * (1 + increase_percentage / 100)
    WHERE id = emp_id;
    
    -- 记录日志
    INSERT INTO salary_history (employee_id, old_salary, new_salary, change_date)
    SELECT id, salary / (1 + increase_percentage / 100), salary, NOW()
    FROM employees WHERE id = emp_id;
END;
$$;

练习8：触发器

-- 创建薪资历史表
CREATE TABLE salary_history (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    change_date TIMESTAMP DEFAULT NOW()
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary != NEW.salary THEN
        INSERT INTO salary_history (employee_id, old_salary, new_salary)
        VALUES (NEW.id, OLD.salary, NEW.salary);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER salary_change_trigger
    AFTER UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_salary_change();

-- 测试触发器
UPDATE employees SET salary = 8500 WHERE id = 1;

-- 查看薪资变更历史
SELECT * FROM salary_history;

练习9：性能优化

-- 创建大表进行性能测试
CREATE TABLE large_table (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    category VARCHAR(50),
    amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW(),
    data TEXT
);

-- 插入大量数据
INSERT INTO large_table (user_id, category, amount, data)
SELECT 
    (random() * 10000)::int,
    (ARRAY['A', 'B', 'C', 'D', 'E'])[ceil(random() * 5)],
    (random() * 1000)::decimal(10,2),
    'Sample data ' || generate_series
FROM generate_series(1, 1000000);

-- 性能测试查询
EXPLAIN ANALYZE
SELECT category, COUNT(*), AVG(amount)
FROM large_table
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY category;

-- 创建索引优化
CREATE INDEX idx_large_table_created_at ON large_table(created_at);
CREATE INDEX idx_large_table_category ON large_table(category);

-- 再次测试性能
EXPLAIN ANALYZE
SELECT category, COUNT(*), AVG(amount)
FROM large_table
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY category;

答案和解析

基础练习答案要点

表设计: 注意主键、外键、约束的使用
数据类型: 选择合适的数据类型，如 SERIAL、DECIMAL、BOOLEAN
查询优化: 使用适当的 WHERE 条件和索引

中级练习答案要点

窗口函数: 理解 PARTITION BY 和 ORDER BY 的作用
JSON 操作: 掌握 -> 和 ->> 操作符的区别
数组处理: 熟练使用 ANY、unnest 等数组函数

高级练习答案要点

函数编写: 注意 PL/pgSQL 语法和返回类型
触发器: 理解 BEFORE/AFTER 和 FOR EACH ROW 的区别
性能优化: 学会使用 EXPLAIN ANALYZE 分析查询计划

建议按顺序完成练习，每个练习都要实际动手操作，遇到问题及时查阅文档。

如果觉得文章对你有用，请随意赞赏

PostgreSQL 练习题集

https://wordpress.luminghao.xyz/archives/wei-ming-ming-wen-zhang

作者

Administrator

发布于

2025-07-08

更新于

2025-07-08

许可协议

CC BY 4.0

PostgreSQL 练习题集

PostgreSQL 练习题集

基础练习

练习1：数据库和表操作

练习2：数据插入和查询

练习3：连接查询

中级练习

练习4：窗口函数

练习5：JSON 数据处理

练习6：数组操作

高级练习

练习7：存储过程和函数

练习8：触发器

练习9：性能优化

答案和解析

基础练习答案要点

中级练习答案要点

高级练习答案要点

作者

发布于

更新于

许可协议

评论