PostgreSQL笔记13：SQL语言基础、数据类型、约束与事务控制

原创 于 2026-08-17 23:30:00 发布 · 793 阅读 · 7 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测
收录于
DataBase

当前文章被收录于：

DataBase
140 篇文章 0 人学习
订阅专栏 查看详情

纲要

  • SQL语言分类
    • DDL（Data Definition Language）：数据定义语言
    • DML（Data Manipulation Language）：数据操纵语言
    • DQL（Data Query Language）：数据查询语言
    • DCL（Data Control Language）：数据控制语言
    • TCL（Transaction Control Language）：事务控制语言
  • PostgreSQL数据类型
    • 数值类型：smallint、integer、bigint、numeric、real、double precision
    • 字符类型：char(n)、varchar(n)、text
    • 日期/时间类型：date、time、timestamp、interval
  • 表操作（DDL）
    • CREATE TABLE：创建表
    • \d：查看表结构
  • 约束（Constraints）
    • PRIMARY KEY：主键约束
    • NOT NULL：非空约束
    • UNIQUE：唯一约束
    • CHECK：检查约束
    • DEFAULT：默认值
  • 数据操纵（DML）
    • INSERT：插入数据
    • UPDATE：更新数据
    • DELETE：删除数据
  • 数据查询（DQL）
    • SELECT：基础查询
    • DISTINCT：去重
    • WHERE：条件过滤
    • GROUP BY：分组聚合
    • HAVING：分组后过滤
    • ORDER BY：排序
    • LIMIT / OFFSET：限制与分页
  • 事务控制（TCL）
    • BEGIN：开启事务
    • COMMIT：提交事务
    • ROLLBACK：回滚事务
  • SELECT语句逻辑执行顺序

SQL语言概述

SQL（Structured Query Language）是操作关系型数据库的标准语言，也是PostgreSQL工作的核心。PostgreSQL对SQL标准的实现非常完整，不仅遵循了常见的SQL标准，还增加了许多实用的扩展功能，如RETURNING子句、自定义列、CTE（Common Table Expression）等。

在关系型数据库中，表（Table） 是最核心的数据组织结构。每个表由行（Row） 和列（Column） 组成：

  • 每一行代表一条记录（Record）
  • 每一列代表一个字段（Field）
  • 多个表之间可以通过主键（Primary Key） 和外键（Foreign Key） 建立关联关系

PostgreSQL中的SQL语句可根据功能分为五大类：

分类全称中文名称核心操作是否影响数据持久性
DDLData Definition Language数据定义语言CREATE、ALTER、DROP是
DMLData Manipulation Language数据操纵语言INSERT、UPDATE、DELETE是
DQLData Query Language数据查询语言SELECT否
DCLData Control Language数据控制语言GRANT、REVOKE否
TCLTransaction Control Language事务控制语言BEGIN、COMMIT、ROLLBACK是

数据类型（Data Types）

PostgreSQL提供了丰富的数据类型，以下是最常用的几类。

数值类型（Numeric Types）

类型存储大小取值范围适用场景
smallint2字节-32,768 ~ 32,767小范围整数
integer4字节-2,147,483,648 ~ 2,147,483,647常规整数
bigint8字节-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807大范围整数，推荐用于主键列
numeric(p, s)可变精度p，标度s金额、分数等精确计算
real4字节6位十进制精度不精确浮点数
double precision8字节15位十进制精度不精确浮点数

注意：real和double precision属于不精确类型（inexact），在金融等需要严格精度的场景中应使用numeric类型。

字符类型（Character Types）

类型说明存储方式最大长度
char(n)定长字符串不足n个字符时用空格填充n个字符
varchar(n)变长字符串按实际长度存储，不超过nn个字符
text变长字符串按实际长度存储，无长度限制1GB
-- 字符类型对比示例
CREATE TABLE char_demo (
    fixed char(10),      -- 固定10个字符，不足补空格
    variable varchar(10), -- 最多10个字符，按实际存储
    unlimited text        -- 最大1GB
);

INSERT INTO char_demo VALUES ('hello', 'hello', 'hello');

-- 查看实际存储长度
SELECT 
    pg_column_size(fixed) AS fixed_size,
    pg_column_size(variable) AS variable_size,
    pg_column_size(unlimited) AS text_size
FROM char_demo;

在实际生产环境中，char(n)使用较少——它相比varchar(n)没有性能优势，反而会因填充空格造成额外的存储浪费。text和varchar是更常见的选择。

日期/时间类型（Date/Time Types）

