WEBKT

PostgreSQL 深度解析：从入门到精通，打造你的数据库专家之路

37 0 0 0

为什么要选择 PostgreSQL？

PostgreSQL 核心概念

PostgreSQL 安装与配置

1. 使用包管理器安装 (推荐)

2. 源码安装 (适合高手)

3. 使用 Docker 安装 (方便快捷)

PostgreSQL 常用命令

PostgreSQL 数据类型

PostgreSQL SQL 语法

1. 创建表 (CREATE TABLE)

2. 插入数据 (INSERT)

3. 查询数据 (SELECT)

4. 更新数据 (UPDATE)

5. 删除数据 (DELETE)

6. 连接 (JOIN)

7. 分组 (GROUP BY) 和聚合函数

8. 子查询

9. 事务控制

PostgreSQL 进阶技巧

1. 索引优化

2. 查询优化

3. 存储过程和函数

4. 触发器

5. 视图

PostgreSQL 实战案例

1. 电商网站订单系统

2. 博客网站文章系统

3. 社交网络用户关系系统

PostgreSQL 学习资源

总结

嘿，老铁们，大家好！我是老码农，一个在代码世界里摸爬滚打了多年的家伙。今天咱们来聊聊一个超级硬核的话题——PostgreSQL。这玩意儿啊，绝对是数据库界的扛把子，功能强大，开源免费，还贼好用！如果你想成为一个合格的程序员，或者想在数据库领域更上一层楼，那么掌握PostgreSQL绝对是你的必修课。

为什么要选择 PostgreSQL？

PostgreSQL，简称PG，它到底凭啥这么牛？听我给你细细道来：

  • 开源免费，省钱！ 开源意味着你可以免费使用、修改和分发它。对于咱们这些苦逼的程序员来说，省钱就是硬道理啊！
  • 功能强大，稳定！ PG的功能绝对不输给任何商业数据库。它支持SQL标准，拥有事务处理、并发控制、数据完整性等一系列高级特性，而且运行稳定，可靠性超高。
  • 扩展性强，灵活！ PG的架构设计非常出色，支持多种编程语言和操作系统。你可以根据自己的需求，定制PG的各种功能，让它完美适配你的项目。
  • 社区活跃，资料丰富！ PG拥有一个庞大而活跃的社区，你可以在这里找到各种技术支持、教程和解决方案。遇到问题，分分钟就能找到答案。
  • 数据类型丰富，支持JSON！ PG支持各种数据类型，包括文本、数字、日期、二进制等等，而且它还原生支持JSON数据类型，这对于处理NoSQL数据非常方便。

总而言之，选择PostgreSQL，你不仅能省钱，还能获得一个功能强大、灵活可靠的数据库系统，何乐而不为呢？

PostgreSQL 核心概念

在开始学习PG之前，咱们先来了解几个核心概念，这样能让你更快地入门：

  1. 数据库（Database）： 数据库是PG中存储数据的基本单位，你可以创建多个数据库来组织你的数据。
  2. 模式（Schema）： 模式是数据库中的一个逻辑分组，它包含表、视图、索引等数据库对象。你可以使用模式来组织和管理你的数据库对象。
  3. 表（Table）： 表是数据库中最基本的数据结构，它由行（Row）和列（Column）组成。每一行代表一个数据记录，每一列代表一个数据属性。
  4. 索引（Index）： 索引是一种特殊的数据结构，它可以加速数据的查询。你可以为表中的列创建索引，提高查询效率。
  5. 事务（Transaction）： 事务是一组数据库操作，它们要么全部成功执行，要么全部回滚。事务可以保证数据的一致性和完整性。
  6. 用户（User）和角色（Role）： 用户是访问数据库的身份标识，角色可以被赋予不同的权限，控制用户对数据库的访问和操作。

PostgreSQL 安装与配置

好了，废话不多说，咱们开始实操！首先，你需要安装PostgreSQL。安装方法有很多种，我这里介绍几种常用的：

1. 使用包管理器安装 (推荐)

  • Debian/Ubuntu:

    		sudo apt update
    		sudo apt install postgresql postgresql-contrib

  • CentOS/RHEL:

    		sudo yum install postgresql postgresql-server postgresql-contrib
    		sudo systemctl enable postgresql
    		sudo systemctl start postgresql

  • macOS:

    		brew install postgresql
    		brew services start postgresql

2. 源码安装 (适合高手)

