2025-09-28

本文您详细介绍 SQLite 中的内连接（INNER JOIN）。本文将深入探讨内连接的概念、语法和用法，并通过实际的例子来展示其在数据库查询中的重要性和应用。我们将从准备测试数据开始，然后逐步深入内连接的各个方面。

准备测试数据

首先，让我们创建一些测试表和数据来演示内连接的使用。我们将创建一个简单的图书管理系统，包含图书、作者和出版社三个表。

SQL
-- 创建作者表
CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT NOT NULL,
    birth_year INTEGER
);

-- 创建出版社表
CREATE TABLE publishers (
    publisher_id INTEGER PRIMARY KEY,
    publisher_name TEXT NOT NULL,
    country TEXT
);

-- 创建图书表
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    publisher_id INTEGER,
    publication_year INTEGER,
    price REAL,
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)
);

-- 插入作者数据
INSERT INTO authors (author_id, author_name, birth_year) VALUES
(1, 'George Orwell', 1903),
(2, 'Jane Austen', 1775),
(3, 'Ernest Hemingway', 1899),
(4, 'Virginia Woolf', 1882),
(5, 'F. Scott Fitzgerald', 1896);

-- 插入出版社数据
INSERT INTO publishers (publisher_id, publisher_name, country) VALUES
(1, 'Penguin Books', 'UK'),
(2, 'HarperCollins', 'USA'),
(3, 'Random House', 'USA'),
(4, 'Simon & Schuster', 'USA'),
(5, 'Macmillan Publishers', 'UK');

-- 插入图书数据
INSERT INTO books (book_id, title, author_id, publisher_id, publication_year, price) VALUES
(1, '1984', 1, 1, 1949, 9.99),
(2, 'Animal Farm', 1, 2, 1945, 8.99),
(3, 'Pride and Prejudice', 2, 3, 1813, 7.99),
(4, 'Sense and Sensibility', 2, 1, 1811, 8.50),
(5, 'The Old Man and the Sea', 3, 4, 1952, 10.99),
(6, 'A Farewell to Arms', 3, 2, 1929, 11.99),
(7, 'Mrs Dalloway', 4, 5, 1925, 9.50),
(8, 'To the Lighthouse', 4, 3, 1927, 10.50),
(9, 'The Great Gatsby', 5, 4, 1925, 12.99),
(10, 'Tender Is the Night', 5, 1, 1934, 11.50);

这些测试数据为我们提供了一个基础，可以用来演示内连接的各种用法。

2025-09-28

本文将详细介绍 SQLite 中的子查询，并提供一些实际的测试数据和示例。本文将深入探讨子查询的概念、类型、用法，并通过具体的例子来展示其在实际应用中的强大功能。

准备测试数据

首先，让我们创建一些测试表和数据来演示子查询的使用。我们将创建一个简单的员工管理系统，包含员工、部门和项目三个表。

SQL
-- 创建部门表
CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT NOT NULL,
    location TEXT
);

-- 创建员工表
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    dept_id INTEGER,
    salary REAL,
    hire_date TEXT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 创建项目表
CREATE TABLE projects (
    project_id INTEGER PRIMARY KEY,
    project_name TEXT NOT NULL,
    start_date TEXT,
    end_date TEXT,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 插入部门数据
INSERT INTO departments (dept_id, dept_name, location) VALUES
(1, 'IT', 'New York'),
(2, 'HR', 'Los Angeles'),
(3, 'Finance', 'Chicago'),
(4, 'Marketing', 'San Francisco');

-- 插入员工数据
INSERT INTO employees (emp_id, first_name, last_name, dept_id, salary, hire_date) VALUES
(1, 'John', 'Doe', 1, 75000, '2020-01-15'),
(2, 'Jane', 'Smith', 2, 65000, '2019-05-20'),
(3, 'Mike', 'Johnson', 1, 80000, '2018-03-10'),
(4, 'Emily', 'Brown', 3, 72000, '2021-02-01'),
(5, 'David', 'Lee', 4, 68000, '2020-11-15'),
(6, 'Sarah', 'Wilson', 2, 62000, '2019-09-01'),
(7, 'Tom', 'Davis', 1, 78000, '2017-07-22'),
(8, 'Lisa', 'Anderson', 3, 70000, '2022-01-10');

-- 插入项目数据
INSERT INTO projects (project_id, project_name, start_date, end_date, dept_id) VALUES
(1, 'CRM Upgrade', '2022-01-01', '2022-06-30', 1),
(2, 'Employee Training Program', '2022-03-15', '2022-12-31', 2),
(3, 'Financial Reporting System', '2022-02-01', '2022-11-30', 3),
(4, 'Product Launch Campaign', '2022-04-01', '2022-09-30', 4),
(5, 'Network Infrastructure Upgrade', '2022-05-01', '2022-10-31', 1);