基本查询

本文档介绍了 YMatrix 支持的基本查询语句分类及语法构成。

1 表表达式

表表达式通常是一个可以对表进行计算的 FROM 子句（SELECT 后跟列表内容也为表表达式，但不作为本节重点，因此不予强调）。FROM 子句可以自由选择后跟 WHERE/GROUP BY/HAVING 子句或子查询。

简单的表表达式只是指存储于磁盘上的表（不包含临时表或视图），即所谓的基表。不过实际使用中，我们可以使用更复杂的表达式以各种方式修改或组合基表，以达到查询目的。

以下给出一些表表达式示例：

从单个表读取数据的表表达式语法：

=# FROM <表名>

1.1 FROM 子句

1.1.1 连接表

连接表语法：

=# FROM t1 <连接类型> t2 [连接条件]

其中 <连接类型> 可以是 CROSS JOIN/INNER JOIN/LEFT JOIN/RIGHT JOIN/FULL JOIN，INNER JOIN 为内连接，后三种为外连接。 FROM t1 CROSS JOIN t2 等价于 FROM t1 INNER JOIN t2 ON TRUE，等价于 FROM t1,t2。

假设有表 t1（包含 c1、c2 列），t2（包含 c1、c2 列）：

=# FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2
=# FROM t1 LEFT JOIN t2 USING (c1,c2)

1.1.2 子查询

当一个外层查询中包含一个或多个内层查询，称之为嵌套查询。外层查询称之为主查询，内层查询称为子查询。

子查询可以多层嵌套，且它几乎可以出现在查询的任何地方，包括 SELECT/GROUP BY 列表、FROM 表表达式、WHERE/ON/HAVING 条件表达式。在这里为 FROM 表表达式中的子查询举例。

语法：

=# FROM (SELECT * FROM <表名>) AS <别名>

假设有表 table1：

=# FROM (SELECT * FROM table1) AS t1

相当于 FROM table1 AS t1。

1.2 WHERE 子句

WHERE 子句可用于查询条件过滤，语法如下：

=# WHERE <过滤条件>

其中，<过滤条件> 可以是返回一个数值或者逻辑值的任何值表达式。

假设有表 t（包含 c1 列），t1（包含 c1 列），t2（包含 c1、c2、c3 列）：

=# SELECT ... FROM t1 WHERE c1 > 5;
=# SELECT ... FROM t1 WHERE c1 IN (1, 2, 3);
=# SELECT ... FROM t1 WHERE c1 IN (SELECT c1 FROM t2);
=# SELECT ... FROM t1 WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = t.c1 + 10);
=# SELECT ... FROM t1 WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = t.c1 + 10) AND 100;
=# SELECT ... FROM t1 WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > t.c1);

1.3 GROUP BY 和 HAVING 子句

用于分组后分别对每个组进行聚集计算。当查询语句中出现 GROUP BY 语句，只能用 HAVING 子句来添加过滤条件，而不能用 WHERE。因为 WHERE 过滤条件只能作用于原始数据集，而 HAVING 过滤条件可以作用于聚集后的数据集。

1.3.1 可用于分组聚集计算的基本聚集函数

YMatrix 提供了如下可用于分组聚集计算的基本聚集函数：

