PostgreSQL 初学者指南

能够查询关系型数据库系统是数据科学家的必备技能。SQL（结构化查询语言）可以让您高效地完成这一任务。SQL 不仅能让您向数据提出有意义的问题，还能以多种方式对数据进行操作。没有数据库，几乎不可能实现任何真实世界的应用。因此，掌握数据库知识并能熟练使用，是数据科学家工具箱中的关键部分。

小知识：SQL 也读作 SE-QU-EL。这与它的历史有关——SQL 的最初名称是 Simple English Query Language。

一般来说，关系型数据库看起来如下所示——

关系也称为表。数据库有多种表示方式，这只是其中之一，也是最常见的一种。

本教程将介绍使用 SQL 最常见的四种操作：创建、读取、更新和删除。合在一起通常称为 CRUD。只要是涉及用户交互的应用，基本都会包含这四种操作。

您将使用 PostgreSQL 作为关系数据库管理系统。PostgreSQL 既轻量又免费。在本教程中，您将：

• 快速上手 PostgreSQL
• 连接到 PostgreSQL 数据库
• 在该数据库中创建、读取、更新和删除表
• 在 Jupyter Notebook 中运行 SQL
• 在 Python 中运行 SQL

让我们开始吧。

快速上手 PostgreSQL

PostgreSQL 是一款轻量的开源 RDBMS，已在业界广泛认可。您可以在其官方网站了解更多信息。

要在 PostgreSQL 中开始编写和执行查询，您需要先在本机安装它。安装非常简单。以下两个简短视频演示了如何在 32 位 Windows 7 机器上下载并安装 PostgreSQL：

• 下载 PostgreSQL
• 安装 PostgreSQL

注意：安装 PostgreSQL 时，请记录下您设置的密码和端口号。

在您的机器上成功安装 PostgreSQL 后，打开 pgAdmin。pgAdmin 是随 PostgreSQL 一起提供的实用工具，借助美观的图形界面可完成常见的数据库相关任务。pgAdmin 的界面如下所示：

打开 pgAdmin 后，您会在界面中看到一个名为“PostgreSQL 9.4 (localhost:5432)”的服务器——

注意：您的版本可能与上图不同，因此端口号（5432）也可能不同。

输入您在安装过程中设置的密码以连接该服务器。参考链接：https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808。

成功连接到本地数据库服务器后，您将看到类似如下的界面——

您的第一项任务是创建一个数据库。右键单击 Databases 选项卡，然后在下拉菜单中选择 New Database。我们来创建一个名为 DataCamp_Courses 的数据库。数据库创建完成后，您就可以继续本教程的后续部分了。

PostgreSQL 中的 CRUD 操作

根据给定的规格创建表——

要在数据库上进行操作，首先需要一张表。让我们创建一张简单的表（也称为关系），名为 datacamp_courses，其规格（模式）如下——

该规格向我们提供了关于表列的多项信息——

• 表的主键应为 course_id（注意只有它是加粗的），其数据类型为整数。主键是一种约束，要求该列的值非空且唯一。它使您能够在表中唯一标识某一条或一组记录。
• 规格中的其余信息现在应该比较容易理解了。

要创建表，右键单击刚创建的数据库 DataCamp_Courses，并在选项中选择 CREATE Script。您应看到类似如下的界面——

现在执行以下查询——

CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);
技术支持

要执行查询，只需选中它并单击菜单栏中的执行按钮——

输出应为——

PostgreSQL 中创建表的通用查询结构如下——

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)
技术支持

在创建表时我们没有指定任何 table_constraints，这一点目前可以省略。除关键字 SERIAL 外，其他内容都相当直观。PostgreSQL 中的 Serial 可用于创建自增列，默认生成整数类型的值。Serial 让我们无需记忆表中最后插入/更新的主键，作为主键使用自增是个良好实践。您可以在这里了解更多关于 serial 的信息。

向新建表中插入一些记录

此步骤中，您将向表中插入一些记录。每条记录应包含：

• 课程名称
• 课程讲师姓名
• 课程主题

course_id 列的取值将由 PostgreSQL 自动处理。PostgreSQL 中插入查询的一般结构如下——

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);
技术支持

我们来插入一些记录——

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
技术支持

请注意，您并未显式指定主键值。稍后您将看到其效果。

执行上述两条查询后，若插入成功，您应看到如下结果——

Query returned successfully: one row affected, 11 ms execution time.

从表中读取/查看数据

这可能是您在数据科学之路上最常做的事之一。现在让我们看看表 datacamp_courses 的情况。

这通常称为查询（select），其通用结构如下——

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;
技术支持

让我们从表 datacamp_courses 中选择所有列

SELECT * FROM datacamp_courses;
技术支持

然后您会得到——

请注意主键的取值。若您只想查看课程名称，可以这样做——

SELECT course_name from datacamp_courses;
技术支持

然后您会得到——

您可以指定任意数量且存在于表中的列名来作为结果返回的列。如果您运行 select course_name, number_particpants from datacamp_courses; 会报错，因为表中不存在 number_particpants 这一列。接下来您将看到如何更新表中的某条记录。

更新表中的记录

SQL 中更新查询的一般结构如下：

UPDATE table
SET column1 = value1,
    column2 = value2 ,...
WHERE
 condition;
技术支持

您将把 course_instructor = "Chester Ismay" 的记录的 course_name 更新为 "Joining Data in SQL"。随后验证该记录是否已更新。对应的查询为——

UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';
技术支持

