PostgreSQL掌握数据库与表操作，揭秘数据类型与运算符详解

PostgreSQL掌握数据库与表操作，揭秘数据类型与运算符详解

引言

PostgreSQL（简称 PG）是一款功能强大且开源的关系型数据库管理系统，以其稳定性、扩展性和丰富的数据类型著称。本文将带你从数据库和表的基本操作入手，深入剖析 PostgreSQL 中常见的数据类型与运算符，并通过代码示例与图解帮助你快速掌握，轻松上手。

一、数据库操作

1. 创建与删除数据库

-- 创建数据库
CREATE DATABASE demo_db
    WITH
    OWNER = postgres           -- 指定拥有者
    ENCODING = 'UTF8'          -- 字符编码
    LC_COLLATE = 'en_US.utf8'  -- 排序规则
    LC_CTYPE = 'en_US.utf8'    -- 字符分类
    TEMPLATE = template0;      -- 基础模板

-- 删除数据库
DROP DATABASE IF EXISTS demo_db;

2. 查看与连接数据库

-- 查看所有数据库
\l

-- 连接到数据库
\c demo_db

-- 退出 psql 客户端
\q

图1：psql 客户端常用命令流程

┌────────────┐      ┌──────────┐      ┌─────────┐
│ 启动 psql  │ ──→ │ 查看数据库 │ ──→ │ 连接数据库 │
└────────────┘      └──────────┘      └─────────┘

二、表操作

1. 创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,         -- 自增主键
    username VARCHAR(50) NOT NULL, -- 用户名
    email VARCHAR(100) UNIQUE,     -- 邮箱唯一
    created_at TIMESTAMP DEFAULT NOW()  -- 创建时间
);

2. 修改表结构

-- 添加列
ALTER TABLE users
ADD COLUMN bio TEXT;

-- 修改列类型
ALTER TABLE users
ALTER COLUMN username TYPE TEXT;

-- 重命名列
ALTER TABLE users
RENAME COLUMN bio TO biography;

3. 删除表

DROP TABLE IF EXISTS users;

4. 查看表结构

-- 查看表的列和约束
\d+ users

图2：表操作流程概览

[创建表] → [插入/查询/更新数据] → [修改表结构] → [删除表]

三、PostgreSQL 常见数据类型

图解：数据类型选型思路

┌─────────────┐
│ 是否需要精确 │ ── 是 → DECIMAL / NUMERIC
│（货币、财务）│
└─────────────┘
        ↓ 否
┌──────────────┐
│ 是否有枚举集 │ ── 是 → ENUM
└──────────────┘
        ↓ 否
┌─────────────────┐
│ 是否 JSON 结构？│ ── 是 → JSONB
└─────────────────┘
        ↓ 否
使用 INTEGER/TEXT 等通用类型

四、运算符详解

1. 算术运算符

SELECT 10 + 5 AS 加法, 
       10 - 5 AS 减法, 
       10 * 5 AS 乘法, 
       10 / 5 AS 除法, 
       10 % 3 AS 取余;

2. 比较运算符

SELECT 5 = 5 AS 等于, 
       5 <> 3 AS 不等于, 
       5 > 3 AS 大于, 
       5 < 3 AS 小于, 
       5 >= 5 AS 大于等于, 
       5 <= 3 AS 小于等于;

3. 逻辑运算符

SELECT TRUE AND FALSE AS 逻辑与,
       TRUE OR FALSE  AS 逻辑或,
       NOT TRUE       AS 逻辑非;

4. 文本运算符

SELECT 'Hello' || ' ' || 'World' AS 拼接;

5. 数组与 JSON 运算符

-- 数组包含
SELECT ARRAY[1,2,3] @> ARRAY[2] AS 包含;

-- JSONB 存取
SELECT '{"a":1,"b":2}'::jsonb -> 'b' AS b键的值;
SELECT '{"a":1,"b":2}'::jsonb ->> 'b' AS b键的文本;

五、综合示例

假设有一张订单表 orders，我们结合上述知识点做一次查询：

-- 表结构
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    items JSONB NOT NULL,              -- 存储订单商品列表
    total_amount NUMERIC(10,2) NOT NULL,-- 总金额
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入示例
INSERT INTO orders (user_id, items, total_amount)
VALUES
(1, '[{"name":"笔记本","price":4999.00},{"name":"鼠标","price":199.00}]', 5198.00),
(2, '[{"name":"键盘","price":299.00}]', 299.00);

-- 查询：筛选总金额大于1000并包含“笔记本”的订单
SELECT order_id, user_id, total_amount,
       items ->> 0 AS first_item
FROM orders
WHERE total_amount > 1000
  AND items @> '[{"name":"笔记本"}]';

解析：

1. NUMERIC(10,2) 保证货币精度。
2. items @> '[{"name":"笔记本"}]' 利用 JSONB 包含运算符筛选包含“笔记本”的订单。
3. items ->> 0 提取 JSON 数组第一个元素并以文本形式输出。

结语

本文系统梳理了 PostgreSQL 数据库与表的基本操作，并详解了常见数据类型与运算符，结合代码示例与图解，帮助你迅速掌握核心概念。掌握之后，你就能灵活地设计表结构、选择合适的数据类型，并用丰富的运算符完成各类查询与数据处理。建议多动手实践，并结合官方文档深入钻研：

• 官方文档：PostgreSQL Documentation