如果你想体验最新版本的PG，或者想定制安装选项，那么源码安装是个不错的选择。具体步骤如下：

  1. 下载源码： 从PG官网下载源码包 (https://www.postgresql.org/download/source/)
  2. 解压： 解压下载的源码包
  3. 配置： 进入解压后的目录，执行 ./configure 命令，配置安装选项
  4. 编译： 执行 make 命令，编译源码
  5. 安装： 执行 sudo make install 命令，安装PG

3. 使用 Docker 安装 (方便快捷)

Docker 是一个非常棒的工具，它可以让你轻松地创建、部署和运行应用程序。使用Docker安装PG非常简单：

# 拉取官方镜像
docker pull postgres
 
# 运行容器
docker run --name my_postgres -p 5432:5432 -e POSTGRES_PASSWORD=your_password -d postgres

安装完成后，你需要进行一些基本的配置，比如设置密码、创建用户等等。这些操作可以通过命令行工具 psql 或者图形化工具 (比如 pgAdmin) 来完成。

PostgreSQL 常用命令

安装完成后，你需要掌握一些常用的命令，才能开始使用PG。下面是一些常用的命令：

  • 连接到数据库：

    psql -U username -d database_name

    -U 参数指定用户名，-d 参数指定数据库名。如果你的用户名是postgres，数据库名也是postgres，那么可以直接使用 psql 命令。

  • 创建数据库：

    CREATE DATABASE database_name;

  • 删除数据库：

    DROP DATABASE database_name;

  • 创建用户：

    CREATE USER username WITH PASSWORD 'password';

  • 修改用户密码：

    ALTER USER username WITH PASSWORD 'new_password';

  • 授权用户：

    GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

    这条命令将授予用户对指定数据库的所有权限。

  • 查看数据库列表：

    \l

  • 查看当前连接的数据库：

    SELECT current_database();

  • 切换数据库：

    \c database_name

  • 退出 psql：

    \q

PostgreSQL 数据类型

PG支持丰富的数据类型，可以满足各种不同的数据存储需求。下面是一些常用的数据类型：

  • 数值类型：

    • INTEGER (INT): 整数
    • BIGINT: 大整数
    • SMALLINT: 小整数
    • NUMERIC: 精确数值，用于存储货币等
    • REAL: 单精度浮点数
    • DOUBLE PRECISION: 双精度浮点数

  • 字符类型：

    • CHAR(n): 定长字符串，n表示字符长度
    • VARCHAR(n): 变长字符串，n表示最大字符长度
    • TEXT: 变长字符串，没有长度限制

  • 日期/时间类型：

    • DATE: 日期
    • TIME: 时间
    • TIMESTAMP: 时间戳 (带时区)
    • TIMESTAMP WITHOUT TIME ZONE: 时间戳 (不带时区)
    • INTERVAL: 时间间隔

  • 布尔类型：

    • BOOLEAN: 布尔值 (TRUE, FALSE)

  • 二进制数据类型：

    • BYTEA: 存储二进制数据

  • JSON类型：

    • JSON: 存储JSON数据
    • JSONB: 存储JSON数据 (二进制格式，更高效)

  • 数组类型：

    • INTEGER[]: 整数数组
    • TEXT[]: 文本数组

  • 枚举类型：

    • ENUM: 定义一个枚举类型，包含一组预定义的值

PostgreSQL SQL 语法

PG使用SQL语言进行数据操作，SQL是数据库领域的标准语言。下面是一些常用的SQL语句：

1. 创建表 (CREATE TABLE)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);
  • SERIAL: 自动增长的整数，通常用作主键
  • PRIMARY KEY: 主键，用于唯一标识每一行数据
  • NOT NULL: 不能为空
  • UNIQUE: 唯一约束，确保该列的值不重复
  • DEFAULT: 默认值
  • NOW(): 获取当前时间

2. 插入数据 (INSERT)

INSERT INTO users (username, email) VALUES ('老码农', 'laomanong@example.com');

3. 查询数据 (SELECT)

-- 查询所有数据
SELECT * FROM users;
 
-- 查询指定列
SELECT username, email FROM users;
 
-- 添加条件
SELECT * FROM users WHERE id = 1;
 
-- 排序
SELECT * FROM users ORDER BY created_at DESC;
 
-- 分页
SELECT * FROM users LIMIT 10 OFFSET 0; -- 获取前10条数据
  • *: 代表所有列
  • WHERE: 用于指定查询条件
  • ORDER BY: 用于对结果进行排序
  • LIMIT: 限制返回的行数
  • OFFSET: 指定偏移量，用于分页

4. 更新数据 (UPDATE)

UPDATE users SET email = 'new_email@example.com' WHERE id = 1;

