MySQL - 使用 JOIN

MySQL - JOIN 使用现代指南

JOIN（连接）子句是 SQL 中一个基本概念，用于根据两个或多个表之间的相关列来组合行。这使您能够查询和检索逻辑上分散在规范化数据库结构中的数据，就好像它们在一个表中一样。

显式 JOIN 语法的重​​要性

历史上，SQL 允许在 FROM 子句中使用隐式的、基于逗号的连接语法，并将连接条件放在 WHERE 子句中。这现在被认为是过时且不良的做法。

旧语法（避免）： SELECT ... FROM table1, table2 WHERE table1.id = table2.fk_id;

现代显式语法（最佳实践）： SELECT ... FROM table1 INNER JOIN table2 ON table1.id = table2.fk_id;

• 清晰性： 它将连接表的逻辑（ON 子句）与过滤行的逻辑（WHERE 子句）清晰地分离。
• 安全性： 它防止了意外的 CROSS JOIN（交叉连接）操作。在旧语法中忘记 WHERE 条件会导致所有行的笛卡尔积，这可能会使您的应用程序崩溃。没有 ON 子句的显式 JOIN 是一个语法错误。
• 功能： 它是唯一支持 OUTER JOIN（外连接）（LEFT、RIGHT、FULL）的语法。

JOIN 的类型

SQL 定义了几种类型的 JOIN 以处理不同的场景：

• INNER JOIN（内连接）： 仅返回在两个表中都有匹配值的记录。这是最常见的连接类型。
• LEFT JOIN（左连接，或 LEFT OUTER JOIN）： 返回左表中的所有记录，以及右表中匹配的记录。如果右表中没有匹配项，则右侧结果为 NULL。
• RIGHT JOIN（右连接，或 RIGHT OUTER JOIN）： 返回右表中的所有记录，以及左表中匹配的记录。如果左表中没有匹配项，则左侧结果为 NULL。
• FULL OUTER JOIN（全外连接）： 当左表或右表中有匹配项时，返回所有记录。（如另一章所述，MySQL 需要模拟此操作）。

示例：连接客户和订单

让我们设置两个表 CUSTOMERS 和 ORDERS 来演示这些连接。

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    CITY VARCHAR(50)
);

CREATE TABLE ORDERS(
    OID INT PRIMARY KEY,
    ORDER_DATE DATETIME NOT NULL,
    CUSTOMER_ID INT,
    AMOUNT DECIMAL(10, 2),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID)
);

INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 'Ahmedabad'), (2, 'Khilan', 'Delhi'), (3, 'Kaushik', 'Kota'), (4, 'Chaitali', 'Mumbai');

INSERT INTO ORDERS VALUES (101, '2023-10-08 10:00:00', 3, 3000.00), (102, '2023-11-20 11:30:00', 2, 1560.00), (103, '2023-05-20 15:00:00', 4, 2060.00);

注意，Ramesh（ID 1）尚未下任何订单。

INNER JOIN 示例： 获取已下订单的客户。

SELECT c.ID, c.NAME, o.ORDER_DATE, o.AMOUNT
FROM CUSTOMERS c
INNER JOIN ORDERS o ON c.ID = o.CUSTOMER_ID;

输出

ID	NAME	ORDER_DATE	AMOUNT
3	Kaushik	2023-10-08 10:00:00	3000.00
2	Khilan	2023-11-20 11:30:00	1560.00
4	Chaitali	2023-05-20 15:00:00	2060.00

LEFT JOIN 示例： 获取所有客户及其订单（如果有）。

SELECT c.ID, c.NAME, o.ORDER_DATE, o.AMOUNT
FROM CUSTOMERS c
LEFT JOIN ORDERS o ON c.ID = o.CUSTOMER_ID;

请注意，Ramesh 现在已包含在结果中，其订单详细信息为 NULL。

使用客户端程序进行连接（现代最佳实践）

从应用程序执行连接时，使用预处理语句对于防止 SQL 注入漏洞至关重要。以下是针对流行语言的现代示例。

PHP
NodeJS
Java
Python



此示例使用现代面向对象的 `mysqli` 扩展和预处理语句。

```php
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';

// 建立连接
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

// 连接查询，获取下过订单且居住在特定城市的客户
$city = 'Kota';
$sql = 'SELECT c.ID, c.NAME, o.AMOUNT FROM CUSTOMERS c JOIN ORDERS o ON c.ID = o.CUSTOMER_ID WHERE c.CITY = ?';

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $city);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    echo "Orders from customers in {$city}:\n";
    while ($row = $result->fetch_assoc()) {
        printf("ID: %s, Name: %s, Amount: %s\n", $row['ID'], $row['NAME'], $row['AMOUNT']);
    }
} else {
    echo "No results found.";
}

$stmt->close();
$mysqli->close();
?>

此示例使用 mysql2/promise 库和 async/await，以实现清晰、现代的异步代码。

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

async function getOrdersByCity(city) {
    let connection;
    try {
        // 在实际应用中使用连接池
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS'
        });

        const sql = `
            SELECT c.ID, c.NAME, o.AMOUNT
            FROM CUSTOMERS c
            JOIN ORDERS o ON c.ID = o.CUSTOMER_ID
            WHERE c.CITY = ?`;

        const [rows] = await connection.execute(sql, [city]);

        if (rows.length > 0) {
            console.log(`Orders from customers in ${city}:`);
            console.log(rows);
        } else {
            console.log('No results found.');
        }

    } catch (error) {
        console.error('Database query failed:', error);
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

getOrdersByCity('Kota');

此示例使用现代 JDBC，结合 try-with-resources 实现自动资源管理，并使用 PreparedStatement 提高安全性。

import java.sql.*;

public class JoinExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String city = "Kota";
        String sql = "SELECT c.ID, c.NAME, o.AMOUNT FROM CUSTOMERS c " +
                     "JOIN ORDERS o ON c.ID = o.CUSTOMER_ID WHERE c.CITY = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, city);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Orders from customers in " + city + ":");
                while (rs.next()) {
                    System.out.printf("ID: %d, Name: %s, Amount: %.2f%n",
                            rs.getInt("ID"),
                            rs.getString("NAME"),
                            rs.getBigDecimal("AMOUNT"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

此示例使用 mysql-connector-python 和上下文管理器（with）自动处理连接和游标。

import mysql.connector
from mysql.connector import errorcode

def get_orders_by_city(city):
    config = {
        'user': 'root',
        'password': 'password',
        'host': 'localhost',
        'database': 'TUTORIALS'
    }

    sql_query = ("""
        SELECT c.ID, c.NAME, o.AMOUNT
        FROM CUSTOMERS c
        JOIN ORDERS o ON c.ID = o.CUSTOMER_ID
        WHERE c.CITY = %s
    """)

    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(sql_query, (city,))
                results = cursor.fetchall()

                if results:
                    print(f"Orders from customers in {city}:")
                    for row in results:
                        print(row)
                else:
                    print("No results found.")

    except mysql.connector.Error as err:
        print(f"Something went wrong: {err}")

if __name__ == '__main__':
    get_orders_by_city('Kota')