类型说明示例
date日期（年-月-日）2026-08-14
time时间（时:分:秒）14:30:25
timestamp日期+时间2026-08-14 14:30:25
timestamp with time zone带时区的日期+时间2026-08-14 14:30:25+08
interval时间间隔1 day、3 months
-- 时间类型示例
SELECT 
    CURRENT_TIMESTAMP,                          -- 当前时间戳（带时区）
    CURRENT_TIMESTAMP::date,                    -- 转为日期
    CURRENT_TIMESTAMP::time,                    -- 转为时间
    CURRENT_TIMESTAMP::timestamp(0);            -- 秒级精度（不带时区）

PostgreSQL的时间精度最高可达6位微秒级。

表操作（DDL）

创建表（CREATE TABLE）

-- 创建学生表
CREATE TABLE student (
    id         bigint PRIMARY KEY,           -- 主键约束
    name       varchar(100) NOT NULL,        -- 非空约束
    age        integer,
    email      text UNIQUE,                  -- 唯一约束
    score      numeric(5,2) CHECK (score >= 0 AND score <= 100), -- 检查约束
    created_at timestamp DEFAULT CURRENT_TIMESTAMP -- 默认值
);

使用\d student命令可查看表结构。

临时表（Temporary Table）

临时表的生命周期仅限于当前数据库会话（Session），会话结束后自动销毁。

-- 创建临时表
CREATE TEMP TABLE temp_student (
    id int,
    name text
);

-- 插入数据（仅当前会话可见）
INSERT INTO temp_student VALUES (1, 'temp_user');

-- 会话断开后，该表自动删除

约束（Constraints）

约束用于限制表中数据的合法范围，确保数据完整性。

约束类型关键字作用
主键约束PRIMARY KEY唯一标识一行，不可为空，不可重复
非空约束NOT NULL列值不可为空
唯一约束UNIQUE列值在表中必须唯一
检查约束CHECK列值必须满足指定条件
默认值DEFAULT插入时若未指定值，则使用默认值
-- 约束验证示例

-- 1. 主键约束：插入NULL值会报错
INSERT INTO student (id, name) VALUES (NULL, 'test');  -- 错误

-- 2. 唯一约束：重复email会报错
INSERT INTO student (id, name, email) VALUES (1, 'Alice', 'a@test.com');
INSERT INTO student (id, name, email) VALUES (2, 'Bob', 'a@test.com');  -- 错误

-- 3. 检查约束：分数超出范围会报错
INSERT INTO student (id, name, score) VALUES (3, 'Charlie', 150);  -- 错误

-- 4. 默认值：未指定created_at时自动填充
INSERT INTO student (id, name) VALUES (4, 'David');
SELECT * FROM student WHERE id = 4;

数据操纵语言（DML）

插入数据（INSERT）

-- 完整插入
INSERT INTO student (id, name, age, email, score) 
VALUES (1, '张三', 20, 'zhangsan@test.com', 95.5);

-- 部分列插入（使用默认值）
INSERT INTO student (id, name) VALUES (2, '李四');

-- 批量插入
INSERT INTO student (id, name, age, score) VALUES 
    (3, '王五', 22, 88.0),
    (4, '赵六', 21, 92.5);

更新数据（UPDATE）

-- 更新指定行
UPDATE student SET age = 21 WHERE id = 1;

-- 更新所有行（谨慎使用）
UPDATE student SET score = 60;  -- 所有学生分数改为60

删除数据（DELETE）

-- 删除指定行
DELETE FROM student WHERE id = 4;

-- 删除所有行（谨慎使用）
DELETE FROM student;

数据查询语言（DQL）

基础查询（SELECT）

-- 查询所有列
SELECT * FROM student;

-- 查询指定列
SELECT id, name, score FROM student;

-- 条件过滤（WHERE）
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE name = '张三';
SELECT * FROM student WHERE score BETWEEN 80 AND 100;

-- 去重（DISTINCT）
SELECT DISTINCT name FROM student;

分组与聚合（GROUP BY）

-- 按姓名分组，计算平均分
SELECT name, AVG(score) AS avg_score
FROM student
GROUP BY name;

-- 常用聚合函数
SELECT 
    COUNT(*) AS total_count,
    AVG(score) AS avg_score,
    SUM(score) AS sum_score,
    MAX(score) AS max_score,
    MIN(score) AS min_score
FROM student;

排序（ORDER BY）

-- 按分数升序排列（默认ASC）
SELECT * FROM student ORDER BY score;

-- 按分数降序排列
SELECT * FROM student ORDER BY score DESC;

-- 多列排序
SELECT * FROM student ORDER BY score DESC, age ASC;

限制与分页（LIMIT / OFFSET）

-- 仅返回前1行
SELECT * FROM student LIMIT 1;