5. 删除数据 (DELETE)

DELETE FROM users WHERE id = 1;

6. 连接 (JOIN)

-- INNER JOIN
SELECT orders.order_id, users.username
FROM orders
INNER JOIN users ON orders.user_id = users.id;
 
-- LEFT JOIN
SELECT orders.order_id, users.username
FROM orders
LEFT JOIN users ON orders.user_id = users.id;
 
-- RIGHT JOIN
SELECT orders.order_id, users.username
FROM orders
RIGHT JOIN users ON orders.user_id = users.id;
 
-- FULL JOIN
SELECT orders.order_id, users.username
FROM orders
FULL JOIN users ON orders.user_id = users.id;
  • INNER JOIN: 内连接，只返回两个表中匹配的行
  • LEFT JOIN: 左连接，返回左表的所有行，以及右表中匹配的行
  • RIGHT JOIN: 右连接，返回右表的所有行，以及左表中匹配的行
  • FULL JOIN: 全连接，返回左表和右表的所有行

7. 分组 (GROUP BY) 和聚合函数

SELECT COUNT(*), username
FROM users
GROUP BY username;
 
SELECT SUM(amount) FROM orders;
 
SELECT AVG(price) FROM products;
 
SELECT MAX(price) FROM products;
 
SELECT MIN(price) FROM products;
  • COUNT(): 计算行数
  • SUM(): 求和
  • AVG(): 求平均值
  • MAX(): 求最大值
  • MIN(): 求最小值

8. 子查询

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

9. 事务控制

BEGIN;
-- 执行一系列操作
INSERT INTO users (username, email) VALUES ('test', 'test@example.com');
UPDATE products SET price = price * 1.1 WHERE id = 1;
-- 提交或者回滚
COMMIT; -- 或者 ROLLBACK;
  • BEGIN: 开始一个事务
  • COMMIT: 提交事务，将所有更改保存到数据库
  • ROLLBACK: 回滚事务，撤销所有更改

PostgreSQL 进阶技巧

掌握了基本的SQL语法之后，你就可以开始探索PG的进阶技巧了。这些技巧可以帮助你写出更高效、更灵活的SQL语句。

1. 索引优化

索引是提高查询性能的关键。正确地使用索引可以大大减少查询时间。下面是一些索引优化技巧：

  • 创建合适的索引： 为经常用于WHERE子句、JOIN子句和ORDER BY子句的列创建索引。
  • 避免在索引列上使用函数： 如果WHERE子句中对索引列使用了函数，索引将失效。
  • 使用复合索引： 如果查询条件包含多个列，可以创建复合索引，提高查询效率。
  • 定期分析表： 使用ANALYZE命令更新表的统计信息，PG会根据统计信息选择最佳的查询计划。

2. 查询优化

除了索引优化，你还可以通过优化查询语句来提高性能：

  • 避免使用SELECT *： 只选择你需要的列，可以减少数据传输量。
  • 使用EXISTS代替COUNT(*)： 如果只需要判断是否存在满足条件的行，使用EXISTS更高效。
  • 优化JOIN操作： 确保JOIN条件的列上有索引，选择合适的JOIN类型。
  • 使用EXPLAIN分析查询计划： EXPLAIN命令可以显示PG执行查询的计划，帮助你找到性能瓶颈。

3. 存储过程和函数

存储过程和函数可以将SQL语句封装起来，方便复用和管理。它们可以提高代码的可读性和可维护性。

-- 创建一个简单的函数
CREATE FUNCTION get_user_count()
RETURNS INTEGER
AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM users);
END;
$$
LANGUAGE plpgsql;
 
-- 调用函数
SELECT get_user_count();
 
-- 创建一个存储过程
CREATE PROCEDURE update_user_email(user_id INTEGER, new_email VARCHAR(100))
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET email = new_email WHERE id = user_id;
END;
$$
 
-- 调用存储过程
CALL update_user_email(1, 'new_email@example.com');

4. 触发器

触发器是一种特殊的存储过程，它会在特定的事件发生时自动执行。你可以使用触发器来实现数据完整性约束、审计日志等功能。

-- 创建一个触发器，当用户被删除时，将用户信息备份到另一个表
CREATE TABLE users_backup (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    deleted_at TIMESTAMP WITHOUT TIME ZONE
);
 
CREATE OR REPLACE FUNCTION backup_user()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO users_backup (id, username, email, deleted_at)
    VALUES (OLD.id, OLD.username, OLD.email, NOW());
    RETURN OLD;
END;
$$
LANGUAGE plpgsql;
 
