pgsql-02 数据库基础操作

2026-03-16 约 3028 字预计阅读 7 分钟

02 - 数据库基础操作

1. 📖 概述

本章将详细介绍PostgreSQL的数据库基础操作，包括创建、删除、修改数据库，以及与MySQL的对比。

2. 🗄️ 数据库操作

2.1 查看所有数据库

-- PostgreSQL
\l
-- 或者使用SQL
SELECT datname FROM pg_database;

-- 更详细的信息
\l+

-- MySQL对比
SHOW DATABASES;

2.2 创建数据库

基本创建

-- PostgreSQL
CREATE DATABASE myapp;

-- MySQL对比
CREATE DATABASE myapp;
-- 或指定字符集
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

高级创建选项

-- PostgreSQL：指定所有者、编码、模板等
CREATE DATABASE myapp
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = 100
    TEMPLATE = template0;

参数说明：

OWNER：数据库所有者
ENCODING：字符编码（推荐UTF8）
LC_COLLATE：排序规则
LC_CTYPE：字符分类
TABLESPACE：表空间
CONNECTION LIMIT：最大连接数（-1表示无限制）
TEMPLATE：模板数据库（template0或template1）

使用模板创建数据库

-- PostgreSQL：从现有数据库创建副本
CREATE DATABASE newdb WITH TEMPLATE olddb;

-- 这在MySQL中没有直接对应功能，需要手动导出导入

2.3 删除数据库

-- PostgreSQL
DROP DATABASE myapp;

-- 如果不存在不报错
DROP DATABASE IF EXISTS myapp;

-- MySQL对比
DROP DATABASE myapp;
DROP DATABASE IF EXISTS myapp;

注意事项：

PostgreSQL不允许删除有活动连接的数据库
需要先断开所有连接

-- PostgreSQL：强制断开连接并删除
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'myapp'
  AND pid <> pg_backend_pid();

DROP DATABASE myapp;

2.4 重命名数据库

-- PostgreSQL
ALTER DATABASE oldname RENAME TO newname;

-- MySQL对比（MySQL 5.1.7+不支持RENAME DATABASE）
-- 需要手动创建新库并迁移数据
-- CREATE DATABASE newname;
-- mysqldump oldname | mysql newname
-- DROP DATABASE oldname;

2.5 修改数据库属性

-- PostgreSQL：修改连接限制
ALTER DATABASE myapp CONNECTION LIMIT 200;

-- 修改所有者
ALTER DATABASE myapp OWNER TO newowner;

-- 设置数据库级别的配置参数
ALTER DATABASE myapp SET timezone TO 'UTC';
ALTER DATABASE myapp SET work_mem TO '16MB';

-- MySQL对比
-- ALTER DATABASE myapp CHARACTER SET utf8mb4;

3. 🔌 连接数据库

3.1 命令行连接

# PostgreSQL
psql -h localhost -p 5432 -U postgres -d myapp

# 参数说明：
# -h : 主机地址
# -p : 端口号
# -U : 用户名
# -d : 数据库名
# -W : 强制输入密码

# MySQL对比
# mysql -h localhost -P 3306 -u root -p myapp

3.2 切换数据库

-- PostgreSQL
\c myapp
-- 或
\connect myapp

-- 完整形式
\c myapp username hostname port

-- MySQL对比
-- USE myapp;

3.3 查看当前数据库

-- PostgreSQL
SELECT current_database();

-- 或使用
\conninfo

-- MySQL对比
-- SELECT DATABASE();

3.4 连接信息查看

-- PostgreSQL：查看所有连接
SELECT
    pid,
    usename,
    datname,
    client_addr,
    client_port,
    application_name,
    state,
    query
FROM pg_stat_activity;

-- 查看特定数据库的连接
SELECT count(*) FROM pg_stat_activity WHERE datname = 'myapp';

-- MySQL对比
-- SHOW PROCESSLIST;
-- 或
-- SELECT * FROM information_schema.PROCESSLIST;

4. 🏗️ Schema（模式）操作

4.1 什么是Schema？

PostgreSQL中的Schema是数据库中的命名空间，类似于文件系统中的文件夹。一个数据库可以包含多个Schema，每个Schema可以包含表、视图、函数等对象。

PostgreSQL vs MySQL：

PostgreSQL：数据库 → Schema → 表
MySQL：数据库（相当于PostgreSQL的Schema） → 表

-- PostgreSQL：查看所有Schema
\dn

-- 或使用SQL
SELECT schema_name FROM information_schema.schemata;

4.2 创建Schema

-- PostgreSQL
CREATE SCHEMA sales;

