第 3 章 使用SELECT开始探索数据

对我来说，挖掘数据最有趣的部分不是收集数据、载入数据或者清理数据这些前期工作，而是实际地访问数据。通过观察数据，我可以知道它是清洁的还是受污染的，它是否完整，还有最重要的是它能够讲述什么样的故事。访问数据的过程就像是在面试求职者，你需要提出一些问题，以此来了解他们的专业知识是否与简历相符。

访问数据最激动人心的地方是你能够从中发现事实真相。比如说，你可能会发现有一半的受访者忘记填写问卷中的电子邮件字段，或者市长在过去五年中没有缴纳财产税。又或者你会了解到自己的数据是受污染的：名字的拼写不一致，日期是错误的，又或者数字与你预期的不一样。你的发现将成为数据故事的一部分。

在SQL中，访问数据可以通过SELECT关键字来完成，它可以从数据库中的一个或多个表里面检索行和列。从检索一个表的所有内容，到连接数十个表、同时处理多项计算并且根据精确的条件进行过滤，SELECT语句可以很简单也可以很复杂。

我们首先会从简单的SELECT语句开始，然后慢慢过渡到使用SELECT完成更强大的任务。

基本的SELECT语法

下面这个SELECT语句会从名为my_table的表中获取每一行和每一列：

SELECT * FROM my_table;

这行代码展示了SQL查询的最基本形式。SELECT关键字后面的星号是一个通配符，它就像值的替身：它不代表特定的某个值，而是代表可能出现的所有值。简单来说，它相当于“选择所有列”的缩写。如果你给定的是列的名字而不是通配符，那么语句将返回指定列的值。FROM关键字用于指定将要返回数据的表。至于表名后面的分号，则用于告知PostgreSQL查询语句已经结束。

让我们对之前在第2章创建的teachers表执行这个带星号通配符的SELECT语句。首先，再次打开pgAdmin，选择analysis数据库并打开查询工具，然后执行代码清单3-1中展示的语句。记住，除了亲自把这些语句键入到查询工具之外，你还可以从GitHub下载代码，然后通过点击打开文件并导航至放置代码的位置来执行代码。当你在书中发现被--snip--截断的代码时，也可以通过这种方法运行它们。对于本章，你应该打开Chapter_03.sql文件，接着高亮选中指定的语句，然后通过点击执行/刷新图标来运行它们。

xxxxxxxxxx
SELECT * FROM teachers;

代码清单3-1：查询teachers表中的所有行和列

执行这个查询之后，你在第2章插入到 teachers 表中的所有行和列就会出现在查询工具输出方框的结果集里面。这里展示的行可能跟你看到的行顺序不一样，这是正常的。

xxxxxxxxxx
id  first_name  last_name   school                 hire_date   salary
--  ----------  ---------   -------------------     ----------  ------
1   Janet       Smith       F.D. Roosevelt HS       2011-10-30  36200
2   Lee         Reynolds    F.D. Roosevelt HS       1993-05-22  65000
3   Samuel      Cole        Myers Middle School     2005-08-01  43500
4   Samantha    Bush        Myers Middle School     2011-10-30  36200
5   Betty       Diaz        Myers Middle School     2005-08-30  43500
6   Kathleen    Roush       F.D. Roosevelt HS       2010-10-22  38500

请注意，即使我们没有明确地插入id列，bigserial类型的它们也会被自动填充为连续的整数，这是非常方便的。这种自动递增的整数会被用作唯一标识符，也可以称之为键（key），它不仅确保了表的每个行都是独一无二的，它还能帮助我们将该表与数据库中的其他表连接起来。

除了上面提到的方法之外，我们还有两个别的方法可以查看表中的所有行。其一，使用pgAdmin，你可以右键点击对象树中的teachers表，然后选择查看/编辑数据 ▸ 所有行。其二，你可以使用这个比较少人知道的标准SQL语句：

