当前位置： 首页 > article >正文

数据库之PostgreSQL详解（待补充）

article 2025/3/12 20:11:09

一、PostgreSQL概述

1.1简介

PostgreSQL由加州大学伯克利分校开发，底层基于C实现。具有以下特点：

  • ACID属性：确保数据库操作的安全性和一致性。
  • 扩展性：支持用户定义的数据类型、函数以及程序语言。
  • 复杂查询支持：支持多种复杂查询方式，如关联、分组、多表联合及窗口函数。
  • 地理数据支持：内置PostGIS插件，支持地理空间数据处理。

PGSQL的官方地址：https://www.postgresql.org/

PGSQL的国内社区：http://www.postgres.cn/v2/home

1.2概述

  • 开源数据库：PostgreSQL 是免费的，并且拥有强大的社区支持。
  • 对象-关系型数据库：不仅支持关系型数据（如表格），还支持复杂的数据类型（如JSON、数组、XML等）。
  • 跨平台：可以在 Linux、Windows、macOS 等多种操作系统上运行。
  • 高扩展性：你可以通过插件（如 PostGIS）扩展数据库的功能。

1.3PostgreSQL 的核心概念

  1. 数据库：存储数据的容器。每个 PostgreSQL 实例可以有多个数据库。
  2. 表（Table）：数据库中数据的结构化存储单位，包含行（记录）和列（字段）。
  3. 行（Row）：表中的一条数据记录。
  4. 列（Column）：表中的数据字段，每列有特定的数据类型。
  5. 模式（Schema）：用于组织数据库中的对象，如表、视图等。
  6. 事务（Transaction）：对数据库的一组操作，要么全部成功，要么全部失败，保证数据一致性。

1.4安装仅供参考

services:
  postgres_db:
    image: postgres:15.7 # 指定镜像及其版本
    container_name: docker_postgres # 指定容器的名称
    environment:
      POSTGRES_PASSWORD: root
      #POSTGRES_DB: default
    ports: # 端口映射
      - "5432:5432"
    volumes: # 数据持久化的配置
      - data:/home/postgresql/data
      - log:/home/postgresql/logs
    logging:
      options:
        max-size: "10m"
        max-file: "3"
volumes: # 数据卷
  data:
  log:

networks:  # 网络
  pub-network:
      external: true

1.5PostgreSQL和MySQL的区别

技术没有好坏之分，知识看一下是否符合你的业务，能否解决你的业务需求。其次也要查看社区的活跃度以及更新的频次。

MySQL不支持的几点内容：

  • MySQL的数据类型不够丰富。
  • MySQL不支持序列概念，Sequence。
  • 使用MySQL时，网上比较好用的插件。
  • MySQL的性能优化监控工具不是很多，定位问题的成本是比较高。
  • MySQL的主从复制没有一个官方的同步策略，同步问题难以解决。
  • MySQL虽然开源，but，不够彻底。

PostgreSQL相对MySQL上述问题的特点：

  • PostgreSQL的数据类型嘎嘎丰富。
  • PostgreSQL是有序列的概念的。
  • PostgreSQL的插件特别丰富。
  • PostgreSQL支持主从复制的同步操作，可以实现数据的0丢失。
  • PostgreSQL的MVCC实现和MySQL不大一样。PostgreSQL一行数据会存储多个版本。最多可以存储40亿个事务版本。

二、快速入门

PostgreSQL是严格遵守SQL规范的，在SQL的标准实现上比MySQL更加完善，所以日常使用的语法基本上都是一样的，这里重点给大家介绍和MySQL不同的操作

2.1数据库操作

## 创建数据库
create database shop;
 
## 查看所有数据库
\l
 
## 切换当前数据库
\c shop
 
##  删除数据库
drop database shop

这里给大家分别介绍了数据库和表的几个基本的操作，其实 PostgreSQL大部分操作和 MySQL都是一样的，这里给大家介绍的都是和MySQL不同的几个操作，先来总结一下：

