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

PostgreSQL - 基础入门 它是开源的,其源代码是免费提供的。它是用PHP编写的PostgreSQL的基于Web的管理工具。PostgreSQL是跨平台的,可以在许多操作系统上运行,如Linux,FreeBSD,OS X,Solaris和Microsoft Windows等。它是一个命令行工具,也是管理PostgreSQL的主要工具。左外连接返回从“ON”条件中指定的左侧表中的所有行,只返回满足条件的另一个表中的行。右外连接返回从“ON”条件中指定的右侧表中的所有行,只返回满足条件的另一个表中的行。右边的空格填充到相等大小的字符。 阅读详情

纲要

  • 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简介 PostgreSQL简介 阅读详情

相关推荐

【PostgreSQL】手把手教学PostgreSQL

PostgreSQL 是一个功能强大的开源对象关系数据库系统,它使用和扩展了 SQL 语言,并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL 因其经过验证的架构、可靠性、数据完整性、强大的功能集、可扩展性以及软件背后的开源社区致力于始终如一地提供高性能和创新的解决方案而赢得了良好的声誉。数据类型基元:整数、数字、字符串、布尔值结构化:日期/时间、数组、范围/多范围、UUID文档:JSON/JSONB、XML、KEY-VALUE (HSTORE)几何:点、线、圆、多边形。

Fanjufei的博客 5582

PGSQL(PostgreSQL)数据库基础篇:PostgreSQL 的 主要优点 、 劣势 、体系结构 、核心功能 、安装教程。

1.最新版本和历史版本不分离存储,导致清理老旧版本时需要做更多的扫描,代价比较大但一般的数据库都有高峰期,如果合理安排VACUUM,这也不是很大的问题,而且在PostgreSQL9.0中VACUUM进一步被加强了。2.在PostgreSQL中,由于索引完全没有版本信息,不能实现Coverage index scan,即查询只扫描索引,不能直接从索引中返回所需的属性,还需要访问表,而Oracle与Innodb则可以。因为存储过程的存在也避免了在网络上大量原始的SQL语句的传输,这样的优势是显而易见的。

一码归一码@ 1万+

Veeam ONE 13双数据库架构安装避坑指南:SQL Server与PostgreSQL协同配置

Veeam ONE 是面向虚拟化环境的统一监控与报表平台,其核心依赖于实时监控与历史分析分离的双数据库架构。该架构基于 Microsoft SQL Server 承载高频写入的监控数据(Monitoring DB),同时依托 PostgreSQL(14+,UTF-8编码)支撑复杂报表与趋势分析(Reporting DB)。这种分工提升了数据吞吐与查询深度,但也引入跨系统权限校准、编码一致性、服务账户协同等工程挑战。实际部署中,Windows Server 2019/2022 的 IIS 模块版本、DNS 反

weixin_26846533的博客 293

数据库之PostgreSQL详解

在日常操作中,对于一组相关操作,通常要求要么都成功,要么都失败。在关系型数据库中,称这一组操作为事务。原子性A:事务是一个最小的执行单位,一次事务中的操作要么都成功,要么都失败。一致性C:在事务完成时,所有数据必须保持在一致的状态。(事务完成后吗,最终结果和预期结果是一致的)隔离性:一次事务操作,要么是其他事务操作前的状态,要么是其他事务操作后的状态,不存在中间状态。持久性:事务提交后,数据会落到本地磁盘,修改是永久性的。

励志成为一名优秀的全能型软件开发工程师。 2万+

深入解析PostgreSQL中ON CONFLICT约束报错的原因与解决方案

本文深入解析了PostgreSQL中ON CONFLICT子句报错“there is no unique or exclusion constraint”的五大常见原因与解决方案。核心在于ON CONFLICT必须精确匹配表上已有的唯一或排他约束。文章提供了从SQL语句检查、约束核对到具体修复的完整诊断流程,并解释了其设计原理与最佳实践,帮助开发者高效解决这一常见问题。

