PostgreSql学习第一篇

一、postgresql优势

1.功能强大

不仅仅关系型数据库，同时支持JSON
地图信息处理
全文检索

2.数据安全

ACID完美支持
强类型约束

3.开源

完全免费
生态好

4.高级特性

窗口函数
公共表表达式（CTE）
存储过程
逻辑复制

二、安装

下载地址

1.拉取镜像

docker pull docker.jiaxin.site/library/postgres:16.2

docker pull postgres:16.2

2.运行镜像

2.1.创建数据卷

docker volume create pgdata

命名卷 pgdata 是 Docker 管理的，数据存储在 /var/lib/docker/volumes/pgdata/_data，不会因容器删除而丢失，适合开发或生产环境

docker volume rm pgdata

2.2.挂载数据卷并运行容器

docker run --name postgres-dev \
  -e POSTGRES_USER=myadmin \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16.2

--name postgres-dev：为容器指定一个名称，方便管理。
-e POSTGRES_USER：自定义超级用户名（默认为 postgres）。
-e POSTGRES_PASSWORD=mypassword：必须设置，这是数据库超级用户的密码。
POSTGRES_DB：在容器启动时自动创建一个额外的数据库。
-p 5432:5432：将容器内的 PostgreSQL 默认端口 5432 映射到宿主机的 5432 端口。
-d：让容器在后台运行。

三、pgsql基本结构及操作

1.数据库（Database）

特点：数据库时物理上的最高隔离，通常一个项目占用一个库
注意：不同数据库之间的数据默认是不通的

2.模式（Schema）

特点：一个数据库下可以有多个Schema
默认值：默认所有的表都放在 public 的模式下
用途：可以创建auth 模式存放用户表，storage 模式存放文件表，从而实现逻辑上的分组和权限控制

3.表（Table）

特点：真正存放数据的地方
结构：每一行（Row）代表一条记录，每一列（Column）代表一个字段。

多租户系统，可以给每个客户分配一个Schema 共享一个数据库

4.数据库操作

-- 创建数据库
create database db_name;
-- 查看数据库
select datname from pg_database;
-- 删除数据库,需要切换到其它库，并且库没有会话
drop database db_name;

5.模式操作

-- 如果不存在，就创建
create schema if not exists storage;
-- 模式删除，为空时才能删除
drop schema if not exists storage;

6.表操作

6.1.创建表

建表时，除了字段名和类型，约束（Constraints）是保证数据质量的关键。

CREATE TABLE IF NOT EXISTS storage.files (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 自增主键
    file_id UUID DEFAULT gen_random_uuid(), -- uuid
    file_name TEXT NOT NULL, -- 不能为空
    file_size BIGINT CHECK (file_size >= 0), -- 检查约束，大小不能为负
    is_public BOOLEAN DEFAULT false, -- 默认值
    tags TEXT[], -- 数组类型 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() -- 带时区
);

6.2.查看当前库下所有表

select 
	schemaname as 模式名,
	tablename as 表名,
	tableowner as 所有人
from
	pg_tables
where
	schemaname not in ('pg_catalog', 'information_schema')
order by
	schemaname,
	tablename;

6.3.查看表字段

select 
	column_name as 字段名,
	data_type as 数据类型,
	is_nullable as 数据类型,
	column_default as 默认值,
	character_maximum_length as 最大长度
from information_schema.columns
where table_name = 'files'
order by ordinal_position; -- 按照表定义的顺序排序

6.4.修改表操作

6.4.1.添加字段

ALTER TABLE storage.files ADD COLUMN download_count INT DEFAULT 0;

6.4.2.修改字段类型

ALTER TABLE storage.files ALTER COLUMN file_name TYPE VARCHAR(255);

6.4.3.重命名列名

ALTER TABLE storage.files RENAME COLUMN is_public TO is_shared;

6.4.4.删除字段

ALTER TABLE storage.files DROP COLUMN tags;

6.5.删除表

6.5.1.单表删除

DROP TABLE IF EXISTS storage.files;

6.5.2.级联删除

