SQL 语法

概述 #

ByteHouse 的 SQL 语法即为 ClickHouse SQL。ClickHouse SQL 和 ANSI SQL （标准 SQL）的语法基本一致，绝大多数语法没有差异。
关于不兼容 ANSI SQL 的部分，请官网文档参考： ClickHouse SQL语法与ANSI SQL的差异 。
ClickHouse 支持的语法类型包括：

• SELECT

• INSERT INTO

• CREATE

• ALTER

• SYSTEM

• SHOW

• GRANT

• REVOKE

• .....

我们在本文档中仅列出常用的 SELECT ， CREATE ， ALTER ， INSERT INTO ， DROP 。其余语法可参考 社区文档 。

查询：SELECT #

SELECT 查询执行数据检索。 默认情况下，将请求的数据返回给客户端，同时结合 INSERT INTO 可以被转发到不同的表。

语法 #

[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]





    

所有子句都是可选的，但紧接在 SELECT 后面的 expr_list 除外。
每个可选子句的具体内容与使用技巧在 ClickHouse 社区文档中介绍，请参阅对应部分：

• WITH 子句

• FROM 子句

• SAMPLE 子句

• JOIN 子句

• PREWHERE 子句

• WHERE 子句

• GROUP BY 子句

• LIMIT BY 子句

• HAVING 子句

• SELECT 子句

• DISTINCT 子句

• LIMIT 子句

• UNION ALL 子句

• INTO OUTFILE 子句

• FORMAT 子句

示例 #

摘自ClickHouse官网。查询数据集为 New Your Taxi数据集示例
SQL 示例如下：

--统计不同出租车类型的打车次数
SELECT cab_type, count(*) FROM trips_mergetree GROUP BY cab_type
--统计不同乘客数量的平均金额        
SELECT passenger_count, avg(total_amount) FROM trips_mergetree GROUP BY passenger_count
--统计每年不同乘客数量的总打车次数
SELECT passenger_count, toYear(pickup_date) AS year, count(*) FROM trips_mergetree GROUP BY passenger_count, year
--统计每年不同乘客数量不同打车距离的总打车次数，按年份与打车次数倒序排列
SELECT passenger_count, toYear(pickup_date) AS year, round(trip_distance) AS distance, count(*)
FROM trips_mergetree
GROUP BY passenger_count, year, distance
ORDER BY year, count(*) DESC





    

插入：INSERT INTO #

概述 #

INSERT INTO 语句主要用于向系统中添加数据.
查询的基本格式:

INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

对于存在于表结构中但不存在于插入列表中的列，它们将会按照如下方式填充数据：

• 如果存在 DEFAULT 表达式，根据 DEFAULT 表达式计算被填充的值。

• 如果没有定义 DEFAULT 表达式，则填充零或空字符

使用 SELECT 的结果写入 #

INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...

写入与SELECT的列的对应关系是使用位置来进行对应的，尽管它们在SELECT表达式与INSERT中的名称可能是不同的。如果需要，会对它们执行对应的类型转换。

从文件向表中插入数据 #

INSERT INTO [db.]table [(c1, c2, c3)] FROM INFILE file_name [COMPRESSION type] FORMAT format_name

使用上面的语句可以从客户端的文件上读取数据并插入表中。

• Format 可选： ClickHouse 输入输出格式 ；

• COMPRESSION 可选：系统默认会去读文件的拓展名作为文件的压缩方式，或者也可以在 COMPRESSION 语句中指明，支持的文件压缩格式如下： 'none' ， 'gzip' ， 'deflate' ， 'br' ， 'xz' ， 'zstd' ， 'lz4' ， 'bz2' 。

这个功能在 command-line client 和 clickhouse-local 是可用的。样例如下：

echo 1,A > input.csv ; echo 2,B >> input.csv
clickhouse-client --query="CREATE TABLE table_from_file (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
clickhouse-client --query="INSERT INTO table_from_file FROM INFILE 'input.csv' FORMAT CSV;"
clickhouse-client --query="SELECT * FROM table_from_file FORMAT PrettyCompact;"

结果:

┌─id─┬─text─┐
│  1 │ A    │
│  2 │ B    │
└────┴──────┘

新建库 #

该语句用于根据指定名称创建数据库。

CREATE DATABASE [IF NOT EXISTS] db_name





    

如果语句中存在 IF NOT EXISTS ，则当数据库已经存在时，该语句不会返回任何错误。