我们通过运行一次查询来看看更新是否生效——

您可以看到更新正如预期生效。接下来看看如何从表中删除一条记录。

删除表中的记录

SQL 中删除查询的一般结构如下：

DELETE FROM table
WHERE condition;
技术支持

您将删除 course_name = "Deep Learning in Python" 的记录，然后验证该记录是否已被删除。按照该结构，下面的查询即可完成此操作——

DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';
技术支持

请记住，SQL 中的关键字不区分大小写，但数据是区分大小写的。这就是为什么您会在查询中看到大小写混用。

我们来看看目标记录是否已从表中删除——

是的，目标记录确已被删除。

本教程中提到的查询通用结构参考自 PostgreSQL Tutorial。

现在，您已经了解了 SQL 中基本的 CRUD 查询。许多读者常用 Jupyter Notebook，可能会想是否可以直接在 Notebook 中执行这些查询。下一节将介绍如何实现这一点。

SQL + Jupyter Notebooks

要在 Jupyter Notebook 中运行 SQL 查询，第一步是安装 ipython-sql 包。

如果尚未安装，请使用以下命令安装：

pip install ipython-sql
技术支持

完成后，在 Jupyter Notebook 中加载 sql 扩展，执行——

%load_ext sql
技术支持

下一步是连接到 PostgreSQL 数据库。您将连接到自己创建的数据库——DataCamp_Courses。

要连接到系统中已创建的数据库，需要让 Python 识别其方言。简单来说，就是告诉 Python 这是一个 PostgreSQL 数据库。为此，您需要安装 psycopg2，可通过以下命令安装：

pip install psycopg2
技术支持

安装好 psycopg 后，使用以下方式连接数据库——

%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses
技术支持

'Connected: postgres@DataCamp_Courses'技术支持

注意 %sql 的用法。这是一条魔术命令，可让您在 Jupyter Notebook 中执行 SQL 语句。跟在 %sql 后的是数据库连接 URL，其中依次指定：

• 方言（postgres）
• 用户名（postgres）
• 密码（postgres）
• 服务器地址（localhost）
• 端口号（5432）
• 数据库名（DaaCamp_Courses）

现在，您可以在 Jupyter Notebook 中完成之前在 pgAdmin 界面中的所有操作。我们先用完全相同的模式创建表 _datacampcourses。

但在此之前需要先删除该表，因为 SQL 不允许保存两个同名表。可以这样删除表——

%sql DROP table datacamp_courses;
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 技术支持

表 _datacampcourses 已从 PostgreSQL 中删除，因此您可以用这个名称创建一个新表。

%%sql
CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 技术支持

注意这里 %sql 与 %%sql 的用法。执行单行查询可使用 %sql，若要一次执行多条查询，则需使用 %%sql。

我们来插入一些记录——

%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.





[] 技术支持

查看该表以确认插入是否如预期完成——

%%sql
select * from datacamp_courses;
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 技术支持

继续流程。下一步，您将更新表中的一条记录——

%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 技术支持

在 SQL 中处理字符串时请格外注意。不同于传统编程语言，字符串值需要使用单引号包裹。

现在验证更新是否生效——

%%sql
select * from datacamp_courses;
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 技术支持

现在我们来删除一条记录并进行验证——

%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 技术支持

%%sql
select * from datacamp_courses;
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 技术支持

至此，您已经清楚地了解了如何在 PostgreSQL 中执行 CRUD 操作，以及如何通过 Jupyter Notebook 来完成这些操作。如果您熟悉 Python，并希望在 Python 代码中访问数据库，也完全可以做到。下一节将专门介绍这一点。

SQLAlchemy 入门与 SQL 魔术命令结合使用

本节需要用到 SQLAlchemy 包。它通常包含在 Anaconda 发行版中，您也可以通过 pip 安装。安装完成后，按如下方式导入：

import sqlalchemy
技术支持

要使用 SQLAlchemy 与数据库交互，需要为存放数据库的相应 RDBMS 创建一个引擎。在这里是 PostgreSQL。SQLAlchemy 只需一次调用 create_engine() 即可创建该 RDBMS 的引擎，该方法接收一个数据库连接 URL，之前我们已见过。

from sqlalchemy import create_engine
技术支持

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # 让您查看数据库中存在的表名
技术支持

['datacamp_courses']技术支持

您可以看到表 _datacampcourses 的名称，这进一步说明引擎创建成功。我们来执行一个简单的查询，查看 _datacampcourses 的记录，并将其存入一个 pandas 的 DataFrame 对象。

您将使用 read_sql() 方法（由 pandas 提供），它接收一条 SQL 查询字符串和一个引擎。

import pandas as pd

df = pd.read_sql('select * from datacamp_courses', engine)
df.head()
技术支持

您也可以在 Python 代码中配合使用 %sql 魔术命令。

df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()
技术支持

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 技术支持

您已经走了很长一段路！

恭喜您，给自己点个赞！在本教程中，您不仅入门了 PostgreSQL，还了解了如何以多种方式执行 SQL 查询。下一步，您可以观看 这场网络研讨会，由 David Robinson（DataCamp 首席数据科学家）讲解他在日常工作中如何使用 SQL。还可以查看我们的教程：如何在 Windows 和 Mac OS X 上安装 PostgreSQL。如果您想提升 SQL 技能，以下资源非常适合入门：

• 面向数据科学的 SQL 入门
• Kaggle Learn 的 SQL
• 在 SQL 中进行表连接