DROP TABLE storage.files CASCADE;

CASCADE 会强制删除所有依赖该表的对象，比如：

引用 storage.files 的外键约束所在的其他表；
依赖该表的视图、函数、存储过程等

6.6.清空表

6.6.1.基本清除

TRUNCATE TABLE storage.files;

效果：删除表中所有行，但表结构（列、约束、索引）完全保留，就像把房子里的家具搬空，但房子不拆。
速度：比 DELETE FROM storage.files; 快几个数量级，因为它不逐行记录日志，而是直接释放数据页。

6.6.2.清除同时重置id

TRUNCATE TABLE storage.files RESTART IDENTITY;

效果：清空数据的同时，重置自增主键的计数器（比如 id 列的下一个值会从 1 开始）。

7.数据操作

7.1.插入

-- 插入数据
INSERT INTO storage.files (file_id, file_name, file_size, is_public, tags, created_at)
VALUES
    (gen_random_uuid(), 'annual_report_2025.pdf', 2048576, true,  ARRAY['finance', 'annual'],    '2026-01-15 09:30:00+00'),
    (gen_random_uuid(), 'profile_photo.jpg',       153600,  false, ARRAY['image', 'profile'],   '2026-02-01 14:20:00+00'),
    (gen_random_uuid(), 'sales_data.csv',          512000,  true,  ARRAY['data', 'sales'],      '2026-02-10 11:00:00+00'),
    (gen_random_uuid(), 'backup.sql',              10485760, false, ARRAY['backup', 'sql'],      '2026-03-01 08:15:00+00'),
    (gen_random_uuid(), 'readme.txt',              2048,    true,  ARRAY['documentation'],      '2026-03-15 16:45:00+00'),
    (gen_random_uuid(), 'presentation.pptx',       3145728, false, ARRAY['presentation', 'work'], '2026-04-01 10:30:00+00'),
    (gen_random_uuid(), 'system_log.log',          102400,  false, ARRAY['log', 'system'],      '2026-04-10 23:59:00+00'),
    (gen_random_uuid(), 'vacation_photo.png',      2048000, true,  ARRAY['image', 'vacation'],  '2026-05-01 12:00:00+00'),
    (gen_random_uuid(), 'budget.xlsx',             98304,   true,  ARRAY['budget', 'finance'],  '2026-05-15 09:00:00+00'),
    (gen_random_uuid(), 'config.json',             4096,    false, ARRAY[]::TEXT[],             '2026-06-01 17:30:00+00');
    
    
-- 插入数据并回显UUID 和创建时间
insert into storage.files (file_name, file_size)
values('test_file.pdf', 9999)
returning file_id, created_at;

7.2.查询

-- 查看公开的且文件大小大于204800字节的文件
select
	file_name,
	file_size,
	created_at
from
	storage.files
where
	is_public = true
	and file_size > 204800;
	
-- 查询所有 csv 文件
select
	file_name,
	file_size,
	created_at
from
	storage.files
where
	file_name like '%.csv';

-- 查看tags包含image的数据
select
	file_name,
	file_size,
	tags,
	created_at
from
	storage.files
where
	'image' = any(tags);

7.3.修改

-- annual_report_2025.pdf 文件下载次数加1
update storage.files
set download_count = download_count + 1
where 
	file_name = 'annual_report_2025.pdf';

-- annual_report_2025.pdf 改为非公开并增加私有tag
update storage.files
set is_public=false,tags = array_append(tags, '私有')
where 
	file_name = 'annual_report_2025.pdf';

7.4.删除

-- 删除id为2的数据
delete from storage.files where id = 2;

四、表结构的定义

1.核心数据类型

1.1.数值类型

INTEGER|INT：4字节整数，范围约正负21亿
BIGINT：8字节整数，用于大ID或大数据量计数
NUMERIC(p,s)|DECIMAL：精确的小数，用于金额。p是总位数，s是小数点后的位数
SERIAL|BIGSERIAL：自增整数（实际上是封装了SQUENCE）

1.2.字符类型