xxxxxxxxxx
TABLE teachers;

这两种方法都能提供跟代码清单3-1一样的结果。接下来，让我们继续改善这个查询，让它变得更加具体。

查询一部分列

通常情况下，比较实用的做法是限制查询检索的列，以此来避免涉及过量信息，这对于大型数据库来说尤为重要。为此，你可以在SELECT关键字后面指名想要检索的列，并使用逗号分隔它们。下面是一个例子：

xxxxxxxxxx
SELECT some_column, another_column, amazing_column FROM table_name;

在使用这种语法的情况下，查询只会从所有行里面检索这三个列的数据。让我们把这种做法应用到teachers表。

假设你的分析只关心教师的名字和薪水。在这种情况下，你可以只选择相关的列，就像代码清单3-2所做的那样。请注意，查询中的列顺序和表中的列顺序是不一样的：你可以使用自己想要的任何顺序检索列。

xxxxxxxxxx
SELECT last_name, first_name, salary FROM teachers;

代码清单3-2：查询一部分列

现在，我们将结果集的数据限制为只有三列：

xxxxxxxxxx
last_name   first_name  salary
---------   ----------  ------
Smith       Janet       36200
Reynolds    Lee         65000
Cole        Samuel      43500
Bush        Samantha    36200
Diaz        Betty       43500
Roush       Kathleen    38500

尽管这些例子都非常基本，但它们展现了一个良好的策略，告诉你该如何开始访问数据集。一般来说，明智的做法是在进行分析之前先检查数据是否存在，还有数据的格式是否符合预期，这种任务非常适合用SELECT来完成。日期的格式是否正确地包含了日、月、年，又或者（像我曾经遗憾地观察到的那样）只记录了文本形式的月和年？是否每一行的每一列都有值？是否由于某种莫名的原因，除了以字母M开头的姓氏之外，没有其他任何姓氏？从丢失数据到工作流中的某处保存了劣质的记录，任何问题都有可能会发生。

尽管现在我们只是在处理一个包含六个行的表，但是当你面对一个拥有数千行甚至数百万行的表时，快速地了解数据的质量以及它们能够容纳的取值范围就显得至关重要了。为了做到这一点，我们需要更加深入地了解SQL，并在有需要的地方添加几个SQL关键字。

注意

pgAdmin允许你从对象浏览器里面把列名、表名以及其他对象拖放至查询工具。如果你正在写一个新的查询，但是又不想一直输入冗长的对象名称，那么这一特性可能会对你有所帮助。你需要做的就是像第1章那样，展开对象树找到你的表或者列，然后通过点击和拖拽把它们放入查询工具。

通过ORDER BY排序数据

当数据按照顺序排列而不是乱七八糟地随意排列的时候，它们会更有意义，并且也更容易揭示出相应的模式。

在SQL中，我们使用包含ORDER BY关键字的子句对查询结果进行排序，而跟在关键字后面的则是一个或多个被排序列的名字。应用这一子句只会对查询结果产生影响，它不改变原有的表。代码清单3-3展示了一个排序teachers表的例子。

xxxxxxxxxx
SELECT first_name, last_name, salary
FROM teachers
ORDER BY salary DESC;

代码清单3-3：使用ORDER BY对列进行排序

在默认情况下，ORDER BY将按值进行升序排序，但这里代码通过添加DESC关键字实行了降序排序。（可选的ASC关键字可以让排序以升序进行。）现在，通过让salary列的值从高到低进行排列，我可以轻而易举地知道哪些教师的薪水是最高的。

xxxxxxxxxx
first_name  last_name   salary
----------  ---------   ------
Lee         Reynolds    65000
Samuel      Cole        43500
Betty       Diaz        43500
Kathleen    Roush       38500
Janet       Smith       36200
Samantha    Bush        36200

