51、关系数据库与结构化查询语言（SQL）：书籍数据库的深入解析

最新推荐文章于 2025-10-10 13:42:11 发布

反内卷战士508

最新推荐文章于 2025-10-10 13:42:11 发布

阅读量50

点赞数

CC 4.0 BY-SA版权

分类专栏： Python数据科学实战精要文章标签：关系数据库 SQL SQLite

本文链接：https://blog.youkuaiyun.com/nut55/article/details/151310424

Python数据科学实战精要专栏收录该内容

59 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

关系数据库与结构化查询语言（SQL）：书籍数据库的深入解析

1. 数据库基础

数据库在大数据领域至关重要。在处理数据时，若要处理文件中的大部分或全部数据，顺序文本文件处理、CSV 文件和 JSON 数据处理很有用；但在事务处理中，我们需要快速定位并可能更新单个数据项，这时数据库就发挥了重要作用。

数据库是数据的集成集合，数据库管理系统（DBMS）提供了与数据库格式一致的存储和组织数据的机制，使我们能方便地访问和存储数据，而无需关注数据库的内部表示。

关系数据库管理系统（RDBMS）将数据存储在表中，并定义表之间的关系。结构化查询语言（SQL）几乎普遍用于关系数据库系统，用于操作数据和执行查询。

常见的开源 RDBMS 包括 SQLite、PostgreSQL、MariaDB 和 MySQL，它们都可免费下载和使用，且都支持 Python，我们将使用与 Python 捆绑的 SQLite。一些流行的专有 RDBMS 包括 Microsoft SQL Server、Oracle、Sybase 和 IBM Db2。

2. 关系数据库的表、行和列

关系数据库是基于表的逻辑数据表示，允许我们在不考虑其物理结构的情况下访问数据。以一个可能用于人事系统的员工表为例：
| Number（主键） | Name | Department | Salary | Location |
| — | — | — | — | — |
| 23603 | Jones | 413 | 1100 | New Jersey |
| 24568 | Kerwin | 413 | 2000 | New Jersey |
| 34589 | Larson | 642 | 1800 | Los Angeles |
| 35761 | Myers | 611 | 1400 | Orlando |
| 47132 | Neumann | 413 | 9000 | New Jersey |
| 78321 | Stephens | 611 | 8500 | Orlando |

该表的主要目的是存储员工的属性。表由行组成，每行描述一个实体，这里每行代表一个员工；行由列组成，列包含单个属性值。该表有六行，“Number” 列是主键，其值对每行都是唯一的，确保每行可通过主键识别。主键的例子包括社会安全号码、员工 ID 号和库存系统中的零件号等。在这个例子中，行按主键升序排列，但也可以按降序或无特定顺序排列。

每个列代表不同的数据属性，表中的行（通过主键）是唯一的，但特定列的值可能在不同行中重复，例如 “Department” 列中有三个不同的行包含数字 413。

不同的数据库用户通常对不同的数据和数据之间的关系感兴趣，大多数用户只需要行和列的子集。我们使用 SQL 来定义查询，以选择表中的数据子集。

3. SQLite 与书籍数据库

后续的代码示例使用与 Python 捆绑的开源 SQLite 数据库管理系统，大多数流行的数据库系统都支持 Python，每个系统通常提供一个遵循 Python 数据库应用程序编程接口（DB - API）的模块。

我们将创建一个包含多本图书信息的书籍数据库。以下是创建和使用该数据库的步骤：
1. 创建书籍数据库 ：在 Anaconda 命令提示符、终端或 shell 中，切换到 ch16 示例文件夹的 sql 子文件夹，执行以下命令创建名为 books.db 的 SQLite 数据库，并执行 books.sql SQL 脚本，该脚本定义了如何创建数据库的表并填充数据：

sqlite3 books.db < books.sql

命令完成后，数据库即可使用。然后开始一个新的 IPython 会话。
2. 连接到数据库 ：在 Python 中，首先调用 sqlite3 的 connect 函数连接到数据库并获取一个 Connection 对象：

import sqlite3
connection = sqlite3.connect('books.db')

4. 书籍数据库的表

该数据库有三个表： authors 、 author_ISBN 和 titles 。
- authors 表 ：存储所有作者信息，有三列：
- id ：作者的唯一 ID 号，该整数列定义为自动递增，是表的主键。
- first ：作者的名字（字符串）。
- last ：作者的姓氏（字符串）。