-- 指定所有者
CREATE SCHEMA sales AUTHORIZATION alice;

-- 创建Schema并创建表
CREATE SCHEMA sales
    CREATE TABLE orders (id SERIAL, product VARCHAR(100))
    CREATE VIEW order_summary AS SELECT * FROM orders;

-- MySQL中没有Schema概念，但数据库本身就相当于Schema
-- CREATE DATABASE sales;

4.3 使用Schema

-- PostgreSQL：设置搜索路径
SET search_path TO sales, public;

-- 查看当前搜索路径
SHOW search_path;

-- 访问特定Schema的表
SELECT * FROM sales.orders;
SELECT * FROM public.users;

4.4 默认Schema

PostgreSQL有几个默认Schema：

public：默认的公共Schema
pg_catalog：系统目录
information_schema：ANSI标准信息Schema

-- 查看当前用户的默认Schema
SELECT current_schema();

4.5 删除Schema

-- PostgreSQL
DROP SCHEMA sales;

-- 级联删除（删除Schema及其所有对象）
DROP SCHEMA sales CASCADE;

-- 如果不存在不报错
DROP SCHEMA IF EXISTS sales CASCADE;

5. 📊 数据库元数据查询

5.1 查看数据库大小

-- PostgreSQL：查看所有数据库大小
SELECT
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 查看特定数据库大小
SELECT pg_size_pretty(pg_database_size('myapp'));

-- MySQL对比
-- SELECT
--     table_schema AS database_name,
--     ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
-- FROM information_schema.tables
-- GROUP BY table_schema;

5.2 查看数据库编码

-- PostgreSQL
SELECT
    datname,
    pg_encoding_to_char(encoding) AS encoding,
    datcollate,
    datctype
FROM pg_database
WHERE datname = 'myapp';

-- MySQL对比
-- SELECT
--     SCHEMA_NAME,
--     DEFAULT_CHARACTER_SET_NAME,
--     DEFAULT_COLLATION_NAME
-- FROM information_schema.SCHEMATA
-- WHERE SCHEMA_NAME = 'myapp';

5.3 查看数据库配置

-- PostgreSQL：查看所有配置参数
SHOW ALL;

-- 查看特定参数
SHOW max_connections;
SHOW shared_buffers;
SHOW work_mem;

-- 查看数据库特定配置
SELECT
    setdatabase,
    setconfig
FROM pg_db_role_setting
WHERE setdatabase = (SELECT oid FROM pg_database WHERE datname = 'myapp');

6. 🔐 权限管理基础

6.1 创建用户

-- PostgreSQL
CREATE USER alice WITH PASSWORD 'secure_password';

-- 创建超级用户
CREATE USER admin WITH SUPERUSER PASSWORD 'admin_password';

-- 创建用户并设置权限
CREATE USER developer WITH
    PASSWORD 'dev_password'
    CREATEDB
    VALID UNTIL '2026-12-31';

-- MySQL对比
-- CREATE USER 'alice'@'localhost' IDENTIFIED BY 'secure_password';
-- GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost';

6.2 授予数据库权限

-- PostgreSQL：授予所有权限
GRANT ALL PRIVILEGES ON DATABASE myapp TO alice;

-- 授予连接权限
GRANT CONNECT ON DATABASE myapp TO alice;

-- 授予创建Schema权限
GRANT CREATE ON DATABASE myapp TO alice;

6.3 撤销权限

-- PostgreSQL
REVOKE ALL PRIVILEGES ON DATABASE myapp FROM alice;

-- 撤销连接权限
REVOKE CONNECT ON DATABASE myapp FROM alice;

6.4 查看权限

-- PostgreSQL：查看数据库权限
\l myapp

-- 查看用户
\du

-- 使用SQL查询
SELECT
    datname,
    datacl
FROM pg_database
WHERE datname = 'myapp';

7. 🛠️ 实用操作示例

7.1 备份数据库

# PostgreSQL：使用pg_dump
pg_dump -U postgres -d myapp -F c -f myapp_backup.dump

# 纯SQL格式备份
pg_dump -U postgres -d myapp -f myapp_backup.sql

# 压缩备份
pg_dump -U postgres -d myapp | gzip > myapp_backup.sql.gz

# MySQL对比
# mysqldump -u root -p myapp > myapp_backup.sql
# mysqldump -u root -p myapp | gzip > myapp_backup.sql.gz

7.2 恢复数据库

# PostgreSQL：从自定义格式恢复
pg_restore -U postgres -d myapp -c myapp_backup.dump

# 从SQL文件恢复
psql -U postgres -d myapp -f myapp_backup.sql

