postgreSQL学习笔记（二）

性能优化-索引

索引可以提升数据库查询性能
索引是预先创建的一种有序的数据结构

创建索引

CREATE INDEX index_name ON table_name (column_name);

验证索引是否生效

EXPLAIN SELECT * FROM table_name WHERE indexed_column = 'some_value';

Seq Scan表示使用全表扫描没用索引
Index Scan表示成功使用索引

索引的好处说完了，代价呢？

空间成本：会占用额外的硬盘空间
维护成本：虽然增加了查询SELECT的速度,但是当数据库要INSERT,UPDATE,DELETE等操作时，还要额外操作索引，降低写操作的速度

使用原则

给谁用
- 出现在WHERE子句的列
- 使用JOINd的列
- 使用ORDER BY的列
谁慎用
- 写的操作比较多的列
- 值的重复度比较高的列（比如值是性别）

索引失效

通配符在前面的时候，索引会失效
WHERE name LIKE '张%'; – 能使用索引 (从目录的’Z’部分开始找)。

WHERE name LIKE '%三'; – 无法使用索引 (不知道从哪个字母开始找)，退化为全表扫描

为什么有时候创建索引了但是没有使用?

数据库中有一个查询优化器 (Query Optimizer)，它会智能地判断使用索引的成本。
核心原因：当数据量非常小时，直接进行全表扫描（一眼看完一张小纸条）比先查索引再找数据（先看目录再翻页）的成本更低，效率更高。因此，优化器会智能地放弃使用索引。只有当数据量大到一定程度，索引的优势才会体现出来。

事务

将多个操作打包，要么全部成功，要么全部失败的机制

核心命令

BEGIN：开启事务
COMMIT：提交事务，将所有更改保存
ROLLBACK：回滚事务

-- 1. 开启事务
BEGIN;
-- 2. 执行一系列操作
UPDATE accounts SET balance = balance - 100 WHERE name = '张三';
UPDATE accounts SET balance = balance + 100 WHERE name = '李四';
-- 3. 根据情况选择提交或回滚
-- 如果所有操作都成功:
COMMIT;
-- 如果中途发生错误或想取消:
ROLLBACK;

JDBC

依稀记得自己在寒假学java的时候看过这个课，也清楚的记得都忘光了

什么是JDBC

JDBC (Java Database Connectivity) 是Java提供的一套标准API，用于连接和操作关系型数据库。它本身是规范，需要由具体的JDBC驱动 (Driver) 来实现。

类比: JDBC是“普通话”规范，而PostgreSQL的JDBC驱动就是一个能将“普通话”翻译成“PostgreSQL方言”的翻译官。
使用maven添加依赖

建立连接

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
String url = "jdbc:postgresql://localhost:5432/database_name";
String user = "your_username";
String password = "your_password";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
    // 在这里执行数据库操作
} catch (SQLException e) {
    e.printStackTrace();
}

执行查询

import java.sql.Statement;
import java.sql.ResultSet;
// ... 在 try-with-resources 块中 ...
String sql = "SELECT id, name FROM authors;";
try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        System.out.println("ID: " + id + ", Name: " + name);
    }
}

执行写入

import java.sql.PreparedStatement;
// ... 在 try-with-resources 块中 ...
// 使用 '?' 作为参数占位符
String sql = "INSERT INTO authors(name) VALUES(?);";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    // 安全地绑定参数
    pstmt.setString(1, "新作者");
    // 执行更新操作
    int affectedRows = pstmt.executeUpdate();
    System.out.println(affectedRows + " 行受影响。");
}

为防止SQL注入，必须使用PreparedStatement它允许我们使用 ?作为参数占位符，然后安全地将变量绑定到这些占位符上，而不是像Statement那样手动拼接SQL字符串。这既安全又高效。

在java中管理事务

点击打开

public class Main {
    private static final String URL = "jdbc:postgresql://localhost:5432/learning_sql";
    private static final String USER = "postgres";
    private static final String PASSWORD = "123456";
    public static void main(String[] args) {
       setupDatabase();
       System.out.println("事务演示");
       //成功转账
        System.out.println("\n[1.zhangsan 向 lisi 成功转账100.00]");
        System.out.println("转账前账户信息：");
        selectAccounts();
        transMoney("zhangsan","lisi",100.00);
        System.out.println("转账后账户余额");
        selectAccounts();
        //失败转账
        System.out.println("\n[2.zhangsan 向 lisi 转账10000.00 失败]");
        System.out.println("转账前账户信息：");
        selectAccounts();
        transMoney("zhangsan","lisi",10000.00);
        System.out.println("转账失败后账户余额");
        selectAccounts();
        System.out.println("演示结束");
    }
    public static void transMoney (String fromAccount, String toAccount, double amount) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            //关闭自动提交，开启事务
            conn.setAutoCommit(false);
            //从转出账户中扣款
            String subtractSql = "UPDATE accounts SET balance = balance - ? WHERE name = ? AND balance >= ?;";
            try (PreparedStatement pstmt1 = conn.prepareStatement(subtractSql)) {
                pstmt1.setDouble(1, amount);
                pstmt1.setString(2, fromAccount);
                pstmt1.setDouble(3, amount);
                int affectedRows = pstmt1.executeUpdate();
                if (affectedRows == 0) {
                    throw new SQLException("账户 " + fromAccount + " 余额不足，无法转账。");
                }
                System.out.println("从账户 " + fromAccount + " 扣款 " + amount);
            }
            String addSql = "UPDATE accounts SET balance = balance + ? WHERE name = ?;";
            try (PreparedStatement pstmt2 = conn.prepareStatement(addSql)) {
                pstmt2.setDouble(1, amount);
                pstmt2.setString(2, toAccount);
                pstmt2.executeUpdate();
                System.out.println("向账户 " + toAccount + " 增加 " + amount);
            }
            conn.commit();
            System.out.println("转账成功:事务已经提交");
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
            if (conn != null) {
                try {
                    System.out.println("转账失败:正在回滚事务...");
                    conn.rollback();
                    System.out.println("回滚成功");
                }
                catch (SQLException ex) {
                    System.err.println("回滚失败");
                    ex.printStackTrace();
                } finally {
                    if (conn != null) {
                        try {
                            conn.setAutoCommit(true);
                            conn.close();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
                }
            }
        }
    }
    public static void setupDatabase() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        Statement stmt = conn.createStatement()) {
            //如果存在就删除旧的表
            stmt.execute("DROP TABLE IF EXISTS accounts;");
            //创建新表
            stmt.execute("CREATE TABLE accounts (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, balance NUMERIC(10,2) NOT NULL);");
            //插入初始数据
            stmt.execute("INSERT INTO accounts (name, balance) VALUES ('zhangsan',1000.00);");
            stmt.execute("INSERT INTO accounts (name, balance) VALUES ('lisi', 500.00);");
            System.out.println("数据库初始化成功");
        } catch (SQLException e) {
            System.err.println("数据库初始化失败");
            e.printStackTrace();
        }
    }
    public static void selectAccounts() {
        String sql = "SELECT name, balance FROM accounts ORDER BY name;";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql)) {
            System.out.println("--------------------------------");
            while (rs.next()) {
                System.out.printf("账户： %s, 余额： %.2f\n", rs.getString("name"), rs.getDouble("balance"));
            }
            System.out.println("--------------------------------");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}