ClickHouse 数据库DML和DDL操作

一、 数据库
1、 语法：CREATE DATABASE [IF NOT EXISTS] db_name [ENGINE = engine]
2、 数据库目前支持5种引擎：
 Oridinary：默认引擎，无须声明
 Dictionary：字段引擎
 Memory：内存引擎，用于存放临时数据
 Lazy：日志引擎，只能使用与Log系列的表引擎
 MySQL：此类数据库下的表会自动拉取MySQL数据库的数据
3、 查看数据库：SHOW DATABASES
4、 删除数据库：DROP DATABASE [IF EXISTS] db_name
二、 创建表
1、 方法一：一般方法

CREATE TABLE [IF NOT EXISTS] [db_name].table_name(
   name1 [TYPE] [DEFAULT|MATERIALIZED|ALIAS expr],
   name2 [TYPE] [DEFAULT|MATERIALIZED|ALIAS expr],
   …..
) ENGINE = engine

[db_name]：指定数据库，如果为空，默认是default
engine：表的引擎，根据数据库引擎来选择使用的表引擎
2、 方法二：复制其他表的结构
CREATE TABLE [IF NOT EXISTS] [db_name1].table_name AS
[db_name2].table_name2 [ENGINE=engine]
表之间的引擎可以不一样
3、 方法三：通过SELECT子句的方式创建

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

4、 删除表

DROP TABLE [IF EXISTS] [db_name].table_name

5、 查看表

DESC [db_name].table_name

三、 字段的默认表达式
1、 数据写入：只有DEFAULT的字段可以出现在INSERT语句中，MATERIALIZED和ALIAS不能显示赋值，是通过计算取值。
2、 数据查询：只有DEFAULT的字段才能通过SELECT * 返回。而MATERIALIZED和ALIAS不会出现在SELECT * 的结果集中。
3、 数据存储：只有DEFAULT和MATERIALIZED类型的字段才支持持久化，而ALIAS类型的字段不支持持久化，数据不会落盘到磁盘
4、 修改默认值：

ALTER TABLE [db_name].table_name MODIFY COLUMN col_name DEFAULT value

修改默认值不会影响数据表内先前已经存在的数据。
四、 临时表
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name(
name1 [TYPE] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [TYPE] [DEFAULT|MATERIALIZED|ALIAS expr],
……
)
 它的生命周期是会话绑定的，只支持Memory表引擎，如果会话结束，数据表就会销毁。
 临时表不属于任何数据库，在创建表时不用指定数据库和表引擎
 如果临时表的表名和普通表的表名一样时，优先读取临时表的数据。
五、 分区表

CREATE TABLE [IF NOT EXISTS] table_name(
   name1 [TYPE] [DEFAULT|MATERIALIZED|ALIAS expr],
   name2 [TYPE] [DEFAULT|MATERIALIZED|ALIAS expr],
   …..
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(column)
ORDER BY column

六、 普通视图
CREATE VIEW [IF NOT EXISTS] [db_name1].table_name AS SELECT ….
七、 物化视图
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db_name1].table_name
[TO[db.]name] [ENGINE=engine] [POPULATE] AS SELECT ….
八、 ALTER操作
只有MergeTree、Merge和Distributed这三类表引擎支持ALTER
1、 追加新字段

ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr [AFTER name_after]

2、 修改数据类型

ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr]

3、 修改备注

ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name ‘描述信息’

4、 删除已有字段

ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name

5、 移动数据表

RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12

6、 清空数据表

TRUNCATE TABLE [IF EXITS] [db_name.]tb_name

九、 数据分区的基本操作
1、 查看分区
system.parts表查看
2、 删除分区

ALTER TABLE tb_name DROP PARTITION partition_expr

3、 复制分区数据
将A表的分区partition_expr复制到B表

ALTER TABLE B REPLACE partition_expr FROM A

复制条件：
 两张表需要拥有相同的分区键
 表结构必须相同
4、 重置分区数据

ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr

5、 卸载与装载分区

ALTER TABLE tb_name DETACH/ATTACH PARTITION partition_expr

十、 分布式DDL执行

CREATE TABLE [IF NOT EXISTS] table_name ON CLUSTER cluster_name(
   name1 [TYPE] [DEFAULT|MATERIALIZED|ALIAS expr],
   name2 [TYPE] [DEFAULT|MATERIALIZED|ALIAS expr],
   …..
) ENGINE = engine

分布式节点都会创建对应的表
十一、 数据写入
1、 常用

INSERT INTO [db.]table [(c1,c2,c3,c4…)] values 
(v11,v12,v13,v14….),(v21,v22,v23,v24…)….

2、 指定格式
INSERT INTO [db.]table [(c1,c2,c3,c4…)] FORMAT format_name data_set
示例：

INSERT INTO gmall.item_master FORMAT CSV \
‘80001’,’测试’,’30001’,’2021-05-01 12:43:23’\
‘80002’,’测试’,’30001’,’2021-05-01 14:25:23’\
‘80003’,’测试’,’30002’,’2021-05-01 16:13:23’\

3、 SELECT子句

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

十二、 数据删除与修改
异步操作，不支持事务，统称为Mutation语句。语句提交后立即返回，并不代表逻辑执行完成，具体执行进度通过system.mutation查询。

ALTER TABLE [db.]tb_name DELETE WHERE filter_expr
ALTER TABLE [db.]tb_name UPDATE column1=expr1 [,…] WHERE filter_expr