PostgreSQL基础知识介绍

作者: 字符空间 发布时间: 2025-11-06 阅读: 29

PostgreSQL，通常被简称为Postgres，是一个功能强大的开源关系型数据库管理系统。它以其高度的可靠性、稳定性、以及对SQL标准的严格遵循而闻名，支持复杂查询、外键、触发器、可更新视图、事务完整性、多版本并发控制等高级特性。

一、 什么是PostgreSQL？

  • 类型: 开源对象-关系型数据库系统（ORDBMS）。
  • 特点: 高度符合SQL标准，支持高级数据类型（如JSON、数组、几何类型），可扩展（允许用户自定义函数、数据类型、操作符等）。
  • 许可证: 自由的PostgreSQL许可证，类似于BSD或MIT许可证。

二、 核心概念

理解以下概念是使用PostgreSQL的基础：

概念描述
数据库 (Database)一个独立的命名空间，包含所有其他对象（如表、视图等）。
模式 (Schema)数据库内部的命名空间，用于组织表、视图等对象。默认有一个`public`模式。
表 (Table)存储数据的基本结构，由行和列组成。
视图 (View)基于一个或多个表的查询结果构成的虚拟表。
序列 (Sequence)用于自动生成唯一的数字序列，常用于主键的自增。
索引 (Index)提高数据检索速度的数据结构。
事务 (Transaction)一个不可分割的工作单元，要么全部成功，要么全部失败，遵循ACID原则。

三、 常用数据类型

PostgreSQL支持非常丰富的数据类型，以下是部分常用类型：

类别数据类型举例
数值类型INTEGER / INT123
SERIAL自动递增的整数（1, 2, 3...）
DECIMAL(p, s) / NUMERIC(p, s)123.45 (p=5, s=2)
REAL123.456
字符类型VARCHAR(n)可变长度字符串，'Hello'
CHAR(n)定长字符串，'ABC  '（会自动用空格填充）
TEXT可变长度的无限长字符串
日期/时间DATE'2023-10-27'
TIME'14:30:00'
TIMESTAMP'2023-10-27 14:30:00'
布尔类型BOOLEANTRUE, FALSE, NULL
其他高级类型JSON / JSONB'{"name": "John", "age": 30}'

四、 基本SQL操作（DDL与DML）

1. 数据定义语言 (DDL)

用于定义和管理数据库对象。

操作SQL语法示例
创建表CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2),
hire_date DATE
);
修改表（添加列）ALTER TABLE employees ADD COLUMN department VARCHAR(50);
删除表DROP TABLE employees;

2. 数据操作语言 (DML)

用于对表中的数据进行操作。

操作SQL语法示例
插入数据 (INSERT)INSERT INTO employees (name, salary, hire_date)
VALUES ('张三', 7500.00, '2023-01-15');
查询数据 (SELECT)SELECT id, name FROM employees WHERE salary > 5000;
更新数据 (UPDATE)UPDATE employees SET salary = 8000.00 WHERE name = '张三';
删除数据 (DELETE)DELETE FROM employees WHERE id = 1;

五、 高级特性简介

PostgreSQL提供了许多超越标准SQL的强大功能：

特性描述简单示例
窗口函数对一组相关的行进行计算，同时保留原始行的信息。SELECT name, salary,
RANK() OVER (ORDER BY salary DESC)
FROM employees;
公共表表达式 (CTE)使用WITH子句创建临时结果集，提高复杂查询的可读性。WITH high_salary_emp AS (
SELECT * FROM employees WHERE salary > 10000
)
SELECT * FROM high_salary_emp;
全文搜索提供强大的自然语言搜索能力。SELECT * FROM articles
WHERE to_tsvector('english', content)
@@ to_tsquery('english', 'PostgreSQL & tutorial');
JSON/JSONB支持原生支持JSON数据类型，JSONB是二进制格式，支持索引，效率更高。SELECT info->>'name' AS name
FROM users
WHERE info @> '{"age": 30}';

六、 PostgreSQL vs MySQL 简要对比

特性PostgreSQLMySQL
SQL标准符合度非常高良好
高级数据类型支持数组、JSON/B、Hstore、几何类型等支持相对较少
事务支持完全支持（ACID）在InnoDB引擎下完全支持
复制物理复制和逻辑复制基于二进制日志的复制
主要应用场景复杂查询、地理空间、大数据分析Web应用、在线事务处理(OLTP)

七、 学习建议

  1. 从基础开始：熟练掌握基本的DDL和DML语句。
  2. 理解事务：学会使用BEGIN, COMMIT, ROLLBACK来保证数据一致性。
  3. 善用索引：了解B-tree, Hash, GIN, GiST等索引类型及其适用场景。
  4. 实践高级特性：尝试使用窗口函数、CTE、JSONB等特性解决复杂问题。
  5. 查阅官方文档：PostgreSQL官方文档非常详尽，是学习的最佳资源。
❤️收藏 👍点赞

用户评论

发表评论

您还未登录，无法发表评论！
快来留下你的足迹吧~