⌘+k ctrl+k
1.1.3 (stable)
Search Shortcut cmd + k | ctrl + k
SQL Introduction

这里我们提供了如何在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 的列,其类型为 VARCHARVARCHAR 指定了一种可以存储任意长度文本的数据类型。温度字段存储在 INTEGER 类型中,这是一种存储整数(即没有小数点的整数)的类型。FLOAT 列存储单精度浮点数(即带有小数点的数字)。DATE 存储日期(即年、月、日的组合)。DATE 只存储具体的日期,不存储与该日期相关的时间。

DuckDB 支持标准的 SQL 类型 INTEGER, SMALLINT, FLOAT, DOUBLE, DECIMAL, CHAR(n), VARCHAR(n), DATE, TIMETIMESTAMP

第二个示例将存储城市及其相关的地理位置:

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子句包含一个布尔(真值)表达式,只有布尔表达式为真的行才会被返回。在限定条件中允许使用常见的布尔运算符(ANDORNOT)。例如,以下查询检索了旧金山在雨天的天气:

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
旧金山
海沃德

再次强调,结果行的顺序可能会有所不同。你可以通过同时使用 DISTINCTORDER 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表中不匹配的行。我们很快会看到如何解决这个问题。
  • 有两个包含城市名称的列。这是正确的,因为来自weathercities表的列列表被连接在一起。然而,在实际应用中这是不理想的,因此你可能希望明确列出输出列,而不是使用*
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的WHEREHAVING子句之间的交互非常重要。WHEREHAVING之间的根本区别在于: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 将从给定表中删除所有行,使其为空。系统在执行此操作前不会请求确认。