ORDER BY子句还允许使用数字来代替列的名字，你只要根据每个列在SELECT子句中的位置，用数字标记想要排序的列即可。我们可以通过这一特性来重写代码清单3-3，用数字3代表SELECT子句中排行第三的salary列：

xxxxxxxxxx
SELECT first_name, last_name, salary
FROM teachers
ORDER BY 3 DESC;

对查询进行排序的能力，极大地提升了我们在观察和呈现数据时的灵活性。比如说，我们可以对不止一个列进行排序。请键入代码清单3-4中的语句。

xxxxxxxxxx
  SELECT last_name, school, hire_date
  FROM teachers
➊ ORDER BY school ASC, hire_date DESC;

代码清单 3-4 ：使用ORDER BY排序多个列

这段代码检索教师的姓氏、他们执教的学校以及他们被雇用的日期。通过对学校进行升序排序，并对雇用日期进行降序排序 ➊ ，我们创建了一个按学校分组的教师列表，而最近被雇用的教师则排在列表的前面。这样一来，我们就能够看到每间学校最新雇用的教师都有谁。以下是这个查询的结果：

xxxxxxxxxx
last_name   school                  hire_date
---------   -------------------     ----------
Smith       F.D. Roosevelt HS       2011-10-30
Roush       F.D. Roosevelt HS       2010-10-22
Reynolds    F.D. Roosevelt HS       1993-05-22
Bush        Myers Middle School     2011-10-30
Diaz        Myers Middle School     2005-08-30
Cole        Myers Middle School     2005-08-01

你可以对两个以上的列使用ORDER BY，但很快就会到达一个收益递减的点，使得排序的效果难以被察觉。想象一下，如果你在ORDER BY子句中加入更多列，比如教师取得的最高学位、他们执教的年级以及他们的出生日期，那么你将很难在短时间内理解结果中的各种排序趋势，更不用说跟别人交流这些排序结果了。当结果只专注于回答一个特定的问题时，消化数据是最容易的；因此，一个更好的策略是限制查询的范围，让它只涉及最重要的列，然后为回答每个问题分别执行多个查询。

使用DISTINCT查找唯一值

在一个表里面，不同行的同一列出现相同值的情况并不少见。比如在teachers表里面，就因为每间学校都雇用了多名教师，所以相同学校的名字就在school列里面出现了好几次。

为了了解列的值区间，我们可以在查询中包含DISTINCT关键字，以此来消除重复值并且只展示独一无二的值。正如代码清单3-5所示，DISTINCT应该紧紧跟在SELECT之后。

xxxxxxxxxx
SELECT DISTINCT school
FROM teachers
ORDER BY school;

代码清单3-5：在school列中查询不同的值

这段代码的执行结果如下：

xxxxxxxxxx
school
-------------------
F.D. Roosevelt HS
Myers Middle School

尽管这个表包含了六个行，但输出只展示了school列中两个独一无二的学校名称。这对于评估数据质量是非常有意义的一步。比如说，如果一个学校的名字具有多种拼法，那么这些拼写的变化就很容易会被发现并纠正，特别是当你对输出进行排序的时候。

当你在处理日期或者数字的时候，DISTINCT将有助于发现不一致或者破损的格式。比如说，你可能继承了一个数据集，它的日期以text数据类型的形式记录在列里面。这种做法允许畸形的日期存在（因此你应该避免这种做法）：

xxxxxxxxxx
date
---------
5/30/2023
6//2023
6/1/2023
6/2/2023

DISTINCT关键字可以同时对多个列产生作用。如果我们增加一个列，那么查询将会返回每一对唯一的值。请运行代码清单3-6中的代码。

xxxxxxxxxx
SELECT DISTINCT school, salary
FROM teachers
ORDER BY school, salary;

代码清单3-6：查询school列和salary列中每一对不同的值

这段代码会返回每间学校每一份独一无二（或者说各不相同）的工资。因为Myers Middle School有两位教师的工资同为$43,500，它们被归纳到了同一个行里面，所以查询只返回了5行而不是表中的全部6行：

