select currentDatabase();

select version();
show create database db_name;

show databases;
show tables;
desc  tableName;
create database test;
drop table if exists test.t1;
create /*temporary*/ table /*if not exists*/ test.m1 (
 id UInt16
,name String
) ENGINE = Memory;
insert into test.m1 (id, name) values (1, 'abc'), (2, 'bbbb');
select * from test.m1;


CREATE TABLE dev.xxx(insert_time DateTime DEFAULT now(), seq_id String,result String)ENGINE = TinyLog

drop table if exists dev.xxx





CREATE

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = xxx


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

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


CREATE TABLE test01( id UInt32,col1 String,col2 String,create_date date ) ENGINE = MergeTree(create_date, (id), 8192);
ENGINE 表引擎类型
MergeTree 最常用的引擎，要求有一个日期字段(Date , 不是 DateTime)，还有主键




INSERT 导入数据


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

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

cat file.csv | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV"

clickhouse-client --quey="insert into tableName format CSV" < file.csv


导出数据
select * from tableName into outfile 'path/file'
clickhouse-client  --database bdName -u default --password password --query='select * from tableName' > abc




SELECT
SELECT [DISTINCT] expr_list
    [FROM [db.]table | (subquery) | table_function] [FINAL]
    [SAMPLE sample_coeff]
    [ARRAY JOIN ...]
    [GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
    [PREWHERE expr]
    [WHERE expr]
    [GROUP BY expr_list] [WITH TOTALS]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT [n, ]m]
    [UNION ALL ...]
    [INTO OUTFILE filename]
    [FORMAT format]
    [LIMIT n BY columns]


ALTER
ALTER查询仅支持* MergeTree族表引擎，以及Merge表引擎和Distributed表引擎。
ALTER操作阻塞所有对表的其他操作。

#添加列
ALTER TABLE [db].name [ON CLUSTER cluster] ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after]
#删除列
ALTER TABLE [db].name [ON CLUSTER cluster] DROP COLUMN [IF EXISTS] name
#重置指定分区中列的所有数据
ALTER TABLE [db].name [ON CLUSTER cluster] CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name
#添加列注解
ALTER TABLE [db].name [ON CLUSTER cluster] COMMENT COLUMN [IF EXISTS] name 'comment'
#修改列类型或者列的默认值
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY COLUMN [IF EXISTS] name [type] [default_expr]
#添加索引
ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value AFTER name [AFTER name2]
#删除索引
ALTER TABLE [db].name DROP INDEX name
#分离分区
ALTER TABLE table_name DETACH PARTITION partition_expr
#删除分区
ALTER TABLE table_name DROP PARTITION partition_expr
#添加被分离的分区
ALTER TABLE table_name ATTACH PARTITION|PART partition_expr
#复制table1中的分区数据到table2
ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1
#重置列值为默认值，默认值为创建表时指定
ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr
#创建指定分区或者所有分区的备份
ALTER TABLE table_name FREEZE [PARTITION partition_expr]
#从其他分片中复制分区数据
ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'