使用 SQL 查询和 pandas 查看 authors 表的内容：

import pandas as pd
pd.options.display.max_columns = 10
pd.read_sql('SELECT * FROM authors', connection, index_col=['id'])

输出结果如下：

        first    last
id                   
1        Paul  Deitel
2      Harvey  Deitel
3       Abbey  Deitel
4         Dan   Quirk
5   Alexander    Wald

SQL 的 SELECT * FROM authors 查询从 authors 表中获取所有列， * 是通配符。

titles 表 ：存储所有书籍信息，有四列：
isbn ：书籍的 ISBN（字符串），是表的主键。
title ：书籍的标题（字符串）。
edition ：书籍的版本号（整数）。
copyright ：书籍的版权年份（字符串）。

使用 SQL 和 pandas 查看 titles 表的内容：

pd.read_sql('SELECT * FROM titles', connection)

输出结果如下：

         isbn                             title  edition copyright
0  0135404673     Intro to Python for CS and DS        1      2020
1  0132151006     Internet & WWW How to Program        5      2012
2  0134743350               Java How to Program       11      2018
3  0133976890                  C How to Program        8      2016
4  0133406954  Visual Basic 2012 How to Program        6      2014
5  0134601548          Visual C# How to Program        6      2017
6  0136151574         Visual C++ How to Program        2      2008
7  0134448235                C++ How to Program       10      2017
8  0134444302            Android How to Program        3      2017
9  0134289366         Android 6 for Programmers        3      2016

author_ISBN 表 ：用于关联 authors 表中的作者和 titles 表中的书籍，有两列：
id ：作者的 ID（整数），是外键，与 authors 表的 id 列匹配。
isbn ：书籍的 ISBN（字符串），是外键，与 titles 表的 isbn 主键列匹配。

id 和 isbn 列共同构成复合主键，表中的每行唯一地将一位作者与一本书的 ISBN 匹配。使用 SQL 和 pandas 查看该表的前五行：

df = pd.read_sql('SELECT * FROM author_ISBN', connection)
df.head()

输出结果如下：

   id        isbn
0   1  0134289366
1   2  0134289366
2   5  0134289366
3   1  0135404673
4   2  0135404673

每个外键值必须作为另一个表中某行的主键值出现，这就是引用完整性规则。外键还允许从多个表中选择相关数据并进行组合，即连接数据。主键和相应外键之间存在一对多关系，一个作者可以写多本书，一本书也可以由多个作者撰写。

5. 实体 - 关系（ER）图

书籍数据库的实体 - 关系（ER）图展示了数据库的表及其之间的关系。图中每个框的第一个隔室包含表名，其余隔室包含表的列，斜体的名称是主键。表的主键唯一标识表中的每一行，每行必须有主键值，且该值在表中必须唯一，这就是实体完整性规则。

对于 author_ISBN 表，主键是两列的组合，即复合主键。连接表的线表示表之间的关系，例如 authors 表和 author_ISBN 表之间的线， authors 端有一个 1， author_ISBN 端有一个无穷大符号（∞），表示一对多关系，即一个作者可以写任意数量的书，作者的 id 可以出现在 author_ISBN 表的多行中。同样， titles 表和 author_ISBN 表之间也是一对多关系，一本书可以由多个作者撰写。

6. SQL 关键字

7. SELECT 查询

之前使用 SELECT 语句和 * 通配符获取表的所有列，通常我们只需要列的子集，特别是在大数据中，可能有数十、数百、数千甚至更多列。要仅检索特定列，可指定用逗号分隔的列名列表。例如，从 authors 表中仅检索 first 和 last 列：

pd.read_sql('SELECT first, last FROM authors', connection)

输出结果如下：

       first    last
0       Paul  Deitel
1     Harvey  Deitel
2      Abbey  Deitel
3        Dan   Quirk
4  Alexander    Wald

8. WHERE 子句

在数据库中，我们经常需要选择满足特定选择标准的行，特别是在大数据中，数据库可能包含数百万或数十亿行。只有满足选择标准（正式称为谓词）的行才会被选中，SQL 的 WHERE 子句指定查询的选择标准。

例如，选择版权年份大于 2016 的所有书籍的标题、版本和版权信息：

pd.read_sql("""SELECT title, edition, copyright 
               FROM titles 
               WHERE copyright > '2016'""", connection)

