基本操作

1、建库

CREATE DATABASE [IF NOT EXISTS] db_name [ENGINE=engine];

数据库目前一共支持5种引擎：

• Ordinary: 默认引擎，在此数据库里可以使用任意类型的表引擎。
• Dictionary: 字典引擎，此类数据库会自动为所有数据字典创建他们的数据表。
• Memory: 内存引擎，此类数据库里的表只会停留在内存中，不涉及磁盘操作，服务重启数据会被清除。
• Lazy：日志引擎，此类数据库里的表只能使用Log系列的表引擎。
• MySQL: MySQL引擎，此类数据库会自动拉取MySQL中的表，并为他们创建MySQL表引擎的数据表

create database if not exists testDb engine=Ordinary;

2、表

常规定义方法：

CREATE TABLE [IF NOT EXISTS] [db_name].tb_name (
    name1 [type] [Default|Materialized|Alias expr],
    name2 [type] [Default|Materialized|Alias expr],
    ....
) ENGINE=engine

复制其他表结构：这种方式支持在不同数据库之间复制表结构

CREATE TABLE [IF NOT EXISTS] [db_name2].tb_name2 AS [db_name1].tb_name1 ENGINE=engine

通过SELECT子句形式创建：

CREATE TABLE [IF NOT EXISTS] [db_name].tb_name ENGINE=engine AS SELECT...

其他

DROP TABL [IF EXISTS] [db_name].tb_name;
SHOW CREATE TABLE tb_name;
DESCRIBE TABLE tb_name;
DESC tb_name;

默认值表达式

表字段支持三种默认值表达式定义方法：DEFAULT、MATERIALIZED、ALIAS

无论哪种表达式，表字段一旦被定义默认值，它便不再强制要求定义数据类型，因为clickhouse会根据默认值进行类型判断。如果同时定义了表字段的类型和默认值表达式，则以明确定义的数据类型为主

create table test(
 a UInt16,
 b default 123,
 c String default b
)engine=TinyLog;

insert into test(a) values(1);
insert into test(a,c) values(2,111);
select a,b,c,toTypeName(b),toTypeName(c) from test; 

b字段没有定义数据类型，默认值是整形123；c字段定义数据类型，且默认值等于b字段

三种默认值表达式的区别：

• 数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中，而MATERIALIZED和ALIAS都不能显示赋值，只能依靠计算取值。
• 数据查询时，只有DEFAULT类型的字段可以通过SELECT * 返回，而MATERIALIZED和ALIAS类型的字段不会出现在SELECT * 的结果集中。
• 数据存储时，只有DEFAULT和MATERIALIZED类型的字段才支持持久化，而ALIAS类型的字段不支持，它的取值总是依靠计算产生

表的基本操作

　　只有Merge、MergeTree和Distributed三类表引擎支持ALTER操作

//添加字段
ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [TYPE] [DEFAULT_EXPR] [AFTER name_after];
//修改数据类型
ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [TYPE] [DEFAULT_EXPR];
//修改备注
ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name '****'
//删除字段
ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name
//移动表:RENAME TABLE 只能在同一个服务节点内
RENAME TABLE [db_name01.]tb_name01 TO [db_name02.]tb_name02, [db_name11.]tb_name11 TO [db_name12.]tb_name12, ...
//清空表
TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name;

分区表

     只有MergeTree家族的表引擎才支持表分区，使用PARTITION BY 指定分区键

create table if not exists test1(
 id UInt8,
 message String,
 etime Date
)engine=MergeTree
partition by toYYYYMM(etime) 
order by id

　　数据

INSERT INTO test1(id, message, etime) 
VALUES(1,'A','2021-02-24'),(2,'B','2021-02-24'),(3,'C','2021-01-23'),(4,'D','2021-01-23');

select * from test1

　　通过system.parts系统表，查看表分区的状态：

select table,partition,path from system.parts where database ='testDb' and table='test1'

 　　删除指定分区：

ALTER TABLE tb_name DROP PARTITION partition_expr;

　　复制分区数据

ALTER TABLE tb_name1 REPLACE PARTITION partition_expr FROM tb_name2;

　　clickhouse支持将A表分区的数据复制到B表。可以相互复制的表需要满足：

• 两张表需要有相同的分区键
• 表结构完全相同

　　重置分区数据：

ALTER TABLE tb_name CLEAR COLUMN column_naem IN PARTITION partition_expr;

//卸载分区
ALTER TABLE tb_name DETACH PARTITION partition_expr;
//装载分区
ALTER TABLE tb_name ATTACH PARTITION partition_expr;

　　分区被卸载后，物理目录并没有被删除而是转移到当前数据目录的detached子目录下。而装载分区则是反向操作。一旦分区被转移到detached子目录，就代表其脱离clickhouse的管理，clickhouse并不会主动清理这些文件，除非我们手动删除或者重新装载

数据的删除与修改

　　Clickhouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation查询，它可以看做是ALTER语句的变种。Mutation虽然能实现DELETE和UPDATE，但不能用通常意义上的DELTE和UPDATE来理解：Mutation是种很重的操作，更适用于批量数据的删除修改；其次不支持事物，语句一旦被提交就立刻对现有数据产生影响，无法回滚；最后，Mutation是一个异步执行的过程，语句提交就立即返回。

//
ALTER TABLE [db_name.]tb_name DELETE WHERE fileter_expr;
//UPDATE支持在一条语句中同时修改多个字段，但分区键和主键不能作为被修改的字段
ALTER TABLE [db_name.]tb_name UPDATE column1 = expr1, column2 = expr2, ... WHERE fileter_expr;