# 实用sql第二版
PRACTICAL SQL 2nd Edition
A Beginner’s Guide to Storytelling with Data
数据讲述入门指南
by Anthony DeBarros
安东尼·德巴罗斯著
# 关于作者
About the Author
Anthony DeBarros is a longtime journalist and early adopter of “data journalism,” the use of spreadsheets, databases, and code to find news in data.
安东尼·德巴罗斯是一位资深的记者，也是“数据新闻”的早期采用者，即利用电子表格、数据库和代码从数据中发现新闻。
He’s currently a data editor for the Wall Street Journal, where he covers topics including the economy, trade, demographics, and the Covid-19 pandemic.
他目前是《华尔街日报》的数据编辑，报道领域包括经济、贸易、人口统计和新冠疫情。
Previously, he worked for the Gannett company at USA Today and the Poughkeepsie Journal and held product development and content strategy roles for Questex and DocumentCloud.
此前，他在美国《今日美国》报和《波基普西报》为甘奈特公司工作，并在 Questex 和 DocumentCloud 担任产品开发和内容策略职位。
# 关于技术审阅者
About the Technical Reviewer
Stephen Frost is the chief technology officer at Crunchy Data.
斯蒂芬·弗罗斯特是 Crunchy Data 的首席技术官。
He has been working with PostgreSQL since 2003 and general database technology since before then.
自 2003 年以来，他一直与 PostgreSQL 合作，并在那之前就接触了通用的数据库技术。
Stephen began contributing to PostgreSQL development in 2004 and has been involved in the development of the role system, column-level privileges, row-level security, GSSAPI encryption, and the predefined roles system.
斯蒂芬从 2004 年开始为 PostgreSQL 开发做出贡献，并参与了角色系统、列级权限、行级安全、GSSAPI 加密和预定义角色系统的开发。
He has also served on the board of the United States PostgreSQL Association and Software in the Public Interest, regularly speaks at PostgreSQL Community conferences and events, and works as a member of various PostgreSQL community teams.
他还曾担任美国 PostgreSQL 协会和软件公共利益的董事会成员，定期在 PostgreSQL 社区会议和活动上发表演讲，并作为多个 PostgreSQL 社区团队的成员工作。
# 第二版前言
PREFACE TO THE SECOND EDITION
前言开始：
Since the publication of the first edition of Practical SQL, I’ve received kind notes about the book from readers around the world.
自从《实用 SQL》第一版出版以来，我收到了来自世界各地读者们的友好留言。
One happy reader said it helped him ace SQL questions on a job interview.
一位读者高兴地说这本书帮助他在面试中轻松应对 SQL 问题。
Another, a teacher, wrote to say that his students remarked favorably about having the book assigned for class.
另一位是教师，写信告诉我他的学生对于被分配使用这本书表示好评。
Others just wanted to say thanks because they found the book helpful and a good read, two pieces of feedback that will warm the heart of most any author.
其他人只是想表达感谢，因为他们觉得这本书既实用又有趣，这两种反馈足以让大多数作者感到温暖。
第一段：
I also sometimes heard from readers who hit a roadblock while working through an exercise or who had trouble with software or data files.
我也偶尔收到读者在练习中遇到困难或在使用软件或数据文件时遇到问题的反馈。
I paid close attention to those emails, especially when the same question seemed to crop up more than once.
我特别关注这些邮件，尤其是当同一个问题反复出现时。
Meanwhile, during my own journey of learning SQL—I use it every day at work—I’d often discover a technique and wish that I’d included it in the book.
同时，在我自己的 SQL 学习旅程中——我每天在工作中都使用它——我常常发现一种技巧，并希望自己能在书中加入它。
第二段：
With all that in mind, I approached the team at No Starch Press with the idea of updating and expanding Practical SQL into a second edition.
考虑到所有这些因素，我向 No Starch Press 团队提出了将《实用 SQL》更新并扩展为第二版的想法。
I’m thankful they said yes.
我很感激他们同意了。
This new version of the book is more complete,offers stronger guidance for readers related to software and code, and clarifies information that wasn’t as clear or presented as accurately as it could have been.
这本书的新版本更加完整，为读者提供了更清晰的软件和代码指导，并澄清了之前不够清晰或不准确的信息。
The book has been thoroughly enjoyable to revisit, and I’ve learned much along the way.
重写这本书的过程非常愉快，我也从中学到了很多。
第三段：
This second edition includes numerous updates, expansions, and clarifications in every chapter.
第二版在每个章节中都包含了许多更新、扩展和澄清。
Throughout, I’ve been careful to note when code syntax adheres to the SQL standard—meaning you can generally use it across database systems—or when the syntax is specific to the database used in the book, PostgreSQL.
我始终注意标注何时代码符合 SQL 标准——这意味着你通常可以在不同的数据库系统中使用它——或者何时代码是针对本书使用的数据库 PostgreSQL 特定的。
The following are among the most substantial changes:
以下是最重要的变更：
第一段：
Two chapters are new.
Chapter 1, “Setting Up Your Coding Environment,”details how to install PostgreSQL, pgAdmin, and additional PostgreSQL components on multiple operating systems(在多个操作系统).
第一章“设置编程环境”详细介绍了如何在多个操作系统上安装 PostgreSQL、pgAdmin 以及额外的 PostgreSQL 组件，
It also shows how to obtain the code listings and data from GitHub.
还展示了如何从 GitHub 获取代码列表和数据。
In the first edition, this information was located in the introduction and occasionally missed by readers.
在第一版中，这些信息位于引言部分，读者偶尔会错过。
Chapter 16,“Working with JSON Data,” covers PostgreSQL’s support for the JavaScript Object Notation data format, using datasets about movies and earthquakes.
第 16 章“处理 JSON 数据”涵盖了 PostgreSQL 对 JavaScript 对象表示法数据格式的支持，使用了关于电影和地震的数据集。
第二段：
In Chapter 4 on data types, I’ve added a section on IDENTITY, the ANSI SQL standard implementation for auto-incrementing integer columns.
在关于数据类型的第 4 章中，我增加了一节关于 IDENTITY 的内容，它是 ANSI(American National Standards Institute 美国国家标准学会) SQL 标准中用于自动递增整数列的实现。
Throughout the book, IDENTITY replaces the PostgreSQL-specific serial auto-incrementing integer type so that code examples more closely reflect the SQL standard.
在全书中，IDENTITY 取代了 PostgreSQL 特定的 serial 自动递增整数类型，以便代码示例更贴近 SQL 标准。
第三段：
Chapter 5 on importing and exporting data now includes a section about using the WHERE keyword with the COPY command to filter which rows are imported from a source file to a table.
关于导入和导出数据的第 5 章现在增加了一节内容，介绍了如何使用 COPY 命令与 WHERE 关键字过滤从源文件导入到表中的行。
I’ve removed the user-created median() function from Chapter 6 on basic math in favor of focusing exclusively on the SQL standard percentile_cont() function for calculating medians.
我在第6章基本数学中移除了用户创建的 median()函数，转而专注于 SQL 标准
percentile_cont() 函数用于计算中位数。
第四段：
In Chapter 7 on table joins, I’ve added a section covering the set operators UNION, UNION ALL, INTERSECT, and EXCEPT.
在第7章关于表连接中，我增加了一个涵盖集合运算符的章节UNION、UNION ALL、INTERSECT 和 EXCEPT。
Additionally, I’ve added a section covering the USING clause in joins to reduce redundant output and simplify query syntax.
此外，我还增加了一个关于连接中 USING 子句的部分，以减少冗余输出并简化查询语法
第五段：
Chapter 10 on inspecting and modifying data includes a new section on using the RETURNING keyword in an UPDATE statement to display the data that the statement modified.
第10章关于检查和修改数据，增加了一个新小节，介绍如何在 UPDATE 语句中使用 RETURNING 关键字来显示该语句修改的数据。
I’ve also added a section that describes how to use the TRUNCATE command to remove all rows from a table and restart an IDENTITY sequence.
我还增加了一个小节，描述如何使用 TRUNCATE 命令删除表中的所有行并重新启动IDENTITY(美/aɪˈdentəti/) 序列。
IDENTITY 身份
第六段：
In Chapter 11 on statistical functions, a new section demonstrates how to create a rolling average to smooth uneven data to get a better sense of trends over time.
在统计函数的第 11 章中，新增了一节演示如何创建滚动平均值来平滑不均匀数据，以更好地感知随时间变化的趋势。
I’ve also added information on functions for calculating standard deviation and variance.
我还添加了关于计算标准差和方差的函数信息。
第七段：
Chapter 13 on advanced query techniques now shows how to use the LATERAL keyword with subqueries.
第 13 章关于高级查询技巧现在展示了如何使用LATERAL（LATERAL横向）关键字与子查询。
One benefit is that, by combining LATERAL with JOIN, you get functionality similar to a for loop in a programming language.
一个好处是，通过将LATERAL 与 JOIN 结合，你将获得类似于编程语言中 for 循环的功能。
第八段：
In Chapter 15 on analyzing spatial data, I demonstrate how to use the Geometry Viewer in pgAdmin to see geographies placed on a map.
在分析空间数据的第 15 章中，我展示了如何使用 pgAdmin 中的几何查看器在地图上查看地理位置。
This feature was added to pgAdmin after publication of the first edition.
这个功能是在第一版出版后添加到 pgAdmin 中的。
第九段：
In Chapter 17 on views, functions, and triggers, I’ve added information about materialized views and showed how their behavior differs from standard views.
在第 17 章关于视图、函数和触发器中，我增加了关于物化视图的信息，并展示了它们与标准视图的行为差异。
I also cover procedures, which PostgreSQL now supports in addition to functions.
我还涵盖了过程，PostgreSQL 现在除了函数外还支持过程。
第十段：
Finally, where practical, datasets have been updated to the most recent available at the time of writing.
最后，在实用的情况下，数据集已更新为编写时最新的可用版本。
This primarily applies to US Census population statistics but also includes the text of presidential speeches and library usage statistics.
这主要适用于美国人口普查统计数据，但也包括总统演讲文本和图书馆使用统计数据。
第十一段：
Thank you for reading Practical SQL! If you have any questions or feedback, please get in touch by emailing [email protected].
感谢您阅读《实用 SQL》！如果您有任何问题或反馈，请通过电子邮件 [email protected] 与我联系。
# 致谢
ACKNOWLEDGMENTS
acknowledgments
ack-now-le-dg-ment-s
美： [ækˈnɑlɪdʒmənts]
第一段：
This second edition of Practical SQL is the work of many hands
《实用 SQL》的第二版是许多人的共同努力成果。
My thanks, first, go to the team at No Starch Press.
首先，我要感谢 No Starch Press 的团队。
Thanks to Bill Pollock for capturing the vision and sharpening the initial concept for the book—and for agreeing to let me have another go at it.
感谢 Bill Pollock 捕捉了这本书的愿景，并完善了最初的构思——也感谢他同意让我再次尝试。
Special thanks and appreciation to senior editor Liz Chadwick, who improved each chapter with her insightful suggestions and deft editing, and to copyeditor Kim Wimpsett and the production team of Paula Williamson and Jennifer Kepler.
特别感谢高级编辑 Liz Chadwick，她通过富有见地的建议和精妙的编辑改进了每一章，以及校对员 Kim Wimpsett 和制作团队 Paula Williamson 与 Jennifer Kepler。
第二段：
Stephen Frost, chief technology officer at Crunchy Data and longtime contributor to the PostgreSQL community, served as the technical reviewer for this edition.
Crunchy Data 的首席技术官 Stephen Frost，也是 PostgreSQL 社区的长期贡献者，担任了本版的审稿人。
I deeply appreciate the time Stephen took to explain the inner workings of PostgreSQL and SQL concepts.
我非常感激 Stephen 花费时间解释 PostgreSQL 的内部工作原理和 SQL 概念。
This book is better, more thorough, and more accurate thanks to his detailed eye.
多亏了他细致的审查，这本书才变得更加完善、全面和准确。
I’d also like to acknowledge Josh Berkus, whose many contributions as technical reviewer for the first edition persist in this new version.
我还要感谢 Josh Berkus，他在第一版担任审稿人时的许多贡献仍然体现在这个新版本中。
第三段：
Thank you to Investigative Reporters and Editors (IRE) and its members and staff past and present for training journalists to find stories in data.
感谢调查性报道编辑人员（IRE）及其过去的成员和现在的成员及工作人员，他们培训记者从数据中寻找故事。
IRE is where I got my start with SQL and data journalism.
IRE 是我开始学习 SQL 和数据新闻的地方。
第四段：
Many of my colleagues have not only imparted memorable lessons on data analysis, they’ve also made my workdays brighter.
我的许多同事不仅传授了难忘的数据分析课程，还让我的工作日更加美好。
Special thanks to Paul Overberg for sharing his vast knowledge of demographics and the US Census, to Lou Schilling for many technical lessons, to Christopher Schnaars for his SQL expertise, to Peter Matseykanets for his encouragement, and to Chad Day, John West, and Maureen Linke and the WSJ DC visuals team for continual inspiration.
特别感谢 Paul Overberg 分享他关于人口统计和美国人口普查的丰富知识，感谢 Lou Schilling 传授许多技术课程，感谢 Christopher Schnaars 的 SQL 专业知识，感谢 Peter Matseykanets 的鼓励，以及感谢 Chad Day、John West、Maureen Linke 和 WSJ DC 视觉团队持续的灵感启发。
第五段：
My deepest appreciation goes to my dear wife, Elizabeth, and our sons.
我最深的感谢给予我亲爱的妻子 Elizabeth 和我们的儿子们。
You are the brightest lights in my day. As we are fond of saying, “To the journey . . .”
你们是我生活中最明亮的光芒。正如我们常说的，“旅程……”
# 引言
INTRODUCTION
introduction
in-tro-duc-tion
美 [ˌɪntrəˈdʌkʃn]
Shortly after joining the staff of USA Today, I received a dataset that I would analyze almost every week for the next decade.
加入美国今日报（USA Today）员工队伍后不久，我获得了一个数据集，在接下来的十年里，我几乎每周都会分析这个数据集。
It was the weekly Best-Selling Books list, which ranked the nation’s top-selling titles based on confidential sales data. Not only did the list produce an endless stream of story ideas to pitch, it also captured the zeitgeist of America in a singular way.
它是每周畅销书排行榜，根据机密的销量数据排名全国最畅销的书籍。这个排行榜不仅源源不断地产生故事点子供我推荐，还捕捉到了时代精神。
America in a singular way.
以一种独特的方式，美国。
第一段：
Did you know that cookbooks sell a bit more during the week of Mother’s Day or that Oprah Winfrey turned many obscure writers into number-one best-selling authors just by having them on her show?
你知道母亲节前后烹饪书的销量会稍微上升吗？或者奥普拉·温弗瑞仅仅通过在节目上邀请他们，就使许多默默无闻的作家成为顶级畅销书作家？
Every week, the book list editor and I pored over the sales figures and book genres, ranking the data in search of a new headline.
每周，我和图书列表编辑都会仔细研究销售数据和图书类型，对数据进行排名，寻找新的头条新闻。
Rarely did we come up empty: we chronicled everything from the rocket-rise of the blockbuster Harry Potter series to the fact that Oh, the Places You’ll Go! by Dr. Seusshad become a perennial gift for new graduates.
我们很少空手而归：我们记录了从大片的热销哈利·波特系列到这样一个事实，即苏斯博士的《你将要去的地方！》已成为新毕业生的常青礼物。
第二段：
My technical companion in that time was the database programming language SQL (for Structured Query Language).
那个时期，我的技术伙伴是数据库编程语言 SQL（结构化查询语言）。
Early on, I convinced USA Today’s IT department to grant me access to the SQL-based database system that powered our book list application.
早期，我说服了 USA Today 的 IT 部门，让我能够访问驱动我们图书列表应用的基于 SQL 的数据库系统。
Using SQL, I was able to discover the stories hidden in the database, which contained sales data related to titles, authors, genres, and the codes that defined the publishing world.
通过 SQL，我能够发现数据库中隐藏的故事，其中包含了与书名、作者、类型以及定义出版界的相关代码的销售数据。
第三段：
SQL has been useful to me ever since, whether my role was in product development, in content strategy, or, lately, as a data editor for the Wall Street Journal.
从那时起，无论我的角色是在产品开发、内容策略，还是最近作为华尔街日报的数据编辑，SQL 都对我很有用。
In each case, SQL has helped me find interesting stories in data—and that’s exactly what you’ll learn to do using this book.
在每种情况下，SQL 都帮助我从数据中发现有趣的故事——这正是你将使用这本书学会做的事情。
# 什么是 SQL？
SQL is a widely used programming language for managing data and database systems.
SQL 是一种广泛使用的编程语言，用于管理数据和数据库系统。
Whether you’re a marketing analyst, a journalist, or a researcher mapping neurons in the brain of a fruit fly, you’ll benefit from using SQL to collect, modify, explore, and summarize data.
无论你是市场分析师、记者，还是研究果蝇大脑神经元的研究人员，使用 SQL 收集、修改、探索和总结数据都将对你有所帮助。
第一段：
Because SQL is a mature language that’s been around for decades, it’s ingrained in many modern systems.
由于 SQL 是一种已经成熟了几十年的语言，它已经深深植根于许多现代系统中。
A pair of IBM researchers first outlined the syntax for SQL (then called SEQUEL) in a 1974 paper, building on the theoretical work of the British computer scientist Edgar F. Codd.
一对 IBM 研究人员在 1974 年的论文中首次概述了 SQL（当时称为 SEQUEL）的语法，该工作基于英国计算机科学家埃德加·F·科德的理论研究。
In 1979, a precursor to the database company Oracle (then called Relational Software) became the first to use the language in a commercial product.
1979 年，数据库公司 Oracle（当时名为 Relational Software）的前身成为第一个在商业产品中使用该语言的公司。
Today, SQL still ranks as one of the most-used computer languages in the world, and that’s unlikely to change soon.
如今，SQL 仍然位列全球最常用的计算机语言之一，这种情况在不久的将来不太可能改变。
第二段：
Each database system, such as PostgreSQL, MySQL or Microsoft SQL Server, implements its own variant of SQL, so you’ll notice subtle—or sometimes significant—differences in syntax if you jump from one system to another.
每个数据库系统，如 PostgreSQL、MySQL 或 Microsoft SQL Server，都实现了它自己的 SQL 变体，因此如果你从一个系统切换到另一个系统，你会注意到细微的——有时甚至是显著的——语法差异。
There are several reasons behind this.
这背后有几个原因。
The American National Standards Institute (ANSI) adopted a standard for SQL in 1986, followed by the International Organization for Standardization (ISO) in 1987.
美国国家标准协会（ANSI）在 1986 年采纳了 SQL 的标准，随后国际标准化组织（ISO）在 1987 年也采纳了。
But the standard doesn’t cover all aspects of SQL that are required for a database implementation—for example, it has no entry for creating indexes.
但这个标准并没有涵盖数据库实现所需的 SQL 的所有方面——例如，它没有创建索引的条目。
That leaves each database system maker to choose how to implement features the standard doesn’t cover—and no database maker currently claims to conform to the entire standard.
这就留给每个数据库系统制造商选择如何实现标准未涵盖的功能——目前没有数据库制造商声称完全符合整个标准。
第三段：
Meanwhile, business considerations can lead commercial database vendors to create nonstandard SQL features for both competitive advantage and as a way to keep users in their ecosystem.
与此同时，商业考量可以促使商业数据库供应商创建非标准的 SQL 功能，以获得竞争优势并作为将用户留在其生态系统的一种方式。
For example, Microsoft’s SQL Server uses the proprietary Transact-SQL (T-SQL) that includes a number of features not in the SQL standard, such as its syntax for declaring local variables.
例如，Microsoft 的 SQL Server 使用专有的 Transact-SQL（T-SQL），其中包括 SQL 标准中未包含的许多功能，例如它的声明语法。局部变量。
Migrating code written using T-SQL to another database system may not be trivial, therefore. system may not be trivial, therefore.
将使用 T-SQL 编写的代码迁移到其他数据库系统可能并不简单，因此。
第四段：
In this book, the examples and code use the PostgreSQL database system.
在这个书中，示例和代码使用 PostgreSQL 数据库系统。
PostgreSQL, or simply Postgres, is a robust application that can handle large amounts of data.
PostgreSQL，简称 Postgres，是一个能够处理大量数据的强大应用程序。
Here are some reasons PostgreSQL is a great choice to use with this book:
以下是选择 PostgreSQL 与本书配合使用的一些原因：
第五段：
It’s free.
这是免费的。
It’s available for Windows, macOS, and Linux operating systems.
它适用于 Windows、macOS 和 Linux 操作系统。
Its SQL implementation aims to closely follow the SQL standard.
它的 SQL 实现旨在紧密遵循 SQL 标准。
It’s widely used, so finding help online is easy.
它被广泛使用，因此在线查找帮助很容易。
Its geospatial extension, PostGIS, lets you analyze geometric data and perform mapping functions and is often used with mapping software such as QGIS.
它的地理空间扩展 PostGIS，可以让你分析几何数据并执行制图功能，通常与 QGIS 等制图软件一起使用。
It’s available in cloud computing environments such as Amazon Web Services and Google Cloud.
它可以在 Amazon Web Services 和 Google Cloud 等云计算环境中使用。
It’s a common choice as a data store for web applications, including those powered by the popular web framework Django.
它作为 Web 应用程序的数据存储是一个常见选择，包括由流行的 Web 框架 Django 提供支持的那些应用程序。
第六段：
The good news is that the fundamental concepts and much of the core SQL syntactical conventions of PostgreSQL will work across databases.
好消息是，PostgreSQL 的基本概念和大部分核心 SQL 语法约定在各个数据库中都能通用。
So, if you’re using MySQL at work, you can employ much of what you learn here—or easily find parallel code concepts.
所以，如果你在工作中使用 MySQL，你可以运用在这里学到的大部分内容——或者轻松找到类似的代码概念。
When syntax is PostgreSQL- specific, I make sure to point that out.
当语法是 PostgreSQL 特有的时，我会特别指出这一点。
If you need to learn the SQL syntax of a system with features that deviate from the standard, such as Microsoft SQL Server’s T-SQL, you may want to further explore a resource focusing on that system. 如果你需要学习具有与标准不同的特性的系统的 SQL 语法，比如 Microsoft SQL Server 的 T-SQL，你可能需要进一步探索专注于该系统的资源。
# 这本书适合谁？
Who Is This Book For?
第一段：
Practical SQL is for people who encounter data in their everyday lives and want to learn how to analyze, manage, and transform it.
实用 SQL 适合那些在日常中遇到数据并希望学习如何分析、管理和转换数据的人。
With that in mind, we cover real-world data and scenarios, such as US Census demographics, crime reports, and data about taxi rides in New York City.
基于这个目的，我们涵盖了现实世界的数据和场景，例如美国人口普查数据、犯罪报告以及纽约市出租车行程数据
We aim to understand not only how SQL works but how we can use it to find valuable insights.
我们的目标是不仅了解 SQL 的工作原理，而且了解如何使用它来发现有价值的见解。
第二段：
This book was written with people new to programming in mind, so the early chapters cover key basics about databases, data, and SQL syntax.
本书是为初学者编写的，因此前几章涵盖了关于数据库、数据和 SQL 语法的基本要点。
Readers with some SQL experience should benefit from later chapters that cover more advanced topics, such as Geographical Information Systems (GIS).
具有一定 SQL 经验的读者将从涵盖更高级主题的后几章中受益，例如地理信息系统（GIS）。
I assume that you know your way around your computer, including how to install programs, navigate your hard drive, and download files from the internet, but I don’t assume you have any experience with programming or data analysis.
我假设你熟悉你的电脑，包括如何安装程序、浏览硬盘以及从互联网下载文件，但我没有假设你有编程或数据分析的经验。
# 你将学到什么
What You’ll Learn
Practical SQL starts with a chapter on setting up your system and getting the code and data examples and then moves through the basics of databases, queries, tables, and data that are common to SQL across many database systems.
《实用 SQL》从设置系统、获取代码和数据示例的章节开始，然后逐步介绍数据库、查询、表和跨多个数据库系统常见的 SQL 数据基础。
Chapters 14 to 19 cover topics more specific to PostgreSQL, such as full-text search, functions, and GIS.
第 14 章到第 19 章涵盖了更特定于 PostgreSQL 的主题，如全文搜索、函数和 GIS。
Although many chapters in this book can stand alone, you should work through the book sequentially to build on the fundamentals.
尽管本书的许多章节可以独立存在，但建议按顺序学习以建立基础知识。
Datasets presented in early chapters often reappear later, so following the book in order will help you stay on track.
早期章节中介绍的数据集在后续章节中经常再次出现，因此按顺序阅读本书将有助于你保持学习进度。
The following summary provides more detail about each chapter:
以下摘要提供了关于每一章的更多细节：
Chapter 1: Setting Up Your Coding Environment walks through setting up PostgreSQL, the pgAdmin user interface, and a text editor, plus how to download example code and data.
第一章：设置你的编程环境介绍了设置 PostgreSQL、pgAdmin 用户界面以及文本编辑器，以及如何下载示例代码和数据。
Chapter 2: Creating Your First Database and Table provides step-by- step instructions for the process of loading a simple dataset about teachers into a new database.
第二章：创建您的第一个数据库和表提供了逐步指导，用于加载关于教师的一个简单数据集导入到一个新的数据库中。
Chapter 3: Beginning Data Exploration with SELECT explores basic SQL query syntax, including how to sort and filter data.
第三章：使用 SELECT 开始数据探索 探讨了基本的 SQL 查询语法，包括如何排序和过滤数据。
Chapter 4: Understanding Data Types explains the definitions for setting columns in a table to hold specific types of data, from text to dates to various forms of numbers.
第四章：理解数据类型解释了数据类型的定义 在表中设置列以存储特定类型的数据，从文本到日期再到各种形式的数字。
Chapter 5: Importing and Exporting Data explains how to use SQL commands to load data from external files and then export it. You’ll load a table of US Census population data that you’ll use throughout the book.
第五章：导入和导出数据解释了如何使用 SQL 用于从外部文件加载数据并导出的命令。你将加载一个美国人口普查数据表，并在整本书中使用它。
Chapter 6: Basic Math and Stats with SQL covers arithmetic operations and introduces aggregate functions for finding sums, averages, and medians.
第 6 章：SQL 中的基础数学和统计涵盖算术操作并介绍了用于查找总和、平均值和中位数的聚合函数。
Chapter 7: Joining Tables in a Relational Database explains how to query multiple, related tables by joining them on key columns. You’ll learn how and when to use different types of joins.
第 7 章：关系数据库中的表连接解释了如何通过在键列上连接它们来查询多个相关的表。您将学习如何以及何时使用不同类型的连接。
Chapter 8: Table Design that Works for You covers how to set up tables to improve the organization and integrity of your data as well as how to speed up queries using indexes.
第 8 章：为您量身定制的表格设计，涵盖了如何设置如何使用索引来提高数据组织和完整性，以及如何通过索引加快查询速度。
Chapter 9: Extracting Information by Grouping and Summarizing explains how to use aggregate functions to find trends in US library usage based on annual surveys.
第 9 章：通过分组和汇总提取信息解释了如何使用聚合函数根据年度调查来查找美国图书馆使用趋势。
Chapter 10: Inspecting and Modifying Data explores how to find and fix incomplete or inaccurate data using a collection of records about meat, egg, and poultry producers as an example.
第 10 章：检查和修改数据探讨了如何查找和修正不完整或不准确的数据，并以关于肉类、蛋类和家禽生产者的记录集为例进行说明。
Chapter 11: Statistical Functions in SQL introduces correlation, regression, ranking, and other functions to help you derive more meaning from datasets.
第 11 章：SQL 中的统计函数介绍了相关性，回归、排名和其他功能，帮助你从数据集中获取更多意义。
Chapter 12: Working with Dates and Times explains how to create, manipulate, and query dates and times in your database, including working with time zones and with data about New York City taxi trips and Amtrak train schedules.
第 12 章：处理日期和时间解释了如何创建操作、查询数据库中的日期和时间，包括处理时区和纽约市出租车行程以及美国铁路客运公司的列车时刻表。
Chapter 13: Advanced Query Techniques explains how to use more complex SQL operations such as subqueries and cross tabulations, plus the CASE statement, to reclassify values in a dataset on temperature readings.
第 13 章：高级查询技巧解释了如何使用更多复杂的 SQL 操作，如子查询和交叉制表，以及 CASE 语句，用于重新分类数据集中的温度读数值。语句，用于重新分类数据集中的温度读数值。
Chapter 14: Mining Text to Find Meaningful Data covers how to use PostgreSQL’s full-text search engine and regular expressions to extract data from unstructured text, using police reports and a collection of speeches by US presidents as examples.
第 14 章：从文本中挖掘有意义的数掘，介绍了如何使用PostgreSQL 的全文搜索引擎和正则表达式用于从非结构化文本中提取数据，以警方报告和美国总统演讲集为例。
Chapter 15: Analyzing Spatial Data with PostGIS introduces data types and queries related to spatial objects, which will let you analyze geographical features such as counties, roads, and rivers.
第 15 章：使用 PostGIS 分析空间数据介绍了与空间对象相关的数据类型和查询，这将使你能够分析县、道路和河流等地理特征。
Chapter 16: Working with JSON Data introduces the JavaScript Object Notation (JSON) data format and uses data about movies and earthquakes to explore PostgreSQL JSON support.
第 16 章：处理 JSON 数据介绍了 JavaScript 对象表示法（JSON）数据格式，并使用有关电影和地震的数据来探索 PostgreSQL 的 JSON 支持。
Chapter 17: Saving Time with Views, Functions, and Triggers explains how to automate database tasks so you can avoid repeating routine work.
第 17 章：使用视图、函数和触发器节省时间解释了如何自动化数据库任务，以便你可以避免重复常规工作。
Chapter 18: Using PostgreSQL from the Command Line covers how to use text commands at your computer’s command prompt to connect to your database and run queries.
第 18 章：从命令行使用 PostgreSQL 介绍了如何使用计算机的命令提示符中的文本命令连接到您的数据库并运行查询。
Chapter 19: Maintaining Your Database provides tips and procedures for tracking the size of your database, customizing settings, and backing up data.
第 19 章：维护您的数据库提供了一些技巧和程序用于跟踪数据库大小、自定义设置和备份数据。
Chapter 20: Telling Your Data’s Story provides guidelines for generating ideas for analysis, vetting data, drawing sound conclusions, and presenting your findings clearly.
第 20 章：讲述你的数据故事提供了关于生成分析想法、审核数据、得出合理结论以及清晰展示你发现的基本准则。
Appendix: Additional PostgreSQL Resources lists software and documentation to help you grow your skills.
附录：更多 PostgreSQL 资源列出了软件和文档，帮助你提升技能。
Each chapter ends with a “Try It Yourself” section that contains exercises to help you reinforce the topics you learned.
每章都以“自己动手试试”部分结束，其中包含练习，帮助你巩固所学内容。
Ready? Let’s begin with Chapter 1, “Setting Up Your Coding Environment.”
准备好了吗？让我们从第一章“设置你的编程环境”开始。