66、数据库操作全解析：查询、插入、更新与删除-优快云博客

本文链接：https://blog.youkuaiyun.com/dapp9builder/article/details/150522345

数据库操作全解析：查询、插入、更新与删除

在数据库的日常使用中，我们经常需要进行各种操作，如查询数据、插入新记录、更新现有数据以及删除不需要的记录。下面将详细介绍这些操作的相关知识和具体实现方法。

1. 基础概念与查询操作

首先，我们来了解一些基础概念。 ResultSet 对象是用于存储数据库查询结果的对象，它就像一个表格，包含了查询返回的所有行和列的数据。

对于 SQL 查询语句，例如 SELECT Id FROM Account ，我们可以分析出该语句是从 Account 表中检索数据，并且只检索 Id 这一列。

假设存在一个名为 Inventory 的表，包含 ProductID （ CHAR(10) 类型）、 QtyOnHand （ INT 类型）和 Cost （ DOUBLE 类型）三列，以下是不同需求的 SELECT 语句示例：
- 返回表中所有行的所有列 ：

SELECT * FROM Inventory;

返回表中所有行的 ProductID 列 ：

SELECT ProductID FROM Inventory;

返回表中所有行的 ProductID 列和 QtyOnHand 列 ：

SELECT ProductID, QtyOnHand FROM Inventory;

返回 Cost 小于 17.00 的行的 ProductID 列 ：

SELECT ProductID FROM Inventory WHERE Cost < 17.00;

返回 ProductID 以 “ZZ” 结尾的行的所有列 ：

SELECT * FROM Inventory WHERE ProductID LIKE '%ZZ';

LIKE 运算符用于在查询中进行模糊匹配。在 LIKE 运算符使用的字符模式中， % 符号表示任意数量（包括零个）的任意字符，而 _ 字符表示单个任意字符。

若要对 SELECT 语句的结果按特定列进行排序，可以使用 ORDER BY 子句。例如，按 ProductID 列升序排序：

SELECT * FROM Inventory ORDER BY ProductID;

以下是一个使用 Java 代码通过 JDBC 连接数据库并执行 SELECT 语句的示例：

import java.sql.*;

public class DatabaseQueryExample {
    public static void main(String[] args) {
        final String DB_URL = "jdbc:derby:CoffeeDB";
        String sql = "SELECT * FROM Coffee";

        try {
            // 创建数据库连接
            Connection conn = DriverManager.getConnection(DB_URL);
            // 创建 Statement 对象
            Statement stmt = conn.createStatement();
            // 执行查询语句
            ResultSet result = stmt.executeQuery(sql);

            // 处理查询结果
            while (result.next()) {
                System.out.println("Description: " + result.getString("Description"));
                System.out.println("ProdNum: " + result.getString("ProdNum"));
                System.out.println("Price: $" + result.getDouble("Price"));
            }

            // 关闭连接
            conn.close();
        } catch (SQLException e) {
            System.out.println("ERROR: " + e.getMessage());
        }
    }
}

在这个示例中，首先创建了数据库连接，然后创建 Statement 对象并执行 SELECT 语句，将结果存储在 ResultSet 对象中。通过 result.next() 方法逐行遍历结果集，并使用 getString 和 getDouble 方法获取相应列的值。最后关闭数据库连接。

2. 插入操作

在 SQL 中，使用 INSERT 语句向数据库表中插入新行。其一般格式有两种：
- 不指定列名 ：

INSERT INTO TableName VALUES (Value1, Value2, etc...);

例如，向 Coffee 表中插入一行数据：

INSERT INTO Coffee VALUES ('Honduran Dark', '22-001', 8.65);

这里需要注意，字符串值要用单引号括起来，并且值的顺序要与表中列的顺序一致。

指定列名 ：

INSERT INTO TableName (ColumnName1, ColumnName2, etc...) VALUES (Value1, Value2, etc...);

同样插入上述数据，指定列名的语句如下：

INSERT INTO Coffee (Description, ProdNum, Price) VALUES ('Honduran Dark', '22-001', 8.65);

或者改变列名顺序：

INSERT INTO Coffee (ProdNum, Price, Description) VALUES ('22-001', 8.65, 'Honduran Dark');

