SQL 查询的工作原理

执行顺序

SQL 语句的执行顺序，即查询语句的处理顺序，可以帮助我们更好地理解 SQL 查询的工作原理。尽管我们在编写 SQL 查询时是从 SELECT 开始的，但数据库系统在实际执行时有其特定的顺序。

以下是典型的 SQL 查询语句的执行顺序：

FROM 子句 首先处理 FROM 子句，包括连接（JOIN）操作。它会构建一个基础的数据集。
1
2
FROM authors
JOIN books ON authors.author_id = books.author_id
WHERE 子句 接下来是 WHERE 子句，用于过滤 FROM 子句生成的数据集。只有满足 WHERE 子句条件的记录才会被保留下来。
1
WHERE books.author_id = 'some_value'
GROUP BY 子句 GROUP BY 子句会将 WHERE 子句过滤后的数据进行分组。分组是基于一个或多个列的值。
1
GROUP BY authors.name
HAVING 子句 HAVING 子句用于过滤 GROUP BY 子句之后的分组结果。它通常用于聚合函数条件。
1
HAVING COUNT(books.book_id) > 1
SELECT 子句 然后是 SELECT 子句，它用于选择并计算需要返回的列和表达式，包括聚合函数。
1
SELECT authors.name, COUNT(books.book_id) AS book_count
DISTINCT 子句 DISTINCT 子句用于移除 SELECT 子句生成结果中的重复行。
1
SELECT DISTINCT authors.name, COUNT(books.book_id) AS book_count
ORDER BY 子句 ORDER BY 子句用于对 SELECT 子句生成的结果集进行排序。
1
ORDER BY book_count DESC
LIMIT/OFFSET 子句 最后是 LIMIT 和 OFFSET 子句，用于限制返回的记录数以及指定从哪一条记录开始返回结果。
1
LIMIT 10 OFFSET 5

案例

假设我们有一个图书管理系统，包含两张表：books 和 authors。books 表记录了每本书的信息，而 authors 表记录了作者的信息，并且每本书都关联一个作者。

创建数据库和表格

CREATE DATABASE library_db;
USE library_db;

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100),
    birth_year INT
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    genre VARCHAR(50),
    published_year INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

插入数据

INSERT INTO authors (author_id, name, birth_year) VALUES
(1, 'George Orwell', 1903),
(2, 'J.K. Rowling', 1965),
(3, 'J.R.R. Tolkien', 1892);

INSERT INTO books (book_id, title, author_id, genre, published_year) VALUES
(1, '1984', 1, 'Dystopian', 1949),
(2, 'Animal Farm', 1, 'Political Satire', 1945),
(3, 'Harry Potter and the Philosopher\'s Stone', 2, 'Fantasy', 1997),
(4, 'Harry Potter and the Chamber of Secrets', 2, 'Fantasy', 1998),
(5, 'The Hobbit', 3, 'Fantasy', 1937),
(6, 'The Lord of the Rings', 3, 'Fantasy', 1954);

查询数据

假设我们想要查询出每个作者的书的数量，并且只显示那些书的数量大于 1 的作者，按照书的数量从高到低排序，并且限制显示前 10 条记录。对应的 SQL 查询如下：

SELECT authors.name, COUNT(books.book_id) AS book_count
FROM authors
JOIN books ON authors.author_id = books.author_id
GROUP BY authors.name
HAVING COUNT(books.book_id) > 1
ORDER BY book_count DESC
LIMIT 10 OFFSET 0;

查询结果

name	book_count
J.K. Rowling	2
J.R.R. Tolkien	2
George Orwell	2

解释查询结果

FROM: 从 authors 和 books 表开始，进行 JOIN 操作，匹配 authors.author_id 和 books.author_id。
WHERE: 由于没有 WHERE 子句，此步骤被跳过。
GROUP BY: 按 authors.name 分组。
HAVING: 只保留那些分组内的记录数大于 1 的组。
SELECT: 选择 authors.name 和计算每组的书数量。
ORDER BY: 按照 book_count 降序排列。
LIMIT: 只返回前 10 条记录。
OFFSET: 从第 0 条记录开始返回结果。