CREATE TRIGGER user_deleted
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION backup_user();

5. 视图

视图是虚拟的表，它基于一个或多个表的查询结果。你可以使用视图来简化复杂的查询，或者隐藏敏感数据。

-- 创建一个视图，显示用户的用户名和邮箱
CREATE VIEW user_info AS
SELECT username, email FROM users;
 
-- 查询视图
SELECT * FROM user_info;

PostgreSQL 实战案例

理论知识再好，也要结合实际应用才能真正掌握。下面我分享一些PostgreSQL的实战案例，希望对你有所帮助：

1. 电商网站订单系统

电商网站需要存储大量的订单数据，包括订单信息、商品信息、用户信息等等。使用PostgreSQL可以很好地满足这种需求。你可以创建以下表：

  • users: 存储用户信息，包括用户ID、用户名、密码、邮箱等。
  • products: 存储商品信息，包括商品ID、商品名称、价格、库存等。
  • orders: 存储订单信息，包括订单ID、用户ID、订单日期、总金额等。
  • order_items: 存储订单中的商品信息，包括订单ID、商品ID、数量、单价等。

可以使用索引来优化查询，比如为users.id、products.id、orders.user_id、order_items.order_id和order_items.product_id创建索引。可以使用触发器来实现库存管理，比如当订单创建时，自动减少商品库存。可以使用视图来简化复杂的查询，比如创建一个视图，显示每个用户的订单总金额。

2. 博客网站文章系统

博客网站需要存储文章数据，包括文章标题、内容、作者、发布时间等等。使用PostgreSQL可以很好地满足这种需求。你可以创建以下表：

  • users: 存储用户信息，包括用户ID、用户名、密码、邮箱等。
  • posts: 存储文章信息，包括文章ID、作者ID、标题、内容、发布时间等。
  • comments: 存储评论信息，包括评论ID、文章ID、用户ID、评论内容、评论时间等。

可以使用全文索引来优化文章内容的搜索。可以使用触发器来实现评论通知，比如当有新评论时，自动发送邮件通知作者。可以使用视图来简化复杂的查询，比如创建一个视图，显示每篇文章的评论数量。

3. 社交网络用户关系系统

社交网络需要存储用户关系数据，包括用户之间的关注关系、好友关系等等。使用PostgreSQL可以很好地满足这种需求。你可以创建以下表：

  • users: 存储用户信息，包括用户ID、用户名、头像等。
  • followers: 存储用户之间的关注关系，包括用户ID和被关注用户ID。
  • friends: 存储用户之间的好友关系，包括用户ID和好友用户ID。

可以使用连接表来存储好友关系，也可以使用自连接来查询用户的粉丝和关注列表。可以使用触发器来维护好友关系，比如当用户添加好友时，自动添加双向好友关系。可以使用视图来简化复杂的查询，比如创建一个视图，显示每个用户的粉丝数量和关注数量。

PostgreSQL 学习资源

学习PostgreSQL的资源非常丰富，你可以通过以下方式进行学习：

  • 官方文档： PostgreSQL官方文档是最权威的资料，包含了PG的各种功能和用法。(https://www.postgresql.org/docs/)
  • 教程： 网上有很多优秀的PostgreSQL教程，可以帮助你快速入门。比如：
  • 书籍： 有很多关于PostgreSQL的书籍，可以帮助你深入学习PG的各种技术。比如：
    • 《PostgreSQL 9.0 从入门到精通》
    • 《PostgreSQL 数据库内核分析》
  • 在线课程： 可以在Coursera、Udacity等在线教育平台上找到关于PostgreSQL的课程。
  • 实践项目： 最好的学习方法是实践，尝试使用PostgreSQL来构建自己的项目，比如博客网站、电商网站等等。
  • 参与社区： 积极参与PostgreSQL社区，与其他开发者交流，可以获得很多帮助和启发。

总结

好了，今天就和大家聊到这里。PostgreSQL是一个非常强大的数据库系统，掌握它对于你的职业发展非常有帮助。希望我的分享能让你对PostgreSQL有一个更深入的了解。记住，学习数据库是一个循序渐进的过程，需要不断地实践和探索。加油，老铁们，让我们一起在数据库的世界里畅游吧！

如果你在学习过程中遇到任何问题，欢迎在评论区留言，我会尽力解答。咱们下期再见！

老码农，一个热爱技术的程序员！

老码农 PostgreSQL数据库SQL编程数据库优化

评论点评

打赏赞助
sponsor

感谢您的支持让我们更好的前行

分享

QRcode

https://www.webkt.com/article/7751