新建表 #

对于 CREATE TABLE ，存在以下几种方式。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
) ENGINE = engine

在指定的 db 数据库中创建一个名为 name 的表，如果语句中没有包含 db ，则默认使用当前选择的数据库作为 db 。
后面的是包含在括号中的表结构以及表引擎的声明。 其中表结构声明是一个包含一组列描述声明的组合。如果表引擎是支持索引的，那么可以在表引擎的参数中对其进行说明。
在最简单的情况下，列描述是指 name type 这样的子句。例如： RegionID UInt32 。 但是也可以为列另外定义默认值表达式（见后文）。

创建一个结构相同的表 #

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

创建一个与 db2.name2 具有相同结构的表，同时你可以对其指定不同的表引擎声明。如果没有表引擎声明，则创建的表将与 db2.name2 使用相同的表引擎。

创建一个结构相同的表并填充数据 #

CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...

使用指定的引擎创建一个与 SELECT 子句的结果具有相同结构的表，并使用 SELECT 子句的结果填充它。
以上所有情况，如果指定了 IF NOT EXISTS ，那么在该表已经存在的情况下，查询不会返回任何错误。在这种情况下，查询几乎不会做任何事情。
在 ENGINE 子句后还可能存在一些其他的子句，更详细的信息可以参考表引擎中关于建表的描述。

分布式 DDL 查询 （ON CLUSTER 子句）

对于 CREATE ， DROP ， ALTER ，以及 RENAME 查询，系统支持通过 ON CLUSTER 子句将其运行在整个集群上。 例如，以下查询将在 cluster 集群的所有节点上创建名为 all_hits 的 Distributed 表：

CREATE TABLE IF NOT EXISTS all_hits ON CLUSTER cluster (p Date, i Int32) ENGINE = Distributed(cluster, default, hits)

这个查询将最终在集群的每台主机上运行，即使一些主机当前处于不可用状态。同时它还保证了所有的查询在单台主机中的执行顺序。

ALTER 仅支持 *MergeTree ， Merge 以及 Distributed 等引擎表。 该操作有多种形式。以下仅列出对表结构的修改操作。语法示例如下：

增加列 #

ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after]

使用指定的 name , type , codec 以及 default_expr ，往表中增加新的列。
添加列仅仅是改变原有表的结构不会对已有数据产生影响。执行完 ALTER 后磁盘中也不会出现新的数据。如果查询表时列的数据为空，那么 ByteHouse 会使用列的默认值来进行填充。当数据块完成合并后，磁盘中会出现该列的数据。

删除列 #

DROP COLUMN [IF EXISTS] name

通过指定 name 删除列。如果语句包含 IF EXISTS ，执行时遇到不存在的列也不会报错。
从文件系统中删除数据。由于是删除列的整个文件，该语句几乎是立即执行完成的。
示例:

ALTER TABLE visits DROP COLUMN browser





    

修改列 #

MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [TTL]

该语句可以改变 name 列的属性：

• Type

• Default expression

• TTL

有关修改列TTL的示例，请参见 Column TTL .
如果语句中包含 IF EXISTS ，遇到不存在的列，sql执行不会报错。
当改变列的类型时，列的值也被转换了，如同对列使用 toType 函数一样。如果只改变了默认表达式，该语句几乎不会做任何复杂操作，并且几乎是立即执行完成的。
改变列的类型是唯一的复杂型动作 - 它改变了数据文件的内容。对于大型表，执行起来要花费较长的时间。因此，该操作不建议在生产环境下进行。

ALTER 操作限制 #

• 不支持对主键（primary key）或者采样键（sampling key）中的列进行删除。

• ALTER 操作会阻塞对表的所有读写操作。换句话说，当一个大的 SELECT 语句和 ALTER 同时执行时， ALTER 会等待，直到 SELECT 执行结束。与此同时，当 ALTER 运行时，新的 sql 语句将会等待。

• 对于不存储数据的表（例如 Merge 及 Distributed 表）， ALTER 仅仅改变了自身的表结构，不会改变从属的表结构。

删除现有实体。 如果指定了 IF EXISTS 子句，如果实体不存在，这些查询不会返回错误。

DROP DATABASE #

删除 db 数据库中的所有表，然后删除 db 数据库本身。
语法:

DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster]

DROP TABLE #

删除数据表
语法:

DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]