通过这个有意义的例子，可以更清晰地理解 SQL 查询的工作原理以及各个子句的作用。

案例

这个案例使用的还是上面的数据，仅仅是查询语句不同。

我们将查询那些出版年份在1950年之后的书籍，并计算这些书籍的作者以及他们的书籍数量。

查询数据

SELECT authors.name, COUNT(books.book_id) AS book_count
FROM authors
JOIN books ON authors.author_id = books.author_id
WHERE books.published_year > 1950
GROUP BY authors.name
HAVING COUNT(books.book_id) > 1
ORDER BY book_count DESC
LIMIT 10 OFFSET 0;

解释查询结果

FROM 和 JOIN 操作：首先，FROM authors JOIN books ON authors.author_id = books.author_id 会将 authors 表和 books 表按 author_id 进行连接，生成一个包含作者及其书籍信息的中间结果集。

生成中间结果集：在 JOIN 操作之后，中间结果集的形式如下：

author_id	name	birth_year	book_id	title	genre	published_year
1	George Orwell	1903	1	1984	Dystopian	1949
1	George Orwell	1903	2	Animal Farm	Political Satire	1945
2	J.K. Rowling	1965	3	Harry Potter and the Philosopher's Stone	Fantasy	1997
2	J.K. Rowling	1965	4	Harry Potter and the Chamber of Secrets	Fantasy	1998
3	J.R.R. Tolkien	1892	5	The Hobbit	Fantasy	1937
3	J.R.R. Tolkien	1892	6	The Lord of the Rings	Fantasy	1954

WHERE 子句：接下来，WHERE books.published_year > 1950 过滤掉出版年份小于或等于1950年的书籍，剩下的结果集如下：

author_id	name	birth_year	book_id	title	genre	published_year
2	J.K. Rowling	1965	3	Harry Potter and the Philosopher's Stone	Fantasy	1997
2	J.K. Rowling	1965	4	Harry Potter and the Chamber of Secrets	Fantasy	1998
3	J.R.R. Tolkien	1892	6	The Lord of the Rings	Fantasy	1954

GROUP BY 操作：然后，GROUP BY authors.name 会根据 authors.name 进行分组。每个作者的所有符合 WHERE 条件的书籍会被聚合到一组中。分组后的结果集如下：

name book_count

J.K. Rowling 2

J.R.R. Tolkien 1
HAVING 子句： HAVING COUNT(books.book_id) > 1 过滤掉书的数量小于或等于 1 的分组。结果集如下：

name book_count

J.K. Rowling 2
SELECT 子句： SELECT authors.name, COUNT(books.book_id) AS book_count 选择每个作者的名字和书籍数量。
ORDER BY 操作： ORDER BY book_count DESC 按照书的数量从高到低排序。由于只有一条记录，排序不会改变结果集的顺序。
LIMIT 和 OFFSET： LIMIT 10 OFFSET 0 返回前 10 条记录。从第 0 条记录开始显示，因为总记录数小于 10，所以所有结果都被返回。

处理顺序总结

FROM 和 JOIN: 构建初始数据集。
WHERE: 过滤数据，只有符合条件的记录会保留下来。
GROUP BY: 按指定列进行分组。
HAVING: 过滤分组结果，只有符合条件的分组会保留下来。
SELECT: 选择要返回的列和计算聚合函数。
ORDER BY: 对结果进行排序。
LIMIT 和 OFFSET: 限制返回的记录数及起始位置。

执行顺序

案例

创建数据库和表格

插入数据

查询数据

查询结果

解释查询结果

案例

查询数据

解释查询结果

处理顺序总结

Vincent Huang