65、数据库基础与 SQL SELECT 语句详解

数据库基础与 SQL SELECT 语句详解

1. 数据库的数据组织

数据库管理系统将数据存储在数据库中，数据在数据库里被组织成表、行和列。表是相关数据的集合，表中的数据又进一步按行和列来组织。行代表关于单个项目的完整信息，列则是关于该项目的单个信息片段。

以 CoffeeDB 数据库中的 Coffee 表为例，该表有 18 行，每行包含一种咖啡的信息，分为三列：Description（咖啡描述）、ProdNum（产品编号）和 Price（每磅价格）。

Description	ProdNum	Price
Bolivian Dark	14 - 001	8.95
Bolivian Medium	14 - 002	8.95
Brazilian Dark	15 - 001	7.95
…	…	…

2. 列的数据类型

数据库表的列会被分配数据类型，这些数据类型是 SQL 数据类型，而非 Java 数据类型。以下是一些标准 SQL 数据类型及其通常兼容的 Java 数据类型：
| SQL 数据类型 | 描述 | 对应的 Java 数据类型 |
| — | — | — |
| INTEGER 或 INT | 整数 | int |
| CHARACTER(n) 或 CHAR(n) | 长度为 n 的固定长度字符串 | String |
| VARCHAR(n) | 最大长度为 n 的可变长度字符串 | String |
| REAL | 单精度浮点数 | float |
| DOUBLE | 双精度浮点数 | double |
| DECIMAL(t, d) | 总位数为 t，小数点后有 d 位的十进制值 | java.math.BigDecimal |
| DATE | 日期 | java.sql.Date |

在 Coffee 表中，Description 列的数据类型为 CHAR(25)，ProdNum 列的数据类型为 CHAR(10)，Price 列的数据类型为 DOUBLE。

3. 主键

大多数数据库表都有主键，主键是用于标识表中特定行的列，该列中的每个值都是唯一的。若尝试在主键列中存储重复数据，将会出错。

在 Coffee 表中，ProdNum 列是主键，因为每种咖啡的产品编号都是唯一的。其他例子包括：
- 存储员工数据的表，员工 ID 列可作为主键。
- 存储手机库存数据的表，手机序列号列可作为主键。
- 存储发票数据的表，发票编号列可作为主键。

4. SQL SELECT 语句简介

SELECT 语句用于从数据库中检索数据，其基本形式为：

SELECT Columns FROM Table

其中，Columns 是一个或多个列名，Table 是表名。例如，在 CoffeeDB 数据库上执行的以下 SELECT 语句：

SELECT Description FROM Coffee

该语句将检索 Coffee 表中每一行的 Description 列。

5. 在 Java 程序中执行 SELECT 语句的步骤

在 Java 程序中，执行 SELECT 语句的结果会返回一个 ResultSet 对象。执行 SELECT 语句的过程可总结为以下步骤：

graph LR
    A[获取数据库连接] --> B[将包含 SQL 语句的字符串传递给 DBMS]
    B --> C{SQL 语句是否有结果返回}
    C -- 是 --> D[返回 ResultSet 对象到程序]
    C -- 否 --> E[无结果返回]
    D --> F[处理 ResultSet 对象的内容]
    F --> G[关闭数据库连接]
    E --> G

1. 获取数据库连接。
2. 将包含 SQL 语句的字符串传递给 DBMS。若 SQL 语句有结果返回，会返回一个 ResultSet 对象到程序。
3. 处理返回的 ResultSet 对象的内容。
4. 完成数据库操作后，关闭连接。

以下是一个完整的 Java 程序示例，用于显示 CoffeeDB 数据库中 Coffee 表的 Description 列：

import java.sql.*;   // Needed for JDBC classes

/**
 * This program demonstrates how to issue an SQL
 * SELECT statement to a database using JDBC.
 */