xxxxxxxxxx
school                 salary
-------------------     ------
F.D. Roosevelt HS       36200
F.D. Roosevelt HS       38500
F.D. Roosevelt HS       65000
Myers Middle School     36200
Myers Middle School     43500

这项技术使得我们能够提出这样一个问题：“对于表中的每个x，与之对应的所有y值是什么？”对于每间工厂，它们能生产什么化学制品？对于每个选区，参与竞选的候选人都有谁？对于每间音乐厅，这个月都有哪些艺术家在演出？

除此之外，SQL还提供了更为复杂的技术，它的聚合函数可以让我们进行计数、求和还有查找最大值和最小值。本书将在第6章和第9章介绍更多相关细节。

使用WHERE过滤行

在一些情况下，你可能想要限制查询返回的行，使得它们的一个或多个列符合特定的条件。以teachers表为例，你可能会想要找到在特定年份之前雇用的所有教师，又或者收入超过$75000的所有小学教师，而完成这些任务则需要用到WHERE子句。

WHERE子句允许你基于操作符提出的条件，找到与特定值、特定值区间或者多个值匹配的行，又或者基于条件排除某些行。操作符是一系列关键字，它们能够执行数学运算、比较操作还有逻辑操作。

代码清单3-7展示了一个基础示例。正如这里所示，在标准SQL语法中，WHERE子句出现在FROM关键字以及被查询表的名字之后。

xxxxxxxxxx
SELECT last_name, school, hire_date
FROM teachers
WHERE school = 'Myers Middle School';

代码清单3-7：使用WHERE过滤行

这个结果集只会展示在Myers Middle School任职的教师：

xxxxxxxxxx
last_name   school                  hire_date
---------   -------------------     ----------
Cole        Myers Middle School     2005-08-01
Bush        Myers Middle School     2011-10-30
Diaz        Myers Middle School     2005-08-30

这段代码使用了相等比较操作符查找与给定值完全匹配的行，当然，你也可以使用WHERE搭配其他操作符定制你想要的过滤条件。表3-1总结了最常用的比较操作符。取决于你正在使用的数据库系统，具体可用的操作符可能还不止这些。

表3-1：PostgreSQL中的比较和匹配操作符

* != 操作符并不是标准ANSI SQL的一部分，但它在PostgreSQL还有其他几个数据库系统中都可用。

接下来的一些例子将会展示比较操作符在实际中的应用。首先，我们可以使用相等操作符来查找名字为Janet的教师：

xxxxxxxxxx
SELECT first_name, last_name, school
FROM teachers
WHERE first_name = 'Janet';

接着，我们可以使用不相等操作符，列出表中除F.D. Roosevelt HS以外其他所有学校的名字：

xxxxxxxxxx
SELECT school
FROM teachers
WHERE school <> 'F.D. Roosevelt HS';

这里我们使用小于操作符以及YYYY-MM-DD格式，列出2000 年 1 月 1日之前雇用的所有教师：

xxxxxxxxxx
SELECT first_name, last_name, hire_date
FROM teachers
WHERE hire_date < '2000-01-01';

然后我们使用>=操作符，查找收入超过$43500的教师：

xxxxxxxxxx
SELECT first_name, last_name, salary
FROM teachers
WHERE salary >= 43500;

接下来的查询使用BETWEEN操作符查找收入介于$40000\text{至}$$40000至$65000之间的所有教师。注意BETWEEN是包括端点的，这意味着如果一个值与给定范围的起始或者结束匹配，那么它也会被包含在结果之内。

xxxxxxxxxx
SELECT first_name, last_name, school, salary
FROM teachers
WHERE salary BETWEEN 40000 AND 65000;