输出结果如下：

                           title  edition copyright
0  Intro to Python for CS and DS        1      2020
1            Java How to Program       11      2018
2       Visual C# How to Program        6      2017
3             C++ How to Program       10      2017
4         Android How to Program        3      2017

WHERE 子句可以包含 < 、 > 、 <= 、 >= 、 = 、 <> （不等于）和 LIKE 等运算符。 LIKE 运算符用于模式匹配，包含百分号（ % ）通配符的模式可搜索在该位置有零个或多个字符的字符串。例如，查找姓氏以字母 D 开头的所有作者：

pd.read_sql("""SELECT id, first, last 
               FROM authors 
               WHERE last LIKE 'D%'""", connection, index_col=['id'])

输出结果如下：

     first    last
id                
1     Paul  Deitel
2   Harvey  Deitel
3    Abbey  Deitel

模式中包含下划线（ _ ）表示该位置有一个通配符字符。例如，选择姓氏以任意字符开头，后跟字母 b，再跟任意数量字符的所有作者的行：

pd.read_sql("""SELECT id, first, last 
               FROM authors 
               WHERE first LIKE '_b%'""", connection, index_col=['id'])

输出结果如下：

    first    last
id               
3   Abbey  Deitel

9. ORDER BY 子句

ORDER BY 子句可将查询结果按升序（最低到最高）或降序（最高到最低）排序，分别使用 ASC 和 DESC 指定，默认排序顺序为升序，所以 ASC 可选。

例如，按标题升序对书籍标题进行排序：

pd.read_sql('SELECT title FROM titles ORDER BY title ASC', connection)

输出结果如下：

                              title
0         Android 6 for Programmers
1            Android How to Program
2                  C How to Program
3                C++ How to Program
4     Internet & WWW How to Program
5     Intro to Python for CS and DS
6               Java How to Program
7  Visual Basic 2012 How to Program
8          Visual C# How to Program
9         Visual C++ How to Program

按多列排序时，在 ORDER BY 关键字后指定用逗号分隔的列名列表。例如，先按姓氏排序，再对姓氏相同的作者按名字排序：

pd.read_sql("""SELECT id, first, last 
               FROM authors 
               ORDER BY last, first""", connection, index_col=['id'])

输出结果如下：

        first    last
id                   
3       Abbey  Deitel
2      Harvey  Deitel
1        Paul  Deitel
4         Dan   Quirk
5   Alexander    Wald

也可以为不同列设置不同的排序顺序，例如按姓氏降序排序，对姓氏相同的作者按名字升序排序：

pd.read_sql("""SELECT id, first, last 
               FROM authors 
               ORDER BY last DESC, first ASC""", connection, index_col=['id'])

输出结果如下：

        first    last
id                   
5   Alexander    Wald
4         Dan   Quirk
3       Abbey  Deitel
2      Harvey  Deitel
1        Paul  Deitel

10. 合并多个表的数据：INNER JOIN

书籍数据库的 author_ISBN 表将作者与他们对应的书籍关联起来。如果不将这些信息分开存储在不同表中， titles 表的每个条目中都需要包含作者信息，这会导致为撰写多本书的作者存储重复的作者信息。

使用 INNER JOIN 可以合并多个表的数据，即连接表。例如，生成一个作者列表，以及每位作者所写书籍的 ISBN 列表：

pd.read_sql("""SELECT first, last, isbn
               FROM authors
               INNER JOIN author_ISBN
                   ON authors.id = author_ISBN.id
               ORDER BY last, first""", connection).head()

输出结果如下：

    first    last        isbn
0   Abbey  Deitel  0132151006
1   Abbey  Deitel  0133406954
2  Harvey  Deitel  0134289366
3  Harvey  Deitel  0135404673
4  Harvey  Deitel  0132151006

INNER JOIN 的 ON 子句使用一个表中的主键列和另一个表中的外键列来确定要合并的行。在 ON 子句中，使用 authors.id （表名.列名）这样的限定名语法，如果两表中的列名相同，则必须使用该语法，该语法可用于任何 SQL 语句中，以区分不同表中具有相同名称的列。

11. INSERT INTO 语句

