ClickHouse SQL

info

ClickHouse 的 SQL 语法与常见数据库大同小异。本文记录 ClickHouse 中一些比较基础、比较常见的 SQL 示例，更多 SQL 语法可参考官方文档：https://clickhouse.com/docs/en/sql-reference/statements/select。

CREATE

CREATE 语句可以用于创建库、表、视图等。

创建库：

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]

创建表：

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

创建视图：

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT ...

创建用户 panda：

CREATE USER panda HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'panda';

SELECT

SELECT 语句支持 GROUP BY、ORDER BY、HAVING、LIMIT 等子句，其示例如下：

[WITH expr_list|(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] 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 ROLLUP|WITH CUBE] [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]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename]
[FORMAT format]

INSERT

INSERT 语句用于数据插入：

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

也可以插入其他表的数据：

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

ALTER

ALTER 语句可以用于表结构修改、行数据更新、行数据删除等。

caution

大部分的 ALTER 语句仅在 MergeTree 系列引擎中得到支持。

更新行数据：

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr

删除行数据：

ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr

DROP

DROP 语句可以用于删除库、表、视图等。

删除库：

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

删除表：

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

删除视图：

DROP VIEW [IF EXISTS] [db.]name [ON CLUSTER cluster]

删除用户：

DROP USER [IF EXISTS] name [,...] [ON CLUSTER cluster_name]

TRUNCATE

TRUNCATE 语句可以删除目标表的所有数据：

TRUNCATE TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]