SQLite 表关联查询指南

在SQLite中关联两个表并根据主键查询相关记录是常见的数据库操作。以下是几种实现方式：

基本语法

1. 使用JOIN连接

SELECT 表1.字段, 表2.字段
FROM 表1
JOIN 表2 ON 表1.主键 = 表2.外键
WHERE 表1.主键 = 值;

2. 使用INNER JOIN（与JOIN相同）

SELECT 表1.字段, 表2.字段
FROM 表1
INNER JOIN 表2 ON 表1.主键 = 表2.外键
WHERE 表1.主键 = 值;

实际示例

假设有两个表：Customers（客户）和 Orders（订单）

-- 创建示例表
CREATE TABLE Customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);

CREATE TABLE Orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    amount REAL,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

查询特定客户的所有订单

SELECT Customers.name, Orders.order_id, Orders.order_date, Orders.amount
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
WHERE Customers.customer_id = 123;

使用表别名简化查询

SELECT c.name, o.order_id, o.order_date, o.amount
FROM Customers AS c
JOIN Orders AS o ON c.customer_id = o.customer_id
WHERE c.customer_id = 123;

其他关联方式

左连接(LEFT JOIN) - 包含左表所有记录

SELECT c.name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

多表关联

-- 假设还有Products表
SELECT c.name, o.order_id, p.product_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
WHERE c.customer_id = 123;

性能提示

1. 确保关联字段已建立索引
2. 只查询需要的字段，避免使用 SELECT *
3. 对于复杂查询，可以考虑使用视图(View)

CREATE VIEW CustomerOrders AS
SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id;

这样以后查询可以直接使用视图：

SELECT * FROM CustomerOrders WHERE customer_id = 123;