使用 JDBC 执行 INSERT 语句的步骤如下：
1. 从 Connection 对象获取 Statement 对象，使用 createStatement 方法。
2. 使用 Statement 对象的 executeUpdate 方法执行 INSERT 语句，该方法返回一个 int 值，表示插入的行数。

以下是一个完整的 Java 程序示例：

import java.util.Scanner;
import java.sql.*;

public class CoffeeInserter {
    public static void main(String[] args) {
        String description; 
        String prodNum;     
        double price;       

        final String DB_URL = "jdbc:derby:CoffeeDB";
        Scanner keyboard = new Scanner(System.in);

        try {
            // 创建数据库连接
            Connection conn = DriverManager.getConnection(DB_URL);
            // 获取新咖啡的数据
            System.out.print("Enter the coffee description: ");
            description = keyboard.nextLine();
            System.out.print("Enter the product number: ");
            prodNum = keyboard.nextLine();
            System.out.print("Enter the price: ");
            price = keyboard.nextDouble();

            // 创建 Statement 对象
            Statement stmt = conn.createStatement();
            // 创建 INSERT 语句字符串
            String sqlStatement = "INSERT INTO Coffee " +
                    "(ProdNum, Price, Description) " +
                    "VALUES ('" +
                    prodNum + "', " +
                    price + ", '" +
                    description + "')";
            // 执行 INSERT 语句
            int rows = stmt.executeUpdate(sqlStatement);
            // 显示结果
            System.out.println(rows + " row(s) added to the table.");
            // 关闭连接
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

在这个程序中，首先获取用户输入的咖啡描述、产品编号和价格，然后创建 INSERT 语句并执行，最后显示插入的行数并关闭连接。需要注意的是，如果表中的某一列是主键，该列的值必须是唯一的，否则数据库管理系统（DBMS）将不允许插入重复值。

3. 插入操作流程图

graph TD;
    A[开始] --> B[创建数据库连接];
    B --> C[获取用户输入数据];
    C --> D[创建 Statement 对象];
    D --> E[创建 INSERT 语句];
    E --> F[执行 INSERT 语句];
    F --> G[显示插入行数];
    G --> H[关闭数据库连接];
    H --> I[结束];

通过以上步骤和示例，我们可以清晰地了解数据库查询和插入操作的基本原理和实现方法。在实际应用中，根据具体需求灵活运用这些知识，能够高效地管理数据库中的数据。

数据库操作全解析：查询、插入、更新与删除

4. 更新操作

在 SQL 中，使用 UPDATE 语句来更改表中现有行的内容。其一般格式为：

UPDATE Table
   SET Column = Value
   WHERE Criteria

其中， Table 是表名， Column 是列名， Value 是要存储在该列中的值， Criteria 是一个条件表达式。

例如，将巴西无咖啡因咖啡（Brazilian Decaf）的价格更改为 9.95：

UPDATE Coffee
   SET Price = 9.95
   WHERE Description = 'Brazilian Decaf'

再如，将产品编号为 20 - 001 的咖啡描述更新为 ‘Galapagos Organic Medium’：

UPDATE Coffee
   SET Description = 'Galapagos Organic Medium'
   WHERE ProdNum = '20-001'

也可以更新多行数据。比如将所有危地马拉咖啡的价格改为 12.95：

UPDATE Coffee
   SET Price = 12.95
   WHERE ProdNum LIKE '21%'

使用 JDBC 执行 UPDATE 语句的步骤与执行 INSERT 语句类似：
1. 从 Connection 对象使用 createStatement 方法获取 Statement 对象。
2. 使用 Statement 对象的 executeUpdate 方法执行 UPDATE 语句，该方法返回一个 int 值，表示受影响的行数。

以下是一个 Java 程序示例，用于更新咖啡表中指定产品的价格：

import java.util.Scanner;
import java.sql.*;

public class CoffeePriceUpdater {
    public static void main(String[] args) {
        String prodNum;
        double price;
        final String DB_URL = "jdbc:derby:CoffeeDB";
        Scanner keyboard = new Scanner(System.in);

        try {
            // 创建数据库连接
            Connection conn = DriverManager.getConnection(DB_URL);
            // 创建 Statement 对象
            Statement stmt = conn.createStatement();
            // 获取所需咖啡的产品编号
            System.out.print("Enter the product number: ");
            prodNum = keyboard.nextLine();

            // 显示咖啡的当前数据
            if (findAndDisplayProduct(stmt, prodNum)) {
                // 获取新价格
                System.out.print("Enter the new price: ");
                price = keyboard.nextDouble();
                // 更新咖啡的价格
                updatePrice(stmt, prodNum, price);
            } else {
                // 指定的产品编号未找到
                System.out.println("That product was not found.");
            }

            // 关闭连接
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }

    /**
     * 查找并显示指定咖啡的数据
     * @param stmt 数据库的 Statement 对象
     * @param prodNum 所需咖啡的产品编号
     * @return 是否找到产品
     * @throws SQLException SQL 异常
     */
    public static boolean findAndDisplayProduct(Statement stmt, String prodNum) throws SQLException {
        boolean productFound;
        // 创建 SELECT 语句
        String sqlStatement = "SELECT * FROM Coffee WHERE ProdNum = '" + prodNum + "'";
        // 执行查询语句
        ResultSet result = stmt.executeQuery(sqlStatement);

        // 显示结果集内容
        if (result.next()) {
            // 显示产品信息
            System.out.println("Description: " + result.getString("Description"));
            System.out.println("Product Number: " + result.getString("ProdNum"));
            System.out.println("Price: $" + result.getDouble("Price"));
            // 设置标志表示产品已找到
            productFound = true;
        } else {
            // 表示产品未找到
            productFound = false;
        }

        return productFound;
    }

    /**
     * 更新指定咖啡的价格
     * @param stmt 数据库的 Statement 对象
     * @param prodNum 所需咖啡的产品编号
     * @param price 产品的新价格
     * @throws SQLException SQL 异常
     */
    public static void updatePrice(Statement stmt, String prodNum, double price) throws SQLException {
        // 创建 UPDATE 语句
        String sqlStatement = "UPDATE Coffee " +
                "SET Price = " + Double.toString(price) +
                " WHERE ProdNum = '" + prodNum + "'";
        // 执行 UPDATE 语句
        int rows = stmt.executeUpdate(sqlStatement);
        // 显示结果
        System.out.println(rows + " row(s) updated.");
    }
}

在这个程序中，首先获取用户输入的产品编号，然后调用 findAndDisplayProduct 方法查找并显示该产品的当前信息。如果找到产品，再获取用户输入的新价格，并调用 updatePrice 方法更新价格。需要注意的是，使用 UPDATE 语句时千万不要遗漏 WHERE 子句和条件表达式，否则可能会更改表中所有行的内容。

5. 删除操作

在 SQL 中，使用 DELETE 语句从表中删除一行或多行数据。其一般格式为：

DELETE FROM Table WHERE Criteria

其中， Table 是表名， Criteria 是一个条件表达式。

例如，删除产品编号为 20 - 001 的咖啡记录：

DELETE FROM Coffee WHERE ProdNum = '20-001'

也可以删除多行数据。比如删除所有描述以 ‘Sumatra’ 开头的咖啡记录：

DELETE FROM Coffee WHERE Description LIKE 'Sumatra%'

使用 JDBC 执行 DELETE 语句的步骤与执行 INSERT 和 UPDATE 语句类似：
1. 从 Connection 对象使用 createStatement 方法获取 Statement 对象。
2. 使用 Statement 对象的 executeUpdate 方法执行 DELETE 语句，该方法返回一个 int 值，表示删除的行数。

以下是一个 Java 程序示例，用于删除咖啡表中指定的咖啡记录：

import java.util.Scanner;
import java.sql.*;

public class CoffeeDeleter {
    public static void main(String[] args) {
        String prodNum;
        String sure;
        final String DB_URL = "jdbc:derby:CoffeeDB";
        Scanner keyboard = new Scanner(System.in);

        try {
            // 创建数据库连接
            Connection conn = DriverManager.getConnection(DB_URL);
            // 创建 Statement 对象
            Statement stmt = conn.createStatement();
            // 获取所需咖啡的产品编号
            System.out.print("Enter the product number: ");
            prodNum = keyboard.nextLine();

            // 显示咖啡的当前数据
            if (findAndDisplayProduct(stmt, prodNum)) {
                // 确认用户是否要删除该产品
                System.out.print("Are you sure you want to delete this item? (y/n): ");
                sure = keyboard.nextLine();

                if (Character.toUpperCase(sure.charAt(0)) == 'Y') {
                    // 删除指定的咖啡
                    deleteCoffee(stmt, prodNum);
                } else {
                    System.out.println("The item was not deleted.");
                }
            } else {
                // 指定的产品编号未找到
                System.out.println("That product was not found.");
            }

            // 关闭连接
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }

    /**
     * 查找并显示指定咖啡的数据
     * @param stmt 数据库的 Statement 对象
     * @param prodNum 所需咖啡的产品编号
     * @return 是否找到产品
     * @throws SQLException SQL 异常
     */
    public static boolean findAndDisplayProduct(Statement stmt, String prodNum) throws SQLException {
        boolean productFound;
        // 创建 SELECT 语句
        String sqlStatement = "SELECT * FROM Coffee WHERE ProdNum = '" + prodNum + "'";
        // 执行查询语句
        ResultSet result = stmt.executeQuery(sqlStatement);

        // 显示结果集内容
        if (result.next()) {
            // 显示产品信息
            System.out.println("Description: " + result.getString("Description"));
            System.out.println("Product Number: " + result.getString("ProdNum"));
            System.out.println("Price: $" + result.getDouble("Price"));
            // 设置标志表示产品已找到
            productFound = true;
        } else {
            // 表示产品未找到
            productFound = false;
        }

        return productFound;
    }

    /**
     * 删除指定的咖啡
     * @param stmt 数据库的 Statement 对象
     * @param prodNum 所需咖啡的产品编号
     * @throws SQLException SQL 异常
     */
    public static void deleteCoffee(Statement stmt, String prodNum) throws SQLException {
        // 创建 DELETE 语句
        String sqlStatement = "DELETE FROM Coffee " +
                "WHERE ProdNum = '" + prodNum + "'";
        // 执行 DELETE 语句
        int rows = stmt.executeUpdate(sqlStatement);
        // 显示结果
        System.out.println(rows + " row(s) deleted.");
    }
}

在这个程序中，首先获取用户输入的产品编号，然后调用 findAndDisplayProduct 方法查找并显示该产品的当前信息。如果找到产品，会询问用户是否确认删除，若用户确认，则调用 deleteCoffee 方法删除该记录。同样，使用 DELETE 语句时不要遗漏 WHERE 子句和条件表达式，否则可能会删除表中所有行。

6. 更新与删除操作总结表格

操作类型	SQL 语句格式	JDBC 执行步骤	注意事项
更新操作	`UPDATE Table SET Column = Value WHERE Criteria`	1. 从 `Connection` 对象获取 `Statement` 对象；2. 使用 `Statement` 对象的 `executeUpdate` 方法执行 `UPDATE` 语句	不要遗漏 `WHERE` 子句和条件表达式，否则可能更改所有行内容
删除操作	`DELETE FROM Table WHERE Criteria`	1. 从 `Connection` 对象获取 `Statement` 对象；2. 使用 `Statement` 对象的 `executeUpdate` 方法执行 `DELETE` 语句	不要遗漏 `WHERE` 子句和条件表达式，否则可能删除所有行

7. 更新与删除操作流程图

graph TD;
    A[开始] --> B[创建数据库连接];
    B --> C[获取用户输入数据];
    C --> D[创建 Statement 对象];
    D --> E{操作类型};
    E -->|更新| F[创建 UPDATE 语句];
    E -->|删除| G[创建 DELETE 语句];
    F --> H[执行 UPDATE 语句];
    G --> I[执行 DELETE 语句];
    H --> J[显示受影响行数];
    I --> J;
    J --> K[关闭数据库连接];
    K --> L[结束];

通过以上对数据库更新和删除操作的详细介绍，我们可以全面掌握数据库中各种数据操作的方法。在实际开发中，合理运用这些操作能够更好地管理和维护数据库中的数据，确保数据的准确性和完整性。