## 查看数据库
\l
## 切换数据库
\c dbName
## 查看表  和查看表结构
\d <tableName> 
## 退出
\q

2.2表操作

create table my_test(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  VARCHAR (25),
	PRIMARY KEY (ID)
);

插入数据：

INSERT INTO my_test (ID, NAME, AGE, ADDRESS) VALUES (1, 'Alice', 30, '123 Main St');
INSERT INTO my_test (ID, NAME, AGE, ADDRESS) VALUES (2, 'Bob', 25, '456 Elm St');

查询数据：

SELECT * FROM my_test;

更新数据：

UPDATE my_test SET ADDRESS = '789 Oak St' WHERE AGE < 30;

删除数据：

DELETE FROM my_test WHERE ID = 2;

2.3用户管理

可以看到PGSQL一个数据库中有多个schema，在每个schema下都有自己的相应的库表信息，权限粒度会比MySQL更细一些。

在PGSQL中，权限的管理分为很多多层

server、cluster、tablespace级别：这个级别一般是基于pg_hba.conf去配置

database级别：通过命令级别操作，grant

namespace、schema级别：玩的不多……不去多了解这个~~

对象级别：通过grant命令去设置

2.4高级功能

1. 事务管理

PostgreSQL 完全支持ACID事务。使用 BEGIN、COMMIT 和 ROLLBACK 来管理事务。

BEGIN;
UPDATE employees SET age = 35 WHERE name = 'Bob';
COMMIT;  -- 提交事务


ROLLBACK;

2. 视图（View）

视图是虚拟表，通过查询定义。可以用来简化复杂的查询。

CREATE VIEW engineering_employees AS
SELECT * FROM employees WHERE department = 'Engineering';

3. 索引（Index）

索引用于提高查询性能，常见的索引类型包括 B-tree、Hash、GIN (广义倒排索引)等。

CREATE INDEX idx_employee_name ON employees (name);

4. 外键（Foreign Key）

外键用于定义表与表之间的关系，确保数据的完整性。

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department_id INT REFERENCES departments(id)
);

5. JSON 支持

PostgreSQL 支持存储和查询 JSON 数据，非常适合现代 Web 应用。

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

-- 插入 JSON 数据
INSERT INTO products (details) 
VALUES ('{"name": "Laptop", "price": 1000}');

JSON在MySQL8.x中也做了支持，但是MySQL支持的不好，因为JSON类型做查询时，基本无法给JSON字段做索引。

PGSQL支持JSON类型以及JSONB类型。

JSON和JSONB的使用基本没区别。

撇去JSON类型，本质上JSON格式就是一个字符串，比如MySQL5.7不支持JSON的情况的下，使用text也可以，但是字符串类型无法校验JSON的格式，其次单独的字符串没有办法只获取JSON中某个key对应的value。

JSON和JSONB的区别：

  • JSON类型无法构建索引，JSONB类型可以创建索引。
  • JSON类型的数据中多余的空格会被存储下来。JSONB会自动取消多余的空格。
  • JSON类型甚至可以存储重复的key，以最后一个为准。JSONB不会保留多余的重复key（保留最后一个）。
  • JSON会保留存储时key的顺序，JSONB不会保留原有顺序。
  • JSON中key对应的value的数据类型

6.IP类型

PGSQL支持IP类型的存储，支持IPv4，IPv6这种，甚至Mac内种诡异类型也支持

这种IP类型，可以在存储IP时，帮助做校验，其次也可以针对IP做范围查找。

2.6Schema(模式)

基本概念

 在postgreSQL中有一个比较重要的概念叫 schema,官方文档中第5章节有关于模式的具体说明PostgreSQL: Documentation: 16: 5.9. Schemas

1、一个数据库可以有 一个或者多个模式

2、模式中包含了 表、数据类型、函数以及操作符 