weixin_29199873的博客 401

【PostgreSQL】启航PostgreSQL之旅:基础构建与环境配置

PostgreSQL,通常简称为Postgres,是一种开源的对象关系型数据库管理系统(ORDBMS)。起源于伯克利的POSTGRES项目,自1996年发布第一个版本以来,已经发展成为世界上最先进的开源数据库之一。PostgreSQL以其高度的稳定性、强大的并发控制、丰富的数据类型支持以及良好的可扩展性闻名。完全支持SQL标准,并在此基础上增加了众多高级功能,如复杂的查询处理、多版本并发控制(MVCC)、触发器、视图、外键约束等。登录数据库登录时可以指定用户名(默认为postgres)和数据库名。

weixin_43298211的博客 1487

Postgresql13笔记

Postgresql笔记,还在更新中,预计2月22日结束本次学习,如果存在任何疑问,请大胆评论,本人不怕社死。

m0_46486963的博客 1万+

9、PostgreSQL 数据管理与约束:全面指南

本文全面介绍了 PostgreSQL 数据库中的核心概念,包括数据类型、数据操作、表管理、视图以及外键约束等。通过详细的示例和解释,帮助开发者更好地理解和应用 PostgreSQL 进行数据库设计与管理。内容涵盖了布尔型、字符型、数值型、时间型等数据类型,数据类型转换、内置函数、视图创建与使用,以及外键约束的定义和优化策略,为构建高效稳定的数据库系统提供了实用指导。

nft7creator的博客 96

Active Record 与 PostgreSQL:官方指南深度解析——从专属数据类型到全文搜索与约束实现

本指南以 Rails 仓库 [guides/source/active_record_postgresql.md](https://link.gitcode.com/i/e8efb8b83d42e38ea75f53f1bfbc33f4) 为主线,系统讲解 Active Record PostgreSQL 适配器的专属能力,包括数据类型映射、UUID 主键、包含列索引、可延迟外键/唯一约束、排他约束

gitblog_00341的博客 821

AI基础入门(大模型基础篇)——技术视角:你应该知道的LLM基础知识

本文从技术视角解析了大模型的工作原理,主要介绍了三个核心概念:Token、温度和Embedding。首先,大模型通过逐个添加Token来生成文本,Token数量直接影响上下文窗口大小和计费标准。其次,通过引入温度参数控制随机性,温度越高输出越多样化。最后,解释了字符串如何通过One-Hot编码和神经网络压缩转化为向量形式的Embedding,这是AI处理各类输入的基础。这些概念对理解大模型应用开发至关重要,Token决定处理能力,温度影响输出风格,Embedding则是信息转换的关键步骤。

小志的博客 1510

无人机散热系统设计:从材料选择到智能调控的全面解析

本文全面解析无人机散热系统设计,从材料选择到智能调控,探讨了导热界面材料、轻量化散热结构和智能调控算法的应用。通过实战案例,展示了如何在极端环境下优化散热性能,提升无人机的工作效率和可靠性。

weixin_42567279的博客 430

AI时代首先数据库-PostgreSQL实战笔记 13 PostgreSQL SQL基础语法(上):DDL、DML、DQL

本文系统梳理了PostgreSQL的SQL基础语法,重点展示其在DDL、DML、DQL中的独特优势。通过丰富的原生数据类型(如JSONB、数组、范围类型)、强大的约束机制(CHECK、UNIQUE、外键等)和高级查询能力(窗口函数、CTE、LATERAL JOIN),PostgreSQL不仅提升数据质量与一致性,更实现复杂业务逻辑的高效表达,真正做到了“数据库即规则引擎”。

源码获取 | 运行部署 | 定制二开修改,如果回复不及时请耐心等待,请见谅。 261

BUUCTF--[OGeek2019]babyrop

才学习了基本的ROP流程,到处找题练,不过也没做出来几道题,以这道32位的题作为例题吧。 这道题是BUUCTF上pwn练习题里的[OGeek2019]babyrop。 代码审计 老规矩先checksec一下: 没有canary保护,nx保护开启排除shellcode可能性,FULL RELEO为地址随机化。 观察主函数,先设定了一个闹铃,到时间就会强制退出程序,解除闹铃的方法在上一篇博客中提到过了, 这道题中闹铃函数依然对我们解题起不到什么干扰作用。 主函数的思路大概是:生成一个随机数,把这个随机数作为

N1rvana的博客 6010

SQL约束大全

SQL约束是用于确保数据完整性、一致性和有效性的数据库规则。主要包含六大类型:1)NOT NULL禁止空值;2)UNIQUE确保值唯一;3)PRIMARY KEY(非空+唯一)作为行标识;4)FOREIGN KEY建立表间引用关系;5)CHECK验证数据条件;6)DEFAULT设置默认值。约束可在建表时定义或后期添加,MySQL 8.0.16+开始完全支持CHECK约束。使用建议包括:优先使用无业务意义的主键、高并发场景慎用外键、注意不同数据库对NULL的处理差异等。约束管理可通过ALTER TABLE操作