函数	描述
count(<列名>/*)	有效值个数（只有 `count(*)` 包含空值）
sum(<列名>)	累加和
avg(<列名>)	指定列平均值
min(<列名>)	指定列最小值
max(<列名>)	指定列最大值

假设有表 t1（包含 c1 列、c2 列）：

=#  SELECT count(*), 
           max(c1),
           c2 
    FROM t1
    GROUP BY c2;

注意！
高级聚集函数请见高级查询。

1.3.2 GROUP BY 子句

注意！
出现在 SELECT 列表的非聚集函数列，必须出现在 GROUP BY 列表，否则查询将会报错。

假设有表 t1（包含 c1 列、c2 列、c3 列）：

-- 正确语法
=# SELECT c1, avg(c2)
    FROM t1
    GROUP BY c1;

-- 正确语法
=# SELECT c1, c2, avg(c3)
    FROM t1
    GROUP BY (c1,c2);

-- 错误语法
=# SELECT c1, c2, avg(c3)
    FROM t1
    GROUP BY c1;

1.3.3 HAVING 子句

对聚集函数的结果进行过滤，不能使用常规的 WHERE 子句，需要使用专门的 HAVING 子句。原因是 WHERE 过滤条件只能作用于原始数据集，而 HAVING 过滤条件可以作用于聚集后的数据集。

假设有表 t1（包含 c1 列、c2 列）：

-- 正确语法
=# SELECT c1, max(c2)
    FROM t1
    GROUP BY c1
    HAVING avg(c2) < 60;

-- 错误语法
=# SELECT c1, max(c2)
    FROM t1
    GROUP BY c1
    WHERE avg(c2) < 60;

2 SELECT 列表

SELECT 列表可以通过计算构建出一个临时的虚拟表，并决定最后输出结果会显示哪些列。

例如，查询 t1 表的 c1、c2、c3 列：

=# SELECT c1, c2, c3 FROM t1;

查询 t1 表的 c1、c2 列和 t2 表的 c1 列。如果多个表中有相同名称的列，需要在列前指定表名：

=# SELECT t1.c1, t2.c1, t1.c2 FROM t1,t2;

在 SELECT 列表中使用 AS 关键字定义别名供后续表达式使用：

=# SELECT c1 AS value, c2 + 1 AS sum FROM t1,t2;

AS 关键字可以省略，但是在此示例中由于 value 也是 YMatrix 的关键字之一，所以需要添加双引号区分：

=# SELECT c1 "value", c2 + 1 AS sum FROM t1,t2;

在 SELECT 关键字后添加 DISTINCT 关键字可以为查询结果集去重，保证结果集的唯一性：

=# SELECT DISTINCT c1 FROM t1;

3 组合查询

组合查询指的是使用 UNION、INTERSECT 或 EXCEPT 关键字将两个查询的结果集分别以计算并集、交集或补集的方式组合起来。

我们可以通过以下语法来组合两个查询的结果：

=# <查询1> UNION [ALL] <查询2>
=# <查询1> INTERSECT [ALL] <查询2>
=# <查询1> EXCEPT [ALL] <查询2>

显式指定 ALL 会保留输出结果中的重复行。

查询 1 和查询 2 指的是使用了基于这个点讨论的任何特性之一的查询。

UNION 有效地将查询 2 的结果附加在了查询 1 后，即查询 1 和查询 2 的并集。
INTERSECT 返回既属于查询 1 又属于查询 2 的所有结果行，即查询1 和查询 2 的交集。
EXCEPT 返回属于查询 1 但不属于查询 2 的所有行，即查询 2 在两个结果集并集中的补集。

UNION 语法示例，其他两种组合关键字亦可参考：

=# SELECT c1 FROM t1 UNION SELECT c2 FROM t2 LIMIT 10;

此查询中的 LIMIT 是对前述所有计算结果的限制条件，即 (SELECT c1 FROM t1 UNION SELECT c2 FROM t2) LIMIT 10;。

4 行排序

使用 ORDER BY 子句选择列进行排序。

使用 AS 关键字定义列别名，并用其排序（AS 可省略）：

=# SELECT c1 + c2 AS sum FROM t1 ORDER BY sum;

使用 ASC 关键字将结果集按照升序顺序（由小至大）进行排序：

=# SELECT c1 FROM t1 ORDER BY c1 ASC;

使用 DESC 关键字将结果集按照降序顺序（由大至小）进行排序：

=# SELECT c1 FROM t1 ORDER BY c1 DESC;

排序选项对于每个排序列都独立起作用，因此如果有多个列需要排序，则需分别指定其排序方式，例如以下两个 SQL 都可以实现 c1 列升序，c2 列降序：

=# SELECT c1, c2 FROM t1 ORDER BY c1 ASC, c2 DESC;
=# SELECT c1, c2 FROM t1 ORDER BY c1, c2 DESC;

当不做升序或降序排序的指定时，默认升序排序。

5 LIMIT 和 OFFSET

使用 LIMIT 和 OFFSET 来限制查询语句输出结果。LIMIT 可以限制返回的行数，例如 LIMIT 1 意为该查询最终返回 1 行结果，LIMIT ALL 相当于没有限制。OFFSET 用于指定最终结果的偏移量。查询计算完成后，跳过结果集的指定行数，从指定行数的下一行开始返回最终结果。

使用说明：

OFFSET 子句必须始终与 LIMIT 子句一起使用，如果省略 LIMIT，则 OFFSET 子句将被忽略并返回所有行。
省略 OFFSET 只使用 LIMIT 是可以的，相当于 OFFSET 0。
OFFSET 须为非负整数，否则将抛出错误。
OFFSET 指定跳过的行在最终返回前仍然需要被计算，因此指定一个大的偏移量可能会比较低效。

语法：

=# SELECT <列表>
    FROM <表表达式>
    [ ORDER BY ... ]
    [ LIMIT { <数字> | ALL } ] [ OFFSET <数字> ]

假设有表 t1（包含 c1 列），只查看降序排序的前 10 条数据（值最大的十条）：

=# SELECT c1 FROM t1
    ORDER BY c1 DESC
    LIMIT 10;

只查看第 11 条到第 20 条数据：

=# SELECT c1 FROM t1
    ORDER BY c1 DESC
    LIMIT 10 OFFSET 10;

此条查询操作会对排序后的结果首先从第 1 条数据进行偏移，偏移量为 10，即从第 11 条数据开始计算限制条数，因此最后输出结果为第 11 条到第 20 条数据。

6 JOIN 和 USING

可以使用 JOIN 子句并用 USING 指定连接键来做多表连接操作：

假设有表 t1（包含 c1 列、c2 列）， t2（包含 c2 列）：

=# SELECT t1.c1, t2.* FROM t2 JOIN t1 USING (c2) ORDER BY t2.c2 DESC LIMIT 10;