使用BETWEEN的时候请务必小心，因为它包括端点的本性可能会无意中导致对值的重复计数。举个例子，如果你在两次查询中，分别使用BETWEEN 10 AND 20和BETWEEN 20 AND 30对值进行过滤，那么包含值20的行将重复出现在两次查询的结果中。为了避免这种情况，我们可以使用更为精确的大于操作符和小于操作符来定义范围。比如说，接下来的这个查询返回的结果跟前一个查询相同，但这个新查询更明确地指定了范围：

xxxxxxxxxx
SELECT first_name, last_name, school, salary
FROM teachers
WHERE salary >= 40000 AND salary <= 65000;

因为这些操作符对于帮助我们找出想要的数据和答案方面起着至关紧要的作用，所以本书将会反复提到它们。

同时使用WHERE和LIKE/ILIKE

跟直观易懂的比较操作符不同，匹配操作符LIKE和ILIKE需要一些更详细的说明。这两个操作符都能够让你找到各式各样的值，并且这些值都包含与给定模式匹配的字符，当你不知道自己想要搜索的确切内容是什么，又或者当你在查找拼写错误的单词时，它们会非常有用。为了使用LIKE和ILIKE，你需要指定一个匹配模式，并在其中用到以下的一个或两个符号：

百分号（%） 匹配一个或多个字符的通配符 下划线号（_） 只匹配一个字符的通配符

举个例子，如果你正在尝试查找baker这个单词，那么以下这些LIKE模式将会与之匹配：

xxxxxxxxxx
LIKE 'b%'
LIKE '%ak%'
LIKE '_aker'
LIKE 'ba_er'

LIKE和ILIKE这两个操作符之间的区别在于前者是ANSI SQL标准的一部分，它是区分大小写的；而后者则是PostgreSQL的专有实现，它是不区分大小写的。代码清单3-8展示了一个LIKE和ILIKE这两个关键字产生不同结果的例子。第一个WHERE子句使用LIKE ➊ 查找以字符sam开头的名字，并且由于LIKE是区分大小写的，所以这次查询将不会得到任何结果。相反地，由于之后的第二个查询使用了不区分大小写的ILIKE ➋ ，所以它将从表中返回Samuel和Samantha作为结果。

xxxxxxxxxx
  SELECT first_name
  FROM teachers
➊ WHERE first_name LIKE 'sam%';
  SELECT first_name
  FROM teachers
➋ WHERE first_name ILIKE 'sam%';

代码清单3-8：使用LIKE和ILIKE进行过滤

因为我从不假设人们在输入人名、地名、产品名以及其他专有名词的时候总是能正确地使用大写，所以多年以来我一直倾向于使用ILIKE和匹配符，以此来避免意外地排除某些搜索结果，特别是在审查数据的时候。另一方面，如果访问数据的其中一个目的是了解它的质量，那么使用区分大小写的搜索将有助于寻找差异。

因为LIKE和ILIKE都是模式搜索，所以它们的性能在大型数据库中可能并不理想，但我们可以通过使用索引来解决这个问题，具体的信息将在第8章的“使用索引提高查询速度”一节中介绍。

通过AND和OR组合操作符

当我们把比较操作符组合起来的时候，它们会变得更有用。为了做到这一点，我们可以使用逻辑操作符AND和OR连接它们，并在有需要的时候为其加上括号。

代码清单3-9中的语句展示了三个以上述方式组合操作符的例子。

xxxxxxxxxx
  SELECT *
  FROM teachers
➊ WHERE school = 'Myers Middle School'
        AND salary < 40000;
        
  SELECT *
  FROM teachers
➋ WHERE last_name = 'Cole'
        OR last_name = 'Bush';
        
  SELECT *
  FROM teachers
➌ WHERE school = 'F.D. Roosevelt HS'
        AND (salary < 38000 OR salary > 40000);

代码清单3-9 ：使用AND和OR组合操作符