2401_87975790的博客 861

家里摄像头录像存 NAS,空间爆满了怎么办?一招实现设定容量自动清理实现循环存储!

📷 家用摄像头录像久了会撑爆 NAS 吗?别担心,这篇文章教如何实现自动清理录像,释放 NAS 空间,避免爆盘🧹

XiaoqiangClub的博客 1882

MTK Camera HAL 与 FeaturePipe 架构解析:从硬件抽象到功能管线的工程落地路径

MTK 的 Camera 系统在 Android 平台下采用高度模块化的 HAL 与 FeaturePipe 架构组合,分别承担着底层硬件抽象与上层功能封装的职责。通过将 ISP、3A 算法、Sensor 驱动与图像处理节点解耦封装于功能链条中,联发科平台实现了灵活可控的影像能力调度机制。本文将基于真实项目实践,系统解析 MTK Camera HAL 与 FeaturePipe 架构的设计思路、核心模块与运行机制,结合 AE/AWB/AF 控制流、Buffer 管理策略、功能管线构建方法与多线程调度实现方式

努力分享一些人工智能、计算机视觉、影像等相关的知识干货! 1576

数据库笔记PostgreSQL

这里写自定义目录标题数据库笔记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

qq_44816970的博客 1220

【OpenClaw从入门到精通】第34篇:医疗AI的“Agent OS”时代:基于WiseClaw构建医院专属智能体平台(2026实测版)

2026年医疗AI迈入实战落地新阶段,全球首个医疗OpenClaw平台WiseClaw正式发布,香港中文大学(深圳)医院完成业内首个私有化部署。本文基于真实医疗场景需求与公开技术资料,系统拆解医疗智能体平台的建设逻辑:从医疗Agent OS的五大核心特性切入,详解门诊病历质控、用药审查、医院运行管理三大临床场景的智能体搭建流程,提供完整的私有化部署代码、安全配置方案与实操演示。文章兼顾新手入门与进阶需求,严格遵循医疗合规要求,强调“只读不写+人工复核”原则,所有方案均经过虚拟实测验证。

专注于人工智能、软件开发、工控自动化、工厂数字化及智能化等领域,希望和大家共同进步! 790

PostgreSQL - SQL语句1

第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

AI工程化、开源分享、文档翻译、代码笔记 1282

河洛理数【陈抟】每月月卦推演每日运卦