# 从压缩文件恢复
gunzip -c myapp_backup.sql.gz | psql -U postgres -d myapp

# MySQL对比
# mysql -u root -p myapp < myapp_backup.sql
# gunzip < myapp_backup.sql.gz | mysql -u root -p myapp

7.3 克隆数据库

-- PostgreSQL：使用模板克隆
-- 1. 断开所有连接
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'myapp';

-- 2. 创建克隆
CREATE DATABASE myapp_clone WITH TEMPLATE myapp;

7.4 数据库性能监控

-- PostgreSQL：查看数据库统计信息
SELECT
    datname,
    numbackends AS connections,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    blks_read AS disk_reads,
    blks_hit AS cache_hits,
    tup_returned AS rows_returned,
    tup_fetched AS rows_fetched,
    tup_inserted AS rows_inserted,
    tup_updated AS rows_updated,
    tup_deleted AS rows_deleted
FROM pg_stat_database
WHERE datname = 'myapp';

-- 计算缓存命中率
SELECT
    datname,
    ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = 'myapp';

8. 📝 最佳实践

8.1 1. 命名规范

-- 推荐：使用小写和下划线
CREATE DATABASE user_management;
CREATE SCHEMA sales_data;

-- 避免：使用大写或混合大小写（需要引号）
CREATE DATABASE "UserManagement";  -- 不推荐

8.2 2. 编码设置

-- 始终使用UTF8编码
CREATE DATABASE myapp
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';

8.3 3. 连接限制

-- 为生产数据库设置合理的连接限制
ALTER DATABASE production_db CONNECTION LIMIT 200;

-- 为开发数据库设置较小的限制
ALTER DATABASE dev_db CONNECTION LIMIT 50;

8.4 4. 使用Schema组织对象

-- 按功能模块组织Schema
CREATE SCHEMA auth;      -- 认证相关
CREATE SCHEMA sales;     -- 销售相关
CREATE SCHEMA reporting; -- 报表相关

-- 设置搜索路径
SET search_path TO auth, sales, public;

8.5 5. 定期维护

-- 分析数据库
ANALYZE;

-- 清理死元组
VACUUM;

-- 完全清理并分析
VACUUM FULL ANALYZE;

9. 🆚 PostgreSQL vs MySQL 总结

操作	PostgreSQL	MySQL
查看数据库	`\l`	`SHOW DATABASES;`
创建数据库	`CREATE DATABASE`	`CREATE DATABASE`
删除数据库	`DROP DATABASE`	`DROP DATABASE`
重命名数据库	`ALTER DATABASE ... RENAME TO`	不支持（需手动迁移）
切换数据库	`\c dbname`	`USE dbname;`
Schema支持	原生支持	不支持（DB即Schema）
模板数据库	支持	不支持
数据库级配置	支持	有限支持
备份工具	`pg_dump/pg_restore`	`mysqldump`
连接管理	`pg_stat_activity`	`SHOW PROCESSLIST`

10. 🎯 练习题

创建一个名为ecommerce的数据库，使用UTF8编码
在该数据库中创建三个Schema：products、orders、users
创建一个用户shop_admin并授予该数据库的所有权限
查看数据库的大小和连接数
设置搜索路径为products, orders, public
备份数据库到文件

10.1 参考答案

-- 1. 创建数据库
CREATE DATABASE ecommerce
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';

-- 2. 连接数据库并创建Schema
\c ecommerce
CREATE SCHEMA products;
CREATE SCHEMA orders;
CREATE SCHEMA users;

-- 3. 创建用户并授权
CREATE USER shop_admin WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE ecommerce TO shop_admin;
GRANT ALL ON SCHEMA products TO shop_admin;
GRANT ALL ON SCHEMA orders TO shop_admin;
GRANT ALL ON SCHEMA users TO shop_admin;

-- 4. 查看大小和连接数
SELECT pg_size_pretty(pg_database_size('ecommerce')) AS size;
SELECT count(*) FROM pg_stat_activity WHERE datname = 'ecommerce';

-- 5. 设置搜索路径
SET search_path TO products, orders, public;

-- 6. 备份（在命令行执行）
-- pg_dump -U postgres -d ecommerce -F c -f ecommerce_backup.dump

11. 📚 下一步

现在你已经掌握了数据库基础操作，接下来可以：

学习数据类型详解
了解表的创建与管理

关键要点：

PostgreSQL使用Schema作为命名空间，MySQL的数据库相当于PostgreSQL的Schema
PostgreSQL支持数据库模板功能，可以快速克隆数据库
使用\c切换数据库，使用\l查看所有数据库
定期备份数据库是必须的操作

目录