MySQL - SELECT 查询

在创建表并填充数据之后，下一步自然是检索和检查这些数据。SELECT 语句是 SQL 中用于此操作的主要工具。它是最常用的命令，允许您从数据库表中获取数据，并将其以结构化的结果集（result set）形式呈现。

数据检索的核心：`SELECT` 语句

MySQL 的 SELECT 语句用于查询数据库并检索符合您指定条件的数据。结果总是以一个名为“结果集”（result-set）的临时表返回。

注意：您可以直接通过 MySQL 客户端（如命令行 mysql> 提示符或 GUI 工具）执行此命令，也可以通过使用 PHP、Node.js、Java 或 Python 等语言编写的应用程序进行编程调用。

语法

下面是 SELECT 语句的通用语法，展示了其最常用的子句：

SELECT
    column1, column2, ... -- 指定您想要的列
FROM
    table_name -- 指定要查询的表
[WHERE condition] -- 可选：根据条件过滤行
[ORDER BY column_to_sort ASC|DESC] -- 可选：对结果进行排序
[LIMIT number_of_rows] -- 可选：限制返回的行数
[OFFSET starting_row]; -- 可选：在返回结果前跳过一定数量的行

要选择表中的所有列，可以使用星号通配符：SELECT * FROM table_name;。虽然这很方便，但在生产代码中明确列出您需要的列是一种最佳实践。
WHERE 子句用于过滤记录，只提取满足特定条件的记录。
ORDER BY 子句按升序（ASC，默认）或降序（DESC）对结果集进行排序。
LIMIT 和 OFFSET 子句常用于分页（例如，每页显示 10 条结果）。

实际示例：查询数据库

我们来看一个实际的例子。首先，我们将创建一个现代的 customers 表。

示例设置

此查询将创建一个带有自增主键（auto-incrementing primary key）的 customers 表，这是一种常见且推荐的做法。

-- 最佳实践：使用 AUTO_INCREMENT 为每行生成唯一的 ID。
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

现在，我们向表中插入一些示例数据：

INSERT INTO customers (name, age, address, salary)
VALUES
    ('Ramesh', 32, 'Ahmedabad', 2000.00),
    ('Khilan', 25, 'Delhi', 1500.00),
    ('Kaushik', 23, 'Kota', 2000.00),
    ('Chaitali', 25, 'Mumbai', 6500.00),
    ('Hardik', 27, 'Bhopal', 8500.00),
    ('Komal', 22, 'Hyderabad', 4500.00),
    ('Muffy', 24, 'Indore', 10000.00);

检索所有列

要从 customers 表中获取所有数据，请使用 SELECT * 语句：

SELECT * FROM customers;

这将返回表中的所有行和所有列。

id	name	age	address	salary
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

检索特定列

为了更高效，请仅指定您需要的列：

SELECT id, name, salary FROM customers;

结果集将只包含请求的列：

id	name	salary
1	Ramesh	2000.00
2	Khilan	1500.00
3	Kaushik	2000.00
4	Chaitali	6500.00
5	Hardik	8500.00
6	Komal	4500.00
7	Muffy	10000.00

使用别名提高清晰度

您可以使用 AS 关键字在结果集中重命名列。这称为别名（aliasing）。它不会更改表中的列名；它只更改输出中的标签。这对于使结果更具可读性或避免 JOIN 操作中的列名冲突非常有用。

SELECT name AS customer_name, salary AS monthly_income FROM customers;

输出列现在已用别名标记：

客户姓名	月收入
Ramesh	2000.00
Khilan	1500.00
Kaushik	2000.00
Chaitali	6500.00
Hardik	8500.00
Komal	4500.00
Muffy	10000.00

在查询中执行计算

SELECT 语句也可以执行计算。您可以根据表数据计算值，甚至运行独立的计算。

示例：计算奖金

在这里，我们根据客户的薪水计算每个客户潜在的 10% 年度奖金。

SELECT name, salary, (salary * 12 * 0.10) AS potential_annual_bonus FROM customers;

结果包含一个新计算出的列：

姓名	薪水	潜在年度奖金
Ramesh	2000.00	2400.00
Khilan	1500.00	1800.00
Kaushik	2000.00	2400.00
Chaitali	6500.00	7800.00
Hardik	8500.00	10200.00
Komal	4500.00	5400.00
Muffy	10000.00	12000.00

从应用程序代码连接和查询（安全地）

