安装

注意在使用 docker up -d 启动容器时之前, 需要准备默认的数据

• 中文文档

# 1.注意需要从一个临时容器中并复制一份默认的数据
docker run --name tmp && docker cp tmp:/var/lib/postgresql/data ./data

# 2.注意删除临时容器
docker stop tmp && docker rm tmp

# 3.注意修改目录权限
chmod -R 777 ./data

services:
  postgres15:
    image: postgres:15
    restart: always
    container_name: pgsql15
    environment:
      POSTGRES_USER: admin # 用户
      POSTGRES_PASSWORD: admin@123456 # 用户密码
      POSTGRES_HOST_AUTH_METHOD: trust # 允许所有用户访问
    ports:
      - 5432:5432
    volumes:
      - ./data:/var/lib/postgresql/data # postgresql 数据
      - ./init:/docker-entrypoint-initdb.d # 初始化脚本(只会在首次启动时执行一次)

数据库操作

# 链接数据库
# -h 链接数据库地址
# -p 端口
# -U 用户名(默认会链接同一用户名创建的数据库)
# -d 数据库名(链接指定数据库)
# -W 输入密码
psql -h 127.0.0.1 -p 5432 -U admin -W

# 创建数据库
create database db1;
create database db2;

# 删除数据
drop database db2;

# 查看数据库
\l

# 切换数据库(类似mysql: use db1)
\c db1

# 查看当前数据库所有的表
\d

# 断开链接(退出psql链接后的终端)
\q

数据表操作

数据类型了解

• PostgreSQL 数据类型 - 文档 1
• PostgreSQL 数据类型 - 文档 2

字段约束

• primary key: 主键
• not null: 非空
• unique: 唯一
• default: 默认值
• check: 检查条件

数据表操作

-- 创建表(SQL语法是通用的, 仅需注意字段的数据类型)
create table users(
    id serial primary key,
    name varchar(255) not null,
    age int not null,
    email varchar(255) unique not null,
    password varchar(255) not null,
    created_at timestamp default current_timestamp,
    updated_at timestamp default current_timestamp
);

-- 查看当前数据库所有的表
\d;

-- 查看 users 表的结构
\d users;

-- 删除数据表
drop table users;

-- 重命名数据表
alter table products rename to goods;

-- 修改表结构: 添加字段
-- 向 users 表中添加 一个 height 字段
alter table users add height int not null;

-- 修改表结构: 修改字段
-- 修改 users 表中 height 字段为 varchar 类型
alter table users alter column height type varchar(8);

-- 修改表结构: 删除字段
-- 删除 users 表中的 height 字段
alter table users drop column height;

-- 修改表结构: 重命名字段
-- 修改产品表中的 product_no 字段为 product_id
alter table products rename column product_no to product_id;

schema 模式

与 MySQL 不同, PostgreSQL 有 schema(可以理解为命名空间), 用于管理数据库对象表, 默认所有的表都放在 public 这个命令空间下, 在创建表的时候可以指定命名空间

-- 查看当前数据库所有的命名空间
\dn;

-- 创建命名空间
create schema dev;

-- 创建表(并指定命名空间)
create table dev.users(
    id serial primary key,
    name varchar(255) not null
);

-- 查询指定命名空间下的表
select * from dev.users;

-- 指定默认执行查询的 shcema
set search_path to dev;
select * from users;

数据表记录操作

postgres 的数据表记录操作, 支持标准的 SQL 语句, 具体参考 MySQL 笔记即可

-- 查询
-- select -> from -> where -> group by -> having -> order by -> limit
-- select * from your_table where filed=value;

-- 插入
-- insert into your_table(filed1,filed2) values(value1,value1), (value2,value2);

-- 修改
-- update your_table set filed=value where filed=value;

-- 删除
-- delete from your_table where filed=value;

备份与导入

## 备份数据库(注意这是个系统命令,所以需要进入到 docker 容器内部然后执行)
pg_dump -h 127.0.0.1 -U admin -W -d db1 > db1_backup.sql

## 导入并执行 SQL
# 1. 先创建数据库确保 db1backup 存在
createdb db1bakup -h 127.0.0.1 -U admin -W

# 2. 手动导入数据(包括命名空间,表结构,表数据)
psql -h 127.0.0.1 -U admin -W -d db1bakup -f db1_backup.sql

# 如果环境是 docker, 可以指定 volumes 让容器启动时自动执行来导入数据
# volumes:
# - ./init:/docker-entrypoint-initdb.d/ # 初始化脚本(只会在首次启动时执行一次)

事物处理机制

事物提交模式

postgres 的事物处理机制, 与 MySQL 相似

-- 开启单个事物
begin;
  insert into users (name) values('tom'), ('jerry');
  update users set email='tom@qq.com' where name='tom';
  update users set email='jerry@qq.com' where name='jerry';
commit;

-- 开启全局事物(关闭自动提交)
\set AUTOCOMMIT off

-- 开启自动提交
\set AUTOCOMMIT on

事物隔离级别

-- 查看当前会话事物隔离级别
show transaction isolation level;

-- 查看全局事物隔离级别
show default_transaction_isolation;

-- 设置当前会话事物隔离级别
set session characteristics as transaction isolation level serializable;

-- 设置全局事物隔离级别
alter system set default_transaction_isolation = 'repeatable read';
select pg_reload_conf(); -- 重新加载配置

-- 设置单个事物的隔离界别
begin;
set transaction isolation level serializable;
  -- 执行事务操作...
commit;

锁机制

postgres 锁机制 与 MySQL 不同, 可阅读postgres官方文档

用户管理

# 1. 查看用户
\du

# 2. 创建用户并设置密码(默认为普通用户)
create user dev password 'dev123456';
create user root superuser password 'root123456'; # 创建超级管理员用户

# 4. 修改用户密码
alter user dev password '123456';

# 3. 删除用户
drop user dev;
drop user root;