SQL，或“结构化查询语言”，是一种向关系数据库提出问题和更新数据的方法。在创建第一个数据库时，您已经看到了SQL。回忆:
SELECT postgis_full_version();
但这是一个关于数据库的问题。现在我们已经将数据加载到数据库中，让我们使用SQL来询问数据问题!例如,
：“纽约市所有社区的名字是什么?”
点击“查询工具”按钮，在pgAdmin中打开SQL查询窗口。
image
然后在查询窗口中输入以下查询
SELECT name FROM nyc_neighborhoods;
并单击Execute Query
image
按钮(绿色三角形)。
查询将运行几秒(毫秒)，并返回129个结果。
image
但这里到底发生了什么?为了理解，让我们从SQL的四个“动词”开始，
- SELECT, returns rows in response to a query(查询)
- INSERT, adds new rows to a table（插入）
- UPDATE, alters existing rows in a table（修改）
-
DELETE, removes rows from a table（删除）
我们几乎只使用SELECT来询问使用空间函数的表的问题。
1.1、SELECT 查询
选择查询通常是这样的:
SELECT some_columns FROM some_data_source WHERE some_condition;
some_columns是列名或列值的函数。some_data_source可以是单个表，也可以是根据键或条件连接两个表而创建的复合表。some_condition是一个限制要返回的行数的过滤器。
“布鲁克林(Brooklyn)所有的社区都叫什么名字?”
我们返回到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有一系列内置的聚合函数，包括用于平均值的通用函数avg()和用于标准差的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列，这样我们就可以确定哪个统计信息应用于哪个自治市。在聚合查询中，只能输出属于(a)分组子句成员或(b)聚合函数的列。
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
1.2、练习
使用nyc_census_blocks表，回答以下问题(不要偷看答案!)
这里有一些有用的信息。回想一下关于我们的数据部分的nyc_census_blocks表定义。
image.png
下面是一些常见的SQL聚合函数，你可能会觉得有用:
- avg() - 一组记录中的值取平均值
- sum() - 一组记录中值的和
- count() - 一组记录中记录的数量
现在的问题:
1.nyc_streets表中有多少条记录?
SELECT Count(*)
FROM nyc_streets;
19091
2.纽约有多少条街是B开头的?
SELECT Count(*)
FROM nyc_streets
WHERE name LIKE 'B%';
1282
3.纽约市的人口是多少?
SELECT Sum(popn_total) AS population
FROM nyc_census_blocks;
8175032
4.布朗克斯(The Bronx)的人口是多少?
SELECT Sum(popn_total) AS population
FROM nyc_census_blocks
WHERE boroname = 'The Bronx';
1385108
5.每个自治市有多少个“社区”?
SELECT boroname, count(*)
FROM nyc_neighborhoods
GROUP BY boroname;
-
boroname | count
---------------+-------
Queens | 30
Brooklyn | 23
Staten Island | 24
The Bronx | 24
Manhattan | 28
6.对于每个行政区，白人人口占总人口的百分比是多少?
SELECT
boroname,
100.0 * Sum(popn_white)/Sum(popn_total) AS white_pct
FROM nyc_census_blocks
GROUP BY boroname;
boroname | white_pct
---------------+------------------
Brooklyn | 42.8011737932687
Manhattan | 57.4493039480463
The Bronx | 27.9037446899448
Queens | 39.722077394591
Staten Island | 72.8942034860154
网友评论