第一个查询在WHERE子句中使用AND ➊ 查找在Myers Middle School执教并且薪水低于$40000的教师。因为这个查询使用了AND来连接两个条件，所以一个行只有同时满足这两个条件的时候，才会被WHERE子句看作是符合标准并将其返回至查询结果当中。

第二个例子使用OR ➋ 搜索姓氏为Cole或者Bush的任意教师。在使用OR连接条件时，一个行只要满足任意一个条件，就会被WHERE子句看作是符合标准。

最后一个例子查找在Roosevelt执教，并且薪水低于$38000\text{或}\text{者}\text{高}\text{于}$$38000或者高于$40000的教师。当我们把语句放入括号里面时，它们会作为一组被先行求值，然后再与其他条件相结合。在这个例子中，教师执教的学校必需为 F.D. Roosevelt HS ，并且他的薪水必需低于或者高于指定数字，这样他才会被WHERE看作是符合标准。

如果在一个子句中同时使用AND和OR但是不使用任何括号，那么数据库就会先求值AND条件然后再求值OR条件。对于前面展示的最后一个例子，如果我们省略查询中的括号，那么就会看到不一样的结果——数据库将查找学校名为F.D. Roosevelt HS并且薪水低于$38000\text{的}\text{行}，\text{又}\text{或}\text{者}\text{任}\text{意}\text{学}\text{校}\text{但}\text{是}\text{薪}\text{水}\text{高}\text{于}$$38000的行，又或者任意学校但是薪水高于$40000 的行。你可以在查询工具里面试试这样做。

合而为一

你会逐渐开始明白，即使是前面那些简单的查询，也能够让我们灵活而准确地深入到数据当中，从而找到我们想要的东西。在此之上，通过使用AND和OR关键字来组合比较操作符语句，你可以为过滤提供多个条件，并通过包含 ORDER BY 子句来排序结果。

在了解了上述信息之后，让我们把本章前面介绍过的概念都组合到单个语句里面，以此来展示它们是如何结合在一起的。因为SQL对关键字的顺序有严苛的要求，所以请遵循以下惯例。

xxxxxxxxxx
SELECT column_names
FROM table_name
WHERE criteria
ORDER BY column_names;

代码清单3-10展示了一个针对teachers表的查询，其中包含了前面提到的所有部分。

xxxxxxxxxx
SELECT first_name, last_name, school, hire_date, salary
FROM teachers
WHERE school LIKE '%Roos%'
ORDER BY hire_date DESC;

代码清单3-10：包含WHERE和ORDER BY 的SELECT语句

这段代码会返回在Roosevelt High School执教的教师，并按照雇用日期从近到远排序他们。从这个结果我们也可以发现，教师的执教时长和他们当前薪资水平之间的某些联系。

xxxxxxxxxx
first_name  last_name   school              hire_date   salary
----------  ---------   -----------------   ----------  ------
Janet       Smith       F.D. Roosevelt HS   2011-10-30  36200
Kathleen    Roush       F.D. Roosevelt HS   2010-10-22  38500
Lee         Reynolds    F.D. Roosevelt HS   1993-05-22  65000

小结

到目前为止，我们已经学习了好几种不同的SQL查询基本结构，并为后续章节将要介绍的很多附加技能奠定了基础。排序、过滤还有只从表中选择最重要的列，这些手段可以从数据中获得惊人的信息量，并帮助你找到它所讲述的故事。

在接下来的一章，我们将要学习的是SQL的另一个基本面：数据类型。

实战演练

你可以通过以下练习探索基础查询：

1. 假设现在学区主管想要知道每间学校执教的教师名单。请编写一个查询，列出每间学校以及它们属下的所有教师，其中学校根据字母顺序排列，而教师则根据姓氏从A到Z的顺序排列。

2. 请编写一个查询，查找一位教师，他的名字以字母S开头并且薪水超过$40000。

3. 找出2010年1月1日以来雇用的所有教师，并按照薪水从高到低的顺序排列他们。