这里我们提供了如何在SQL中执行简单操作的概述。 本教程仅旨在为您提供介绍,绝不是SQL的完整教程。 本教程改编自PostgreSQL教程。
DuckDB的SQL方言紧密遵循PostgreSQL方言的惯例。 少数例外情况列在PostgreSQL兼容性页面上。
在以下示例中,我们假设您已经安装了DuckDB命令行界面(CLI)shell。有关如何安装CLI的信息,请参阅安装页面。
概念
DuckDB 是一个关系型数据库管理系统 (RDBMS)。这意味着它是一个用于管理存储在关系中的数据的系统。关系本质上是表的数学术语。
每个表都是一个命名的行集合。给定表的每一行都有相同的一组命名列,每列都有特定的数据类型。表本身存储在模式中,模式的集合构成了您可以访问的整个数据库。
Creating a New Table
您可以通过指定表名以及所有列名及其类型来创建一个新表:
CREATE TABLE weather (
city VARCHAR,
temp_lo INTEGER, -- minimum temperature on a day
temp_hi INTEGER, -- maximum temperature on a day
prcp FLOAT,
date DATE
);
你可以在shell中输入这个,包括换行符。命令直到分号才结束。
空白(即空格、制表符和换行符)可以在SQL命令中自由使用。这意味着你可以以不同于上面的方式对齐输入命令,甚至可以全部写在一行上。两个破折号(--
)用于引入注释。它们后面的内容直到行尾都会被忽略。SQL对关键字和标识符不区分大小写。当返回标识符时,它们的原始大小写会被保留。
在SQL命令中,我们首先指定要执行的命令类型:CREATE TABLE
。之后是命令的参数。首先给出表名weather
,然后是列名和列类型。
city VARCHAR
指定表有一个名为 city
的列,其类型为 VARCHAR
。VARCHAR
指定了一种可以存储任意长度文本的数据类型。温度字段存储在 INTEGER
类型中,这是一种存储整数(即没有小数点的整数)的类型。FLOAT
列存储单精度浮点数(即带有小数点的数字)。DATE
存储日期(即年、月、日的组合)。DATE
只存储具体的日期,不存储与该日期相关的时间。
DuckDB 支持标准的 SQL 类型 INTEGER
, SMALLINT
, FLOAT
, DOUBLE
, DECIMAL
, CHAR(n)
, VARCHAR(n)
, DATE
, TIME
和 TIMESTAMP
。
第二个示例将存储城市及其相关的地理位置:
CREATE TABLE cities (
name VARCHAR,
lat DECIMAL,
lon DECIMAL
);
最后,应该提到的是,如果你不再需要一个表或者想要以不同的方式重新创建它,你可以使用以下命令来删除它:
DROP TABLE ⟨tablename⟩;
填充表格行
插入语句用于向表中填充行:
INSERT INTO weather
VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
非数值的常量(例如,文本和日期)必须用单引号(''
)包围,如示例所示。日期类型的输入日期必须格式化为'YYYY-MM-DD'
。
我们可以以相同的方式插入到cities
表中。
INSERT INTO cities
VALUES ('San Francisco', -194.0, 53.0);
到目前为止使用的语法要求你记住列的顺序。另一种语法允许你明确列出列:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
如果您愿意,可以以不同的顺序列出列,甚至可以省略某些列,例如,如果prcp
未知:
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
提示 许多开发者认为明确列出列比依赖隐式顺序更好。
请输入上面显示的所有命令,以便在接下来的部分中有一些数据可以使用。
或者,您可以使用COPY
语句。这对于大量数据来说更快,因为COPY
命令针对批量加载进行了优化,尽管它比INSERT
允许的灵活性较少。一个使用weather.csv
的示例如下:
COPY weather
FROM 'weather.csv';
源文件的文件名必须在运行该进程的机器上可用。还有许多其他方法可以将数据加载到DuckDB中,更多信息请参阅相应的文档部分。
查询表
要从表中检索数据,需要查询表。使用SQL SELECT
语句来完成此操作。该语句分为选择列表(列出要返回的列的部分)、表列表(列出从中检索数据的表的部分)和可选的限定条件(指定任何限制的部分)。例如,要检索表weather的所有行,请输入:
SELECT *
FROM weather;
这里 *
是“所有列”的简写。因此,同样的结果可以通过以下方式获得:
SELECT city, temp_lo, temp_hi, prcp, date
FROM weather;
输出应为:
城市 | 最低温度 | 最高温度 | 降水量 | 日期 |
---|---|---|---|---|
旧金山 | 46 | 50 | 0.25 | 1994-11-27 |
旧金山 | 43 | 57 | 0.0 | 1994-11-29 |
海沃德 | 37 | 54 | NULL | 1994-11-29 |
你可以在选择列表中编写表达式,而不仅仅是简单的列引用。例如,你可以这样做:
SELECT city, (temp_hi + temp_lo) / 2 AS temp_avg, date
FROM weather;
这应该给出:
城市 | 平均温度 | 日期 |
---|---|---|
旧金山 | 48.0 | 1994-11-27 |
旧金山 | 50.0 | 1994-11-29 |
海沃德 | 45.5 | 1994-11-29 |
注意如何使用AS
子句来重新标记输出列。(AS
子句是可选的。)
可以通过添加一个WHERE
子句来“限定”查询,该子句指定了所需的行。WHERE
子句包含一个布尔(真值)表达式,只有布尔表达式为真的行才会被返回。在限定条件中允许使用常见的布尔运算符(AND
、OR
和NOT
)。例如,以下查询检索了旧金山在雨天的天气:
SELECT *
FROM weather
WHERE city = 'San Francisco'
AND prcp > 0.0;
结果:
城市 | 最低温度 | 最高温度 | 降水量 | 日期 |
---|---|---|---|---|
旧金山 | 46 | 50 | 0.25 | 1994-11-27 |
您可以请求查询结果按排序顺序返回:
SELECT *
FROM weather
ORDER BY city;
城市 | 最低温度 | 最高温度 | 降水量 | 日期 |
---|---|---|---|---|
海沃德 | 37 | 54 | NULL | 1994-11-29 |
旧金山 | 43 | 57 | 0.0 | 1994-11-29 |
旧金山 | 46 | 50 | 0.25 | 1994-11-27 |
在这个例子中,排序顺序没有完全指定,因此你可能会以任意顺序得到旧金山的行。但如果你这样做,你总是会得到上面显示的结果:
SELECT *
FROM weather
ORDER BY city, temp_lo;
您可以请求从查询结果中删除重复的行:
SELECT DISTINCT city
FROM weather;
city |
---|
旧金山 |
海沃德 |
再次强调,结果行的顺序可能会有所不同。你可以通过同时使用 DISTINCT
和 ORDER BY
来确保结果的一致性:
SELECT DISTINCT city
FROM weather
ORDER BY city;
表之间的连接
到目前为止,我们的查询一次只能访问一个表。查询可以同时访问多个表,或者以同时处理表中多行的方式访问同一个表。一次访问同一表或不同表的多行的查询称为连接查询。例如,假设您希望列出所有天气记录以及相关城市的位置。为此,我们需要将weather
表中每行的city列与cities
表中所有行的name列进行比较,并选择这些值匹配的行对。
这将通过以下查询完成:
SELECT *
FROM weather, cities
WHERE city = name;
城市 | 最低温度 | 最高温度 | 降水量 | 日期 | 名称 | 纬度 | 经度 |
---|---|---|---|---|---|---|---|
旧金山 | 46 | 50 | 0.25 | 1994-11-27 | 旧金山 | -194.000 | 53.000 |
旧金山 | 43 | 57 | 0.0 | 1994-11-29 | 旧金山 | -194.000 | 53.000 |
观察结果集的两件事:
- 海沃德市没有结果行。这是因为在
cities
表中没有与海沃德匹配的条目,所以连接操作忽略了weather
表中不匹配的行。我们很快会看到如何解决这个问题。 - 有两个包含城市名称的列。这是正确的,因为来自
weather
和cities
表的列列表被连接在一起。然而,在实际应用中这是不理想的,因此你可能希望明确列出输出列,而不是使用*
:
SELECT city, temp_lo, temp_hi, prcp, date, lon, lat
FROM weather, cities
WHERE city = name;
城市 | 最低温度 | 最高温度 | 降水量 | 日期 | 经度 | 纬度 |
---|---|---|---|---|---|---|
旧金山 | 46 | 50 | 0.25 | 1994-11-27 | 53.000 | -194.000 |
旧金山 | 43 | 57 | 0.0 | 1994-11-29 | 53.000 | -194.000 |
由于所有列的名称都不同,解析器自动找到了它们所属的表。如果两个表中有重复的列名,你需要限定列名以显示你指的是哪一个,如下所示:
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.lon, cities.lat
FROM weather, cities
WHERE cities.name = weather.city;
广泛认为,在连接查询中限定所有列名是一种良好的风格,这样如果以后在其中一个表中添加了重复的列名,查询也不会失败。
到目前为止所见的连接查询也可以用这种替代形式编写:
SELECT *
FROM weather
INNER JOIN cities ON weather.city = cities.name;
这种语法不像上面那样常用,但我们在这里展示它是为了帮助你理解以下主题。
现在我们将弄清楚如何将海沃德的记录重新加入。我们希望查询做的是扫描weather
表,并为每一行找到匹配的城市行。如果没有找到匹配的行,我们希望用一些“空值”来替代cities
表的列。这种查询被称为外连接。(我们目前看到的连接都是内连接。)命令如下所示:
SELECT *
FROM weather
LEFT OUTER JOIN cities ON weather.city = cities.name;
城市 | 最低温度 | 最高温度 | 降水量 | 日期 | 名称 | 纬度 | 经度 |
---|---|---|---|---|---|---|---|
旧金山 | 46 | 50 | 0.25 | 1994-11-27 | 旧金山 | -194.000 | 53.000 |
旧金山 | 43 | 57 | 0.0 | 1994-11-29 | 旧金山 | -194.000 | 53.000 |
海沃德 | 37 | 54 | NULL | 1994-11-29 | NULL | NULL | NULL |
此查询称为左外连接,因为连接运算符左侧的表的每一行在输出中至少会出现一次,而右侧的表只会输出与左侧表的某些行匹配的行。当输出没有右侧表匹配的左侧表行时,右侧表的列将替换为空(null)值。
Aggregate Functions
与大多数其他关系数据库产品一样,DuckDB支持聚合函数。聚合函数从多个输入行计算单个结果。例如,有一些聚合函数可以计算一组行的count
(计数)、sum
(总和)、avg
(平均值)、max
(最大值)和min
(最小值)。
例如,我们可以找到任何地方的最低温度读数:
SELECT max(temp_lo)
FROM weather;
最高温度 |
---|
46 |
如果我们想知道阅读发生在哪个城市(或哪些城市),我们可能会尝试:
SELECT city
FROM weather
WHERE temp_lo = max(temp_lo); -- WRONG
但这将不起作用,因为聚合函数 max 不能在 WHERE
子句中使用。(存在此限制是因为 WHERE
子句决定了哪些行将包含在聚合计算中;因此显然它必须在聚合函数计算之前进行评估。)然而,通常情况下,可以通过重新表述查询来实现所需的结果,这里通过使用子查询来实现:
SELECT city
FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city |
---|
San Francisco |
这是可以的,因为子查询是一个独立的计算,它独立于外部查询中发生的事情计算自己的聚合。
聚合函数在与GROUP BY
子句结合使用时也非常有用。例如,我们可以通过以下方式获取每个城市观测到的最高低温:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
城市 | 最低温度最大值 |
---|---|
旧金山 | 46 |
海沃德 | 37 |
这为我们提供了每个城市的一个输出行。每个聚合结果都是在该城市匹配的表行上计算的。我们可以使用HAVING
来过滤这些分组行:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
city | max(temp_lo) |
---|---|
海沃德 | 37 |
这为我们提供了仅包含所有temp_lo
值低于40的城市的结果。最后,如果我们只关心名称以S
开头的城市,我们可以使用LIKE
操作符:
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%' -- (1)
GROUP BY city
HAVING max(temp_lo) < 40;
有关LIKE
操作符的更多信息可以在模式匹配页面找到。
理解聚合函数与SQL的WHERE
和HAVING
子句之间的交互非常重要。WHERE
和HAVING
之间的根本区别在于:WHERE
在计算分组和聚合之前选择输入行(因此,它控制哪些行进入聚合计算),而HAVING
在计算分组和聚合之后选择分组行。因此,WHERE
子句不能包含聚合函数;试图使用聚合函数来确定哪些行将作为聚合的输入是没有意义的。另一方面,HAVING
子句总是包含聚合函数。
在前面的例子中,我们可以在WHERE
中应用城市名称限制,因为它不需要聚合。这比将限制添加到HAVING
中更高效,因为我们避免了为所有未通过WHERE
检查的行进行分组和聚合计算。
更新
您可以使用UPDATE
命令更新现有行。假设您发现11月28日之后的温度读数都偏差了2度。您可以按如下方式更正数据:
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
查看数据的新状态:
SELECT *
FROM weather;
city | temp_lo | temp_hi | prcp | date |
---|---|---|---|---|
San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
旧金山 | 41 | 55 | 0.0 | 1994-11-29 |
海沃德 | 35 | 52 | NULL | 1994-11-29 |
删除
可以使用DELETE
命令从表中删除行。假设你不再对Hayward的天气感兴趣。那么你可以执行以下操作来从表中删除这些行:
DELETE FROM weather
WHERE city = 'Hayward';
所有属于海沃德的天气记录已被移除。
SELECT *
FROM weather;
city | temp_lo | temp_hi | prcp | date |
---|---|---|---|---|
San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
旧金山 | 41 | 55 | 0.0 | 1994-11-29 |
在发布以下形式的声明时应谨慎:
DELETE FROM ⟨table_name⟩;
警告 如果没有限定条件,
DELETE
将从给定表中删除所有行,使其为空。系统在执行此操作前不会请求确认。