跳至内容
概览
数据库
PostgreSQL
基本操作

基本操作

这篇讲 PostgreSQL 的日常基本操作：psql 客户端使用、库和表的创建、基础 CRUD、以及一些 PG 特有的便捷命令。

psql 客户端

psql 是 PG 的命令行客户端，类似 MySQL 的 mysql 命令：

# 连接数据库
docker exec -it pgsql psql -U postgres

# 指定数据库连接
docker exec -it pgsql psql -U postgres -d mydb

# 远程连接
psql -h 192.168.1.100 -p 5432 -U postgres -d mydb

常用 psql 元命令（以 \ 开头）：

命令说明
\l列出所有数据库
\c dbname切换到指定数据库
\dt列出当前库的所有表
\d tablename查看表结构
\di列出所有索引
\du列出所有用户/角色
\dn列出所有 schema
\e用外部编辑器编辑上一条命令
\q退出

数据库操作 

-- 创建数据库
CREATE DATABASE myapp;

-- 指定所有者和编码
CREATE DATABASE myapp
    OWNER postgres
    ENCODING 'UTF8'
    LC_COLLATE 'zh_CN.UTF-8'
    LC_CTYPE 'zh_CN.UTF-8';

-- 查看所有数据库
SELECT datname FROM pg_database;

-- 删除数据库（需断开所有连接）
DROP DATABASE myapp;
PG 中 DROP DATABASE 不能在事务块中执行，且当前连接不能占用目标数据库。如果报错 database is being accessed，先 \c postgres 切换到其他库再删除。

表操作 

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,          -- 自增主键（PG 特有语法）
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 查看表结构
\d users

-- 修改表
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);
ALTER TABLE users DROP COLUMN bio;

-- 删除表
DROP TABLE IF EXISTS users;

基础 CRUD 

-- 插入
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 28);

-- 批量插入
INSERT INTO users (username, email, age) VALUES
    ('bob', '[email protected]', 32),
    ('charlie', '[email protected]', 25);

-- 查询
SELECT * FROM users;
SELECT username, email FROM users WHERE age > 25;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 更新
UPDATE users SET age = 29 WHERE username = 'alice';

-- 删除
DELETE FROM users WHERE username = 'charlie';

-- 使用 RETURNING 子句（PG 特有，非常实用）
INSERT INTO users (username, email, age)
VALUES ('david', '[email protected]', 30)
RETURNING id, created_at;  -- 返回刚插入的 ID 和创建时间

UPDATE users SET age = age + 1
WHERE username = 'alice'
RETURNING username, age;  -- 返回更新后的值
RETURNING 是 PG 非常实用的特性——在 INSERT/UPDATE/DELETE 后直接返回相关字段，省去一次 SELECT 查询。MySQL 和 SQL Server 都没有这个语法。

Schema 概念

PG 的 Schema 相当于数据库内部的命名空间——一个数据库可以有多个 schema，不同 schema 可以有同名表：

-- 创建 schema
CREATE SCHEMA blog;
CREATE SCHEMA shop;

-- 在指定 schema 下建表
CREATE TABLE blog.posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT
);

-- 查询时指定 schema
SELECT * FROM blog.posts;

-- 设置默认搜索路径
SET search_path TO blog, public;

一句话小结

psql 客户端用 \ 元命令查看数据库信息，SERIAL 创建自增主键，RETURNING 减少查询次数，schema 隔离命名空间。PG 的这些小语法糖让日常操作比 MySQL 更顺手。下一篇讲 数据类型。

最后更新于
PostgreSQL 简介数据类型