public class ShowCoffeeDescriptions {
    public static void main(String[] args) {
        // Create a named constant for the URL.
        // NOTE: This value is specific for Java DB.
        final String DB_URL = "jdbc:derby:CoffeeDB";

        try {
            // Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);

            // Create a Statement object.
            Statement stmt = conn.createStatement();

            // Create a string with a SELECT statement.
            String sqlStatement = "SELECT Description FROM Coffee";

            // Send the statement to the DBMS.
            ResultSet result = stmt.executeQuery(sqlStatement);

            // Display a header for the listing.
            System.out.println("Coffees Found in the Database");
            System.out.println("-----------------------------");

            // Display the contents of the result set.
            // The result set will have three columns.
            while (result.next()) {
                System.out.println(result.getString("Description"));
            }

            // Close the connection.
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

6. SELECT 语句的更多用法

• 选择多列 ：可以在 SELECT 语句中用逗号分隔列名来选择多个列。例如：

SELECT Description, Price FROM Coffee

该语句将检索 Coffee 表中每一行的 Description 列和 Price 列。

• 选择所有列 ：使用 * 字符可以选择表中的所有列。例如：

SELECT * FROM Coffee

7. 使用 WHERE 子句指定搜索条件

WHERE 子句可与 SELECT 语句一起使用，用于指定搜索条件，只有满足条件的行才会出现在结果集中。SELECT 语句带 WHERE 子句的一般格式为：

SELECT Columns FROM Table WHERE Criteria

其中，Criteria 是一个条件表达式。例如：

SELECT * FROM Coffee WHERE Price > 12.00

该语句将返回 Coffee 表中 Price 列值大于 12.00 的所有行。

标准 SQL 支持以下关系运算符用于编写 WHERE 子句中的条件表达式：
| 运算符 | 含义 |
| — | — |
| > | 大于 |
| < | 小于 |
| >= | 大于或等于 |
| <= | 小于或等于 |
| = | 等于 |
| <> | 不等于 |

8. 字符串比较与 LIKE 运算符

• 字符串比较 ：SQL 中的字符串比较是区分大小写的。若要进行不区分大小写的比较，可以使用 UPPER() 或 LOWER() 函数。例如：

SELECT * FROM Coffee WHERE UPPER(Description) = 'FRENCH ROAST DARK'

• LIKE 运算符 ：当需要搜索包含特定子字符串的行时，可以使用 LIKE 运算符。% 符号是通配符，表示任意零个或多个字符，_ 表示单个字符。例如：

SELECT * FROM Coffee WHERE Description LIKE '%Decaf%'

该语句将返回 Description 列中包含 “Decaf” 子字符串的所有行。

9. 使用 AND 和 OR 逻辑运算符

可以使用 AND 和 OR 逻辑运算符在 WHERE 子句中指定多个搜索条件。例如：

SELECT * FROM Coffee WHERE Price > 10.00 AND Price < 14.00

该语句将返回 Price 列值大于 10.00 且小于 14.00 的所有行。

SELECT * FROM Coffee WHERE Description LIKE '%Dark%' OR ProdNum LIKE '16%'

该语句将返回 Description 列包含 “Dark” 子字符串或 ProdNum 列以 “16” 开头的所有行。

10. 对 SELECT 查询结果进行排序

可以使用 ORDER BY 子句对 SELECT 查询的结果进行排序。例如：

SELECT * FROM Coffee ORDER BY Price

该语句将按 Price 列升序排列 Coffee 表中的所有行。

若要按降序排列，可以使用 DESC 运算符：

SELECT * FROM Coffee WHERE Price > 9.95 ORDER BY Price DESC

11. SQL 数学函数

SQL 提供了几个用于执行计算的函数：
- AVG ：计算特定列的平均值。例如：

SELECT AVG(Price) FROM Coffee

• SUM ：计算列值的总和。例如：

SELECT SUM(Price) FROM Coffee

• MIN 和 MAX ：确定列中的最小值和最大值。例如：

SELECT MIN(Price) FROM Coffee
SELECT MAX(Price) FROM Coffee

• COUNT ：确定表中的行数。例如：

SELECT COUNT(*) FROM Coffee

以下是一个 Java 程序示例，用于使用 MIN、MAX 和 AVG 函数查找 Coffee 表中的最低、最高和平均价格：

import java.sql.*;

/**
 * This program demonstrates some of the SQL math functions.
 */
public class CoffeeMath {
    public static void main(String[] args) {
        // Variables to hold the lowest, highest, and
        // average prices of coffee.
        double lowest = 0.0,
                highest = 0.0,
                average = 0.0;

        // Create a named constant for the URL.
        // NOTE: This value is specific for Java DB.
        final String DB_URL = "jdbc:derby:CoffeeDB";

        try {
            // Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);

            // Create a Statement object.
            Statement stmt = conn.createStatement();

            // Create SELECT statements to get the lowest, highest,
            // and average prices from the Coffee table.
            String minStatement = "SELECT MIN(Price) FROM Coffee";
            String maxStatement = "SELECT MAX(Price) FROM Coffee";
            String avgStatement = "SELECT AVG(Price) FROM Coffee";

            // Get the lowest price.
            ResultSet minResult = stmt.executeQuery(minStatement);
            if (minResult.next())
                lowest = minResult.getDouble(1);

            // Get the highest price.
            ResultSet maxResult = stmt.executeQuery(maxStatement);
            if (maxResult.next())
                highest = maxResult.getDouble(1);

            // Get the average price.
            ResultSet avgResult = stmt.executeQuery(avgStatement);
            if (avgResult.next())
                average = avgResult.getDouble(1);

            // Display the results.
            System.out.printf("Lowest price: $%.2f\n", lowest);
            System.out.printf("Highest price: $%.2f\n", highest);
            System.out.printf("Average price: $%.2f\n", average);

            // Close the connection.
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

通过上述内容，我们详细了解了数据库的数据组织方式、SQL SELECT 语句的基本用法、在 Java 程序中执行 SELECT 语句的步骤，以及如何使用 WHERE 子句、字符串比较、逻辑运算符、排序和数学函数来进行更复杂的查询。这些知识对于开发数据库应用程序非常重要。

数据库基础与 SQL SELECT 语句详解（续）

12. 综合示例：根据用户输入进行查询

在实际应用中，我们常常需要根据用户的输入来动态生成 SQL 查询语句。以下是一个 Java 程序示例，它允许用户输入一个最低价格，然后在 Coffee 表中搜索 Price 列大于或等于该价格的行。

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

/**
 * This program lets the user search for coffees
 * priced at a minimum value.
 */
public class CoffeeMinPrice {
    public static void main(String[] args) {
        double minPrice;        // To hold the minimum price
        int coffeeCount = 0;    // To count coffees that qualify

        // Create a named constant for the URL.
        // NOTE: This value is specific for Java DB.
        final String DB_URL = "jdbc:derby:CoffeeDB";

        // Create a Scanner object for keyboard input.
        Scanner keyboard = new Scanner(System.in);

        // Get the minimum price from the user.
        System.out.print("Enter the minimum price: ");
        minPrice = keyboard.nextDouble();

        try {
            // Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);

            // Create a Statement object.
            Statement stmt = conn.createStatement();

            // Create a string containing a SELECT statement.
            // Note that we are incorporating the user's input
            // into the string.
            String sqlStatement =
                    "SELECT * FROM Coffee WHERE Price >= " +
                            Double.toString(minPrice);

            // Send the statement to the DBMS.
            ResultSet result = stmt.executeQuery(sqlStatement);

            // Display the contents of the result set.
            // The result set will have three columns.
            while (result.next()) {
                // Display a row from the result set.
                System.out.printf("%25s %10s %5.2f\n",
                        result.getString("Description"),
                        result.getString("ProdNum"),
                        result.getDouble("Price"));

                // Increment the counter.
                coffeeCount++;
            }

            // Display the number of qualifying coffees.
            System.out.println(coffeeCount + " coffees found.");

            // Close the connection.
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

这个程序的执行流程如下：
1. 提示用户输入最低价格。
2. 创建数据库连接。
3. 根据用户输入的最低价格动态生成 SQL 查询语句。
4. 执行查询并将结果存储在 ResultSet 对象中。
5. 遍历 ResultSet 对象，显示符合条件的咖啡信息，并统计符合条件的咖啡数量。
6. 显示符合条件的咖啡数量。
7. 关闭数据库连接。

13. 注意事项

在使用 SQL 进行数据库查询时，有一些注意事项需要牢记：
- SQL 语法与 Java 语法的差异 ：
- SQL 中的等于运算符是一个等号（=），而 Java 中是两个等号（==）。
- SQL 中的不等于运算符是 <>，而 Java 中是 !=。
- SQL 中的字符串字面量使用单引号（’），而 Java 中使用双引号（”）。
- 大小写敏感性 ：
- SQL 中的关键字和表名通常是不区分大小写的，但字符串比较默认是区分大小写的。可以使用 UPPER() 或 LOWER() 函数来进行不区分大小写的比较。
- 通配符的使用 ：
- % 表示任意零个或多个字符。
- _ 表示单个字符。

14. 总结

本文详细介绍了数据库的数据组织方式，包括表、行和列的概念，以及列的数据类型和主键的作用。同时，深入探讨了 SQL SELECT 语句的各种用法，包括基本的查询、选择多列、选择所有列、使用 WHERE 子句指定搜索条件、字符串比较、LIKE 运算符、逻辑运算符、排序和数学函数等。此外，还展示了如何在 Java 程序中执行 SQL 查询，并处理查询结果。

以下是本文涵盖的主要内容总结表格：
| 主题 | 详细内容 |
| — | — |
| 数据组织 | 表、行、列；列的数据类型（SQL 数据类型与 Java 数据类型的对应关系）；主键 |
| SELECT 语句 | 基本形式；选择多列；选择所有列 |
| WHERE 子句 | 指定搜索条件；关系运算符（>、<、>=、<=、=、<>） |
| 字符串比较 | 大小写敏感性；UPPER() 和 LOWER() 函数 |
| LIKE 运算符 | 通配符（% 和 _）的使用 |
| 逻辑运算符 | AND 和 OR |
| 排序 | ORDER BY 子句；DESC 运算符 |
| 数学函数 | AVG、SUM、MIN、MAX、COUNT |
| Java 程序执行查询 | 获取数据库连接；执行 SQL 语句；处理 ResultSet 对象；关闭连接 |

通过掌握这些知识，你可以更有效地进行数据库查询和开发数据库应用程序。希望本文对你有所帮助！

15. 流程图回顾

为了更清晰地展示在 Java 程序中执行 SELECT 语句的过程，再次给出流程图：

graph LR
    A[获取数据库连接] --> B[将包含 SQL 语句的字符串传递给 DBMS]
    B --> C{SQL 语句是否有结果返回}
    C -- 是 --> D[返回 ResultSet 对象到程序]
    C -- 否 --> E[无结果返回]
    D --> F[处理 ResultSet 对象的内容]
    F --> G[关闭数据库连接]
    E --> G

这个流程图概括了在 Java 程序中执行 SELECT 语句的主要步骤，从获取连接到最终关闭连接，涵盖了查询执行和结果处理的整个过程。

16. 代码示例总结

以下是本文中涉及的主要 Java 代码示例总结列表：
1. 显示 Coffee 表的 Description 列 ：

import java.sql.*;   // Needed for JDBC classes

/**
 * This program demonstrates how to issue an SQL
 * SELECT statement to a database using JDBC.
 */
public class ShowCoffeeDescriptions {
    public static void main(String[] args) {
        // Create a named constant for the URL.
        // NOTE: This value is specific for Java DB.
        final String DB_URL = "jdbc:derby:CoffeeDB";

        try {
            // Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);

            // Create a Statement object.
            Statement stmt = conn.createStatement();

            // Create a string with a SELECT statement.
            String sqlStatement = "SELECT Description FROM Coffee";

            // Send the statement to the DBMS.
            ResultSet result = stmt.executeQuery(sqlStatement);

            // Display a header for the listing.
            System.out.println("Coffees Found in the Database");
            System.out.println("-----------------------------");

            // Display the contents of the result set.
            // The result set will have three columns.
            while (result.next()) {
                System.out.println(result.getString("Description"));
            }

            // Close the connection.
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

2. 显示 Coffee 表的 Description 和 Price 列 ：

import java.sql.*;   // Needed for JDBC classes

/**
 * This program displays the coffee descriptions
 * and their prices.
 */
public class ShowDescriptionsAndPrices {
    public static void main(String[] args) {
        // Create a named constant for the URL.
        // NOTE: This value is specific for Java DB.
        final String DB_URL = "jdbc:derby:CoffeeDB";

        try {
            // Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);

            // Create a Statement object.
            Statement stmt = conn.createStatement();

            // Create a string with a SELECT statement.
            String sqlStatement =
                    "SELECT Description, Price FROM Coffee";

            // Send the statement to the DBMS.
            ResultSet result = stmt.executeQuery(sqlStatement);

            // Display the contents of the result set.
            // The result set will have three columns.
            while (result.next()) {
                System.out.printf("%25s %.2f\n",
                        result.getString("Description"),
                        result.getDouble("Price"));
            }

            // Close the connection.
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

3. 显示 Coffee 表的所有列 ：

import java.sql.*;   // Needed for JDBC classes

/**
 * This program displays all of the columns in the
 * Coffee table of the CoffeeDB database.
 */
public class ShowCoffeeData {
    public static void main(String[] args) {
        // Create a named constant for the URL.
        // NOTE: This value is specific for Java DB.
        final String DB_URL = "jdbc:derby:CoffeeDB";

        try {
            // Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);

            // Create a Statement object.
            Statement stmt = conn.createStatement();

            // Create a string with a SELECT statement.
            String sqlStatement = "SELECT * FROM Coffee";

            // Send the statement to the DBMS.
            ResultSet result = stmt.executeQuery(sqlStatement);

            // Display the contents of the result set.
            // The result set will have three columns.
            while (result.next()) {
                System.out.printf("%25s %10s %5.2f\n",
                        result.getString("Description"),
                        result.getString("ProdNum"),
                        result.getDouble("Price"));
            }

            // Close the connection.
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

4. 根据用户输入的最低价格查询咖啡信息 ：

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

/**
 * This program lets the user search for coffees
 * priced at a minimum value.
 */
public class CoffeeMinPrice {
    public static void main(String[] args) {
        double minPrice;        // To hold the minimum price
        int coffeeCount = 0;    // To count coffees that qualify

        // Create a named constant for the URL.
        // NOTE: This value is specific for Java DB.
        final String DB_URL = "jdbc:derby:CoffeeDB";

        // Create a Scanner object for keyboard input.
        Scanner keyboard = new Scanner(System.in);

        // Get the minimum price from the user.
        System.out.print("Enter the minimum price: ");
        minPrice = keyboard.nextDouble();

        try {
            // Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);

            // Create a Statement object.
            Statement stmt = conn.createStatement();

            // Create a string containing a SELECT statement.
            // Note that we are incorporating the user's input
            // into the string.
            String sqlStatement =
                    "SELECT * FROM Coffee WHERE Price >= " +
                            Double.toString(minPrice);

            // Send the statement to the DBMS.
            ResultSet result = stmt.executeQuery(sqlStatement);

            // Display the contents of the result set.
            // The result set will have three columns.
            while (result.next()) {
                // Display a row from the result set.
                System.out.printf("%25s %10s %5.2f\n",
                        result.getString("Description"),
                        result.getString("ProdNum"),
                        result.getDouble("Price"));

                // Increment the counter.
                coffeeCount++;
            }

            // Display the number of qualifying coffees.
            System.out.println(coffeeCount + " coffees found.");

            // Close the connection.
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

5. 使用数学函数查询最低、最高和平均价格 ：

import java.sql.*;

/**
 * This program demonstrates some of the SQL math functions.
 */
public class CoffeeMath {
    public static void main(String[] args) {
        // Variables to hold the lowest, highest, and
        // average prices of coffee.
        double lowest = 0.0,
                highest = 0.0,
                average = 0.0;

        // Create a named constant for the URL.
        // NOTE: This value is specific for Java DB.
        final String DB_URL = "jdbc:derby:CoffeeDB";

        try {
            // Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);

            // Create a Statement object.
            Statement stmt = conn.createStatement();

            // Create SELECT statements to get the lowest, highest,
            // and average prices from the Coffee table.
            String minStatement = "SELECT MIN(Price) FROM Coffee";
            String maxStatement = "SELECT MAX(Price) FROM Coffee";
            String avgStatement = "SELECT AVG(Price) FROM Coffee";

            // Get the lowest price.
            ResultSet minResult = stmt.executeQuery(minStatement);
            if (minResult.next())
                lowest = minResult.getDouble(1);

            // Get the highest price.
            ResultSet maxResult = stmt.executeQuery(maxStatement);
            if (maxResult.next())
                highest = maxResult.getDouble(1);

            // Get the average price.
            ResultSet avgResult = stmt.executeQuery(avgStatement);
            if (avgResult.next())
                average = avgResult.getDouble(1);

            // Display the results.
            System.out.printf("Lowest price: $%.2f\n", lowest);
            System.out.printf("Highest price: $%.2f\n", highest);
            System.out.printf("Average price: $%.2f\n", average);

            // Close the connection.
            conn.close();
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}

通过对这些代码示例的学习和实践，你可以更好地掌握在 Java 程序中使用 SQL 进行数据库查询的方法。希望你在实际开发中能够灵活运用这些知识，提高开发效率和质量。