3、模式不是严格的隔离

4、一个用户可以访问它所连接的数据库下所有模式，但是需要具备对应的权限

Schema相关操作

## 创建数据库
create database tianlongbabu;
## 切换数据库
\c tianlongbabu;
## 创建模式
create schema gaibang;
 
create schema shaolin;
 
## 创建表
create table gaibang.user (
   id  INT              NOT NULL,
   user_name  VARCHAR (20)     NOT NULL,
   age INT              NOT NULL,
   face_value VARCHAR (25),
	PRIMARY KEY (id)
);
 
create table shaolin.user (
   id  INT              NOT NULL,
   user_name  VARCHAR (20)     NOT NULL,
   age INT              NOT NULL,
   face_value VARCHAR (25),
	PRIMARY KEY (id)
);

 上述命令我们新建了一个数据库 tianlongbabu 然后在这个数据库中新建了一个 gaibang 和shaolin的schema，并且分别在这两个schema里面创建了user 表。你可能会好奇同一个数据库里为什么可以创建同名表，到这里相信你也知道 肯定就是和 schema有关了。

好了 我们可以使用下面的语句查询一下schema信息 

SELECT nspname AS schema_name   
FROM pg_namespace  
WHERE nspname !~ '^pg_' AND nspname <> 'information_schema';

1、允许多个用户使用一个数据库，各自使用单独的schema

2、将数据库对象组织成逻辑组，使其更易于管理。

3、第三方应用程序可以放在单独的模式中，这样它们就不会与其他对象的名称发生冲突

就类似上述案例中的gaibang和shaolin都有一个user表，我们可以创建2个用户 可以使用共同使用tianlongbabu这个数据库，两个用户使用各自的schema 这样就不回相互产生影响了。需要注意的是模式不能嵌套

相关命令总结

## 创建模式
create schema schema_name
 
## 删除模式
drop schema schema_name
 
## 删除模式并且删除该模式下的数据对象
drop schema schema_name cascade

三、备份与恢复

3.1. 备份数据库

pg_dump mydb > mydb_backup.sql

    3.2. 恢复数据库

    psql mydb < mydb_backup.sql

    四、优化与调优


    PostgreSQL 提供了多种优化机制来提升查询性能：

    适当使用索引：索引能够显著提高查询速度，但过多的索引可能会导致插入、更新操作变慢。

    分区表：对于非常大的表，可以使用分区来优化查询性能。

    查询计划分析：使用 EXPLAIN 分析查询计划，帮助找到查询的瓶颈。

    EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;


    http://www.kler.cn/a/581785.html

    相关文章：

  • Websocket的基本使用
  • 使用 React 和 Ant Design 处理 Excel 和 CSV 文件
  • upload-labs-master通关攻略(1~4)
  • 本地部署 OpenManus 保姆级教程（Windows 版）
  • C语言零基础入门教程（1）
  • 关于sqlalchemy的ORM的使用
  • R语言中byrow参数的作用
  • 【GIT】non-fast-forward错误
  • 大白话react第十九章React 与 WebGL 项目的深度拓展和优化
  • 计算机图形学交互式技术实验（鼠标、拾取操作和菜单）——绘制可用鼠标进行修改颜色的五角星和矩形
  • Linux rpcbind漏洞
  • Python----计算机视觉处理（Opencv：自适应二值化，取均值，加权求和（高斯定理））
  • GitHub 项目版本管理与 Release 发布流程记录
  • 学习文章：Spring Boot 中 Redis 配置与序列化管理
  • 软件工程概述
  • 消息队列MQ使用场景有哪些？
  • 逐梦DBA：基本的SELECT语句
  • C#运算符详解
  • GStreamer —— 2.15、Windows下Qt加载GStreamer库后运行 - “播放教程 1：Playbin 使用“（附：完整源码）
  • AI重构私域增长：从流量收割到终身价值运营的三阶跃迁