7. 简单SQL

SQL，或称为"结构化查询语言"，是一种用于查询和更新关系数据库中数据的方法。我们在创建第一个数据库时已经见过 SQL。回想一下：

SELECT postgis_full_version();

但那是关于数据库的问题。现在我们已经将数据加载到我们的数据库中，让我们使用SQL来查询数据！例如，

“纽约市所有社区的名称是什么？”

点击"Query Tool"按钮，在pgAdmin中打开SQL查询窗口。

_images/pgadmin_05.png

然后在查询窗口中输入以下查询语句

SELECT name FROM nyc_neighborhoods;

然后点击**执行查询**按钮（绿色三角形）。

_images/pgadmin_08.png

查询将运行几（毫）秒钟，并返回 129 个结果。

_images/pgadmin_09.png

但这里到底发生了什么呢？要理解这一点，让我们从SQL的四个“动词”开始，

  • SELECT，返回对查询的响应中的行

  • INSERT，向表中添加新行

  • UPDATE，修改表中现有的行

  • DELETE，从表中删除行

我们几乎完全会使用``SELECT``来使用空间函数对表格进行查询。

7.1. SELECT 查询

一个SELECT查询通常的形式是:

SELECT some_columns FROM some_data_source WHERE some_condition;

注解

要查看所有``SELECT``参数的概要，请参阅 `PostgresSQL文档<http://www.postgresql.org/docs/current/interactive/sql-select.html>`_。

some_columns 可以是列名或列值的函数。 some_data_source 可以是单个表，也可以是通过在关键列上或根据条件连接两个表创建的复合表。 some_condition 是一个过滤器，用于限制要返回的行数。

“布鲁克林的所有社区的名称是什么？”

我们回到手中有一个过滤器的``nyc_neighborhoods``表中。该表包含了纽约所有的社区，但我们只想要布鲁克林的社区。

SELECT name
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

查询将运行更少的（毫）秒，并返回 23 个结果。

有时我们需要对查询结果应用函数。例如，

“布鲁克林所有社区的名称中有多少个字母？”

幸运的是，PostgreSQL有一个字符串长度函数， char_length(string)。

SELECT char_length(name)
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

通常，我们对单个行的兴趣不如对所有行都适用的统计数据感兴趣。因此，知道社区名称的长度可能不如知道名称的平均长度有趣。接受多行并返回单个结果的函数称为“聚合”函数。

PostgreSQL有一系列内置的聚合函数，包括用于计算平均值的通用函数:command:avg()，以及用于计算标准差的函数:command:stddev()。

“布鲁克林所有社区名称中字母的平均数量和标准偏差是多少？”

SELECT avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

         avg         |       stddev
---------------------+--------------------
 11.7391304347826087 | 3.9105613559407395

在我们上一个示例中，聚合函数应用于结果集中的每一行。如果我们希望摘要针对整体结果集内的较小分组进行，我们该怎么做呢？为此，我们添加一个``GROUP BY``子句。聚合函数通常需要添加``GROUP BY``语句来根据一个或多个列对结果集进行分组。

“纽约市所有社区名称的平均字母数，按行政区划报告？”

SELECT boroname, avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  GROUP BY boroname;

我们在输出结果中包含了``boroname``列，这样我们就可以确定哪个统计数据适用于哪个行政区。在聚合查询中，您只能输出属于分组子句的列或聚合函数。

   boroname    |         avg         |       stddev
---------------+---------------------+--------------------
 Brooklyn      | 11.7391304347826087 | 3.9105613559407395
 Manhattan     | 11.8214285714285714 | 4.3123729948325257
 The Bronx     | 12.0416666666666667 | 3.6651017740975152
 Queens        | 11.6666666666666667 | 5.0057438272815975
 Staten Island | 12.2916666666666667 | 5.2043390480959474

7.2. 函数列表

avg(expression): 返回数值列的平均值的PostgreSQL聚合函数。

char_length(string): PostgreSQL字符串函数，返回字符串中的字符数。

stddev(expression): PostgreSQL聚合函数，返回输入值的标准差。