Step2:每卦中的日爻遇到二十四节气中的当日为节(月初)或气(月中)的时候,所代表的日期需要变更。正常来说,屯卦分别代表初一到初六,但是由于当月初二时立秋节,所以初爻就不能作为初一、而是要作为立秋节开始时(文中还精算到了立秋是从初二的午时开始),如下。说的代表月运的基础卦上进行当月日卦的推演方式。老样子,我们还是按照书中举得例子卦在这边进行一步步的推演,最后做总结。Step1:从月卦爻下一爻开始,自下而上,进行五个爻的爻变,新推演的每个卦代表六天,如下。假设当月月卦是即济卦,月爻在二爻,如图所示(

Daniel专栏 764

使用mid360运行lio-livox对比fast-lio2

msg_type为激光雷达发出的数据格式,有livox自定义数据类型格式CustomMsg和ros标准的PointCloud2格式,因此首先需要确定激光雷达端配置的是哪种格式。可以从激光雷达的ros驱动中看到驱动端有三种格式可以选,驱动端的数字2对应lio-livox中的1,驱动端的0对应lio-livox的格式0。如果使用的是Ubuntu18.04编译完成后运行直接成功,如果使用20.04运行会报错,需要修改以下源代码。针对livox-mid360 运行lio-livox的一些问题。

z1872385的博客 1231

SQLAlchemy PostgreSQL 方言完全指南:数据类型、区间类型与驱动选型

SQLAlchemy 为 PostgreSQL 提供了一整套原生方言支持,涵盖从 ARRAY、JSONB、HSTORE、ENUM 到 Range/Multirange 的丰富数据类型,以及 EXCLUDE 约束、`INSERT ... ON CONFLICT` 等 DML 构造与多个 DBAPI 驱动。本文以 [doc/build/dialects/postgresql.rst](https://

gitblog_00011的博客 226

Statistics and Machine Learning Toolbox

Statistics and Machine Learning Toolbox(带书签),MATLAB统计学习工具

PostgreSQL基础与SQL-1

PostgreSQL 基础与 SQL 摘要 PostgreSQL 是一个功能强大的开源对象关系数据库管理系统(ORDBMS),结合了关系型和面向对象特性。它源于1986年加州大学伯克利分校的POSTGRES项目,1996年更名为PostgreSQL。核心特点包括:完全开源、SQL标准兼容性高、支持ACID事务、MVCC并发控制等,常被用作Oracle替代方案。 PostgreSQL采用客户端/服务器架构,通过TCP/IP通信。服务器端包含Postmaster监听进程、共享内存区域、后端进程。

weixin_51955414的博客 369

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

汉书HE8,好用的汇编代码编写工具

汉书HE8,好用的汇编代码编写工具早期编写汇编代码所用功能强大

PostgreSQL 基础知识笔记(摘抄PPT)

摘抄的PPT上的内容

Catalany的博客 1536

LinePhone3.2.7(android端最新版本) sip客户端

SIP客户端For android:LinePhone3.2.7(最新版本),官网地址:http://www.linphone.org/

13.PostgreSQL Alter,Truncate Table

在 PostgreSQL 中,命令用于添加,修改,删除一张已经存在表的列。另外你也可以用命令添加和删除约束。

wujianyouhun的专栏 1067

【离线版下载】Dll-Files Fixer 教程:如何快速修复 DLL 文件缺失(附完整步骤)

刚启动游戏,弹窗提示"缺少 VCRUNTIME140.dll";或者某个老软件双击就报错"找不到 xxx.dll,无法继续执行代码"。这类提示意味着系统里某个 DLL 文件丢了或损坏了。重装系统动静大、耗时久,而且未必能根治问题。如果你不想折腾,可以先试试 Dll-Files Fixer 3.2.9 中文版的 DLL 文件修复方案。 ## 它是什么 Dll-Files Fixer 3.2.9

gitblog_00166的博客 852
上一篇: PostgreSQL笔记12:PGDATA 数据目录结构与核心文件深度解析
下一篇: PostgreSQL笔记14:子查询与嵌套查询的完整分类、执行模型与性能优化
Wang's Blog
博客等级 码龄12年 7440粉丝 2427原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

Wang's Blog

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

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

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

打赏作者

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

抵扣说明:

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

余额充值