-- 跳过第1行，返回第2行
SELECT * FROM student OFFSET 1 LIMIT 1;

-- 分页查询：每页10条，第3页（跳过20条）
SELECT * FROM student OFFSET 20 LIMIT 10;

SELECT语句逻辑执行顺序

SQL语句的书写顺序与实际执行顺序不同。理解这一点对排查"列不存在"等错误至关重要：

-- 书写顺序（开发者视角）
SELECT DISTINCT column_list
FROM table_name
JOIN other_table ON condition
WHERE filter_condition
GROUP BY group_columns
HAVING group_filter
ORDER BY sort_columns
LIMIT n OFFSET m;

-- 实际逻辑执行顺序（数据库视角）
-- 1. FROM / JOIN      -- 确定数据来源
-- 2. WHERE            -- 行级过滤
-- 3. GROUP BY         -- 分组
-- 4. HAVING           -- 分组后过滤
-- 5. SELECT           -- 选择列（此时可定义别名）
-- 6. DISTINCT         -- 去重
-- 7. ORDER BY         -- 排序（可使用SELECT中的别名）
-- 8. LIMIT / OFFSET   -- 限制返回行数

关键点：SELECT中定义的列别名，仅对ORDER BY和LIMIT/OFFSET可见，对WHERE、GROUP BY、HAVING不可见。

事务控制语言（TCL）

事务概述

事务（Transaction）是一组数据库操作的逻辑单元，具有原子性（Atomicity） ——要么全部成功，要么全部失败。

PostgreSQL默认采用自动提交（Auto-commit） 模式，即每个独立的INSERT、UPDATE、DELETE语句都会被自动包装为一个事务并立即提交。

显式事务控制

-- 开启显式事务
BEGIN;

-- 执行操作
INSERT INTO student (id, name, score) VALUES (5, '孙七', 95);
UPDATE student SET score = 98 WHERE id = 5;

-- 提交事务（确认操作）
COMMIT;

-- 或者回滚事务（撤销操作）
ROLLBACK;

事务示例：提交与回滚

-- 场景1：提交事务
BEGIN;
INSERT INTO student (id, name) VALUES (6, '周八');
COMMIT;
SELECT * FROM student WHERE id = 6;  -- 可见

-- 场景2：回滚事务
BEGIN;
INSERT INTO student (id, name) VALUES (7, '吴九');
ROLLBACK;
SELECT * FROM student WHERE id = 7;  -- 不可见（未插入）

事务隔离级别

PostgreSQL支持SQL标准定义的四种事务隔离级别：

隔离级别脏读不可重复读幻读PostgreSQL默认
READ UNCOMMITTED可能可能可能否（等价于READ COMMITTED）
READ COMMITTED不可能可能可能是
REPEATABLE READ不可能不可能可能否
SERIALIZABLE不可能不可能不可能否
-- 设置事务隔离级别
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 执行操作...
COMMIT;

API速览（API Overview）

psql元命令

命令说明示例
\d查看表结构\d student
\dt列出所有表\dt
\di列出所有索引\di
\?查看所有元命令帮助\?

系统函数

函数说明示例
CURRENT_TIMESTAMP当前时间戳（带时区）SELECT CURRENT_TIMESTAMP;
length(text)返回字符串长度SELECT length('hello');
pg_column_size(any)返回列的存储字节数SELECT pg_column_size('hello');
AVG(expression)计算平均值SELECT AVG(score) FROM student;
SUM(expression)计算总和SELECT SUM(score) FROM student;
COUNT(expression)计数SELECT COUNT(*) FROM student;
MAX(expression)最大值SELECT MAX(score) FROM student;
MIN(expression)最小值SELECT MIN(score) FROM student;

类型转换语法

PostgreSQL支持两种类型转换方式：

-- 方式1：CAST函数
SELECT CAST('2026-08-14' AS date);

-- 方式2：::语法（PostgreSQL扩展）
SELECT '2026-08-14'::date;
SELECT CURRENT_TIMESTAMP::timestamp(0);

Demo完整示例（Complete Demo）

以下是一个基于Node.js + pg驱动（PostgreSQL原生驱动）的完整可运行示例。

环境准备

# 初始化项目
mkdir pg-demo && cd pg-demo
npm init -y

# 安装依赖
npm install pg

完整代码

// index.js
const { Client } = require('pg');

// 数据库连接配置
const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'testdb',
    user: 'postgres',
    password: 'your_password',
});

