垦荒学园
  • 中文

      • PostgreSQL完整学习教程

      概述

      本教程旨在从零开始，系统地教授PostgreSQL数据库的知识。无论您是软件工程师、数据分析师还是数据库管理员，本教程都将为您提供有效使用PostgreSQL所需的知识和技能。

      教程结构

      第一部分：入门基础

      第二部分：数据库基础

      第三部分：表操作

      第四部分：数据操作

      第五部分：高级查询

      第六部分：数据库对象

      第七部分：高级特性

      第八部分：管理与安全

      第九部分：参考资源

      快速入门

      安装PostgreSQL

      # Linux (Ubuntu/Debian)
sudo apt update
sudo apt install postgresql postgresql-contrib

# macOS
brew install postgresql@16

# 启动服务
sudo systemctl start postgresql

      连接PostgreSQL

      # 以postgres用户连接
sudo -u postgres psql

# 或使用密码连接
psql -U postgres -d postgres

      创建您的第一个数据库

      -- 创建数据库
CREATE DATABASE myapp;

-- 连接到数据库
\c myapp

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO users (name, email) VALUES
    ('张三', 'zhangsan@example.com'),
    ('李四', 'lisi@example.com');

-- 查询数据
SELECT * FROM users;

      核心SQL命令速查

      数据定义语言（DDL）

      -- 创建
CREATE DATABASE dbname;
CREATE TABLE tablename (column1 type1, column2 type2);
CREATE INDEX idxname ON tablename(column);

-- 修改
ALTER TABLE tablename ADD COLUMN columnname type;
ALTER TABLE tablename RENAME COLUMN oldname TO newname;

-- 删除
DROP TABLE tablename;
DROP DATABASE dbname;

      数据操作语言（DML）

      -- 插入
INSERT INTO tablename (col1, col2) VALUES (val1, val2);
INSERT INTO tablename SELECT ... FROM ...;

-- 查询
SELECT * FROM tablename;
SELECT col1, col2 FROM tablename WHERE condition;

-- 更新
UPDATE tablename SET col1 = val1 WHERE condition;

-- 删除
DELETE FROM tablename WHERE condition;

      数据控制语言（DCL）

      -- 授权
GRANT SELECT, INSERT ON tablename TO username;
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

-- 撤销
REVOKE SELECT ON tablename FROM username;

      事务控制语言（TCL）

      -- 开始事务
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

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

      数据类型快速参考

      数值类型

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

      字符串类型

      类型描述最大长度
      CHAR(n)定长字符串10,485,760字符
      VARCHAR(n)变长字符串10,485,760字符
      TEXT无限变长字符串无限

      日期/时间类型

      类型描述范围
      DATE日期公元前4713年到公元5874897年
      TIME时间00:00:00 到 24:00:00
      TIMESTAMP日期和时间公元前4713年到公元294276年
      TIMESTAMPTZ带时区的日期时间同上
      INTERVAL时间间隔±178,000,000年

      其他类型

      类型描述示例
      BOOLEAN布尔值TRUE, FALSE, NULL
      JSONJSON数据'{"key": "value"}'
      JSONB二进制JSON'{"key": "value"}'
      UUID通用唯一标识符a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      ARRAY数组ARRAY[1, 2, 3]

      常用函数

      字符串函数

      -- 长度
SELECT LENGTH('Hello');  -- 5

-- 大小写转换
SELECT UPPER('hello');   -- 'HELLO'
SELECT LOWER('HELLO');   -- 'hello'

-- 字符串连接
SELECT CONCAT('Hello', ' ', 'World');  -- 'Hello World'
SELECT 'Hello' || ' ' || 'World';      -- 'Hello World'

-- 截取
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'

-- 替换
SELECT REPLACE('Hello World', 'World', 'PostgreSQL');  -- 'Hello PostgreSQL'

-- 去除空格
SELECT TRIM('  Hello  ');  -- 'Hello'

      数值函数

      -- 绝对值
SELECT ABS(-10);  -- 10

-- 四舍五入
SELECT ROUND(3.14159, 2);  -- 3.14

-- 向上取整
SELECT CEIL(3.14);  -- 4

-- 向下取整
SELECT FLOOR(3.14);  -- 3

-- 幂运算
SELECT POWER(2, 3);  -- 8

-- 平方根
SELECT SQRT(16);  -- 4

      日期函数

      -- 当前日期/时间
SELECT CURRENT_DATE;      -- 2024-01-15
SELECT CURRENT_TIME;      -- 14:30:00
SELECT CURRENT_TIMESTAMP; -- 2024-01-15 14:30:00

-- 日期运算
SELECT CURRENT_DATE + INTERVAL '1 day';      -- 2024-01-16
SELECT CURRENT_DATE - INTERVAL '1 week';     -- 2024-01-08

-- 提取日期部分
SELECT EXTRACT(YEAR FROM CURRENT_DATE);  -- 2024
SELECT EXTRACT(MONTH FROM CURRENT_DATE); -- 1
SELECT EXTRACT(DAY FROM CURRENT_DATE);   -- 15

-- 年龄计算
SELECT AGE('2000-01-15');  -- 24 years

      聚合函数

      -- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM orders;

-- 求和
SELECT SUM(amount) FROM orders;

-- 平均值
SELECT AVG(price) FROM products;

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

-- 字符串聚合
SELECT STRING_AGG(name, ',') FROM users;

      最佳实践

      命名约定

      -- 使用小写和下划线
CREATE TABLE user_accounts;  -- 推荐
CREATE TABLE userAccounts;   -- 避免
CREATE TABLE USERACCOUNTS;   -- 避免

-- 使用描述性名称
CREATE TABLE customer_orders;  -- 好
CREATE TABLE co;              -- 避免

-- 表名使用复数（可选）
CREATE TABLE users;           -- 常见做法
CREATE TABLE user;            -- 也可接受

      安全实践

      -- 使用强密码
-- 创建具有有限权限的用户
CREATE USER app_user WITH PASSWORD 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE ON TABLE users TO app_user;
REVOKE DELETE ON TABLE users FROM app_user;

-- 避免SQL注入
-- 使用参数化查询，而不是字符串拼接

      性能优化

      -- 创建适当的索引
CREATE INDEX idx_users_email ON users(email);

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 避免SELECT *
SELECT id, name, email FROM users;  -- 指定需要的列

-- 使用LIMIT限制结果
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

      学习路径

      初学者（1-2周）

      • PostgreSQL简介和安装
      • pgAdmin工具使用
      • SQL基础语法
      • 数据类型
      • 数据库和表的基本操作

      中级（3-4周）

      • 高级查询技术
      • 数据过滤和排序
      • 聚合和分组
      • JOIN操作
      • 子查询

      高级（5-6周）

      • 索引和性能优化
      • 事务和锁
      • 视图和存储过程
      • 触发器
      • 安全和权限管理

      资源

      官方文档

      在线学习

      社区

      • PostgreSQL邮件列表
      • Stack Overflow
      • Reddit: r/postgresql