软件开发的一个关键方面是从应用程序代码与数据库交互。最重要的原则是绝不通过拼接字符串与用户输入来构建查询。这会产生一个严重的安全漏洞，称为 SQL 注入（SQL Injection）。始终使用预处理语句（prepared statements）或参数化查询（parameterized queries），其中 SQL 命令和数据是分开发送到数据库的。

现代客户端程序示例

下面是使用流行编程语言获取数据的现代安全示例。它们假设您已安装必要的数据库驱动程序/库（例如，通过 composer、npm、maven、pip）。凭据应安全存储，例如在环境变量中，而不是硬编码。

PHP
Node.js
Java
Python



在 PHP 中，使用 PDO 进行数据库交互是一种现代最佳实践，因为它支持多种数据库系统和强大的预处理语句。

<?php
$host = getenv('DB_HOST') ?: '127.0.0.1';
$db   = getenv('DB_NAME') ?: 'TUTORIALS';
$user = getenv('DB_USER') ?: 'root';
$pass = getenv('DB_PASS') ?: 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully.\n";

    // 获取所有客户
    $stmt = $pdo->query('SELECT id, name, age FROM customers');

    echo "All Customers:\n";
    while ($row = $stmt->fetch()) {
        echo sprintf("ID: %d, Name: %s, Age: %d\n", $row['id'], $row['name'], $row['age']);
    }
} catch (\PDOException $e) {
    // 在实际应用程序中，应该记录此错误，而不仅仅是回显。
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>



现代 Node.js 开发严重依赖 `async/await` 来处理数据库查询等异步操作。`mysql2/promise` 库提供了基于 Promise 的 API，与此模式完美契合。

const mysql = require('mysql2/promise');

async function main() {
  let connection;
  try {
    // 在实际应用程序中，使用环境变量存储凭据
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'password',
      database: process.env.DB_NAME || 'TUTORIALS',
    });

    console.log('Connected successfully!');

    // [rows, fields] 解构是 mysql2 的一个特性
    const [rows, fields] = await connection.execute('SELECT id, name, salary FROM customers');

    console.log('Query Results:');
    console.table(rows);

  } catch (err) {
    console.error('Error connecting or querying the database:', err);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Connection closed.');
    }
  }
}

main();



现代 Java 使用 `try-with-resources` 块自动管理 `Connection` 和 `Statement` 等资源，确保它们始终被关闭。出于安全和性能原因，即使是没有参数的查询，使用 `PreparedStatement` 也是执行查询的标准做法。

import java.sql.*;

public class SelectQueryExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";

        String sql = "SELECT id, name, age, salary FROM customers";

        try (Connection con = DriverManager.getConnection(url, user, password);
             PreparedStatement pst = con.prepareStatement(sql);
             ResultSet rs = pst.executeQuery()) {

            System.out.println("Connected and query executed successfully!");
            System.out.println("Customer Records:");
            System.out.printf("%-5s %-20s %-5s %-10s\n", "ID", "Name", "Age", "Salary");
            System.out.println("---------------------------------------------");

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                double salary = rs.getDouble("salary");
                System.out.printf("%-5d %-20s %-5d %-10.2f\n", id, name, age, salary);
            }
        } catch (SQLException e) {
            // 在实际应用程序中，请使用日志框架
            e.printStackTrace();
        }
    }
}



`mysql-connector-python` 库是 Oracle 官方的驱动程序。最佳实践是使用 `try...except...finally` 块来确保数据库连接被关闭，即使发生错误也是如此。通过将参数元组传递给 `execute` 方法来实现参数化查询。

import mysql.connector
from mysql.connector import Error

def main():
    connection = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='TUTORIALS',
            user='root',
            password='password'
        )

        if connection.is_connected():
            print('Connected successfully!')
            cursor = connection.cursor(dictionary=True) # dictionary=True 将行作为字典返回

            cursor.execute("SELECT id, name, salary FROM customers")
            records = cursor.fetchall()

            print("\nTotal number of rows is: ", cursor.rowcount)
            print("\nPrinting each row")
            for row in records:
                print(f"Id: {row['id']}, Name: {row['name']}, Salary: {row['salary']}")

    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

if __name__ == '__main__':
    main()

MySQL - SELECT 查询

MySQL - SELECT 查询

数据检索的核心：SELECT 语句

语法

实际示例：查询数据库

示例设置

使用别名提高清晰度

在查询中执行计算

示例：计算奖金

从应用程序代码连接和查询（安全地）

现代客户端程序示例

数据检索的核心：`SELECT` 语句