ClickHouse初级 - 第五章 SQL操作

骑猪看日落 2024-02-05 16:01 2阅读 0赞

#### 文章目录 ####

*   *  一、Insert
     *  二、Update 和 Delete
     *  三、查询操作
     *  四、alter 操作
     *  五、导出数据

> 基本上来说传统关系型数据库(以 MySQL 为例)的 SQL 语句，ClickHouse 基本都支持， 这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL(MySQL)不一致的地方。

### 一、Insert ###

> 基本与标准 SQL(MySQL)基本一致

> 1）标准

insert into [table_name] values(...),(....)

> 2）从表到表的插入

insert into [table_name] select a,b,c from [table_name_2]

### 二、Update 和 Delete ###

>  *  ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation 查询，它可以看 做 Alter 的一种。
>  *  虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很 “重”的操作，而且不支持事务。
>  *  “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。 所以尽量做批量的变更，不要进行频繁小数据的操作。

> 1）删除操作

alter table t_order_smt delete where sku_id ='sku_001';

> 2）修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id
    =102;

> 由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行 新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

### 三、查询操作 ###

> ClickHouse 基本上与标准 SQL 差别不大
> 
>  *  支持子查询
>  *  支持 CTE(Common Table Expression 公用表表达式 with 子句)
>  *  支持各种JOIN，但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，ClickHouse 也会视为两条新 SQL
>  *  窗口函数(官方正在测试中…)
>  *  不支持自定义函数
>  *  GROUP BY 操作增加了 with rollup\\with cube\\with total 用来计算小计和总计。
> 
> **ps（以下是维护a，b时，三关键字查询的维度数据）:**  
> 维度a,b
> 
>  *  rollup（上卷）:  
>     group by  
>     group by a  
>     group by a,b
>  *  cube（多维分析）:  
>     group by a,b  
>     group by a  
>     group by b  
>     group by
>  *  total（总计）:  
>     group by a,b  
>     group by

> 1）插入数据

alter table t_order_mt delete where 1=1; 
    insert into t_order_mt values 
    (101,'sku_001',1000.00,'2020-06-01 12:00:00'), 
    (101,'sku_002',2000.00,'2020-06-01 12:00:00'),
    (103,'sku_004',2500.00,'2020-06-01 12:00:00'),
    (104,'sku_002',2000.00,'2020-06-01 12:00:00'), 
    (105,'sku_003',600.00,'2020-06-02 12:00:00'), 
    (106,'sku_001',1000.00,'2020-06-04 12:00:00'), 
    (107,'sku_002',2000.00,'2020-06-04 12:00:00'), 
    (108,'sku_004',2500.00,'2020-06-04 12:00:00'), 
    (109,'sku_002',2000.00,'2020-06-04 12:00:00'),
    (110,'sku_003',600.00,'2020-06-01 12:00:00');

> 2）with rollup:**从右至左去掉维度进行小计**

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup;

![请添加图片描述][fbc9343649914af99a8071b853070e8f.png]

> 3）with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;

![请添加图片描述][b4091897476d46c5ba41e5ef2c9f248f.png]

> 4）with totals: 只计算合计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals;

![请添加图片描述][50e995c5dd0f43fdb54c61c46e70fc4f.png]

### 四、alter 操作 ###

> 同 MySQL 的修改字段基本一致

> 1）新增字段

alter table tableName add column newcolname String after col1;

2）修改字段类型

alter table tableName modify column newcolname String;

3）删除字段

alter table tableName drop column newcolname;

### 五、导出数据 ###

> clickhouse-client --query “select \* from t\_order\_mt where  
> create\_time=‘2020-06-01 12:00:00’” --format CSVWithNames>  
> /opt/module/data/rs1.csv

> 更多支持格式参照:  
> https://clickhouse.tech/docs/en/interfaces/formats/

[fbc9343649914af99a8071b853070e8f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/01e73c5e031a414ebd660315e862323b.png
[b4091897476d46c5ba41e5ef2c9f248f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/91ef7057c76643ed9ebbbc6c2ef4e980.png
[50e995c5dd0f43fdb54c61c46e70fc4f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/447fa96c203847cebf8c0645a3257ecc.png