VACHAR(a)：变长字符串，有长度限制
TEXT：变长字符串，无长度限制（PostgreSQL推荐直接用这个，除非业务硬性长度约束）
CHAR(n)：定长字符串，不足部分补空格

1.3.日期|时间类型

TIMESTAMP：日期和时间
TIMESTAMPZ：带时区的日期和时间（生产环境推荐使用，避免时区混乱）
DATE：仅日期
INTERVAL：时间间隔（如 1 day 2 hours）
tsrange：对应 timestamp without time zone 的范围
tszrange：对应 timestamp without time zone 的范围（推荐用于跨时区业务）
daterange：对应date的范围

1.4.特色|高级类型

BOOLEAN：TRUE，FALSE或NULL
JSONB：二进制存储的JSON数据，支持索引，性能极佳
UUID：通用唯一标识码
ARRAY：数组类型，例如TEXT[] 可以存储标签列表
INET：ip类型

2.列约束

约束用于确保数据的准确性和可靠性（实体完整性、参照完整性）。

约束类型	描述	示例
NOT NULL	强制列不能包含NULL值	name TEXT NOT NULL
UNIQUE	确保列中的所有值互不相同	email TEXT UNIQUE
PRIMARY KEY	主键，唯一标识每一行，隐含NOT NULL 和UNIQUE	id SERIAL PRIMARY KEY
FOREIGN KEY	外键，建立表与表之间的链接，防止破坏关系	user_id INT REFERENCES users(id)
CHECK	检查值是否满足特定逻辑条件。	age INT CHECK (age > =18)
DEFAULT	如果插入时未指定值，则使用默认值。	created_at TIMESTAMPTZ DEFAULT NOW()

2.1.建表语句

CREATE TABLE public.file_details (
    -- 1. 自增主键（8字节整数）
    id BIGSERIAL PRIMARY KEY,

    -- 2. 对外唯一标识（UUID）
    detail_id UUID UNIQUE DEFAULT gen_random_uuid(),

    -- 3. 有效期范围（带时区的时间范围）
    valid_period TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)'),

    -- 4. 上传者 IP 地址（支持 IPv4/IPv6）
    uploader_ip INET NOT NULL,

    -- 5. 标签数组
    tags TEXT[] DEFAULT '{}',

    -- 6. 半结构化元数据
    metadata JSONB DEFAULT '{}',

    -- 基础字段
    file_name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

2.2.插入数据

INSERT INTO public.file_details (
    uploader_ip,
    tags,
    metadata,
    file_name,
    valid_period
)
VALUES
    -- 1. 文档：使用三参数构造函数，包含起止时间
    (
        '10.0.0.5',
        '{"PDF", "文档"}',
        '{"pages": 45, "author": "Fengfeng"}',
        'PostgreSQL手册.pdf',
        tstzrange('2026-01-01 00:00:00Z', '2027-01-01 00:00:00Z', '[]')
    ),
    -- 2. 图片：使用简写范围，开始时间带时区，无结束边界（永久有效）
    (
        '127.0.0.1',
        '{"素材", "封面"}',
        '{"dpi": 300, "color": "RGB"}',
        'bilibili横屏封面.png',
        tstzrange('2026-04-14 08:00:00+08:00', NULL, '[)')   -- 时区格式规范化为 +08:00
    ),
    -- 3. 简单文件：起点无时区，无结束边界
    (
        '172.16.0.100',
        '{}',
        '{}',
        'test_file.txt',
        tstzrange('2025-01-01', NULL, '[]')
    ),
    -- 4. 视频文件：直接使用表的默认值（从当前时刻开始，永久有效）
    (
        '192.168.50.20',
        '{"视频"}',
        '{"video": {"resolution": {"width": 1920}}}',
        'test_file.mp4',
        DEFAULT
    );

本文来自博客园，作者：TheLifelongLearner，转载请注明原文链接：https://www.cnblogs.com/The-Lifelong-Learner/p/21184993

posted @ 2026-07-06 22:58 TheLifelongLearner 阅读(10) 评论(0) 收藏举报

刷新页面返回顶部

TheLifelongLearner