PostgreSQL完整学习教程

概述

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

教程结构

第一部分：入门基础

第二部分：数据库基础

第三部分：表操作

第四部分：数据操作

第五部分：高级查询

第六部分：数据库对象

第七部分：高级特性

第八部分：管理与安全

第九部分：参考资源

学习资源

快速入门

安装PostgreSQL

bash

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

# macOS
brew install postgresql@16

# 启动服务
sudo systemctl start postgresql

连接PostgreSQL

bash

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

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

创建您的第一个数据库

sql

-- 创建数据库
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）

sql

-- 创建
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）

sql

-- 插入
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）

sql

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

-- 撤销
REVOKE SELECT ON tablename FROM username;

事务控制语言（TCL）

sql

-- 开始事务
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
JSON	JSON数据	'{"key": "value"}'
JSONB	二进制JSON	'{"key": "value"}'
UUID	通用唯一标识符	a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
ARRAY	数组	ARRAY[1, 2, 3]

常用函数

字符串函数

sql

-- 长度
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'

数值函数

sql

-- 绝对值
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

日期函数

sql

-- 当前日期/时间
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

聚合函数

sql

-- 计数
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;

最佳实践

命名约定

sql

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

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

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

安全实践

sql

-- 使用强密码
-- 创建具有有限权限的用户
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注入
-- 使用参数化查询，而不是字符串拼接

性能优化

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官方文档: https://www.postgresql.org/docs/
pgAdmin文档: https://www.pgadmin.org/docs/

在线学习

PostgreSQL教程: https://www.postgresqltutorial.com/
Mode SQL教程: https://mode.com/sql-tutorial/

社区

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

PostgreSQL完整学习教程 ​

概述 ​

教程结构 ​

第一部分：入门基础 ​

第二部分：数据库基础 ​

第三部分：表操作 ​

第四部分：数据操作 ​

第五部分：高级查询 ​

第六部分：数据库对象 ​

第七部分：高级特性 ​

第八部分：管理与安全 ​

第九部分：参考资源 ​

快速入门 ​

安装PostgreSQL ​

连接PostgreSQL ​

创建您的第一个数据库 ​

核心SQL命令速查 ​

数据定义语言（DDL） ​

数据操作语言（DML） ​

数据控制语言（DCL） ​

事务控制语言（TCL） ​

数据类型快速参考 ​

数值类型 ​

字符串类型 ​

日期/时间类型 ​

其他类型 ​

常用函数 ​

字符串函数 ​

数值函数 ​

日期函数 ​

聚合函数 ​

最佳实践 ​

命名约定 ​

安全实践 ​

性能优化 ​

学习路径 ​

初学者（1-2周） ​

中级（3-4周） ​

高级（5-6周） ​

资源 ​

官方文档 ​

在线学习 ​

社区 ​

PostgreSQL完整学习教程

概述

教程结构

第一部分：入门基础

第二部分：数据库基础

第三部分：表操作

第四部分：数据操作

第五部分：高级查询

第六部分：数据库对象

第七部分：高级特性

第八部分：管理与安全

第九部分：参考资源

快速入门

安装PostgreSQL

连接PostgreSQL

创建您的第一个数据库

核心SQL命令速查

数据定义语言（DDL）

数据操作语言（DML）

数据控制语言（DCL）

事务控制语言（TCL）

数据类型快速参考

数值类型

字符串类型

日期/时间类型

其他类型

常用函数

字符串函数

数值函数

日期函数

聚合函数

最佳实践

命名约定

安全实践

性能优化

学习路径

初学者（1-2周）

中级（3-4周）

高级（5-6周）

资源

官方文档

在线学习

社区