到目前为止，我们主要是查询现有数据，有时需要执行修改数据库的 SQL 语句，这时可以使用 sqlite3 的 Cursor 对象，通过调用 Connection 对象的 cursor 方法获取该对象。后续可以使用该对象执行插入、更新和删除等操作。例如，插入新行到指定表中的操作可以使用 INSERT INTO 语句，但具体示例代码未在当前内容中详细给出，后续可根据具体需求编写相应的 SQL 语句并使用 Cursor 对象执行。

通过以上内容，我们详细了解了关系数据库和 SQL 的基本概念、操作，以及如何使用 Python 和 SQLite 管理和查询书籍数据库。这些知识对于处理和分析各种数据，特别是大数据，具有重要的意义。

关系数据库与结构化查询语言（SQL）：书籍数据库的深入解析

12. 进一步的 SQL 操作示例

在前面的内容中，我们已经了解了多种 SQL 操作，接下来通过更多示例加深对这些操作的理解。

12.1 结合 WHERE 和 ORDER BY 子句

我们可以将 WHERE 和 ORDER BY 子句结合在一个查询中。例如，获取 titles 表中标题以 ‘How to Program’ 结尾的每本书的 isbn 、 title 、 edition 和 copyright ，并按标题升序排序：

pd.read_sql("""SELECT isbn, title, edition, copyright
               FROM titles
               WHERE title LIKE '%How to Program'
               ORDER BY title""", connection)

输出结果如下：

         isbn                             title  edition copyright
0  0134444302            Android How to Program        3      2017
1  0133976890                  C How to Program        8      2016
2  0134448235                C++ How to Program       10      2017
3  0132151006     Internet & WWW How to Program        5      2012
4  0134743350               Java How to Program       11      2018
5  0133406954  Visual Basic 2012 How to Program        6      2014
6  0134601548          Visual C# How to Program        6      2017
7  0136151574         Visual C++ How to Program        2      2008

12.2 复杂的 INNER JOIN 操作

除了简单的 INNER JOIN ，我们还可以进行更复杂的操作。例如，我们想获取作者的姓名、所写书籍的标题以及书籍的版权年份：

pd.read_sql("""SELECT authors.first, authors.last, titles.title, titles.copyright
               FROM authors
               INNER JOIN author_ISBN
                   ON authors.id = author_ISBN.id
               INNER JOIN titles
                   ON author_ISBN.isbn = titles.isbn
               ORDER BY authors.last, authors.first""", connection).head()

这个查询通过两次 INNER JOIN 操作，将 authors 表、 author_ISBN 表和 titles 表连接起来，获取我们需要的信息。

13. 数据库操作的流程图

下面是一个简单的 mermaid 格式流程图，展示了使用 Python 和 SQLite 进行数据库操作的基本流程：

graph TD;
    A[开始] --> B[连接到数据库];
    B --> C[执行 SQL 查询];
    C --> D{是否需要修改数据库};
    D -- 是 --> E[获取 Cursor 对象];
    E --> F[执行修改操作];
    D -- 否 --> G[获取查询结果];
    G --> H[处理查询结果];
    F --> H;
    H --> I[关闭数据库连接];
    I --> J[结束];

14. 数据库操作总结

操作类型	关键字	示例代码	说明
查询	SELECT	`pd.read_sql('SELECT first, last FROM authors', connection)`	从表中检索特定列的数据
条件查询	WHERE	`pd.read_sql("""SELECT title, edition, copyright FROM titles WHERE copyright > '2016'""", connection)`	根据条件筛选行
排序	ORDER BY	`pd.read_sql('SELECT title FROM titles ORDER BY title ASC', connection)`	对查询结果进行排序
连接表	INNER JOIN	`pd.read_sql("""SELECT first, last, isbn FROM authors INNER JOIN author_ISBN ON authors.id = author_ISBN.id ORDER BY last, first""", connection)`	合并多个表的数据
插入	INSERT INTO	暂未详细示例，后续可使用 `Cursor` 对象执行	向表中插入新行

15. 注意事项

SQL 语法 ：在编写 SQL 语句时，要注意语法的正确性，特别是关键字的大小写（虽然 SQL 关键字通常不区分大小写，但为了代码的可读性，建议统一大小写风格）。
数据类型 ：要确保插入或查询的数据类型与表中列的数据类型一致，否则可能会导致错误。
事务处理 ：在进行修改数据库的操作时，建议使用事务处理，以保证数据的一致性和完整性。例如，使用 Connection 对象的 commit() 和 rollback() 方法。