async function runDemo() {
    try {
        await client.connect();
        console.log('✅ 数据库连接成功');

        // ============ DDL: 创建表 ============
        // PostgreSQL原生等价: CREATE TABLE IF NOT EXISTS users (...)
        await client.query(`
            CREATE TABLE IF NOT EXISTS users (
                id         BIGSERIAL PRIMARY KEY,
                name       VARCHAR(100) NOT NULL,
                age        INTEGER,
                email      TEXT UNIQUE,
                score      NUMERIC(5,2) CHECK (score >= 0 AND score <= 100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        console.log('✅ 表创建成功');

        // ============ DML: 插入数据 ============
        // PostgreSQL原生等价: INSERT INTO users (name, age, email, score) VALUES (...)
        await client.query(
            `INSERT INTO users (name, age, email, score) VALUES ($1, $2, $3, $4)`,
            ['张三', 20, 'zhangsan@test.com', 95.5]
        );
        await client.query(
            `INSERT INTO users (name, age, email, score) VALUES ($1, $2, $3, $4)`,
            ['李四', 22, 'lisi@test.com', 88.0]
        );
        await client.query(
            `INSERT INTO users (name, age, email, score) VALUES ($1, $2, $3, $4)`,
            ['王五', 21, 'wangwu@test.com', 92.5]
        );
        console.log('✅ 数据插入成功');

        // ============ DQL: 查询数据 ============
        // PostgreSQL原生等价: SELECT * FROM users
        const res1 = await client.query('SELECT * FROM users');
        console.log('📊 所有用户:', res1.rows);

        // PostgreSQL原生等价: SELECT * FROM users WHERE age > $1
        const res2 = await client.query(
            'SELECT * FROM users WHERE age > $1',
            [20]
        );
        console.log('📊 年龄大于20的用户:', res2.rows);

        // PostgreSQL原生等价: SELECT AVG(score) FROM users
        const res3 = await client.query('SELECT AVG(score) AS avg_score FROM users');
        console.log('📊 平均分:', res3.rows[0].avg_score);

        // PostgreSQL原生等价: SELECT name, AVG(score) FROM users GROUP BY name
        const res4 = await client.query(
            'SELECT name, AVG(score) AS avg_score FROM users GROUP BY name'
        );
        console.log('📊 按姓名分组平均分:', res4.rows);

        // ============ TCL: 事务演示 ============
        // PostgreSQL原生等价: BEGIN; ... COMMIT;
        await client.query('BEGIN');
        await client.query(
            `INSERT INTO users (name, age, email, score) VALUES ($1, $2, $3, $4)`,
            ['赵六', 23, 'zhaoliu@test.com', 78.0]
        );
        await client.query('COMMIT');
        console.log('✅ 事务提交成功');

        // PostgreSQL原生等价: BEGIN; ... ROLLBACK;
        await client.query('BEGIN');
        await client.query(
            `INSERT INTO users (name, age, email, score) VALUES ($1, $2, $3, $4)`,
            ['孙七', 24, 'sunqi@test.com', 85.0]
        );
        await client.query('ROLLBACK');
        console.log('✅ 事务回滚成功（数据未插入）');

        // 验证回滚结果
        const res5 = await client.query(
            "SELECT * FROM users WHERE name = '孙七'"
        );
        console.log('📊 孙七是否存在:', res5.rows.length > 0 ? '是' : '否（已回滚）');

    } catch (err) {
        console.error('❌ 错误:', err.message);
    } finally {
        await client.end();
        console.log('🔌 数据库连接已关闭');
    }
}

runDemo();

运行说明

# 1. 确保PostgreSQL已启动
# 2. 创建测试数据库
createdb testdb

# 3. 修改代码中的数据库连接配置（host, port, database, user, password）

# 4. 运行
node index.js

技术点总结

技术点说明
pg驱动Node.js官方PostgreSQL客户端
参数化查询使用$1, $2, ...占位符防止SQL注入
BIGSERIALPostgreSQL自增主键类型
CHECK约束数据库层面的数据校验
显式事务BEGIN / COMMIT / ROLLBACK
CURRENT_TIMESTAMP自动填充创建时间

多语言Demo补充

本部分提供与 Node.js 示例（基于 pg 驱动）功能完全对等的 Go、Python、Java 版本实现，包含连接、增删改查、参数化查询和显式事务等核心操作。

所有示例均基于同一张 users 表，表结构如下（PostgreSQL DDL）：

CREATE TABLE IF NOT EXISTS users (
    id         BIGSERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    age        INTEGER,
    email      TEXT UNIQUE,
    score      NUMERIC(5,2) CHECK (score >= 0 AND score <= 100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Go 版本（使用 pgx 驱动）

安装依赖
go mod init demo
go get github.com/jackc/pgx/v5
完整代码（main.go）
package main

import (
    "context"
    "fmt"
    "log"

    "github.com/jackc/pgx/v5"
)

func main() {
    // 连接字符串格式：postgres://用户名:密码@主机:端口/数据库名
    conn, err := pgx.Connect(context.Background(), "postgres://postgres:your_password@localhost:5432/testdb")
    if err != nil {
        log.Fatal("连接失败:", err)
    }
    defer conn.Close(context.Background())

    // ---------- DML: 插入数据 ----------
    // 使用参数化查询 $1, $2, ...
    _, err = conn.Exec(context.Background(),
        `INSERT INTO users (name, age, email, score) VALUES ($1, $2, $3, $4)`,
        "Alice", 25, "alice@test.com", 90.5)
    if err != nil {
        log.Fatal("插入失败:", err)
    }
    fmt.Println("✅ 插入成功")

    // ---------- DQL: 查询数据 ----------
    rows, err := conn.Query(context.Background(),
        `SELECT id, name, age, email, score, created_at FROM users WHERE age > $1`, 20)
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    defer rows.Close()

    fmt.Println("📊 年龄大于20的用户：")
    for rows.Next() {
        var id int64
        var name string
        var age int
        var email string
        var score float64
        var createdAt string // 可改为 time.Time
        err = rows.Scan(&id, &name, &age, &email, &score, &createdAt)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }
        fmt.Printf("ID: %d, Name: %s, Age: %d, Score: %.2f\n", id, name, age, score)
    }

    // ---------- TCL: 显式事务 ----------
    tx, err := conn.Begin(context.Background())
    if err != nil {
        log.Fatal("开启事务失败:", err)
    }
    // 执行更新
    _, err = tx.Exec(context.Background(),
        `UPDATE users SET score = 100 WHERE name = 'Alice'`)
    if err != nil {
        tx.Rollback(context.Background())
        log.Fatal("更新失败，已回滚:", err)
    }
    // 提交事务
    err = tx.Commit(context.Background())
    if err != nil {
        log.Fatal("提交事务失败:", err)
    }
    fmt.Println("✅ 事务提交成功（Alice分数已更新）")

    fmt.Println("Go demo 运行完成")
}
运行方式
go mod tidy
go run main.go

Python 版本（使用 psycopg2）

安装依赖
pip install psycopg2-binary
完整代码（demo.py）
import psycopg2

def main():
    # 连接配置
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        database="testdb",
        user="postgres",
        password="your_password"
    )
    cur = conn.cursor()

    # ---------- DML: 插入数据 ----------
    # 使用参数化查询 %s
    cur.execute(
        "INSERT INTO users (name, age, email, score) VALUES (%s, %s, %s, %s)",
        ("Bob", 30, "bob@test.com", 85.0)
    )
    conn.commit()
    print("✅ 插入成功")

    # ---------- DQL: 查询数据 ----------
    cur.execute("SELECT id, name, age, email, score, created_at FROM users WHERE age > %s", (20,))
    rows = cur.fetchall()
    print("📊 年龄大于20的用户：")
    for row in rows:
        # row 是元组，按位置索引
        print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}, Score: {row[4]}")

    # ---------- TCL: 显式事务 ----------
    # 方式1：使用 connection 的 begin/commit/rollback 方法（psycopg2 默认自动提交，需手动控制）
    conn.autocommit = False  # 关闭自动提交，进入事务模式
    cur.execute("BEGIN")  # 或直接执行 BEGIN
    try:
        cur.execute("UPDATE users SET score = 95 WHERE name = 'Bob'")
        conn.commit()  # 提交
        print("✅ 事务提交成功（Bob分数已更新）")
    except Exception as e:
        conn.rollback()
        print("❌ 更新失败，已回滚:", e)

    # 恢复自动提交模式（可选）
    conn.autocommit = True
    cur.close()
    conn.close()
    print("Python demo 运行完成")

if __name__ == "__main__":
    main()
运行方式
python demo.py

Java 版本（使用 JDBC）

Maven 依赖（pom.xml）
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>
完整代码（JavaDemo.java）
import java.sql.*;

public class JavaDemo {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/testdb";
        String user = "postgres";
        String password = "your_password";

        // 使用 try-with-resources 自动管理连接
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // ---------- DML: 插入数据 ----------
            String insertSql = "INSERT INTO users (name, age, email, score) VALUES (?, ?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
                pstmt.setString(1, "Charlie");
                pstmt.setInt(2, 28);
                pstmt.setString(3, "charlie@test.com");
                pstmt.setDouble(4, 78.5);
                int affected = pstmt.executeUpdate();
                System.out.println("✅ 插入成功，影响行数: " + affected);
            }

            // ---------- DQL: 查询数据 ----------
            String selectSql = "SELECT id, name, age, email, score, created_at FROM users WHERE age > ?";
            try (PreparedStatement pstmt = conn.prepareStatement(selectSql)) {
                pstmt.setInt(1, 20);
                ResultSet rs = pstmt.executeQuery();
                System.out.println("📊 年龄大于20的用户：");
                while (rs.next()) {
                    long id = rs.getLong("id");
                    String name = rs.getString("name");
                    int age = rs.getInt("age");
                    double score = rs.getDouble("score");
                    // created_at 可获取为 Timestamp 或 String
                    Timestamp createdAt = rs.getTimestamp("created_at");
                    System.out.printf("ID: %d, Name: %s, Age: %d, Score: %.2f, Created: %s%n",
                            id, name, age, score, createdAt);
                }
                rs.close();
            }

            // ---------- TCL: 显式事务 ----------
            conn.setAutoCommit(false); // 关闭自动提交
            try (PreparedStatement pstmt = conn.prepareStatement("UPDATE users SET score = 88 WHERE name = ?")) {
                pstmt.setString(1, "Charlie");
                int affected = pstmt.executeUpdate();
                conn.commit(); // 提交事务
                System.out.println("✅ 事务提交成功（Charlie分数已更新），影响行数: " + affected);
            } catch (SQLException e) {
                conn.rollback();
                System.out.println("❌ 更新失败，已回滚: " + e.getMessage());
            } finally {
                conn.setAutoCommit(true); // 恢复自动提交
            }

            System.out.println("Java demo 运行完成");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
运行方式
# 使用 Maven 编译并运行
mvn compile
mvn exec:java -Dexec.mainClass="JavaDemo"

# 或直接使用 javac 和 java（需包含驱动jar包）
javac -cp "postgresql-42.7.3.jar:." JavaDemo.java
java -cp "postgresql-42.7.3.jar:." JavaDemo

技术要点总结

特性Go (pgx)Python (psycopg2)Java (JDBC)
参数占位符$1, $2, ...%s?
事务控制conn.Begin(), tx.Commit(), tx.Rollback()conn.autocommit=False, conn.commit(), conn.rollback()conn.setAutoCommit(false), conn.commit(), conn.rollback()
查询结果映射手动 Scan 到变量元组索引或游标字典按列名或索引获取
连接方式DSN 字符串关键字参数JDBC URL + Properties
错误处理返回 error异常捕获SQLException

所有示例均遵循以下最佳实践：

  • 使用参数化查询防止 SQL 注入
  • 显式管理事务边界
  • 及时释放资源（连接、语句、结果集）

项目难点与解决方案

核心难点

字符类型选择困惑：机器翻译文本中对char(n)、varchar(n)和text的描述存在混淆，特别是对char(n)"定长"特性的解释不够准确，容易导致开发者做出错误的数据类型选择。

解决方案

通过查阅PostgreSQL官方文档，明确了三种字符类型的本质区别：

  • char(n)：定长，不足n个字符时用空格填充，存在存储浪费
  • varchar(n)：变长，有长度上限
  • text：变长，无长度上限（最大1GB）

在实践中，char(n)几乎不被使用，推荐使用varchar(n)或text。

广度

本文覆盖了SQL五大分类（DDL、DML、DQL、DCL、TCL）、PostgreSQL常用数据类型、表约束、事务控制以及SELECT语句的逻辑执行顺序，涵盖了PostgreSQL入门所需的核心知识体系。

深度

深入剖析了：

  • 数值类型中numeric与real/double precision的精度差异及适用场景
  • 字符类型char(n)与varchar(n)的存储机制差异
  • 事务的ACID特性及PostgreSQL默认隔离级别（Read Committed）
  • SELECT语句的逻辑执行顺序与书写顺序的差异

复杂度

本文面向PostgreSQL初学者，内容复杂度为入门到中级水平。涉及的SQL语法均为标准语法，兼容PostgreSQL 9.6及以上所有版本。

官方文档

官方文档

参考链接

总结

本文系统性地介绍了PostgreSQL中SQL语言的五大分类及其核心操作，涵盖DDL（数据定义）、DML（数据操纵）、DQL（数据查询）、DCL（数据控制）和TCL（事务控制）。

在数据类型方面，详细对比了数值类型（smallint、integer、bigint、numeric、real、double precision）、字符类型（char(n)、varchar(n)、text）以及日期/时间类型（date、time、timestamp、interval）的特性与适用场景，明确指出numeric适用于金融等需要精确计算的场景，而char(n)因存储浪费在实际生产中几乎不被使用。

在表定义方面，介绍了PRIMARY KEY、NOT NULL、UNIQUE、CHECK、DEFAULT五种约束的作用与用法。在DQL层面，阐述了SELECT语句的完整语法及各子句（WHERE、GROUP BY、HAVING、ORDER BY、LIMIT/OFFSET）的功能，并重点说明了SELECT语句的逻辑执行顺序与书写顺序的差异。

在事务控制方面，介绍了BEGIN、COMMIT、ROLLBACK的用法及PostgreSQL默认的READ COMMITTED隔离级别。本文所有示例代码均基于PostgreSQL标准语法，兼容PostgreSQL 9.6及以上版本。

标签
#postgresql #笔记 #sql
PostgreSql简介
qq_45903207的博客
08-28 1万+
PostgreSQL简介
【PostgreSQL】手把手教学PostgreSQL
Fanjufei的博客
01-11 5454
PostgreSQL 是一个功能强大的开源对象关系数据库系统，它使用和扩展了 SQL 语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL 因其经过验证的架构、可靠性、数据完整性、强大的功能集、可扩展性以及软件背后的开源社区致力于始终如一地提供高性能和创新的解决方案而赢得了良好的声誉。数据类型基元：整数、数字、字符串、布尔值结构化：日期/时间、数组、范围/多范围、UUID文档：JSON/JSONB、XML、KEY-VALUE （HSTORE）几何：点、线、圆、多边形。
PGSQL(PostgreSQL)数据库基础篇：PostgreSQL 的 主要优点 、 劣势 、体系结构 、核心功能 、安装教程。
一码归一码@
12-09 1万+
1.最新版本和历史版本不分离存储，导致清理老旧版本时需要做更多的扫描，代价比较大但一般的数据库都有高峰期，如果合理安排VACUUM，这也不是很大的问题，而且在PostgreSQL9.0中VACUUM进一步被加强了。2.在PostgreSQL中，由于索引完全没有版本信息，不能实现Coverage index scan，即查询只扫描索引，不能直接从索引中返回所需的属性，还需要访问表，而Oracle与Innodb则可以。因为存储过程的存在也避免了在网络上大量原始的SQL语句的传输，这样的优势是显而易见的。
深入解析PostgreSQL中ON CONFLICT约束报错的原因与解决方案
weixin_29199873的博客
02-26 386
本文深入解析了PostgreSQL中ON CONFLICT子句报错“there is no unique or exclusion constraint”的五大常见原因与解决方案。核心在于ON CONFLICT必须精确匹配表上已有的唯一或排他约束。文章提供了从SQL语句检查、约束核对到具体修复的完整诊断流程，并解释了其设计原理与最佳实践，帮助开发者高效解决这一常见问题。
数据库之PostgreSQL详解
热门推荐
励志成为一名优秀的全能型软件开发工程师。
06-09 2万+
在日常操作中，对于一组相关操作，通常要求要么都成功，要么都失败。在关系型数据库中，称这一组操作为事务。原子性A：事务是一个最小的执行单位，一次事务中的操作要么都成功，要么都失败。一致性C：在事务完成时，所有数据必须保持在一致的状态。（事务完成后吗，最终结果和预期结果是一致的）隔离性：一次事务操作，要么是其他事务操作前的状态，要么是其他事务操作后的状态，不存在中间状态。持久性：事务提交后，数据会落到本地磁盘，修改是永久性的。
Postgresql13笔记
m0_46486963的博客
06-25 1万+
Postgresql笔记，还在更新中，预计2月22日结束本次学习，如果存在任何疑问，请大胆评论，本人不怕社死。
SQL约束大全
2401_87975790的博客
11-26 851
SQL约束是用于确保数据完整性、一致性和有效性的数据库规则。主要包含六大类型：1）NOT NULL禁止空值；2）UNIQUE确保值唯一；3）PRIMARY KEY（非空+唯一）作为行标识；4）FOREIGN KEY建立表间引用关系；5）CHECK验证数据条件；6）DEFAULT设置默认值。约束可在建表时定义或后期添加，MySQL 8.0.16+开始完全支持CHECK约束。使用建议包括：优先使用无业务意义的主键、高并发场景慎用外键、注意不同数据库对NULL的处理差异等。约束管理可通过ALTER TABLE操作
数据库笔记PostgreSQL
qq_44816970的博客
05-14 1208
这里写自定义目录标题数据库笔记PostgreSQL 13二. 数据库运行环境1. 创建表1.1 基本语法1.2 使用主键约束1.3 使用外键约束1.4 使用非空约束1.5 使用唯一性约束1.6 使用默认约束2. 修改表2.1 修改表名2.2 修改字段的数据类型2.3 修改字段名2.4 添加字段2.5 删除字段2.6 删除表的外键约束3. 删除表3.1 删除没有被关联的表3.2 删除被其他表关联的主表数据类型和运算符1. 整数类型2. 浮点数类型3. 任意精度类型4. 日期与时间类型4.1 TIME4.2 D
PostgreSQL - SQL语句1
AI工程化、开源分享、文档翻译、代码笔记
09-15 1254
第4章 SQL语法 4.1 词汇结构 4.1.1标识符和关键词 4.1.2常数 4.1.2.1字符串常量 4.1.2.2 具有C样式转义的字符串常量 4.1.2.3 具有Unicode转义的字符串常量 4.1.2.4美元报价字符串常量 4.1.2.5位串常量 4.1.2.6数值常数 4.1.2.7其他类型的常数 4.1.3 操作符 4.1.4特殊字符 4.1.5评论 4.1.6运算符优先级 4.2 值表达式 4.2.1列引用 4.2.2位置参数 4.2.3下标 4.2.4领域选择 4.2.5 操作符调用 4
PostgreSQL基础与SQL-1
最新发布
weixin_51955414的博客
07-10 348
PostgreSQL 基础与 SQL 摘要 PostgreSQL 是一个功能强大的开源对象关系数据库管理系统(ORDBMS)，结合了关系型和面向对象特性。它源于1986年加州大学伯克利分校的POSTGRES项目，1996年更名为PostgreSQL。核心特点包括：完全开源、SQL标准兼容性高、支持ACID事务、MVCC并发控制等，常被用作Oracle替代方案。 PostgreSQL采用客户端/服务器架构，通过TCP/IP通信。服务器端包含Postmaster监听进程、共享内存区域、后端进程。
9、PostgreSQL 数据管理与约束：全面指南
nft7creator的博客
07-08 94
本文全面介绍了 PostgreSQL 数据库中的核心概念，包括数据类型、数据操作、表管理、视图以及外键约束等。通过详细的示例和解释，帮助开发者更好地理解和应用 PostgreSQL 进行数据库设计与管理。内容涵盖了布尔型、字符型、数值型、时间型等数据类型，数据类型转换、内置函数、视图创建与使用，以及外键约束的定义和优化策略，为构建高效稳定的数据库系统提供了实用指导。
PostgreSQL 基础知识笔记（摘抄PPT）
Catalany的博客
05-18 1530
摘抄的PPT上的内容
13.PostgreSQL Alter,Truncate Table
wujianyouhun的专栏
08-14 1065
在 PostgreSQL 中，命令用于添加，修改，删除一张已经存在表的列。另外你也可以用命令添加和删除约束。
PostgreSQL 基础（一）-- 新手教程
chinusyan的专栏
05-29 1万+
PostgreSQL 基础（一）-- 教程
【Postrsql】postgresql的介绍、安装和使用
Mo_nor的博客
03-19 7229
PostgreSQL是一个功能强大的开源关系型数据库系统。经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。目前PostgreSQL可以运行在所有主流操作系统上，包括Linux、Unix和Windows。PostgreSQL是完全的，支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。
PostgreSQL-01-入门篇-简介
被拯救的威尼斯的博客
08-06 5321
相比之下，PostgreSQL 采用了类似于 MIT 的许可协议，允许开发人员在开源或闭源产品中自由使用，这使得 PostgreSQL 成为了一种极具吸引力的选择。1990 年 6 月发布的版本 2 包含了重新设计的规则系统。Postgres95 的开发重点在于识别和解决后端代码的问题，而 PostgreSQL 的开发则转向了更具争议性的特性和功能，尽管这两个方面的工作同时进行。随着外部用户社区的增长，代码维护的时间成本增加，为了减少支持负担，伯克利的 POSTGRES 项目在版本 4.2 时正式终止。
postgreSQL安装教程及使用方法（保姆级）
2403_89073657的博客
05-21 2万+
PostgreSQL是一个功能强大、稳定且可扩展的开源对象关系型数据库管理系统。它支持标准SQL和多种高级特性，如事务处理、外键、触发器和多版本并发控制（MVCC），适合处理大规模数据和复杂查询。安装PostgreSQL涉及下载、设置安装路径、配置数据库用户密码和端口号等步骤。安装完成后，可以通过命令行或图形界面（如pgAdmin4）进行数据库管理，执行增删改查等操作。安装过程中可能遇到的问题包括连接失败、编码错误等，通常需要检查网络设置或重新安装解决。
全网最全：MySQL 与 PostgreSQL 深度对比
草原孤狼的专栏
06-09 1万+
全网最全的开源数据库综合对比
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Wang's Blog

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值